java - 使用 CriteriaBuilder 的动态查询 JPA 2 规范

Question

我有以下表格数据:

Product Reason Qty
Pepsi   IN     10
Pepsi   Out    2
Pepsi   In     15
Pepsi   Out    5 
Coke    IN     100
Coke    Out    20
Coke    In     35
Coke    Out    25
Fanta   Out     55

我想要得到如下结果:

Product Qty
Pepsi   18
Coke    90
Fanta   -55

为此我得到了以下查询:

SELECT Product,
       SUM(CASE WHEN reason IN ('IN','REFUND') THEN Qty 
                WHEN reason IN ('OUT','WASTE') THEN -Qty
                ELSE NULL END) AS Qty
FROM stock
GROUP BY Product;

我想使用 JPA 规范，就像 link 中使用的那样，我是JPA规范的新手，有人可以指导我吗？

我有这个link还有这个link但我不确定解决问题的正确问题是什么？

编辑:

我尝试过以下代码

@Override
    public Predicate toPredicate(Root<Stockdiary> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

    Path reasonexpression = root.get("reason");
                    Path qtyexpression = root.get("qty");

                    List<String> inList = new ArrayList<String> ();
                    inList.add("IN");

                    List<String> outList = new ArrayList<String> ();
                    outList.add("OUT");

                    Predicate inpredicate = reasonexpression.in(inList);
                    Predicate outpredicate = reasonexpression.in(outList);

                    Expression<Number> sum = builder.sum(
                            builder.<Number>selectCase()  
                                 .when(inpredicate,qtyexpression.as(Double.class))  
                                 .when(outpredicate,builder.neg(qtyexpression).as(Double.class))  
                                 .otherwise(0));

return builder.equal(sum.as(Integer.class), 1)
}

从可分页调用它

Page<Stockdiary> page = stockRepository.findAll(spec,pageable);

正在执行以下查询:-

select count(stock0_.stock_id) as col_0_0_ from stock stock0_ cross join session_tbl session_tb1_ 
where stock0_.session_id=session_tb1_.session_id and 
cast(sum(case when stock0_.reason in (?) then stock0_.qty when stock0_.reason in (?) then -stock0_.qty else 101 end) as signed)=1 
group by stock0_.reason , stock0_.products_id , session_tb1_.terminal_id

由于 case 语句位于 where 子句之后，我收到 SQL 异常。我不知道为什么 spring 在 where 子句之后放置 case 。

我正在尝试进行动态查询，因为我无法使用静态查询。

Answer 1

本质上，您正在寻求执行 Native Query .

特别是，您可以使用 JPA 功能 SqlResultSetMapping 来定义 native 查询和可保存数据的 DTO 对象之间的映射。

假设满足您需求的 DTO 如下所示:

class ProductQuantity {
   private String product;
   private Integer quantity;

   public ProductQuantity(String product, Integer quantity) {
      this.product = product;
      this.quantity = quantity;
   }

   public getProduct() { return this.product; }

   public getQuantity() { return this.quantity; }
}

以下定义可以充当 native 查询的映射:

@SqlResultSetMapping(
   name = "ProductQuantityMapping",
   classes = @ConstructorResult(
         targetClass = ProductQuantity.class,
         columns = {
            @ColumnResult(name = "product", type = String.class),
            @ColumnResult(name = "qty", type = Integer.class)
         })
)

最后可以调用查询:

Query q = entityManager.createNativeQuery("SELECT Product,SUM(CASE WHEN reason IN ('IN','REFUND') THEN Qty WHEN reason IN ('OUT','WASTE') THEN -Qty ELSE NULL END) AS Qty FROM stock GROUP BY Product");

List<ProductQuantity> result = q.getResultList();

for(ProductQuantity pq : result) {
   System.out.println("product: " + pq.getProduct() + ", Quantity: " + pq.getQuantity());
}

有关更多信息/示例，请查看此 article .