Spring / JPA - niedziałające wyrażenie COALESCE

Korzystam z JPA / HIBERNATE, PostgreSQL oraz Springa. Próbuję użyć wyrażenia COALESCE w swoich zapytaniach w sposób podobny do poniższego przykładu:

@Query("SELECT s FROM com.entity.SomeEntity s WHERE s.price >= COALESCE(:price, 0)")
Page<SomeEntity> findEntities(@Param("price") Integer price, Pageable pageable);

W momencie kiedy wartość przekazana w parametrze price jest różna od null wszystko jest ok. W przeciwnym przypadku dostaję wyjątek:

org.postgresql.util.PSQLException: BŁĄD: COALESCE typy bytea i integer nie mogą być dopasowane

Próbowałem pierwszy parametr zrzutować na typ INTEGER:

@Query("SELECT s FROM com.entity.SomeEntity s WHERE s.price >= COALESCE(CAST (:price AS INTEGER), 0)")
Page<SomeEntity> findEntities(@Param("price") Integer price, Pageable pageable);

Jednak wtedy dostaje inny wyjątek:

java.lang.IllegalArgumentException: org.hibernate.QueryException: Could not resolve requested type for CAST : INTEGER [tu moje zapytanie]

Da się to jakos obejść? Dodam, że z pewnych względów zależy mi na tym COALESCE i obejście problemu na zasadzie "zamiast NULLa przekażę do repozytorium wartość 0" nie wchodzi w grę.

Rzutuj na Integer (patrz wielkosc liter)

Niestety, dalej to samo.

Wygląda jakbyś mieszał język ORM (np. JPQL) z językiem SQL. Proponuje zastosować NativeQuery i zrobić to natywnie. Ja w PostgreSQL rzutuje operatorem :: (niestety, czasem trzeba).

Ogólnie szedłbym w takie zapytanie SQL, które najpierw wytestowałbym w konsole SQL, dopiero później integrował z ORMem:

 SELECT t.a, t.b, t.c, COALESCE(t.price::INTEGER, 0) FROM some_entitity_table t WHERE t.price::INTEGER > ?1

Przy założeniu, że tabela some_entity_table ma kolumny a, b, c i price (gdzie może być nullem, który jeśli występuje, chcemy zastąpić zerem).

Potem NativeQuery dla EntityManagera.
?1 - parametr dla EntityManagera (wartosc od ktorej ma byc wiecej)
https://docs.jboss.org/hibernate/entitymanager/3.5/reference/en/html/query_native.html

margor90 napisał(a):

Wygląda jakbyś mieszał język ORM (np. JPQL) z językiem SQL.

Z powodu COALESCE? Wydawalo mi sie, ze uzycie COALESCE w zapytaniach JPQL jest jak najbardziej dozwolone :)

margor90 napisał(a):

Proponuje zastosować NativeQuery i zrobić to natywnie.

Problem tylko w tym, że stosując zapytania natywne stracę część funkcjonalności (np. stronnicowanie wyników, ale też nie tylko) na czym mi akurat zależy. Póki co postanowiłem rozwiązać problem tworząc nową funkcje SQL:

CREATE OR REPLACE FUNCTION CAST_AS_INTEGER(arg TEXT) RETURNS INTEGER AS $$
        BEGIN
                RETURN CAST(arg AS INTEGER);
        END;
$$ LANGUAGE plpgsql;

Poźniej można tę funkcje wykorzystać w następujący sposób:

@Query("SELECT s FROM com.entity.SomeEntity s WHERE s.price >= COALESCE(CAST_AS_INTEGER(:price), 0)")
Page<SomeEntity> findEntities(@Param("price") Integer price, Pageable pageable);

Może i jest to troszeczkę mieszanie języka natywnego z JPQL ale z racji korzyści, o których pisałem wyżej postanowiłem tak rozwiązać problem - no chyba, że ktoś ma lepszy pomysł to chętnie się czegoś nowego dowiem :)