jpa 默认空字符_如何在JPA命名查询中处理数字类型的空值

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;

}

UserDao.java

public User getUser(Long id, String username) {

TypedQuery query = Dao.entityManager.createNamedQuery("getUser", User.class);

query.setParameter("id", id);

query.setParameter("username", username);

List users = query.getResultList();

if (users.size() > 0) {

return users.get(0);

} else {

return null;

}

}

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

What I have tried:

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

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

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

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

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

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 ";

}

}

解决方案

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