SpringBoot集成MyBatis-Plus-Join

1.前言

1.1 简介

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

1.2 特点

• 无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
• 无感引入, 支持MP风格的查询, 您会MP就会MPJ, 无需额外的学习成本
• 兼容MP的别名、逻辑删除、枚举列、TypeHandle列等特性
• 支持注解形式一对一、一对多和连表查询形式的一对一和一对多

1.3 官网

https://mybatisplusjoin.com

2.代码

2.1 引入依赖

<dependency>
    <groupId>com.github.yulichang</groupId>
    <artifactId>mybatis-plus-join-boot-starter</artifactId>
    <version>1.4.11</version>
</dependency>

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

2.2 配置

# mybatis-plus配置
mybatis-plus:
  # 启动检查MyBatis配置文件
  check-config-location: false
  # MyBatis配置文件位置
  config-location:
  # MyBaits别名包扫描路径
  type-aliases-package: com.qiangesoft.mybatisplusjoin.entity
  # Mapper所对应的XML文件位置 默认【classpath*:/mapper/**/*.xml】
  mapper-locations: classpath*:/mapper/*Mapper.xml
  # TypeHandler扫描路径
  type-handlers-package:
  configuration:
    # 日志打印
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 是否开启自动驼峰命名规则
    map-underscore-to-camel-case: true
    # 开启Mybatis二级缓存，默认为true
    cache-enabled: true
  global-config:
    # 控制台mybatis-plus的logo
    banner: true
    db-config:
      # 全局默认主键类型
      id-type: auto
      # 逻辑删除配置
      logic-delete-field: deleted
      logic-delete-value: 1
      logic-not-delete-value: 0

2.3 表准备

CREATE TABLE `sys_user` (
                            `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
                            `dept_id` bigint DEFAULT NULL COMMENT '部门id',
                            `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '账号',
                            `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '密码',
                            `nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '昵称',
                            `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户邮箱',
                            `phone_number` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '手机号码',
                            `sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '用户性别（0男 1女）',
                            `avatar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '头像地址',
                            `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',
                            `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
                            `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志',
                            `version` int DEFAULT NULL COMMENT '乐观锁',
                            `create_by` bigint DEFAULT NULL COMMENT '创建人',
                            `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                            `update_by` bigint DEFAULT NULL COMMENT '更新人',
                            `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                            PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户信息';

CREATE TABLE `sys_dept` (
                            `id` bigint NOT NULL AUTO_INCREMENT COMMENT '部门id',
                            `parent_id` bigint DEFAULT '0' COMMENT '父部门id',
                            `dept_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '部门名称',
                            `order_num` int DEFAULT '0' COMMENT '显示顺序',
                            `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '部门状态（0正常 1停用）',
                            `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
                            `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志',
                            `create_by` bigint DEFAULT NULL COMMENT '创建人',
                            `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                            `update_by` bigint DEFAULT NULL COMMENT '更新人',
                            `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                            PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='部门信息';

2.4 配置mapper扫描

package com.qiangesoft.mybatisplus;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.qiangesoft.mybatisplus.mapper")
@SpringBootApplication
public class MybatisPlusApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisPlusApplication.class, args);
    }

}

2.5 配置分页插件

package com.qiangesoft.mybatisplusjoin.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * mybatis-plus配置
 *
 * @author qiangesoft
 * @date 2024-04-11
 */
@Configuration
public class MybatisPlusConfig {

    /**
     * 分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 指定数据库mysql
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        // 智能，根据实际数据库类型构造分页sql
//        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }

}

2.6 字段填充

package com.qiangesoft.mybatisplusjoin.config;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;

/**
 * 自定义sql字段填充器，自动填充创建修改相关字段
 *
 * @author qiangesoft
 * @date 2024-04-11
 */
@Component
public class CustomMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        setFieldValByName("createBy", 1L, metaObject);
        setFieldValByName("createTime", LocalDateTime.now(), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        setFieldValByName("updateBy", 1L, metaObject);
        setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
    }
}

2.7 api使用

package com.qiangesoft.mybatisplusjoin.controller;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.yulichang.toolkit.JoinWrappers;
import com.github.yulichang.wrapper.DeleteJoinWrapper;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.github.yulichang.wrapper.UpdateJoinWrapper;
import com.qiangesoft.mybatisplusjoin.entity.SysDept;
import com.qiangesoft.mybatisplusjoin.entity.SysUser;
import com.qiangesoft.mybatisplusjoin.service.ISysUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * <p>
 * 用户信息 前端控制器
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
@RestController
@RequestMapping("/sys-user")
public class SysUserController {

