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

user_489088 0
Viewed 38303

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

criteria-api jpa hibernate java
7 Answers

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

user_13146570

The maximum optimized and full solution for existance checking via Criteria API based on dimo answer and with help of user3158918 answer:

EntityManager em = ...;

val cb = em.getCriteriaBuilder();
val query = cb.createQuery(Integer.class);
val root = query.from(YourEntity.class);

val predicate = cb.equal(root.get(attribute), attributeValue);

query.select(cb.literal(1)).where(predicate);

return !em.createQuery(query).setMaxResults(1).getResultList().isEmpty();

I used a lombok to be clean. This code generates SQL query:

SELECT 1 FROM your_entity_table WHERE your_entity_table.attribute = attributeValue LIMIT 1

Please post your solution, if its faster...

user_193322450
Related