jeecg-boot通过sql获取的数据集，实现分页查询

jeecg-boot通过sql获取的数据集，实现分页查询

1、controller实现

/**
     * 分页列表查询（SQL）
     *
     * @param cronJobList
     * @param pageNo
     * @param pageSize
     * @param req
     * @return
     */
    @AutoLog(value = "发布任务清单表SQL-分页列表查询")
    @ApiOperation(value = "发布任务清单表SQL-分页列表查询", notes = "发布任务清单表SQL-分页列表查询")
    @GetMapping(value = "/list2")
    public Result<?> queryPageListBySql(CronJobList cronJobList,
                                        @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                        @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                        HttpServletRequest req) {
        Page<CronJobList> page = new Page<CronJobList>(pageNo, pageSize);
        IPage<CronJobList> pageList = cronJobListService.queryPageListBySql(page, cronJobList);
        return Result.OK(pageList);
    }

2、service层

2.1接口

package org.jeecg.modules.task.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import org.jeecg.modules.task.entity.CronJobList;

/**
 * @Description: 发布任务清单表
 * @Author: jeecg-boot
 * @Date: 2022-09-29
 * @Version: V1.0
 */
public interface ICronJobListService extends IService<CronJobList> {
    IPage<CronJobList> queryPageListBySql(Page<CronJobList> page, CronJobList cronJobList);
}

2.2实现

package org.jeecg.modules.task.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.jeecg.modules.task.entity.CronJobList;
import org.jeecg.modules.task.mapper.CronJobListMapper;
import org.jeecg.modules.task.service.ICronJobListService;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @Description: 发布任务清单表
 * @Author: jeecg-boot
 * @Date: 2022-09-29
 * @Version: V1.0
 */
@Service
public class CronJobListServiceImpl extends ServiceImpl<CronJobListMapper, CronJobList> implements ICronJobListService {
    @Override
    public IPage<CronJobList> queryPageListBySql(Page<CronJobList> page, CronJobList cronJobList) {
        List<CronJobList> cronJobLists = this.baseMapper.queryPageListBySql(page, cronJobList);
        return page.setRecords(cronJobLists);
    }
}

3、mapper接口

package org.jeecg.modules.task.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.jeecg.modules.task.entity.CronJobList;

import java.util.List;

/**
 * @Description: 发布任务清单表
 * @Author: jeecg-boot
 * @Date: 2022-09-29
 * @Version: V1.0
 */
public interface CronJobListMapper extends BaseMapper<CronJobList> {
    List<CronJobList> queryPageListBySql(Page<CronJobList> page, CronJobList dto);
}

4、xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.task.mapper.CronJobListMapper">

    <!--查list分组归并，状态、时间排序-->
    <select id="queryPageListBySql" parameterType="Object"
            resultType="org.jeecg.modules.task.entity.CronJobList">
        SELECT
        cjl.*
        FROM
        CRON_JOB_LIST cjl
        LEFT JOIN (
        SELECT
        LIST_CODE,
        CASE

        WHEN min( CASE WHEN CRON_STATUS = 1 THEN 1 ELSE 0 END ) = 0 THEN
        0 ELSE 1
        END rn
        FROM
        CRON_JOB_LIST cjl
        GROUP BY
        LIST_CODE
        ) tt ON cjl.LIST_CODE = tt.list_code
        WHERE
        1 = 1
        <if test="dto.cronName !=null and dto.cronName != ''">
            AND cjl.cron_name like concat(concat('%',#{dto.cronName}),'%')
        </if>
        <if test="dto.demindCycletxt !=null and dto.demindCycletxt != ''">
            AND cjl.demind_cycletxt like concat(concat('%',#{dto.demindCycletxt}),'%')
        </if>
        <if test="dto.cronStatus !=null">
            AND cjl.cron_status = #{dto.cronStatus}
        </if>
        ORDER BY
        tt.rn,
        DEMIND_DATE DESC

    </select>

</mapper>