如何使用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 tableDoing 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.
更多推荐
如何计算多列的差异
发布评论