Sample code for Mybatis-plus to implement join table query

Recently I discovered a fun framework. We know that mybatis-plus is not good for table-connected query. If we need to table-connected query, then we have to write the XML file obediently. However, today we found a new framework mybatis-plus-join. It not only contains all the advantages of mybatis-plus, but also supports continuous table query, and supports to-many and one-on-one query. If you can talk nonsense, just read the code without saying much.

1. Database DDL

The test database, this test is based on the mysql database.

/*
  Navicat Premium Data Transfer
  Source Server: Local Database
  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 'Primary Key',
  `dept_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'Department Name',
  `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 = 'department' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_dept
-- ----------------------------
INSERT INTO `tb_dept` VALUES (1, 'Human Affairs Department', '2022-12-07 13:06:06', '2022-12-07 13:06:06');
INSERT INTO `tb_dept` VALUES (2, 'Procurement Department', '2022-12-07 13:06:13', '2022-12-07 13:06:13');
INSERT INTO `tb_dept` VALUES (3, 'Development Department', '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 'Job Name',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Position' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_post
-- ----------------------------
INSERT INTO `tb_post` VALUES (1, 'Human Affairs Manager');
INSERT INTO `tb_post` VALUES (2, 'Personnel Specialist');
INSERT INTO `tb_post` VALUES (3, 'Purchasing Manager');
INSERT INTO `tb_post` VALUES (4, 'Procurement Specialist');
INSERT INTO `tb_post` VALUES (5, 'Technical Director');
INSERT INTO `tb_post` VALUES (6, 'Technical Manager');
-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'username',
  `post_id` int(11) NULL DEFAULT NULL COMMENT 'Position id',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT 'Department ID',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT 'Create time',
  `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT 'Modify time',
  `created` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT 'snail' COMMENT 'Create person',
  `updated` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT 'snail' COMMENT 'Modify',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Test User Table' 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;

2. JAVA code

Entity Class

package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
  * <p>
  * Test user table
  * </p>
  *
  * @author Ripe snail
  * @since 2022-12-07
  */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tb_user")
@ApiModel(value="User object", description="Test User Table")
public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    @ApiModelProperty(value = "Primary Key")
    @TableId(value = "id", type = )
    private Integer id;
    @ApiModelProperty(value = "username")
    private String userName;
    @ApiModelProperty(value = "Position ID")
    private Integer postId;
    @ApiModelProperty(value = "Department ID")
    private Integer deptId;
    @ApiModelProperty(value = "Create time")
    private LocalDateTime createTime;
    @ApiModelProperty(value = "Modify time")
    private LocalDateTime updateTime;
    @ApiModelProperty(value = "Create")
    private String created;
    @ApiModelProperty(value = "Modify")
    private String updated;
}

package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
  * <p>
  * Position
  * </p>
  *
  * @author Ripe snail
  * @since 2022-12-07
  */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tb_post")
@ApiModel(value="Post object", description="Position")
public class Post implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId(value = "id", type = )
    private Integer id;
    @ApiModelProperty(value = "Job Name")
    private String postName;
}

package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
  * <p>
  * department
  * </p>
  *
  * @author Ripe snail
  * @since 2022-12-07
  */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tb_dept")
