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

Question

Hi, is there an easy way to get the (to-be-generated) sql from a Hibernate Criteria?

Ideally I would have something like:

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

... build up the criteria ...
... and then do something like ...

String sql = criteria.toSql()

(But this of course does not exist)

The idea would then be to use the sql as part of a huge 'MINUS' query (I need to find the differences between 2 identical schemas - identical in structure, not in data - and the MINUS is not supported by Hibernate)

(BTW I know I can check the SQL from the log files)

Answer 1

If you mean 'MINUS' as in NOT IN, you can assemble an HQL-String instead of using criteria doing something like

SELECT * FROM table1 WHERE yourconditions = here AND table1.whatever NOT IN (
    SELECT table2.whatever FROM table2
);

Answer 2

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.

Answer 3

anyway to do that BUT generate sql with parameters already bound????? That would rock as I am not sure the order of the ? and we need to generate the sql with about 30 parameters and then wrap that in a select * from any ideas?