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

Asked 16/2, 2009 at 20:41 Answered 7/7, 2022 at 21:32

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)

Walston answered 16/2, 2009 at 20:41 Comment(0)

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.

Trews answered 17/2, 2009 at 0:52 Comment(6)

Wouldn't it be more portable to redirect the hibernate log to a string temporarily? – Elrod 15/8, 2011 at 13:28

Possibly, but if multiple threads were executing SQL at the same time, it may be hard to figure out which log messages go with the SQL you are trying to capture. An interceptor using onPrepareStatement would get you the SQL too, but the OP asked for a way to get the SQL for a given Criteria object. – Trews 15/8, 2011 at 21:26

Is there a way to also get the parameters of the SQL query printed out? – Moo 11/5, 2013 at 12:51

Here is my method to get the parameters: gist.github.com/bdeterling/5563683. I haven't revisited it in about 4 years. – Trews 12/5, 2013 at 14:4

If you're using Hibernate 5.x with JPA, and have a javax.persistence.Query instance, this works: return new org.hibernate.engine.jdbc.internal.BasicFormatterImpl().format(query.unwrap(org.hibernate.query.Query.class).getQueryString()); – Greenhouse 11/2, 2018 at 20:54

My Entity associated Criteria query has a oneTomany relationship with another table. So CriteriaQuery.list() result is a joined result. But converted sql string doesn't contains any joining? – Wichita 3/10, 2019 at 10:8

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();

Chibcha answered 8/10, 2010 at 8:28 Comment(5)

I tried your solution and it works out great except for one thing. It doesn't print out correctly when my criteria is having criteria.setMaxResults(n). It doesn't shot that requirement in the generated statement. Do you know why? – Fifteen 16/1, 2011 at 18:11

Thanks, helped me to debug an application I had to work on and to find the error immediately. – Epner 6/6, 2014 at 7:51

Nice answer. I've adapted this to run in a single line so it can be easily run in a debug session or added to a watch list etc. See answer below: stackoverflow.com/questions/554481#46788621 – Jacobsen 17/10, 2017 at 11:38

May be not related but how to fit it for

@Repository public interface EmployeeRepository extends JpaRepository<Employee, Long> {      @Query("select c from Employee c  where  c.login_time > :timeOfLogin")      public List<Employee> findEvasiveEmployees(@Param("timeOfLogin") LocalDateTime timeOfLogin);  }

– Edeline 15/5, 2023 at 1:52

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.

Trews answered 17/2, 2009 at 0:52 Comment(6)

Wouldn't it be more portable to redirect the hibernate log to a string temporarily? – Elrod 15/8, 2011 at 13:28

Is there a way to also get the parameters of the SQL query printed out? – Moo 11/5, 2013 at 12:51

Here is my method to get the parameters: gist.github.com/bdeterling/5563683. I haven't revisited it in about 4 years. – Trews 12/5, 2013 at 14:4

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();
    }

Sorus answered 12/11, 2010 at 15:15 Comment(1)

does you know how to get values of parameters in that query ?? – Leyden 29/3, 2015 at 20:22

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);

Demantoid answered 11/6, 2012 at 14:26 Comment(0)

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;

Unmoor answered 9/11, 2012 at 11:58 Comment(0)

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 && parameters.length>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 ");
}

Mccully answered 2/3, 2016 at 20:53 Comment(1)

Thanks for this code. There's however a little typo ("traslator" should be "translator"). And when replacing the CriteriaLoader constructor call with the one from @Demantoid ' s answer it'll also work with hibernate 3.6+ (tested with 4.1.9) – Alchemist 20/9, 2016 at 8:6

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.

Jacobsen answered 17/10, 2017 at 11:4 Comment(0)

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 && parameters.length > 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("<>",
        "!=").replaceAll("<", " < ").replaceAll(">", " > ");
}

Constringe answered 27/6, 2016 at 13:12 Comment(0)

Michael's answer is perfect, just the imports it is missing:

import java.lang.reflect.Field;

import org.hibernate.Criteria;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.Subqueries;
import org.hibernate.engine.spi.LoadQueryInfluencers;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.internal.CriteriaImpl;
import org.hibernate.internal.SessionImpl;
import org.hibernate.loader.OuterJoinLoader;
import org.hibernate.loader.criteria.CriteriaLoader;
import org.hibernate.persister.entity.OuterJoinLoadable;



  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);

Pauli answered 7/7, 2022 at 21:32 Comment(1)

May be not related but how to fit it for org.springframework.data.jpa.repository.Query

@Repository public interface EmployeeRepository extends JpaRepository<Employee, Long> {      @Query("select c from Employee c  where  c.login_time > :timeOfLogin")      public List<Employee> findEvasiveEmployees(@Param("timeOfLogin") LocalDateTime timeOfLogin);  }

– Edeline 15/5, 2023 at 1:56

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags