Mybatis-plus-join连表查询

2023-08-31 15:24

短信预约 -IT技能 免费直播动态提醒

最近发现一个好玩的框架，我们知道mybatis-plus在连表查询上是不行的，如果需要连表查询，那么我们就得乖乖的去写xml文件了，但是今天发现一个新的框架 mybatis-plus-join。它既包含了mybatis-plus的所有优点，然后还支持连表查询，还支持对多，对一的查询，行了废话不多说直接看代码吧。

一、数据库DDL

测试的数据库，本测试基于mysql数据库。

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;@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;@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;@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;@Datapublic class PostUserDo extends Post {    private List userList;}

package com.wssnail.model.bo;import com.wssnail.model.User;import lombok.Data;@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;@Servicepublic class UserServiceImpl extends MPJBaseServiceImpl implements UserService {    @Autowired    private UserMapper userMapper; //这里对应主表的mapper        @Override    public 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;@Servicepublic class PostServiceImpl extends MPJBaseServiceImpl implements PostService {    @Autowired    private PostMapper postMapper;    @Override    public 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 {    @Bean    public MybatisPlusInterceptor mybatisPlusInterceptor() {        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));        return interceptor;    }}

三、pom依赖和配置文件

server:  port: 8090spring:  application:    name: test  datasource:    url: jdbc:mysql://127.0.0.1:3306/test-1?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai    username: root    password: snailpagehelper:  helper-dialect: mysql  reasonable: true  support-methods-arguments: false  params: count=countsql  #打印sqlmybatis-plus:  configuration:    mapper-locations: classpath*:mapper/*Mapper.xml    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

    4.0.0    com.wssnail    test-mybatis-plus-join    1.0-SNAPSHOT            8        8                org.springframework.boot        spring-boot-starter-parent        2.4.2                                    org.springframework.boot            spring-boot-starter-web                            mysql            mysql-connector-java                            org.apache.commons            commons-lang3            3.12.0                            com.baomidou            mybatis-plus-boot-starter            3.5.2                            com.github.yulichang            mybatis-plus-join-boot-starter            1.3.8                                    com.spring4all            swagger-spring-boot-starter            1.9.1.RELEASE                            com.github.xiaoymin            swagger-bootstrap-ui            1.9.6                            org.projectlombok            lombok            1.18.24