SpringDateJpa动态查询记录

    //时间格式2000-07
    //SELECT *
    //FROM `water_monthly_statistics`;
    //where ((year>2018 and year <2020) or (year=2018 and month>=5) or(year=2020 and month<=4)) and region_id in(1,2)
    private Specification<WaterMonthlyStatistics> getSpecByYearAndMonth(Integer regionId, String startTime, String endTime) {
        return (root, query, cb) -> {
            int startYear = 1900;
            int startMonth = 1;
            int endYear = 2100;
            int endMonth = 11;
            List<Predicate> l1 = new ArrayList<>();
            List<Integer> ids = getRegionId(regionId == null ? 0 : regionId);
            l1.add(cb.in(root.get("regionId")).value(ids));
            Predicate p1;
            if (startTime != null) {
                String[] start = startTime.split("-");
                try {
                    startYear = Integer.valueOf(start[0]);
                    startMonth = Integer.valueOf(start[1]);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (endTime != null) {
                String[] end = endTime.split("-");
                try {
                    endYear = Integer.valueOf(end[0]);
                    endMonth = Integer.valueOf(end[1]);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (startYear == endYear) {
                List<Predicate> l2 = new ArrayList<>();
                //year==2019 and month>4 and month<9
                l2.add(cb.equal(root.get("year"), startYear));
                l2.add(cb.greaterThanOrEqualTo(root.get("month"), startMonth));
                l2.add(cb.lessThanOrEqualTo(root.get("month"), endMonth));
                p1 = cb.and(l2.toArray(new Predicate[l2.size()]));
            } else if (startYear > endYear) {//不存在
                p1 = cb.and(cb.equal(root.get("year"), startYear), cb.equal(root.get("year"), endYear));
            } else {
                //((year>2018 and year <2020) or (year=2018 and month>5) or(year=2020 and month<4))
                List<Predicate> l2 = new ArrayList<>();
                //(year>2018 and year <2020)
                l2.add(cb.and(cb.greaterThan(root.get("year"), startYear), cb.lessThan(root.get("year"), endYear)));
                //(year=2018 and month>=5)
                l2.add(cb.and(cb.equal(root.get("year"), startYear), cb.greaterThanOrEqualTo(root.get("month"), startMonth)));
                //(year=2020 and month<=4))
                l2.add(cb.and(cb.equal(root.get("year"), endYear), cb.lessThanOrEqualTo(root.get("month"), endMonth)));
                p1 = cb.or(l2.toArray(new Predicate[l2.size()]));
            }
            l1.add(p1);
            return cb.and(l1.toArray(new Predicate[l1.size()]));
        };
    }

参考文章：https://www.cnblogs.com/dslx/p/11474453.html