Using IN clause in a native sql query

Question

We are trying to dynamically generate an IN clause for a native sql query to return a JPA entity. Hibernate is our JPA provider. Our code looks something like this.

@NamedQuery(
    name="fooQuery",
    queryString="select f from Foo f where f.status in (?1)"
)

....

Query q = entityManager.createNamedQuery("fooQuery");
q.setParameter(1, "('NEW','OLD')");
return q.getResultList();

This doesn't work, the in clause does not recognize any of the values passed in via this manner. Does anyone know of a solution to this problem?

score 21 · Accepted Answer · edited May 05 '15 at 15:41

21

JPA supports named list parameters, in your case:

@NamedQuery(
    name="fooQuery",
    queryString="select f from Foo f where f.status in (?1)"
)

Query q = entityManager.createNamedQuery("fooQuery");

List<String> listParameter = new ArrayList<>();
listParameter.add("NEW");
listParameter.add("OLD");

q.setParameter(1, listParameter); 
return q.getResultList();

edited May 05 '15 at 15:41

Jean-François Savard

20,626
7
49
76

answered May 07 '09 at 05:47

topchef

19,091
9
63
102

1

This is not a Native Query, so I guess it's not an acceptable answer? – Cécile Fecherolle Aug 31 '18 at 13:35
JPA will generate SQL query with IN clause parameterized from Java list - resulting SQL query will contain native (SQL) IN clause. – topchef Sep 01 '18 at 04:40

score 0 · Answer 2 · answered May 06 '09 at 23:25

I would recommend to not use a composite parameter for your query, but to split it out individually. Don't specify ('NEW','OLD'); specify separate parameters of 'NEW' and 'OLD'. That may help.

This, of course, may cause problems if your parameter lengths are undefined.

Using IN clause in a native sql query

2 Answers2

Linked