Variables in Spring Data JPA native query

Question

Using Spring Dat JPA, I need to query my database and return a range of OrderEntitys based on a startAmt and a endAmt of amounts. I'm not sure if I should map these two variables to entity OrderEntity, as fields in some type of separate class/entity/model, or simply declare them in my native query. Perhaps I should be using a service that implements EntityManager.createNativeQuery()?

Would like to do something like :

@Repository
public interface OrderRangeRepository extends JpaRepository<OrderEntity, OrderEntityID> {

        @Query(value = "SELECT * FROM Orders WHERE Amount BETWEEN startAmt AND endAmt;" , nativeQuery=true)
    List<OrderEntity> findOrdersBy(int startAmt, int endAmt);

}

If I were to use EntityManager.createNativeQuery() in a service, perhaps something like below :

@Service
public class OrderRangeService {

    @Autowired
    EntityManager entityManager;

    public List<OrderEntity> findAmountsBetween() {

        List<OrderEntity> amountsBetween = entityManager.createNativeQuery("SELECT * FROM Orders WHERE Amount BETWEEN ?1 AND 2?;")
        .setParameter(1, "startAmt")
        .setParameter(2, "endAmt")
        .getResultList();

        return amountsBetween;


    }

}

Solution 1

You can achieve this with Spring Data JPA without defining a native query.

@Repository
public interface OrderRangeRepository extends JpaRepository<OrderEntity, OrderEntityID> {
    List<OrderEntity> findByAmountBetween(int startAmt, int endAmt);
}

If you want to use the native query change it to

 @Query(value = "SELECT * FROM Orders WHERE Amount BETWEEN :startAmt AND :endAmt" , nativeQuery=true)
List<OrderEntity> findOrdersBy(@Param("startAmt") int startAmt, @Param("endAmt") int endAmt);

You can invoke the query in a service by doing

@Service
public class OrderRangeService {

    @Autowired
    OrderRangeRepository orderRangeRepository ;

    public List<OrderEntity> findAmountsBetween(int startAmt, int endAmt) {
        List<OrderEntity> amountsBetween = orderRangeRepository.findByAmountBetween(startAmt, endAmt);
        return amountsBetween;
    }

}

Finally, from your controller, you should autowire the OrderRangeService and invoke the findAmountsBetween service method

@Autowired
OrderRangeService orderRangeService;

@GetMapping("/amountsFromAndTo")
@ResponseBody
public String getAmounts(@RequestParam int startAmt, @RequestParam int endAmt) {
    List<OrderEntity> orderEntityL = orderRangeService.findAmountsBetween(startAmt, endAmt);
    return orderEntityL.toString();
}

Solution 2

1. Named Parameters

Each parameter annotated with @Param must have a value string matching the corresponding JPQL or SQL query parameter name. A query with named parameters is easier to read and is less error-prone in case the query needs to be refactored.

@Query(value = "SELECT * FROM Orders WHERE Amount BETWEEN :startAmt AND :endAmt;" , nativeQuery=true)
List<OrderEntity> findOrdersBy(@Param("startAmt") int startAmt, @Param("endAmt") int endAmt);

}

2. Indexed Query Parameters

Spring Data will pass method parameters to the query in the same order they appear in the method declaration

@Query(value = "SELECT * FROM Orders WHERE Amount BETWEEN ?1 AND ?2;" , nativeQuery=true)
List<OrderEntity> findOrdersBy(int startAmt, int endAmt);

Comments

[wallwalker]

--Using findByAmountBetween(1,2) results in a query with numbers between 1 & 2. How could I make this work from the HTTP Query String such as in http://localhost:8080/amountsFromAndTo?startAmt=1000&endAmt=‌​2000 please?And I'm using your second example, native query.

[Ioannis Barakos]

The 1,2 was an example to show how to pass the from-to between values. You can replace the 1,2 with int variables. I have updated my answer (second example). You should pass these variables from your controller to the service and to JPA repository