Troncador Troncador - 7 months ago 99
Java Question

In jpa criteria, "in case there is at least 1 row return true"

I'm trying to create the follow sentence using the criteria api in JPA (eclipselink),
it simple ask if there exist some user in some category

The sentence I want:

SELECT
CASE
WHEN EXISTS
(SELECT * FROM user WHERE category = ?)
THEN true
ELSE false
END
bind => [10]


I trying using this code:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Boolean> criteriaQuery = criteriaBuilder.createQuery(Boolean.class);
Root<T> root = criteriaQuery.from(tclass);

Subquery<T> subquery = criteriaQuery.subquery(tclass);
Root<T> subroot = subquery.from(tclass);
subquery.select(subroot);

Predicate subPredicate = criteriaBuilder.equal(subroot.get("category"),category);
subquery.where(subPredicate);

Predicate predicateExists = criteriaBuilder.exists(subquery);

Case<Boolean> booleancase = criteriaBuilder.<Boolean>selectCase();
Expression<Boolean> booleanExpression =
booleancase.when(predicateExists,true)
.otherwise(false);

criteriaQuery.select(booleanExpression);

TypedQuery<Boolean> typedQuery = entityManager.createQuery(criteriaQuery);
typedQuery.getResultList();


Sadly the sentence I have is the follow, I want to erase the last "from user":

SELECT
CASE
WHEN EXISTS
(SELECT ? FROM user t1 WHERE (t1.category = ?))
THEN ?
ELSE ?
END
FROM user t0
bind => [1, 110, true, false]


Any idea?

Answer

Finally I work around :P

TypedQuery<Boolean> typedQuery = 
     entityManager.createQuery(criteriaQuery).setMaxResults(1);

Then the sentence (is not the same, but is very close):

SELECT 
  CASE 
      WHEN EXISTS
          (SELECT ? FROM user t1 WHERE (t1.category = ?))  
     THEN ?
     ELSE ? 
  END
FROM user t0 LIMIT ?, ? 
bind => [1, 110, true, false,0,1]