十六、MyBatis使用PageHelper
16.1 limit分页
mysql的limit后面两个数字:
- 第一个数字:
startIndex
(起始下标。下标从0开始。) - 第二个数字:
pageSize
(每页显示的记录条数)
假设已知页码pageNum
,还有每页显示的记录条数pageSize
,第一个数字可以动态的获取吗?
startIndex = (pageNum - 1) * pageSize
所以,标准通用的mysql分页SQL:
select
*
from
tableName ......
limit
(pageNum - 1) * pageSize, pageSize
使用mybatis应该怎么做?
模块名:mybatis-012-page
package com.study.mybatis.mapper;
import com.study.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
/**
* 通过分页的方式获取Car列表
* @param startIndex 页码
* @param pageSize 每页显示记录条数
* @return
*/
List<Car> selectAllByPage(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize);
}
<?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.study.mybatis.mapper.CarMapper">
<select id="selectAllByPage" resultType="Car">
select * from t_car limit #{startIndex},#{pageSize}
</select>
</mapper>
package com.study.mybatis.test;
import com.study.mybatis.mapper.CarMapper;
import com.study.mybatis.pojo.Car;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.util.List;
public class PageTest {
@Test
public void testPage()throws Exception{
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = sqlSessionFactory.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
// 页码
Integer pageNum = 2;
// 每页显示记录条数
Integer pageSize = 3;
// 起始下标
Integer startIndex = (pageNum - 1) * pageSize;
List<Car> cars = mapper.selectAllByPage(startIndex, pageSize);
cars.forEach(car -> System.out.println(car));
sqlSession.commit();
sqlSession.close();
}
}
执行结果:
获取数据不难,难的是获取分页相关的数据比较难。可以借助mybatis的PageHelper
插件。
16.2 PageHelper插件
使用PageHelper插件进行分页,更加的便捷。
第一步:引入依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.3.1</version>
</dependency>
第二步:在mybatis-config.xml文件中配置插件
typeAliases
标签下面进行配置:
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>
第三步:编写Java代码
List<Car> selectAll();
<select id="selectAll" resultType="Car">
select * from t_car
</select>
关键点:
- 在查询语句之前开启分页功能。
- 在查询语句之后封装PageInfo对象。(PageInfo对象将来会存储到request域当中。在页面上展示。)
@Test
public void testPageHelper() throws Exception{
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = sqlSessionFactory.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
// 开启分页
PageHelper.startPage(2, 2);
// 执行查询语句
List<Car> cars = mapper.selectAll();
// 获取分页信息对象
PageInfo<Car> pageInfo = new PageInfo<>(cars, 5);
System.out.println(pageInfo);
}
执行结果:
PageInfo{pageNum=2, pageSize=2, size=2, startRow=3, endRow=4, total=6, pages=3, list=Page{count=true, pageNum=2, pageSize=2, startRow=2, endRow=4, total=6, pages=3, reasonable=false, pageSizeZero=false}[Car{id=86, carNum='1234', brand='丰田霸道', guidePrice=50.5, produceTime='2020-10-11', carType='燃油车'}, Car{id=87, carNum='1234', brand='丰田霸道', guidePrice=50.5, produceTime='2020-10-11', carType='燃油车'}], prePage=1, nextPage=3, isFirstPage=false, isLastPage=false, hasPreviousPage=true, hasNextPage=true, navigatePages=5, navigateFirstPage=1, navigateLastPage=3, navigatepageNums=[1, 2, 3]}
对执行结果进行格式化:
PageInfo{
pageNum=2, pageSize=2, size=2, startRow=3, endRow=4, total=6, pages=3,
list=Page{count=true, pageNum=2, pageSize=2, startRow=2, endRow=4, total=6, pages=3, reasonable=false, pageSizeZero=false}
[Car{id=86, carNum='1234', brand='丰田霸道', guidePrice=50.5, produceTime='2020-10-11', carType='燃油车'},
Car{id=87, carNum='1234', brand='丰田霸道', guidePrice=50.5, produceTime='2020-10-11', carType='燃油车'}],
prePage=1, nextPage=3, isFirstPage=false, isLastPage=false, hasPreviousPage=true, hasNextPage=true,
navigatePages=5, navigateFirstPage=1, navigateLastPage=3, navigatepageNums=[1, 2, 3]
}
tPage=3, isFirstPage=false, isLastPage=false, hasPreviousPage=true, hasNextPage=true,
navigatePages=5, navigateFirstPage=1, navigateLastPage=3, navigatepageNums=[1, 2, 3]
}