Is Select EXISTS() possible in JPQL?

user_1262978 0
Viewed 52536

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?

jpa-2.0 jpql jakarta-ee jpa
7 Answers

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

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