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

Have you tried executing exactly the same query using your DB client? — Art Licis, Jun 08 '11 at 11:39
you should not use bracket (?userIdList) just remove the bracket and it would work — prashant thakre, Aug 28 '18 at 11:51

score 34 · Answer 1 · edited May 29 '21 at 15:07

34

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)

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

edited May 29 '21 at 15:07

Matthias

331
2
11

answered Jun 11 '18 at 08:56

HopeKing

2,574
3
34
54

8

Those are JPA/JPQL queries, not native SQL queries, now are they. The question is about _native queries_, and the accepted answer even calls out that difference, or am I misreading something here? – Sander Verhagen Aug 27 '18 at 19:22
"A list is not a valid parameter for a native SQL query" -this is the specific part of the previous answer which is incorrect since lists are allowed in native queries. The query i have given as example is a native query (which is a small part of what i use in my code). It works. – HopeKing Sep 06 '18 at 04:57
Your example says `jpql`, it shows a query without `SELECT ...` which makes it invalid native SQL in at least some databases I know, you're using `createQuery` instead of `createNativeQuery`. Maybe we're just not talking about the same thing here. Native == SQL. Your answer == JPQL != SQL. – Sander Verhagen Sep 06 '18 at 07:42
I was trying to convey the broad point that it is possible using native queries. I modified the answer now since some of those parts were probably confusing you. Hope that helps - thanks for the feedback. – HopeKing Sep 07 '18 at 06:57
How did you got all the points? The title of the question is about native queries! The code and the links do not help here in any way. Just renaming createQuery to createNativeQuery does not fix the answer. – tak3shi Apr 02 '20 at 12:43
2

Works for me, but only if the list has at least 1 entry. – Klaus Aug 20 '20 at 20:05
This is wrong and it doesn't work. You can do this in JPA/JPQL queries, not native SQL queries like @SanderVerhagen said – rMonteiro Sep 07 '20 at 12:48

score 21 · Accepted Answer · answered Jun 08 '11 at 13:02

21

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.

answered Jun 08 '11 at 13:02

James

19,367
9
72
129

3

Attention: this answer is incorrect. See my answer below and also the references. – HopeKing Jun 11 '18 at 08:56

Erhannis · Answer 3 · 2018-03-08T23:57:24.303

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/

score 4 · Answer 4 · answered Jun 12 '18 at 20:17

Using hibernate, JPA 2.1 and deltaspike data I could pass a list as parameter in query that contains IN clause. my query is below.

@Query(value = "SELECT DISTINCT r.* FROM EVENT AS r JOIN EVENT AS t on r.COR_UUID = t.COR_UUID where " +
        "r.eventType='Creation' and t.eventType = 'Reception' and r.EVENT_UUID in ?1", isNative = true)
public List<EventT> findDeliveredCreatedEvents(List<String> eventIds);

score 2 · Answer 5 · answered Jun 29 '18 at 14:17

currently I use JPA 2.1 with Hibernate

I also use IN condition with native query. Example of my query

SELECT ... WHERE table_name.id IN (?1)

I noticed that it's impossible to pass String like "id_1, id_2, id_3" because of limitations described by James

But when you use jpa 2.1 + hibernate it's possible to pass List of string values. For my case next code is valid:

    List<String> idList = new ArrayList<>();
    idList.add("344710");
    idList.add("574477");
    idList.add("508290");

    query.setParameter(1, idList);

score 2 · Answer 6 · answered Jul 27 '19 at 08:26

In my case ( EclipseLink , PostGreSQL ) this works :

    ServerSession serverSession = this.entityManager.unwrap(ServerSession.class);
    Accessor accessor = serverSession.getAccessor();
    accessor.reestablishConnection(serverSession);
    BigDecimal result;
    try {
        Array jiraIssues = accessor.getConnection().createArrayOf("numeric", mandayWorkLogQueryModel.getJiraIssues().toArray());
        Query nativeQuery = this.entityManager.createNativeQuery(projectMandayWorkLogQueryProvider.provide(mandayWorkLogQueryModel));
        nativeQuery.setParameter(1,mandayWorkLogQueryModel.getPsymbol());
        nativeQuery.setParameter(2,jiraIssues);
        nativeQuery.setParameter(3,mandayWorkLogQueryModel.getFrom());
        nativeQuery.setParameter(4,mandayWorkLogQueryModel.getTo());
        result = (BigDecimal) nativeQuery.getSingleResult();
    } catch (Exception e) {
        throw new DataAccessException(e);
    }

    return result;

Also in query cannot use IN(?) because you will get error like :

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = numeric[]

'IN(?)' must be swapped to '= ANY(?)'

