Rewrite query in JPA

Question

I want to rewrite this SQL query in JPA.

String hql = "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)";

        TypedQuery<Merchants> query = entityManager.createQuery(hql, Merchants.class);
        List<Merchants> merchants = query.getResultList();

Is there a way to rewrite the queries into JPA or I should use it as it is?

Answer 1

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)

Answer 2

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