java - 在这种情况下如何使用 DISTINCT 创建 hibernate 条件

Question

我正在使用 hibernate 条件来创建 postgres 查询:

criteria.addOrder(Order.asc(property).ignoreCase()).setProjection(
                            Projections.distinct(Projections.property("name")));

生成类似于下面的sql:

select DISTINCT name from table1 order by lower( name) asc;

当执行此 sql 时会出现以下错误:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

我知道这违反了 postgres 规则，但是当我查看错误时，我发现我们不应该在 order by 子句中使用任何函数。如果使用它，那么我们需要有两个解决方法:

1. 使用嵌套选择如下:

   select name from (select distinct name from table1) 作为别名 order by lower(name) asc;

2. 在 select 和 order by 子句中使用 lower 函数。

   select DISTINCT lower(name) from table1 order by lower(name) asc;

所以对于修复，我更喜欢 hibernate 方面的解决方案。如何创建条件以便我可以获得上述两个 sql 中的任何一个。

• 如何创建条件以获得嵌套选择。

• 如何根据标准确保在 order by 中使用的函数也必须在 select 中应用

  • 在 hibernate 或数据库方面是否有更好的解决方案(不使用硬编码 sql、createQuery() 或 HQL)。

提前感谢您的建议。

Answer 1

我最近遇到了同样的问题并最终使用了 SQLProjection .我意识到这篇文章已经有几年了，但我还没有找到任何其他帖子的解决方案可以工作或不需要用硬编码的 sql 重写整个查询，所以希望这能帮助遇到这个问题的其他人问题。此解决方案仍然需要一些硬编码的 sql，但它是最小的。

String[] columnAlias = {"name"};
criteria.addOrder(Order.asc("name").ignoreCase()).setProjection(Projections.distinct(
Projections.sqlProjection("lower(" + criteria.alias + "_.name) as name", columnAlias,
Hibernate.STRING as Type[])));

这应该产生以下 sql:

select distinct lower(this_.name) as name from table1 this_ order by lower(this_.name) asc;

注意:除非另有说明，否则 criteria.alias 默认设置为 'this'。