JPA通过列表IN子句中命名的本地查询 [英] JPA passing list to IN clause in named native query
问题描述
我知道我可以通过一个列表在JPA命名的查询,但如何NamedNativeQuery?我试过很多方法,但仍不能只是通过列表中一个NamedNativeQuery。任何人都知道如何在列表子句中NamedNativeQuery传递到?非常感谢你!
该NamedNativeQuery是如下:
@NamedNativeQuery(
NAME =User.findByUserIdList
查询=选择u.user_id,u.dob,u.name,u.sex,从用户u.address U+
凡在u.user_id(?userIdList)
)
和它被称为是这样的:
列表<对象[]>用户列表= em.createNamedQuery(User.findByUserIdList)的setParameter(userIdList列表).getResultList()。
但结果并不如我所料。
的System.out.println(userList.size()); //输出1[对象] USER = userList.get(0);
的System.out.println(user.length); //预计5,但结果是3
的System.out.println(用户[0]); //输出MDAVERSION这是不为user_id
的System.out.println(用户[1]); //输出5
的System.out.println(用户[2]); //输出7
一个列表是不是原生SQL查询的有效参数,因为它不能在JDBC的约束。你需要有对列表中的每个参数的参数。
在这里u.user_id在(?ID1,?ID2)
这是通过JPQL支持,但不是SQL,所以你可以使用,而不是本机查询JPQL。
一些JPA提供者可能支持这一点,所以你可能要记录错误与您的供应商。
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!
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)"
)
and it is called like this:
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
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.
where u.user_id in (?id1, ?id2)
This is supported through JPQL, but not SQL, so you could use JPQL instead of a native query.
Some JPA providers may support this, so you may want to log a bug with your provider.
这篇关于JPA通过列表IN子句中命名的本地查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!