解决Mybatis-Plus或PageHelper多表分页查询总条数不对问题

识时务者J

已于 2022-09-26 09:56:56 修改

阅读量1.4w

点赞数 11

分类专栏：项目问题文章标签： mybatis java mysql

于 2022-06-22 14:21:33 首次发布

本文链接：https://blog.csdn.net/qq_65377318/article/details/125408219

版权

项目问题专栏收录该内容

10 篇文章 3 订阅

订阅专栏

前言

项目老大说项目需要重构搜索功能，决定交给我这个比较闲的人！嗯？？？

因为以前的项目数据不大，都不能说不大，是很少，所有搜索采用的是MySQL中的like模糊搜索操作的，他希望我改一下；

我第一时间想到了ES，但他说没必要用ES，等以后数据量大了再换，现在只是稍微多了一些数据，没必要

Ok！那我就用了MySQL自带的全文检索功能，因为本文主要说的还是Mybatis-Plus的问题，所以全文检索在下面只会提到怎么使用，以及一些问题

好像说了一大堆废话，回归正题！

项目以前分页搜索用的是PageHelper这个插件，但公司封装的3.0框架中已经封装了Mybatis-Plus，所以我采用了Mybatis-Plus的分页插件

一、问题说明

场景：

老师表是有4条数据，每个老师对应2个学生

使用的是两个表联查letf join加Mybatis的级联查询，一次性获取所有数据出现3个问题：

1、数据总条数以及页数不对

2、数据分页数量不对

3、数据混乱

已下是我有问题的代码：

1、引入依赖

版本选择尽量3.4+

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>

2、Mybatis-Plus配置

@Configuration
public class MybatisPlusConfig {

    /**
     * 插件注册
     *
     * @param paginationInnerInterceptor 分页插件
     * @return MybatisPlus拦截器
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(PaginationInnerInterceptor paginationInnerInterceptor) {
        MybatisPlusInterceptor mp = new MybatisPlusInterceptor();
        mp.addInnerInterceptor(paginationInnerInterceptor);
        return mp;
    }

    //分页插件
    @Bean
    public PaginationInnerInterceptor paginationInnerInterceptor() {
        PaginationInnerInterceptor pii = new PaginationInnerInterceptor();
        pii.setMaxLimit(20L);
        pii.setDbType(DbType.MYSQL);
        //当超过最大页数时不会报错
        pii.setOverflow(true);
        return pii;
    }

}

3、创建mapper层

创建了一个返回实体类TeacherVO，包括老师信息以及学生信息，以及一个传入的参数类TeacherRequestVo

@Data
public class TeacherVO {
     /**
     * 跟学生表关联的字段
     */
    private String classs;

    private String tname;

    private String tsex;

    private Date tbirthday;

    private String prof;

    private String depart;

    private List<Student> student;
}

@Data
public class TeacherRequestVo {
    private String classs;
    private String tname;
    private String sname;
}

public interface TeacherMapper extends BaseMapper<Teacher> {
    /**
     * 获取老师所带班级中的所有老师及学生信息
     * @param page mybatisplus自带的page类
     * @param teacherRequestVo 传入的参数
     * @return
     */
    Page<TeacherVO> getAll(Page<TeacherVO> page, TeacherRequestVo teacherRequestVo);
}

4、编写xxxMapper.xml文件

<resultMap id="GetAllMap" type="com.qjj.demo.entity.vo.TeacherVO">
    <!--@mbg.generated-->
    <!--@Table teacher-->
    <result column="classs" jdbcType="VARCHAR" property="classs"/>
    <result column="Tname" jdbcType="VARCHAR" property="tname"/>
    <result column="Tsex" jdbcType="VARCHAR" property="tsex"/>
    <result column="Tbirthday" jdbcType="TIMESTAMP" property="tbirthday"/>
    <result column="Prof" jdbcType="VARCHAR" property="prof"/>
    <result column="Depart" jdbcType="VARCHAR" property="depart"/>
    <collection property="student"
                ofType="com.qjj.demo.entity.Student"
                resultMap="com.qjj.consumer.mapper.StudentMapper.BaseResultMap"/>
</resultMap>

<select id="getAll" resultMap="GetAllMap">
    select *
    from teacher t
    left join student s on t.classs = s.classs
    <where>
        <if test="param2.size != null">
            and s.size <![CDATA[ <= ]]> #{param2.size}
        </if>
        <if test="param2.classs != null and param2.classs != ''">
            and t.classs = #{param2.classs}
        </if>
        <if test="param2.sname != null and param2.sname != ''">
            and s.Sname = #{param2.sname}
        </if>
        <if test="param2.tname != null and param2.tname != ''">
            and t.Tname = #{param2.tname}
        </if>
    </where>
</select>

5、测试一（不传任何条件，只分页）

