Java 如何从 Hibernate Criteria API 获取 SQL（*不* 用于日志记录）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/554481/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get SQL from Hibernate Criteria API (*not* for logging)
提问by David Bulté
is there an easy way to get the (to-be-generated) sql from a Hibernate Criteria?
有没有一种简单的方法可以从 Hibernate Criteria 中获取（要生成的）sql？
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)
然后的想法是将 sql 用作巨大的“MINUS”查询的一部分（我需要找到 2 个相同模式之间的差异 - 结构相同，而不是数据 - 并且 Hibernate 不支持 MINUS）
(BTW I know I can check the SQL from the log files)
（顺便说一句，我知道我可以从日志文件中检查 SQL）
采纳答案by Brian Deterling
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.
我已经使用 Spring AOP 完成了类似的操作，因此我可以获取应用程序中运行的任何查询的 sql、参数、错误和执行时间，无论是 HQL、Criteria 还是本机 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:
这显然是脆弱的、不安全的，容易因 Hibernate 等的变化而中断，但它说明了可以获得 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.
将整个内容包装在 try/catch 中，并自担风险使用。
回答by ramdane.i
Here's "another" way to get the SQL :
这是获取 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();
回答by LiamV
For those using NHibernate, this is a port of [ram]'s code
对于那些使用 NHibernate 的人，这是 [ram] 代码的一个端口
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();
}
回答by Michael
If you are using Hibernate 3.6 you can use the code in the accepted answer (provided by Brian Deterling) with slight modification:
如果您使用的是 Hibernate 3.6，则可以使用已接受的答案（由 Brian Deterling 提供）中的代码稍加修改：
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);
回答by Triqui
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;
回答by fformigli
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 ");
}
回答by Michael Capper
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:
该答案基于 user3715338 的答案（更正了一个小的拼写错误）并与 Michael 对 Hibernate 3.6 的答案混合 - 基于 Brian Deterling 接受的答案。然后我扩展了它（对于 PostgreSQL），用更多的类型替换了问号：
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(">", " > ");
}
回答by Steve Chambers
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:
对于希望在一行中执行此操作的任何人（例如，在显示/立即窗口、监视表达式或调试会话中的类似内容中），以下将执行此操作并“漂亮地打印”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 Criteriaobject 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 BasicFormatterImplto "pretty print" the HQL. If using a different version, see this answerfor how to modify. Or perhaps just remove the pretty printing entirely as it's just a "nice to have".
- It's using
getEnabledFiltersrather thangetLoadQueryInfluencers()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.
- 答案基于ramdane.i 发布的解决方案。
- 它假定Criteria对象名为
crit。如果命名不同，请搜索并替换。 - 它假设 Hibernate 版本晚于 3.3.2.GA 但早于 4.0，以便使用BasicFormatterImpl来“漂亮地打印”HQL。如果使用不同的版本，请参阅此答案以了解如何修改。或者也许只是完全删除漂亮的印刷品，因为它只是“很高兴拥有”。
- 它使用
getEnabledFilters而不是getLoadQueryInfluencers()为了向后兼容，因为后者是在更高版本的 Hibernate (3.5???) 中引入的 - 如果查询被参数化，它不会输出使用的实际参数值。
