Mybatis-plus實(shí)現(xiàn)join連表查詢的示例代碼

更新時(shí)間：2023年08月31日 11:13:03 作者：熟透的蝸牛

mybatis-plus在連表查詢上是不行的,如果需要連表查詢,就得乖乖的去寫(xiě)xml文件了,本文介紹了mybatis-plus-join框架,它支持連表查詢,感興趣的可以了解一下

最近發(fā)現(xiàn)一個(gè)好玩的框架，我們知道m(xù)ybatis-plus在連表查詢上是不行的，如果需要連表查詢，那么我們就得乖乖的去寫(xiě)xml文件了，但是今天發(fā)現(xiàn)一個(gè)新的框架 mybatis-plus-join。它既包含了mybatis-plus的所有優(yōu)點(diǎn)，然后還支持連表查詢，還支持對(duì)多，對(duì)一的查詢，行了廢話不多說(shuō)直接看代碼吧。

一、數(shù)據(jù)庫(kù)DDL

測(cè)試的數(shù)據(jù)庫(kù)，本測(cè)試基于mysql數(shù)據(jù)庫(kù)。

/*
 Navicat Premium Data Transfer
 Source Server         : 本地?cái)?shù)據(jù)庫(kù)
 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 '部門(mén)名稱',
  `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 = '部門(mén)' 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, '采購(gòu)部', '2022-12-07 13:06:13', '2022-12-07 13:06:13');
INSERT INTO `tb_dept` VALUES (3, '開(kāi)發(fā)部', '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, '人事經(jīng)理');
INSERT INTO `tb_post` VALUES (2, '人事專員');
INSERT INTO `tb_post` VALUES (3, '采購(gòu)經(jīng)理');
INSERT INTO `tb_post` VALUES (4, '采購(gòu)專員');
INSERT INTO `tb_post` VALUES (5, '技術(shù)總監(jiān)');
INSERT INTO `tb_post` VALUES (6, '技術(shù)經(jīng)理');
-- ----------------------------
-- 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 '部門(mén)id',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改時(shí)間',
  `created` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT 'snail' COMMENT '創(chuàng)建人',
  `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 = '測(cè)試用戶表' 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代碼

實(shí)體類

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;
/**
 * <p>
 * 測(cè)試用戶表
 * </p>
 *
 * @author 熟透的蝸牛
 * @since 2022-12-07
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tb_user")
@ApiModel(value="User對(duì)象", description="測(cè)試用戶表")
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 = "部門(mén)id")
    private Integer deptId;
    @ApiModelProperty(value = "創(chuàng)建時(shí)間")
    private LocalDateTime createTime;
    @ApiModelProperty(value = "修改時(shí)間")
    private LocalDateTime updateTime;
    @ApiModelProperty(value = "創(chuàng)建人")
    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;
/**
 * <p>
 * 職位
 * </p>
 *
 * @author 熟透的蝸牛
 * @since 2022-12-07
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tb_post")
@ApiModel(value="Post對(duì)象", 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;
/**
 * <p>
 * 部門(mén)
 * </p>
 *
 * @author 熟透的蝸牛
 * @since 2022-12-07
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tb_dept")
@ApiModel(value="Dept對(duì)象", description="部門(mén)")
public class Dept implements Serializable {
    private static final long serialVersionUID = 1L;
    @ApiModelProperty(value = "主鍵")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    @ApiModelProperty(value = "部門(mén)名稱")
    private String deptName;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

業(yè)務(wù)實(shí)體類

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: 一個(gè)崗位下有多個(gè)人
 * @Version: 1.0
 */
