MyBatis如何实现分页查询呢?

Mybatis分页查询的实现思路

使用计算出offset pagesize
    并且将这些参数传入到mapper.xml中
使mapper中的xml实现分页查询

//创建数据库表

在MySQL数据库中创建用户信息表(tb_user)并添加数据
-- 创建“用户信息”数据表
CREATE TABLE IF NOT EXISTS tb_user
( 
	user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
	user_name VARCHAR(50) NOT NULL COMMENT '用户姓名',
	province VARCHAR(50) NOT NULL COMMENT '省份',
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) COMMENT = '用户信息表';
 
-- 添加数据
INSERT INTO tb_user(user_name,province) VALUES
('1','广东'),('2','湖北'),('3','四川'),
('4','台湾'),('5','陕西')


//编写实体类（Entity层）

//创建公共分页类（BasePaging.java）。

package com.java265.sm.entity;
/**
 * 公共分页类
 * 
 **/
public class BasePaging
{
    private int pageIndex; //当前页码
    private int pageSize; //分页大小
    private int totalData; //数据总数
    private int totalPage; //总页数
    private String orderBy; //排序
    private int offset; //偏移量
 
    public int getPageIndex()
    {
        return pageIndex;
    }
 
    public void setPageIndex(int pageIndex)
    {
        this.pageIndex = pageIndex;
    }
 
    public int getPageSize()
    {
        return pageSize;
    }
 
    public void setPageSize(int pageSize)
    {
        this.pageSize = pageSize;
    }
 
    public int getTotalData()
    {
        return totalData;
    }
 
    public void setTotalData(int totalData)
    {
        this.totalData = totalData;
    }
 
    public String getOrderBy()
    {
        return orderBy;
    }
 
    public void setOrderBy(String orderBy)
    {
        this.orderBy = orderBy;
    }
 
    //计算偏移量：偏移量 = (page_index-1)*page_size
    public int getOffset()
    {
        int offset = 0;
        if(this.pageIndex>0 && this.pageSize>0)
        {
            offset = (this.pageIndex-1)*this.pageSize;
        }
        return offset;
    }
 
    //计算总页数：总页数 = (数据总数 + 分页大小 -1) / 分页大小
    public int getTotalPage()
    {
        int totalPage = 0;
        if (this.totalData > 0 && this.pageSize > 0)
        {
            totalPage = (this.totalData + this.pageSize - 1) / this.pageSize;
        }
        return totalPage;
    }
}

//创建分页结果类(PageResult.java)
//并继承公共分页类(BasePaging.java)
package com.java265.sm.entity;
import java.util.list;
 
/**
 * 分页结果类
 * 
 **/
public class PageResult<T> extends BasePaging
{
    private List<T> dataList; //数据列表
 
    public List<T> getDataList()
    {
        return dataList;
    }
 
    public void setDataList(List<T> dataList)
    {
        this.dataList = dataList;
    }
}

//创建用户信息的持久化类User.java

package com.java265.sm.entity;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
 * 用户信息的持久化类
 * 
 **/
@Component
public class User
{
    private int userId; //用户编号
    private String userName; //用户姓名
    private String province; //省份
    private Date createTime; //注册时间
 
    //省略getter与setter方法...
}

//创建用户查询条件类(UserSearchParam.java)，并继承公共分页类（BasePaging.java）。

package com.java265.sm.entity;
 
/**
 * 用户查询条件类
 * 
 **/
public class UserSearchParam extends BasePaging
{
    private String userName; //用户姓名
    private String province; //省份
 
    //省略getter与setter方法...
}


//编写Mapper配置文件
//创建用户信息Mapper配置文件(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.java265.sm.dao.UserDao">
 
    <!-- 用户信息ResultMap -->
    <resultMap id="userResultMap" type="com.java265.sm.entity.User">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name"/>
        <result property="province" column="province"/>
        <result property="createTime" column="create_time"/>
    </resultMap>
 
    <!--用户查询条件SQL片段-->
    <sql id="query_user_where">
        <if test="userName!=null and userName!=''">
            AND user_name like '%${userName}%'
        </if>
        <if test="province!=null and province!=''">
            AND province = #{province}
        </if>
    </sql>
 
    <!-- 分页查询用户列表 -->
    <select id="getUserPaging" parameterType="com.java265.sm.entity.UserSearchParam" resultMap="userResultMap">
        SELECT * FROM tb_user
        <where>
            <include refid="query_user_where"/>
        </where>
        <if test="orderBy!=null and orderBy!=''">
            Order BY ${orderBy}
        </if>
        LIMIT ${offset},${pageSize}
    </select>
 
    <!-- 统计用户数量 -->
    <select id="getCountUser" parameterType="com.java265.sm.entity.UserSearchParam" resultType="int">
        SELECT COUNT(1) FROM tb_user
        <where>
            <include refid="query_user_where"/>
        </where>
    </select>
