Dynamic Query JPA 2 Specification With CriteriaBuilder

Question

I have below table 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
Fanta   Out     55

and I want to get result like below:

Product Qty
Pepsi   18
Coke    90
Fanta   -55

for that I have got below query:

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;

I want to use JPA Specification like used in this link, I am new to JPA Specification can someone please guide me?

I have got this link and this link but I am not sure what is the correct question to solve the problem?

EDIT:

I have tried below code

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

Call it from as pageable

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

which is making below query :-

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

I am getting SQL Exception due to case statement is after where clause. I dont know why spring put case after the where clause.

I am trying to make dynamic query, as i cant use static query.

Answer 1

Essentially you are looking to perform a Native Query.

In particular, you could use the JPA feature SqlResultSetMapping to define the mapping between your native query and the DTO object that can hold your data.

Assuming the DTO for your needs looks like this:

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

The following definition can act as a mapping for the native query:

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

And finally the query can be invoked as:

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());
}

For more information/examples take a look at this article.