[SOLVED] JPA passing list to IN clause in named native query

JPA passing list to IN clause in named native query

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

Solution

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.