CODEWITHSUNDEEP
javahibernatejpaspring-datajpql
Stefan Haberl
Stefan Haberl

Reputation: 10539

Spring Data JPA and Exists query

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?

Upvotes: 99

Views: 243665

Answers (9)

Prashant Rai
Prashant Rai

Reputation: 33

Spring data provides method for checking the existence of a row using field: example: boolean existsByEmployeeIdAndEmployeeName(String employeeId, String employeeName);

Upvotes: 3

sparkyspider
sparkyspider

Reputation: 13519

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

}

Upvotes: 22

Yosua Simanjuntak
Yosua Simanjuntak

Reputation: 17

You can use .exists (return boolean) in jpaRepository.

if(commercialRuleMsisdnRepo.exists(commercialRuleMsisdn.getRuleId())!=true){

        jsRespon.setStatusDescription("SUCCESS ADD TO DB");
    }else{
        jsRespon.setStatusCode("ID already exists is database");
    }

Upvotes: -7

Sahil Chhabra
Sahil Chhabra

Reputation: 11676

You can use Case expression for returning a boolean in your select query like below.

@Query("SELECT CASE WHEN count(e) > 0 THEN true ELSE false END FROM MyEntity e where e.my_column = ?1")

Upvotes: 3

Ankit Soni
Ankit Soni

Reputation: 2534

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);
}

Upvotes: 232

Narasimha
Narasimha

Reputation: 1707

Apart from the accepted answer, I'm suggesting another alternative. Use QueryDSL, create a predicate and use the exists() method that accepts a predicate and returns Boolean.

One advantage with QueryDSL is you can use the predicate for complicated where clauses.

Upvotes: 3

Stephane L
Stephane L

Reputation: 3275

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

Upvotes: 12

Runomu
Runomu

Reputation: 431

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

Upvotes: 26

K. Siva Prasad Reddy
K. Siva Prasad Reddy

Reputation: 12385

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.

Upvotes: 108

Related Questions