mybatis-plus 连接查询分页

前言

这篇简单讲讲连接查询分页

表数据在我这篇博客有 mybatis 多表连接查询，万能map

多表查询分页，其实和单表查询类似，只是换个泛型和方法，当然你不写泛型也可以，毕竟他只是泛型，不会影响代码执行

单表分页

使用 Mybatis 中 BaseMapper 中 selectPage方法，或者你也可以使用 ServiceImpl 的 page 方法

    @Test
    public void selectStuOfBook() {
    	// 1： 前端传递的参数，第 1 页；
    	// 3： 页面大小，每页 3 条
        Page<Student>> page = new Page<>(1,3);
        Page<Student> pageList = studentMapper.selectPage(page);
        System.out.println(JSON.toJSONString(pageList));
    }

多表分页

mapper.xml

就正常写 sql 不需要带分页相关参数

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ye.test.mapper.StudentMapper">

	<!-- type 一般写全类名，java.util.Map, map 也可以使用 -->
    <resultMap id="stuOfBookMap" type="map">
        <result column="t_id" property="tid"/>
        <result column="t_name" property="tname"/>
        <!-- property 可以随便指定，map 集合的 key -->
        <collection property="student" javaType="java.util.List" resultMap="studentMap"/>
    </resultMap>
    
    <resultMap id="studentMap" type="java.util.Map">
        <result column="s_id" property="sid"/>
        <result column="s_name" property="sname"/>
        <result column="s_tid" property="stid"/>
        <collection property="book" javaType="java.util.List" resultMap="bookMap"/>
    </resultMap>
    
    <resultMap id="bookMap" type="book">
        <result column="b_id" property="id"/>
        <result column="book_name" property="bookName"/>
    </resultMap>
    
    <select id="selectStuOfBook" resultMap="stuOfBookMap">
        SELECT t.id t_id, t.name t_name, s.id s_id, s.name s_name, s.tid s_tid, b.id b_id, b.book_name
        FROM mybatis.teacher t
                 LEFT JOIN mybatis.student s ON t.id = s.tid
                 LEFT JOIN mybatis.book b ON s.id = b.sid
    </select>
</mapper>

没分页写法

	// controller 方法
    @Test
    public void selectStuOfBook() {
       List<Map<String, Object>> pageList = studentMapper.selectStuOfBook();
        System.out.println(JSON.toJSONString(pageList));
    }
    
// mapper 接口
@Mapper
public interface StudentMapper extends BaseMapper<Student> {

   List<Map<String, Object>> selectStuOfBook();
}

没分页结果集

[{"student":[{"sname":"小明","book":[{"bookName":"java","id":1},{"bookName":"python","id":5},{"bookName":"js","id":7}],"stid":1,"sid":1},{"sname":"小红","book":[{"bookName":"c","id":2},{"bookName":"javaweb","id":6}],"stid":1,"sid":2},{"sname":"小张","book":[],"stid":1,"sid":3},{"sname":"小赵","book":[],"stid":1,"sid":7},{"sname":"小孙","book":[],"stid":1,"sid":8},{"sname":"小钱","book":[],"stid":1,"sid":9},{"sname":"小李","book":[],"stid":1,"sid":10},{"sname":"老王","book":[],"stid":1,"sid":11}],"tname":"秦老师","tid":1},{"student":[{"sname":"小李","book":[{"bookName":"c++","id":3},{"bookName":"vue","id":8}],"stid":2,"sid":4},{"sname":"小王","book":[{"bookName":"linux","id":4}],"stid":2,"sid":5},{"sname":"小黑","book":[],"stid":2,"sid":6}],"tname":"郭老师","tid":2}]

分页写法

和单表查询类似

    @Test
    public void selectStuOfBook() {
        Page<List<Map<String, Object>>> page = new Page<>(1,1);
        Page<List<Map<String, Object>>> pageList = studentMapper.selectStuOfBook(page);
        System.out.println(JSON.toJSONString(pageList));
    }
@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    Page<List<Map<String, Object>>> selectStuOfBook(Page<List<Map<String, Object>>> page);
}

不写泛型

    @Test
    public void selectStuOfBook() {
        Page page = new Page(1,1);
        Page pageList = studentMapper.selectStuOfBook(page);
        System.out.println(JSON.toJSONString(pageList));
    }
@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    Page selectStuOfBook(Page page);
}

分页结果集

两种分页结果相同

{"current":1,"optimizeCountSql":true,"orders":[],"pages":2,"records":[{"student":[{"sname":"小明","book":[{"bookName":"java","id":1}],"stid":1,"sid":1}],"tname":"秦老师","tid":1}],"searchCount":true,"size":1,"total":2}

带参数的分页，

和平时写法一样，正常带参就行

    @Test
    public void selectStuOfBook() {
        Map<String, Object> map = new HashMap<>();
        map.put("id",1);
        Page<List<Map<String, Object>>> page = new Page<>(1,1);
        Page<List<Map<String, Object>>> pageList = studentMapper.selectStuOfBook(page, map);
        System.out.println(JSON.toJSONString(pageList));
    }
    
// mapper 接口
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    Page<List<Map<String, Object>>> selectStuOfBook(Page<List<Map<String, Object>>> page, @Param("map") Map<String, Object> map);
}

mapper.xml

// mapper.xml 
SELECT t.id t_id, t.name t_name, s.id s_id, s.name s_name, s.tid s_tid, b.id b_id, b.book_name
        FROM mybatis.teacher t
                 LEFT JOIN mybatis.student s ON t.id = #{map.id}
                 LEFT JOIN mybatis.book b ON s.id = b.sid

如果你是 Service 层调的分页接口，改动如下

Page<List<Map<String, Object>>> pageList = studentMapper.selectStuOfBook(page, map);

改为

IPage<List<Map<String, Object>>> pageList = studentService.selectStuOfBook(page, map);

以上就是连接查询分页全部内容，我这是测试没有写 Service 层，建议真实开发加上，不过我觉得如果不需要 service 层处理业务数据，可以直接使用 mapper 去调用