How to Get SQL from Hibernate Criteria API (*Not* for Logging)

How to get SQL from Hibernate Criteria API (*not* for logging)

I've done something like this using Spring AOP so I could grab the sql, parameters, errors, and execution time for any query run in the application whether it was HQL, Criteria, or native SQL.

This is obviously fragile, insecure, subject to break with changes in Hibernate, etc, but it illustrates that it's possible to get the SQL:

CriteriaImpl c = (CriteriaImpl)query;
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);

Wrap the entire thing in a try/catch and use at your own risk.

Getting SQL from Criteria

The easiest way to do this -- edit your Hibernate configuration.

You can set up

hibernate.show_sql = true

to enable the logging of all the generated SQL statements to the console.

Moreover you can set up

hibernate.format_sql = true
hibernate.use_sql_comments = true

to make it more readable

Read more about Hibernate configuration here

Get SQL String from Hibernate query

There is a tool called p6spy. It's a bit older and I am afraid it isn't maintained anymore. It gave me good results in the past, anyway.

Edit: just found that it is being actively developed again. Works like a charm for me!

get SQL from hibernate get

Enable Hibernate Statistics in persistence.xml

<property name="hibernate.generate_statistics">true</property>

Then use SessionFactory.getStatistics() and access necessary statiscits (probably getQueries()).

For more info see Hibernate Statistics docs here or here or this post on SO.

EDIT:

If you want to do it without logging, you'll have to use JDBC driver proxy, for example log4jdbc, p6spy or jdbc-trace-wrapper.

How to get bound parameters out of a Hibernate Criteria object?

You can log the Criteria and the Restrictions will be displayed as well:

Criteria criteria = session.createCriteria(Post.class)
    .add(Restrictions.eq("title", "post"));
LOGGER.info("Criteria: {}", criteria);

will display:

Criteria: CriteriaImpl(com.vladmihalcea.book.hpjp.hibernate.association.AllAssociationTest$Post:this[][title=post])

How to get number of submission of last 24 hours using Hibernate Criteria API

//this is working

@Override
public long last24Hours() {
    Date yesterday = new Date(System.currentTimeMillis() - 1000L * 60L * 60L * 24L);
    CriteriaBuilder cb = sessionFactory.getCurrentSession().getCriteriaBuilder();
    CriteriaQuery<Long> query = cb.createQuery(Long.class);
    Root<FlowSubmission> flowSubmission = query.from(FlowSubmission.class);
    query.select(cb.count(flowSubmission));
    query.where(cb.greaterThanOrEqualTo(flowSubmission.get("submitted"), yesterday));
    Query<Long> q = sessionFactory.getCurrentSession().createQuery(query);

    return q.uniqueResult();
}

---

Related Topics