Return Boolean Value from Spring Data JPA Query

Published on March 26, 2015 by

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.

Author avatar
Bo Andersen

About the Author

I am a back-end web developer with a passion for open source technologies. I have been a PHP developer for many years, and also have experience with Java and Spring Framework. I currently work full time as a lead developer. Apart from that, I also spend time on making online courses, so be sure to check those out!

12 comments on »Return Boolean Value from Spring Data JPA Query«

  1. Benoit

    Thank you very much for the tip!

    • You are welcome – I’m happy that you found it useful! :-)

  2. OSA

    Thank you it was useful for me. I even haven’t known about CASE WHEN in mysql.

  3. Aksil

    good tip, thanks

  4. RK

    Great tip, exactly what I was looking for. Thank you!

  5. MS

    Cheers, good tip!

  6. Firza

    Unexpected token on when
    It gives me this error

  7. Aarambh

    Can we return Integer instead ?
    I did this but its not working, everytime the result is -1

    @Query(nativeQuery=true, value="select sum(CASE WHEN (status = 'purchased') THEN isv.quantity ELSE -isv.quantity END) as availableQuantity from itemstockview isv where itemId = :itemId and branchId=:branchId")

    Thanks!!!

  8. Swati

    Thank you, this helped a lot.

  9. sunu

    awesome brother !! thank you.

  10. mapa

    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.

  11. dhukas

    even simpler without case-when: `@Query(“SELECT COUNT(c) > 0 FROM Company c WHERE c.name = :companyName”)`

Leave a Reply

Your e-mail address will not be published.