@Data
public class PostUserDo extends Post {
    private List<User> 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
 */
@Data
public class UserDo extends User {
    //崗位名稱
    private String postName;
    //部門(mén)名稱
    private String deptName;
}

mapper接口，注意接口不再繼承BaseMapper 而是繼承了MPJBaseMapper

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

service接口也不是繼承BaseService而是繼承了MPJBaseService，這個(gè)繼承不是必須的，我這里實(shí)現(xiàn)了繼承

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接口實(shí)現(xiàn)類，代碼里有詳細(xì)注釋

簡(jiǎn)單的連表查詢

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;
/**
 * <p>
 * 測(cè)試用戶表 服務(wù)實(shí)現(xiàn)類
 * </p>
 *
 * @author 熟透的蝸牛
 * @since 2022-12-07
 */
@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {
    @Autowired
    private UserMapper userMapper; //這里對(duì)應(yīng)主表的mapper
    /*
     * @description:
     * 連表分頁(yè)查詢,以下示例代碼為左連接查詢
     * 內(nèi)連接方法 innerJoin()
     * 右連接方法 rightJoin() 和這個(gè)使用方法一樣
     * @date: 2022/12/7 14:05
     * @param postName
     * @param userName
     * @return: java.util.List<com.wssnail.model.bo.UserDo>
     **/
    @Override
    public List<UserDo> listByPage(String postName, String userName) {
        MPJLambdaWrapper<User> userMPJLambdaWrapper = new MPJLambdaWrapper<User>()
                .selectAll(User.class) //查詢主表所有的字段
                .select(Dept::getDeptName) //查詢部門(mén)表的部門(mén)名稱
                .select(Post::getPostName) //查詢崗位表的 崗位名稱
                .leftJoin(Dept.class, Dept::getId, User::getDeptId) //左連接查詢，相當(dāng)于 left join dept on dept.id=user.dept_id
                .leftJoin(Post.class, Post::getId, User::getPostId) // 左連接查詢，相當(dāng)于 left join post on post.id=user.post_id
                .eq(Post::getPostName, postName)
                .like(User::getUserName, userName);
        //返回自定義的數(shù)據(jù)，相當(dāng)于執(zhí)行如下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<UserDo> userDos = userMapper.selectJoinList(UserDo.class, userMPJLambdaWrapper);
//        return userDos;
        //分頁(yè)查詢等于執(zhí)行如下SQL，分頁(yè)查詢需要 配置mybatis plus 分頁(yè)插件，詳情見(jiàn) 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<User> page = new Page<>();
        IPage<UserDo> userDoIPage = userMapper.selectJoinPage(page, UserDo.class, userMPJLambdaWrapper);
        return userDoIPage.getRecords();
    }
}

對(duì)多查詢

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;
/**
 * <p>
 * 職位 服務(wù)實(shí)現(xiàn)類
 * </p>
 *
 * @author 熟透的蝸牛
 * @since 2022-12-07
 */
@Service
public class PostServiceImpl extends MPJBaseServiceImpl<PostMapper, Post> implements PostService {
    @Autowired
    private PostMapper postMapper;
    @Override
    public List<PostUserDo> listPostUser() {
        //相當(dāng)于執(zhí)行如下SQL ，以下示例代碼是對(duì)多查詢，對(duì)一查詢使用 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)
//        等價(jià)于 如下的xml配置
//        <resultMap id="xxxxxxxx" type="com.wssnail.model.bo.PostUserDo">
//            <result property="id" column="id"/>
//            <result property="postName" column="post_name"/>
//            <!--其他屬性省略-->
//            <collection property="userList" javaType="java.util.List"
//                ofType="com.wssnail.model.User">
//                <id property="id" column="id"/>
//                <result property="userName" column="user_name"/>
//                <!--其他屬性省略-->
//            </collection>
//        </resultMap>
        //返回?cái)?shù)據(jù)如下 ，注意由于嵌套結(jié)果方式會(huì)導(dǎo)致結(jié)果集被折疊，因此分頁(yè)查詢的結(jié)果在折疊后總數(shù)會(huì)減少，所以無(wú)法保證分頁(yè)結(jié)果數(shù)量正確。
//       [{
//            "id": 1,
//            "postName": "人事經(jīng)理",
//            "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": "采購(gòu)經(jīng)理",
//            "userList": []
//        }]
        MPJLambdaWrapper<Post> postMPJLambdaWrapper = new MPJLambdaWrapper<Post>().selectAll(Post.class)
                .selectCollection(User.class, PostUserDo::getUserList)
                .leftJoin(User.class, User::getPostId, Post::getId);//一對(duì)多查詢
        List<PostUserDo> postUserDos = postMapper.selectJoinList(PostUserDo.class, postMPJLambdaWrapper);
        return postUserDos;
    }
}

分頁(yè)配置

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;
@Configuration
public class MybatisPageConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

三、pom依賴和配置文件

server:
  port: 8090
spring:
  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: snail
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: false
  params: count=countsql
  #打印sql
mybatis-plus:
  configuration:
    mapper-locations: classpath*:mapper/*Mapper.xml
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.wssnail</groupId>
    <artifactId>test-mybatis-plus-join</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath/>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.github.yulichang</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.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>
</project>

以上就是本人測(cè)試的結(jié)果，還有很多使用方法沒(méi)有一一驗(yàn)證，如果感興趣的可以參考源代碼，自己動(dòng)手試試

https://gitee.com/best_handsome/mybatis-plus-join

到此這篇關(guān)于Mybatis-plus實(shí)現(xiàn)join連表查詢的示例代碼的文章就介紹到這了,更多相關(guān)Mybatis-plus join連表查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: