Search code examples
javapostgresqlhibernatejpahql

JPA @Query with 'case when exists' does not work with Hibernate

I have a @Repository interface with the following method for checking IF database contains a record colliding (in business domain meaning) with the one I'm about to persist (I don't care about WHAT the colliding records are):

@Query("select case when exists (
            select me from MyEntity me where {my conditions regarding someParam here}
        ) then true else false end from MyEntity")
boolean findColliding(@Param("someParam") String someParam);

The table on which I run it is empty (PostgreSQL) therefore the exists subquery shouldn't find anything and I believe the whole method should return false as case states it can only return true if exists and false otherwise.

It returns null #facepalm

My query passes query syntax check on startup (no QuerySyntaxException) but throws exception when executed:

org.springframework.aop.AopInvocationException: Null return value from advice does not match primitive return type for: public abstract boolean findColliding(...)

What am I doing wrong? Should I take some other approach for my problem?

Hibernate 5.0.11.Final

Solution

  • JPA 2.1 CASE expression only supports scalar expressions, not queries.

    For more details, check out the JPA specification.

    If the DB supports this syntax, you need to use a native SQL query instead.