jpa 跨表_Jpa动态多表多条件联合查询，并对查询结果进行分页

public Page> resourceList(TeachingInfo teachingInfo, Pageable pageable) {

...

//int offset = (pageable.getPageNumber() - 1) * pageable.getPageSize();

List> 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> 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> formatHumpNameForList(List> list) {

List> newList = new ArrayList>();

for (Map o : list) {

newList.add(formatHumpName(o));

}

return newList;

}

public static Map formatHumpName(Map map) {

Map newMap = new HashMap();

Iterator> it = map.entrySet().iterator();

while (it.hasNext()) {

Map.Entry 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();

}