My solution was based on Erhannis concept.

score 1 · Answer 7 · answered Mar 04 '13 at 22:20

Tried in JPA2 with Hibernate as provider and it seems hibernate does support taking in a list for "IN" and it works. (At least for named queries and I believe it will be similar with named NATIVE queries) What hibernate does internally is generate dynamic parameters, inside the IN same as the number of elements in the passed in list.

So in you example above

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

If list has 2 elements the query will look like

select u.user_id, u.dob, u.name, u.sex, u.address from user u "+
         "where u.user_id in (?, ?)

and if it has 3 elements it looks like

select u.user_id, u.dob, u.name, u.sex, u.address from user u "+
         "where u.user_id in (?, ?, ?)

score 0 · Answer 8 · answered Jul 14 '20 at 15:16

0

can be as simple as:

@Query(nativeQuery =true,value = "SELECT * FROM Employee as e WHERE e.employeeName IN (:names)")  
 List<Employee> findByEmployeeName(@Param("names") List<String> names);

answered Jul 14 '20 at 15:16

Wei Wang

1
2

score 0 · Answer 9 · edited Jan 14 '21 at 06:48

0

In jpa, it worked for me

@Query(nativeQuery =true,value = "SELECT * FROM Employee as e WHERE e.employeeName IN (:names)")  
List<Employee> findByEmployeeName(@Param("names") List<String> names);

edited Jan 14 '21 at 06:48

Tomer Shetah

7,646
6
20
32

answered Jan 14 '21 at 02:02

Edgar Nir

1

score 0 · Answer 10 · answered Feb 03 '21 at 23:35

you should do this:

String userIds ="1,2,3,4,5"; List<String> userIdList= Stream.of(userIds.split(",")).collect(Collectors.toList());

Then, passes like parameter inside your query, like this:

@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)")

Jack · Answer 11 · 2020-02-08T21:28:18.840

It's not possible with standard JPA. Hibernate offers the proprietary method setParameterList(), but it only works with Hibernate sessions and is not available in JPA's EntityManager.

I came up with the following workaround for Hibernate, which is not ideal but almost standard JPA code and has some nice properties to it.

For starters you can keep the named native query nicely separated in a orm.xml file:

<named-native-query name="Item.FIND_BY_COLORS" result-class="com.example.Item">
    <query>
        SELECT i.*
        FROM item i
        WHERE i.color IN ('blue',':colors')
        AND i.shape = :shape
    </query>
</named-native-query>

The placeholder is wrapped in single quotes, so it's a valid native JPA query. It runs without setting a parameter list and would still return correct results when other matching color parameters are set around it.

Set the parameter list in your DAO or repository class:

@SuppressWarnings("unchecked")
public List<Item> findByColors(List<String> colors) {
    String sql = getQueryString(Item.FIND_BY_COLORS, Item.class);
    sql = setParameterList(sql, "colors", colors);

    return entityManager
            .createNativeQuery(sql, Item.class)
            .setParameter("shape", 'BOX')
            .getResultList();
}

No manual construction of query strings. You can set any other parameter as you normally would.

Helper methods:

String setParameterList(String sql, String name, Collection<String> values) {
    return sql.replaceFirst(":" + name, String.join("','", values));
}

String getQueryString(String queryName, Class<?> resultClass) {
    return entityManager
            .createNamedQuery(queryName, resultClass)
            .unwrap(org.hibernate.query.Query.class) // Provider specific
            .getQueryString();
}

So basically we're reading a query string from orm.xml, manually set a parameter list and then create the native JPA query. Unfortunately, createNativeQuery().getResultList() returns an untyped query and untyped list even though we passed a result class to it. Hence the @SuppressWarnings("unchecked").

Downside: Unwrapping a query without executing it may be more complicated or impossible for JPA providers other than Hibernate. For example, the following might work for EclipseLink (untested, taken from Can I get the SQL string from a JPA query object?):

Session session = em.unwrap(JpaEntityManager.class).getActiveSession();
DatabaseQuery databaseQuery =     query.unwrap(EJBQueryImpl.class).getDatabaseQuery();
databaseQuery.prepareCall(session, new DatabaseRecord());
Record r = databaseQuery.getTranslationRow();
String bound = databaseQuery.getTranslatedSQLString(session, r);
String sqlString = databaseQuery.getSQLString();

An alternative might be to store the query in a text file and add code to read it from there.

score -4 · Answer 12 · answered Jul 18 '17 at 09:53

-4

You can try this :userIdList instead of (?userIdList)

@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"
)

answered Jul 18 '17 at 09:53

Ajay Yadav

49
8

JPA passing list to IN clause in named native query

12 Answers12

Linked

Related