spring jpa @Query error, expecting CLOSE, found '('-CodePudding

I have a following mysql query which works fine:

*Edit: the path is just for illustration purpose... I did not want to copy my packages...

select COUNT(IF(amount > 0, amount, 0)) as creditCount,
       COUNT(IF(amount < 0, amount, 0)) as debitCount,
       SUM(IF(amount > 0, amount, 0)) as credit,
       SUM(IF(amount < 0, amount, 0)) as debit,
       SUM(amount)                    as balance,
       cp.surname, cp.id, pcp.number from prepaid_card_transaction_pojo pctp
         join prepaid_card_pojo pcp on pctp.account = pcp.id
         join customer_pojo cp on pcp.id = cp.prepaid_card
where pcp.number = '1000765352' AND pctp.client = UUID_TO_BIN('fb9dbcac-cd03-46ad-94b0-2709b0b0e2a8') AND booking_time > '2020-12-01 00:00:00' group by cp.id;

I wish to transfer it to jpql:

@Query("select new path...Bean("   //
            "COUNT(IF(pctp.amount > 0, pctp.amount, 0)),"   //
            "COUNT(IF(pctp.amount < 0, pctp.amount, 0)), "   //
            "SUM(IF(pctp.amount > 0, pctp.amount, 0)), "   //
            "SUM(IF(pctp.amount < 0, pctp.amount, 0)), "   //
            "SUM(pctp.amount), cp) "   //
            "FROM PrepaidCardTransactionPojo pctp "   //
            "JOIN PrepaidCardPojo pcp ON pctp.account = pcp.id "   //
            "JOIN CustomerPojo cp ON pcp.id = cp.prepaid_card "   //
            "WHERE pcp.number =:voucherNumber AND pctp.client =:client AND booking_time >= :from AND booking_time < :to GROUP BY :customer")
    List<Bean> loadGroupedCustomerPrepaidTransactions(@Param("client") ClientReadable client, @Param("customer") CustomerReadable customer, @Param("voucherNumber") String voucherNumber, @Param("from") Date from, @Param("to") Date to);

I am getting a following error: ...QuerySyntaxException: expecting CLOSE, found '(' near...

CodePudding user response：

I don't think JPQL has if - so you would have to use CASE WHEN ... ELSE ..., like so:

SELECT e.name, CASE WHEN (e.salary >= 100000) THEN 1 WHEN (e.salary < 100000) THEN 2 ELSE 0 END from Employee e

more here https://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#New_in_JPA_2.0

CodePudding user response：

Try adding nativeQuery = true which indicates that query is native MySql query, not Jpql query.

@Query("select ...", nativeQuery = true)
    List<Bean> loadGroupedCustomerPrepaidTransactions(...);

CodePudding user response：

Try to like this :

@QUERY(nativeQuery = true, value = "select COUNT(IF(amount > 0, amount, 0)) as creditCount,
       COUNT(IF(amount < 0, amount, 0)) as debitCount,
       SUM(IF(amount > 0, amount, 0)) as credit,
       SUM(IF(amount < 0, amount, 0)) as debit,
       SUM(amount)                    as balance,
       cp.surname, cp.id, pcp.number from prepaid_card_transaction_pojo pctp
         join prepaid_card_pojo pcp on pctp.account = pcp.id
         join customer_pojo cp on pcp.id = cp.prepaid_card
where pcp.number = :voucherNumber AND pctp.client = :client AND booking_time > :from AND booking_time < :to group by cp.id)
List<Bean> loadGroupedCustomerPrepaidTransactions( ClientReadable client,  CustomerReadable customer,  String voucherNumber, Date from, Date to);