SpringBoot中如何整合MyBatis-Plus实现联表查询呢?

mybatis-plus

mybatis-plus是Mybatis的增强工具
主要为简化开发
mybatis-plus官网如下:
  https://baomidou.com/pages/24112f/   

 
一、引入依赖
<!-- mybatis-plus-join -->
<dependency>
    <groupId>com.github.java265</groupId>
    <artifactId>mybatis-plus-join</artifactId>
    <version>1.2.4</version>
</dependency>
<!-- mybatis-plus -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>
<!-- mysql连接 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>Runtime</scope>
</dependency>

 
二、mybatis配置信息
配置文件信息
spring:
  # 数据源配置
  datasource:
    # 连接池类型
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    # 数据库名称
    database: test
    port: 3306
    url: jdbc:mysql://127.0.0.1:${spring.datasource.port}/${spring.datasource.database}?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=UTF-8
    username: root
    password: 123456

# mybatis配置
mybatis-plus:
  # xml文件路径
  mapper-locations: classpath*:/mapper/*.xml
  # 实体类路径
  type-aliases-package: com.java265.entity
  configuration:
    # 驼峰转换
    map-underscore-to-camel-case: true
    # 是否开启缓存
    cache-enabled: false
    # 打印sql，正式环境关闭
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 全局配置
  global-config:
    db-config:
      #主键类型  0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
      id-type: AUTO

 
//配置类信息
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.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * mybatisplus配置类
 *
 * @author java265
 */
@Configuration
@MapperScan("com.java265.mapper")
public class MybatisPlusConfigurer {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}
 
//建库
CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

//建表

//1、user 表
CREATE TABLE `user`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sex` int(1) NULL DEFAULT NULL,
  `age` int(4) NULL DEFAULT NULL,
  `role_id` bigint(20) NULL DEFAULT NULL,
  `del_flag` int(3) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
//2、role表
CREATE TABLE `role`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `del_flag` int(3) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
//插入数据
INSERT INTO `role` VALUES (1, '超级管理员', 0);
INSERT INTO `user` VALUES (1, 'java265', 1, 22, 1, 0);


//代码自动生成

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("user")
@ApiModel(value="User对象", description="")
public class User extends Model<User> {


    @TableId("id")
    private Long id;

    @TableField("name")
    private String name;

    @TableField("sex")
    private Integer sex;

    @TableField("age")
    private Integer age;

    @TableField("role_id")
    private Long roleId;

    @ApiModelProperty(value = "删除状态（0--未删除1--已删除）")
    @TableField("del_flag")
    @TableLogic
    private Integer delFlag;
}

 
//UserMapper
import com.java265.entity.User;
import com.github.java265.base.MPJBaseMapper;

public interface UserMapper extends MPJBaseMapper<User> {

}
 
UserMapper.xml
<?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.java265.mapper.UserMapper">

</mapper>
 
//UserService
import com.java265.entity.User;
import com.github.java265.base.MPJBaseService;

public interface UserService extends MPJBaseService<User> {

}
 
//继承MPJBaseService

UserServiceImpl
import com.java265.entity.User;
import com.java265.mapper.UserMapper;
import com.java265.service.UserService;
import com.github.java265.base.MPJBaseServiceImpl;
import org.springframework.stereotype.Service;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author java265
 * @since 2022-12-14
 */
@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {

}

//联表查询
vo类
import com.java265.entity.User;
import lombok.Data;

@Data
public class UserVO extends User {

    private String roleName;
}

//联表查询
public UserVO getUserVO(Long id) {
    UserVO userVO = this.baseMapper.selectJoinOne(
            UserVO.class,
            new MPJLambdaWrapper<User>()
                    .selectAll(User.class)
                    .selectAs(Role::getName, UserVO::getRoleName)
                    .leftJoin(Role.class, Role::getId, User::getRoleId)
                    .eq(User::getId, id));
    return userVO;
}


//生成SQL
SELECT
	t.id,
	t.NAME,
	t.sex,
	t.age,
	t.role_id,
	t.del_flag,
	t1.NAME AS roleName 
FROM
	USER t
	LEFT JOIN role t1 ON ( t1.id = t.role_id ) 
WHERE
	t.del_flag = 0 
	AND ( t.id = ? )


//联表分页查询
public IPage<UserVO> getUserVO(Long id) {
    IPage<UserVO> list = this.baseMapper.selectJoinPage(
            new Page<UserVO>(1, 10),
            UserVO.class,
            new MPJLambdaWrapper<User>()
                    .selectAll(User.class)
                    .selectAs(Role::getName, UserVO::getRoleName)
                    .leftJoin(Role.class, Role::getId, User::getRoleId)
                    .eq(User::getId, id));
    return list;
}
 
//生成SQL
SELECT
	t.id,
	t.NAME,
	t.sex,
	t.age,
	t.role_id,
	t.del_flag,
	t1.NAME AS roleName 
FROM
	USER t
	LEFT JOIN role t1 ON ( t1.id = t.role_id ) 
WHERE
	t.del_flag = 0 
	AND ( t.id = ? ) 
	LIMIT ?
 
//普通写法（QueryWrapper）
public UserVO getUserVO(Long id) {
    UserVO userVO = this.baseMapper.selectJoinOne(
            UserVO.class,
            new MPJQueryWrapper<User>()
                    .selectAll(User.class)
                    .select("t1.name as role_name")
                    .leftJoin("role t1 on (t.role_id = t1.id)")
                    .eq("t.id", id));
    return userVO;
}
 
//生成SQL
SELECT
	t.id,
	t.NAME,
	t.sex,
	t.age,
	t.role_id,
	t.del_flag,
	t1.NAME AS role_name 
FROM
	USER t
	LEFT JOIN role t1 ON ( t.role_id = t1.id ) 
WHERE
	t.del_flag = 0 
	AND ( t.id = 1 )