How to realize Multi-table Joint query by MyBatis-Plus

This article mainly introduces the relevant knowledge of "how to achieve multi-table check in MyBatis-Plus". The editor shows you the operation process through an actual case. The operation method is simple and fast, and it is practical. I hope this article "how to achieve multi-table check in MyBatis-Plus" can help you solve the problem.

Build database and table DROP DATABASE IF EXISTS mp;CREATE DATABASE mp DEFAULT CHARACTER SET utf8;USE mp; DROP TABLE IF EXISTS `t _ user`; DROP TABLE IF EXISTS `t_ blog`; SET NAMES utf8mb4 CREATE TABLE `tuser` (`id` BIGINT (0) NOT NULL AUTO_INCREMENT, `create_ name` VARCHAR (64) NOT NULL COMMENT 'username (cannot be repeated)', `nick_ name`username (64) NOT NULL COMMENT 'nickname (repeatable)', `email`VARCHAR (64) COMMENT 'mailbox', `create_ time`DATETIME (0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time' `user_ time` DATETIME (0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', `deleted_ time` BIGINT (0) NOT NULL DEFAULT 0 COMMENT'0: not deleted other: deleted', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `index_user_name_deleted_ time` (`user_ name`, `deleted_ time`), KEY `index_create_ time` (`create_ time`) ENGINE = InnoDB COMMENT = 'user' CREATE TABLE `t_ blog``id` BIGINT (0) NOT NULL AUTO_INCREMENT, `user_ id` BIGINT (0) NOT NULL, `user_ name` VARCHAR (64) NOT NULL, `title`VARCHAR (256) CHARACTER SET utf8mb4 NOT NULL COMMENT 'title', `abstraction `VARCHAR (256) CHARACTER SET utf8mb4 NOT NULL COMMENT', `content`LONGTEXT CHARACTER SET utf8mb4 NOT NULL COMMENT 'content', KEY `index_user_ id` (`user_ id`) ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = 'blog' INSERT INTO `t _ user`VALUES (1, 'knife',' Blade', 'abc@qq.com',' 2021-01-23 09 knife', 33 knife', 36, '2021-01-23 09 knife', 33 knife', 36, 0); INSERT INTO `t _ user` knife', (2,' sky', 'Sky Blue', '123 qq.compositions,' 2021-01-24 18 18 knife', 21 colors, '2021-01-24 18 18 abc@qq.com', 21 colors, 0) INSERT INTO `tblog` VALUES (1, 1, 'knife',' Java), 'this article introduces the use of enumerated classes of Java', '2021-01-23 11-33-33-36, 2021-01-23 11-33-33, 0) INSERT INTO `tblog`VALUES (2, 1, 'knife',' Java usage of generics', 'this article introduces the use of generics of Java. , '2021-01-28 23 knife', 37 Java 37 HashMap,' 2021-01-28 23 15 37 knife', 37); INSERT INTO `t _ blog` HashMap (3, 1, 'knife',' Java principle', 'this paper introduces the principle of HashMap of Java. , '2021-05-28 09 BigDecimal 06 BigDecimal,' 2021-05-28 09 V 06 BigDecimal, 0); INSERT INTO `t _ blog` BigDecimal (4,1, 'knife',' Java), 'this article introduces the use of BigDecimal of Java. , '2021-06-24 20 knife', 36 Java 54 reflection,' 2021-06-24 20 15 36 V 54 reflection, 0); INSERT INTO `t _ blog` reflection (5,1, 'knife',' reflection usage', 'this article introduces the use of reflection in blog'. , '2021-10-28 22-22-24-18-8,' 2021-10-28-22-22-24-24-18, 0) INSERT INTO `t _ blog` VALUES (6, 2, 'use of sky',' Vue-cli', 'Vue-cli is a scaffolding tool of Vue', 'Vue-cli can be used to create vue projects', '2021-02-23 1134 sky', 36 pieces,' 2021-02-25 14 sky', 33 36 pieces, 0) INSERT INTO `tblog` VALUES (7, 2, 'usage of sky',' Vuex', 'Vuex is a plug-in for vue to share variables', 'generally use vuex to share variables', '2021-03-28 23 VLV 37V37),' 2021-03-28 23 VLV 37VAS, 0); dependency

