Reputation: 443
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);
Upvotes: 32
Views: 110883
Reputation: 151
In my case with help of EntityManager native query works with named parameters:
Query query = em.createNativeQuery("select * from MY_TABLE where name = :name", TextEntity.class);
query.setParameter("name", "Axel");
query.setMaxResults(10);
query.setFirstResult(0);
List resultList = query.getResultList();
Upvotes: 0
Reputation: 11
@Query(value = "SELECT * FROM db.issuer_details where creation_date between ?1 AND ?2", nativeQuery = true)
List<MerchantData> dataWithDate(String from ,String to);
Upvotes: 1
Reputation: 19173
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.
Upvotes: 37