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?

score 29 · Answer 1 · edited Jun 15 '15 at 11:38

29

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

edited Jun 15 '15 at 11:38

BalusC

1,082,665
372
3,610
3,555

answered Jun 15 '15 at 09:27

René Link

48,224
13
108
140

2

A more readable way is : "select (count(*) > 0) from ..." – Reda Sep 01 '20 at 09:33
2

@Reda This would still search the whole index/table although the DB could and should stop after it found the first result. – T3rm1 Jan 25 '22 at 13:16
@T3rm1 yes you are right, but unfortunately I don't know if there is another solution to that problem – Reda Jan 25 '22 at 15:03

Kowser · Accepted Answer · 2023-03-27T18:26:31.660

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)

score 6 · Answer 3 · answered May 12 '18 at 22:33

6

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

answered May 12 '18 at 22:33

pek

171
1
2

But why bother computing a count that you're not going to use? Why not just try selecting one under a limit, that would be less work for your DB – Alkanshel May 09 '19 at 02:10
@Amalgovinus JPQL does not support the LIMIT operation on its own. More information about JPA and JPQL is available here: (https://en.wikibooks.org/wiki/Java_Persistence) (https://en.wikibooks.org/wiki/Java_Persistence/JPQL) – pek May 13 '19 at 19:52
Furthermore, the Entity Manager gives an API that can be used for limiting the results of any JPQL query. This is already answered here: (https://stackoverflow.com/questions/20679237/jpql-limit-query) (https://stackoverflow.com/questions/3479128/limit-number-of-results-in-jpql) In this case, and for the example I used earlier, I would use a different query: `SELECT c FROM Contract c WHERE c.person.id = :pid` and I would limit the results to 1, using: `entityManager.setMaxResults(1) ` – pek May 13 '19 at 19:53

score 3 · Answer 4 · answered Mar 01 '12 at 15:43

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

answered Mar 01 '12 at 15:43

Didier L

18,905
10
61
103

8

Not necessarily. `exists(...)` will probably exist after finding the first result. `count(...)` will have to scan many more index/table rows to determine the actual count. – Konrad Garus Aug 18 '15 at 07:56
2

@KonradGarus: It doesn't really matter though, when `count(..)` is the only solution that will work in JPQL anyway. So, when life gives you lemons... – Priidu Neemre Nov 05 '16 at 00:42

score 2 · Answer 5 · answered Feb 24 '17 at 10:07

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.

score 2 · Answer 6 · answered Jul 24 '11 at 00:25

2

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

answered Jul 24 '11 at 00:25

Bohemian

412,405
93
575
722

1

Thanks for pointing that out, but brackets or no the query still does not work in JPQL. – Elk Jul 24 '11 at 00:34
http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/jpa_langref.html#jpa_langref_exists So according to documentation at least exist part is correct from the question. "exists_expression ::= [NOT] EXISTS (subquery)" – Kowser Jul 24 '11 at 17:26

Grigory Kislin · Answer 7 · 2018-05-21T17:17:53.933

1

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

edited May 21 '18 at 17:17

answered May 21 '18 at 16:30

Grigory Kislin

16,647
10
125
197

2

You are trading theoretical efficiency for potential code breakage, since native query syntax is not verified until runtime. – Josh M. Jun 12 '20 at 14:22

score 0 · Answer 8 · answered Apr 19 '23 at 19:08

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()) { ... }

Is Select EXISTS() possible in JPQL?

8 Answers8

Linked