Pom.xml

4.0.0 com.example MyBatis-Plus_Multiple 0.0.1-SNAPSHOT jar MyBatis-Plus_Multiple Demo project for Spring Boot org.springframework.boot spring-boot-starter-parent 2.3.12.RELEASE UTF-8 UTF-8 1.8 org.springframework.boot spring- Boot-starter-web mysql mysql-connector-java runtime spring-boot-starter-test test com.baomidou mybatis-plus-boot-starter 3.5.1 org.projectlombok lombok com.github.xiaoymin knife4j-spring-boot-starter 3.0.3 org.apache.maven.plugins maven-compiler-plugin 8 8 configuration

Application.yml

Spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/mp?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false password: 222333 username: root # mybatis-plus configuration console print complete SQL statement with parameters mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

MyBatis-Plus paging plug-in configuration

Package com.example.demo.config; import com.baomidou.mybatisplus.annotation.DbType;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;@Configurationpublic class MyBatisPlusConfig {/ * paging plugin * / @ Bean public MybatisPlusInterceptor mybatisPlusInterceptor () {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor () Interceptor.addInnerInterceptor (new PaginationInnerInterceptor (DbType.MYSQL)); return interceptor;}} Code

VO

Package com.example.demo.business.blog.vo; import lombok.Data;import java.time.LocalDateTime;@Datapublic class BlogVO {private Long id; private Long userId; private String userName; / * title * / private String title; * abstract private String description; * content private String content; * creation time private LocalDateTime createTime; * modification time private LocalDateTime updateTime * nickname (this is the field of t_user) private String nickName;}

Mapper

Package com.example.demo.business.blog.mapper; import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.baomidou.mybatisplus.core.metadata.IPage;import com.example.demo.business.blog.entity.Blog;import com.example.demo.business.blog.vo.BlogVO;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;import java.util.List @ Repositorypublic interface BlogMapper extends BaseMapper {/ * * static query * / @ Select ("SELECT t_user.user_name" + "FROM t_blog, t_user" + "WHERE t_blog.id = # {id}" + "AND t_blog.user_id = t_user.id") String findUserNameByBlogId (@ Param ("id") Long id) * dynamic query @ Select ("SELECT *" + "${ew.customSqlSegment}") IPage findBlog (IPage page, @ Param ("ew") Wrapper wrapper);}

Controller

Package com.example.demo.business.blog.controller; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import com.example.demo.business.blog.mapper.BlogMapper;import com.example.demo.business.blog.vo.BlogVO;import io.swagger.annotations.Api;import io.swagger.annotations.ApiOperation;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.util.StringUtils Import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;@Api (tags = "Custom SQL") @ RestController@RequestMapping ("/ blog") public class BlogController {@ Autowired private BlogMapper blogMapper; @ ApiOperation ("static query") @ GetMapping ("staticQuery") public String staticQuery () {return blogMapper.findUserNameByBlogId (1L) } @ ApiOperation ("dynamic query") @ GetMapping ("dynamicQuery") public IPage dynamicQuery (Page page, String nickName, String title) {QueryWrapper queryWrapper = new QueryWrapper (); queryWrapper.like (StringUtils.hasText (nickName), "t_user.nick_name", nickName); queryWrapper.like (StringUtils.hasText (title), "t_blog.title", title); queryWrapper.eq ("t_blog.deleted_flag", 0) QueryWrapper.eq ("t_user.deleted_flag", 0); queryWrapper.apply ("t_blog.user_id = t_user.id"); return blogMapper.findBlog (page, queryWrapper);} Test

Visit the knife4j page: http://localhost:8080/doc.html

1. Static query

two。 Dynamic query 1. Non-transmission condition

Result: (all data can be found)

Back-end output

Creating a new SqlSession

SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@60bbb9ec] was not registered for synchronization because synchronization is not active

JDBC Connection [HikariProxyConnection@1853643659 wrapping com.mysql.cj.jdbc.ConnectionImpl@6a43d29c] will not be managed by Spring

= = > Preparing: SELECT COUNT (*) AS total FROM t_blog, t_user WHERE (t_blog.deleted_flag =? AND t_user.deleted_flag =? AND t_blog.user_id = t_user.id)

= > Parameters: 0 (Integer), 0 (Integer)