COALESCE function in JPA

Question

I'm developing an application in the MVC layer pattern. In a part of the code where I noticed a GET query, from RecordRepository using the COALESCE function, it is returning the following error in the console, as shown in the image below. I'm configuring this function, because in Postgres it doesn't just work (:min IS NULL).

org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "NULL, ?"; SQL statement: select record0_.id as id1_2_, record0_.age as age2_2_, record0_.game_id as game_id5_2_, record0_.moment as moment3_2_, record0_.name as name4_2_ from tb_record record0_ where (coalesce(?, null) is null or record0_.moment>=?) and (coalesce(?, null) is null or record0_.moment<=?) order by record0_.moment desc limit ? [50004-214]

Erro Run Project

My code:

@Repository
public interface RecordRepository extends JpaRepository<Record, Long>{

    @Query("SELECT obj FROM Record obj WHERE "
            + "(COALESCE(:min, null) IS NULL OR obj.moment >= :min) AND "
            + "(COALESCE(:max, null) IS NULL OR obj.moment <= :max)")
    Page<Record> findByMoments(Instant min, Instant max, Pageable pageable);

}

Answer 1

I had the very same problem: COALESCE works with Postgress and it crashs the same exception with H2 database, so I created a workaround implementing two repositories interfaces (Product entity) and used annotation @profile, then in compilation time the correct query will be built. First of all I created a base repository interface without extends no other interface:

public interface ProductRepositoryBase {
    
    Page<Product>      findCustomized(List<Category> categories, String name, Pageable page);
    Optional<Product>  findById(Long id);
    Product save(Product entity);
    void deleteById(Long id);
}

Then I created two other interfaces (one for each profile), one for development with postgres db (dev):

@Profile("dev")  
public interface ProductRepositoryDev extends ProductRepositoryBase,JpaRepository<Product, Long>  {
    
    @Query("SELECT DISTINCT obj FROM Product obj INNER JOIN obj.categories cats "
             + "WHERE (COALESCE(:categories,null) IS NULL OR cats IN :categories) AND "
             + "(LOWER(obj.name) LIKE LOWER(CONCAT('%',:name,'%' )))")
        Page<Product> findCustomized(List<Category> categories, String name, Pageable page);

}

And another for test (where H2 database is used):

@Profile("test")
public interface ProductRepositoryTest extends ProductRepositoryBase,JpaRepository<Product, Long> {

    @Query("SELECT DISTINCT obj FROM Product obj INNER JOIN obj.categories cats "
             + "WHERE (:categories IS NULL OR cats IN :categories) AND "
             + "(LOWER(obj.name) LIKE LOWER(CONCAT('%',:name,'%' )))")
    Page<Product> findCustomized(List<Category> categories, String name, Pageable page);

}

Note that my problematic query was associated with findCustomized method, but in the base interface you must declare any other repository ,method you use in the Product entity service layer, otherwise when running the code it will crash as these functions are not defined in the base interface.

Also in the service layer you need to declare the base interface

@Service
public class ProductService {

    @Autowired
    private ProductRepositoryBase productRepository;

Another note: It seems that Spring Boot has tagged COALESCE as deprecated, a colegue of mine tested for Spring Boot 2.4.4 the query with COALESCE with H2 and it worked fine, but as I´m using 2.7.3 it raises the exception. Anyway the query writen in @Profile("test") worked fine for both databases (H2 and postgres).

Answer 2

By checking for null, you can force cast to timestamp/date type only when the H2/Postgres drivers are unable to indicate the proper type.

So instead of

@Query("SELECT obj FROM Record obj WHERE "
        + "(COALESCE(:min, null) IS NULL OR obj.moment >= :min) AND "
        + "(COALESCE(:max, null) IS NULL OR obj.moment <= :max)")

or

@Query("SELECT obj FROM Record obj WHERE "
        + "(:min IS NULL OR obj.moment >= :min) AND "
        + "(:max IS NULL OR obj.moment <= :max)")

use

@Query("SELECT obj FROM Record obj WHERE "
        + "(CAST(:min AS date) IS NULL OR obj.moment >= :min) AND "
        + "(CAST(:max AS date) IS NULL OR obj.moment <= :max)")

This will make the GET request with or without the date query parameters work both in the test environment with H2 and in the dev environment with Postgres, without the need to change your Query or create interfaces with profiles.