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!

This is quite an old post, but did you manage to solve this? — Deniss M.
– Deniss M., Commented Nov 7, 2016 at 8:34

Thomas Turner · Accepted Answer · 2013-10-25 13:56:27Z

14

Try taking away the @Query and make the method name:

public List<Event> findByTypeIn(List<Integer> types);

See table 2.2 in the link: http://docs.spring.io/spring-data/jpa/docs/1.2.0.RELEASE/reference/html/

answered Oct 25, 2013 at 13:56

Thomas Turner

1811 silver badge3 bronze badges

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

2 Comments

antacerod Over a year ago

I can't do that because the real query is more complex. I have shown here the base case.

LearnerOP Over a year ago

The title is NativeQuery but the query itself is a simple one. The answer works in case of Query, and not NativeQuery. Though, it would be helpful to people who land here with above scenarios.

Sahil Chhabra · Accepted Answer · 2018-12-31 02:40:36Z

8

I tried like below and it works for me.

@Query(value = "select * from events where type_id in :types", nativeQuery = true)
List<Event> findEventsByType(@Param("types") List<Integer> types);

edited Dec 31, 2018 at 2:40

answered Dec 4, 2018 at 10:37

Sahil Chhabra

11.9k5 gold badges69 silver badges64 bronze badges

Comments

Mayuresh Mitkari · Accepted Answer · 2020-10-27 03:43:08Z

7

@Query(value = "SELECT c from Company c where " +
    "c.companyName IN (:company_names)")
List<Company> findCompaniesByName(@Param("company_names") List<String> companyNames);

This is the solution to your problem.

Here I am passing List which contains company names and I am querying DB and storing result in List.

Hope this hepls!

answered Oct 27, 2020 at 3:43

Mayuresh Mitkari

711 silver badge5 bronze badges

Comments

Chris · Accepted Answer · 2013-10-25 15:02:59Z

6

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.

answered Oct 25, 2013 at 15:02

Chris

21.3k2 gold badges34 silver badges45 bronze badges

Comments

Ser · Accepted Answer · 2019-11-13 15:33:18Z

2

I know this is a little bit out of context (we use Update and not Select), but this can be usefull for others :


    /**
     * Update the state of list of entities using their ids
     * @param ids request ids
     * @param state new state
     * @return
     */
    @Modifying
    @Query(value = "UPDATE AbstractRequest SET state = :state WHERE id IN (:ids)")
    int updateStates(@Param("ids") List<Long> ids, @Param("state") InternalRequestStep state);

answered Nov 13, 2019 at 15:33

Ser

2,92027 silver badges33 bronze badges

Comments

Tanveer Badar · Accepted Answer · 2019-11-13 07:11:39Z

1

Try this. It will work for Native Query in SpringBoot JPA:

@Query(value = "SELECT * FROM table WHERE columnName IN (:inputList)" , 
     nativeQuery = true)
List<Object> findByObjectList(@Param("inputList") List<Object> inputList);

And also in case of JPA, try the below :

List<Object> findByObjectList(List<Object> inputList)

edited Nov 13, 2019 at 7:11

Tanveer Badar

5,5622 gold badges29 silver badges34 bronze badges

answered Jan 28, 2019 at 9:12

rahulnikhare

1,5461 gold badge19 silver badges29 bronze badges

Comments

Chakresh Tiwari · Accepted Answer · 2019-09-19 11:26:07Z

0

pass array this way inside IN Clause

@Query(value = "select name from teams where name in :names", nativeQuery = true)
List<String> getNames(@Param("names") String[] names);

Call this way

String[] names = {"testing team","development team"};
List<String> teamtest = teamRepository.getNames(names);

answered Sep 19, 2019 at 11:26

Chakresh Tiwari

7659 silver badges7 bronze badges

Comments

ecm · Accepted Answer · 2021-09-07 10:35:14Z

0

Remove brackets around (?1) parameter. Your query value should look like that:

@Query(value = "SELECT * FROM events WHERE typeId IN ?1")

edited Sep 7, 2021 at 10:35

ecm

2,9534 gold badges25 silver badges33 bronze badges

answered Sep 3, 2021 at 18:00

Alexander Peryshkin

1

Comments

TommyW · Accepted Answer · 2017-03-09 09:02:51Z

-2

try querying like this:

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

did it work?

answered Mar 9, 2017 at 9:02

TommyW

5701 gold badge6 silver badges22 bronze badges

Collectives™ on Stack Overflow

Passing List<Integer> in spring data jpa native query

9 Answers 9

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related