Passing List<Integer> in spring data jpa native query

Question

Using spring data JPA, I am trying to make this sort of query (it is more complex, this is a simple case)

@Query(nativeQuery = true, 
       value = "SELECT * FROM events WHERE typeId IN (?1)")
List<Event> findEventsByType(List<Integer> types);

When I launch the query, an exception raises:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

I have tried List < Integer >, Integer[], Object[] and String but it is not working...

Can't I pass list of values?

Which is the best approach to make this sort of queries?

Thanks!

Answer 1

Use JPQL. A native query is or should be passed to the database exactly as you have created the SQL string, and unless your driver can take a serialized collection and understand that the single parameter needs to be interpreted as many, it just won't work. The collection you pass in needs the SQL expanded from (?) to (?, ?,...) based on the number of elements in a collection, and JDBC drivers just are not able to do this, and JPA providers are required to execute the string as is.

A JPQL query allows the JPA provider to create the SQL it needs dynamically based on the list passed in, so it can expand the collection for you.