目录
一、数据库DDL
二、JAVA代码
三、pom依赖和配置文件
最近发现一个好玩的框架，我们知道mybatis-plus在连表查询上是不行的，如果需要连表查询，那么我们就得乖乖的去写xml文件了，但是今天发现一个新的框架 mybatis-plus-join。它既包含了mybatis-plus的所有优点，然后还支持连表查询，还支持对多，对一的查询，行了废话不多说直接看代码吧。
一、数据库DDL
测试的数据库，本测试基于mysql数据库。
/* Navicat Premium Data Transfer Source Server : 本地数据库 Source Server Type: MySQL Source Server Version : 50710 Source Host : localhost:3306 Source Schema : test-1 Target Server Type: MySQL Target Server Version : 50710 File Encoding : 65001 Date: 07/12/2022 15:35:14*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for tb_dept-- ----------------------------DROP TABLE IF EXISTS `tb_dept`;CREATE TABLE `tb_dept`(`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`dept_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '部门名称',`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),`update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '部门' ROW_FORMAT = Dynamic;-- ------------------------------ Records of tb_dept-- ----------------------------INSERT INTO `tb_dept` VALUES (1, '人事部', '2022-12-07 13:06:06', '2022-12-07 13:06:06');INSERT INTO `tb_dept` VALUES (2, '采购部', '2022-12-07 13:06:13', '2022-12-07 13:06:13');INSERT INTO `tb_dept` VALUES (3, '开发部', '2022-12-07 13:06:17', '2022-12-07 13:06:17');-- ------------------------------ Table structure for tb_post-- ----------------------------DROP TABLE IF EXISTS `tb_post`;CREATE TABLE `tb_post`(`id` int(11) NOT NULL AUTO_INCREMENT,`post_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '职位名称',PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '职位' ROW_FORMAT = Dynamic;-- ------------------------------ Records of tb_post-- ----------------------------INSERT INTO `tb_post` VALUES (1, '人事经理');INSERT INTO `tb_post` VALUES (2, '人事专员');INSERT INTO `tb_post` VALUES (3, '采购经理');INSERT INTO `tb_post` VALUES (4, '采购专员');INSERT INTO `tb_post` VALUES (5, '技术总监');INSERT INTO `tb_post` VALUES (6, '技术经理');-- ------------------------------ Table structure for tb_user-- ----------------------------DROP TABLE IF EXISTS `tb_user`;CREATE TABLE `tb_user`(`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '用户名',`post_id` int(11) NULL DEFAULT NULL COMMENT '职位id',`dept_id` int(11) NULL DEFAULT NULL COMMENT '部门id',`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',`update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',`created` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT 'snail' COMMENT '创建人',`updated` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT 'snail' COMMENT '修改人',PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '测试用户表' ROW_FORMAT = Dynamic;-- ------------------------------ Records of tb_user-- ----------------------------INSERT INTO `tb_user` VALUES (1, 'admin', 1, 1, '2022-12-07 12:03:20', '2022-12-07 12:03:20', 'snail', 'snail');INSERT INTO `tb_user` VALUES (2, 'test', 2, 1, '2022-12-07 12:03:51', '2022-12-07 12:03:51', 'snail', 'snail');INSERT INTO `tb_user` VALUES (3, 'test1', 1, 1, '2022-12-07 12:04:03', '2022-12-07 12:04:03', 'snail', 'snail');SET FOREIGN_KEY_CHECKS = 1;二、JAVA代码
实体类
package com.wssnail.model;import com.baomidou.mybatisplus.annotation.TableName;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableId;import java.time.LocalDateTime;import java.io.Serializable;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.EqualsAndHashCode;/** * * 测试用户表 *
* * @author 熟透的蜗牛 * @since 2022-12-07 */@Data@EqualsAndHashCode(callSuper = false)@TableName("tb_user")@ApiModel(value="User对象", description="测试用户表")public class User implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty(value = "主键")@TableId(value = "id", type = IdType.AUTO)private Integer id;@ApiModelProperty(value = "用户名")private String userName;@ApiModelProperty(value = "职位id")private Integer postId;@ApiModelProperty(value = "部门id")private Integer deptId;@ApiModelProperty(value = "创建时间")private LocalDateTime createTime;@ApiModelProperty(value = "修改时间")private LocalDateTime updateTime;@ApiModelProperty(value = "创建人")private String created;@ApiModelProperty(value = "修改人")private String updated;}package com.wssnail.model;import com.baomidou.mybatisplus.annotation.TableName;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableId;import java.io.Serializable;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.EqualsAndHashCode;/** * * 职位 *
* * @author 熟透的蜗牛 * @since 2022-12-07 */@Data@EqualsAndHashCode(callSuper = false)@TableName("tb_post")@ApiModel(value="Post对象", description="职位")public class Post implements Serializable {private static final long serialVersionUID = 1L;@TableId(value = "id", type = IdType.AUTO)private Integer id;@ApiModelProperty(value = "职位名称")private String postName;}package com.wssnail.model;import com.baomidou.mybatisplus.annotation.IdType;import java.time.LocalDateTime;import java.io.Serializable;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.EqualsAndHashCode;/** * * 部门 *
* * @author 熟透的蜗牛 * @since 2022-12-07 */@Data@EqualsAndHashCode(callSuper = false)@TableName("tb_dept")@ApiModel(value="Dept对象", description="部门")public class Dept implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty(value = "主键")@TableId(value = "id", type = IdType.AUTO)private Integer id;@ApiModelProperty(value = "部门名称")private String deptName;private LocalDateTime createTime;private LocalDateTime updateTime;}业务实体类
package com.wssnail.model.bo;import com.wssnail.model.Post;import com.wssnail.model.User;import lombok.Data;import java.util.List;/** * @Author: 熟透的蜗牛 * @CreateTime: 2022-12-07 13:57 * @Description: 一个岗位下有多个人 * @Version: 1.0 */@Datapublic class PostUserDo extends Post {private List userList;}package com.wssnail.model.bo;import com.wssnail.model.User;import lombok.Data;/** * @Author: 熟透的蜗牛 * @CreateTime: 2022-12-07 13:57 * @Description: TODO * @Version: 1.0 */@Datapublic class UserDo extends User {//岗位名称private String postName;//部门名称private String deptName;}mapper接口，注意接口不再继承BaseMapper 而是继承了MPJBaseMapper
@Repositorypublic interface DeptMapper extends MPJBaseMapper {}@Repositorypublic interface PostMapper extends MPJBaseMapper {}@Repositorypublic interface UserMapper extends MPJBaseMapper {}service接口也不是继承BaseService而是继承了MPJBaseService，这个继承不是必须的，我这里实现了继承
public interface UserService extends MPJBaseService {List listByPage(String postName, String userName);}public interface PostService extends MPJBaseService {List listPostUser();}public interface DeptService extends MPJBaseService {}service接口实现类，代码里有详细注释
简单的连表查询
package com.wssnail.service.impl;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import com.github.yulichang.base.MPJBaseServiceImpl;import com.github.yulichang.wrapper.MPJLambdaWrapper;import com.wssnail.mapper.UserMapper;import com.wssnail.model.Dept;import com.wssnail.model.Post;import com.wssnail.model.User;import com.wssnail.model.bo.UserDo;import com.wssnail.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;/** * * 测试用户表 服务实现类 *
* * @author 熟透的蜗牛 * @since 2022-12-07 */@Servicepublic class UserServiceImpl extends MPJBaseServiceImpl implements UserService {@Autowiredprivate UserMapper userMapper; //这里对应主表的mapper/* * @description: * 连表分页查询,以下示例代码为左连接查询 * 内连接方法 innerJoin() * 右连接方法 rightJoin() 和这个使用方法一样 * @date: 2022/12/7 14:05 * @param postName * @param userName * @return: java.util.List **/@Overridepublic List listByPage(String postName, String userName) {MPJLambdaWrapper userMPJLambdaWrapper = new MPJLambdaWrapper().selectAll(User.class) //查询主表所有的字段.select(Dept::getDeptName) //查询部门表的部门名称.select(Post::getPostName) //查询岗位表的 岗位名称.leftJoin(Dept.class, Dept::getId, User::getDeptId) //左连接查询，相当于 left join dept on dept.id=user.dept_id.leftJoin(Post.class, Post::getId, User::getPostId) // 左连接查询，相当于 left join post on post.id=user.post_id.eq(Post::getPostName, postName).like(User::getUserName, userName);//返回自定义的数据，相当于执行如下SQL，可以看出主表别名为t 其他表名依次为t1,t2.........//SELECT//t.id,//t.user_name,//t.post_id,//t.dept_id,//t.create_time,//t.update_time,//t.created,//t.updated,//t1.dept_name,//t2.post_name//FROM//tb_user t//LEFT JOIN tb_dept t1 ON ( t1.id = t.dept_id )//LEFT JOIN tb_post t2 ON ( t2.id = t.post_id )//WHERE//(//t2.post_name = ?//AND t.user_name LIKE ?)//List userDos = userMapper.selectJoinList(UserDo.class, userMPJLambdaWrapper);//return userDos;//分页查询等于执行如下SQL，分页查询需要 配置mybatis plus 分页插件，详情见 com.wssnail.config.MybatisPageConfig 类//SELECT//t.id,//t.user_name,//t.post_id,//t.dept_id,//t.create_time,//t.update_time,//t.created,//t.updated,//t1.dept_name,//t2.post_name//FROM//tb_user t//LEFT JOIN tb_dept t1 ON ( t1.id = t.dept_id )//LEFT JOIN tb_post t2 ON ( t2.id = t.post_id )//WHERE//(//t2.post_name = ?//AND t.user_name LIKE ?)//LIMIT ?Page page = new Page();IPage userDoIPage = userMapper.selectJoinPage(page, UserDo.class, userMPJLambdaWrapper);return userDoIPage.getRecords();}}对多查询
package com.wssnail.service.impl;import com.github.yulichang.base.MPJBaseServiceImpl;import com.github.yulichang.wrapper.MPJLambdaWrapper;import com.wssnail.mapper.PostMapper;import com.wssnail.model.Post;import com.wssnail.model.User;import com.wssnail.model.bo.PostUserDo;import com.wssnail.service.PostService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;/** * * 职位 服务实现类 *
* * @author 熟透的蜗牛 * @since 2022-12-07 */@Servicepublic class PostServiceImpl extends MPJBaseServiceImpl implements PostService {@Autowiredprivate PostMapper postMapper;@Overridepublic List listPostUser() {//相当于执行如下SQL ，以下示例代码是对多查询，对一查询使用 selectAssociation()方法，用法与此相同//SELECT//t.id,//t.post_name,//t1.id AS join_id,//t1.user_name,//t1.post_id,//t1.dept_id,//t1.create_time,//t1.update_time,//t1.created,//t1.updated//FROM//tb_post t//LEFT JOIN tb_user t1 ON (//t1.post_id = t.id)//等价于 如下的xml配置//////////////////////返回数据如下 ，注意由于嵌套结果方式会导致结果集被折叠，因此分页查询的结果在折叠后总数会减少，所以无法保证分页结果数量正确。// [{//"id": 1,//"postName": "人事经理",//"userList": [{//"id": 1,//"userName": "admin",//"postId": 1,//"deptId": 1,//"createTime": "2022-12-07T12:03:20",//"updateTime": "2022-12-07T12:03:20",//"created": "snail",//"updated": "snail"//}, {//"id": 3,//"userName": "test1",//"postId": 1,//"deptId": 1,//"createTime": "2022-12-07T12:04:03",//"updateTime": "2022-12-07T12:04:03",//"created": "snail",//"updated": "snail"//}]//}, {//"id": 2,//"postName": "人事专员",//"userList": [{//"id": 2,//"userName": "test",//"postId": 2,//"deptId": 1,//"createTime": "2022-12-07T12:03:51",//"updateTime": "2022-12-07T12:03:51",//"created": "snail",//"updated": "snail"//}]//}, {//"id": 3,//"postName": "采购经理",//"userList": []//}]MPJLambdaWrapper postMPJLambdaWrapper = new MPJLambdaWrapper().selectAll(Post.class).selectCollection(User.class, PostUserDo::getUserList).leftJoin(User.class, User::getPostId, Post::getId);//一对多查询List postUserDos = postMapper.selectJoinList(PostUserDo.class, postMPJLambdaWrapper);return postUserDos;}}分页配置
package com.wssnail.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;@Configurationpublic class MybatisPageConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));return interceptor;}}三、pom依赖和配置文件
server:port: 8090spring:application:name: testdatasource:url: jdbc:mysql://127.0.0.1:3306/test-1?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghaiusername: rootpassword: snailpagehelper:helper-dialect: mysqlreasonable: truesupport-methods-arguments: falseparams: count=countsql#打印sqlmybatis-plus:configuration:mapper-locations: classpath*:mapper/*Mapper.xmllog-impl: org.apache.ibatis.logging.stdout.StdOutImpl4.0.0com.wssnailtest-mybatis-plus-join1.0-SNAPSHOT88org.springframework.bootspring-boot-starter-parent2.4.2org.springframework.bootspring-boot-starter-webmysqlmysql-connector-javaorg.apache.commonscommons-lang33.12.0com.baomidoumybatis-plus-boot-starter3.5.2com.github.yulichangmybatis-plus-join-boot-starter1.3.8com.spring4allswagger-spring-boot-starter1.9.1.RELEASEcom.github.xiaoyminswagger-bootstrap-ui1.9.6org.projectlomboklombok1.18.24以上就是本人测试的结果，还有很多使用方法没有一一验证，如果感兴趣的可以参考源代码，自己动手试试
https://gitee.com/best_handsome/mybatis-plus-join