I've a class Lawsuit
, that contains a List<Hearing>
, each one with a Date
attribute.
I need to select all the Lawsuit
s ordered by the date of their Hearing
s
I've a CriteriaQuery like
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Lawsuit> cq = cb.createQuery(Lawsuit.class);
Root<Lawsuit> root = cq.from(Lawsuit.class);
I use distinct to flatten the results:
cq.select(root).distinct(true);
I then join Lawsuit
with Hearing
Join<Lawsuit, Hearing> hearing = root.join("hearings", JoinType.INNER);
to create Predicate
s
predicateList.add(cb.isNotNull(hearing.<Date>get("date")));
and Order
s:
orderList.add(cb.asc(hearing.<Date>get("date")));
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:
Caused by: org.postgresql.util.PSQLException: ERROR: for
SELECT DISTINCT
,ORDER BY
expressions must appear in select list
The List<Hearing>
is already accessible through the Lawsuit
classes returned, so I'm confused: how should I add them to the select list ?
Criteria Queries in JPA include various methods to select single entity, single value, multiple values from same entity or different entities and aggregate functions. JPA Criteria API provides the following options for the selection of values. CriteriaQuery.select method. CriteriaQuery.multiselect method.
JPA Criteria API - Distinct clause in Criteria API [Last Updated: Aug 13, 2018] Previous Page Next Page In Criteria API distinctclause can be applied by using following method of CriteriaQuery: CriteriaQuery<T> distinct(boolean distinct); Quick example:
JPA Criteria SELECT Clause. The SELECT clause is used to fetch the data from database. The data can be retrieved in the form of single expression or multiple expressions. In Criteria API, each form is expressed differently. Generally, select() method is used for the SELECT clause to fetch all type of forms.
JPA - Criteria API. The Criteria API is a predefined API used to define queries for entities. It is the alternative way of defining a JPQL query. These queries are type-safe, and portable and easy to modify by changing the syntax.
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 join
s 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()));
}
The join
should obviously be performed as inner
and only if needed:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With