从入门到精通的使用Mybatis框架（七）——Mybatis实现分页效果

在学习mybatis等持久层框架的时候，会经常对数据进行增删改查操作，使用最多的是对数据库进行查询操作，如果查询大量数据的时候，我们往往使用分页进行查询，也就是每次处理小部分数据，这样对数据库压力就在可控范围内。

使用limit进行分页

#语法
SELECT * FROM table LIMIT stratIndex，pageSize
SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15   
#为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：    
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.   
#如果只给定一个参数，它表示返回最大的记录行数目：    
SELECT * FROM table LIMIT 5; //检索前 5 个记录行   
#换句话说，LIMIT n 等价于 LIMIT 0,n。

修改Mapper文件

<select id="selectUser" parameterType="map" resultType="user">
    select * from user limit #{startIndex},#{pageSize}
</select>

修改Mapper接口

//选择全部用户实现分页
List<User> selectUser(Map<String,Integer> map);

测试类中进行测试

//分页查询 , 两个参数startIndex , pageSize
@Test
public void testSelectUser() {
    SqlSession session = MybatisUtils.getSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    int currentPage = 1;  //第几页
    int pageSize = 2;  //每页显示几个
    Map<String,Integer> map = new HashMap<String,Integer>();
    map.put("startIndex",(currentPage-1)*pageSize);
    map.put("pageSize",pageSize);
    List<User> users = mapper.selectUser(map);
    for (User user: users){
        System.out.println(user);
    }
    session.close();
}

测试效果

E:\JAVA\JDK16\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2021.2\lib\idea_rt.jar=9865:C:\Program Files\JetBrains\IntelliJ IDEA 2021.2\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\JetBrains\IntelliJ IDEA 2021.2\lib\idea_rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2021.2\plugins\junit\lib\junit5-rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2021.2\plugins\junit\lib\junit-rt.jar;E:\idea-workspace\Mybatis\mybatis-03\target\test-classes;E:\idea-workspace\Mybatis\mybatis-03\target\classes;D:\server\maven\repository\org\mybatis\mybatis\3.5.2\mybatis-3.5.2.jar;D:\server\maven\repository\mysql\mysql-connector-java\8.0.26\mysql-connector-java-8.0.26.jar;D:\server\maven\repository\com\google\protobuf\protobuf-java\3.11.4\protobuf-java-3.11.4.jar;D:\server\maven\repository\junit\junit\4.12\junit-4.12.jar;D:\server\maven\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;D:\server\maven\repository\log4j\log4j\1.2.17\log4j-1.2.17.jar" com.intellij.rt.junit.JUnitStarter -ideVersion5 -junit4 mybatisTest,selectUser
[org.apache.ibatis.logging.LogFactory]-Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
[org.apache.ibatis.logging.LogFactory]-Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
[org.apache.ibatis.datasource.pooled.PooledDataSource]-PooledDataSource forcefully closed/removed all connections.
[org.apache.ibatis.datasource.pooled.PooledDataSource]-PooledDataSource forcefully closed/removed all connections.
[org.apache.ibatis.datasource.pooled.PooledDataSource]-PooledDataSource forcefully closed/removed all connections.
[org.apache.ibatis.datasource.pooled.PooledDataSource]-PooledDataSource forcefully closed/removed all connections.
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Opening JDBC Connection
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 2048013503.
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7a1234bf]
[com.PAG.mapper.userMapper.selectUser]-==>  Preparing: select * from user limit ?,? 
[com.PAG.mapper.userMapper.selectUser]-==> Parameters: 0(Integer), 2(Integer)
[com.PAG.mapper.userMapper.selectUser]-<==      Total: 2
User{id=1, name='pag', pwd='123456'}
User{id=2, name='fff', pwd='123456'}
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7a1234bf]
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7a1234bf]
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 2048013503 to pool.

Process finished with exit code 0

RowBounds分页

mapper接口

//选择全部用户RowBounds实现分页
List<User> getUserByRowBounds();

mapper文件

<select id="getUserByRowBounds" resultType="user">
  select * from user
</select>

测试类

@Test
public void testUserByRowBounds() {
   SqlSession session = MybatisUtils.getSession();
   int currentPage = 2;  //第几页
   int pageSize = 2;  //每页显示几个
   RowBounds rowBounds = new RowBounds((currentPage-1)*pageSize,pageSize);
   //通过session.**方法进行传递rowBounds，[此种方式现在已经不推荐使用了]
   List<User> users = session.selectList("com.kuang.mapper.UserMapper.getUserByRowBounds", null, rowBounds);
   for (User user: users){
       System.out.println(user);
   }
   session.close();
}

PageHelper 了解即可

官方文档：https://pagehelper.github.io/