How to handle null value of number type in JPA named query

Question

I want to pass two parameters to namedquery. One is number type and the other is String type. They both could be null.

For instance, (id=null, username='joe') and (id=1, username='joe') are two different results. In namedQuery, the syntax is "u.id is null" if id is null, but "u.id = :id" if id is not null. My question is how to dynamically handle the id filed in namedQuery?

Please check my sample code:

1.User.java

@NamedQueries({
        @NamedQuery(name = "getUser", query = "select u from User u"
                + " where u.id = :id"    
                + " And u.username= :username")
})
public class User{
     public Long id;
     public String username;
}

2. UserDao.java

public User getUser(Long id, String username) {
    TypedQuery<User> query = Dao.entityManager.createNamedQuery("getUser", User.class);
    query.setParameter("id", id);
    query.setParameter("username", username);

    List<User> users = query.getResultList();

    if (users.size() > 0) {
        return users.get(0);
    } else {
        return null;
    }
}

=======================================

What I have tried:

1. This is legacy code and I don't want to change the structure. So I don't want to use Criteria.

2. select u from User u where (:id is null or u.id= :id) and u.username= :username

   // throw exception: inconsistent datatypes: expected NUMBER got BINARY

3. select u from User u where u.id= nullif(:id, null) and u.username= :username

   // Throw exception: inconsistent datatypes: expected NUMBER got BINARY

4. I also tried nvl and decode in namedQuery, didn't work.

5. query.setParameter("id", id==null?-1:id) // didn't work.

My last choice will be writing query in UserDao file to replace namedQuery in User file.

Thank you !

===========================================

I am running out of time and have to give up using namedQuery. My solution:

# UserDao.java 

  public User getUser(Long id, String usename) {
        String getUser = "select u from user u where u.id " + Dao.isNull(id) 
                       + " And u.username " + Dao.isNull(username);
        Query query = Dao.entityManager.createQuery(getUser);
    }

# Dao.java

   public static String isNull(Object field) {
        if (field != null) {
                if (field instanceof String) {
                    return " = " + "'" + field + "'";
                } else {
                    return " = " + field;
                }

            } else {
                return " is NULL ";
            }
    }

Answer 1

You cannot change the named query at run time. Doing so would defeat the purpose of the named query. Dynamic queries should be created using the criteria api.

See this answer on what to use in the named query.

   from CountryDTO c where 
    ((:status is null and c.status is null) or c.status = :status) 
    and c.type =:type

Answer 2

Try to let a boolean check the null. It worked for me.

query.setParameter("idIsSet", id != null); 

query.setParameter("id", id); 

In the named query:

(:idIsSet = false or id = :id)

When id is null, the "idIsSet" is false. So ":idIsSet = false" turns into true and the id will not be checked.

Answer 3

If ) at the end of your query was a mistake, try without it

    @NamedQuery(name = "getUser", query = "select u from User u"
            + " where (:id is null or u.id = :id)"    
            + " And :username"

EDIT

If everything else fails, this quick workaround should do it (if you don't have negative ids in database) in combination with the above null check

query.setParameter("id", id == null ? -1 : id);