Implement JPA Projection with count

Question

I want to implement JPA Projection with count. I tried this:

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

But I get error: 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

How I can implement proper count when I have class based Projection?

remove space between **=** and **:** as below `WHERE terminal_id =:id AND (created_at >:created_at) " +` — TheSprinter, Jul 22 '19 at 13:54
once i was getting similar error and resolved by doing this, u can try. also remove space between > & : — TheSprinter, Jul 22 '19 at 13:59

Cepr0 · Answer 1 · 2019-07-23T15:15:19.920

Try Interface-based Projection instead of 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);

Pay attention on aliases (i.e. id as id) - they are mandatory.

score 2 · Answer 2 · answered Jul 22 '19 at 14:09

You are mixing JPQL and SQL syntax.

The constructor expression (new ...) is JPQL, but in the annotation you mark it as a nativeQuery i.e. as SQL so you have to make up your mind.

If it is to be SQL I don't think you can use aliases in the ORDER BY clause, so you might have to either repeat the expression or wrap it in a subselect as described here: Using an Alias in a WHERE clause.

If it is to be JPQL it doesn't support aliases in a constructor expression, so I guess you have to repeat the expression in the order by clause.

I'm stuck. Can you give a hand please? https://stackoverflow.com/questions/57149382/querysyntaxexception-expecting-close-found — Peter Penzov, Jul 22 '19 at 15:41

Implement JPA Projection with count

2 Answers2

Linked