Spring Data JPA and Exists query

Question

I'm using Spring Data JPA (with Hibernate as my JPA provider) and want to define an exists method with a HQL query attached:

public interface MyEntityRepository extends CrudRepository<MyEntity, String> {

  @Query("select count(e) from MyEntity e where ...")
  public boolean existsIfBlaBla(@Param("id") String id);

}

When I run this query, I get a java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Boolean.

How does the HQL query have to look like to make this work? I know I could simply return a Long value and afterwards check in my Java code if count > 0, but that workaround shouldn't be necessary, right?

Solution 1

I think you can simply change the query to return boolean as

@Query("select count(e)>0 from MyEntity e where ...")

PS: If you are checking exists based on Primary key value CrudRepository already have exists(id) method.

Solution 2

Spring Data JPA 1.11 now supports the exists projection in repository query derivation.

See documentation here.

In your case the following will work:

public interface MyEntityRepository extends CrudRepository<MyEntity, String> {  
    boolean existsByFoo(String foo);
}

Solution 3

in my case it didn't work like following

@Query("select count(e)>0 from MyEntity e where ...")

You can return it as boolean value with following

@Query(value = "SELECT CASE  WHEN count(pl)> 0 THEN true ELSE false END FROM PostboxLabel pl ...")

Solution 4

It's gotten a lot easier these days!

@Repository
public interface PageRepository extends JpaRepository<Page, UUID> {

    Boolean existsByName(String name); //Checks if there are any records by name
    Boolean existsBy(); // Checks if there are any records whatsoever

}

Solution 5

Since Spring data 1.12 you can use the query by Example functionnality by extending the QueryByExampleExecutor interface (The JpaRepositoryalready extends it).
Then you can use this query (among others) :

<S extends T> boolean exists(Example<S> example);

Consider an entity MyEntity which as a property name, you want to know if an entity with that name exists, ignoring case, then the call to this method can look like this :

//The ExampleMatcher is immutable and can be static I think
ExampleMatcher NAME_MATCHER = ExampleMatcher.matching()
            .withMatcher("name", GenericPropertyMatchers.ignoreCase());
Example<MyEntity> example = Example.<MyEntity>of(new MyEntity("example name"), NAME_MATCHER);
boolean exists = myEntityRepository.exists(example);

View more solutions

Comments

[Neil Stockton]

obviously you could change your JPQL query to return a boolean ... by not returning "count(e)" and instead returning a boolean expression

[Stefan Haberl]

Thanks for the pointer to exists(id), but my where clause contains some complex constraints...

[Stefan Haberl]

Not really relevant in my case since my query is in HQL

[Stefan Haberl]

If you'll add a working example I'll be happy to upvote.

[Ankit Soni]

@StefanHaberl I have included one example.

[JRA_TLL]

count(e)>e will likely only work with certain databases (e.g. Oracle). With DB2 it does not and you will have to use select case when count(e) > 0 then true else false end from Entity e

[bartektartanus]

That's the whole point of Spring data - you don't have to write any HQL :)

[Stefan Haberl]

@bartektartanus I disagree. Automatic query creation works for simple use cases only. If you have complex business logic and want to push that into your query (rather than filter in Java in-memory), there's no way around a nice JPA/HQL query.

[bartektartanus]

Yup, but if you want to check if there is a record with value on one field - this is definitely simple use case :) But for more complex, I agree - spring data is sometimes not efficient.

[Jagger]

Doesn't work. org.springframework.data.mapping.PropertyReferenceException: No property existsByXxx found for type Xxx!

[Ankit Soni]

@Jagger, could you please provide a sample code, and also could you check if spring-data-jpa's version is 1.11 or higher?

[Jagger]

@AnkitSoni The JPA version must be all right, because it is coming from Spring Boot version 1.4.6. I have already reopened the issue in Spring Jira. There you can see also the sample code.

[Jagger]

@AnkitSoni It turned out, the Spring Data JPA 1.11 is first available in Spring Boot 1.5.

[A.W.]

This is great and easy. Works in Spring Boot 2.0.4 boolean existsByUsername(String username);

[JMDenver]

do a SQL exists query here instead of count rows. Using count has to complete all rows in an index (hopefully) or else with no index a table scan. SQL exists will return after the first encountered row rather than find every row and count them the way count(*) does. In a 10 row table this is no issue in 10s/100s of thousands and beyond this matters.

[Rhushikesh Chaudhari]

Initially it was not working for one of the property named tank_no. I was trying to use the method boolean existsByTank_no(String tank_no); Reason was an underscore is not supported as it is reserved character. When I changed property name to tankNo and method name to existsByTankNo(String tankNo); it is working now.

[skwisgaar]

which ones? afaik it will always return boolean, no?

[Sahil Chhabra]

Which all databases did you try?

[Minkesh Jain]

Thanks, this seems like the correct approach. Works perfectly.

[Haroldo_OK]

Basically, checking counting something is slower than simply checking for it's existance; in the absence of better solutions, the answer works, but it is suboptimal.

[fdaugan]

Does not work as expected. Returns null value instead of false

[Dave B]

I don't know if anybody pointed to this, for all things Hibernate I look at Vlad Mihalcea's work. I just solved this same problem with this link. vladmihalcea.com/spring-data-exists-query In my cases, I was able to leverage JPARepository.existsById().