CODEWITHSUNDEEP
hibernatehql
Ron
Ron

Reputation: 8196

Hibernate: how to make EXISTS query? (not subquery)

For example:

EXISTS ( SELECT * FROM [table] WHERE ... )

How to make such query using Hibernate?

Upvotes: 17

Views: 51983

Answers (8)

goldenfingers98
goldenfingers98

Reputation: 31

If you use JpaRepository, this could be simple if the existence criteria is simple like: 

Boolean existsByName(String name);

If not, this solution may be the one you look for.
Write a query that looks like:

SELECT CASE WHEN count(attr) > 0 THEN TRUE ELSE FALSE
FROM entity
WHERE <condition>

Example: 

SELECT CASE count(pr_id) > 0 THEN TRUE ELSE FALSE
FROM product
WHERE pr_name LIKE 'a__%'

test your query, then add it into your repo

@Repository
public interface FooRepository extends JpaRepository<FooEntity,UUID>{

    @Query(value = "<tested_query_here>", nativeQuery = true)
    Boolean existsByConditionYouWrote(<args>);

}

Upvotes: 3

Yamashiro Rion
Yamashiro Rion

Reputation: 1976

Also, if you use JpaRepository, you can easily check entity existence via ORM. Here is a Kotlin example:

interface UserRepository : JpaRepository<User, Long> {
    fun existsByUsername(username: String): Boolean
}

Upvotes: 4

Sergey Ponomarev
Sergey Ponomarev

Reputation: 3201

HQL doesn't allow to use exists statement. UPD Starting from Hibernate 5 it supports it as a predicate in WHERE

You can use several approaches:

  1. For Hibrnate 5: you can use subquery with the same table boolean exists = session.createQuery("select 1 from PersistentEntity where exists (select 1 from PersistentEntity p where ...)").uniqueResult() != null;. Thank to author bellow.
  2. count(*) > 0 but this is bad for performance Avoid Using COUNT() in SQL When You Could Use EXISTS()
  3. Use boolean exists = session.createQuery("from PersistentEntity where ...").setMaxResults(1).uniqueResult() != null; but this will force Hibernate to load all fields and make hydration to object even if you need just to check for a null.
  4. Use session.get(PersistentEntity.class, id) != null and this will work faster if you enabled second level cache but it will be a problem if you need more criteries than just id.
  5. You can use the following method: getSession().createQuery("select 1 from PersistentEntity where ...").uniqueResult() != null)

Upvotes: 21

ptoinson
ptoinson

Reputation: 2051

There's some nice comments about using EXISTS but no examples. Here is my solution in Hibernate 5.2:

boolean exists = session.createQuery( 
                    "SELECT 1 FROM PersistentEntity WHERE EXISTS (SELECT 1 FROM PersistentEntity p WHERE ...)")
            .uniqueResult() != null;

Upvotes: 5

Radek Wroblewski
Radek Wroblewski

Reputation: 299

Try:

"select count(e) > 0 from Entity e where..."

Works fine with Spring Data.

Upvotes: -1

Matthew Cachia
Matthew Cachia

Reputation: 4722

I used the following: SELECT COUNT(e) FROM Entity e. Worked perfectly fine in Spring Data JPA.

Upvotes: 0

Mykola Lysenko
Mykola Lysenko

Reputation: 131

If we are using WHERE clause, the database may have to scan the whole table to count records that matches our criteria, but we can limit to search only for one record, it is enough to say about emptiness.

If there wasn't any search filters the previous query would be admissible as the database would do some optimisation with the use of an index.

so I suppose following query will increase some perfomance comparing to previous one:

boolean exists = session.createQuery("from PersistentEntity where ...").setMaxResults(1).uniqueResult() != null;

Upvotes: 6

user and
user and

Reputation: 560

If your goal is inspect some set on emptiness, you may use simple HQL query:

boolean exists = (Long) session.createQuery("select count(*) from PersistentEntity where ...").uniqueResult() > 0

Upvotes: 12

Related Questions