(4)分页操作

分页一般来说使用得比较多，下面就介绍分页插件的使用。

可以先参考github的wike的文章：
https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md

文章详细介绍的分页插件的使用，这里我简单写了一个测试的例子

mybatis自带的分页实际上分页逻辑分页，实在内存中进行分页的，但这种方式是不可取的,分页插件实现了物理分页

1.环境准备

编写实体类(set和get方法省略)

public class User {
    private Integer id;

    private String username;

    private Date birthday;

    private String sex;

    private String address;

编写User的mapper的接口

public interface UserMapper {

    List<User> selectAll();
}

编写UserMapper.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.mybatis.project.mapper.UserMapper">
    <select id="selectAll" resultMap="BaseResultMap">
    select  id, username, birthday, sex, address from User
  </select>
</mapper>

更多信息

参考github的项目mybatis下的mybatis-generator工程下的com.mybatis.bestPractice.paging

地址为： https://github.com/sunkang123/mybatis

2.逻辑分页

逻辑分页的测试方法

public class UserPagingTest {

    private SqlSessionFactory sqlSessionFactory ;
    @Before
    public void setUp() throws Exception {
        //创建sqlsessionFactory
        String resource = "SqlMapConfig.xml";
        //得到配置文件流
        InputStream inputStream = Resources.getResourceAsStream(resource);

        //创建会话工厂，传入mybatis的配置文件的信息
        sqlSessionFactory  = new SqlSessionFactoryBuilder().build(inputStream);
    }

    //逻辑分页，内存的分页
    @Test
    public void testSqlession() {
        SqlSession sqlSession  =sqlSessionFactory.openSession();
       List<User> userList =   sqlSession.selectList("selectAll",null,new RowBounds(2,4));
        for(User user: userList){
            System.out.println(user);
        }
    }
}

测试结果：可以发现输出的sql语句为select id, username, birthday, sex, address from User ，根本就没有用到sql语句分页

DEBUG [main] - ==>  Preparing: select id, username, birthday, sex, address from User 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 5
User{id=3, username='wangzhezhi', birthday=Sun Oct 07 00:00:00 CST 2018, sex='3', address='温州:update:update:update:update', orderList=null}
User{id=9, username='wangzhezhi', birthday=null, sex='null', address='hangzhoubingjiang:update:update:update:update', orderList=null}
User{id=10, username='sunkang', birthday=null, sex='1', address='hangzhou', orderList=null}

源码追踪

在源码中
org.apache.ibatis.executor.resultset.DefaultResultSetHandler#handleRowValuesForSimpleResultMap方法中的skipRows方法发现发现
ResultSet 的类型不是1003，这个代表是ResultSet .TYPE_FORWARD_ONLY，无法滚动结果集;它的光标仅向前移动，从第一行之前移动到最后一行之后,如果结果集类型为TYPE_FORWARD_ONLY就直接跳过到指定的游标的节点，否则说明结果集类型是可以滚动的，就调用absolute的方法，这个是原生的jdbc的操作方法

可以参考我的文章： https://www.jianshu.com/p/c5d7da273c8d

   private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping) throws SQLException {
        DefaultResultContext<Object> resultContext = new DefaultResultContext();
        this.skipRows(rsw.getResultSet(), rowBounds);

        while(this.shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
            ResultMap discriminatedResultMap = this.resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, (String)null);
            Object rowValue = this.getRowValue(rsw, discriminatedResultMap);
            this.storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
        }

    }
private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
        if (rs.getType() != 1003) {
            if (rowBounds.getOffset() != 0) {
                rs.absolute(rowBounds.getOffset());
            }
        } else {
            for(int i = 0; i < rowBounds.getOffset(); ++i) {
                rs.next();
            }
        }

    }

3.分页插件（实现物理分页）

加上相关的jar的依赖

   <dependency>
          <groupId>com.github.pagehelper</groupId>
          <artifactId>pagehelper</artifactId>
          <version>5.0.3</version>
      </dependency>

在mybatis的配置文件加上分页插件的配置，指定数据方言为mysql

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

编写测试类，验证插件的分页

/**
 * @Project: mybatis
 * @description:  分页插件演示
 * @author: sunkang
 * @create: 2018-10-28 16:13
 * @ModificationHistory who      when       What
 **/
public class UserPagingTest {

    private SqlSessionFactory sqlSessionFactory ;
    @Before
    public void setUp() throws Exception {
        //创建sqlsessionFactory
        String resource = "SqlMapConfig.xml";
        //得到配置文件流
        InputStream inputStream = Resources.getResourceAsStream(resource);

        //创建会话工厂，传入mybatis的配置文件的信息
        sqlSessionFactory  = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testPaging() throws Exception {
        SqlSession sqlSession  =sqlSessionFactory.openSession();
        //创建userMapper对象,mybatis自动生成mapper代理对象
        final UserMapper userMapper =  sqlSession.getMapper(UserMapper.class);
        //方式一：use static method startPage
        PageHelper.startPage(1,2);
        //查询会进行分页
        List<User> userList  = userMapper.selectAll();
       // System.out.println(userList.get(0));

        //方式二： ISelect interface
        //startPage的pageNum为2，pageSize为2，说明从2页面开始查，也就是从第三条数据开始查
        PageInfo<User> page =PageHelper.startPage(2, 2).doSelectPageInfo(new ISelect() {
            public void doSelect() {
                userMapper.selectAll();
            }
        });
        System.out.println("total: "+ page.getTotal());
        System.out.println("pagesize:"+page.getPageSize());
        System.out.println("pageNum:"+page.getPageNum());
        for(User user: page.getList()){
            System.out.println(user);
        }
    }
}

输出结果为(已经实现了分页):

pagesize:2
pageNum:2
User{id=3, username='wangzhezhi', birthday=Sun Oct 07 00:00:00 CST 2018, sex='3', address='温州:update:update:update:update', orderList=null}
User{id=9, username='wangzhezhi', birthday=null, sex='null', address='hangzhoubingjiang:update:update:update:update', orderList=null}

(4)分页操作

1.环境准备

2.逻辑分页

3.分页插件 （实现物理分页）

3.分页插件（实现物理分页）