Hibernate中Projections.rowCount()在SQL Server带Order by时报错，Oracle正常

近期将数据库从Oracle迁移至SQL Server，应用使用Hibernate v5.x。代码中先通过Criteria查询带分页、条件及排序的ABC列表，之后复用该Criteria设置Projections.rowCount()查询总结果数时，SQL Server报错：Column "ABC.END_DATE" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause。生成的SQL包含count(*)及原排序语句，此前在Oracle中运行正常，使用的是SQLServer2012Dialect。

代码片段 #

Criteria criteria = session.createCriteria(ABC.class);

setResultPaging(criteria, 1, 100);

// 添加所有查询条件
criteria.add(Restrictions.eq("ABCId", ABCSearch.getId()));
...
// 所有条件添加完毕，添加排序规则

criteria.addOrder(Property.forName("endDate").desc());
criteria.addOrder(Property.forName("startDate").asc());
criteria.addOrder(Property.forName("ABCId").asc());

List<ABC> ABCList = criteria.list();

if (ABCList.size() > 0) {
    criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
    setResultPaging(criteria, 0, 1);
    searchResult.setTotalResults(CountTranslate.translateCount(criteria.list())); // 报错位置
}

// CountTranslate.translateCount() 用于将criteria.list()转换为Integer类型

错误栈 #

Column "ABC.END_DATE" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
2023-07-16 08:49:38,051 WARN  [services-pool housekeeper] : services-pool - Thread starvation or clock leap detected (housekeeper delta=1m20s409ms434µs600ns).
2023-07-16 08:49:41,474 ERROR [RMI TCP Connection(14)-192.168.43.237] : New DAOException -
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
...

生成的SQL #

select count(*) as y0_ 
from ABC this_

inner join ABC_REQUEST abcreques1_ 
on this_.ABC_REQUEST_ID=abcreques1_.ABC_REQUEST_ID

where 
abcreques1_.FORM_TYPE=? and 
this_.ABC_STATUS=? and 
this_.ARCHIVED_FLAG=?

order by 
this_.END_DATE desc, 
this_.START_DATE asc, 
this_.ABC_ID asc 
offset 0 rows fetch next ? rows only

问题原因 #

1. SQL Server语法规则限制：SQL Server对聚合查询（如count(*)）的语法检查更严格，要求ORDER BY子句中的列必须包含在聚合函数或GROUP BY子句中；而Oracle对这种场景的限制更宽松，允许聚合查询保留未参与聚合/分组的排序字段。
2. Criteria复用导致排序规则残留：你在查询列表时给Criteria添加了排序条件，复用同一个对象统计总数时，没有清除这些排序规则，导致生成的SQL带着ORDER BY语句，触发SQL Server的语法错误。

解决办法 #

1. 统计前清除排序规则 #

在设置Projection之前，调用clearOrderings()清除之前添加的所有排序条件：

if (ABCList.size() > 0) {
    criteria.clearOrderings(); // 移除所有排序规则
    criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
    setResultPaging(criteria, 0, 1);
    searchResult.setTotalResults(CountTranslate.translateCount(criteria.list()));
}

2. 新建独立的Criteria统计总数 #

避免复用同一个Criteria，为统计总数单独创建新对象，只复制必要的查询条件，不添加排序（统计总数不需要排序）：

List<ABC> ABCList = criteria.list();

if (ABCList.size() > 0) {
    // 创建统计用的新Criteria
    Criteria countCriteria = session.createCriteria(ABC.class);
    // 复制原查询的所有条件
    countCriteria.add(Restrictions.eq("ABCId", ABCSearch.getId()));
    // 其他查询条件也要逐一复制...
    
    countCriteria.setProjection(Projections.rowCount());
    searchResult.setTotalResults(CountTranslate.translateCount(countCriteria.list()));
}

这种方式逻辑更清晰，能避免复用对象带来的意外副作用，同时统计总数不需要设置分页，可以去掉setResultPaging调用。

3. 使用ScrollableResults一次获取列表和总数（可选） #

如果不想复制查询条件，可以用ScrollableResults一次查询同时获取列表数据和总数，避免二次查询：

ScrollableResults scroll = criteria.scroll(ScrollMode.FORWARD_ONLY);
scroll.last();
int totalCount = scroll.getRowNumber() + 1; // 获取总行数
scroll.beforeFirst();
List<ABC> ABCList = scroll.list(); // 获取分页列表

searchResult.setTotalResults(totalCount);

注意：数据量极大时，这种方式的性能可能不如单独统计总数高效，需根据实际场景选择。

内容的提问来源于stack exchange，提问作者NonExpertDeveloper