Store Hibernate Query in variable

Question

we are using hibernate to Insert/Update/Delete data in mysql db. whenever the hibernate query is fired it is shown on console. But my requirement is to store the query in db for audit purpose. hence i would require to store the query in a string variable so that i can further save it in db.

public int updatebarePumpData(Tbl13BarePumpData barePumpData)
{
    if (log.isInfoEnabled())
        log.info("start--BarePumpGADaoImpl---updatebarePumpData");
    int ans = 0;
    session = HibernateUtil.getSessionFactory().getCurrentSession();
    try
    {
        tx = session.beginTransaction();
        Tbl13BarePumpData barepumpObj = (Tbl13BarePumpData) session.load(Tbl13BarePumpData.class, barePumpData.getBarePumpdataId());
        barepumpObj.getBarePumpdataId();
        barepumpObj.setParameter(barePumpData.getParameter());
        barepumpObj.setValue(barePumpData.getValue());
        barepumpObj.setModifiedBy(barePumpData.getModifiedBy());
        barepumpObj.setModifiedDate(barePumpData.getModifiedDate());
        session.save(barepumpObj);
        tx.commit();
        ans = barepumpObj.getBarePumpdataId();
    }
    catch (Exception e)
    {
        if (tx != null && tx.isActive())
            tx.rollback();
        log.error(e.getMessage(), e);
    }

    if (log.isInfoEnabled())
        log.info("end--BarePumpGADaoImpl---updatebarePumpData");
    return ans;
}

The console output is

Hibernate: update pumpManagement_mp.dbo.tbl_13_barePump_data set barepumpga_id=?, parameter=?, value=?, createdBy=?, createdDate=?, modifiedBy=?, modifiedDate=?, company=? where barePumpdata_id=?

I would like to have the same Output in a variable

String qry=hibernate show query

Any help would be appreciated

Thanks & Regards, Pranav C Lunavat

Why this is needed programmatically while the application is running? A simple `grep` through your log file will result in all queries that were executed and then you can use that result as your set of queries. I do not think hibernate provides public APIs to get this. You would have to use something like `P6SpyDriver` that will proxy your actual MySQL driver in your configuration so that you can obtain a separate log files of queries. — ring bearer, May 26 '15 at 04:27

Afsun Khammadli · Answer 1 · 2015-05-26T04:49:37.493

1

Following code help you how to get sql query of Criteria.

Criteria criteria = session.createCriteria(User.class);

CriteriaImpl c = (CriteriaImpl)criteria;
SessionImpl s = (SessionImpl)c.getSession();

SessionFactoryImplementor factory = (SessionFactoryImplementor)s.getSessionFactory();
String[] implementors = factory.getImplementors( c.getEntityOrClassName() );
CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable)factory.getEntityPersister(implementors[0]),
    factory, c, implementors[0], s.getEnabledFilters());

Field f = OuterJoinLoader.class.getDeclaredField("sql");
f.setAccessible(true);
String sql = (String)f.get(loader);

I hope this will help you.

edited May 26 '15 at 04:49

answered May 26 '15 at 04:41

Afsun Khammadli

2,048
4
18
28

But if you see, we have not used criteria. criteria would be for select query. we are saving the object/pojo in database. how do we capture the save/update query – Pranav May 26 '15 at 07:14
I never use for getting string of sql while inserting.But this will help you I think http://stackoverflow.com/questions/11717888/get-sql-string-from-hibernate-query – Afsun Khammadli May 26 '15 at 07:24

score 0 · Answer 2 · answered May 26 '15 at 04:32

There is this way of using a JDBCAppender of log4j, and then use log4j configuration as below. How much of unwanted logs, because of the nature of Hibernate, is something which you will have to try and find out.

Log4j DB Logging

Something like this will be your final configuration.

 log4j.rootLogger = DEBUG, DB
 log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
 log4j.appender.DB.URL=jdbc:mysql://localhost/DBNAME
 log4j.appender.DB.driver=com.mysql.jdbc.Driver
 log4j.appender.DB.user=user_name    log4j.appender.DB.password=password
 log4j.appender.DB.sql=INSERT INTO LOGS VALUES('%x','%d','%C','%p','%m')

 log4j.appender.DB.layout=org.apache.log4j.PatternLayout

Store Hibernate Query in variable

2 Answers2