Question 113086 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Spring Query – check number of rows in DB

I have the following method in my Spring Boot app in my Repository:

@Query("""
            SELECT tw FROM TW tw
            INNER JOIN tw.docks d // TW entity has list of docks
            WHERE d.id = :dockId
            AND tw.status <> 'DELETED'
            """)
List<TW> findAllTWs(long dockId);

How to check if TW has exactly one dock (tw.docks.size() == 1)? I have to filter out tw.docks with more than 1 dock (I want only list of TWs with one dock)
Probably I need native query for that

Tags:

2

Answers


  1. 0

    This should generate sql with a subquery:

    SELECT tw 
FROM TW tw 
WHERE tw.docks.size = 1

    See the appended documentation, size is a special HQL property.

    See: HQL docs

    Login or Signup to reply.
  2. 0

    Try with SIZE function which is valid in jpql language.

    @Query("""
            SELECT tw FROM TW tw
            INNER JOIN tw.docks d // TW entity has list of docks
            WHERE d.id = :dockId
            AND tw.status <> 'DELETED' AND SIZE(tw.docks) = 1
            """)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search