Reputation: 3109
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)
Upvotes: 85
Views: 136756
Reputation: 1732
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);
Upvotes: 1
Reputation: 39464
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:
crit
. If named differently, do a search and replace.getEnabledFilters
rather than getLoadQueryInfluencers()
for backwards compatibility since the latter was introduced in a later version of Hibernate (3.5???)Upvotes: 2
Reputation: 731
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;
Upvotes: 4
Reputation: 91
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(">", " > ");
}
Upvotes: 1
Reputation: 31
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 ");
}
Upvotes: 3
Reputation: 654
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();
Upvotes: 51
Reputation: 2510
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);
Upvotes: 8
Reputation: 1108
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();
}
Upvotes: 11
Reputation: 13734
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.
Upvotes: 41