Spring Data JPA 本机查询中的变量

Question

OrderEntity使用 Spring Dat JPA，我需要查询数据库并根据 astartAmt和 aendAmt金额返回一系列s。我不确定是否应该将这两个变量映射到实体OrderEntity，作为某种类型的单独类/实体/模型中的字段，或者只是在我的本机查询中声明它们。也许我应该使用实现的服务EntityManager.createNativeQuery()？

想做类似的事情：

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

}

如果我要EntityManager.createNativeQuery()在服务中使用，也许如下所示：

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

    }

}

Answer 1

您可以使用 Spring Data JPA 来实现此目的，而无需定义本机查询。@Repositorypublic interface OrderRangeRepository extends JpaRepository<OrderEntity, OrderEntityID> {&nbsp; &nbsp; List<OrderEntity> findByAmountBetween(int startAmt, int endAmt);}如果您想使用本机查询，请将其更改为&nbsp;@Query(value = "SELECT * FROM Orders WHERE Amount BETWEEN :startAmt AND :endAmt" , nativeQuery=true)List<OrderEntity> findOrdersBy(@Param("startAmt") int startAmt, @Param("endAmt") int endAmt);您可以通过执行以下操作来调用服务中的查询@Servicepublic class OrderRangeService {&nbsp; &nbsp; @Autowired&nbsp; &nbsp; OrderRangeRepository orderRangeRepository ;&nbsp; &nbsp; public List<OrderEntity> findAmountsBetween(int startAmt, int endAmt) {&nbsp; &nbsp; &nbsp; &nbsp; List<OrderEntity> amountsBetween = orderRangeRepository.findByAmountBetween(startAmt, endAmt);&nbsp; &nbsp; &nbsp; &nbsp; return amountsBetween;&nbsp; &nbsp; }}最后，从您的控制器中，您应该自动装配 OrderRangeService 并调用 findAmountsBetween 服务方法@AutowiredOrderRangeService orderRangeService;@GetMapping("/amountsFromAndTo")@ResponseBodypublic String getAmounts(@RequestParam int startAmt, @RequestParam int endAmt) {&nbsp; &nbsp; List<OrderEntity> orderEntityL = orderRangeService.findAmountsBetween(startAmt, endAmt);&nbsp; &nbsp; return orderEntityL.toString();}

Answer 2

1. 命名参数每个用 @Param 注释的参数必须具有与相应的 JPQL 或 SQL 查询参数名称匹配的值字符串。具有命名参数的查询更易于阅读，并且在需要重构查询时更不易出错。@Query(value&nbsp;=&nbsp;"SELECT&nbsp;*&nbsp;FROM&nbsp;Orders&nbsp;WHERE&nbsp;Amount&nbsp;BETWEEN&nbsp;:startAmt&nbsp;AND&nbsp;:endAmt;"&nbsp;,&nbsp;nativeQuery=true) List<OrderEntity>&nbsp;findOrdersBy(@Param("startAmt")&nbsp;int&nbsp;startAmt,&nbsp;@Param("endAmt")&nbsp;int&nbsp;endAmt); }2. 索引查询参数Spring Data 将按照方法参数在方法声明中出现的顺序将方法参数传递给查询@Query(value&nbsp;=&nbsp;"SELECT&nbsp;*&nbsp;FROM&nbsp;Orders&nbsp;WHERE&nbsp;Amount&nbsp;BETWEEN&nbsp;?1&nbsp;AND&nbsp;?2;"&nbsp;,&nbsp;nativeQuery=true)List<OrderEntity>&nbsp;findOrdersBy(int&nbsp;startAmt,&nbsp;int&nbsp;endAmt);