How to correctly determine if a sentence "exists" regarding a JPA Criteria Query sentence true or false?

Question

I do not know how to make a JAP criteria request end in a logical output.

The goal is to have a criteria query that looks like Oracle shows:

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

The where exists (...) that I did with the subquery, I am struggling with an external query.

And the practical use of this is to determine if this subquery is returned in an exists true or false clause.

Here is what I got:

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

But the last line is not responding: Boolean is not an entity. I think my problem is not knowing how to express "from" the part of the query, since the result will be 1 or 0, true or false, not the entity.

I know that I could get any object, and then check if the result list has size 1, but I'm trying to learn how to get the logical result correctly, to avoid the unnecessary task of extracting these columns, and also to learn how to do this.

Is this even possible?

Thanks! Eduardo

Answer 1

You can make a selection for one property (for example, an identifier) ​​and set the maximum results returned to 1 to make sure that the database does not do more work than necessary (for example, counting all instances). Then your result list will either be empty (exists = false) or have one element (exists = true).

Answer 2

Yes it is possible. Assuming you have an object that matches your dual table, you'll want to use this 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 3

I know this is an older question, but for everyone else: how about using Spring jpa @Query annotation and a select query (depending on your db implementation) using a method that returns a boolean. For example (MySQL):

 @Query("SELECT CASE WHEN COUNT(l) > 0 THEN TRUE ELSE FALSE END FROM Location l WHERE l.state=?1") boolean locationForStateExists(String state); 

Sometimes just using the query string in @Query can be a lifesaver when the named methods of the JPA or query builder do not quite do what you want.

Answer 4

Hibernate 5 works:

 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 5

I think the problem is the query.from question (Boolean.class). He is trying to create a query "select object from boolean". If you want to use a boolean return type, you need to use

 CriteriaQuery<Boolean> query = criteriaBuilder.createQuery(Boolean.class) 

Then a query from any existing entity table to create a valid query (possibly from a subquery table). I do not think that to create from double products, except when you managed to match a double table.

Answer 6

Is there a reason that all logic should be in JPA? If not, why not use a SELECT COUNT and then a conditional value to set the boolean?

 Boolean exists = false; int count = selectCountQuery(); if (count > 0) { exists = true; }