java - 自定义查询 Spring Data JPA 中的参数处理

Question

使用 Spring Data JPA 在 Spring Boot 中创建自定义 native SQL 查询。想搜索office_products表 order_num并返回关联的数据行(在现实世界中，具有相同订单号的多个订单是没有意义的，但在本例中，我们只需说"is"，这样我们就可以返回一个列表)。

使用此 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";

}

}

当使用 order_num 硬编码查询时，我可以成功查询数据库并返回结果12354 的值如下所示:

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

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

但是，当尝试通过 http://localhost:8080/test?orderNum=12354 传入值来使用参数时，如下所示，代码不起作用:

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

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

当尝试使用参数时，我收到以下错误:

@ 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

在控制台中:

[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

最后传入@Param("orderNum") String orderNum方法List<OrderEntity> getOrderByOrderNum();我收到一个新错误:

@ 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 ()

知道我在这里缺少什么吗？

<小时/>

更新:

深入研究后，我意识到我需要将以下代码添加到我的 Controller 中，并使用 Shabbir 建议的 JPQL 示例，代码现在可以工作了:

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

}

更新:

当然还有派生查询解决方案:

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

     List<OrderEntity> findByOrderNo(String orderNo);
} 

Answer 1

试试这个

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

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

最好使用 JPQL 而不是 native 查询，如下所示:

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

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