lambdaQueryWrapper多条件嵌套查询方式

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

/**
     * @param pagenum     当前页
     * @param pagesize    每页条数
     * @param modulename  产品
     * @param usernettype 运营商
     * @param cityname    城市名称
     * @param begintime   开始周的任意一天日期（例如：2019-12-20）
     * @param endtime     结束周的任意一天日期（例如：2020-01-20）
     * @param keyword     查询条件（专题英文名或者中文名）
     * @return
     */
        //开始年份
        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 常用条件

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。