达梦数据库使用jpa源生sql实现多条件查询

话不多说，直接上案例！

我要实现这么一个列表，看上去很简单吧！

只要在sql中判断一下条件值是否为空，不为空的话就拼接该条件即可。

在mysql中使用case when 即可解决这个问题。即判断条件为空就拼接上 “1=1” ，不为空就拼接上条件 如 “company_type=?1”

@Query(value = "select  play_name_zh playNameZh,import_company importCompany,company_type companyType,production_org productionOrg," +
            "feed_org feedOrg,producing_area producingArea,play_classification playClassification," +
            " theme_type themeType,sum(submit_num) submitNums,all_play_num allPlayNum, state state" +
            " from yj_item_plan where " +
                " (case WHEN ?1 is null then '1=1' else company_type end)=?1 " +
                " and  (case WHEN ?2 is null then '1=1' else play_classification end)=?2 " +
                " and  (case WHEN ?3 is null then '1=1' else theme_type end)=?3 " +
                " and  (case WHEN ?4 is null then '1=1' else state end)=?4 " +
            " group by state,play_name_zh,import_company,production_org,feed_org,producing_area,play_classification,theme_type ",
    countQuery = " 这里就省略了 "
            ,nativeQuery = true)
    Page<Map<String, Object>> findItemStatistics(Integer companyType, Integer playClassification, Integer themeType, Integer state, Pageable pageable);

但我们用的是达梦数据库，这就比较坑了，虽然达梦也有case when语法，但不支持那种写法。想了很久也试过很多种方式，最终还是选择用 or 来实现。如下：

@Query(value = "select  play_name_zh playNameZh,import_company importCompany,company_type companyType,production_org productionOrg," +
            "feed_org feedOrg,producing_area producingArea,play_classification playClassification," +
            " theme_type themeType,sum(submit_num) submitNums,all_play_num allPlayNum, state state" +
            " from yj_item_plan where " +
            " ((?1 is null) or company_type=?1) " +
            " and  ((?2 is null) or play_classification=?2) " +
            " and ((?3 is null) or theme_type=?3)  " +
            " and ((?4 is null) or state=?4) " +
            " group by state,play_name_zh,import_company,production_org,feed_org,producing_area,play_classification,theme_type ",
    countQuery = " 这里就省略了 "
            ,nativeQuery = true)
    Page<Map<String, Object>> findItemStatistics(Integer companyType, Integer playClassification, Integer themeType, Integer state, Pageable pageable);

"((?1 is null) or company_type=?1)"  如果表达式 (?1 is null) 为TRUE 就不执行后面的了，否则执行company_type=?1 。

另外还有一个问题，如果返回值类型是 Map(String , Object) ，在达梦数据库中，返回的字段全部为大写，但map.get("这里小写也可以获取到值")  ，这就很神奇，如果不调用map的get方法，返回到前端，依然是大写字段，操作起来就很麻烦，我们希望的是驼峰式命名格式，所以就给手动转了一下，大家有什么更好的方法吗？

欢迎大家点赞！留言！