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);
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.