在JPA 2 Criteria API中选择DISTINCT + ORDER BY [英] SELECT DISTINCT + ORDER BY in JPA 2 Criteria API

问题描述

我有一个诉讼，其中包含一个列表< Hearing> ，每个都有一个日期属性。

I've a class Lawsuit, that contains a List<Hearing>, each one with a Date attribute.

我需要选择所有诉讼 s按照听证会的日期排序 s

I need to select all the Lawsuits ordered by the date of their Hearings

我有一个CriteriaQuery，如

I've a CriteriaQuery like

CriteriaBuilder           cb = em.getCriteriaBuilder();
CriteriaQuery<Lawsuit>    cq = cb.createQuery(Lawsuit.class);
Root<Lawsuit>           root = cq.from(Lawsuit.class);

我使用 distinct 来平展结果：

cq.select(root).distinct(true);

然后加入 诉讼 听证会

Join<Lawsuit, Hearing> hearing = root.join("hearings", JoinType.INNER);

创建谓词 s

predicateList.add(cb.isNotNull(hearing.<Date>get("date")));

和订单 s：

orderList.add(cb.asc(hearing.<Date>get("date")));

如果我避免 distinct ，一切正常但如果我使用它，它会抱怨无法根据SELECT中没有的字段进行订购：

Everything works fine if I avoid distinct, but if I use it, it complains about not being able to order based on fields that are not in the SELECT:

引起：org .postgresql.util.PSQLException：错误：对于 SELECT DISTINCT ， ORDER BY 表达式必须出现在选择列表中

Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

列表< Hearing> 已经可以通过诉讼返回的课程，所以我很困惑：我应该如何将它们添加到选择列表？

The List<Hearing> is already accessible through the Lawsuit classes returned, so I'm confused: how should I add them to the select list ?

推荐答案

我已经在其他地方发现了问题的根源，而解决问题则没有必要去做问题中的问题;如其他答案所述
，此处不必执行 distinct 。

I've discovered the source of the problem somewhere else, and solving it has made unnecessary to do what asked in the question; as described in other answers, it should be unnecessary to perform the distinct here.

重复行是由对集合（根对象的属性）执行的错误左连接发起的，即使未使用谓词：

The duplicate rows were originated by erroneous left joins that were performed on collections (attributes of the root object) even if the predicates were not been used:

Join<Lawsuit, Witness> witnesses = root.join("witnesses", JoinType.LEFT);
if (witnessToFilterWith!=null) {
    predicateList.add(cb.equal(witnesses.<Long>get("id"),witnessToFilterWith.getId()));
}

加入应该显然可以内部和仅在需要时执行：

if (witnessToFilterWith!=null) {
    Join<Lawsuit, Witness> witnesses = root.join("witnesses", JoinType.INNER);
    predicateList.add(cb.equal(witnesses.<Long>get("id"),witnessToFilterWith.getId()));
}

所以，如果你在这里是因为你遇到了同样的问题， 在联接中搜索问题。

So, if you're here because you're getting the same problem, search the problem in the joins.

这篇关于在JPA 2 Criteria API中选择DISTINCT + ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！