Count using native SQL in JPA Data Spring returns 0

Question

I have a problem with one query/interface:

@Query(value = "SELECT count(1) FROM que_table que " +
            "WHERE que.CREATED_DATE >= (SELECT CREATED_DATE " +
            "   FROM que_table " +
            "   WHERE id = :selectedId " +
            "   ORDER BY CREATED_DATE DESC LIMIT 1) " +
            "AND que.QUEUE_STATUS in (:queueStatuses)", nativeQuery = true)
    Long countCurrentPosition(@Param("selectedId ") String selectedId , @Param("queueStatuses") Set<QueueStatus> queueStatuses);

I connect to MySQL using spring-data-JPA. When I run this query on console it's working perfectly.

What is wrong here?

Thanks in advance.

Try removing the parens around `:queueStatuses`. See also: https://stackoverflow.com/questions/26677310/spring-data-jpa-repositories-in-clause-in-derived-query-not-working — Jens Schauder, Jun 19 '18 at 12:52
I have tried without brackets. The result is the same. The query looks ok. — Piotr, Jun 20 '18 at 20:48

ali mostafavi · Answer 1 · 2022-06-26T06:22:22.823

use string name of QueueStatus enum :

@Query(value = "SELECT count(1) FROM que_table que " +
            "WHERE que.CREATED_DATE >= (SELECT CREATED_DATE " +
            "   FROM que_table " +
            "   WHERE id = :selectedId " +
            "   ORDER BY CREATED_DATE DESC LIMIT 1) " +
            "AND que.QUEUE_STATUS in (:queueStatuses)", nativeQuery = true)
    Long countCurrentPosition(@Param("selectedenter code hereId ") String selectedId , @Param("queueStatuses") Set<String> queueStatuses);

Count using native SQL in JPA Data Spring returns 0

1 Answers1