Is there a 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)
createCriteria(Employee. class); Criterion salary = Restrictions.gt("salary", 2000); Criterion name = Restrictions. ilike("firstNname","zara%"); // To get records matching with OR conditions LogicalExpression orExp = Restrictions.or(salary, name); cr.
I first set the Book entity as the root and join it with the Author entity. Then I use the count function to determine the number of Books in the SELECT clause. And after that, I compare the id of the Author entity which got selected in the outer query with the id of the Author selected in the subquery.
In Hibernate, the Criteria API helps us build criteria query objects dynamically. Criteria is a another technique of data retrieval apart from HQL and native SQL queries. The primary advantage of the Criteria API is that it is intuitively designed to manipulate data without using any hard-coded SQL statements.
Here's "another" way to get the SQL :
CriteriaImpl criteriaImpl = (CriteriaImpl)criteria; SessionImplementor session = criteriaImpl.getSession(); SessionFactoryImplementor factory = session.getFactory(); CriteriaQueryTranslator translator=new CriteriaQueryTranslator(factory,criteriaImpl,criteriaImpl.getEntityOrClassName(),CriteriaQueryTranslator.ROOT_SQL_ALIAS); String[] implementors = factory.getImplementors( criteriaImpl.getEntityOrClassName() ); CriteriaJoinWalker walker = new CriteriaJoinWalker((OuterJoinLoadable)factory.getEntityPersister(implementors[0]), translator, factory, criteriaImpl, criteriaImpl.getEntityOrClassName(), session.getLoadQueryInfluencers() ); String sql=walker.getSQLString();
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With