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

Edy Bourne picture Edy Bourne · Jul 18, 2011 · Viewed 32.3k times · Source

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

Matthew T. Staebler picture Matthew T. Staebler · Apr 6, 2013

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