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]
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);
}
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
or
use
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.