Rewrite query in JPA

In situations like these, more often than not the best approach is to write a plain SQL view:

CREATE OR REPLACE VIEW payment_transactions_stats AS
SELECT date(created_at) AS cdate, sum(amount) AS amount, count(id) AS nooftransaction
FROM payment_transactions
WHERE date(created_at)>=date(now()- interval 10 DAY)
AND date(created_at)<date(now()) GROUP BY date(created_at);

And map it to an @Immutable entity. This approach works well when:

• you have read only data
• the view does not need parameters (in this case there are solutions as well which span from hacky to nice)

You provide no details about the classes and entities but it could be something like:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = builder.createTupleQuery();
From<PaymentTransaction> tx = query.from(PaymentTransaction.class);
Expression<Long> sumAmount = builder.sum(tx.get("amount"));
Expression<Long> count = builder.count(tx.get("id"));
Expression<Date> createdAt = tx.get("created_at");
query.multiselect(createdAt, sumAmount, count);
query.where(builder.greaterThanOrEqualTo(createdAt, builder.function("DATEADD", "DAY", new Date(), builder.literal(-10))),
    builder.lessThan(createdAt, new Date()));
query.groupBy(createdAt);

entityManager.createQuery(query).getResultList().stream()
    .map(t -> new Merchants(t.get(0, Date.class), t.get(1, Long.class), t.get(2, Long.class)))
    .collect(Collectors.toList());

It is better not to use JPA for complex queries like this. JPA are usually used for simple queries.

Since the question is tagged with spring-data-jpa, you could try using a Spring CRUDRepository on top of your table. In the CRUDRepository, write a custom method with the @Query annotation.

It's hard for me to formulate the entire query because I don't know the members of your Merchants class.

Alternatively you can set the nativeQuery = true for the @Query annotation and use actual DB query to solve your problem.

You can use below code

CriteriaBuilder qb = entityManager.getCriteriaBuilder();
CriteriaQuery cq = qb.createQuery();
Root paymentInstructionsRoot = cq.from(PaymentInstructions.class);
List<Predicate> predicates = new ArrayList<>();
predicates.add(qb.greaterThanOrEqualTo(path, fromDateRange));
predicates.add(qb.lessThanOrEqualTo(path, toDateRange));

Selection cdate = paymentInstructionsRoot.get(PaymentInstructions_.createdAt).alias("cdate");
Selection amount = qb.sum(paymentInstructionsRoot.get(PaymentInstructions_.amount))).alias("amount");
Selection nooftransaction = qb.count(paymentInstructionsRoot.get(PaymentInstructions_.id))).alias("nooftransaction");

Selection[] selectionExpression = {cdate, amount, nooftransaction};
Expression[] groupByExpression = {paymentInstructionsRoot.get(PaymentInstructions_.createdAt)};
cq.multiselect(selectionExpression).where(predicates.toArray(new Predicate[]{})).groupBy(groupByExpression).where(predicates.toArray(new Predicate[]{}));

List<PaymentInstructions> paymentInstructions = entityManager.createQuery(cq).getResultList();

In your Entity class that represents the 'payment_transactions' table, add the following:

@SqlResultSetMapping(
    name = "PaymentTransaction.summaryMapping",
    classes = {
        @ConstructorResult(targetClass = PaymentTransactionSummary.class,
                columns = {
                    @ColumnResult(name = "cdate")
                    , @ColumnResult(name = "amount")
                    , @ColumnResult(name = "nooftransaction")
                })
    }         
)

Create a new pojo class named PaymentTransactionSummary (must match the name used above, or whatever name you choose, with member fields cdate, amount, and nooftransaction. Include a constructor that includes those three fields in the order listed above.

Then in your dao class, write this:

Query q = entityManager.createNativeQuery("your query string from above"
    , "PaymentTransaction.summaryMapping");
List<PaymentTransactionSummary> results = q.getResultList();