JPA passing list to IN clause in named native query

Question

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 

Answer 1

The above accepted answer is not correct and led me off track for many days !!

JPA and Hibernate both accept collections in native query using Query.

You just need to do

String nativeQuery = "Select * from A where name in :names"; //use (:names) for older versions of hibernate Query q = em.createNativeQuery(nativeQuery); q.setParameter("names", l); 

Also refer the answers here which suggest the same (I picked the above example from one of them)

1. Reference 1
2. Reference 2 which mentioned which cases paranthesis works which giving the list as a parameter

*note that these references are about jpql queries, nevertheless the usage of collections is working with native queries too.

Answer 2

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.