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?

Answer 1

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.