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