How to get SQL from Hibernate Criteria API not for logging

Question

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

User · Answer

I like this if you want to get just some parts of the query:

new CriteriaQueryTranslator(
    factory,
    executableCriteria,
    executableCriteria.getEntityOrClassName(), 
    CriteriaQueryTranslator.ROOT_SQL_ALIAS)
        .getWhereCondition();

For instance something like this:

String where = new CriteriaQueryTranslator(
    factory,
    executableCriteria,
    executableCriteria.getEntityOrClassName(), 
    CriteriaQueryTranslator.ROOT_SQL_ALIAS)
        .getWhereCondition();

String sql = "update my_table this_ set this_.status = 0 where " + where;

User · Answer

Here is a method I used and worked for me  public static String toSql Session session  Criteria criteria       String sql         Object   parameters   null      try          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           sql    String f get loader           Field fp   CriteriaLoader class getDeclaredField  traslator            fp setAccessible true           CriteriaQueryTranslator translator    CriteriaQueryTranslator  fp get loader           parameters   translator getQueryParameters   getPositionalParameterValues              catch Exception e           throw new RuntimeException e             if  sql   null           int fromPosition   sql indexOf   from             sql    SELECT      sql substring fromPosition            if  parameters  null  amp  amp  parameters length gt 0               for  Object val   parameters                    String value                      if val instanceof Boolean                       value     Boolean val   1   0                    else if  val instanceof String                       value       val                                        sql   sql replaceFirst        value                                     return sql replaceAll  left outer join     nleft outer join   replace   and      nand    replace   on      non

User · Answer

For anyone wishing to do this in a single line  e g in the Display Immediate window  a watch expression or similar in a debug session   the following will do so and  pretty print  the SQL   new org hibernate jdbc util BasicFormatterImpl   format  new org hibernate loader criteria CriteriaJoinWalker  org hibernate persister entity OuterJoinLoadable   org hibernate impl CriteriaImpl crit  getSession   getFactory   getEntityPersister   org hibernate impl CriteriaImpl crit  getSession   getFactory   getImplementors   org hibernate impl CriteriaImpl crit  getEntityOrClassName    0   new org hibernate loader criteria CriteriaQueryTranslator   org hibernate impl CriteriaImpl crit  getSession   getFactory     org hibernate impl CriteriaImpl crit    org hibernate impl CriteriaImpl crit  getEntityOrClassName   org hibernate loader criteria CriteriaQueryTranslator ROOT SQL ALIAS    org hibernate impl CriteriaImpl crit  getSession   getFactory    org hibernate impl CriteriaImpl crit   org hibernate impl CriteriaImpl crit  getEntityOrClassName     org hibernate impl CriteriaImpl crit  getSession   getEnabledFilters     getSQLString          or here s an easier to read version   new org hibernate jdbc util BasicFormatterImpl   format     new org hibernate loader criteria CriteriaJoinWalker        org hibernate persister entity OuterJoinLoadable          org hibernate impl CriteriaImpl crit  getSession   getFactory   getEntityPersister            org hibernate impl CriteriaImpl crit  getSession   getFactory   getImplementors              org hibernate impl CriteriaImpl crit  getEntityOrClassName    0         new org hibernate loader criteria CriteriaQueryTranslator              org hibernate impl CriteriaImpl crit  getSession   getFactory                org hibernate impl CriteriaImpl crit               org hibernate impl CriteriaImpl crit  getEntityOrClassName              org hibernate loader criteria CriteriaQueryTranslator ROOT SQL ALIAS          org hibernate impl CriteriaImpl crit  getSession   getFactory          org hibernate impl CriteriaImpl crit         org hibernate impl CriteriaImpl crit  getEntityOrClassName           org hibernate impl CriteriaImpl crit  getSession   getEnabledFilters            getSQLString        Notes    The answer is based on the solution posted by ramdane i  It assumes the Criteria object is named crit  If named differently  do a search and replace  It assumes the Hibernate version is later than 3 3 2 GA but earlier than 4 0 in order to use BasicFormatterImpl to  pretty print  the HQL  If using a different version  see this answer for how to modify  Or perhaps just remove the pretty printing entirely as it s just a  nice to have   It s using getEnabledFilters rather than getLoadQueryInfluencers   for backwards compatibility since the latter was introduced in a later version of Hibernate  3 5     It doesn t output the actual parameter values used if the query is parameterized

User · Answer

