SpringBoot整合MybatisPlus 实现多表分页条件联查

本文最后更新于2023.06.02-18:41，某些文章具有时效性，若有错误或已失效，请在下方留言或联系涛哥。

前言

在做这个之前，首先我们得了解 mybatis中的关联查询；包括一对一，一对多，多对一

技术选型

• SpringBoot
• MySQL
• Mybatis
• MybatisPlus

实现案列

1，pom文件配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.tg</groupId>
    <artifactId>server</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>server</name>
    <description>server</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
<!--        redis-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
<!--       session共享 -->
        <dependency>
            <groupId>org.springframework.session</groupId>
            <artifactId>spring-session-data-redis</artifactId>
        </dependency>
<!--        web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
<!--        mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <!--swagger-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.6</version>
        </dependency>
<!--        热部署-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
<!--        jdbc-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
<!--        lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
<!--        MybatisPlus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
<!--        MybatisPlus代码生成器-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.2</version>
        </dependency>
<!--        hutool-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <!---jwt-->
        <dependency>
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
            <version>3.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>
        <!--        测试-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

配置分页插件配置

package com.tg.admin.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Program: admin
 * @ClassName MybatisPlusConfig
 * @Author: liutao
 * @Description: Mybatisplus插件
 * @Create: 2023-03-12 13:07
 * @Version 1.0
 **/

@Configuration
@MapperScan("com.tg.admin.mapper")
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

}

mapper

IPage<ArticleVo> findPage(IPage<ArticleVo> page, @Param("ew") QueryWrapper<Article> queryWrapper);

xml文件（${ew.customSqlSegment} ）

<?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.tg.admin.mapper.ArticleMapper">
    <resultMap id="map" type="com.tg.admin.entity.vo.ArticleVo">
        <id property="id" column="id"/>
        <result property="title" column="title"/>
        <result property="coverImage" column="cover_image"/>
        <result property="content" column="content"/>
        <result property="author" column="author"/>
        <result property="status" column="status"/>
        <result property="createTime" column="create_time"/>
        <result property="category" column="cid"/>
        <result property="categoryName" column="categoryName"/>
        <collection property="tags" column="cid" ofType="integer" >
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
    <select id="findPage"  resultType="com.tg.admin.entity.vo.ArticleVo">
        SELECT a.id,
               a.title,
               a.cover_image,
               a.content,
               a.author,
               a.status,
               a.create_time,
               c.id                               as cid,
               c.name                             as categoryName,
               group_concat(t.id SEPARATOR ',')   AS tags,
               group_concat(t.NAME SEPARATOR ',') AS tagNames
        FROM article a
                 LEFT JOIN article_category_tag b ON a.id = b.article_id
                 LEFT JOIN category c ON b.category_id = c.id
                 LEFT JOIN tag t on b.tag_id = t.id
            ${ew.customSqlSegment}
    </select>

</mapper>

vo

package com.tg.admin.entity.vo;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

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

/**
 * @Program: admin
 * @ClassName ArtileDto
 * @Author: liutao
 * @Description: 文章分类
 * @Create: 2023-04-08 02:14
 * @Version 1.0
 **/


@NoArgsConstructor
@AllArgsConstructor
@Data
@ApiModel("Article文章dto")
public class ArticleVo implements Serializable {
    private static final long serialVersionUID = 1L;
    @ApiModelProperty("文章id")
    private Integer id;

    @ApiModelProperty("文章标题")
    private String title;

    @ApiModelProperty("封面图")
    private String coverImage;

    @ApiModelProperty("文章内容")
    private String content;

    @ApiModelProperty("作者")
    private String author;

    @ApiModelProperty("发布状态")
    private Integer status;

    @ApiModelProperty("发布时间")
    private Timestamp createTime;

    @ApiModelProperty("文章分类")
    private Integer category;

    @ApiModelProperty("文章名称")
    private String categoryName;

    @ApiModelProperty("文章标签")
    private String tagNames;
    @ApiModelProperty("文章标签")
    private String tags;

    private List<Integer> tagIds;
}

service

IPage<ArticleVo> findPage(IPage<ArticleVo> page, @Param("ew") QueryWrapper<Article> queryWrapper);

impl

 @Autowired
    private ArticleMapper articleMapper;
    @Override
    public IPage<ArticleVo> findPage(IPage<ArticleVo> page, QueryWrapper<Article> queryWrapper) {
        return articleMapper.findPage(page, queryWrapper);
    }

controller

 @ApiOperation("分页查询所有文章信息")
    @GetMapping("/page")
    public Result<Article> findPage(@RequestParam Integer pageNum,
                                    @RequestParam Integer pageSize,
                                    @RequestParam String name) {
        IPage<ArticleVo> page = new Page<>(pageNum, pageSize);
        QueryWrapper<Article> queryWrapper = new QueryWrapper<>();
        if (StrUtil.isNotBlank(name)) {
            queryWrapper.like("title", name);
        }
        return Result.success(articleService.findPage(page, queryWrapper));
    }