JPA: How to check NULL query-parameter value with HQL?

Question

Query query =  em.createQuery(
        "select count(u) from User u where (u.id=:a) and (u.photo=:b)")
        .setParameter("a",userId).setParameter("b",null);

In my case, I want check whether user photo uploaded or not (photo value is null or not). I tried above query.

Taky · Accepted Answer · 2013-03-05 05:46:34Z

11

See example in Hibernate documentation.

You should check is parameter is null:

Query query =  em.createQuery(
        "select count(u) from User u where (u.id=:userId) and (u.photo is null)")
        .setParameter("userId",userId);

answered Mar 5, 2013 at 5:46

Taky

5,3401 gold badge22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zakmck · Accepted Answer · 2020-11-25 14:27:37Z

7

If you're aiming at generalising "b", so that a method can receive either a given search value, or a null, this usually works:

Query q = em.createQuery (
  "SELECT ... WHERE ... ( :b IS NULL AND u.photo IS NULL OR u.photo = :b )" 
);
q.setParameter ( "b", searchValue ); // can be null
...

There is an helper to use this approach here (see parameterizedWithNullHql()).

edited Nov 25, 2020 at 14:27

answered Feb 3, 2015 at 16:57

zakmck

3,1041 gold badge42 silver badges64 bronze badges

Collectives™ on Stack Overflow

JPA: How to check NULL query-parameter value with HQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related