@ApiModel(value="Dept object", description="department")
public class Dept implements Serializable {
    private static final long serialVersionUID = 1L;
    @ApiModelProperty(value = "Primary Key")
    @TableId(value = "id", type = )
    private Integer id;
    @ApiModelProperty(value = "Department Name")
    private String deptName;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

Business Entity

package ;
import ;
import ;
import ;
import ;
/**
  * @Author: Ripe snail
  * @CreateTime: 2022-12-07 13:57
  * @Description: There are multiple people under one position
  * @Version: 1.0
  */
@Data
public class PostUserDo extends Post {
    private List&lt;User&gt; userList;
}

package ;
import ;
import ;
/**
  * @Author: Ripe snail
  * @CreateTime: 2022-12-07 13:57
  * @Description: TODO
  * @Version: 1.0
  */
@Data
public class UserDo extends User {
    //Position name    private String postName;
    //Department name    private String deptName;
}

Mapper interface, note that the interface no longer inherits BaseMapper but inherits MPJBaseMapper.

@Repository
public interface DeptMapper extends MPJBaseMapper<Dept> {
}
@Repository
public interface PostMapper extends MPJBaseMapper<Post> {
}
@Repository
public interface UserMapper extends MPJBaseMapper<User> {
}

The service interface does not inherit BaseService but MPJBaseService. This inheritance is not necessary. I have implemented inheritance here.

public interface UserService extends MPJBaseService<User> {
    List<UserDo> listByPage(String postName, String userName);
}
public interface PostService extends MPJBaseService<Post> {
    List <PostUserDo> listPostUser();
}
public interface DeptService extends MPJBaseService<Dept> {
}

Service interface implementation class, detailed comments in the code

Simple table query

package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
  * <p>
  * Test user table Service implementation class
  * </p>
  *
  * @author Ripe snail
  * @since 2022-12-07
  */
@Service
public class UserServiceImpl extends MPJBaseServiceImpl&lt;UserMapper, User&gt; implements UserService {
    @Autowired
    private UserMapper userMapper; //The mapper corresponding to the main table here    /*
      * @description:
      * Connect table pagination query, the following example code is left-join query
      * InnerJoin()
      * Right join method rightJoin() is the same as this method
      * @date: 2022/12/7 14:05
      * @param postName
      * @param userName
      * @return: <>
      **/
    @Override
    public List&lt;UserDo&gt; listByPage(String postName, String userName) {
        MPJLambdaWrapper&lt;User&gt; userMPJLambdaWrapper = new MPJLambdaWrapper&lt;User&gt;()
                .selectAll() //Query all fields in the main table                .select(Dept::getDeptName) //Query department name of department table                .select(Post::getPostName) //Check the job list job name                .leftJoin(, Dept::getId, User::getDeptId) //Left join query, equivalent to left join dept on =user.dept_id                .leftJoin(, Post::getId, User::getPostId) // Left join query, equivalent to left join post on =user.post_id                .eq(Post::getPostName, postName)
                .like(User::getUserName, userName);
        //Return custom data, which is equivalent to executing the following SQL. You can see that the main table alias is t and the other table names are t1, t2 in turn.........        //        SELECT
        //        ,
        //        t.user_name,
        //        t.post_id,
        //        t.dept_id,
        //        t.create_time,
        //        t.update_time,
        //        ,
        //        ,
        //        t1.dept_name,
        //        t2.post_name
        //        FROM
        //        tb_user t
        //        LEFT JOIN tb_dept t1 ON (  = t.dept_id )
        //        LEFT JOIN tb_post t2 ON (  = t.post_id )
        //        WHERE
        //                (
        //                        t2.post_name = ?
        //                AND t.user_name LIKE ?)
//        List&lt;UserDo&gt; userDos = (, userMPJLambdaWrapper);
//        return userDos;
        //Pagination query is equal to executing the following SQL. Pagination query requires configuration of mybatis plus paging plug-in. For details, see Class//        SELECT
//        ,
//        t.user_name,
//        t.post_id,
//        t.dept_id,
//        t.create_time,
//        t.update_time,
//        ,
//        ,
//        t1.dept_name,
//        t2.post_name
//        FROM
//        tb_user t
//        LEFT JOIN tb_dept t1 ON (  = t.dept_id )
//        LEFT JOIN tb_post t2 ON (  = t.post_id )
//        WHERE
//                (
//                        t2.post_name = ?
//                AND t.user_name LIKE ?)
//        LIMIT ?
        Page&lt;User&gt; page = new Page&lt;&gt;();
        IPage&lt;UserDo&gt; userDoIPage = (page, , userMPJLambdaWrapper);
        return ();
    }
}

To many queries

package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
  * <p>
  * Job Service Implementation Class
  * </p>
  *
  * @author Ripe snail
  * @since 2022-12-07
  */
@Service
public class PostServiceImpl extends MPJBaseServiceImpl&lt;PostMapper, Post&gt; implements PostService {
    @Autowired
    private PostMapper postMapper;
    @Override
    public List&lt;PostUserDo&gt; listPostUser() {
        //It is equivalent to executing the following SQL. The following example code is for multiple queries and use the selectAssociation() method for one query, and the usage is the same as this//        SELECT
//        ,
//        t.post_name,
//         AS join_id,
//        t1.user_name,
//        t1.post_id,
//        t1.dept_id,
//        t1.create_time,
//        t1.update_time,
//        ,
//        
//        FROM
//        tb_post t
//        LEFT JOIN tb_user t1 ON (
//                t1.post_id = )
// Equivalent to the following xml configuration//        &lt;resultMap  type=""&gt;
//            &lt;result property="id" column="id"/&gt;
//            &lt;result property="postName" column="post_name"/&gt;
// <!--Other attributes are omitted-->//            &lt;collection property="userList" javaType=""
//                ofType=""&gt;
//                &lt;id property="id" column="id"/&gt;
//                &lt;result property="userName" column="user_name"/&gt;
// <!--Other attributes are omitted-->//            &lt;/collection&gt;
//        &lt;/resultMap&gt;
        //Return the data as follows. Note that because the nested result method will cause the result set to be collapsed, the total number of results of the paging query will decrease after collapse, so it is impossible to ensure that the number of paging results is correct.//       [{
//            "id": 1,
// "postName": "Human Affairs Manager",//            "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": "Personnel Specialist",//            "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": "Purchasing Manager",//            "userList": []
//        }]
        MPJLambdaWrapper&lt;Post&gt; postMPJLambdaWrapper = new MPJLambdaWrapper&lt;Post&gt;().selectAll()
                .selectCollection(, PostUserDo::getUserList)
                .leftJoin(, User::getPostId, Post::getId);//One-to-many query        List&lt;PostUserDo&gt; postUserDos = (, postMPJLambdaWrapper);
        return postUserDos;
    }
}

Pagination configuration

package ;
import ;
import ;
import ;
import ;
import ;
@Configuration
public class MybatisPageConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        (new PaginationInnerInterceptor());
        return interceptor;
    }
}

