这篇文章主要介绍了SpringBoot中怎么整合mybatisPlus Join使用联表查询的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇springBoot中怎么整合MyBatisPlus Join使用联表查
这篇文章主要介绍了SpringBoot中怎么整合mybatisPlus Join使用联表查询的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇springBoot中怎么整合MyBatisPlus Join使用联表查询文章都会有所收获,下面我们一起来看看吧。
相信大家在日常的开发中用的最多的就是 mybatis-plus了吧,作为一个 MyBatis (opens new window)的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
联表查询一直是 mybatis-plus 的短板之处,当需要联表查询时,还得打开 xml 文件写入长长的 sql 语句。于是有需求就有产出,mybatis-plus-join 出世了,可以以类似 mybatis-plus 中 QueryWrapper 的方式来进行联表查询,下面一起来体验吧!
<!-- 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>
配置文件信息
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@Configuration@MapperScan("com.asurplus.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;
建表
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;
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, 'Asurplus', 1, 18, 1, 0);
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
UserServiceImplimport 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;@Servicepublic class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {}
注意:这里我们继承了 MPJBaseServiceImpl
vo类
import com.asurplus.entity.User;import lombok.Data;@Datapublic 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语句中我们为它起的别名,如果使用原表名在运行中会出现报错。
关于“SpringBoot中怎么整合MyBatisPlus Join使用联表查询”这篇文章的内容就介绍到这里,感谢各位的阅读!相信大家对“SpringBoot中怎么整合MyBatisPlus Join使用联表查询”知识都有一定的了解,大家如果还想学习更多知识,欢迎关注编程网精选频道。
--结束END--
本文标题: SpringBoot中怎么整合MyBatisPlus Join使用联表查询
本文链接: https://www.lsjlt.com/news/350661.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0