Spring Query - check number of rows in DB

Question

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

off the top of my head, I think you basically have 2 options, either check docks.size() in your code or use select count(). If you use select count() and get 1, you'll need a second query to return the record. — dsp_user
– dsp_user, Commented Oct 20, 2022 at 9:04

Panagiotis Bougioukos · Accepted Answer · 2022-10-20 09:08:44Z

1

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
            """)

answered Oct 20, 2022 at 9:08

Panagiotis Bougioukos

19.6k4 gold badges41 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matexon Over a year ago

But this solution is better, with tw.docks.size I get WARN: syntax in HQL/JPQL query [tw.ramps.size]; use collection function syntax instead [size(tw.docks)].

void void · Accepted Answer · 2022-10-20 09:13:09Z

1

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

edited Oct 20, 2022 at 9:13

answered Oct 20, 2022 at 9:02

void void

1,2241 gold badge6 silver badges11 bronze badges

2 Comments

Panagiotis Bougioukos Over a year ago

@Matley just a note here docs.oracle.com/cd/E29542_01/apirefs.1111/e13946/…

The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.

SIZE is part of JPQL

Panagiotis Bougioukos Over a year ago

HQL is only for Hibernate while you might switch to EclipseLink or some other provider in future that comply with the general JPQL.

Collectives™ on Stack Overflow

Spring Query - check number of rows in DB

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related