3. Pom dependencies and configuration files

server:
  port: 8090
spring:
  application:
    name: test
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/test-1?useUnicode=true&amp;characterEncoding=UTF-8&amp;zeroDateTimeBehavior=convertToNull&amp;useSSL=false&amp;allowMultiQueries=true&amp;serverTimezone=Asia/Shanghai
    username: root
    password: snail
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: false
  params: count=countsql
  #Print sqlmybatis-plus:
  configuration:
    mapper-locations: classpath*:mapper/*
    log-impl:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="/POM/4.0.0"
         xmlns:xsi="http:///2001/XMLSchema-instance"
         xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.">
    <modelVersion>4.0.0</modelVersion>
    <groupId></groupId>
    <artifactId>test-mybatis-plus-join</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <>8</>
        <>8</>
    </properties>
    <parent>
        <groupId></groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath/>
    </parent>
    <dependencies>
        <dependency>
            <groupId></groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId></groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version>
        </dependency>
        <dependency>
            <groupId></groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId></groupId>
            <artifactId>mybatis-plus-join-boot-starter</artifactId>
            <version>1.3.8</version>
        </dependency>
        <!--swagger-->
        <dependency>
            <groupId>com.spring4all</groupId>
            <artifactId>swagger-spring-boot-starter</artifactId>
            <version>1.9.</version>
        </dependency>
        <dependency>
            <groupId></groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.6</version>
        </dependency>
        <dependency>
            <groupId></groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>
</project>

The above are the results of my tests. There are many usage methods that have not been verified one by one. If you are interested, you can refer to the source code and try it yourself.

/best_handsome/mybatis-plus-join

This is the article about the sample code for Mybatis-plus join table query. For more related contents of Mybatis-plus join table query, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!