Hibernate: how to make EXISTS query? (not subquery

问题:

For example:

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

How to make such query using Hibernate?

回答1:

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

回答2:

HQL doesn't allow to use exists statement. But you can use several approaches:

1. count(*) > 0 but this is bad for performance Avoid Using COUNT() in SQL When You Could Use EXISTS()
2. 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 but you need just to check for null.
3. Use session.get(PersistentEntity.class, id) != null and this will work faster if you have enabled second level cache but it will be a problem if you need more criteries than just id.
4. You can use the following method: getSession().createQuery("select 1 from DTOAny t where ...").uniqueResult() != null)

回答3:

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;

回答4:

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;

回答5:

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

回答6:

Try:

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

Works fine with Spring Data.