This answer is based on user3715338 s answer  with a small spelling error corrected  and mixed with Michael s answer for Hibernate 3 6 - based on the accepted answer from Brian Deterling  I then extended it  for PostgreSQL  with a couple more types replacing the questionmarks   public static String toSql Criteria criteria        String sql           Object   parameters   null      try               CriteriaImpl criteriaImpl    CriteriaImpl  criteria          SessionImpl sessionImpl    SessionImpl  criteriaImpl getSession            SessionFactoryImplementor factory   sessionImpl getSessionFactory            String   implementors   factory getImplementors criteriaImpl getEntityOrClassName             OuterJoinLoadable persister    OuterJoinLoadable  factory getEntityPersister implementors 0            LoadQueryInfluencers loadQueryInfluencers   new LoadQueryInfluencers            CriteriaLoader loader   new CriteriaLoader persister  factory              criteriaImpl  implementors 0  toString    loadQueryInfluencers           Field f   OuterJoinLoader class getDeclaredField  sql            f setAccessible true           sql    String  f get loader           Field fp   CriteriaLoader class getDeclaredField  translator            fp setAccessible true           CriteriaQueryTranslator translator    CriteriaQueryTranslator  fp get loader           parameters   translator getQueryParameters   getPositionalParameterValues              catch  Exception e                throw new RuntimeException e             if  sql    null                int fromPosition   sql indexOf   from             sql     nSELECT       sql substring fromPosition            if  parameters    null  amp  amp  parameters length  gt  0                        for  Object val   parameters                                String value                        if  val instanceof Boolean                                        value     Boolean  val     1     0                                     else if  val instanceof String                                        value         val                                          else if  val instanceof Number                                        value   val toString                                      else if  val instanceof Class                                        value           Class  val  getCanonicalName                                            else if  val instanceof Date                                        SimpleDateFormat sdf   new SimpleDateFormat                           yyyy-MM-dd HH mm ss SSS                        value         sdf format  Date  val                                           else if  val instanceof Enum                                        value          Enum  val  ordinal                                      else                                       value   val toString                                      sql   sql replaceFirst        value                                     return sql replaceAll  left outer join     nleft outer join   replaceAll            and      nand    replaceAll   on      non    replaceAll   lt  gt                  replaceAll   lt       lt     replaceAll   gt       gt

User · Answer

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

User · Answer

For those using NHibernate  this is a port of  ram  s code  public static string GenerateSQL ICriteria criteria                NHibernate Impl CriteriaImpl criteriaImpl    NHibernate Impl CriteriaImpl criteria          NHibernate Engine ISessionImplementor session   criteriaImpl Session          NHibernate Engine ISessionFactoryImplementor factory   session Factory           NHibernate Loader Criteria CriteriaQueryTranslator translator                new NHibernate Loader Criteria CriteriaQueryTranslator                  factory                   criteriaImpl                   criteriaImpl EntityOrClassName                   NHibernate Loader Criteria CriteriaQueryTranslator RootSqlAlias            String   implementors   factory GetImplementors criteriaImpl EntityOrClassName            NHibernate Loader Criteria CriteriaJoinWalker walker   new NHibernate Loader Criteria CriteriaJoinWalker               NHibernate Persister Entity IOuterJoinLoadable factory GetEntityPersister implementors 0                                    translator                                  factory                                  criteriaImpl                                  criteriaImpl EntityOrClassName                                  session EnabledFilters            return walker SqlString ToString

User · Answer

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

User · Answer

If you are using Hibernate 3 6 you can use the code in the accepted answer  provided by Brian Deterling  with slight modification     CriteriaImpl c    CriteriaImpl  criteria    SessionImpl s    SessionImpl  c getSession      SessionFactoryImplementor factory    SessionFactoryImplementor  s getSessionFactory      String   implementors   factory getImplementors c getEntityOrClassName       LoadQueryInfluencers lqis   new LoadQueryInfluencers      CriteriaLoader loader   new CriteriaLoader  OuterJoinLoadable  factory getEntityPersister implementors 0    factory  c  implementors 0   lqis     Field f   OuterJoinLoader class getDeclaredField  sql      f setAccessible true     String sql    String  f get loader

[java] How to get SQL from Hibernate Criteria API (not for logging)

Examples related to java

Examples related to sql

Examples related to hibernate

Examples related to criteria

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

Examples related to java

Examples related to sql

Examples related to hibernate

Examples related to criteria

[java] How to get SQL from Hibernate Criteria API (not for logging)