在这种情况下如何使用DISTINCT创建hibernate条件 [英] How to create hibernate criteria with DISTINCT in this case

问题描述

  criteria.addOrder（Order.asc（property）.ignoreCase（ ））。setProjection（
 Projections.distinct（Projections.property（name）））; 
  

生成类似于以下的sql：

 从table1中选择DISTINCT名称order by lower（name）asc; 
  

当这个sql被执行时出现以下错误：

 错误：对于SELECT DISTINCT，ORDER BY表达式必须出现在选择列表中
  

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

1. 使用如下的嵌套选择：
   
   

   select name from（从table1中选择不同的名称）as aliasname order by lower（name）asc;

$



从table1顺序中选择DISTINCT lower（name）。b $ b

2. 在select和order by子句中使用lower函数。 by lower（name）asc;

解决方案进入休眠端。如何创建一个标准，我可以得到任何上述两个sqls。

• 如何创建条件以获取嵌套select 。
  

• 如何从条件中确保按顺序使用的函数必须在select中应用

  
  
  
  
  
  • 在hibernate或DB方面是更好的解决方案（不使用硬编码sql，createQuery（）或HQL ）。
    

感谢您的建议。 div>

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

  String [] columnAlias = { 名称}; 
 criteria.addOrder（Order.asc（name）。ignoreCase（））。setProjection（Projections.distinct（
 Projections.sqlProjection（lower（+ criteria.alias +_.name） as name，columnAlias，
 Hibernate.STRING as Type []）））; 
  

这应该产生以下sql：

 从table1中选择不同的lower（this_.name）作为名称this_ order by lower（this_.name）asc; 
  

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

I am using hibernate criteria to create postgres query:

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

which generates sql similar to below:

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

and when this sql gets executed gives below error:

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

I know this is something against the postgres rule, but when i looked into the error, i found that we should not use any function at order by clause. If its used then we need to have two work arounds:

1. use nested select as below:

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

2. use lower function in both select and order by clause.

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

So for the fix, I will prefer a solution into hibernate side. how to create a criteria such that i can get any of the above two sqls.

• how to create criteria to get nested select.

• how ensure from criteria that the function used in order by must be applied in select also

  • is ther any better solution on hibernate or DB side (without using hardcoded sql, createQuery() or HQL).

thanks for your suggetions in advance.

解决方案

I recently ran into the same problem and ended up using SQLProjection. I realize that this post is a couple of years old, but I haven't found any other posts with solutions that either work or don't require rewriting the entire query with hardcoded sql, so hopefully this will help someone else who runs into this issue. This solution still requires some hardcoded sql, but it is minimal.

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[])));

This should produce the following sql:

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

Note: unless otherwise specified, criteria.alias is set to 'this' by default.

这篇关于在这种情况下如何使用DISTINCT创建hibernate条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！