mybatis分页插件之分页原理

1. limit分⻚  126

mysql的limit后⾯两个数字：

第⼀个数字：startIndex（起始下标。下标从0开始。）

第⼆个数字：pageSize（每⻚显示的记录条数）

假设已知⻚码pageNum，还有每⻚显示的记录条数pageSize，第⼀个数字可以动态的获取吗？

startIndex = (pageNum - 1) * pageSize

所以，标准通⽤的mysql分⻚SQL：

select
 *
from
 tableName ......
limit
 (pageNum - 1) * pageSize, pageSize

1.1 使⽤mybatis应该怎么做？

<select id="selectByPage" resultType="Car">
        select * from t_car limit #{startIndex},#{pageSize}
    </select>

//分页查询  127
    @Test
    public void testSelectByPage(){
        // 获取每页显示的记录条数
        int pageSize = 3;
        // 显示第几页：页码
        int pageNum = 2;
        // 计算开始下标
        int startIndex = (pageNum - 1) * pageSize;

        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByPage(startIndex, pageSize);
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

获取数据不难，难的是获取分⻚相关的数据⽐较难。可以借助mybatis的PageHelper插件。

2. 使用PageHelper插件  128

使⽤PageHelper插件进⾏分⻚，更加的便捷。

2.1 第⼀步：引⼊依赖

<dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>5.3.1</version>
</dependency>

2.2 第⼆步：在mybatis-config.xml⽂件中配置插件

typeAliases标签下⾯进⾏配置：

<plugins>
     <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
</plugins>

2.3 第三步：编写Java代码  128-129

关键点：

在查询语句之前开启分⻚功能。

<select id="selectAll" resultType="Car">
        select * from t_car
    </select>

//实现分页  127
public class CarMapperTest {

    //使用PageHelper插件  128
    @Test
    public void testSelectAll(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);

        // 一定一定一定要注意：在执行DQL语句之前。开启分页功能。
        int pageNum = 2;
        int pageSize = 3;
        PageHelper.startPage(pageNum, pageSize);

        List<Car> cars = mapper.selectAll();
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

在查询语句之后封装PageInfo对象。（PageInfo对象将来会存储到request域当中。在⻚⾯上展示。）

//实现分页  127
public class CarMapperTest {

    //使用PageHelper插件  128
    @Test
    public void testSelectAll(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);

        // 一定一定一定要注意：在执行DQL语句之前。开启分页功能。
        int pageNum = 2;
        int pageSize = 3;
        PageHelper.startPage(pageNum, pageSize);

        List<Car> cars = mapper.selectAll();
        //cars.forEach(car -> System.out.println(car));

        // 封装分页信息对象new PageInfo()    129
        // PageInfo对象是PageHelper插件提供的，用来封装分页相关的信息的对象。
        //这里这个 5 是底部导航卡片数，就是不管你显示哪一页信息，底部导航卡片用永远展示5个卡片数
        PageInfo<Car> carPageInfo = new PageInfo<>(cars, 5);

        System.out.println(carPageInfo);

        sqlSession.close();

        /*
        //查出来的信息
        PageInfo{pageNum=2, pageSize=3, size=3, startRow=4, endRow=6, total=14, pages=5,
        list=Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=14, pages=5, reasonable=false, pageSizeZero=false}
        [Car{id=7, carNum='1003', brand='丰田霸道', guidePrice=30.0, produceTime='2000-10-11', carType='燃油车'},
        Car{id=31, carNum='1111', brand='比亚迪汉2', guidePrice=10.0, produceTime='2020-11-11', carType='电车'},
        Car{id=32, carNum='3333', brand='比亚迪秦', guidePrice=30.0, produceTime='2020-11-11', carType='新能源'}],
        prePage=1, nextPage=3, isFirstPage=false, isLastPage=false, hasPreviousPage=true, hasNextPage=true,
        navigatePages=5, navigateFirstPage=1, navigateLastPage=5, navigatepageNums=[1, 2, 3, 4, 5]}

         */
    }

对执⾏结果进⾏格式化：

//查出来的信息
        PageInfo{pageNum=2, pageSize=3, size=3, startRow=4, endRow=6, total=14, pages=5, 
        list=Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=14, pages=5, reasonable=false, pageSizeZero=false}
        [Car{id=7, carNum='1003', brand='丰田霸道', guidePrice=30.0, produceTime='2000-10-11', carType='燃油车'},
        Car{id=31, carNum='1111', brand='比亚迪汉2', guidePrice=10.0, produceTime='2020-11-11', carType='电车'}, 
        Car{id=32, carNum='3333', brand='比亚迪秦', guidePrice=30.0, produceTime='2020-11-11', carType='新能源'}],
        prePage=1, nextPage=3, isFirstPage=false, isLastPage=false, hasPreviousPage=true, hasNextPage=true, 
        navigatePages=5, navigateFirstPage=1, navigateLastPage=5, navigatepageNums=[1, 2, 3, 4, 5]}

3. 代码汇总   

main中com.powernode.mybatis.mapper

CarMapper

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CarMapper {
    /**
     * 使用PageHelper插件  128
     * 查询所有的Car，通过分页查询插件PageHelper完成。  128
     * @return
     */
    List<Car> selectAll();

    /**
     * 分页查询    127
     * @param startIndex 起始下标。
     * @param pageSize 每页显示的记录条数
     * @return
     */
    List<Car> selectByPage(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);
}

CarMapper.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.powernode.mybatis.mapper.CarMapper">

    <select id="selectAll" resultType="Car">
        select * from t_car
    </select>

    <select id="selectByPage" resultType="Car">
        select * from t_car limit #{startIndex},#{pageSize}
    </select>
</mapper>

test中com.powernode.mybatis.test

CarMapperTest

package com.powernode.mybatis.test;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.powernode.mybatis.mapper.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

//实现分页  127
public class CarMapperTest {

    //使用PageHelper插件  128
    @Test
    public void testSelectAll(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);

        // 一定一定一定要注意：在执行DQL语句之前。开启分页功能。
        int pageNum = 2;
        int pageSize = 3;
        PageHelper.startPage(pageNum, pageSize);

        List<Car> cars = mapper.selectAll();
        //cars.forEach(car -> System.out.println(car));

        // 封装分页信息对象new PageInfo()    129
        // PageInfo对象是PageHelper插件提供的，用来封装分页相关的信息的对象。
        //这里这个 5 是底部导航卡片数，就是不管你显示哪一页信息，底部导航卡片用永远展示5个卡片数
        PageInfo<Car> carPageInfo = new PageInfo<>(cars, 5);

        System.out.println(carPageInfo);

        sqlSession.close();

        /*
        //查出来的信息
        PageInfo{pageNum=2, pageSize=3, size=3, startRow=4, endRow=6, total=14, pages=5,
        list=Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=14, pages=5, reasonable=false, pageSizeZero=false}
        [Car{id=7, carNum='1003', brand='丰田霸道', guidePrice=30.0, produceTime='2000-10-11', carType='燃油车'},
        Car{id=31, carNum='1111', brand='比亚迪汉2', guidePrice=10.0, produceTime='2020-11-11', carType='电车'},
        Car{id=32, carNum='3333', brand='比亚迪秦', guidePrice=30.0, produceTime='2020-11-11', carType='新能源'}],
        prePage=1, nextPage=3, isFirstPage=false, isLastPage=false, hasPreviousPage=true, hasNextPage=true,
        navigatePages=5, navigateFirstPage=1, navigateLastPage=5, navigatepageNums=[1, 2, 3, 4, 5]}

         */
    }

    //分页查询  127
    @Test
    public void testSelectByPage(){
        // 获取每页显示的记录条数
        int pageSize = 3;
        // 显示第几页：页码
        int pageNum = 2;
        // 计算开始下标
        int startIndex = (pageNum - 1) * pageSize;

        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByPage(startIndex, pageSize);
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

}

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <properties resource="jdbc.properties"/>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="com.powernode.mybatis.pojo"/>
    </typeAliases>

    <!--mybatis分页的拦截器  128-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.powernode.mybatis.mapper"/>
    </mappers>
</configuration>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.powernode</groupId>
    <artifactId>course25</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

    <dependencies>
        <!--mybatis的插件pagehelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.3.1</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.10</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.11</version>
        </dependency>
    </dependencies>


    <properties>
        <!--    编译代码使用的jdk版本-->
        <maven.compiler.source>1.8</maven.compiler.source>
        <!--    运行程序使用的jdk版本-->
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>



</project>

剩余的

pojo

utils

logback.xml

jdbc.properties

不做赘述