MyBatis手动SQL分页

亦凉 2023-06-04 10:55 22阅读 0赞

不少人在使用MyBatis时，涉及分页会使用PageHelper分页工具。在只对单表或者一对一的情况下，PageHelper确实挺简单方便的，但在**一对多的多表联表查询**时，却会出现某些问题，可以查看[这篇文章][Link 1]。

相比使用PageHelper分页工具，我们可以采取更加保险的原始分页（虽然相对会麻烦点，但却是最靠谱的），而且只需进行两次数据库查询，节省开销。

关于分页，其实最关键是获取选取页的如果数据以及总记录数。

## **案例：** ##

***三张表的初始数据：***  
老师表

CREATE TABLE `teacher`  (
      `tid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师名字',
      PRIMARY KEY (`tid`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES (1, '张老师');
    INSERT INTO `teacher` VALUES (2, '李老师');
    INSERT INTO `teacher` VALUES (3, '王老师');
    
    SET FOREIGN_KEY_CHECKS = 1;

班级表

CREATE TABLE `class`  (
      `cid` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级id',
      `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名称',
      `tid` int(11) NOT NULL COMMENT '老师id',
      PRIMARY KEY (`cid`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of class
    -- ----------------------------
    INSERT INTO `class` VALUES (1, '文一班', 1);
    INSERT INTO `class` VALUES (2, '文二班', 1);
    INSERT INTO `class` VALUES (3, '理一班', 2);
    INSERT INTO `class` VALUES (4, '理二班', 2);
    
    SET FOREIGN_KEY_CHECKS = 1;

学生表

CREATE TABLE `student`  (
      `sid` int(11) NOT NULL COMMENT '学生id',
      `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生名字',
      `cid` int(11) NOT NULL COMMENT '班级id',
      PRIMARY KEY (`sid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES (1, '麦兜', 1);
    INSERT INTO `student` VALUES (2, '麦唛', 1);
    INSERT INTO `student` VALUES (3, '阿May', 2);
    INSERT INTO `student` VALUES (4, '得巴', 3);
    INSERT INTO `student` VALUES (5, '阿辉', 4);
    
    SET FOREIGN_KEY_CHECKS = 1;

三者关系：老师一对多班级，班级一对多学生  
**需求**：根据条件查出老师以及老师所拥有的学生（注意老师是主表）  
所有数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70]

**代码（只贴关键部分）：**  
分页PageInfo自定义分装类（用于接收封装返回的数据，可根据自己的实际需要添加一些字段）；

import java.util.List;
    
    public class PageInfo<T> {
    
    	protected int pageNum = 1;//页码
    	protected int pageSize = 10;//返回条数
    	protected long totalRecord = -1;//总记录数
    	protected int totalPage = -1;//总页数
    	protected int startIndex = 0; //第几条记录开始
    	protected List<T> resultList;//结果集
    
    	public PageInfo() {
    	}
    
    	public PageInfo(int pageSize, int pageNum) {
    		this.pageSize = pageSize;
    		this.pageNum = pageNum;
    	}
    
    	public PageInfo(Integer pageNum) {
    		if (pageNum != null) {
    			this.pageNum = pageNum.intValue();
    		}
    	}
    
    	public int getPageNum() {
    		return pageNum;
    	}
    
    	public void setPageNum(int pageNum) {
    		this.pageNum = pageNum;
    		setPageSize(this.pageSize);
    	}
    
    	public int getPageSize() {
    		return pageSize;
    	}
    
    	public void setPageSize(int pageSize) {
    		this.pageSize = pageSize;
    		computeTotalPage();
    	}
    
    	public long getTotalRecord() {
    		return totalRecord;
    	}
    
    	public int getTotalPage() {
    		return totalPage;
    	}
    
    	public void setTotalRecord(long totalRecord) {
    		this.totalRecord = totalRecord;
    		computeTotalPage();
    	}
    
    	public int getStartIndex() {
    		return (pageNum - 1) * pageSize;
    	}
    
    	protected void computeTotalPage() {
    		if (getPageSize() > 0 && getTotalRecord() > -1) {
    			this.totalPage = (int) (getTotalRecord() % getPageSize() == 0 ? getTotalRecord()
    					/ getPageSize()
    					: getTotalRecord() / getPageSize() + 1);
    		}
    		if (this.totalPage == 0) {
    			this.totalPage = 1;
    		}
    	}
    
    	public List<T> getResultList() {
    		return resultList;
    	}
    
    	public void setResultList(List<T> resultList) {
    		this.resultList = resultList;
    	}
    
    }

service层（封装选取页的结果数据以及总记录数）：

@Service
    public class TeacherService {
    
        @Resource
        TeacherMapper teacherMapper;
    
        public PageInfo selectListByPageOriginal(PageParam pageParam) {
        	// mysql的limit分页语句:  limit startIndex, pageSize
        	// 所以需要传的参数是startIndex和pageSize
            List<TeacherDto> resultList = teacherMapper.selectListByPageOriginal((pageParam.getPageNum()-1)*pageParam.getPageSize(), pageParam.getPageSize());
            Integer totalRecord = teacherMapper.selectTtoal();
            PageInfo pageInfo = new PageInfo();
            pageInfo.setPageNum(pageParam.getPageNum());
            pageInfo.setPageSize(pageParam.getPageSize());
            pageInfo.setTotalRecord(totalRecord);
            pageInfo.setResultList(resultList);
            return pageInfo;
        }
    }

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.example.page.dao.TeacherMapper">
    
        <resultMap id="TeacherDtoMap" type="com.example.page.dto.TeacherDto">
            <id column="tid" jdbcType="INTEGER" property="tid" />
            <result column="tname" jdbcType="VARCHAR" property="tname" />
            <collection property="classDtoList" resultMap="classDtoMap" javaType="java.util.List">
            </collection>
        </resultMap>
    
        <resultMap id="classDtoMap" type="com.example.page.dto.ClassDto">
            <id column="cid" jdbcType="INTEGER" property="cid" />
            <result column="tid" jdbcType="INTEGER" property="tid" />
            <result column="cname" jdbcType="VARCHAR" property="cname" />
            <collection property="studentDtoList" ofType="com.example.page.dto.StudentDto" javaType="java.util.List">
                <id column="sid" jdbcType="INTEGER" property="sid" />
                <result column="cid" jdbcType="INTEGER" property="cid" />
                <result column="sname" jdbcType="VARCHAR" property="sname" />
            </collection>
        </resultMap>
    
       <select id="selectListByPageOriginal" resultMap="TeacherDtoMap">
          SELECT
           t1.*, t2.cid, t2.cname, t3.sid, t3.sname 
          FROM (SELECT * FROM teacher LIMIT #{startIndex}, #{pageSize}) t1 LEFT JOIN class t2 ON t1.tid = t2.tid LEFT JOIN student t3 ON t2.cid = t3.cid
        </select>
        
        <select id="selectTtoal" resultType="java.lang.Integer">
          SELECT count(1) FROM (SELECT t1.tid FROM teacher t1 LEFT JOIN class t2 ON t1.tid = t2.tid LEFT JOIN student t3 ON t2.cid = t3.cid GROUP BY t1.tid) t4
        </select>
    
    </mapper>

根据接口返回的结果，可看到当当前页为1，页大小为2时，会把张老师以及李老师的数据查询出来：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 1]

**说明：**

1.  在一对多的情况下，选取页的结果数据需要在主表中进行limit（假如有加条件判断的，需自主去分辨是主表的条件还是从表的条件），不能放在最外面，错误的写法如

SELECT t1.*, t2.cid, t2.cname, t3.sid, t3.sname FROM teacher t1 LEFT JOIN class t2 ON t1.tid = t2.tid LEFT JOIN student t3 ON t2.cid = t3.cid  LIMIT #{startIndex}, #{pageSize}

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 2]  
这样是对所有join查询得出来的结果分页  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 3]  
正确的sql查询语句查出的结果则是以下：  
![在这里插入图片描述][20191007234754910.png]

1.  获取的总记录数也应该是主表的总数（只需确保主表的主键不重复就可以了，可以通过group by主键语句或者distinct去重主键），即便有加条件判断。

更多的细节希望能举一反三，这里不做详细说明了！

[Link 1]: https://blog.csdn.net/u013068184/article/details/101909282
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70]: /images/20230601/568080d849a9439a9966f160aead8958.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 1]: /images/20230601/c264997cb3f6494d80815895f97d67d0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 2]: /images/20230601/7bdf07296ee948068e64197a2f830fd4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwNjgxODQ_size_16_color_FFFFFF_t_70 3]: /images/20230601/184ee31cc11a4defab786c4381fba95e.png
[20191007234754910.png]: /images/20230601/7249455c7e3340f68d614044ab4bdd56.png