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

最新推荐文章于 2024-10-08 10:55:16 发布

原创最新推荐文章于 2024-10-08 10:55:16 发布 · 568 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#jpa 默认空字符

本文探讨了在使用JPA的命名查询时如何动态处理ID字段可能为null的情况。作者尝试了多种方法，包括在查询语句中直接使用null值，但遇到了类型不匹配的错误。由于无法在运行时更改命名查询，作者最终选择了在DAO层构建动态查询来解决此问题。这种方法避免了修改原本的命名查询，同时满足了处理空值的需求。

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