How to pass parameters in a native query JPA

user_15140588 0
Viewed 36123

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);
spring-data-jpa jpa spring sql java
1 Answers

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.

user_72378840
Related