Parameter handling in custom query Spring Data JPA

Question

Creating a custom native SQL query in Spring Boot with Spring Data JPA. Would like to search office_products table with an order_num and return associated row(s) of data(in the real world it wouldn't make sense to have multiple orders with the same order number but in this example let's just say 'Yes' so we can return a list).

Using this Controller :

@Controller
public class OrderController {

@Autowired
private OrderRepository orderRepository;

@GetMapping("/test")
public String getOrderListByNum(Model model) {
     List<OrderEntity> foundByOrderNo = orderRepository.getOrderByOrderNum();
     model.addAttribute("foundByOrderNo", foundByOrderNo);
     return "test";

}

}

I can successfully query the database and return a result when the query is hard coded with order_num value of 12354 like below :

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, OrderID> {

     @Query(value ="SELECT * FROM office_products WHERE order_num=12354", nativeQuery = true)
     List<OrderEntity> getOrderByOrderNum();
} 

However, when attempting to use a parameter by passing in a value via http://localhost:8080/test?orderNum=12354, like below the code doesn't work :

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, OrderID> {

     @Query(value ="SELECT * FROM office_products WHERE order_num = :orderNum", nativeQuery = true)
     List<OrderEntity> getOrderByOrderNum();
} 

When attempting to use parameter I get back the following errors :

@http://localhost:8080/test?orderNum=12354 :

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Mon Oct 14 12:07:44 CDT 2019
There was an unexpected error (type=Internal Server Error, status=500).
Named parameter not bound : orderNum; nested exception is org.hibernate.QueryException: Named parameter not bound : orderNum
org.springframework.dao.InvalidDataAccessResourceUsageException: Named parameter not bound : orderNum; nested exception is org.hibernate.QueryException: Named parameter not bound : orderNum

In console :

[ERROR]~2019-10-14-12.07.44.569CDT~~~~~~ o.a.c.c.C.[.[.[.[dispatcherServlet] Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: Named parameter not bound : orderNum; nested exception is org.hibernate.QueryException: Named parameter not bound : orderNum] with root cause
org.hibernate.QueryException: Named parameter not bound : orderNum

Lastly, passing in @Param("orderNum") String orderNum to method List<OrderEntity> getOrderByOrderNum(); I get a new error :

@http://localhost:8080/test?orderNum=12354 :

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Mon Oct 14 12:22:11 CDT 2019
There was an unexpected error (type=Internal Server Error, status=500).
Unresolved compilation problem: The method getOrderByOrderNum(String) in the type OrderRepository  is not applicable for the arguments ()
java.lang.Error: Unresolved compilation problem: 
    The method getOrderByOrderNum(String) in the type OrderRepository is not applicable for the arguments ()

Any idea what I am missing here?

---

UPDATE:

After digging in deeper, I realized I needed to add the following code to my controller and using the JPQL example as suggested by Shabbir the code now works :

@GetMapping("/test")
public String getOrderListByNum(Model model, OrderEntity orderEntity) {
     List<OrderEntity> foundByOrderNo = orderRepository.getOrderByOrderNum(orderEntity.getOrderNo());
     model.addAttribute("foundByOrderNo", foundByOrderNo);
     return "test";

}

UPDATE :

And of course there is the Derived Query solution :

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, OrderID> {

     List<OrderEntity> findByOrderNo(String orderNo);
} 

Answer 1

Try this

@Query(value ="SELECT * FROM order_entity WHERE order_num = :orderNum", nativeQuery = true)

List<OrderEntity> getOrderByOrderNum(@Param(value="orderNum") String orderNum);

Better to use JPQL instead of native query which would be like:

@Query("SELECT op FROM OrderEntity op WHERE orderNum = :orderNum")

List<OrderEntity> getOrderByOrderNum(@Param(value="orderNum") String orderNum);