如何计算多列的差异 [英] 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屋！