Mybatis系列--08-分页实现
08-mybatis系列--分页实现
概述
本文主要讲述在使用mybatis中实现分页的两种方式,分别为
- 在sql中传入limit相关分页参数
- 使用RowBoungs实现
- 使用pageHelpher分页插件(本文不介绍,详细请看官网)
- 采用第一种方式是速度最快的,但是目前我们公司项目中有架构师是采用方法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'}