Is Select EXISTS() possible in JPQL?

Question

I'm trying to run a query that checks if some conditions are true and returns a simple boolean result as output. What makes it slightly tricky is that one of the conditions is to test for whether no results are returned for a set of criteria.

I'm currently using JPA-2.0 with hibernate as my provider, backed by MySQL. I have gotten an example query working fine in MySQL, but when trying to get it running in JPQL it flops. The MySQL query looks a bit like this:

Select exists(Select statement with criteria) 
  or not exists(Select statement with criteria);

I also got the same output using CASE, but as JPQL doesn't support that statement.

Anyways, when I try to use a similar query in JPQL I get the error:

"unexpected end of subtree"

which from my understanding means that something is missing in the query. Does anyone have any idea how to fix it?

Solution 1

This answer is obsolete. Please refer to correct answer from Rene Link

---

No, it is not possible.

Refer to the JPQL BNF documentation from oracle.

simple_cond_expression ::= comparison_expression | between_expression | like_expression | in_expression | null_comparison_expression | empty_collection_comparison_expression | collection_member_expression | exists_expression

exists_expression ::= [NOT] EXISTS(subquery)

Solution 2

You can do a boolean query using a case expression.

As of JPA 2.0 (Java EE 6) you can create a TypedQuery .

String query = "select case when (count(*) > 0)  then true else false end from ......"
TypedQuery<Boolean> booleanQuery = entityManager.createQuery(query, Boolean.class);
boolean exists = booleanQuery.getSingleResult();

In JPA 1.0 (Java EE 5) you must use an untyped query.

Query booleanQuery = entityManager.createQuery(query);
boolean exists = (Boolean) booleanQuery.getSingleResult();

Solution 3

Alternatively you could use a select count(...) and test whether it returns 0. This should be almost as efficient without requiring to write much more code (in fact, the query itself will probably look simpler).

Solution 4

You have mismatched brackets. Try removing the one before the not (and the ones around the first exists):

select exists(Select statement with criteria) 
  or not exists(Select statement with criteria);

You don't need brackets around exists()

Solution 5

In a project with Hibernate 5.2 (which supports JPA 2.1), and Spring Data Jpa 2.0.6, I successfully used this JPQL query:

@Query("SELECT COUNT(c) > 0 FROM Contract c WHERE c.person.id = :pid")
Boolean existContractForPerson(@Param("pid") Long personId);

In the logs, I read that the produced native query is the following:

select count(contract0_.contract_id)>0 as col_0_0_ from contracts contract0_ where contract0_.fk_person_id=?

View more solutions