如何在 Hibernate Criteria API 中添加 SQL-Server 查询提示

Question

我们正在使用 MS SQL-Server 和 Hibernate Criteria API。

最近我发现一个查询受到参数嗅探的影响，所以我想添加一个OPTION (RECOMPILE)as 查询提示。但是虽然 Hibernate 似乎支持Criteria API的查询提示，但添加的

criteria.addQueryHint("OPTION (RECOMPILE)");

似乎没有任何影响（记录的 SQL 不包含任何提示）。

有任何想法吗？

Answer 1

似乎 Hibernate Criteria API 只支持 Oracle 数据库的查询提示。我找到的最好的解决方案是自己实现对 SQL-Server 的支持（您也可以做类似的事情，criteria.add(Restrictions.sqlRestriction("1=1 OPTION (RECOMPILE) "));但这几乎是一种黑客行为，如果您例如想向查询添加排序则不起作用）。我使用 Oracle 的 Hibernate 实现作为蓝图（看看Oracle8iDialect）。对于我的用例，实现一个将查询提示附加到查询末尾的版本就足够了（因为OPTION-clause 总是在查询的末尾）。package de.mystuff.hibernate;import java.util.List;import org.hibernate.annotations.common.util.StringHelper;import org.hibernate.dialect.SQLServer2008Dialect;/**&nbsp;* Special version of {@link SQLServer2008Dialect} which adds a simple query hint support.&nbsp;*/public class MySQLServer2012Dialect extends org.hibernate.dialect.SQLServer2012Dialect {&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* {@inheritDoc}&nbsp; &nbsp; &nbsp;* <p>&nbsp; &nbsp; &nbsp;* Currently this is a pretty simple query hint implementation. It just concatenates all SQL hints and adds them to the end of the query. This is fine for&nbsp; &nbsp; &nbsp;* e.g. {@code OPTION (RECOMPILE)}.&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; @Override&nbsp; &nbsp; public String getQueryHintString(String sql, List<String> hints) {&nbsp; &nbsp; &nbsp; &nbsp; if (hints.isEmpty()) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // no query hints at all&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return sql;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // concatenate all hints&nbsp; &nbsp; &nbsp; &nbsp; final String hint = StringHelper.join(", ", hints.iterator());&nbsp; &nbsp; &nbsp; &nbsp; if (StringHelper.isEmpty(hint)) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // all query hints are empty&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return sql;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return sql + " " + hint;&nbsp; &nbsp; }}提醒您必须告诉 Hibernate 使用支持方言的查询提示：hibernate.dialect=de.mystuff.hibernate.MySQLServer2012Dialect