Hibernate CriteriaBuiler .or()

Question

I have constructed the following or predicate for my query. I want to retrieve all orders that either have no customer associated with it, and those that do and have one of several specific statuses.

Predicate p1 = cbuilder.isNull(root.get(Order_.customer)); 
Predicate p2 = root.get(Order_.customer).get(Customer_.status).in(statusList);

predicates.add(cbuilder.or(p2, p1));

...

cquery.where(cbuilder.and(predicates.toArray(new Predicate[predicates.size()])));

For an example dataset, both p1 and p2 are true. However, the query returns no resuls. In other words, this:

Predicate p1 = cbuilder.isNull(root.get(Order_.customer)); 
predicates.add(p1);

and this:

Predicate p2 = root.get(Order_.customer).get(Customer_.status).in(statusList);
predicates.add(p2);

both return results, but when I try to add the .or() operator it returns no results. Below is part of the generated sql query, what am I doing wrong here?

Hibernate:

select 
    order0_.orderId as orderId1_1_, 
    order0_.customer_customerId as customer_s10_1_,
    ...

from 
    Order order0_ 

cross join Customer customer1_ 
    where 
        order0_.customer_customerId=customer1_.customerId 

    and 
        (customer1_.status in (? , ? , ? , ? , ? , ? , ? , ?) 
            or 
        order0_.customer_customerId is null)

Generated sql query from adding only p1 to the predicates:

Hibernate:

select 
    order0_.orderId as orderId1_1_, 
    order0_.customer_customerId as customer_s10_1_
    ...

from 
    Order order0_ 

where 
    (order0_.customer_customerId is null)

Generated sql query from adding only p2 to the predicates:

Hibernate:

select 
    order0_.orderId as orderId1_1_,  
    order0_.customer_customerId as customer_s10_1_, 

from 
    Order order0_ 

cross join Customer customer1_ 
    where 
        order0_.customer_customerId=customer1_.customerId 

    and 
        (customer1_.status in (? , ? , ? , ? , ? , ? , ? , ?))

I think the problem is in the cross join and this line: and (customer1_.status in (? , ? , ? , ? , ? , ? , ? , ?) or order0_.customer_customerId is null) — appel, Sep 01 '15 at 11:44
Can you show us the model mappings? I also suggest you to make the join explicit instead of walking using get().get() to get rid of the cross join. — mamuso, Sep 01 '15 at 12:42

score 0 · Accepted Answer · answered Sep 01 '15 at 12:56

This part returned false when the Customer was null, because the first predicate (where ..) cannot be satisfied. Added a subquery first and took the second predicate out of the query, fixing it.

cross join Customer customer1_ 
where 
    order0_.customer_customerId=customer1_.customerId 

and 
    (customer1_.status in (? , ? , ? , ? , ? , ? , ? , ?) 
        or 
    order0_.customer_customerId is null)

Hibernate CriteriaBuiler .or()

1 Answers1