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!
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.
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.
The Predicate class is part of the Criteria API and is used to construct where clauses.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With