javasqlspringjpaspring-data-jpa

How to pass parameters in a native query JPA


I have service class which receives list of cars by calling JPA repository with carRepository.retrieveCars(). Repository method is using native query to retrieves records.

public interface CarRepository extends JpaRepository<Car, String> {
    @Query(nativeQuery = true,
           value = "select *" +
                   "from car_records")
 }
 List<Car> retrieveCars(); 

Now I would like to pass parameters carRepository.retrieveCars(Long vinNo, Long serialNo) and use them in a query. I assume that I will need something as a prepared statements. However Im not sure how to implement that.

public interface CarRepository extends JpaRepository<TRace, String> {
    @Query(nativeQuery = true,
           value = "select *" +
                   "from car_records" +
                    "where carVinNo = ?! and carSerialNo >= ?1")
 }
 
 query.setParameter(1, vinNo, 2,serialNo);   //this is certainly not correct implementation
 List<Car> retrieveCars(vinNo, serialNo); 

Solution

  • There are two ways around that when you use Spring Data JPA

    1) Named Parameters

    public interface CarRepository extends JpaRepository<TRace, String> {
            @Query(nativeQuery = true,
                   value = "select *" +
                           "from car_records" +
                            "where carVinNo = :vinNo and carSerialNo >= :serialNo")
         }
         List<Car> retrieveCars(@Param("vinNo") Long vinNo,@Param("serialNo") Long serialNo); 
        }
    

    spring doc for named parameters

    2) Indexed Parameters

    public interface CarRepository extends JpaRepository<TRace, String> {
        @Query(nativeQuery = true,
               value = "select *" +
                       "from car_records" +
                        "where carVinNo = ?1 and carSerialNo >= ?2")
     }
     List<Car> retrieveCars(Long vinNo, Long serialNo); 
    }
    

    example for index parameter from spring doc

    Then from your service class you call it

    carRepository.retrieveCars(vinNo, serialNo);
    

    Both cases will work the same for you.