lambdaQueryWrapper多条件嵌套查询
需求:根据条件获取一段时期内按照年份和周存储的数据
表结构如下
userNetType | moduleName | cityName | subjectCname | subjectEname | pv | uv | year | week |
---|---|---|---|---|---|---|---|---|
1 | 1 | 江苏省 | 死神专题 | sszt | 100 | 70 | 2019 | 51 |
1 | 1 | 江苏省 | 海贼王专题 | hzwzt | 200 | 80 | 2019 | 52 |
1 | 1 | 江苏省 | 火影忍者专题 | hyrzzt | 300 | 90 | 2020 | 01 |
//开始年份
String beginYear = null;
//结束年份
String endYear = null;
//开始周数
String beginWeek = null;
//结束周数
String endWeek = null;
if (StringUtils.isBlank(beginTime) || StringUtils.isBlank(endTime)) {
DateTime dateTime = DateUtil.lastWeek();
//格式化日期,结果:yyyyMMdd
beginTime = DateUtil.formatDate(dateTime);
beginYear = TimeUtils.getDateOfYearWeek(beginTime).get("year");
endYear = beginYear;
beginWeek = TimeUtils.getDateOfYearWeek(beginTime).get("week");
endWeek = beginWeek;
} else {
beginYear = TimeUtils.getDateOfYearWeek(beginTime).get("year");
endYear = TimeUtils.getDateOfYearWeek(endTime).get("year");
beginWeek = TimeUtils.getDateOfYearWeek(beginTime).get("week");
endWeek = TimeUtils.getDateOfYearWeek(endTime).get("week");
}
Page<DwSubjectDataInfoWw> page = new Page<>(pageNum, pageSize);
LambdaQueryWrapper<DwSubjectDataInfoWw> queryWrapper = Wrappers.<DwSubjectDataInfoWw>lambdaQuery();
if (beginYear.equals(endYear)) {
queryWrapper.eq(DwSubjectDataInfoWw::getYear, beginYear);
queryWrapper.between(DwSubjectDataInfoWw::getWeek, beginWeek, endWeek);
} else {
//因为Java8 Lambda表达式中最终变量问题,重新赋值一个参数解决
String year1 = beginYear;
String year2 = endYear;
String week1 = beginWeek;
String week2 = endWeek;
queryWrapper.and(wrapper -> wrapper.and(wrapper1 -> wrapper1.eq(DwSubjectDataInfoWw::getYear, year1).ge(DwSubjectDataInfoWw::getWeek, week1))
.or(wrapper2 -> wrapper2.eq(DwSubjectDataInfoWw::getYear, year2).le(DwSubjectDataInfoWw::getWeek, week2)));
}
queryWrapper.orderByDesc(DwSubjectDataInfoWw::getYear);
queryWrapper.orderByDesc(DwSubjectDataInfoWw::getWeek);
if (StrUtil.isNotEmpty(cityName)) {
queryWrapper.eq(DwSubjectDataInfoWw::getCityName, cityName);
}
if (StrUtil.isNotEmpty(userNetType)) {
queryWrapper.eq(DwSubjectDataInfoWw::getUserNetType, userNetType);
}
if (StrUtil.isNotEmpty(moduleName)) {
queryWrapper.eq(DwSubjectDataInfoWw::getModuleName, moduleName);
}
//搜索条件可以是专题中文名或英文名
if (StrUtil.isNotEmpty(keyWord)) {
queryWrapper.and(wrapper -> wrapper.like(DwSubjectDataInfoWw::getSubjectCname, keyWord).or().like(DwSubjectDataInfoWw::getSubjectEname, keyWord));
}
try {
Page<DwSubjectDataInfoWw> list = dwSubjectDataInfoWwService.page(page, queryWrapper);
return AjaxResult.success(list);
} catch (Exception e) {
logger.error("获取分周专题数据列表错误,错误信息为:", e);
return AjaxResult.error();
}
下面是根据条件生成的SQL语句
WHERE
(
(
(year = ? AND week >= ?)
OR (year = ? AND week <= ?)
)
AND city_name = ?
AND user_net_type = ?
AND module_name = ?
AND (
subject_cname LIKE ?
OR subject_ename LIKE ?
)
)
ORDER BY
year DESC,
week DESC
LambdaQueryWrapper 常用条件
以上为个人经验,希望能给大家一个参考,也希望大家多多支持编程网。