JPA对接SQL Server执行带ORDER BY的COUNT查询报列无效错误

问题根因 #

SQL Server 对SQL语法的校验比MySQL更严格，聚合查询（此处为COUNT查询）中如果携带ORDER BY子句，ORDER BY用到的字段必须包含在聚合函数或者GROUP BY子句中。而COUNT查询的结果是单条计数值，排序不会对结果产生任何影响，反而会额外消耗性能。本次报错的核心原因是FilterBuilder生成的JPQL默认拼接了全局的ORDER BY A.update_time DESC子句，被带到了COUNT查询的最终执行SQL中。

JPA代码层面修复方案 #

方案1：构建COUNT查询时，移除FilterBuilder中的ORDER BY子句 #

你可以在FilterBuilder中新增专门生成无排序JPQL的方法，或者在生成COUNT查询用的JPQL时，主动排除ORDER BY部分，示例修改如下：

FilterBuilder builder = new FilterBuilder("FROM RuleChangeLog A  ");
builder.startWhere();
builder.checkAndAdd1(filters[0], "and A.action LIKE ?", filters[0] );
builder.checkAndAdd1(filters[1], "and A.ruleName like ?", filters[1] );
builder.checkAndAdd1(filters[2], "and A.updatedBy like ?", filters[2] );
// 生成COUNT查询用的JPQL时，仅保留FROM和WHERE部分，剔除ORDER BY
String countJpql = "select count(A) " + builder.getJpqlWithoutOrder();
TypedQuery<Long> countQuery = em.createQuery(countJpql, Long.class);
builder.setParameters(countQuery);

如果你的FilterBuilder没有现成的剔除ORDER BY的方法，也可以手动截取JPQL字符串去掉ORDER BY之后的内容：

String originalJpql = builder.getJPQL();
String jpqlWithoutOrder = originalJpql.contains("ORDER BY") ? originalJpql.split("ORDER BY")[0] : originalJpql;
TypedQuery<Long> countQuery = em.createQuery("select count(A) " + jpqlWithoutOrder, Long.class);

方案2：动态控制FilterBuilder是否添加排序 #

如果你的FilterBuilder支持配置排序规则，在构建COUNT查询对应的FilterBuilder时，不传入排序参数，避免ORDER BY子句被拼接到JPQL中即可。

两种方案都可以从代码层面解决问题，无需修改数据库配置，同时还能优化COUNT查询的执行性能。

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