How to properly determine whether an "exists" JPA Criteria Query clause returned true or false?

Question

I don't know how to perform a JPA criteria query that returns with a boolean output.

The goal is to have a criteria query that looks like this when rendered on Oracle:

select 1 from dual where exists ( ... );

The where exists (...) part I performed with a subquery. I'm struggling with the external query.

The practical use of this is to determine whether that subquery in the exists clause returns true or false.

This is what I've written:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

CriteriaQuery<Object> query = criteriaBuilder.createQuery();
query.from(Boolean.class);
query.select(criteriaBuilder.literal(true));

Subquery<Location> subquery = query.subquery(Location.class);
Root<Location> subRootEntity = subquery.from(Location.class);
subquery.select(subRootEntity);

Path<?> attributePath = subRootEntity.get("State");
Predicate predicate = criteriaBuilder.equal(attributePath, criteriaBuilder.literal("TX"));
subquery.where(predicate);
query.where(criteriaBuilder.exists(subquery));

TypedQuery<Object> typedQuery = em.createQuery(query);

The last line outputs an error, stating that "Boolean is not an entity". I think my issue is not knowing how to express the "from" part of the query so that the result outputs 1 or 0/ true or false - not an entity.

I know I could retrieve any entity and then check if the list of results has size of 1.

I'm asking how to get a boolean result, both to avoid the unnecessary task of retrieving those columns and also to learn how to do it.

Is this possible at all?

Thanks! Eduardo

Answer 1

Yes, this is possible. Assuming that you have an entity corresponding to your dual table, you will want to use that entity class in CriteriaQuery#from.

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

CriteriaQuery<Boolean> query = criteriaBuilder.createQuery(Boolean.class);
query.from(dual.class);
query.select(criteriaBuilder.literal(true));

Subquery<Location> subquery = query.subquery(Location.class);
Root<Location> subRootEntity = subquery.from(Location.class);
subquery.select(subRootEntity);

Path<?> attributePath = subRootEntity.get("State");
Predicate predicate = criteriaBuilder.equal(attributePath, criteriaBuilder.literal("TX"));
subquery.where(predicate);
query.where(criteriaBuilder.exists(subquery));

TypedQuery<Boolean> typedQuery = em.createQuery(query);

Answer 2

Hibernate 5 is working:

Subquery<Integer> subquery = query.subquery(Integer.class);
Root<Location> subRootEntity = subquery.from(Location.class);
subquery.select(criteriaBuilder.literal(1));

Path<?> attributePath = subRootEntity.get("State");
Predicate predicate = criteriaBuilder.equal(attributePath, criteriaBuilder.literal("TX"));
subquery.where(predicate);
query.where(criteriaBuilder.exists(subquery));

Answer 3

You could do a select for one property (e.g. the ID) and set the max results returned to 1 so that you make sure the DB does not do more work than necessary (like counting all instances). Then your results list will either be empty (exists = false) or have one element (exists = true).