java Projections.count() 和 Projections.countDistinct() 都产生相同的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2318952/
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
Projections.count() and Projections.countDistinct() both result in the same query
提问by Kim L
EDIT: I've edited this post completely, so that the new description of my problem includes all the details and not only what I previously considered relevant. Maybe this new description will help to solve the problem I'm facing.
编辑：我已经完全编辑了这篇文章，以便对我的问题的新描述包括所有细节，而不仅仅是我以前认为相关的内容。也许这个新的描述将有助于解决我面临的问题。
I have two entity classes, Customer and CustomerGroup. The relation between customer and customer groups is ManyToMany. The customer groups are annotated in the following way in the Customer class.
我有两个实体类，Customer 和 CustomerGroup。客户和客户组之间的关系是多对多。客户组在 Customer 类中按以下方式注释。
@Entity
public class Customer {
...
@ManyToMany(mappedBy = "customers", fetch = FetchType.LAZY)
public Set<CustomerGroup> getCustomerGroups() {
...
}
...
public String getUuid() {
return uuid;
}
...
}
The customer reference in the customer groups class is annotated in the following way
客户组类中的客户引用按如下方式标注
@Entity
public class CustomerGroup {
...
@ManyToMany
public Set<Customer> getCustomers() {
...
}
...
public String getUuid() {
return uuid;
}
...
}
Note that both the CustomerGroup and Customer classes also have an UUID field. The UUID is a unique string (uniqueness is not forced in the datamodel, as you can see, it is handled as any other normal string).
请注意， CustomerGroup 和 Customer 类也有一个 UUID 字段。UUID 是一个唯一的字符串（数据模型中没有强制要求唯一性，正如您所看到的，它与任何其他普通字符串一样处理）。
What I'm trying to do, is to fetch all customers which do not belong to any customer group OR the customer group is a "valid group". The validity of a customer group is defined with a list of valid UUIDs.
我想要做的是获取不属于任何客户组或客户组是“有效组”的所有客户。客户组的有效性由有效 UUID 列表定义。
I've created the following criteria query
我创建了以下条件查询
Criteria criteria = getSession().createCriteria(Customer.class);
criteria.setProjection(Projections.countDistinct("uuid"));
criteria = criteria.createCriteria("customerGroups", "groups", Criteria.LEFT_JOIN);
List<String> uuids = getValidUUIDs();
Criterion criterion = Restrictions.isNull("groups.uuid");
if (uuids != null && uuids.size() > 0) {
criterion = Restrictions.or(criterion, Restrictions.in(
"groups.uuid", uuids));
}
criteria.add(criterion);
When executing the query, it will result in the following SQL query
执行查询时，会产生如下SQL查询
select
count(*) as y0_
from
Customer this_
left outer join
CustomerGroup_Customer customergr3_
on this_.id=customergr3_.customers_id
left outer join
CustomerGroup groups1_
on customergr3_.customerGroups_id=groups1_.id
where
groups1_.uuid is null
or groups1_.uuid in (
?, ?
)
The query is exactly what I wanted, but with one exception. Since a Customer can belong to multiple CustomerGroups, left joining the CustomerGroup will result in duplicated Customer objects. Hence the count(*)will give a false value, as it only counts how many results there are. I need to get the amount of unique customers and this I expected to achieve by using the Projections.countDistinct("uuid");-projection. For some reason, as you can see, the projection will still result in a count(*)query instead of the expected count(distinct uuid). Replacing the projection countDistinctwith just count("uuid")will result in the exactly same query.
查询正是我想要的，但有一个例外。由于一个 Customer 可以属于多个 CustomerGroups，离开加入 CustomerGroup 将导致重复的 Customer 对象。因此count(*)将给出一个错误值，因为它只计算有多少结果。我需要获得唯一客户的数量，这是我希望通过使用Projections.countDistinct("uuid");-projection实现的。出于某种原因，如您所见，投影仍将导致count(*)查询而不是预期的count(distinct uuid). countDistinct用 just替换投影count("uuid")将导致完全相同的查询。
Am I doing something wrong or is this a bug?
我做错了什么还是这是一个错误？
===
===
"Problem" solved. Reason: PEBKAC (Problem Exists Between Keyboard And Chair). I had a branch in my code and didn't realize that the branch was executed. That branch used rowCount() instead of countDistinct().
“问题解决了。原因：PEBKAC（键盘和椅子之间存在问题）。我的代码中有一个分支，但没有意识到该分支已被执行。该分支使用 rowCount() 而不是 countDistinct()。
回答by OxUlsen
Thre is bug in 3.5.1
在 3.5.1 中存在错误
After
后
cr.setProjection(Projections.countDistinct("memberId"));
cr.setProjection(Projections.countDistinct("memberId"));
SQL result is count(memberId)
SQL 结果是 count(memberId)
bo not count(distinct memberId)
bo 不算（不同的 memberId）
after debug ....
调试后....
public final class Projections {
public static CountProjection countDistinct(String propertyName) {
return new CountProjection(propertyName).setDistinct();
}
distinct is noticed but not used.
distinct 被注意到但没有被使用。
public class CountProjection extends AggregateProjection {
private boolean distinct;
protected CountProjection(String prop) {
super("count", prop);
}
public String toString() {
if ( distinct ) {
return "distinct " + super.toString();
}
else {
return super.toString();
}
}
public CountProjection setDistinct() {
distinct = true;
return this;
}
}
and ... in AggregateProjection only getFunctionName() = functionName = "count" is used !!!
和 ... 在 AggregateProjection 中只使用 getFunctionName() = functionName = "count" ！！！
public class AggregateProjection extends SimpleProjection {
公共类 AggregateProjection 扩展 SimpleProjection {
protected AggregateProjection(String functionName, String propertyName) {
this.functionName = functionName;
this.propertyName = propertyName;
}
public String getFunctionName() {
return functionName;
}
public String getPropertyName() {
return propertyName;
}
...
public String toSqlString(Criteria criteria, int loc, CriteriaQuery criteriaQuery)
throws HibernateException {
final String functionFragment = getFunction( criteriaQuery ).render(
buildFunctionParameterList( criteria, criteriaQuery ),
criteriaQuery.getFactory()
);
return functionFragment + " as y" + loc + '_';
}
protected SQLFunction getFunction(CriteriaQuery criteriaQuery) {
return getFunction( getFunctionName(), criteriaQuery );
}
回答by Kim L
"Problem" solved. Reason: PEBKAC (Problem Exists Between Keyboard And Chair). I had a branch in my code and didn't realize that the branch was executed. That branch used rowCount() instead of countDistinct().
“问题解决了。原因：PEBKAC（键盘和椅子之间存在问题）。我的代码中有一个分支，但没有意识到该分支已被执行。该分支使用 rowCount() 而不是 countDistinct()。
回答by Stijn Geukens
try debugging the method toSqlString in class CountProjection. Looking at the code I can only image that distinct was not true; I see no other reason why the distinct would not be included:
尝试调试 CountProjection 类中的 toSqlString 方法。看着代码，我只能想象那不是真的；我看不出为什么不包括 distinct 的其他原因：
public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery)
throws HibernateException {
StringBuffer buf = new StringBuffer();
buf.append("count(");
if (distinct) buf.append("distinct ");
return buf.append( criteriaQuery.getColumn(criteria, propertyName) )
.append(") as y")
.append(position)
.append('_')
.toString();
}
regards,
Stijn
问候，
斯蒂恩
回答by Fortega
I guess this is because your 'id' field is a unique field, and hibernate knows that. Does the query give a different result when you use 'distinct'?
我想这是因为您的“id”字段是一个独特的字段，而 hibernate 知道这一点。当您使用“distinct”时，查询是否会给出不同的结果？
回答by Thomas L?tzer
Maybe the field ID is defined as unique anyway? Try using a different field, which is not unique.
也许字段 ID 被定义为唯一的？尝试使用不同的字段，它不是唯一的。
