CODEWITHSUNDEEP
jpajpqljpa-2.0
Elk
Elk

Reputation: 705

Is Select EXISTS() possible in JPQL?

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?

Upvotes: 39

Views: 71195

Answers (9)

Mormegil
Mormegil

Reputation: 8071

Unfortunately, the exists expression (added in JPA 2.0) can be used only in the where clause. So you cannot use it directly in the way you want. However, with a slight massaging of syntax, you can use it in some simpler usecases, at least.

The basic idea is that instead of select exists(select something where criteria) you write select constant where exists(select something where criteria) and the result is given by the fact whether you got the (constant) result row or not. For the constant, I’m not sure if there is a simpler way than to include a (basically dummy) select by PK from any table.

As a more specific example, imagine you want to check whether there is a second person with the specified e-mail address in the database. You’d like to write something similar to select exists(select p.id WHERE p.phoneNumber=:phoneNumber and p.id<>:primaryPersonId). The other answers make you write select count(p.id) where p.phoneNumber=:phoneNumber and p.id<>:primaryPersonId) (or the slightly tweaked but without noticeable effect on performance select (case when count(p.id)>0 then 1 else 0) where p.phoneNumber=:phoneNumber and p.id<>:primaryPersonId)) which works fine but unnecessarily computes the full count which can have performance impact on the database (depending on the specifics).

But you can write select p.id from persons where p.id=:primaryPersonId and exists(select p.id where p.phoneNumber=:phoneNumber and p.id<>:primaryPersonId) instead, which will return one result row when there are duplicates, and returns no rows when there are no duplicates (when the exists returned false).

Upvotes: 1

gavenkoa
gavenkoa

Reputation: 48743

The solution with count(*) > 0 triggered full table scan in Postgres for me:

EXPLAIN ANALYSE select count(*) > 0 from msg where type = 'ALERT';

Node Type   Entity  Cost            Rows    Time        Condition
Aggregate   [NULL]  462793 - 462793 1       24606.407   [NULL]
Gather      [NULL]  462793 - 462793 3       24606.397   [NULL]
Aggregate   [NULL]  461793 - 461793 1       24560.095   [NULL]
Seq Scan    msg     0.00 - 460781   335954  24489.559   ((type)::text = 'ALERT'::text)

The proper performant way is to short-circuit scan with limit or top keyword. As pagination is not portable you have to resort to setMaxResults():

Query query = EntityManager.createQuery("select 1 from Book where ...", Integer.class);
List<Integer> tinyList = query.setFirstResult(0).setMaxResults(1).getResultList();
if (tinyList.isEmpty()) { ... }

Upvotes: 0

Kowser
Kowser

Reputation: 8261

This answer is obsolete. Yes, it is possible. Please refer to the 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)

Upvotes: 10

Grigory Kislin
Grigory Kislin

Reputation: 17990

It is more efficient for DB not counting all records. Create native query

Spring-Data-JPA

@Query(value = ".....", nativeQuery = true)

or JPA:

@NamedNativeQuery(name=.., query="..", resultClass=..)

Upvotes: 1

pek
pek

Reputation: 211

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=?

Upvotes: 10

Markus Barthlen
Markus Barthlen

Reputation: 379

In a project that uses

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>4.2.4.Final</version>
    </dependency>
    ...

I successfully used an 

exists(select s.primaryKey from Something s)

clause. So it might have changed. It could also be Hibernate-proprietary. Since a lot of people use Hibernate as a persistence provider, I thought I might add this here.

Upvotes: 2

Ren&#233; Link
Ren&#233; Link

Reputation: 51343

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

Upvotes: 32

Didier L
Didier L

Reputation: 20579

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

Upvotes: 3

Bohemian
Bohemian

Reputation: 424983

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

Upvotes: 3

Related Questions