CODEWITHSUNDEEP
javasqlspringjpaspring-data-jpa
Mar
Mar

Reputation: 443

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

Upvotes: 32

Views: 110883

Answers (3)

Axel
Axel

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

vishvajeet.rajput
vishvajeet.rajput

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

Panagiotis Bougioukos
Panagiotis Bougioukos

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

Related Questions