Jpa动态多表if多条件联合查询（if中包含list不为null和“=”的判断），并对查询结果进行分页

方法一：

public Page<Map<String, Object>> resourceList(TeachingInfo teachingInfo, IPage pageable) {
        ...
        if (pageable != null && StringUtils.isNotEmpty(pageable.getSortName())) {
            //驼峰转下划线
            pageable.setSortName(PublicUtils.humpToLine(pageable.getSortName()));
        }
        Page<TeachingInfo> result = resourceInfoRepository.findResourceByCondition(chapterId, contentType, courseIds, diffLevel, name, type, resourceType, PageUtils.getPageable(pageable));
        return result;
    }
    
    //if里的不为null是(!='')
    @Query(value = "select distinct ri.*, chapter_name, type  " +
            "from resource_info ri left join teaching_resource_info tri on tri.resource_id = ri.id left join teaching_info ti on ti.id = tri.teaching_id " +
            "where 1=1 " +
            "and IF (?1 != '', chapter_id = ?1, 1=1) " +
            "and IF (?2 != '', content_type = ?2, 1=1) " +
            "and (coalesce (?3 , null) is null or course_id in ( ?3 )) " +
            "and IF (?4 != '', diff_level = ?4, 1=1) " +
            "and IF (?5 != '', ri.name like %?5%, 1=1) " +
            "and IF (?6 != '', ti.type = ?6, 1=1) " +
            "and IF (?7 = 0, date_sub(create_date, interval 2 month) <= curdate(), 1=1) ", nativeQuery = true)
    Page<TeachingInfo> findResourceByCondition(Long chapterId, String contentType, List<Long> courseIds, String diffLevel, String name, Integer type, String resourceType, Pageable pageable);

     /**
     * 驼峰转下划线
     * @param str
     * @return
     */
    public static String humpToLine(String str) {
        Matcher matcher = humpPattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

方法二：

public Page<Map<String, Object>> resourceList(TeachingInfo teachingInfo, Pageable pageable) {
        ...
        //int offset = (pageable.getPageNumber() - 1) * pageable.getPageSize();
        List<Map<String, String>> result = resourceInfoRepository.findResourceByCondition(chapterId, contentType, courseId, diffLevel, name, type, resourceType, pageable.getOffset(), pageable.getPageSize());
        int total = resourceInfoRepository.findResourceCountByCondition(chapterId, contentType, courseId, diffLevel, name, type, resourceType);
        //MDStringUtils.formatHumpNameForList(result)将List中map的key值命名方式由下划线转为驼峰命名
        return new PageImpl<>(MDStringUtils.formatHumpNameForList(result), pageable, total);
    }
    
    //if里的不为null是(!='')
    @Query(value = "select distinct ri.*, chapter_name, type  " +
            "from resource_info ri left join teaching_resource_info tri on tri.resource_id = ri.id left join teaching_info ti on ti.id = tri.teaching_id " +
            "where 1=1 " +
            "and IF (?1 != '', chapter_id = ?1, 1=1) " +
            "and IF (?2 != '', content_type = ?2, 1=1) " +
            "and IF (?3 != '', course_id = ?3, 1=1) " +
            "and IF (?4 != '', diff_level = ?4, 1=1) " +
            "and IF (?5 != '', ri.name like %?5%, 1=1) " +
            "and IF (?6 != '', ti.type = ?6, 1=1) " +
            "and IF (?7 != '', resource_type = ?7, 1=1) limit ?8,?9", nativeQuery = true)
    List<Map<String, String>> findResourceByCondition(Long chapterId, String contentType, Long courseId, String diffLevel, String name, Integer type, String resourceType, long offset, int size);

    @Query(value = "select count(distinct ri.id) " +
            "from resource_info ri left join teaching_resource_info tri on tri.resource_id = ri.id left join teaching_info ti on ti.id = tri.teaching_id " +
            "where 1=1 " +
            "and IF (?1 != '', chapter_id = ?1, 1=1) " +
            "and IF (?2 != '', content_type = ?2, 1=1) " +
            "and IF (?3 != '', course_id = ?3, 1=1) " +
            "and IF (?4 != '', diff_level = ?4, 1=1) " +
            "and IF (?5 != '', ri.name like %?5%, 1=1) " +
            "and IF (?6 != '', ti.type = ?6, 1=1) " +
            "and IF (?7 != '', resource_type = ?7, 1=1) ", nativeQuery = true)
    int findResourceCountByCondition(Long chapterId, String contentType, Long courseId, String diffLevel, String name, Integer type, String resourceType);

 /**
     * 将List中map的key值命名方式格式化为驼峰
     *
     * @param
     * @return
     */
    public static List<Map<String, Object>> formatHumpNameForList(List<Map<String, String>> list) {
        List<Map<String, Object>> newList = new ArrayList<Map<String, Object>>();
        for (Map<String, String> o : list) {
            newList.add(formatHumpName(o));
        }
        return newList;
    }
    
    public static Map<String, Object> formatHumpName(Map<String, String> map) {
        Map<String, Object> newMap = new HashMap<String, Object>();
        Iterator<Map.Entry<String, String>> it = map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, String> entry = it.next();
            String key = entry.getKey();
            String newKey = toFormatCol(key);
            newMap.put(newKey, entry.getValue());
        }
        return newMap;
    }

    public static String toFormatCol(String colName) {
        StringBuilder sb = new StringBuilder();
        String[] str = colName.toLowerCase().split("_");
        int i = 0;
        for (String s : str) {
            if (s.length() == 1) {
                s = s.toUpperCase();
            }
            i++;
            if (i == 1) {
                sb.append(s);
                continue;
            }
            if (s.length() > 0) {
                sb.append(s.substring(0, 1).toUpperCase());
                sb.append(s.substring(1));
            }
        }
        return sb.toString();
    }