Issue
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
Solution
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).
Answered By - dimo
Answer Checked By - Gilberto Lyons (JavaFixing Admin)
