Mybatis-plus-join连表查询
皇甫学海
目录
最近发现一个好玩的框架，我们知道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;
/**
* <p>
* 测试用户表
* </p>
*
* @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;
/**
* <p>
* 职位
* </p>
*
* @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;
/**
* <p>
* 部门
* </p>
*
* @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
*/
@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;
//部门名称
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，这个继承不是必须的，我这里实现了继承
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接口实现类，代码里有详细注释
简单的连表查询
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>
* 测试用户表 服务实现类
* </p>
*
* @author 熟透的蜗牛
* @since 2022-12-07
*/
@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {
@Autowired
private UserMapper userMapper; //这里对应主表的mapper
/*
* @description:
* 连表分页查询,以下示例代码为左连接查询
* 内连接方法 innerJoin()
* 右连接方法 rightJoin() 和这个使用方法一样
* @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) //查询部门表的部门名称
.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<UserDo> 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<User> page = new Page<>();
IPage<UserDo> 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;
/**
* <p>
* 职位 服务实现类
* </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() {
//相当于执行如下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配置
// <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>
//返回数据如下 ，注意由于嵌套结果方式会导致结果集被折叠，因此分页查询的结果在折叠后总数会减少，所以无法保证分页结果数量正确。
// [{
// "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<Post> postMPJLambdaWrapper = new MPJLambdaWrapper<Post>().selectAll(Post.class)
.selectCollection(User.class, PostUserDo::getUserList)
.leftJoin(User.class, User::getPostId, Post::getId);//一对多查询
List<PostUserDo> 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;
@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>以上就是本人测试的结果，还有很多使用方法没有一一验证，如果感兴趣的可以参考源代码，自己动手试试
类似资料：