SpringBoot系列之集成MybatisPlus操作指南

1、什么是mybatisPlus？

MyBatis-Plus ，引用官网的说法：

MyBatis-Plus (opens new window)（简称 MP）是一个 MyBatis (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。

2、创建MybatisPlus项目

开发环境

• JDK 1.8
• SpringBoot2.2.1
• Maven 3.2+
• Mysql5.7.36
• 开发工具
  • IntelliJ IDEA
  • smartGit

使用阿里的脚手架，链接： https://start.aliyun.com

选择mybatis plus framework

新建application.yml，加上配置：

mybatis-plus:
  type-aliases-package: com.example.mybatisplus.*.*.model
  mapper-locations: classpath*:mapper/*/*.xml
  configuration:
    map-underscore-to-camel-case: true
    default-statement-timeout: 60

创建数据库表，sql使用官网的

DROP TABLE IF EXISTS user;
CREATE TABLE user
    id BIGINT(20) NOT NULL COMMENT '主键ID',
    name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (id)
);

表数据，数据使用官网的

DELETE FROM user;
INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, '[email protected]'),
(2, 'Jack', 20, '[email protected]'),
(3, 'Tom', 28, '[email protected]'),
(4, 'Sandy', 21, '[email protected]'),
(5, 'Billie', 24, '[email protected]');

3、MybatisPlus快速入门

写一个例子，写一套RestFul风格的curd接口：

加上注解 @TableName ，id加上 @TableId

package com.example.mybatisplus.model;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import lombok.experimental.SuperBuilder;
import java.io.Serializable;
@TableName("user")
@Data
@SuperBuilder(toBuilder = true)
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User implements Serializable {
    @TableId
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

加上一个mapper类，继承 BaseMapper

package com.example.mybatisplus.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mybatisplus.model.User;
public interface UserMapper extends BaseMapper<User>{
}

MapperScan 扫描具体的mapper包，就不需要每个mapper类都加 @Mapper

package com.example.mybatisplus;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
@SpringBootApplication
@MapperScan("com.example.mybatisplus.mapper")
public class SpringbootMybatisPlusApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatisPlusApplication.class, args);
}

接口实现 IService

package com.example.mybatisplus.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.mybatisplus.model.User;
public interface IUserService extends IService<User> {
}

接口实现类实现 ServiceImpl

package com.example.mybatisplus.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.mybatisplus.mapper.UserMapper;
import com.example.mybatisplus.model.User;
import com.example.mybatisplus.service.IUserService;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper , User> implements IUserService {
}

写一个RestFul风格的控制类：

package com.example.mybatisplus.controller;
import cn.hutool.core.bean.BeanUtil;
import com.example.mybatisplus.common.ResultBean;
import com.example.mybatisplus.model.User;
import com.example.mybatisplus.model.dto.UserDto;
import com.example.mybatisplus.model.vo.UserVo;
import com.example.mybatisplus.service.impl.UserServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping(value = "/api")
public class UserController {
    @Autowired
    private UserServiceImpl userService;
    @GetMapping(value = {"/user"})
    public ResultBean<List<UserVo>> users() {
        List<User> poList = userService.list();
        List<UserVo> voList = BeanUtil.copyToList(poList , UserVo.class);
        return ResultBean.ok(voList);
    @GetMapping(value = {"/user/{id}"})
    public ResultBean<UserVo> user(@PathVariable("id")Integer id) {
        User user = userService.getById(id);
        UserVo userVo = BeanUtil.copyProperties(user , UserVo.class);
        return ResultBean.ok(userVo);
    @PostMapping(value = "/user")
    public ResultBean<User> save(@RequestBody UserDto userDto) {
        User user = BeanUtil.copyProperties(userDto , User.class);
        boolean flag = userService.save(user);
        if (flag) return ResultBean.ok(user);
        return ResultBean.badRequest("新增失败");
    @DeleteMapping(value = "/user/{id}")
    public ResultBean<Integer> del(@PathVariable("id") Integer id) {
        boolean flag = userService.removeById(id);
        if (flag) return ResultBean.ok(id);
        return ResultBean.badRequest("删除失败");
    @PutMapping(value = "/user")
    public ResultBean<User> update(@RequestBody UserDto userDto) {
        User user = BeanUtil.copyProperties(userDto , User.class);
        boolean flag = userService.updateById(user);
        if (flag) return ResultBean.ok(user);
        return ResultBean.badRequest("更新失败");
}

封装一个ResultBean类：

package com.example.mybatisplus.common;
import lombok.Data;
import org.springframework.http.HttpStatus;
 * <pre>
 *      ResultBean
 * </pre>
 * <pre>
 * @author mazq
 * 修改记录
 *    修改后版本:     修改人：  修改日期: 2020/08/10 17:07  修改内容:
 * </pre>
@Data
public class ResultBean<T> {
	private int status;
	private String desc;
	 * 数据返回
	private T data;
	public ResultBean(int status, String desc, T data) {
		this.status = status;
		this.desc = desc;
		this.data = data;
	public ResultBean(T data) {
		this.status = HttpStatus.OK.value();
		this.desc = "处理成功";
		this.data = data;
	public static <T> ResultBean<T> ok(T data) {
		return new ResultBean(data);
	public static <T> ResultBean<T> ok() {
		return new ResultBean(null);
	public static <T> ResultBean<T> badRequest(String desc,T data) {
		return new ResultBean(HttpStatus.BAD_REQUEST.value(), desc, data);
	public static <T> ResultBean<T> badRequest(String desc) {
		return new ResultBean(HttpStatus.BAD_REQUEST.value(), desc, null);
	public static <T> ResultBean serverError(String desc, T data){
		return new ResultBean(HttpStatus.INTERNAL_SERVER_ERROR.value(),"服务器内部异常:"+desc,data);
	public static <T> ResultBean serverError(String desc){
		return new ResultBean(HttpStatus.INTERNAL_SERVER_ERROR.value(),"服务器内部异常:"+desc,null);
}

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>

4、多数据源配置

pom加上配置：

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

多数据源，参考 官网

spring:
  datasource:
    dynamic:
      primary: shop #设置默认的数据源或者数据源组,默认值即为master
      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
        shop:
          url: jdbc:mysql://127.0.0.1:3306/shop?useUnicode=true&characterEncoding=utf-8
          username: root
          password:
          driver-class-name: com.mysql.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
          schema: classpath:db/schema-mysql.sql
          data: classpath:db/data-mysql.sql
        slave_1:
          url: jdbc:mysql://127.0.0.1:3306/canaltest?useUnicode=true&characterEncoding=utf-8
          username: root
          password:
          driver-class-name: com.mysql.jdbc.Driver

表格来自 官网 ：

package com.example.mybatisplus.mapper;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mybatisplus.model.User;
@DS(value = "shop")
public interface UserMapper extends BaseMapper<User>{
}

有些版本可能可能会出现异常：Failed to configure a DataSource: ‘url‘ attribute is not specified and no embedded datasource

这种情况，可以排查数据源的自动配置

@SpringBootApplication(exclude= {DataSourceAutoConfiguration.class})

5、MybatisPlus测试

pom配置：

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

测试类：

package com.example.mybatisplus;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.test.autoconfigure.MybatisPlusTest;
import com.example.mybatisplus.mapper.UserMapper;
import com.example.mybatisplus.model.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;
@MybatisPlusTest
class MybatisPlusSampleTest {
    @Autowired
    private UserMapper userMapper;
    @Test
    void test() {
        List<User> userList = userMapper.selectList(new QueryWrapper<User>());
        userList.stream().forEach(System.out::println);
}

6、和Lambda表达式的妙用

mybatisPlus支持Lambda表示式，单表查询的业务一遍用不上，还是给下具体的业务场景：列举电商spu和sku的例子，spu表和sku表有一张关联表，传入一个spuCode，查询对应的spu清单，具体sql

SELECT
	t_sku sku
	LEFT JOIN t_spu_sku_relate r ON sku.skuCode = r.skuCode 
WHERE
	r.spuCode = #{spuCode};

先查出关联表数据，然后再查具体的业务表，可以使用 LambdaQueryWrapper 表达式

String spucode = "123456";
final List<SkuBean> list = new ArrayList<SkuBean>();
relationMapper.selectList(new LambdaQueryWrapper<SkuSpuRelationBean>()
                .eq(SkuSpuRelationBean::getSpuCode, spuCode)).forEach(
                relation ->
                        list.add(
                          skuMapper.selectById(relation.getSkuCode()))
        );

再给一个例子，使用了in，然后再嵌套一条sql的：

SELECT
	t_sku sku 
WHERE
	sku.skuCode IN (
	SELECT
		skuCode form t_spu_sku_relate 
WHERE