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?

Jean-François Savard · Accepted Answer · 2015-05-05 15:41:41Z

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 5, 2015 at 15:41

Jean-François Savard

21k7 gold badges54 silver badges78 bronze badges

answered May 7, 2009 at 5:47

topchef

19.9k9 gold badges69 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cécile Fecherolle Over a year ago

This is not a Native Query, so I guess it's not an acceptable answer?

topchef Over a year ago

JPA will generate SQL query with IN clause parameterized from Java list - resulting SQL query will contain native (SQL) IN clause.

Paul Sonier · Accepted Answer · 2009-05-06 23:25:36Z

0

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.

answered May 6, 2009 at 23:25

Paul Sonier

39.6k3 gold badges81 silver badges118 bronze badges

Collectives™ on Stack Overflow

Using IN clause in a native sql query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related