jpa - 在 JPA 中重写查询

Question

我想在 JPA 中重写这个 SQL 查询。

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

有没有办法将查询重写为 JPA 或者我应该按原样使用它？

Answer 1

在这种情况下，通常最好的方法是编写一个简单的 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);

并将其映射到 @Immutable实体。这种方法适用于以下情况:

您只读了数据

View 不需要参数(在这种情况下，也有从 hacky 到 nice 的解决方案)