JPQL statement returning boolean value

Question

Is it possible to write JPQL query like following:

select count(*) > 0 from Scenario scen where scen.name = :name

that would return true/false boolean values depending of whether entity filling criteria exists or not?

I would like to use the query this way:

boolean exists = entityManager.createQuery(query,Boolean.class).setParameter("name",name).getSingleResult();

The query from my example just isn't syntactically correct (parse error), but is there any correct way of doing checks like that in JPQL, that would return boolean value, or is it only possible in Java code?

`SELECT NEW java.lang.Boolean(count(*) > 0)` should work. Maybe you can omit the package declaration, I have no chance to try it right now. — Petr Mensik, Aug 21 '12 at 09:19
Strange, but I'm getting following error: `org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [java.lang.Boolean] [select new java.lang.Boolean(count(*) > 0) from ...` — Piotr Sobczyk, Aug 21 '12 at 09:25
So try to determine what this query returns - I mean try `Object exists = entityManager.createQuery(...`, then look in the debug mode what type and value is exists Object — Petr Mensik, Aug 21 '12 at 09:35

score 90 · Accepted Answer · answered Aug 21 '12 at 09:55

90

Yes, it is possible with following:

select case when (count(scen) > 0)  then true else false end  
from Scenario scen where scen.name = :name

answered Aug 21 '12 at 09:55

Mikko Maunu

41,366
10
132
135

1

Actually this statement is valid `HQL` but not valid `JPQL`. – Oleksandr Bondarenko Aug 25 '12 at 07:36
1

@Oleksandr Bondarenko, that can be, but I am unfortunately not able to recognize why exactly it is not valid JQPL, can you please explain? – Mikko Maunu Aug 25 '12 at 10:52
3

Sorry, it's a valid JPQL, it was invalid in JPA 1.0. – Oleksandr Bondarenko Aug 25 '12 at 11:21
4

Is it normal that this QUERY returns an `Integer` not a `Boolean`? – Jin Kwon Jan 14 '13 at 11:22
1

I've got the same issue as @JinKwon. Does anyone has a solution that would actually return a boolean with JPA? – Oct 16 '14 at 10:29
3

I have it working as an @Query annotation on a Spring Data JPA repository using the exact syntax from the answer. – Wim Deblauwe Jan 26 '15 at 12:47
Confirm the usability with Spring DATA JPA and it can return boolean as it is. – Chayne P. S. Nov 14 '20 at 00:59

score 21 · Answer 2 · answered May 22 '15 at 09:23

21

What about just:

select count(scen) > 0
from Scenario scen where scen.name = :name

answered May 22 '15 at 09:23

Stefan Haberl

9,812
7
72
81

1

Even with current Hibernate 5.2.12 this statement does not work for all Databases. It work for H2 but not for MSSQL, Instead the "case when then.." works for both (and hopefully all) Databases – Ralph Feb 22 '18 at 11:25
i think most of projects never migrate to MSSQL from other table) – fedrbodr Feb 14 '19 at 09:54
2

:) One of the most misunderstood, hyped “features” of Hibernate IMHO. You’ll never ever just swap your Enterprise DB without any thorough migration plan and code changes, at least not at projects of any reasonable size. If somebody really wants to switch from say Postgres to M$SQL, youl’ll have to test your codebase anyway. Fix the query and be done with it. Just don’t write complex queries from the start, just because you *might* need it sometime in the future (you never will!) – Stefan Haberl Feb 15 '19 at 10:09
3

IntelliJ IDEA complained about the `>` operator until I put parentheses around the comparison: `(count(scen) > 0)`. – Jens Piegsa Jul 15 '19 at 13:01
@JensPiegsa I think they fixed this now it works without parentheses – solujic Aug 13 '20 at 13:55
Hello from 2021, Idea 2021.1.1 still doesn't like expression without parentheses – ruX Jun 02 '21 at 10:07

Manhattan · Answer 3 · 2022-11-07T14:50:22.707

2

I had success with:

@Query(nativeQuery = true, value = "select if(count(name) > 0, 'true', 'false') from Scenario scene where scene.name = :name")
Boolean nameExists(@Param("name") String name);

NOTE: I'm using MySQL with MariaDB

edited Nov 07 '22 at 14:50

answered Nov 07 '22 at 14:36

Manhattan

51
4

score 1 · Answer 4 · answered Aug 27 '15 at 15:28

1

I was having the same problem, then I updated my hibernate to 4.3.11.Final and now it's working.

answered Aug 27 '15 at 15:28

Error

91
9

score 0 · Answer 5 · answered Feb 17 '23 at 13:26

0

Just use

@Query("select count(scen) > 0 from Scenario scen where scen.name = :name")

answered Feb 17 '23 at 13:26

Simon

997
1
15
29

JPQL statement returning boolean value

5 Answers5

Linked