JPA Criteria Disjunction

Question

Im trying to learn and understand JPA criteria. So far i am very capable in SQL, and fairly capable in Hibernate Criteria and HQL.

I am trying to do a fairly simple select with an OR statement.

In plain SQL my select looks like this:

SELECT * FROM CHANGED_LOG
WHERE key1 = 52540 AND objectCode = 'Order'
OR key1 = 48398 AND objectCode = 'Package'

This gives me every row where key1 = 52540 and objectCode equals Order AND also every row where key = 48398 and objectCode equals Package. This is exactly what i want.

So trying to do this with JPA criteria (which seems exceptionally complicated...), my best guess so far has been this:

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();

    CriteriaQuery<ChangedLogBean> query = builder.createQuery(ChangedLogBean.class);
    Root<ChangedLogBean> from = query.from(ChangedLogBean.class);
    CriteriaQuery<ChangedLogBean> select = query.select(from);

    Predicate orderChangedLogBeans = builder.conjunction();
    builder.and(orderChangedLogBeans, builder.equal(from.get("key1"), orderId));
    builder.and(orderChangedLogBeans, builder.equal(from.get("objectCode"), ChangedLogBean.ObjectType.OrderBean));

    Predicate packageChangedLogBeans = builder.conjunction();
    builder.and(packageChangedLogBeans, builder.equal(from.get("key1"), packageId));
    builder.and(packageChangedLogBeans, builder.equal(from.get("objectCode"), ChangedLogBean.ObjectType.PackageBean));

    Predicate orderOrPackage = builder.disjunction();
    orderOrPackage.getExpressions().add(orderChangedLogBeans);
    orderOrPackage.getExpressions().add(packageChangedLogBeans);

    query.where(orderOrPackage);

    return entityManager.createQuery(select).getResultList();

Wow. A lot of lines for a simple query... But still, this returnes to me EVERY single row from the DB.

What am i doing wrong here?

Thanks for all your helpful answers :)

score 6 · Accepted Answer · edited May 23 '17 at 12:33

First thing to do in such cases is configuring the JPA provider in order to log the generated queries. See this q/a for an example.

To answer your question, I am unsure about the use of getExpressions(), so I'd suggest the following rude approach:

Predicate p1 = builder.equal(...);
Predicate p2 = builder.equal(...);
Predicate p3 = builder.equal(...);
Predicate p4 = builder.equal(...);

Predicate p5 = builder.and(p1, p2);
Predicate p6 = builder.and(p3, p4);

query.where(builder.or(p5, p6));

Your error is that such a line:

builder.and(orderChangedLogBeans, builder.equal(from.get("key1"), orderId));

will NOT modify in place orderChangedLogBeans. You have to get the Predicate returned by CriteriaBuilder#and() instead:

orderChangedLogBeans = builder.and(orderChangedLogBeans, builder.equal(from.get("key1"), orderId));

Thanks. Simplified and beutiful. Works like a charm. – user829237 Jan 18 '13 at 11:43 — user829237, Jan 18 '13 at 11:43

JPA Criteria Disjunction

1 Answers1