MyBatis实现分页

mybaits-plus分页方式：

1. 通过使用集合的subList方式分页

2. 通过使用sql语句分页

3. 通过使用自定义拦截器实现分页

4. 通过使用RowBounds实现分页

案例实现

1. 导入pom依赖

      <!--这个是mybaits依赖-->
      <!-- <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.1</version>
        </dependency> -->

      <!--我这里使用的是mybaits-plus分页，都是一样的-->
       <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

2. application.yml配置

server:
  port: 9090
  servlet:
    context-path: /
spring:
  application:
    name: oil_1201
  mvc:
    static-path-pattern: /**
#  thymeleaf:
#    prefix: classpath:/templates/
#    check-template-location: true
#    cache: false
#    suffix: .html #模板后缀
#    encoding: UTF-8 #编码
#    mode: HTML #模板
    servlet:
      content-type: text/html

  web:
    resources:
      static-locations: classpath:/static/


  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:mysql://192.168.224.123:3306/shiro2?useJDBCCompliantTimezoneShift=true&serverTimezone=UTC
    username: root
    password: root
  redis:
    host: localhost
    port: 6379
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  type-aliases-package: com.examplae.shirorealms.**
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-column-underline: true
    db-config:
      id-type: auto
#  config-location: classpath:mybatisconfig.xml

3.实现分页

2.1使用数组方式在serviceImpl中实现

 public List<TestDO> findAll(int page, int pageSize) {
        // 使用数组
        List<TestDO> testDOS = test1.selectList(null);
        // 从那条数据开始
        int fristIndex=(page-1)*pageSize;

        // 到第几条数据结束
        int endIndex=page*pageSize;

       return testDOS.subList((page-1)*pageSize,(pageSize*page));

3.2使用sql语句分页

编写mapper.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="com.example.shirorealms.mapper.TestMapper">

    <!--查询数据分页-->
    <select id="findLimt" resultType="com.example.shirorealms.entity.TestDO">
        select * from test limit #{page},#{pageSize}
    </select>

</mappper>

编写DAO接口

public interface TestMapper extends BaseMapper<TestDO> {

    //  sql分页
    public List<TestDO>findLimt(int page,int pageSize);
}

编写serviceImpl

@Service
public class TestService extends ServiceImpl<TestMapper, TestDO> implements ITestService {

    @Autowired
    private  TestMapper test1;
    @Override
    public List<TestDO> findAll(int page, int pageSize) {
         // 使用sql分页

        // 从那条数据开始
        int fristIndex=(page-1)*pageSize;

        // 到第几条数据结束
        int endIndex=page*pageSize;

        return test1.findLimt(fristIndex, endIndex);
}

3.3使用拦截器实现分页

• 自定义拦截器：

package com.example.shirorealms.interceptor;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import java.sql.Connection;
import java.util.Map;
import java.util.Properties;

/**
 * hmg
 */

