这篇文章主要介绍了SpringBoot中怎么整合MyBatisPlus Join使用联表查询的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇SpringBoot中怎么整合MyBatisPlus Join使用联表查询文章都会有所收获，下面我们一起来看看吧。
1、mybatis-plus
相信大家在日常的开发中用的最多的就是 mybatis-plus了吧，作为一个 MyBatis (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
2、mybatis-plus-join
联表查询一直是 mybatis-plus 的短板之处，当需要联表查询时，还得打开 xml 文件写入长长的 sql 语句。于是有需求就有产出，mybatis-plus-join 出世了，可以以类似 mybatis-plus 中 QueryWrapper 的方式来进行联表查询，下面一起来体验吧！
3、引入依赖
<!-- mybatis-plus-join --> <dependency> <groupId>com.github.yulichang</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>
4、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.asurplus.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 asurplus
*/
@Configuration
@MapperScan("com.asurplus.mapper")
public class MybatisPlusConfigurer {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}5、建库建表
建库
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;
3、插入数据
INSERT INTO `role` VALUES (1, '超级管理员', 0); INSERT INTO `user` VALUES (1, 'Asurplus', 1, 18, 1, 0);
6、代码自动生成
User
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.asurplus.entity.User;
import com.github.yulichang.base.MPJBaseMapper;
public interface UserMapper extends MPJBaseMapper<User> {
}注意：这里我们继承了 MPJBaseMapper
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.asurplus.mapper.UserMapper"> </mapper>
UserService
import com.asurplus.entity.User;
import com.github.yulichang.base.MPJBaseService;
public interface UserService extends MPJBaseService<User> {
}注意：这里我们继承了 MPJBaseService
UserServiceImpl
import com.asurplus.entity.User;
import com.asurplus.mapper.UserMapper;
import com.asurplus.service.UserService;
import com.github.yulichang.base.MPJBaseServiceImpl;
import org.springframework.stereotype.Service;
/**
* <p>
* 服务实现类
* </p>
*
* @author lizhou
* @since 2022-12-14
*/
@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {
}注意：这里我们继承了 MPJBaseServiceImpl
7、联表查询
vo类
import com.asurplus.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 )
运行结果与之前完全相同，需要注意的是，这样写时在引用表名时不要使用数据库中的原表名，主表默认使用 t，其他表使用join语句中我们为它起的别名，如果使用原表名在运行中会出现报错。