    @Autowired
    private ISysUserService sysUserService;

    @GetMapping("/page")
    public Page<SysUser> page() {
        // SELECT t.*,t1.dept_name FROM sys_user t LEFT JOIN sys_dept t1 ON t1.id = t.dept_id WHERE (t.id = ?) LIMIT ?,?
        MPJLambdaWrapper<SysUser> wrapper = new MPJLambdaWrapper<>();
        wrapper.selectAll(SysUser.class)
                .select(SysDept::getDeptName)
                .leftJoin(SysDept.class, SysDept::getId, SysUser::getDeptId)
                .eq(SysUser::getId, 1);
        return sysUserService.selectJoinListPage(new Page<>(1, 10), SysUser.class, wrapper);
    }

    @GetMapping("/list")
    public List<SysUser> list() {
        // SELECT t.*,t1.dept_name FROM sys_user t LEFT JOIN sys_dept t1 ON t1.id = t.dept_id WHERE (t.id = ?)
        MPJLambdaWrapper<SysUser> wrapper = new MPJLambdaWrapper<>();
        wrapper.selectAll(SysUser.class)
                .select(SysDept::getDeptName)
                .leftJoin(SysDept.class, SysDept::getId, SysUser::getDeptId)
                .eq(SysUser::getId, 1);
        return sysUserService.selectJoinList(SysUser.class, wrapper);
    }

    @GetMapping("/deleteJoinMaster")
    public Boolean deleteJoinMaster() {
        // DELETE t FROM `sys_user` t LEFT JOIN sys_dept t1 ON (t1.id = t.dept_id) WHERE (t.id = ?)
        DeleteJoinWrapper<SysUser> wrapper = JoinWrappers.delete(SysUser.class)
                .leftJoin(SysDept.class, SysDept::getId, SysUser::getDeptId)
                .eq(SysUser::getId, 1);
        return sysUserService.deleteJoin(wrapper);
    }

    @GetMapping("/deleteJoinAll")
    public Boolean deleteJoinAll() {
        // DELETE t,t1 FROM `sys_user` t LEFT JOIN sys_dept t1 ON (t1.id = t.dept_id) WHERE (t.id = ?)
        DeleteJoinWrapper<SysUser> wrapper = JoinWrappers.delete(SysUser.class)
                .deleteAll()
                .leftJoin(SysDept.class, SysDept::getId, SysUser::getDeptId)
                .eq(SysUser::getId, 1);
        return sysUserService.deleteJoin(wrapper);
    }

    @GetMapping("/update")
    public Boolean update() {
        // UPDATE `sys_user` t LEFT JOIN sys_dept t1 ON (t1.id = t.dept_id) SET t.nick_name=?, t1.dept_name=? WHERE (t.id = ?)
        UpdateJoinWrapper<SysUser> wrapper = JoinWrappers.update(SysUser.class)
                .set(SysUser::getNickName, "aaaaaa")
                .set(SysDept::getDeptName, "bbbbb")
                .leftJoin(SysDept.class, SysDept::getId, SysUser::getDeptId)
                .eq(SysUser::getId, 1);
        return sysUserService.updateJoin(null, wrapper);
    }
}

继承

package com.qiangesoft.mybatisplusjoin.mapper;

import com.github.yulichang.base.MPJBaseMapper;
import com.qiangesoft.mybatisplusjoin.entity.SysUser;

/**
 * <p>
 * 用户信息 Mapper 接口
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
public interface SysUserMapper extends MPJBaseMapper<SysUser> {

}

package com.qiangesoft.mybatisplusjoin.service;

import com.github.yulichang.base.MPJBaseService;
import com.qiangesoft.mybatisplusjoin.entity.SysUser;

/**
 * <p>
 * 用户信息 服务类
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
public interface ISysUserService extends MPJBaseService<SysUser> {

}

package com.qiangesoft.mybatisplusjoin.service.impl;

import com.github.yulichang.base.MPJBaseServiceImpl;
import com.qiangesoft.mybatisplusjoin.entity.SysUser;
import com.qiangesoft.mybatisplusjoin.mapper.SysUserMapper;
import com.qiangesoft.mybatisplusjoin.service.ISysUserService;
import org.springframework.stereotype.Service;

/**
 * <p>
 * 用户信息 服务实现类
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
@Service
public class SysUserServiceImpl extends MPJBaseServiceImpl<SysUserMapper, SysUser> implements ISysUserService {

}