JPA 将列表传递给命名本机查询中的 IN 子句 [英] JPA passing list to IN clause in named native query

问题描述

我知道我可以将列表传递给 JPA 中的命名查询，但是 NamedNativeQuery 怎么样?我尝试了很多方法，但仍然不能只是将列表传递给 NamedNativeQuery.任何人都知道如何将列表传递给 NamedNativeQuery 中的 in 子句?非常感谢！

I know I can pass a list to named query in JPA, but how about NamedNativeQuery? I have tried many ways but still can't just pass the list to a NamedNativeQuery. Anyone know how to pass a list to the in clause in NamedNativeQuery? Thank you very much!

NamedNativeQuery 如下:

The NamedNativeQuery is as below:

@NamedNativeQuery(
   name="User.findByUserIdList", 
   query="select u.user_id, u.dob, u.name, u.sex, u.address from user u "+
         "where u.user_id in (?userIdList)"
)

它是这样调用的:

List<Object[]> userList = em.createNamedQuery("User.findByUserIdList").setParameter("userIdList", list).getResultList();

然而结果并不如我所料.

However the result is not as I expected.

System.out.println(userList.size());  //output 1

Object[] user = userList.get(0);
System.out.println(user.length);   //expected 5 but result is 3
System.out.println(user[0]);       //output MDAVERSION which is not a user_id
System.out.println(user[1]);       //output 5
System.out.println(user[2]);       //output 7

推荐答案

列表不是本地 SQL 查询的有效参数，因为它不能在 JDBC 中绑定.列表中的每个参数都需要有一个参数.

A list is not a valid parameter for a native SQL query, as it cannot be bound in JDBC. You need to have a parameter for each argument in the list.

u.user_id 在 (?id1, ?id2) 中的位置

where u.user_id in (?id1, ?id2)

JPQL 支持此功能，但 SQL 不支持，因此您可以使用 JPQL 而不是本机查询.

This is supported through JPQL, but not SQL, so you could use JPQL instead of a native query.

某些 JPA 提供程序可能支持此功能，因此您可能希望与您的提供程序一起记录错误.

Some JPA providers may support this, so you may want to log a bug with your provider.

这篇关于JPA 将列表传递给命名本机查询中的 IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！