MyBatis-Plus--多表联查(动态查询)--方法/教程/实例

左手的ㄟ右手 2023-09-30 10:47 25阅读 0赞

原文网址：[MyBatis-Plus--多表联查(动态查询)--方法/教程/实例\_IT利刃出鞘的博客-CSDN博客][MyBatis-Plus--_--_IT_-CSDN]

**技术星球**

欢迎来到IT技术星球，网站是：[learn.skyofit.com][]（或者百度直接搜：自学精灵）。内容有：**Java真实面试题**、**Java设计模式实战**、Shiro项目实战、**Idea和Navicat的“魔法”**教程、**SpringBoot进阶**、架构与微服务设计、**高并发实战**、Java入门实战、**网站防御技术**、JavaWeb入门项目等。网站的定位：超高的质量、超高的真实性、超高的实用性。欢迎加入~

## 简介 ##

本文用示例介绍使用MyBatis-Plus进行多表查询的方法，包括静态查询和动态查询。

## 测试 ##

访问knife4j页面：[http://localhost:8080/doc.html][http_localhost_8080_doc.html]

![91395375d427445e8dc307389f3df3b7.png][]

### 1.静态查询 ###

![019e3def60354e9b93cd841b99363d01.png][]

### 2.动态查询 ###

#### **1.不传条件** ####

![53619ace35ae47459769f826d54e94c0.png][]

结果：（可以查到所有数据）

![b4aadbbf8a334b318e468a361d924d7e.png][]

后端输出

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)
    <==    Columns: total
    <==        Row: 7
    <==      Total: 1
    ==>  Preparing: SELECT * FROM t_blog, t_user WHERE (t_blog.deleted_flag = ? AND t_user.deleted_flag = ? AND t_blog.user_id = t_user.id) LIMIT ?
    ==> Parameters: 0(Integer), 0(Integer), 10(Long)
    <==    Columns: id, user_id, user_name, title, description, content, create_time, update_time, deleted_flag, id, user_name, nick_name, email, create_time, update_time, deleted_flag
    <==        Row: 1, 1, knife, Java中枚举的用法, 本文介绍Java的枚举类的使用, <<BLOB>>, 2021-01-23 11:33:36, 2021-01-23 11:33:36, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 2, 1, knife, Java中泛型的用法, 本文介绍Java的泛型的使用。, <<BLOB>>, 2021-01-28 23:37:37, 2021-01-28 23:37:37, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 3, 1, knife, Java的HashMap的原理, 本文介绍Java的HashMap的原理。, <<BLOB>>, 2021-05-28 09:06:06, 2021-05-28 09:06:06, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 4, 1, knife, Java中BigDecimal的用法, 本文介绍Java的BigDecimal的使用。, <<BLOB>>, 2021-06-24 20:36:54, 2021-06-24 20:36:54, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 5, 1, knife, Java中反射的用法, 本文介绍Java的反射的使用。, <<BLOB>>, 2021-10-28 22:24:18, 2021-10-28 22:24:18, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 6, 2, sky, Vue-cli的使用, Vue-cli是Vue的一个脚手架工具, <<BLOB>>, 2021-02-23 11:34:36, 2021-02-25 14:33:36, 0, 2, sky, 天蓝, 123@qq.com, 2021-01-24 18:12:21, 2021-01-24 18:12:21, 0
    <==        Row: 7, 2, sky, Vuex的用法, Vuex是vue用于共享变量的插件, <<BLOB>>, 2021-03-28 23:37:37, 2021-03-28 23:37:37, 0, 2, sky, 天蓝, 123@qq.com, 2021-01-24 18:12:21, 2021-01-24 18:12:21, 0
    <==      Total: 7
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@60bbb9ec]

#### 2.传条件 ####

只传：nickName：刀

![32c945b893114fbe967b3ab567791d71.png][]

结果

