java 将 Group by 添加到无投影的 Hibernate Criteria-Query
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2035359/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add a Group by to a Hibernate Criteria-Query without Projection
提问by Kai Moritz
I have a Criteria-Query, which joins a second table B, to select entities from table A. The problem is, that this query returns some entities from table A multiple times. But I need the results to be distinct.
我有一个 Criteria-Query，它连接第二个表 B，从表 A 中选择实体。问题是，这个查询多次返回表 A 中的一些实体。但我需要结果是不同的。
Using Criteria.DISTINCT_ROOT_ENTITY is useless, becaus this filters out the multiple occurences after the SQL-Query was executed. So, when I limit my results to 20 hits, I end up with only 4, though there are more entries, that match my query.
使用 Criteria.DISTINCT_ROOT_ENTITY 是没有用的，因为这会在执行 SQL-Query 后过滤掉多次出现。因此，当我将结果限制为 20 个匹配项时，我最终只有 4 个匹配项，但与我的查询匹配的条目更多。
In pure SQL I simply can add a "GROUP BY ID" to the query and everything is fine, because the join of table B is only used, to select the entities from table A. But with the Criteria-API I cannot do this. The only way to add a "GROUP BY" is by using Projections. But then, I end up with scalar values, not with a real instance of my class. Using a SQL-restriction does not work either, because hibernate adds a bogous "1=1" after my "GROUP BY"-clause. :(
在纯 SQL 中，我只需将“GROUP BY ID”添加到查询中，一切都很好，因为仅使用表 B 的连接从表 A 中选择实体。但是使用 Criteria-API 我不能这样做。添加“GROUP BY”的唯一方法是使用投影。但是，我最终得到的是标量值，而不是我的类的真实实例。使用 SQL 限制也不起作用，因为 hibernate 在我的“GROUP BY”子句之后添加了一个虚假的“1=1”。:(
Any ideas?
有任何想法吗？
回答by user748954
Have you tried to use something like this?
你有没有试过使用这样的东西？
ICriteria criteria = dc.GetExecutableCriteria(RepositoryInstance.Session)
.SetProjection(Projections.distinct(Projections.projectionList()
.add(Projections.property("Prop1"), "Prop1")
.add(Projections.property("Prop2"), "Prop2")
.add(Projections.property("Prop3"), "Prop3")
.add(Projections.property("Prop4"), "Prop4")));
result = criteria.List();
You can dynamically add properties through reflection of the class.
您可以通过类的反射动态添加属性。
This creates SQl like this: select distinct prop1,prop2,prop3,prop4 from yourClass
这会像这样创建 SQL： select distinct prop1,prop2,prop3,prop4 from yourClass
I did not include DetachedCriteria dcsince that is irrelevant.
我没有包括，DetachedCriteria dc因为那是无关紧要的。
回答by PHP Avenger
GROUP BY WITHOUT PROJECTION:Its not possible as it make sense, in many answers you may found, But most people don't want to use projection, because it require them to project each and every attribute, but requirement is that a bean must be projected. (and returned as a result). In example below I have tried to projectthe required beanas resultant object.
GROUP BY WITHOUT PROJECTION：这是不可能的，因为它有道理，在您可能会发现的许多答案中，但大多数人不想使用投影，因为它要求他们投影每个属性，但要求是 bean 必须是预计。（并作为结果返回）。在下面的示例中，我尝试project将所需的bean对象作为结果对象。
I have achieved the same result with a little bit of trick I believe, First I was trying to apply group by without projection but I have found no solution, so I have to rely on Projection.
我相信我通过一些技巧取得了相同的结果，首先我试图在没有投影的情况下应用 group by 但我没有找到解决方案，所以我不得不依赖投影。
Here is what I wanted to achieve
这是我想要实现的
select p.* FROM parent p INNER JOIN child c ON p.id_parent=c.id_father
WHERE c.child_name like '%?%' AND p.parent_name like '%?%'
group by p.id_parent
In Java code I wanted p.*to be a Parentclass which is my entity bean and I wanted it be unique, one way is get the result list in a Set, but i dont like this way due many reasons :)
在 Java 代码中，我想p.*成为一个Parent类，它是我的实体 bean 并且我希望它是唯一的，一种方法是在 Set 中获取结果列表，但由于多种原因我不喜欢这种方式:)
So I created a Criteria from Child.classinstead of Parent.class, and this trick worked for me.
所以，我创建了一个标准的Child.class，而不是Parent.class和这一招对我来说有效。
Criteria c = session.createCriteria(Child.class,"c");// starting from Child
c.add(Restrictions.like("childName", "abc", MatchMode.ANYWHERE));
c.createAlias("parent", "p"); //remember parent is an attribute in Child.class
c.add(Restrictions.like("p.parentName", "xyz", MatchMode.ANYWHERE));
c.setProjection( Projections.projectionList().add(Projections.groupProperty("parent"))); //projecting parent which is an attribute of Child.class
List<Parent> result = c.list(); //get the result
for (Parent p: result) {
System.out.println(p);
}
If you still haven't got the idea here are my mapped Entity Bean classes.
如果您仍然不明白这里是我映射的实体 Bean 类。
package com.mazhar.beans;
import static javax.persistence.GenerationType.IDENTITY;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@Entity
@Table(name = "parent")
public class Parent {
private Integer idParent;
private String parentName;
private List<Child> childs;
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id_parent")
public Integer getIdParent() {
return idParent;
}
public void setIdParent(Integer idParent) {
this.idParent = idParent;
}
@Column(name = "parent_name")
public String getParentName() {
return parentName;
}
public void setParentName(String parentName) {
this.parentName = parentName;
}
@OneToMany(fetch=FetchType.LAZY, mappedBy="parent", cascade=CascadeType.ALL)
public List<Child> getChilds() {
return childs;
}
public void setChilds(List<Child> childs) {
this.childs = childs;
}
}
and my child class
和我的孩子班
package com.mazhar.beans;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
@Entity
@Table(name = "child")
public class Child {
private Integer idChild;
private String childName;
private Parent parent; //this actually we projected in criteria query.
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id_city", unique = true, nullable = false)
public Integer getIdChild() {
return idChild;
}
public void setIdChild(Integer idChild) {
this.idChild = idChild;
}
@Column(name = "city_name", nullable = false)
public String getChildName() {
return childName;
}
public void setChildName(String cName) {
this.childName = cName;
}
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "id_father")
public Parent getParent() {
return parent;
}
public void setParent(Parent parent) {
this.parent = parent;
}
}
回答by Renato Oliveira
The main problem of grouping by without projecting, in the way you want, is that in some DBMS like Oracle it will not work, the Oracle will return an error.
以您想要的方式进行分组而不进行投影的主要问题是，在像 Oracle 这样的某些 DBMS 中它不起作用，Oracle 将返回错误。
If you group a select, you have to group by all non-aggregation fields that you are selecting. The MySQL for example don't have this restriction.
如果您对选择进行分组，则必须按您选择的所有非聚合字段进行分组。例如 MySQL 没有这个限制。
The approach that i've been using is selecting only the id as groupProperty projection with all filters, orderings and number of results limit. Then a execute other query filtering with these retrieved ids. That way the implementation will be independent of the DBMS.
我一直在使用的方法是仅选择 id 作为 groupProperty 投影，并带有所有过滤器、排序和结果数量限制。然后使用这些检索到的 id 执行其他查询过滤。这样，实现将独立于 DBMS。
回答by alasdairg
It is possible to write actual SQL Queries which Hibernate can use to return entities. So if you really need to, you can bypass HQL and write exactly the query you want with your GROUP BY in it.
可以编写实际的 SQL 查询，Hibernate 可以使用它来返回实体。因此，如果您确实需要，您可以绕过 HQL 并在其中使用 GROUP BY 准确编写您想要的查询。
See herefor details.
有关详细信息，请参见此处。
For example you can define a query something like this in your hbm.xml file:
例如，您可以在 hbm.xml 文件中定义类似这样的查询：
<sql-query name="exampleQuery">
<query-param name="param" type="int"/>
<return alias="x" class="foo.bar.X"/>
<return alias="y" class="foo.bar.Y"/>
<![CDATA[
select {x.*}, {y.*}
from XEntity x
inner join YEntity y on y.xId = x.id
where y.value = :param
]]>
</sql-query>
Note the {x.} and {y.} shorthand syntax for selecting all the properties of entity X and entity Y
注意 {x. } 和 {y. 用于选择实体 X 和实体 Y 的所有属性的简写语法
