方法一：sql直接拼接

server

Map page = new HashMap();
page.put("ROWNUM",Integer.parseInt(request.getPage())*Integer.parseInt(request.getLimit()));
page.put("RN",Integer.parseInt(request.getPage())-1);
List<QueryEntity> orderList = dao.getQuery(page,param);

Dao

List<QueryEntity>getQuery(Map page, Map param);

mapper

<select id="getQuery" resultType="com.xxx.entity.QueryEntity" parameterType="java.util.Map">
SELECT *
        FROM (SELECT TMP.*, ROWNUM ROW_ID
              FROM (
                select
                    t.ID as ID,
                    t.NAME as NAME
                from stable t
                    where 1 = 1
                  and to_char(time, 'yyyy-mm-dd')   <![CDATA[<=]]> to_char(to_date(#{param.endtime}, 'yyyy-mm-dd'),'yyyy-mm-dd')
                  and to_char(time, 'yyyy-mm-dd') >= to_char(to_date(#{param.starttime}, 'yyyy-mm-dd'),'yyyy-mm-dd')
                order by time desc
        ) TMP
        WHERE ROWNUM <![CDATA[<=]]> #{page.ROWNUM})
        WHERE ROW_ID > #{page.RN}
</select>

注意上面的sql中，select 的字段要重新定义参数，不然sql报错

方法二：使用mybatis-plus的iPage封装sql

Service

//import com.baomidou.mybatisplus.core.metadata.IPage;
//import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
            Page<QueryEntity> page = new Page<>(Integer.parseInt(request.getPage()),Integer.parseInt(request.getLimit()));
            IPage iPage =  dao.getQuery(page,param);
            List<QueryEntity> list = iPage.getRecords();

Dao

//import org.apache.ibatis.annotations.Mapper;
//import org.apache.ibatis.annotations.Param;
//import com.baomidou.mybatisplus.core.mapper.BaseMapper;
//import com.baomidou.mybatisplus.core.metadata.IPage;
//import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
//@Mapper
//public interface ResourceDao extends BaseMapper<RequestEntity> {
    IPage<Map<String,Object>> getQuery(Page<QueryEntity> page, @Param("param") Map param);

mapper

<select id="getQuery" resultType="com.xxx.entity.QueryEntity" parameterType="java.util.Map">
        select
            t.ID as ID,
            t.NAME as NAME
        from stable t
        where 1 = 1
          and to_char(TIME, 'yyyy-mm-dd')   <![CDATA[<=]]> to_char(to_date(#{param.endtime}, 'yyyy-mm-dd'),'yyyy-mm-dd')
          and to_char(TIME, 'yyyy-mm-dd') >= to_char(to_date(#{param.starttime}, 'yyyy-mm-dd'),'yyyy-mm-dd')
        order by TIME desc

    </select>

注意实体对象与表结构字段一一对应，会报错，注意参数格式

方法三：使用mybatis-plus的 QueryWrapper 直接拼接

Contoller

//import com.xxx.common.utils.PageUtils;  自定义封装的page工具类
    @RequestMapping("/list")
    public R list(@RequestParam Map<String, Object> params){
        try{
            PageUtils page = thService.queryPage(params);
            logger.info("${comments}:  成功");
            return R.ok().put("page", page);
        }catch (Exception e){
            logger.error("${comments}: 失败:",e);
            return R.error();
        }
    }

Service

//import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
//import com.baomidou.mybatisplus.core.metadata.IPage;
//import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
//import com.xxx.common.utils.Query; 自定义封装的Query工具类
//mport com.xxx.common.utils.PageUtils; 自定义封装的Query工具类
    @Override
    public PageUtils queryPage(Map<String, Object> params) {
        IPage<thEntity> page = this.page(
                new Query<thEntity>().getPage(params),
                new QueryWrapper<thEntity>()
                  .eq(  "STATE","0")
                  .apply(  params.containsKey("createTimeStart")," CREATE_TIME  BETWEEN  to_date('"+params.get("createTimeStart")+"', 'yyyy-mm-dd') AND to_date('"+params.get("createTimeEnd")+"', 'yyyy-mm-dd')")
                      .orderByDesc("CREATE_TIME")
        );
        return new PageUtils(page);
    }

params内封装了分页参数（PAGE，LIMIT），自定义封装工具完成进一步的调用

相关pom依赖

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>com.baomidou</groupId>
                    <artifactId>mybatis-plus-generator</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>${mybatisplus.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!--oracle驱动-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>${oracle.version}</version>
        </dependency>