![b0bea72f762244d29e9a9164fd0d86d9.png][]后端结果

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@30026aab] was not registered for synchronization because synchronization is not active
    JDBC Connection [HikariProxyConnection@2127441980 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_user.nick_name LIKE ? AND t_blog.deleted_flag = ? AND t_user.deleted_flag = ? AND t_blog.user_id = t_user.id)
    ==> Parameters: %刀%(String), 0(Integer), 0(Integer)
    <==    Columns: total
    <==        Row: 5
    <==      Total: 1
    ==>  Preparing: SELECT * FROM t_blog, t_user WHERE (t_user.nick_name LIKE ? AND t_blog.deleted_flag = ? AND t_user.deleted_flag = ? AND t_blog.user_id = t_user.id) LIMIT ?
    ==> Parameters: %刀%(String), 0(Integer), 0(Integer), 10(Long)
    <==    Columns: id, user_id, user_name, title, description, content, create_time, update_time, deleted_flag, id, user_name, nick_name, email, create_time, update_time, deleted_flag
    <==        Row: 1, 1, knife, Java中枚举的用法, 本文介绍Java的枚举类的使用, <<BLOB>>, 2021-01-23 11:33:36, 2021-01-23 11:33:36, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 2, 1, knife, Java中泛型的用法, 本文介绍Java的泛型的使用。, <<BLOB>>, 2021-01-28 23:37:37, 2021-01-28 23:37:37, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 3, 1, knife, Java的HashMap的原理, 本文介绍Java的HashMap的原理。, <<BLOB>>, 2021-05-28 09:06:06, 2021-05-28 09:06:06, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 4, 1, knife, Java中BigDecimal的用法, 本文介绍Java的BigDecimal的使用。, <<BLOB>>, 2021-06-24 20:36:54, 2021-06-24 20:36:54, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==        Row: 5, 1, knife, Java中反射的用法, 本文介绍Java的反射的使用。, <<BLOB>>, 2021-10-28 22:24:18, 2021-10-28 22:24:18, 0, 1, knife, 刀刃, abc@qq.com, 2021-01-23 09:33:36, 2021-01-23 09:33:36, 0
    <==      Total: 5
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@30026aab]

### 3.动态查询 （join） ###

略

## 代码 ##

**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 = "自定义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);
        }
    
        @ApiOperation("动态查询(使用join)")
        @GetMapping("dynamicQueryByJoin")
        public IPage<BlogVO> dynamicQueryByJoin(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);
            return blogMapper.findBlogByJoin(page, queryWrapper);
        }
    }

上边是文章的部分内容，为便于维护，全文已永久转移到此网址：[MyBatis-Plus-多表联查(动态查询)-实例 - 自学精灵][MyBatis-Plus-_-_ -]

[MyBatis-Plus--_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/123681896
[learn.skyofit.com]: http://learn.skyofit.com/
[http_localhost_8080_doc.html]: http://localhost:8080/doc.html
[91395375d427445e8dc307389f3df3b7.png]: https://img-blog.csdnimg.cn/91395375d427445e8dc307389f3df3b7.png
[019e3def60354e9b93cd841b99363d01.png]: https://img-blog.csdnimg.cn/019e3def60354e9b93cd841b99363d01.png
[53619ace35ae47459769f826d54e94c0.png]: https://img-blog.csdnimg.cn/53619ace35ae47459769f826d54e94c0.png
[b4aadbbf8a334b318e468a361d924d7e.png]: https://img-blog.csdnimg.cn/b4aadbbf8a334b318e468a361d924d7e.png
[32c945b893114fbe967b3ab567791d71.png]: https://img-blog.csdnimg.cn/32c945b893114fbe967b3ab567791d71.png
[b0bea72f762244d29e9a9164fd0d86d9.png]: https://img-blog.csdnimg.cn/b0bea72f762244d29e9a9164fd0d86d9.png
[MyBatis-Plus-_-_ -]: https://learn.skyofit.com/archives/1872