Mybatis-plus實現(xiàn)join連表查詢的示例代碼
最近發(fā)現(xiàn)一個好玩的框架,我們知道m(xù)ybatis-plus在連表查詢上是不行的,如果需要連表查詢,那么我們就得乖乖的去寫xml文件了,但是今天發(fā)現(xiàn)一個新的框架 mybatis-plus-join。它既包含了mybatis-plus的所有優(yōu)點(diǎn),然后還支持連表查詢,還支持對多,對一的查詢,行了廢話不多說直接看代碼吧。
一、數(shù)據(jù)庫DDL
測試的數(shù)據(jù)庫,本測試基于mysql數(shù)據(jù)庫。
/* Navicat Premium Data Transfer Source Server : 本地數(shù)據(jù)庫 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, '開發(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, '采購經(jīng)理'); INSERT INTO `tb_post` VALUES (4, '采購專員'); 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 '部門id', `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '創(chuàng)建時間', `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 '創(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 = '測試用戶表' 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 = "創(chuàng)建時間")
private LocalDateTime createTime;
@ApiModelProperty(value = "修改時間")
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對象", 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;
}業(yè)務(wù)實體類
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,這個繼承不是必須的,我這里實現(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接口實現(xiàn)類,代碼里有詳細(xì)注釋
簡單的連表查詢
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>
* 測試用戶表 服務(wù)實現(xiàn)類
* </p>
*
* @author 熟透的蝸牛
* @since 2022-12-07
*/
@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {
@Autowired
private UserMapper userMapper; //這里對應(yīng)主表的mapper
/*
* @description:
* 連表分頁查詢,以下示例代碼為左連接查詢
* 內(nèi)連接方法 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) //左連接查詢,相當(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;
//分頁查詢等于執(zhí)行如下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>
* 職位 服務(wù)實現(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 ,以下示例代碼是對多查詢,對一查詢使用 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>
//返回數(shù)據(jù)如下 ,注意由于嵌套結(jié)果方式會導(dǎo)致結(jié)果集被折疊,因此分頁查詢的結(jié)果在折疊后總數(shù)會減少,所以無法保證分頁結(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": "采購經(jīng)理",
// "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>以上就是本人測試的結(jié)果,還有很多使用方法沒有一一驗證,如果感興趣的可以參考源代碼,自己動手試試
https://gitee.com/best_handsome/mybatis-plus-join
到此這篇關(guān)于Mybatis-plus實現(xiàn)join連表查詢的示例代碼的文章就介紹到這了,更多相關(guān)Mybatis-plus join連表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- Mybatis-Plus多表關(guān)聯(lián)查詢的使用案例解析
- MyBatis多表查詢和注解開發(fā)案例詳解
- mybatis-plus多表分頁查詢最佳實現(xiàn)方法(非常簡單)
- MyBatis中ResultMap與多表查詢的處理方法
- MybatisPlus多表連接查詢的具體實現(xiàn)
- mybatis于xml方式和注解方式實現(xiàn)多表查詢的操作方法
- mybatis-plus多表查詢操作方法
- MyBatis?實現(xiàn)動態(tài)排序的多表查詢
- 深入解析MybatisPlus多表連接查詢
- Mybatis分頁查詢主從表的實現(xiàn)示例
- mybatis連接數(shù)據(jù)庫實現(xiàn)雙表查詢
相關(guān)文章
SpringBoot之使用Feign實現(xiàn)微服務(wù)間的交互
這篇文章主要介紹了SpringBoot中使用Feign實現(xiàn)微服務(wù)間的交互,對微服務(wù)這方面感興趣的小伙伴可以參考閱讀本文2023-03-03
零基礎(chǔ)入門學(xué)習(xí)——Spring Boot注解(一)
這篇文章主要介紹了Spring Boot注解學(xué)習(xí)(一)要點(diǎn),非常不錯,具有參考借鑒價值,需要的朋友參考下吧2017-05-05
springboot整合mybatis-plus代碼生成器的配置解析
這篇文章主要介紹了springboot整合mybatis-plus代碼生成器的配置解析,本文通過實例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-02-02
Java 數(shù)據(jù)結(jié)構(gòu)與算法系列精講之背包問題
背包問題是一個非常典型的考察動態(tài)規(guī)劃應(yīng)用的題目,對其加上不同的限制和條件,可以衍生出諸多變種,若要全面理解動態(tài)規(guī)劃,就必須對背包問題了如指掌2022-02-02

