How to form a JPA query?

Question

I have the table below:

I want to form a query with the following rules: Get product1 where Type is not type1 and not type2 and Flavor is not flavor1.

Type can be type1, type 2 or null.

I formed my query like this:

    CriteriaBuilder cb = this.getEntityManager().getCriteriaBuilder();
    CriteriaQuery<Product> searchQuery = cb.createQuery(Product.class);
    Root<Product> u = searchQuery.from(Product.class);

    List<Predicate> predicates = new ArrayList<Predicate>();

    predicates.add(cb.and(cb.equal(u.get("product"),"product1"),cb.isNull(u.get("type")),cb.notEqual(u.get("flavor"), "flavor1")));

The problem is that this query returns nothing… Am I missing something? Please note that my question refers to the logic and not to the syntax, as the syntax is checked by forming simpler queries that returned some dummy results. Thank you!

Mapping of related entities (probably `Product` and `Service` mapings). Moreover if base of your "where clause" is AND, then add predicates to your `predicates` without wrapping into AND. — Antoniossss, Mar 29 '16 at 11:38

score 1 · Answer 1 · answered Mar 29 '16 at 11:47

1

Try removing top level AND predicate. Add every predicate to predicates and create where from them with

predicates.add(cb.equal(u.get("product"),"product1"))
predicates.add(cb.isNull(u.get("type")));
predicates.add(cb.notEqual(u.get("flavor"), "flavor1")); // and so on

and then

searchQuery.where(predicates.toArray(new Predicate[predicates.size()]));

And on top of that, make sure, that your DB content is matching your query so indeed something should be returned :)

As I can see in yout "table" there are no nulls in "type" column. Are there any nulls in this columnt in the database? Maybe it is not null in the db only empty string (and that is a big difference)

answered Mar 29 '16 at 11:47

Antoniossss

31,590
6
57
99

Thank you for your response, but I also have rules for the other products (product2, etc. ) so this solution will not have the desired result :( – christinasantz Mar 29 '16 at 13:00
Well i can only reffer to what you have showed us. You wold have to show all predicates. Besides did you test your query in as sql in your db environement? You can try to proxy DB calls to check what SQL is actually generated and investigate. What about NULL values i have mentioned ? – Antoniossss Mar 29 '16 at 13:56
False alarm! I present the query results in a table and I haven't noticed that the desired rows were there. Thanks anyway! – christinasantz Mar 30 '16 at 06:14
Hehehe what a fail :) Best of luck! – Antoniossss Mar 30 '16 at 07:02

score 1 · Answer 2 · edited May 23 '17 at 11:59

Seems pretty straight forward. I think what you're missing is that a NULL in the database column doesn't match anything unless you specify it exactly. In other words, if you say type not in ('type1', 'type2') you are not implicity getting null columns. You have to ask for them if you want them:

With a JPQL query:

List<User> c1 = em.createQuery("select u from User u where (type not in ('type1', 'type2') or type = null) and flavor != 'flavor1'", User.class).getResultList();
System.out.println(c1);

With a CriteriaQuery:

// and with CriteriaQuery
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> q = cb.createQuery(User.class);
Root<User> u = q.from(User.class);
List<String> typeFilter = Arrays.asList("type1", "type2");
String flavor = "flavor1";
List<User> rs = em.createQuery(q.select(u).where( cb.or(cb.not(u.get("type").in(typeFilter)), cb.isNull(u.get("type"))), cb.notEqual(u.get("flavor"), flavor) ) ).getResultList();

This gives me the following output:

Hibernate: select user0_.id as id1_0_, user0_.flavor as flavor2_0_, user0_.product as product3_0_, user0_.type as type4_0_ from User user0_ where (user0_.type not in  ('type1' , 'type2') or user0_.type is null) and user0_.flavor<>'flavor1'
[model.User@30263191]
Hibernate: select user0_.id as id1_0_, user0_.flavor as flavor2_0_, user0_.product as product3_0_, user0_.type as type4_0_ from User user0_ where (user0_.type not in  (? , ?) or user0_.type is null) and user0_.flavor<>?
[model.User@30263191]

Helpful Link: How to query a column which value is null in JPA?.

How to form a JPA query?

2 Answers2