pgsql中通过coalesce()函数实现对字段使用in关键字(jpa)
@Query(value = "select * from tb_ajjbxx " +
"where 1=1 " +
"AND (coalesce(:#{#param.barPoliceNo},null) is null or \"bar1_police_no\" in (:#{#param.barPoliceNo}) OR coalesce(:#{#param.barPoliceNo},null) is null or \"bar2_police_no\" in (:#{#param.barPoliceNo}))" +
" order by \"SLRQ\" desc"
, nativeQuery = true)
Page<Ajjbxx> getDataByList(@Param("param") QueryDetalParam param,Pageable pageable);
相当于sql语句:select * from tb_ajjbxx where 1=1 AND ( bar1_police_no in (‘test1’,‘test2’) or bar2_police_no in (‘test1’,‘test2’)) order by “SLRQ” desc;
注:parpm的barPoliceNo字段类型为List。
(记录一下!)