Hibernate Criteria Builder: How to filter by a nullable entity attribute?

Question

Scenario

I have an entity Treatment which has two FKs:

@ManyToOne
@JoinColumn(name = "product_id", nullable = true)
private Product product;

@ManyToOne
@JoinColumn(name = "product2_id", nullable = true)
private Product product2;

This Product entity has a regular id attribute as PK.

What I want:

I want to find all treatments where any of the products' ids matches a given one, in native SQL it would be something like:

select t.id, product_id,product2_id, p1.category_id,p2.category_id
from Treatment t
left join product p1 on t.product_id=p1.id
left join product p2 on t.product2_id=p2.id
where p1.category_id=17 or p2.category_id=17

What I've done so far:

Given:

CriteriaQuery<Treatment> cr = cb.createQuery(Treatment.class);
Root<Treatment> root = cr.from(Treatment.class);

My attempt was:

if (category != null ) {
    Predicate predicate = cb.or( 
      cb.equal(root.get("product").get("category"), category),
      cb.equal(root.get("product2").get("category"), category)
    );
    predicateList.add(predicate);
}

but it did not work: it gets treatments where both products are not null and at least one of them matches the category condition.

The generated query was (after cleaning it to make it readable):

select 
  ...
from treatment t 
  cross join product p1 
  cross join product p2 
  where t.product_id=p1.id 
  and t.product2_id=p2.id 
  and (p1.category_id=17 or p2.category_id=17) 
order by t.id desc

You can perform the joins yourself using criteria as far as I know. `createAlias()` seems to be the way to go [according to this question](https://stackoverflow.com/questions/14215288/left-join-using-hibernate-criteria) — XtremeBaumer, Sep 24 '19 at 14:47
@XtremeBaumer I'm using Hibernate 5 and its *new* CriteriaBuilder API. The linked example generates warnings (*Deprecated* classes) as it is the old API — Pablo Lozano, Sep 24 '19 at 14:49

score 4 · Accepted Answer · answered Sep 24 '19 at 15:02

Try building paths using From#join, where you can explicitly define join type, something like:

Predicate predicate = cb.or( 
  cb.equal(root.join("product", JoinType.LEFT).get("category"), category),
  cb.equal(root.join("product2", JoinType.LEFT).get("category"), category)
);

Alan Sereb · Answer 2 · 2019-09-24T15:00:47.037

0

This should work:

Predicate predicate = (root1, cq1, cb1) -> Specifications.where(
            cb1.equal(root1.get("product").get("category"), category)).or(
            cb1.equal(root1.get("product2").get("category"), category))
            .toPredicate(root, cb, cq);

edited Sep 24 '19 at 15:00

answered Sep 24 '19 at 14:54

Alan Sereb

2,358
2
17
31

I guess I'm missing something: Where should I put this lambda? – Pablo Lozano Sep 24 '19 at 14:58
You are right, I did not clarify it. It returns predicate, so it should be placed inside of your predicate. (I updated the answer) – Alan Sereb Sep 24 '19 at 15:00
Sorry, but `javax.persistence.criteria.Predicate` is not a functional interface in javax.persistence-api-2.2 – Pablo Lozano Sep 24 '19 at 15:05
1

I believe its @Alex's answer that is correct. The key is the Left Join – Jayr Sep 24 '19 at 15:06
`.toPredicate(root, cb, cq);` pass your root, cb, cq you defined before – Alan Sereb Sep 24 '19 at 15:06

Hibernate Criteria Builder: How to filter by a nullable entity attribute?

Scenario

What I want:

What I've done so far:

2 Answers2