Hibernate: how to make an EXISTS request? (not a subquery)

Question

For instance:

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

How to make such a request using Hibernate?

Answer 1

If your goal is to test some set on a void, you can use a simple HQL query:

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

Answer 2

HQL does not allow the use of the exists operator. UPD Starting with Hibernate 5, it supports it as a predicate in WHERE

You can use several approaches:

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

Answer 3

If we use the WHERE clause, the database may have to scan the entire table to count records matching our criteria, but we can limit the search to only one record, just say nothing.

If there weren’t any search filters, the previous query would be valid, since the database would do some optimization using the index.

therefore, I assume that the following query will increase some performance over the previous one:

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

Answer 4

There are some good 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; 

Answer 5

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

Answer 6

Try:

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

Works fine with Spring data.