使用JPA Projection实现计数

Question

我希望实现JPA投影并计数。我尝试了以下代码：

Original Answer 翻译成“最初的回答”：

@Query(value = "SELECT new org.service.PaymentTransactionsDeclineReasonsDTO( id, count(id) as count, status, error_class, error_message) " +
        " FROM payment_transactions " +
        " WHERE terminal_id = :id AND (created_at > :created_at) " +
        " AND (status != 'approved') " +
        " GROUP BY error_message " +
        " ORDER BY count DESC", nativeQuery = true)
List<PaymentTransactionsDeclineReasonsDTO> transaction_decline_reasons(@Param("id") Integer transaction_unique_id, @Param("created_at") LocalDateTime created_at);

但是我遇到了错误：Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.plugin.service.PaymentTransactionsDeclineReasonsDTO( id, count(id) as c' at line 1

当我拥有基于类的Projection时，我该如何实现正确的计数？

最初的回答：

您的SQL语句中存在语法错误。在使用基于类的Projection时，如果要进行计数操作，请确保您的SQL语法正确。请参考MariaDB服务器版本的手册以获得正确的语法。

- Peter Penzov

---

WHERE terminal_id=:id AND (created_at>:created_at) " + - TheSprinter

你确定吗？错误是在第一行的count(id) as c'吗？ - Peter Penzov

我曾经遇到过类似的错误，并通过这样做解决了，你可以试试。还要删除">"和":"之间的空格。 - TheSprinter

Answer 1

尝试使用基于接口的投影而不是DTO：

public interface TransactionDeclineReason {
   Integer getId();
   Long getCount();
   Status getStatus();
   ErrorClass getErrorClass(); // I suppose it's enum...
   String getErrorMessage();
}

@Query(value = "select " +
                 "id as id, " + 
                 "count(id) as count, " + 
                 "status as status, " + 
                 "error_class as errorClass, " + 
                 "error_message as errorMessage " +
               "from " +
                 "payment_transactions " +
               "where " +
                 "terminal_id = ?1 " + 
                 "and created_at > ?2 " +
                 "and status != 'approved' " +
               "group " + 
                 "by error_message " +
               "order by " +
                 "2 desc", nativeQuery = true)
List<TransactionDeclineReason> getTransactionDeclineReasons(Integer transactionId, LocalDateTime createdAt);

请注意别名（例如id as id）-它们是必需的。

- Cepr0

Answer 2

你混淆了JPQL和SQL语法。

构造函数表达式（new ...）是JPQL，但在注释中将其标记为nativeQuery，即SQL，因此你需要决定。

如果它是SQL，我认为你不能在ORDER BY子句中使用别名，因此你可能需要重复表达式或像“在WHERE子句中使用别名”中所述的那样将其包装在子查询中。

如果它是JPQL，则不支持构造函数表达式中的别名，因此我想你必须在order by 子句中重复表达式。

- Jens Schauder

---

我卡住了。你能帮个忙吗？https://dev59.com/lbXna4cB1Zd3GeqPHj4O - Peter Penzov