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.

Thank you for reading and happy coding!

6 Comments

  1. Benoit said:

    Thank you very much for the tip!

    November 19, 2015
    Reply
    • Bo Andersen said:

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

      November 19, 2015
      Reply
  2. OSA said:

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

    January 4, 2016
    Reply
  3. Aksil said:

    good tip, thanks

    October 4, 2016
    Reply
  4. RK said:

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

    October 16, 2016
    Reply
  5. MS said:

    Cheers, good tip!

    December 25, 2016
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *