tk.mybatis零sql语句单表分页查询

		//开启分页
        Page<Student> page = PageHelper.startPage(1, 10);

        Weekend<Student> weekend = Weekend.of(Student.class);

        //筛选字段
        weekend.selectProperties(Student::getId, Student::getCode, Student::getName);
        
        weekend.orderBy(Student::getCode);//正序
        weekend.orderBy(Student::getName).desc();//倒序

        String key = "a";

        //查询条件1
        SqlCriteriaHelper<Student> where1 = SqlCriteriaHelper.custom(Student.class);
        where1.andLike(Student::getCode, key); //like 会自动加%
        where1.orLike(Student::getName, key);

        //查询条件2
        SqlCriteriaHelper<Student> where2 = SqlCriteriaHelper.custom(Student.class);
        where2.andEqualTo(Student::getClassId, null); //当为null时，不参与查询条件
        where2.andEqualTo(Student::getFlag, 1);
        where2.andEqualTo(Student::getStatus, "1");
        where2.andLessThanOrEqualTo(Student::getCreateTime, LocalDateTime.now());

        //查询条件1 and 查询条件2
        Example example = Example.builder(Student.class).andWhere(where1).andWhere(where2).build();

        //weekend实际上是一个Example,因此可以将weekend和example进行查询条件合并
        //where del = 0 and (查询条件1 and 查询条件2)
        //逻辑删除字段会自动填充，如：del=0
        weekend.getOredCriteria().addAll(example.getOredCriteria());
        
        this.selectByExample(weekend);

sql执行结果:

SELECT TOP
	10 id,
	code,
	name 
FROM
	(
	SELECT
		ROW_NUMBER ( ) OVER ( ORDER BY code, name DESC ) PAGE_ROW_NUMBER,
		id,
		code,
		name 
	FROM
		(
		SELECT
			id,
			code,
			name 
		FROM
		STUDENT
		WHERE
			( ( code LIKE '%a%' OR name LIKE '%a%' ) AND ( last_flag = 1 AND status = '1' AND create_time <= '2022-02-23T18:06:12.004' ) ) 
			AND del = 0 
		) AS PAGE_TABLE_ALIAS 
	) AS PAGE_TABLE_ALIAS 
WHERE
	PAGE_ROW_NUMBER > 0 
ORDER BY
	PAGE_ROW_NUMBER;

优点：

Weekend 对于字段筛选和排序较为友好，避免输入字符串
SqlCriteriaHelper：默认 value=null 则不参与查询, like查询已自动加了%号

SqlCriteriaHelper的部分源码:

    /**
     * AND column != value
     * 默认 value=null 则不参与查询
     * @param fn
     * @param value
     * @return
     */
    public SqlCriteriaHelper<T> andNotEqualTo(Fn<T, Object> fn, Object value) {
        return this.andNotEqualTo(fn, value, false);
    }

    /**
     *  AND column != value
     * @param fn
     * @param value
     * @param required false 当value=null 则不参与查询 ;
     *                 true 当value = null 则转 is not null 查询 ： AND column is not null
     *
     * @return
     */
    public SqlCriteriaHelper<T> andNotEqualTo(Fn<T, Object> fn, Object value, boolean required) {
        if(Optional.ofNullable(value).isPresent()){
            this.criteria.getCriterions().add(new Sqls.Criterion(Reflections.fnToFieldName(fn), value, "<>", "and"));
        }else {
            if(required){
                //转非空查询
                this.andIsNotNull(fn);
            }
        }
        return this;
    }

like 查询自动加了%

	/**
     * AND column LIKE %value%
     * 当 value = null 则当前属性不参与查询
     * @param fn
     * @param value
     * @return
     */
    public SqlCriteriaHelper<T> andLike(Fn<T, Object> fn, String value) {
        if(Optional.ofNullable(value).isPresent()){
            value = "%"+value+"%";
            this.criteria.getCriterions().add(new Sqls.Criterion(Reflections.fnToFieldName(fn), value, "like", "and"));
        }
        return this;
    }