Jpa动态多表if多条件联合查询，并对查询结果进行分页
2024-08-27 04:03:17
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();
}
最新文章
- excel批处理_判断一个名称是不是药品
- ubuntu nginx 安装以及配置文件详解
- [Eclipse] eclipse中打开xml文件，使用ctrl+鼠标左键无法跳转至Java源文件【待解决】
- Aspose 强大的服务器端 excel word ppt pdf 处理工具
- Java的Properties类和读取.properties文件
- Shell基础：Linux权限管理
- MongoDB学习笔记(六) MongoDB索引用法和效率分析
- 安卓常用 widget
- 学习笔记——观察者模式Observer
- Android 9Patch图片的使用-android学习之旅（十八）
- 二叉查找树之 Java的实现
- 图像分析函数：skimage.measure中的label、regionprops
- git reset 和 git revert 使用区别
- Freeswitch 入门
- mysql日期相关的函数
- Java 的JAR包、EAR包、WAR包区别
- 奔跑吧DKY——团队Scrum冲刺阶段-Day 4
- Leetcode题库——34.在排序数组中国查找元素的第一个和最后一个位置
- lua入门之二：c/c++ 调用lua及多个函数返回值的获取
- 3d标签云（JS版）