JPA criteria filter by related entity without join

Question

I want to know how to use JPA Criteria API to filter entity by related entity's foreign key.

Let's say i have two entities as the following:

public class Employee {
    @Id
    private Long id;
    ...
    @ManyToOne
    private Department department;
    ...
}

public class Department {
    @Id
    private Long id;
}

I want to query the employees under departments of ids (1,2,3).

I was able to do that using Hibernate's depricated criteria, And want to know how to do it using the JPA Criteria predicate without join (root.join). It is logical that i don't need any join or subquery, as the desired result can be fetched from one table:

select e.* from employee e where e.department_id in (1,2,3)

** Update **

My problem was - as new for JPA Criteria and coming from the deprecated Hibernate Criteria - that I've used all APIs from the CriteriaBuilder such as (equal, notEqual, isNull, like, .....); And ,thus , used the CriteriaBuilder.In(experssion).in(values). But, as shown in the answer of @frank, I figured out that for the IN usage, I'll use Root.get(<attr>).in(<values>)

The CriteriaBuilder.in also can be used but differently:

In<Object> inClause = criteriaBuilder.in(root.get(<attr>);
for (Long id : ids) {
    inClause.value(id);
}

But, Of-course, the first solution is easier.

score 1 · Accepted Answer · answered Aug 28 '20 at 08:09

Set<Integer> departments = new HashSet<Integer>();
departments.add(1);
departments.add(2);
departments.add(3);

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> r = cq.from(Employee.class);
cq.select(r).where(r.get(Employee_.department).get(Department_.id).in(departments));
TypedQuery<Employee> query = em.createQuery(cq);
List<Employee> result = query.getResultList();

score 0 · Answer 2 · answered Aug 28 '20 at 08:03

0

It is logical in SQL, but in JPA you should do the Join to prevent queries from being launched to initialize the Department type objects.

It can be done, but I think it would be better to do it with a Join.

In the end, even if the table has the information, your JPA entities, which is what Criteria-api understands, does not have that ID, it has a reference to an object with that ID.

Although I do not recommend it, the implementation would be something like this:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);

Root<Employee> root = cq.from(Employee.class);

cq.where(root.get("department").in(listIds));
cq.select(root);

Activate the log traces to show the launched queries and think if it is worth throwing a query with a join or N queries without a join.

In order not to overload the memory you should establish the relations as lazy.

answered Aug 28 '20 at 08:03

JLazar0

933
1
6
19

I totally disagree with you. It is logical in SQL and JPA. Also, the load of the department is not related to the problem, If i set it as FetchType.LAZY, it won't be loaded initially. And the JPA has the ID (set a look on the next answer). – Mohammad Awwaad Aug 28 '20 at 14:17
As I told you before, if you do .get ().get () will trigger the number of queries (when it should be only one) by having an in clause, also if you don't put the relations like Lazy you will overload memory. All for defining a Join. – JLazar0 Aug 28 '20 at 14:46
And regarding the fact that the JPA has the ID, I want to clarify, the entity has a parameter that points to another entity which has the ID, it is not like in an sql query that the table itself has the ID, that is why will produce 1 + N queries, the main query and one for each element of the in clause to compare by the ID – JLazar0 Aug 28 '20 at 14:53
I've tried the .get("department").get("id). It works as expected as one query without join or subquery. – Mohammad Awwaad Aug 28 '20 at 14:53
Can you tell me your version of hibernate and JPA to test it? With version 5.2.1.Final and JPA2.1 it triggers one more query for each element of the in clause. – JLazar0 Aug 28 '20 at 15:18
I'm using spring-boot 2.3.2 (with related data-jpa starter) and hibernate 5.4.19. I believe that if i downgraded to your version the result will be the same. I don't understand what do you mean by extra query for each 'in clause' element. But maybe you have mis-understanding of the case. – Mohammad Awwaad Aug 28 '20 at 15:42
I think that is it, that I have not understood the case. – JLazar0 Aug 28 '20 at 15:57

JPA criteria filter by related entity without join

2 Answers2