MyBatis 分页插件pagehelper使用

 引入分页插件：

  <!-- 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.6</version>
        </dependency>

在mybatis.xml主配置中配置插件

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- config params as the following -->
            <property name="helperDialect" value="mysql" />
        </plugin>
    </plugins>

  如果是spring集成mybatis 在spring配置文件中配置插件：

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- other configuration -->
        <property name="plugins">
        <array>
             <bean class="com.github.pagehelper.PageInterceptor">
                     <property name="properties">
                    <!-- config params as the following -->
                         <value> helperDialect=mysql</value>
                     </property>
             </bean>
        <array>
    <bean>

在实际项目中应用插件：

 @Test
    void findPersonByNameByLike() {
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);

        //pageNum 显示第几页 pageSize 每页显示数量
        Page<Person> page = PageHelper.startPage(2,2).doSelectPage(
                ()->personMapper.findPersonByNameByLike("张"));
        List<Person> personList = page.getResult();
        System.out.println(page.getTotal());
        System.out.println(page.getPageSize());
        personList.forEach(person -> {
            System.out.println(person);
        });
    }

查看下它的sql执行，后面会讲解分页插件原理，已经拦截器等：

2021-02-04 13:15:11 [ main:1134 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@223aa2f7]
2021-02-04 13:15:11 [ main:1142 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT count(0) FROM t_person p, t_idcard idcard WHERE p.card_id = idcard.id AND p.name LIKE '%张%' 
2021-02-04 13:15:11 [ main:1188 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 
2021-02-04 13:15:11 [ main:1214 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <==      Total: 1
2021-02-04 13:15:11 [ main:1219 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like '%张%' LIMIT ?, ? 
2021-02-04 13:15:11 [ main:1220 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 2(Integer), 2(Integer)
2021-02-04 13:15:11 [ main:1225 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <==      Total: 2

Mybatis采用的是逻辑分页，而非物理分页 要实现物理分页，就需要对String sql进行拦截并增强，Mybatis通过BoundSql对象存储String sql，而BoundSql则由StatementHandler对象获取。

public interface StatementHandler {
    <E> List<E> query(Statement statement, ResultHandler resultHandler)
    throws SQLException{
        String sql = getBoundSql()；
        分页语句： sql+"limit 语句"
        查询总数语句："SELECT COUNT(1) "" +sql.substring(from语句之后)
    };
    BoundSql getBoundSql();
    }
    public class BoundSql {
        public String getSql() {
        return sql;
    }
}