Return Boolean Value from Spring Data JPA Query
Did you know that you can return a boolean value from a Spring Data JPA query? For instance, you may want to know whether or not an entity (i.e. database row) exists with a given value for a field.Β Surely one could simply return the row count and check if it is larger than zero, but it is a little more “clean” to return a boolean value. For this example, this can be accomplishedΒ as in the code snippet below.
@Repository
public interface CompanyRepository extends JpaRepository<Company, Integer> {
@Query("SELECT CASE WHEN COUNT(c) > 0 THEN true ELSE false END FROM Company c WHERE c.name = :companyName")
boolean existsByName(@Param("companyName") String companyName);
}
As you might have guessed, one can do like this for other use cases too, as long as we can use CASE WHEN (switch) and the query only returns a single row. This is usually the case when using aggregate functions such as COUNT, AVG, SUM, etc., unless they are used in combination with GROUP BY. If this is the case, then you would probably execute a native query anyways.
12 comments on »Return Boolean Value from Spring Data JPA Query«
Thank you very much for the tip!
You are welcome – I’m happy that you found it useful! :-)
Thank you it was useful for me. I even haven’t known about CASE WHEN in mysql.
good tip, thanks
Great tip, exactly what I was looking for. Thank you!
Cheers, good tip!
Unexpected token on when
It gives me this error
Can we return Integer instead ?
I did this but its not working, everytime the result is -1
Thanks!!!
Thank you, this helped a lot.
awesome brother !! thank you.
This is beutifull but if can simplify the code, i would prefer.
ππππππππππππππππππππππππππππππππππππππ
Long countByCompanyName(String companyName);
final boolean hasCompany = guideScheduleRepository.countByCompanyName() > 0;
ππππππππππππππππππππππππππππππππππππππ
Most simple code for this solution.
even simpler without case-when: `@Query(“SELECT COUNT(c) > 0 FROM Company c WHERE c.name = :companyName”)`