/**
 * @Intercepts 说明是一个拦截器
 * @Signature 拦截器的签名
 * type 拦截的类型 四大对象之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)
 * method 拦截的方法
 * args 参数
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MyPageInterceptor implements Interceptor {

    //每页显示的条目数
    private int pageSize;
    //当前现实的页数
    private int page;

    private String dbType;


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //获取StatementHandler，默认是RoutingStatementHandler
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //获取statementHandler包装类
        MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);

        //分离代理对象链
        while (MetaObjectHandler.hasGetter("h")) {
            Object obj = MetaObjectHandler.getValue("h");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }

        while (MetaObjectHandler.hasGetter("target")) {
            Object obj = MetaObjectHandler.getValue("target");
            MetaObjectHandler = SystemMetaObject.forObject(obj);
        }

        //获取连接对象
        //Connection connection = (Connection) invocation.getArgs()[0];


        //object.getValue("delegate");  获取StatementHandler的实现类

        //获取查询接口映射的相关信息
        MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");
        String mapId = mappedStatement.getId();

        //statementHandler.getBoundSql().getParameterObject();

        //拦截以.ByPage结尾的请求，分页功能的统一实现
        if (mapId.matches(".+ByPage$")) {
            //获取进行数据库操作时管理参数的handler
            ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
            //获取请求时的参数
            Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
            //也可以这样获取
            //paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();

            //参数名称和在service中设置到map中的名称一致
            page = (int) paraObject.get("page");
            pageSize = (int) paraObject.get("pageSize");

            String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql");
            //也可以通过statementHandler直接获取
            //sql = statementHandler.getBoundSql().getSql();

            //构建分页功能的sql语句
            String limitSql;
            sql = sql.trim();
            limitSql = sql + " limit " + (page - 1) * pageSize + "," + pageSize;

            //将构建完成的分页sql语句赋值个体'delegate.boundSql.sql'，偷天换日
            MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
        }
//调用原对象的方法，进入责任链的下一级
        return invocation.proceed();
    }


    //获取代理对象
    @Override
    public Object plugin(Object o) {
        //生成object对象的动态代理对象
        return Plugin.wrap(o, this);
    }

    //设置代理对象的参数
    @Override
    public void setProperties(Properties properties) {
//如果项目中分页的pageSize是统一的，也可以在这里统一配置和获取，这样就不用每次请求都传递pageSize参数了。参数是在配置拦截器时配置的。
        String limit1 = properties.getProperty("limit", "10");
        this.pageSize = Integer.valueOf(limit1);
        this.dbType = properties.getProperty("dbType", "mysql");
    }
}

• 修改application.yaml

server:
  port: 9090
  servlet:
    context-path: /
spring:
  application:
    name: oil_1201
  mvc:
    static-path-pattern: /**
#  thymeleaf:
#    prefix: classpath:/templates/
#    check-template-location: true
#    cache: false
#    suffix: .html #模板后缀
#    encoding: UTF-8 #编码
#    mode: HTML #模板
    servlet:
      content-type: text/html

  web:
    resources:
      static-locations: classpath:/static/


  datasource:
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:mysql://192.168.224.123:3306/shiro2?useJDBCCompliantTimezoneShift=true&serverTimezone=UTC
    username: root
    password: root
  redis:
    host: 192.168.224.123
    port: 6379
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml
  type-aliases-package: com.examplae.shirorealms.**
  global-config:
    db-column-underline: true
    db-config:
      id-type: auto
  config-location: classpath:mybatisconfig.xml

• 创建mybaitconfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 核心配置文件 -->
<configuration>

    <settings>
        <setting name="logImpl" value="org.apache.ibatis.logging.stdout.StdOutImpl"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="cacheEnabled" value="false"/>
    </settings>

    <!--自定义分页插件-->
    <plugins>
        <plugin interceptor="com.example.shirorealms.interceptor.MyPageInterceptor">
<!--            <property name="limit" value="10"/>-->
<!--            <property name="dbType" value="mysql"/>-->
        </plugin>
    </plugins>

<!--    <plugins>-->
<!--        &lt;!&ndash;        配置分页插件&ndash;&gt;-->
<!--        <plugin interceptor="com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor">-->
<!--            <property name="@page" value="com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor"/>-->
<!--            <property name="page:dbType" value="MYSQL"/>-->
<!--        </plugin>-->
<!--    </plugins>-->

<!--    <plugins>-->
<!--        &lt;!&ndash;分页插件&ndash;&gt;-->
<!--        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>-->
<!--    </plugins>-->
</configuration>

• 编写Mapper.xml

    <!--查询数据分页-->
    <select id="findByPage" resultType="com.example.shirorealms.entity.TestDO">
        select * from test
    </select>

• 编写Dao

    public List<TestDO>findByPage(int page,int pageSize);

• 编写ServiceImpl

   @Override
    public List<TestDO> findByPage(int page, int pageSize) {
        return test1.findByPage(page,pageSize);
    }

3.4使用RowBounds分页

• 编写Dao

    // 使用RowBounds分页
    @Select("select * from test") 
    public List<TestDO>findRowBounds(RowBounds rowBounds);

• 编写ServiceImpl

    @Override
    @Transactional(isolation = Isolation.READ_COMMITTED,propagation = Propagation.SUPPORTS)
    public List<TestDO> findRowBounds(int start,int limit) {
        return test1.findRowBounds(new RowBounds(start,limit));
    }