分页

1、为什么需要分页

数据量太大。分页展示，减少数据的处理量。

2、使用limit分页

select * from user limit startIndex,pageSize

从第startIndex+1条开始查，每页显示pageSize条数据。

select * from user limit pageSize
等价于
select * from user limit 0,pageSize

从第一条开始查，每页显示pageSize条数据。

例子：

select * from user limit 0,2

结果

id	name  pwd
1	狂神	123456
2	法外狂徒张三	333333

3、使用Mybatis实现分页

UserMapper.java接口中新增方法

/**
 * 分页查询
 * @param map
 * @return
 */
List<User> getUserByLimit(Map<String, Object> map);

修改mapper文件UserMapper.xml

<!--分页查询-->
<resultMap id="UserMapLimit" type="user">
    <result column="pwd" property="password"/>
</resultMap>
<select id="getUserByLimit" resultMap="UserMapLimit" parameterType="map">
    select * from user limit #{startIndex},#{pageSize}
</select>

测试

/**
 * 分页查询
 */
@Test
public void testLimit(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    HashMap<String, Object> map = new HashMap<String, Object>();
    map.put("startIndex",0);
    map.put("pageSize",2);

    List<User> userList = userMapper.getUserByLimit(map);
    for (User user : userList) {
        System.out.println(user);
    }

    sqlSession.close();
}

结果

[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.io.VFS]-Class not found: org.jboss.vfs.VFS
[org.apache.ibatis.io.JBoss6VFS]-JBoss 6 VFS API is not available in this environment.
[org.apache.ibatis.io.VFS]-Class not found: org.jboss.vfs.VirtualFile
[org.apache.ibatis.io.VFS]-VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
[org.apache.ibatis.io.VFS]-Using VFS adapter org.apache.ibatis.io.DefaultVFS
[org.apache.ibatis.io.DefaultVFS]-Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-03/target/classes/com/kuang/pojo
[org.apache.ibatis.io.DefaultVFS]-Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-03/target/classes/com/kuang/pojo
[org.apache.ibatis.io.DefaultVFS]-Reader entry: User.class
[org.apache.ibatis.io.DefaultVFS]-Listing file:/E:/code/Java/mybatis-study/mybatis-03/target/classes/com/kuang/pojo
[org.apache.ibatis.io.DefaultVFS]-Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-03/target/classes/com/kuang/pojo/User.class
[org.apache.ibatis.io.DefaultVFS]-Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-03/target/classes/com/kuang/pojo/User.class
[org.apache.ibatis.io.DefaultVFS]-Reader entry: ����   1 <
[org.apache.ibatis.io.ResolverUtil]-Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
[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 1019298652.
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3cc1435c]
[com.kuang.dao.UserMapper.getUserByLimit]-==>  Preparing: select * from user limit ?,? 
[com.kuang.dao.UserMapper.getUserByLimit]-==> Parameters: 0(Integer), 2(Integer)
[com.kuang.dao.UserMapper.getUserByLimit]-<==      Total: 2
User{id=1, name='狂神', password='123456'}
User{id=2, name='法外狂徒张三', password='333333'}
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3cc1435c]
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3cc1435c]
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 1019298652 to pool.

Process finished with exit code 0

4、RowBounds分页

不再使用sql实现分页。

UserMapper.java接口中新增方法

/**
 * RowBounds分页
 * @return
 */
List<User> getUserByRowBounds();

修改mapper文件UserMapper.xml

<!--RowBounds分页-->
<resultMap id="UserMapRowBounds" type="user">
    <result column="pwd" property="password"/>
</resultMap>
<select id="getUserByRowBounds" resultMap="UserMapRowBounds">
    select * from user
</select>

测试

源码

/**
   * Retrieve a list of mapped objects from the statement key and parameter,
   * within the specified row bounds.
   * @param <E> the returned list element type
   * @param statement Unique identifier matching the statement to use.
   * @param parameter A parameter object to pass to the statement.
   * @param rowBounds  Bounds to limit object retrieval
   * @return List of mapped object
   */
  <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds);

测试代码

/**
 * RowBounds分页查询
 */
@Test
public void testRowBounds(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    /**
         * 通过Java代码层面实现分页
         */
    RowBounds rowBounds = new RowBounds(0, 2);
    List<User> userList = sqlSession.selectList("com.kuang.dao.UserMapper.getUserByRowBounds",null,rowBounds);

    userList.forEach(System.out::println);

    sqlSession.close();

}

结果正常。

5、分页插件

MyBatis分页插件PageHelper：https://pagehelper.github.io/

如果你也在用 MyBatis，建议尝试该分页插件，这一定是最方便使用的分页插件。分页插件支持任何复杂的单表、多表分页。