设为首页收藏本站
开启辅助访问

SpringBoot+MyBatis多表联合查询

 您所在的位置：网站首页 › vue多表查询 › SpringBoot+MyBatis多表联合查询

SpringBoot+MyBatis多表联合查询

2024-07-10 13:08| 来源: 网络整理| 查看: 265

SpringBoot+MyBatis多表联合查询 写在前面

联合查询在实际工作中用的并不多，因为很多表的数据比较大，或者说未来比较大的表，都要谨慎使用联合查询

数据准备 建表语句 create table m_user ( id bigint not null primary key, username varchar(64) null, avatar varchar(255) null, email varchar(64) null, password varchar(64) null, status int(5) not null, created datetime null, last_login datetime null ); create index UK_USERNAME on m_user (username); create table m_blog ( id bigint not null primary key, user_id bigint not null, title varchar(255) not null, description varchar(255) not null, content longtext null, created datetime not null on update CURRENT_TIMESTAMP, status tinyint null ) charset = utf8mb4; 插入数据

插入数据(数据很多都是没用的，那都是我平时自己测试用的，但是不影响使用

INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (1, '文章一', 'https://image-1300566513.cos.ap-guangzhou.myqcloud.com/upload/images/5a9f48118166308daba8b6da7e466aab.jpg', '[email protected]', '96e79218965eb72c92a549dd5a330112', 0, '2021-06-02 17:52:01', null); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (2, '张liu', 'new', '[email protected]', '123456', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (3, '王wu', 'old', '[email protected]', '2222222', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (4, 'si', 'sisi', 'sisisi', 'sisisisi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (5, 'wu', 'wuwu', 'wuwuwu', 'wuwuwuwuwu', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (6, 'liu', 'liuliu', 'liuliuliu', 'liuliuliuliu', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (7, 'qi', 'qiqi', 'qiqiqi', 'qiqiqiqi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (8, 'ba', 'baba', 'bababa', 'babababa', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (9, 'jiu', 'jiujiu', 'jiujiujiu', 'jiujiujiujiu', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (10, 'shi', 'shishi', 'shishishi', 'shishishishi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (11, 'shiyi', 'shiyiyi', 'shiyiyiyi', 'shiyiyiyiyi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (12, 'shier', 'shierer', 'shiererer', 'shierererer', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (13, 'shisan', 'shisansan', 'shisansansan', 'shisansansansan', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (14, 'shisi', 'shisisi', 'shisisisi', 'shisisisisi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (15, 'shiwu', 'shiwuwu', 'shiwuwuwu', 'shiwuwuwuwu', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (16, 'shiliu', 'shiliuyliu', 'shiliuliuliu', 'shiliuliuliuliu', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (17, 'shiqi', 'shiqiqi', 'shiqiqiqi', 'shiqiqiqiqi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (18, 'shiba', 'shibaba', 'shibabababa', 'shibababababa', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (19, 'shijiu', 'shijiujiu', 'shijiujiujiu', 'shijiujiujiujiu', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (21, 'ershi', 'ershishi', 'ershishishi', 'ershishishishi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_user (id, username, avatar, email, password, status, created, last_login) VALUES (22, 'ershiyi', 'ershiyiyi', 'ershiyiyiyi', 'ershiyiyiyiyi', 0, '2021-06-02 17:52:01', '2021-06-02 17:52:01'); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (1, 1, '文章一', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-06-17 17:36:11', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (2, 1, '文章一2', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-06-17 17:36:11', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (3, 2, '文章一3', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-06-18 11:38:56', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (4, 3, '文章一4', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-06-18 11:38:56', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (5, 2, '文章一5', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-06-18 11:38:56', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (6, 4, '文章一6', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-06-18 11:38:56', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (7, 4, '文章一7', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-09-29 17:45:29', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (8, 5, '文章一8', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-11-14 14:12:37', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (9, 5, '文章一8', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-11-14 14:12:37', 0); INSERT INTO testgroup.m_blog (id, user_id, title, description, content, created, status) VALUES (11, 6, '文章一8', '第一篇文章', '第一篇文章第一篇文章第一篇文章', '2021-11-14 14:12:37', 0); entity层 package com.newcrud.entity; import lombok.Data; @Data public class Blog { private Integer id; private Integer user_id; private String title; private String description; private String content; private String created; private Integer status; } package com.newcrud.entity; import lombok.Data; @Data public class User { private Integer id; private String username; private String avatar; private String email; private String password; private Integer status; private String created; private String last_login; } 多表联合查询的思路

首先我们来举一个例子,就像上面两张表

1、作者和多条博客为一对多的关系，也就是一条m_user对应了多条m_blog的数据

2、一条博客和作者为一对一的关系，也就是一条m_blog对应了一条m_user的数据

对于一对一，我们可以在m_blog的entity层加入一个User实例，但是对于一对多，我们就需要在m_user的entity层加入一个List，里面的内容为User。然后我们再去编写mybatis的xml文件，对于一对一，我们在xml文件中需要用到一个关键词为：association，意思为关联，对于一对多，我们在xml文件中需要用到一个关键词为：collection，意思为集合。

一对一

一篇博客对应一个作者

entity

首先，我们在m_bloh的entity里加入User

package com.newcrud.entity; import lombok.Data; @Data public class Blog { private Integer id; private Integer user_id; private String title; private String description; private String content; private String created; private Integer status; private User user; } mapper package com.newcrud.mapper; import com.newcrud.entity.Blog; import org.apache.ibatis.annotations.Mapper; import java.util.List; @Mapper public interface BlogMapper { Blog getBlogById(Integer id); } service package com.newcrud.service; import com.newcrud.entity.Blog; import java.util.List; public interface BlogService { Blog getBlogById(Integer id); } impl package com.newcrud.service.impl; import com.newcrud.entity.Blog; import com.newcrud.mapper.BlogMapper; import com.newcrud.service.BlogService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class BlogServiceImpl implements BlogService { @Autowired BlogMapper blogMapper; @Override public Blog getBlogById (Integer id){ return blogMapper.getBlogById(id); } } mybatis的xml文件 DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> select a.* from m_user a,m_blog b b.user_id=a.id and b.id= #{id} 测试类 package com.newcrud.service.impl; import com.newcrud.entity.Blog; import com.newcrud.entity.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.testng.AbstractTransactionalTestNGSpringContextTests; import org.testng.annotations.Test; @SpringBootTest public class BlogServiceImplTest extends AbstractTransactionalTestNGSpringContextTests { @Autowired BlogServiceImpl blogService; @Test public void getBlogByIdTest(){ Blog blog= blogService.getBlogById(1); User user=blog.getUser(); System.out.println(user); } }

结果

User(id=1, username=文章一, avatar=https://image-1300566513.cos.ap-guangzhou.myqcloud.com/upload/images/5a9f48118166308daba8b6da7e466aab.jpg, [email protected], password=96e79218965eb72c92a549dd5a330112, status=0, created=2021-06-02 17:52:01, last_login=null) 如果xml文件不这样写呢

我们把m_user从xml文件中摘出来

select * from m_blog id= #{id}

同样的测试类，结果

User(id=1, username=null, avatar=null, email=null, password=null, status=0, created=2021-06-17 17:36:11, last_login=null)

emmmm,经过分析得知，这个结果是将blog表里和user的表里有相同的字段，直接将blog的字段结果填充到了user的数据里，为什么这样谁能给我解释一下。。。

一对多 entity

关键：private List blogs; 是一个List

package com.newcrud.entity; import lombok.Data; import java.util.List; @Data public class User { private Integer id; private String username; private String avatar; private String email; private String password; private Integer status; private String created; private String last_login; /** * 由于一个作者对应了多篇博客，所以这里用List来加入Blog * */ private List blogs; }

最好呢，将Blog的entity还原成下面这个样子，我们暂时先不还原，看看结果如何。

package com.newcrud.entity; import lombok.Data; @Data public class Blog { private Integer id; private Integer user_id; private String title; private String description; private String content; private String created; private Integer status; } mapper package com.newcrud.mapper; import com.newcrud.entity.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; /** * 这里有两种方法，一种是像我们一样使用@Mapper，一种是在CrudApplication上面新增了@MapperScan备注。直接扫描了整个包 * https://www.cnblogs.com/JackpotHan/p/10286496.html * 不过不知道为啥，不加上这个好像是不行，没办法auto，稍微有那么点尴尬 * **/ @Mapper public interface UserMapper { /** * 一对多联合查询 * */ User getUserAndBlog(Integer id); } service package com.newcrud.service; import com.newcrud.entity.User; import com.newcrud.outInParam.PageRequest; import com.newcrud.outInParam.PageResult; import java.util.List; public interface UserService { /** * 一对多联合查询 * */ User getUserAndBlog(Integer id); } impl package com.newcrud.service.impl; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import com.newcrud.entity.User; import com.newcrud.mapper.UserMapper; import com.newcrud.outInParam.PageRequest; import com.newcrud.outInParam.PageResult; import com.newcrud.service.UserService; import com.newcrud.utils.PageUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; /** * 一对多联合查询 * */ @Override public User getUserAndBlog(Integer id){ return userMapper.getUserAndBlog(id); } xml DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> select a.id uid,a.username,a.avatar,a.email,a.password,a.status ustatus,a.created ucreated,a.last_login,b.id bid,b.user_id,b.title,b.description,b.content,b.created bcreated,b.status bstatus from m_user a , m_blog b where a.id=b.user_id and a.id=#{id} 测试类 package com.newcrud.service.impl; import com.newcrud.entity.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.testng.AbstractTestNGSpringContextTests; import org.testng.annotations.Test; import java.util.List; import static org.testng.Assert.*; @SpringBootTest public class UserServiceImplTest extends AbstractTestNGSpringContextTests { @Autowired UserServiceImpl userService; @Test public void testGetUserById() { User users = userService.getUserAndBlog(1); System.out.println(users); }

结果

User(id=1, username=文章一, avatar=https://image-1300566513.cos.ap-guangzhou.myqcloud.com/upload/images/5a9f48118166308daba8b6da7e466aab.jpg, [email protected], password=96e79218965eb72c92a549dd5a330112, status=0, created=2021-06-02 17:52:01, last_login=null, blogs=[Blog(id=1, user_id=1, title=文章一, description=第一篇文章, content=第一篇文章第一篇文章第一篇文章, created=2021-06-17 17:36:11, status=0, user=null), Blog(id=2, user_id=1, title=文章一2, description=第一篇文章, content=第一篇文章第一篇文章第一篇文章, created=2021-06-17 17:36:11, status=0, user=null)])

有没有注意到blog里有一段user=null，我们把entity层的Blog还原就行了

优化

一个小小的优化，一是我们并不需要返回那么多字段信息，二是实际工作中这个xml里的sql，其实更多的是使用union的方式，而不是像我们上面的那种

entity package com.newcrud.entity; import lombok.Data; import java.util.List; @Data public class User { private Integer id; private String username; private String avatar; private String email; private String password; private Integer status; private String created; private String last_login; /** * 由于一个作者对应了多篇博客，所以这里用List来加入Blog * */ private List blogs; } mapper package com.newcrud.mapper; import com.newcrud.entity.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; /** * 这里有两种方法，一种是像我们一样使用@Mapper，一种是在CrudApplication上面新增了@MapperScan备注。直接扫描了整个包 * https://www.cnblogs.com/JackpotHan/p/10286496.html * 不过不知道为啥，不加上这个好像是不行，没办法auto，稍微有那么点尴尬 * **/ @Mapper public interface UserMapper { /** * 一对多联合查询-union方式 * */ User getAllUserAndBlog(Integer id); } service package com.newcrud.service; import com.newcrud.entity.User; import com.newcrud.outInParam.PageRequest; import com.newcrud.outInParam.PageResult; import java.util.List; public interface UserService { /** * 一对多联合查询-union方式 * */ User getAllUserAndBlog(Integer id); } impl package com.newcrud.service.impl; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import com.newcrud.entity.User; import com.newcrud.mapper.UserMapper; import com.newcrud.outInParam.PageRequest; import com.newcrud.outInParam.PageResult; import com.newcrud.service.UserService; import com.newcrud.utils.PageUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; /** * 一对多联合查询-union方式 * */ @Override public User getAllUserAndBlog(Integer id){ return userMapper.getAllUserAndBlog(id); } } mybatis的xml文件 DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> select a.id uid,a.username,a.email,b.id bid,b.title,b.description from m_user a left outer join m_blog b on a.id = b.user_id where a.id=#{id} 测试类 package com.newcrud.service.impl; import com.newcrud.entity.Blog; import com.newcrud.entity.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.testng.AbstractTestNGSpringContextTests; import org.testng.annotations.Test; import javax.jws.soap.SOAPBinding; import java.util.List; import static org.testng.Assert.*; @SpringBootTest public class UserServiceImplTest extends AbstractTestNGSpringContextTests { @Autowired UserServiceImpl userService; @Test public void testgetAllUserAndBlog(){ User user = userService.getAllUserAndBlog(1); System.out.println(user); } }

结果

User(id=1, username=文章一, avatar=null, [email protected], password=null, status=null, created=null, last_login=null, blogs=[Blog(id=1, user_id=null, title=文章一, description=第一篇文章, content=null, created=null, status=null), Blog(id=2, user_id=null, title=文章一2, description=第一篇文章, content=null, created=null, status=null)])


【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3