MyBatis 分页插件PageHelper使用

PageHelper

1、概念

MyBatis 分页插件 PageHelper

Maven依赖

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

2、简单示例

① Maven坐标

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.49</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.6</version>
    </dependency>
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>5.2.0</version>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.75</version>
    </dependency>
</dependencies>

② 示例SQL

CREATE TABLE user (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  name VARCHAR(32) DEFAULT NULL COMMENT '姓名',
  age INT(11) DEFAULT NULL COMMENT '年龄',
  time DATETIME DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '用户表'

INSERT INTO user (name, age, time)
VALUES ('张三', 14, NOW()),
('李四', 12, NOW()),
('王五', 10, NOW()),
('赵六', 16, NOW()),
('田七', 11, NOW()),
('ze', 13, NOW()),
('l4', 13, NOW()),
('w5', 20, NOW()),
('z6', 19, NOW()),
('t7', 18, NOW()),
('anna', 16, NOW()),
('bill', 23, NOW()),
('cat', 14, NOW()),
('doge', 17, NOW()),
('emma', 11, NOW()),
('fire', 17, NOW())

③ jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8
username=
password=

④ log4j.properties

log4j.rootLogger=DEBUG,console
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%m%n
#SQL日志级别
log4j.logger.org.apache.ibatis=INFO

⑤ 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" />

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

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="UserMapper.xml" />
    </mappers>
</configuration>

⑥ 代码

import java.io.Serializable;
import java.util.List;

public class Page<T> implements Serializable {

    private static final long serialVersionUID = 1L;

    private int currentPage;

    private int pageSize;

    private int totalRow;

    private int totalPage;

    private List<T> data;

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalRow() {
        return totalRow;
    }

    public void setTotalRow(int totalRow) {
        this.totalRow = totalRow;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }
}

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;

    private String name;

    private Integer age;

    private Date time;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }
}

import java.util.List;

public interface UserDao {

    List<User> selectListByCondition(User user);

}

import com.github.pagehelper.PageInfo;

public class PageUtil {

    public static <T> Page<T> convertPage(PageInfo<T> pageInfo) {
        Page<T> page = new Page<T>();
        if (null == pageInfo) {
            return page;
        }

        page.setCurrentPage(pageInfo.getPageNum()); // 当前页
        page.setPageSize(pageInfo.getPageSize()); // 每页数据量
        page.setTotalRow((int)pageInfo.getTotal()); // 总数据量
        page.setTotalPage(pageInfo.getPages()); // 总页码
        page.setData(pageInfo.getList()); // 当前页数据集
        return page;
    }

}

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
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 java.io.IOException;
import java.io.InputStream;

public class Test {

    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            sqlSession = getSqlSession();
            exec(sqlSession);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != sqlSession) {
                sqlSession.commit();
                sqlSession.close();
            }
        }
    }

    private static void exec(SqlSession sqlSession) {
        UserDao dao = sqlSession.getMapper(UserDao.class);

        Page<User> page = new Page<User>();
        page.setCurrentPage(1);
        page.setPageSize(5);

        User condition = new User();
        condition.setAge(14);

        page = selectPageByCondition(dao, page, condition);
        System.out.println("当前页：" + page.getCurrentPage());
        System.out.println("每页数据量：" + page.getPageSize());
        System.out.println("总数据量：" + page.getTotalRow());
        System.out.println("总页码：" + page.getTotalPage());
        System.out.println("当前页数据集：\n" + JSON.toJSONString(page.getData(),
                SerializerFeature.UseISO8601DateFormat, SerializerFeature.PrettyFormat));
    }

    private static Page<User> selectPageByCondition(UserDao dao, Page<User> page, User condition) {
        PageInfo<User> pageInfo = PageHelper.startPage(page.getCurrentPage(), page.getPageSize()).doSelectPageInfo(() -> {
            dao.selectListByCondition(condition);
        });
        return PageUtil.convertPage(pageInfo);
    }

    private static SqlSession getSqlSession() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        return sqlSessionFactory.openSession();
    }

}

⑦ 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="???.UserDao">
    <resultMap id="ResultMap" type="???.User">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
        <result column="time" jdbcType="TIMESTAMP" property="time" />
    </resultMap>
    <sql id="Column_List">
        id, name, age, time
    </sql>
    <select id="selectListByCondition" parameterType="???.User" resultMap="ResultMap">
        SELECT <include refid="Column_List" /> FROM user
        <where>
            <if test="null != id">
                AND id = #{id,jdbcType=BIGINT}
            </if>
            <if test="null != name and '' != name">
                AND name = #{name,jdbcType=VARCHAR}
            </if>
            <if test="null != age">
                AND age = #{age,jdbcType=INTEGER}
            </if>
        </where>
    </select>
</mapper>

结果日志：

Cache Hit Ratio [SQL_CACHE]: 0.0
==>  Preparing: SELECT count(0) FROM user WHERE age = ?
==> Parameters: 14(Integer)
<==      Total: 1
==>  Preparing: SELECT id, name, age, time FROM user WHERE age = ? LIMIT ?
==> Parameters: 14(Integer), 5(Integer)
<==      Total: 2


当前页：1
每页数据量：5
总数据量：2
总页码：1
当前页数据集：
[
	{
		"age":14,
		"id":1,
		"name":"张三",
		"time":"1970-01-01T08:00:00+08:00"
	},
	{
		"age":14,
		"id":13,
		"name":"cat",
		"time":"1970-01-01T08:00:00+08:00"
	}
]

本文地址：https://blog.****.net/adsl624153/article/details/112630667