java - 使用计数实现 JPA 投影

Question

我想用计数实现 JPA 投影。我试过这个:

@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: 你的 SQL 语法有错误；查看与您的 MariaDB 服务器版本对应的手册，了解在第 1 行的“.plugin.service.PaymentTransactionsDeclineReasonsDTO( id, count(id) as c”附近使用的正确语法

当我有基于类的投影时，如何实现正确的计数？

Answer 1

尝试 Interface-based Projection而不是 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)——它们是强制性的。