JPA中COALESCE函数查询报错求助：H2数据库未知数据类型异常

开发MVC架构应用时，在RecordRepository的JPQL查询中使用COALESCE函数处理可选时间范围参数，在H2数据库中触发如下错误：

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]

使用COALESCE是因为在PostgreSQL中直接判断:min IS NULL不生效，当前Repository代码如下：

@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);

}

原因分析 #

H2对参数类型推断的要求比PostgreSQL严格，当:min/:max为null时，COALESCE(:min, null)里的null没有明确类型，H2无法识别，从而抛出类型未知的错误。

方案1：给NULL指定明确类型 #

修改JPQL中的COALESCE调用，通过CAST给null指定与moment字段匹配的类型（Instant对应数据库的TIMESTAMP类型），同时兼容PostgreSQL：

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

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

}

方案2：用JPA Specifications构建动态查询 #

直接根据参数是否为null动态拼接查询条件，彻底避开数据库兼容性问题，代码可读性也更强：

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

    default Page<Record> findByMoments(Instant min, Instant max, Pageable pageable) {
        return findAll((root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (min != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("moment"), min));
            }
            if (max != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("moment"), max));
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        }, pageable);
    }

}

方案3：分数据库配置查询（可选） #

如果必须保留COALESCE写法，可以针对H2和PostgreSQL分别配置查询，再通过服务层根据当前数据库选择调用：

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

    // PostgreSQL版本
    @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> findByMomentsForPostgres(Instant min, Instant max, Pageable pageable);

    // H2版本
    @Query("SELECT obj FROM Record obj WHERE "
            + "(COALESCE(:min, CAST(null AS TIMESTAMP)) IS NULL OR obj.moment >= :min) AND "
            + "(COALESCE(:max, CAST(null AS TIMESTAMP)) IS NULL OR obj.moment <= :max)")
    Page<Record> findByMomentsForH2(Instant min, Instant max, Pageable pageable);

}

内容的提问来源于stack exchange，提问作者dlima