Search code examples
sqlhibernatejpain-operatorsql-in

Using IN clause in a native sql query

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?

Solution

  • 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();