如何在JPA命名查询中处理数字类型的空值 [英] How to handle null value of number type in JPA named query

问题描述

我想将两个参数传递给namedquery。一个是数字类型，另一个是字符串类型。它们都可以为空。

例如，（id = null，username ='joe'）和（id = 1，username ='joe'）是两个不同的结果。在namedQuery中，如果id为null，则语法为u.id为null，如果id为非null，则为u.id =：id。我的问题是如何动态处理namedQuery中的id？

请检查我的示例代码：

1 .User.java

  @NamedQueries（{
 @NamedQuery（name =getUser，query =select u来自用户u
 +，其中u.id =：id
 +和u.username =：username）
}）
公共类用户{
 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）; 
 
列表<用户> users = query.getResultList（）; 
 
 if（users.size（）> 0）{
 return users.get（0）; 
} else {
返回null; 
} 
} 
  

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

我的尝试：

1. 这是遗留代码，我不想更改结构。所以我不想使用Criteria。




2. 从User u中选择你的地方（：id为null或u.id =：id）和u。 username =：username

   
   
   

   //抛出异常：不一致的数据类型：预期NUMBER得到BINARY




3. 选择u来自用户u，其中u.id = nullif（：id，null）和u.username =：username

   
   
   

   //抛出异常：不一致的数据类型：预期NUMBER得到了BINARY




4. 我也试过nvl并在namedQuery中解码，但没有用。




5. query.setParameter（id，id == null？-1：id）//无效。

我的最后一个选择是在UserDao文件中编写查询以替换用户文件中的namedQuery。

谢谢！

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

我没时间了，不得不放弃使用namedQuery。我的解决方案：

 ＃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）; 
查询查询= 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 {
 returnis NULL; 
} 
} 
  

解决方案

你无法在运行时更改命名查询。这样做会破坏命名查询的目的。
应使用条件api创建动态查询。

参见这关于在命名查询中使用什么的答案。

<来自CountryDTO c的pre> 其中
（（：status为null且c.status为null）或c.status =：status）
和c.type =：输入


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

解决方案

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

这篇关于如何在JPA命名查询中处理数字类型的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！