JPA规范复杂查询 [英] JPA Specification complex query

问题描述

我有以下资料： -

 产品原因数量
百事可乐10 
百事可乐2 
百事可乐15 
百事可乐5 
可乐100 
可乐20 
可乐35 
可口可乐25 
  
 
从JPA规范我想执行下面的查询如下所示： -  $ / $> 
 
 
  select * from（
（从股票中选择SUM（QTY），其中的原因如'IN'）作为INDATA  -  
（从股票中选择SUM（QTY）像'OUT'）OUTDATA 
 
）作为TBL; 
  
结果如下： -  $ / 
 
 
 产品数量
百事可乐18 
可乐90 
  
我第一次使用JPA规范，我不知道如何执行查询以从股票表中获取当前股票。     解决方案

假设您有一个实体，比如 Beverage ，它映射到上面的表<$ c



  @Entity 
 @Table（name =Stock ）
 class Beverage {
 public Beverage（String品牌，字符串原因，int数量）{
 this.brand =品牌; 
 this.reason =原因; 
 this.quantity = quantity; 
 
 
 public Beverage（）{} 
 
 @Id 
 @GeneratedValue 
整数id; 
 
弦乐品牌; 
字符串原因; 
 int数量; 
} 
  

您可以使用 case语句和sum ，如下所示： JPQL ：



 查询查询= entityManager.createQuery（通过b.brand从饮料b组中选择新地图（b.brand作为品牌，总和（情况b.reason'IN'然后b.quantity else -b.quantity结束）作为数量） 
列表< Map> list = query.getResultList（）; （Map m：list）
 
 System.out.println（m.get（brand）+ - + m.get（quantity））; 
  




I have below data :-

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

and from JPA Specification i want to execute below query something like below :-

select * from (
(select SUM(QTY) from stock where reason like 'IN') as INDATA -
(select SUM(QTY) from stock where reason like 'OUT') as OUTDATA

) as TBL;

result will be below :-

Product Qty
Pepsi   18
Coke    90

I am using JPA Specification for the first time and i dont no idea how to execute the query to get current stock from stock table.

解决方案

Assuming that you have an Entity, say Beverage, that is mapped to the above table Stock as :

@Entity
@Table(name="Stock")
class Beverage {
    public Beverage(String brand, String reason, int quantity) {
        this.brand = brand;
        this.reason = reason;
        this.quantity = quantity;
    }

    public Beverage() {}

    @Id
    @GeneratedValue
    Integer id;

    String brand;
    String reason;
    int quantity;
}

You can use case statement along with sum as below with JPQL:

Query query = entityManager.createQuery("select new map(b.brand as brand, sum(case b.reason when 'IN' then b.quantity else -b.quantity end) as quantity)  from Beverage b group by b.brand");
List<Map> list = query.getResultList();
for(Map m : list)
    System.out.println(m.get("brand") + "-" + m.get("quantity"));

这篇关于JPA规范复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！