如何在JPQL/JpaRepository中使用可选参数并兼容H2数据库？

我当前使用的JPQL查询如下：

@Query("SELECT new ResumoHistorico(c.id, c.nome, h.isParalisado, COUNT(h), SUM(h.valor), c.isPrioritaria) "
        + "FROM Historico h "
        + "JOIN h.categorias c "
        + "WHERE h.dataRef = :dataRef "
        + "AND h.dataProjecao <= :dataProjecao "
        + "AND c IN :categorias "
        + "AND c.isAgrupador = false "
        + "AND (COALESCE(:agencias, NULL) IS NULL OR h.agencia IN :agencias) "
        + "GROUP BY c.id, c.nome, c.isPrioritaria, h.dataPrevisao, h.isParalisado")

其中AND (COALESCE(:agencias, NULL) IS NULL OR h.agencia IN :agencias)这段逻辑是为了在:agencias参数为null时忽略该条件，但遇到了兼容性问题：

• 该写法在应用中正常运行，但在H2数据库的仓库测试中插入测试数据时，抛出异常：

  Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "NULL, ?"; SQL statement:
  

• 若改为AND (:agencias IS NULL OR h.agencia IN :agencias)，测试可正常执行，但应用会报错：

  org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector}
  

解决方案 #

方案1：使用Spring Data JPA的Specification动态构建查询

通过Specification可以根据参数是否为空动态拼接查询条件，彻底规避JPQL条件判断的兼容性问题。

示例代码：

public interface HistoricoRepository extends JpaRepository<Historico, Long>, JpaSpecificationExecutor<Historico> {

    default List<ResumoHistorico> findResumoHistorico(LocalDate dataRef, LocalDate dataProjecao, List<Categoria> categorias, List<Agencia> agencias) {
        Specification<Historico> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            
            // 基础固定条件
            predicates.add(cb.equal(root.get("dataRef"), dataRef));
            predicates.add(cb.lessThanOrEqualTo(root.get("dataProjecao"), dataProjecao));
            
            // 关联Categoria的条件
            Join<Historico, Categoria> categoriaJoin = root.join("categorias");
            predicates.add(categoriaJoin.in(categorias));
            predicates.add(cb.equal(categoriaJoin.get("isAgrupador"), false));
            
            // 处理可选参数agencias
            if (agencias != null && !agencias.isEmpty()) {
                predicates.add(root.get("agencia").in(agencias));
            }
            
            // 构建分组和投影
            query.multiselect(
                categoriaJoin.get("id"),
                categoriaJoin.get("nome"),
                root.get("isParalisado"),
                cb.count(root),
                cb.sum(root.get("valor")),
                categoriaJoin.get("isPrioritaria")
            )
            .groupBy(
                categoriaJoin.get("id"),
                categoriaJoin.get("nome"),
                categoriaJoin.get("isPrioritaria"),
                root.get("dataPrevisao"),
                root.get("isParalisado")
            );
            
            return cb.and(predicates.toArray(new Predicate[0]));
        };
        
        return findAll(spec).stream()
            .map(result -> new ResumoHistorico(
                (Long) result[0],
                (String) result[1],
                (Boolean) result[2],
                (Long) result[3],
                (BigDecimal) result[4],
                (Boolean) result[5]
            ))
            .collect(Collectors.toList());
    }
}

方案2：调整JPQL写法兼容多数据库

如果不想改用Specification，可以利用集合的IS EMPTY方法替代null判断（要求:agencias参数为Collection类型，比如List<Agencia>）：

@Query("SELECT new ResumoHistorico(c.id, c.nome, h.isParalisado, COUNT(h), SUM(h.valor), c.isPrioritaria) "
        + "FROM Historico h "
        + "JOIN h.categorias c "
        + "WHERE h.dataRef = :dataRef "
        + "AND h.dataProjecao <= :dataProjecao "
        + "AND c IN :categorias "
        + "AND c.isAgrupador = false "
        + "AND (:agencias IS EMPTY OR h.agencia IN :agencias) "
        + "GROUP BY c.id, c.nome, c.isPrioritaria, h.dataPrevisao, h.isParalisado")

注意：使用该写法需要在调用时将null参数转为空集合，可在Repository中添加适配方法：

List<ResumoHistorico> findResumoHistorico(
    @Param("dataRef") LocalDate dataRef,
    @Param("dataProjecao") LocalDate dataProjecao,
    @Param("categorias") List<Categoria> categorias,
    @Param("agencias") List<Agencia> agencias
);

// 参数适配，将null转为空集合
default List<ResumoHistorico> findResumoHistoricoWithOptionalAgencias(LocalDate dataRef, LocalDate dataProjecao, List<Categoria> categorias, List<Agencia> agencias) {
    return findResumoHistorico(dataRef, dataProjecao, categorias, agencias == null ? Collections.emptyList() : agencias);
}

方案3：使用Hibernate@Filter（适合全局常用过滤）

如果该可选参数是全局频繁使用的过滤条件，可以定义Hibernate过滤器按需启用：

1. 在Historico实体上定义过滤器：

@Entity
@FilterDef(name = "filterByAgencias", parameters = @ParamDef(name = "agencias", type = "list"))
@Filter(name = "filterByAgencias", condition = "agencia in (:agencias)")
public class Historico {
    // 实体字段定义...
}

2. 在Repository方法中控制过滤器启用：

@Transactional
public List<ResumoHistorico> findResumoHistorico(LocalDate dataRef, LocalDate dataProjecao, List<Categoria> categorias, List<Agencia> agencias) {
    Session session = entityManager.unwrap(Session.class);
    if (agencias != null && !agencias.isEmpty()) {
        session.enableFilter("filterByAgencias").setParameterList("agencias", agencias);
    }
    
    // 执行移除agencias条件后的JPQL查询
    Query query = entityManager.createQuery("SELECT new ResumoHistorico(c.id, c.nome, h.isParalisado, COUNT(h), SUM(h.valor), c.isPrioritaria) "
            + "FROM Historico h "
            + "JOIN h.categorias c "
            + "WHERE h.dataRef = :dataRef "
            + "AND h.dataProjecao <= :dataProjecao "
            + "AND c IN :categorias "
            + "AND c.isAgrupador = false "
            + "GROUP BY c.id, c.nome, c.isPrioritaria, h.dataPrevisao, h.isParalisado");
    
    query.setParameter("dataRef", dataRef);
    query.setParameter("dataProjecao", dataProjecao);
    query.setParameter("categorias", categorias);
    
    return query.getResultList();
}

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