JPA查询子句IN /无效的关系操作符

Question

如果参数不为空，如何在查询中包含IN子句?

如果我想把":ramos is null"它给出一个错误

@Query(value = "SELECT r FROM ParceiroNegocio r " +
" WHERE (:razaoSocial is null or UPPER(r.razaoSocial) LIKE CONCAT('%',UPPER(:razaoSocial),'%')) " +
"   AND (:nomeFantasia is null or UPPER(r.nomeFantasia) LIKE CONCAT('%',UPPER(:nomeFantasia),'%')) " +
"   AND (:cnpj is null or r.cnpj =:cnpj) " +
"   AND ((:ramos) is null or r.ramo IN (:ramos))")
Page<ParceiroNegocio> findByCnpjNomeFantasiaRazaoSocialRamoWithPagination(
@Param("razaoSocial") String razaoSocial,
@Param("nomeFantasia") String nomeFantasia,
@Param("cnpj") String cnpj,
@Param("ramos") List<Long> ramos,
Pageable pageable);

}

错误:

SqlExceptionHelperORA-00920:无效的关系运算符

Answer 1

COALESCE帮了我!:)

AND (COALESCE(:ramos) is null or r.ramo IN (:ramos))