jpa连mysql没什么说的，但是jpa连informix真的坑到家了。各种函数不支持。
需求：在自定义sql的时候，需要判断参数是否为空，如果参数为空则不参与sql条件判断。
就这么简单的一个需求，如果是jpa连mysql，那就用几个if判断就完事了，但是，informix不行。要用case when then else end。目前我只找到这个方法，后续有其他方法再更新，也欢迎大家提供其他方式。下面给出一个示例
jpa连mysql时：
@Query(value = "SELECT\n" +
"name, \n" +
"home \n" +
"FROM\n" +
"USER \n" +
"WHERE\n" +
" 1 = 1 \n" +
"and IF(:#{#req.name} is not null and :#{#req.name} != '',name = :#{#req.name},1=1)\n"+
"and IF(:#{#req.home} is not null and :#{#req.home} != '',home = :#{#req.home},1=1)\n", countProjection = "id", nativeQuery = true)
Page<Map<String, Object>> listByPage(@Param("req") UserQueryRequest request, Pageable pageable);
jpa连informix时：
@Query(value = "SELECT\n" +
"name, \n" +
"home \n" +
"FROM\n" +
"USER \n" +
"WHERE\n" +
" 1 = 1 \n" +
" and (case when :#{#req.name} is not null and :#{#req.name} != '' then name else '1' end) = \n"+
" (case when :#{#req.name} is not null and :#{#req.name} != '' then :#{#req.name} else '1' end) \n"+
" and (case when :#{#req.home} is not null and :#{#req.home} != '' then home else '1' end) = \n"+
" (case when :#{#req.home} is not null and :#{#req.home} != '' then :#{#req.home} else '1' end) \n"+
" and (case when :#{#req.createTime} is not null then to_char(create_time,'%Y-%m-%d') else '1' end) = \n"+
" (case when :#{#req.createTime} is not null then to_char(:#{#req.createTime},'%Y-%m-%d') else '1' end) \n",
countProjection = "id", nativeQuery = true)
Page<Map<String, Object>> listByPage(@Param("req") UserQueryRequest request, Pageable pageable);
注意很多细节：
1.informix里面case when (断言) then (列｜值) else (列｜值) end
我是用两组case when来组合判断的（这是我想到的办法，欢迎大家提供其他办法）
2.很多时候需要把case when 里面的（列｜值）转成字符串比较才行，不然要报错，比如我这里是’1’ = ‘1’。如果直接是1=1会报错。
3.在时间比较的时候需要将（列｜值）都转成字符串比较，不然会报错。比如我这里用到的是：to_char(create_time,‘%Y-%m-%d’) = to_char(:#{#req.createTime},‘%Y-%m-%d’)
4.在自定义sql的时候注意空格
