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

Question

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?

What happens if you use subroot instead of root, in subPredicate? — perissf, Jun 08 '13 at 06:01
Sorry, it was subroot in subpredicate. I write wrong in stackoverflow (I already correct the question). Thanks, but that don't solve my problem :P — Troncador, Jun 08 '13 at 06:10
Why do you want this complex and unreadable java code if you already have simple and clear SQL queries ? — jbaliuka, Feb 13 '14 at 19:12
because I can edit the sentence programatically, change the table, change the column whitout edit any string (is error-prone the edition of string) — Troncador, Feb 23 '14 at 20:55

score 1 · Answer 1 · answered Apr 15 '14 at 15:08

Unfortunately, JPA does not support selecting data without an entity (table). Not even standard SQL92 supports this - have a look at how you can select without a table in different databases: http://en.wikibooks.org/wiki/SQL_Dialects_Reference/Select_queries/Select_without_tables

In SQL92 standard, you must provide table in SELECT clause.

JPA is also a widely accepted standard and usually does not provide non-standard features and even many standard SQL features come only in latest versions of JPA (for example nested selects came only recently with JPA 2.1 in JavaEE 7).

So the solution is to select from some entity, which always has at least one row in database, and limit the results to max 1 by query.setMaxResults(1). Even better is to create a dummy entity, which always has 1 row and its contents is never changed. This would increase performance of the select.

score 0 · Accepted Answer · answered May 05 '16 at 07:52

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]

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

2 Answers2