How to use list parameter in JPA query with Spring Data Rest?

Question

@Query("Select loc from Location loc where loc in :locations"
    )
    public Page<Location> searchLocations(List<Location> locations,
                                 Pageable pageable);

And then when the endpoint is called with

https://localhost:8080/api/v1/stores/search/searchLocations?locations=https://localhost:8080/api/v1/locations/23,https://localhost:8080/api/v1/locations/24

Only 24 is returned, not 23.

How do you actually supply a list parameter to the JPA query?

Have you tried: `locations=https://localhost:8080/api/v1/locations/23&locations=https://localhost:8080/api/v1/locations/24` — Alan Hay, Dec 15 '20 at 16:09
I know this works with the QueryDsl extension. Don't know if you have looked at that. Essentailly would give you query capability out of the box without having to create the search methods in the repository. Not answer, but maybe worth looking at.... — Alan Hay, Dec 15 '20 at 16:16
Are you restricted to use `List` or it is possible to switch to an array `Location[]`? — hjoeren, Dec 15 '20 at 18:00
Array is fine as long as there is a way to call the endpoint — erotsppa, Dec 15 '20 at 18:58

hjoeren · Accepted Answer · 2020-12-15T20:29:04.113

According to https://stackoverflow.com/a/48504735/2201165 varargs works out of the box (if you would avoid the Pageable parameter). Because an array and varargs are pretty much the same and you also want to pass a Pageable to the method, I tried to use arrays:

You can change the parameter to Location[] locations so that the signature looks like this:

@Query("Select loc from Location loc where loc in :locations")
public Page<Location> searchLocations(Location[] locations, Pageable pageable);

With this, you are able to call the endpoint in the way @alan-hay mentioned in his comment:

curl -X GET "https://localhost:8080/api/v1/stores/search/searchLocations?locations=https://localhost:8080/api/v1/locations/24&locations=https://localhost:8080/api/v1/locations/23"

Here some SQL output to show what's executed:

Hibernate: select location0_.id as id1_0_ from location location0_ where location0_.id in (? , ?) limit ?

Note

Because you are able to access the locations via an own endpoint (http://localhost:8080/api/v1/locations/{locationId}) it is likely that there is an extra (Spring Data Rest) repository for the locations itself. In this case this is also possible:

public interface LocationRepository extends JpaRepository<Location, Long> {
  // Here you are also able to use Long[] instead of List<Long>
  public Page<Location> findByIdsIn(List<Long> ids, Pageable pageable);
}

With that, you can search the repository like this

curl -X GET "https://localhost:8080/api/v1/locations/search/findByIdsIn?ids=24,23"

How to use list parameter in JPA query with Spring Data Rest?

1 Answers1