如何计算多列的差异 [英] How to countDistinct on multiple columns
问题描述
如何使用JPA标准API执行以下操作:
select table(distinct column1,column2)from table
使用CriteriaBuilder.countDistinct在一个列/路径上执行此操作很简单,但我该如何执行此操作两个路径/列?
这是一个迟到的答案:-)虽然我不确定事情是否发生了变化。 / p>
最近我遇到了同样的需求,并且使用concat解决了它,即通过将列连接到伪列,然后 countDistinct
在伪列上。
但我无法使用 criteriaBuilder.concat
,因为它使用 ||
为连接生成JPQL, Hibernate遇到了的麻烦。
幸运的是 @Formula
,因此,我将伪列映射到 @Formula
字段:
$ b
@Entity
public class MyEntity {
@Column(name =col_a)
private String colA;
@Column(name =col_b)
private String colB;
@Formula(concat(col_a,col_b))//< = THE TRICK
private String concated;
}
这样我终于可以使用 code> CriteriaBuilder.countDistinct
:
code> // ...
表达式<?> exp = criteriaBuilder.countDistinct(entity.get(concated));
criteriaQuery.select(exp);
TypedQuery< Long> query = entityManager.createQuery(criteriaQuery);
返回query.getSingleResult();
我希望JPA会(或者希望已经)支持 countDistinct
有多列,那么所有这些混乱都可以避免。
How can I, using the JPA criteria API do the following:
select count(distinct column1, column2) from table
Doing this on one column/path is simple using CriteriaBuilder.countDistinct, but how can I do this on two paths/columns?
Here is a late answer :-) though I'm not sure if things had changed.
Recently I encountered the very same need, and worked around it using concat, i.e., by concatenating the columns into a pseudo column, then countDistinct
on the pseudo column.
But I couldn't use criteriaBuilder.concat
because it generated JPQL using ||
for the concatenation, which Hibernate had trouble with.
Fortunately there's @Formula
, thus, I mapped the pseudo column to a field with @Formula
:
@Entity
public class MyEntity {
@Column(name="col_a")
private String colA;
@Column(name="col_b")
private String colB;
@Formula("concat(col_a, col_b)") // <= THE TRICK
private String concated;
}
This way I can finally use the concated
field for CriteriaBuilder.countDistinct
:
//...
Expression<?> exp = criteriaBuilder.countDistinct(entity.get("concated"));
criteriaQuery.select(exp);
TypedQuery<Long> query = entityManager.createQuery(criteriaQuery);
return query.getSingleResult();
I wish JPA would (or hopefully already) support countDistinct
with multiple columns, then all these mess could have been avoided.
这篇关于如何计算多列的差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!