Mybatis系列--08-分页实现

08-mybatis系列--分页实现

概述

本文主要讲述在使用mybatis中实现分页的两种方式，分别为

1. 在sql中传入limit相关分页参数
2. 使用RowBoungs实现
3. 使用pageHelpher分页插件（本文不介绍，详细请看官网）
4. 采用第一种方式是速度最快的，但是目前我们公司项目中有架构师是采用方法3

在sql中传入limit相关分页参数

Mybatis中默认仅支持一个参数的传递，如果其他参数想要传进来，要么使用map的形式，要么使用注解@Param,将参数映射进来，此处选择注解@Param方式

UserMapper.java文件

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.kuang.pojo.User;


/**
 * 功能描述
 *
 * @since 2022-07-13
 */
public interface UserMapper {
    List<User> getUserListLimit(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);
}

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.kuang.dao.UserMapper">
    <resultMap id="userMap" type="com.kuang.pojo.User">
        <result column="pwd" property="password"></result>
    </resultMap>

    <select id="getUserListLimit" resultMap="userMap">
        select *
        from user limit #{startIndex},#{pageSize}
    </select>
</mapper>

测试类

package dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.kuang.dao.UserMapper;
import com.kuang.pojo.User;
import com.kuang.util.MyBatisUtil;

/**
 * 功能描述
 *
 * @since 2022-07-13
 */
public class UserMapperTest {

    @Test
    public void getListLimit() {
        SqlSession sqlsession = MyBatisUtil.getSqlsession();
        UserMapper mapper = sqlsession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserListLimit(1, 3);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlsession.close();
    }
}

运行结果

com.intellij.rt.junit.JUnitStarter -ideVersion5 -junit4 dao.UserMapperTest,getListLimit
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - Class not found: org.jboss.vfs.VFS
DEBUG [main] - JBoss 6 VFS API is not available in this environment.
DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
...
DEBUG [main] - Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
DEBUG [main] - Created connection 1231156911.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4961f6af]
DEBUG [main] - ==>  Preparing: select * from user limit ?,?
DEBUG [main] - ==> Parameters: 1(Integer), 3(Integer)
TRACE [main] - <==    Columns: id, name, pwd
TRACE [main] - <==        Row: 2, 张三, 111
TRACE [main] - <==        Row: 3, 李四, 222
TRACE [main] - <==        Row: 4, haha, 123456
DEBUG [main] - <==      Total: 3
User{id=2, name='张三', password='111'}
User{id=3, name='李四', password='222'}
User{id=4, name='haha', password='123456'}
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4961f6af]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4961f6af]
DEBUG [main] - Returned connection 1231156911 to pool.

采用RowBounds（官方不推荐）

RowBounds的方式官方不推荐了，但是pageHelper插件是基于这个搞的，所以可以了解一下，pageHelper的使用本文不涉及，请查阅官方文档

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.kuang.dao.UserMapper">
    <resultMap id="userMap" type="com.kuang.pojo.User">
        <result column="pwd" property="password"></result>
    </resultMap>

    <select id="getUserListByRowBounds" resultMap="userMap">
        select *
        from user
    </select>
</mapper>

测试类

package dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.kuang.dao.UserMapper;
import com.kuang.pojo.User;
import com.kuang.util.MyBatisUtil;

import org.apache.ibatis.session.RowBounds;

/**
 * 功能描述
 *
 * @since 2022-07-13
 */
public class UserMapperTest {

    public void getUserListByRowBounds() {
        SqlSession sqlsession = MyBatisUtil.getSqlsession();
       RowBounds rowBounds = new RowBounds(1, 3);

       // 通过java代码层面实现分页
       List<User> list = sqlsession.selectList("com.kuang.dao.UserMapper.getUserListByRowBounds", null, rowBounds);

        for (User user : list) {
            System.out.println(user);
        }
    }
}

结果

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - Class not found: org.jboss.vfs.VFS
DEBUG [main] - JBoss 6 VFS API is not available in this environment.
DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
...
DEBUG [main] - Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
DEBUG [main] - Created connection 587003819.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@22fcf7ab]
DEBUG [main] - ==>  Preparing: select * from user
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: id, name, pwd
TRACE [main] - <==        Row: 1, 狂神, 123456
TRACE [main] - <==        Row: 2, 张三, 111
TRACE [main] - <==        Row: 3, 李四, 222
TRACE [main] - <==        Row: 4, haha, 123456
User{id=2, name='张三', password='111'}
User{id=3, name='李四', password='222'}
User{id=4, name='haha', password='123456'}