How to pass multiple query parameters in @Query in Spring Data JPA?

user_17573032 0
Viewed 2670

I am using nativeQuery in my repository which fetches data from multiple tables using join and it also checks for inputs sent by the user within the query. My Repository layer looks something like:

@Query(
  value = "SELECT e.empname,c.countryName,r.RegionName
  FROM Employee e,Country c, Region r
  WHERE e.countryId=c.countryId
  AND c.regionId=r.regionId
  AND e.employeeId IN (:empIds)
  AND c.countryId IN (:countryIds)
  AND r.regionId IN (:regionIds)", 
  nativeQuery = true)
Collection<Object> findAllActiveUsersNative(CustomFilterRequest request,Pageable pageable);

Following is my filter Class which are lists of IDs sent from the UI and this list properties of class should be sent to query for further filtering.

class CustomFilterRequest{
List<Long> empIds,
List<Long> countryIds,
List<Long> regionIds,
}

I know there's @Param which can be used for sending individual query parameters but how to send multiple query parameters as in the scenario mentioned above? Is there any way to map properties of this class to query parameters in the nativeQuery of @Query?

spring-data-jpa spring-boot jpa hibernate java
2 Answers

If it's not manadatory for you to pass the entire CustomFilterRequest object as a single parameter, how about the following?

@Query(
  value = "SELECT e.empname,c.countryName,r.RegionName
  FROM Employee e,Country c, Region r
  WHERE e.countryId=c.countryId
  AND c.regionId=r.regionId
  AND e.employeeId IN :empIds
  AND c.countryId IN :countryIds
  AND r.regionId IN :regionIds", 
  nativeQuery = true)
Collection<Object> findAllActiveUsersNative(
  @Param("empIds") List<Long> empIds,
  @Param("countryIds") List<Long> countryIds,
  @Param("regionIds") List<Long> regionIds,
  Pageable pageable);

And then in your service class:

repository.findAllActiveUsersNative(
  customFilterRequest.getEmpIds(),
  customFilterRequest.getCountryIds(),
  customFilterRequest.getRegionIds(),
  pageable);
user_94255560

You can use SpEL expressions.

  @Query(
      value = "SELECT e.empname,c.countryName,r.RegionName
      FROM Employee e,Country c, Region r
      WHERE e.countryId=c.countryId
      AND c.regionId=r.regionId
      AND e.employeeId IN (:#{#request.empIds})
      AND c.countryId IN (:#{#request.countryIds})
      AND r.regionId IN (:#{#request.regionIds})", 
      nativeQuery = true)
    Collection<Object> findAllActiveUsersNative(CustomFilterRequest request,Pageable pageable);
user_76790070
Related