JPA passing list to IN clause in named native query

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.

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.

Depending on your database/provider/driver/etc., you can, in fact, pass a list in as a bound parameter to a JPA native query.

For example, with Postgres and EclipseLink, the following works (returning true), demonstrating multidimensional arrays and how to get an array of double precision. (Do SELECT pg_type.* FROM pg_catalog.pg_type for other types; probably the ones with _, but strip it off before using it.)

Array test = entityManager.unwrap(Connection.class).createArrayOf("float8", new Double[][] { { 1.0, 2.5 }, { 4.1, 5.0 } });
Object result = entityManager.createNativeQuery("SELECT ARRAY[[CAST(1.0 as double precision), 2.5],[4.1, 5.0]] = ?").setParameter(1, test).getSingleResult();

The cast is there so the literal array is of doubles rather than numeric.

More to the point of the question - I don't know how or if you can do named queries; I think it depends, maybe. But I think following would work for the Array stuff.

Array list = entityManager.unwrap(Connection.class).createArrayOf("int8", arrayOfUserIds);
List<Object[]> userList = entityManager.createNativeQuery("select u.* from user u "+
     "where u.user_id = ANY(?)")
     .setParameter(1, list)
     .getResultList();

I don't have the same schema as OP, so I haven't checked this exactly, but I think it should work - again, at least on Postgres & EclipseLink.

Also, the key was found in: http://tonaconsulting.com/postgres-and-multi-dimensions-arrays-in-jdbc/