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?

obviously you could change your JPQL query to return a boolean ... by not returning "count(e)" and instead returning a boolean expression — Neil Stockton, May 22 '15 at 08:46

score 223 · Answer 1 · edited Dec 07 '17 at 07:07

223

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

edited Dec 07 '17 at 07:07

Abdullah Khan

12,010
6
65
78

answered Jan 31 '17 at 06:16

Ankit Soni

2,434
2
17
19

4

If you'll add a working example I'll be happy to upvote. – Stefan Haberl Jan 31 '17 at 07:51
@StefanHaberl I have included one example. – Ankit Soni Jan 31 '17 at 09:53
12

That's the whole point of Spring data - you don't have to write any HQL :) – bartektartanus May 22 '17 at 08:42
@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. – Stefan Haberl May 22 '17 at 08:51
3

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. – bartektartanus May 22 '17 at 09:11
1

Doesn't work. `org.springframework.data.mapping.PropertyReferenceException: No property existsByXxx found for type Xxx!` – Jagger Jun 06 '17 at 19:24
@Jagger, could you please provide a sample code, and also could you check if spring-data-jpa's version is 1.11 or higher? – Ankit Soni Jun 06 '17 at 20:32
@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](https://jira.spring.io/browse/DATAJPA-851) in Spring Jira. There you can see also the sample code. – Jagger Jun 06 '17 at 20:40
1

@AnkitSoni It turned out, the Spring Data JPA 1.11 is first available in Spring Boot 1.5. – Jagger Jun 07 '17 at 08:44
1

This is great and easy. Works in Spring Boot 2.0.4 `boolean existsByUsername(String username);` – A.W. Sep 06 '18 at 06:45
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. – Rhushikesh Chaudhari Aug 18 '19 at 16:08
Thanks, this seems like the correct approach. Works perfectly. – Minkesh Jain Dec 26 '19 at 09:24

score 99 · Accepted Answer · edited May 22 '15 at 09:08

99

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.

edited May 22 '15 at 09:08

Adrian Shum

38,812
10
83
131

answered May 22 '15 at 08:50

K. Siva Prasad Reddy

11,786
12
68
95

1

Thanks for the pointer to `exists(id)`, but my where clause contains some complex constraints... – Stefan Haberl May 22 '15 at 08:58
31

`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` – JRA_TLL Apr 11 '17 at 14:26
2

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. – JMDenver Jan 23 '19 at 20:57
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. – Haroldo_OK Feb 12 '21 at 12:20
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. https://vladmihalcea.com/spring-data-exists-query/ In my cases, I was able to leverage JPARepository.existsById(). – Dave B Jun 30 '22 at 16:47
@JRA_TLL Doesn't work with Oracle. 30 upvotes = maybe it did? Not anymore ... – VXp Dec 21 '22 at 06:24

score 24 · Answer 3 · answered Sep 29 '16 at 08:08

24

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

answered Sep 29 '16 at 08:08

Runomu

411
1
8
18

score 22 · Answer 4 · answered Sep 02 '20 at 15:19

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

}

Stephane L · Answer 5 · 2016-10-08T11:54:46.097

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

Not really relevant in my case since my query is in HQL – Stefan Haberl Oct 08 '16 at 18:09 — Stefan Haberl, Oct 08 '16 at 18:09

score 3 · Answer 6 · edited Nov 17 '16 at 22:48

3

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.

edited Nov 17 '16 at 22:48

Ninjakannon

3,751
7
53
76

answered Nov 17 '16 at 22:18

Narasimha

1,537
1
16
17

Sahil Chhabra · Answer 7 · 2018-04-27T09:08:59.087

3

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

edited Apr 27 '18 at 09:08

answered Apr 27 '18 at 03:55

Sahil Chhabra

10,621
4
63
62

which ones? afaik it will always return boolean, no? – skwisgaar Oct 05 '19 at 11:05
Which all databases did you try? – Sahil Chhabra Oct 05 '19 at 16:02

score 1 · Answer 8 · answered Aug 05 '21 at 06:49

1

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

answered Aug 05 '21 at 06:49

Prashant Rai

15
2

Yosua Simanjuntak · Answer 9 · 2018-07-31T07:03:48.033

-6

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

edited Jul 31 '18 at 07:03

answered Jul 25 '18 at 10:02

Yosua Simanjuntak

19
2

Spring Data JPA and Exists query

9 Answers9

Linked

Related