测试结果应该是二条数据，总数是四条

@RestController
@RequestMapping("/demo")
public class DemoController {

    @Resource
    private TeacherMapper teacherMapper;


    @PostMapping("/test3")
    public Page<TeacherVO> getAll(TeacherRequestVo teacherRequestVo) {
        Page<TeacherVO> teacherVOPage = new Page<>(1, 2);
        return teacherMapper.getAll(teacherVOPage, teacherRequestVo);
    }

}

在这里插入图片描述

{
    "records": [
        {
            "classs": "804",
            "tname": "李诚",
            "tsex": "男",
            "tbirthday": "1958-12-02 00:00:00",
            "prof": "副教授",
            "depart": "计算机系",
            "student": [
                {
                    "sno": "108",
                    "sname": "丘东",
                    "ssex": "男",
                    "sbirthday": "1977-09-01 00:00:00",
                    "classs": null
                },
                {
                    "sno": "105",
                    "sname": "匡明",
                    "ssex": "男",
                    "sbirthday": "1975-10-02 00:00:00",
                    "classs": null
                }
            ]
        }
    ],
    "total": 4,
    "size": 2,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 2
}

5.1、结果总结

1、总条数正确

2、页数正确

3、数据不正确，返回条数不正确，应该返回两条数据，但现在只返回了一条

5.2、结果分析

查看它最终指向的sql语句

找到在SimpleExecutor下的doQuery方法。

在这里插入图片描述

总条数的sql语句为：

SELECT COUNT(*) AS total FROM teacher t

分页语句为：

select *
   from teacher t
       left join student s on t.classs = s.classs LIMIT 2

拿去数据库运行结果为：

在这里插入图片描述

至此可以看出它只是获取了同一个老师下两个不同的学生信息；

而不是我们想象的两个老师，分别对应多个学生；

但总条数和条数正确

6、测试二（传两个表的条件）

在这里插入图片描述

得到的结果应该是一个老师对应他下面的两个学生

总条数是1

总数是1

6.1、测试结果

{
    "records": [
        {
            "classs": "804",
            "tname": "李诚",
            "tsex": "男",
            "tbirthday": "1958-12-02 00:00:00",
            "prof": "副教授",
            "depart": "计算机系",
            "student": [
                {
                    "sno": "108",
                    "sname": "丘东",
                    "ssex": "男",
                    "sbirthday": "1977-09-01 00:00:00",
                    "classs": null,
                    "size": 1
                },
                {
                    "sno": "105",
                    "sname": "匡明",
                    "ssex": "男",
                    "sbirthday": "1975-10-02 00:00:00",
                    "classs": null,
                    "size": 2
                }
            ]
        }
    ],
    "total": 2,
    "size": 2,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 1
}

6.2、结果总结

总条数不对

页数虽然对，但是那是因为我们分页的数量是2，而学生表中正好是一个老师对应两个学生，所以才对，但只要当一个老师对应3个学生或者超过2的话，页数也就不会对了，这里就不给大家测试了，大家可以自行测试一下

数据虽然看起来对的，但是跟页数是一样的道理，其实是错的

6.3、结果分析

还是查看它最终执行的SQL语句：

发现执行查询总条数的SQL语句有问题

SELECT COUNT(*) AS total FROM teacher t LEFT JOIN student s ON t.classs = s.classs WHERE s.size <= 3 AND t.classs = '804'

二、解决

在上面的测试中发现两个问题

1、数据不对

2、条数和页数不对

1、没条件查询只分页

我们修改xxxMapper.xml中的resultMap采用级联查询

  <resultMap id="GetAllMap" type="com.qjj.demo.entity.vo.TeacherVO">
        <!--@mbg.generated-->
        <!--@Table teacher-->
        <result column="classs" jdbcType="VARCHAR" property="classs"/>
        <result column="Tname" jdbcType="VARCHAR" property="tname"/>
        <result column="Tsex" jdbcType="VARCHAR" property="tsex"/>
        <result column="Tbirthday" jdbcType="TIMESTAMP" property="tbirthday"/>
        <result column="Prof" jdbcType="VARCHAR" property="prof"/>
        <result column="Depart" jdbcType="VARCHAR" property="depart"/>
        <collection property="student"
                    ofType="com.qjj.demo.entity.Student1"
                    column="classs"
                    select="getStudent"/>
    </resultMap>
    <select id="getAll" resultMap="GetAllMap">
        select t.*
        from teacher t
                     left join student s on t.classs = s.classs
        <where>
            <if test="param2.size != null">
                and s.size <![CDATA[ <= ]]> #{param2.size}
            </if>
            <if test="param2.classs != null and param2.classs != ''">
                and t.classs = #{param2.classs}
            </if>
            <if test="param2.sname != null and param2.sname != ''">
                and s.Sname = #{param2.sname}
            </if>
            <if test="param2.tname != null and param2.tname != ''">
                and t.Tname = #{param2.tname}
            </if>
        </where>
    </select>
    
    <select id="getStudent" resultMap="com.qjj.demo.mapper.Student1Mapper.BaseResultMap">
        select *
        from student
        where classs = #{classs}
    </select>

