MyBatis-Plus多表联查的实现方法(动态查询和静态查询)

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 `t_user`

(

     `id`              BIGINT (0) NOT NULL AUTO_INCREMENT,

     `user_name`       VARCHAR (64) NOT NULL COMMENT '用户名（不能重复）' ,

     `nick_name`       VARCHAR (64) NOT NULL COMMENT '昵称（可以重复）' ,

     `email`           VARCHAR (64) COMMENT '邮箱' ,

     `create_time`  DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,

     `update_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' ,

     `deleted_flag` BIGINT (0) NOT NULL DEFAULT 0 COMMENT '0：未删除 其他：已删除' ,

     PRIMARY KEY (`id`) USING BTREE,

     UNIQUE KEY `index_user_name_deleted_flag`(`user_name`, `deleted_flag`),

     KEY `index_create_time`(`create_time`)

) ENGINE = InnoDB COMMENT = '用户' ;

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 '标题' ,

     `description`  VARCHAR (256) CHARACTER SET utf8mb4 NOT NULL COMMENT '摘要' ,

     `content`      LONGTEXT CHARACTER SET utf8mb4 NOT NULL COMMENT '内容' ,

     KEY `index_user_id`(`user_id`),

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '博客' ;

INSERT INTO `t_user` VALUES (1, 'knife' , '刀刃' , 'abc@qq测试数据' , '2021-01-23 09:33:36' , '2021-01-23 09:33:36' , 0);

INSERT INTO `t_user` VALUES (2, 'sky' , '天蓝' , '123@qq测试数据' , '2021-01-24 18:12:21' , '2021-01-24 18:12:21' , 0);

INSERT INTO `t_blog` VALUES (1, 1, 'knife' , 'Java中枚举的用法' ,

                              '本文介绍Java的枚举类的使用' ,

                              '2021-01-23 11:33:36' , '2021-01-23 11:33:36' , 0);

INSERT INTO `t_blog` VALUES (2, 1, 'knife' , 'Java中泛型的用法' ,

                              '本文介绍Java的泛型的使用。' ,

                              '2021-01-28 23:37:37' , '2021-01-28 23:37:37' , 0);

INSERT INTO `t_blog` VALUES (3, 1, 'knife' , 'Java的HashMap的原理' ,

                              '本文介绍Java的HashMap的原理。' ,

                              '2021-05-28 09:06:06' , '2021-05-28 09:06:06' , 0);

INSERT INTO `t_blog` VALUES (4, 1, 'knife' , 'Java中BigDecimal的用法' ,

                              '本文介绍Java的BigDecimal的使用。' ,

                              '2021-06-24 20:36:54' , '2021-06-24 20:36:54' , 0);

INSERT INTO `t_blog` VALUES (5, 1, 'knife' , 'Java中反射的用法' ,

                              '本文介绍Java的反射的使用。' ,

                              '2021-10-28 22:24:18' , '2021-10-28 22:24:18' , 0);

INSERT INTO `t_blog` VALUES (6, 2, 'sky' , 'Vue-cli的使用' ,

                              'Vue-cli是Vue的一个脚手架工具' ,

                              'Vue-cli可以用来创建vue项目' ,

                              '2021-02-23 11:34:36' , '2021-02-25 14:33:36' , 0);

INSERT INTO `t_blog` VALUES (7, 2, 'sky' , 'Vuex的用法' ,

                              'Vuex是vue用于共享变量的插件' ,

                              '一般使用vuex来共享变量' ,

                              '2021-03-28 23:37:37' , '2021-03-28 23:37:37' , 0);

<? xml version = "1.0" encoding = "UTF-8" ?>

< project xmlns = "http://maven.apache.org/POM/4.0.0" xmlns:xsi = "http://HdhCmsTestw3.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.example</ groupId >

     < artifactId >MyBatis-Plus_Multiple</ artifactId >

     < version >0.0.1-SNAPSHOT</ version >

     < packaging >jar</ packaging >

     < name >MyBatis-Plus_Multiple</ name >

     < description >Demo project for Spring Boot</ description >

     < parent >

         < groupId >org.springframework.boot</ groupId >

         < artifactId >spring-boot-starter-parent</ artifactId >

         < version >2.3.12.RELEASE</ version >

         < relativePath /> <!-- lookup parent from repository -->

     </ parent >

     < properties >

         < project.build.sourceEncoding >UTF-8</ project.build.sourceEncoding >

         < project.reporting.outputEncoding >UTF-8</ project.reporting.outputEncoding >

         < java.version >1.8</ java.version >

     </ properties >

     < dependencies >

         < dependency >

             < groupId >org.springframework.boot</ groupId >

             < artifactId >spring-boot-starter-web</ artifactId >

         </ dependency >

             < groupId >mysql</ groupId >

             < artifactId >mysql-connector-java</ artifactId >

             < scope >runtime</ scope >

             < artifactId >spring-boot-starter-test</ artifactId >

             < scope >test</ scope >

             < groupId >com.baomidou</ groupId >

             < artifactId >mybatis-plus-boot-starter</ artifactId >

             < version >3.5.1</ version >

             < groupId >org.projectlombok</ groupId >

             < artifactId >lombok</ artifactId >

             < groupId >com.github.xiaoymin</ groupId >

             < artifactId >knife4j-spring-boot-starter</ artifactId >

             < version >3.0.3</ version >

     </ dependencies >

     < build >

         < plugins >

             < plugin >

                 < groupId >org.apache.maven.plugins</ groupId >

                 < artifactId >maven-compiler-plugin</ artifactId >

                 <!-- 指定maven编译的jdk版本。若不指定,maven3默认用jdk 1.5 maven2默认用jdk1.3 -->

                 < configuration >

                     < source >8</ source >

                     < target >8</ target >

                 </ configuration >

             </ plugin >

         </ plugins >

     </ build >

</ project >

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配置控制台打印完整带参数SQL语句

mybatis-plus:

   configuration:

     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

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;

@Configuration

public class MyBatisPlusConfig {

     /**

      * 分页插件

      */

     @Bean

     public MybatisPlusInterceptor mybatisPlusInterceptor() {

         MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

         interceptor.addInnerInterceptor( new PaginationInnerInterceptor(DbType.MYSQL));

         return interceptor;

     }

}

package com.example.demo.business.blog.vo;

import lombok.Data;

import java.time.LocalDateTime;

@Data

public class BlogVO {

     private Long id;

     private Long userId;

     private String userName;

     /**

      * 标题

      */

     private String title;

      * 摘要

     private String description;

      * 内容

     private String content;

      * 创建时间

     private LocalDateTime createTime;

      * 修改时间

     private LocalDateTime updateTime;

      * 昵称（这个是t_user的字段）

     private String nickName;

}

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;

@Repository

public interface BlogMapper extends BaseMapper<Blog> {

     /**

      * 静态查询

      */

     @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);

      * 动态查询

     @Select ( "SELECT * " +

             " ${ew.customSqlSegment} " )

     IPage<BlogVO> findBlog(IPage<BlogVO> page, @Param ( "ew" ) Wrapper wrapper);

}

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 = "自定义SQL" )

@RestController

@RequestMapping ( "/blog" )

public class BlogController {

     @Autowired

     private BlogMapper blogMapper;

     @ApiOperation ( "静态查询" )

     @GetMapping ( "staticQuery" )

     public String staticQuery() {

         return blogMapper.findUserNameByBlogId(1L);

     }

     @ApiOperation ( "动态查询" )

     @GetMapping ( "dynamicQuery" )

     public IPage<BlogVO> dynamicQuery(Page<BlogVO> page, String nickName, String title) {

         QueryWrapper<BlogVO> 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);

}