</mapper>


//实现数据库访问接口层(Dao层)
//创建用户信息数据库访问接口(UserDao.java)

package com.java265.sm.dao;
import com.java265.sm.entity.User;
import com.java265.sm.entity.UserSearchParam;
 
import java.util.List;
 
/**
 * 用户信息数据库访问接口
 * Mapper动态代理接口
 * 
 **/
public interface UserDao
{
    /**
     * 分页查询用户列表
     */
    public List<User> getUserPaging(UserSearchParam param);
 
    /**
     * 统计用户数量
     */
    public int getCountUser(UserSearchParam param);
}


//实现业务逻辑层(Service层)
//创建用户信息业务逻辑接口(UserService.java)

package com.java265.sm.service;
 
import com.java265.sm.entity.PageResult;
import com.java265.sm.entity.User;
import com.java265.sm.entity.UserSearchParam;
 
/**
 * 用户信息业务逻辑接口
 * 
 **/
public interface UserService
{
    /**
     * 分页查询用户列表
     */
    public PageResult<User> getUserPagingResult(UserSearchParam param);
}

创建用户信息业务逻辑类（UserServiceImpl.java），并实现UserService接口，编写业务逻辑方法。

package com.java265.sm.service.impl;
 
import com.java265.sm.dao.UserDao;
import com.java265.sm.entity.PageResult;
import com.java265.sm.entity.User;
import com.java265.sm.entity.UserSearchParam;
import com.java265.sm.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import java.util.List;
 
/**
 * 用户信息业务逻辑类
 * 
 **/
@Service
public class UserServiceImpl implements UserService
{
    @Autowired
    private UserDao userDao;
 
    /**
     * 分页查询用户列表
     */
    public PageResult<User> getUserPagingResult(UserSearchParam param)
    {
        PageResult<User> userPageResult = new PageResult<User>();
        //获取分页查询用户列表
        List<User> userList = userDao.getUserPaging(param);
        userPageResult.setDataList(userList);
        //统计用户数量
        int totalData = userDao.getCountUser(param);
        userPageResult.setTotalData(totalData);
        //分页信息
        userPageResult.setPageIndex(param.getPageIndex());
        userPageResult.setPageSize(param.getPageSize());
        //返回结果
        return userPageResult;
    }
}

//引入Dao的注解 @Autowired 及 Service 实现类的暴露注解 @Service。


//编写测试方法(Test层)
//创建用户信息业务逻辑测试类(UserTest.java)

package com.java265.sm.test;
import com.java265.sm.entity.PageResult;
import com.java265.sm.entity.User;
import com.java265.sm.entity.UserSearchParam;
import com.java265.sm.service.UserService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import java.util.List;
 
/**
 * 用户信息业务逻辑测试类
 * 
 **/
public class UserTest
{
    private ApplicationContext applicationContext;
    Logger logger = LogManager.getLogger(LogTest.class);
    private UserService userService;
 
    @Before
    public void setUp() throws Exception
    {
        //获取Spring配置文件对象
        applicationContext = new ClassPathXmlApplicationContext("classpath:spring/applicationContext.xml");
 
        //通过配置资源对象获取UserServiceImpl对象
        userService = (UserService)applicationContext.getBean("userServiceImpl");
    }
 
    @After
    public void tearDown() throws Exception
    {
    }
 
    /**
     * 分页查询用户列表
     */
    @Test
    public void getUserPagingResult()
    {
        //分页查询条件
        UserSearchParam userSearchParam = new UserSearchParam();
        userSearchParam.setPageIndex(2); //获取第2页的数据
        userSearchParam.setPageSize(10); //每页10条数据
        //userSearchParam.setUserName("pan_junbiao的博客"); //查询条件1
        //userSearchParam.setProvince("广东省"); //查询条件2
        userSearchParam.setOrderBy("create_time ASC"); //排序
        //分页查询用户列表
        PageResult<User> userPageResult = userService.getUserPagingResult(userSearchParam);
        //获取用户列表
        List<User> userList = userPageResult.getDataList();
        if (userList != null && userList.size()>0)
        {
            for(User user : userList)
            {
                System.out.println("编号：" + user.getUserId() +" 姓名：" + user.getUserName() + " 省份：" + user.getProvince());
            }
        }
        //分页信息
        System.out.println("当前页码：第" + userPageResult.getPageIndex()+"页");
        System.out.println("分页大小：每页" + userPageResult.getPageSize()+"条");
        System.out.println("数据总数：共" + userPageResult.getTotalData()+"条");
        System.out.println("总页数：共" + userPageResult.getTotalPage()+"页");
    }
}