Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Criteria API combined AND / OR

I am struggling to create a query using the Criteria API. The following SQL query returns the result that I need:

SELECT * FROM MODEL WHERE MANUFACTURER_ID = 1 AND SHORTNAME LIKE '%SF%' OR LONGNAME LIKE '%SF%';

I have written the following code using the criteria API:

public List<Model> findAllByManufacturer(Manufacturer manufacturer,
        String pattern) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Model> cq = cb.createQuery(Model.class);
    Root<Model> m = cq.from(Model.class);
    cq.select(m);
    Join<Model, Manufacturer> mf = m.join("manufacturer");
    Predicate p = cb.equal(mf.get("id"), manufacturer.getId());
    p = cb.and(cb.like(cb.upper(m.<String>get("shortName")),
            pattern.toUpperCase()));
    p = cb.or(cb.equal(cb.upper(m.<String>get("longName")),
            pattern.toUpperCase()));
    cq.where(p);
    return em.createQuery(cq).getResultList();
}

When I think about it logically, it should work:

Predicate p = cb.equal(mf.get("id"), manufacturer.getId());

WHERE MANUFACTURER_ID = 1

p = cb.and(cb.like(cb.upper(m.<String>get("shortName")),
                pattern.toUpperCase()));

AND SHORTNAME LIKE '%SF%'

p = cb.or(cb.equal(cb.upper(m.<String>get("longName")),
                pattern.toUpperCase()));

OR LONGNAME LIKE '%SF%'

The query does properly run in that I am not getting any errors, it just is not returning any results. Any clue on where I went wrong are very much appreciated!

like image 490
Laurens Avatar asked Aug 27 '11 14:08

Laurens


People also ask

Is Criteria API deprecated?

The Criteria API allows us to build up a criteria query object programmatically, where we can apply different kinds of filtration rules and logical conditions. Since Hibernate 5.2, the Hibernate Criteria API is deprecated, and new development is focused on the JPA Criteria API.

What is the criteria API and what is it used for?

The Criteria API is used to define queries for entities and their persistent state by creating query-defining objects. Criteria queries are written using Java programming language APIs, are typesafe, and are portable. Such queries work regardless of the underlying data store.

What is predicate in criteria query?

The Predicate class is part of the Criteria API and is used to construct where clauses.


1 Answers

Have you checked the generated SQL?

You're building a predicate and assigning it to p, but then you're creating another one and assign it to the same p variable.

I think it should rather be:

Predicate predicateOnManufacturerId = cb.equal(mf.get("id"), 
                                               manufacturer.getId());
Predicate predicateOnShortName = cb.like(cb.upper(m.<String>get("shortName")),
                                         pattern.toUpperCase()));
Predicate predicateOnLongName = cb.equal(cb.upper(m.<String>get("longName")),
                                         pattern.toUpperCase()));
Predicate p = cb.or(cb.and(predicateOnManufacturerId, 
                           predicateOnShortName),
                    predicateOnLongName);
cq.where(p);

Also, why don't you use the meta-model of your entities:

Join<Model, Manufacturer> mf = m.join(Model_.manufacturer);
... cb.equal(mf.get(Manufacturer_.id);
...

It would be more type-safe, and you would detect incorrect queries at compilation time rather than runtime.

like image 110
JB Nizet Avatar answered Oct 03 '22 21:10

JB Nizet