在这里插入图片描述

{
    "records": [
        {
            "classs": "804",
            "tname": "李诚",
            "tsex": "男",
            "tbirthday": "1958-12-02 00:00:00",
            "prof": "副教授",
            "depart": "计算机系",
            "student": [
                {
                    "sno": "108",
                    "sname": "丘东",
                    "ssex": "男",
                    "sbirthday": "1977-09-01 00:00:00",
                    "classs": null,
                    "size": 1
                },
                {
                    "sno": "105",
                    "sname": "匡明",
                    "ssex": "男",
                    "sbirthday": "1975-10-02 00:00:00",
                    "classs": null,
                    "size": 2
                }
            ]
        },
        {
            "classs": "804",
            "tname": "李诚",
            "tsex": "男",
            "tbirthday": "1958-12-02 00:00:00",
            "prof": "副教授",
            "depart": "计算机系",
            "student": [
                {
                    "sno": "108",
                    "sname": "丘东",
                    "ssex": "男",
                    "sbirthday": "1977-09-01 00:00:00",
                    "classs": null,
                    "size": 1
                },
                {
                    "sno": "105",
                    "sname": "匡明",
                    "ssex": "男",
                    "sbirthday": "1975-10-02 00:00:00",
                    "classs": null,
                    "size": 2
                }
            ]
        }
    ],
    "total": 4,
    "size": 2,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 2
}

2、两个表都有条件

在这里插入图片描述

{
    "records": [
        {
            "classs": "804",
            "tname": "李诚",
            "tsex": "男",
            "tbirthday": "1958-12-02 00:00:00",
            "prof": "副教授",
            "depart": "计算机系",
            "student": [
                {
                    "sno": "108",
                    "sname": "丘东",
                    "ssex": "男",
                    "sbirthday": "1977-09-01 00:00:00",
                    "classs": null,
                    "size": 1
                },
                {
                    "sno": "105",
                    "sname": "匡明",
                    "ssex": "男",
                    "sbirthday": "1975-10-02 00:00:00",
                    "classs": null,
                    "size": 2
                }
            ]
        },
        {
            "classs": "804",
            "tname": "李诚",
            "tsex": "男",
            "tbirthday": "1958-12-02 00:00:00",
            "prof": "副教授",
            "depart": "计算机系",
            "student": [
                {
                    "sno": "108",
                    "sname": "丘东",
                    "ssex": "男",
                    "sbirthday": "1977-09-01 00:00:00",
                    "classs": null,
                    "size": 1
                },
                {
                    "sno": "105",
                    "sname": "匡明",
                    "ssex": "男",
                    "sbirthday": "1975-10-02 00:00:00",
                    "classs": null,
                    "size": 2
                }
            ]
        }
    ],
    "total": 2,
    "size": 2,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "searchCount": true,
    "countId": null,
    "maxLimit": null,
    "pages": 1
}

3、结果总结

无条件时

数量正确，数据重复，页数正确
两表都有条件时：

总数不对，数据重复，页数不正确

4、结果分析

查看最终sql语句

查询总条数的SQL语句：

SELECT COUNT(*) AS total FROM teacher t LEFT JOIN student s ON t.classs = s.classs WHERE s.size <= ? AND t.classs = ?

查询老师表的SQL语句：

  select t.*
        from teacher t
              left join student s on t.classs = s.classs
        WHERE s.size  <= 3
        and t.classs = "804" LIMIT 2

去数据库执行发现查询老师表的sql语句查出两条相同结果

其实到这里很多人都知道怎么解决了，只要去除重复的数据，所有问题都可以解决，无论是用去重，还是GROUP BY都可以实现，我下面采用GROUP BY

5、最终方案

加上GROUP BY进行去重，其他地方都没改动

 <select id="getAll" resultMap="GetAllMap">
        select t.classs,
               t.Tname,
               t.Tsex,
               t.Tbirthday,
               t.Prof,
               t.Depart
        from teacher t
                     left join student s on t.classs = s.classs
        <where>
            <if test="param2.size != null">
                and s.size <![CDATA[ <= ]]> #{param2.size}
            </if>
            <if test="param2.classs != null and param2.classs != ''">
                and t.classs = #{param2.classs}
            </if>
            <if test="param2.sname != null and param2.sname != ''">
                and s.Sname = #{param2.sname}
            </if>
            <if test="param2.tname != null and param2.tname != ''">
                and t.Tname = #{param2.tname}
            </if>
        </where>
        GROUP BY t.classs
    </select>