mybatis实现跨库多表查询

深藏阁楼爱情的钟 2022-05-14 07:05 575阅读 0赞

需求：查询用户名具体负责的项目的名称  
已有数据库表如下所示，二者通过项目ID关联

![Image 1][]

test数据库的user表

![Image 1][]

test1数据库的project表

需要实现的SQL查询

select user.id,user.name,projectName from test.user left join test1.project on user.`projectId`=project.id;

查询结果

![Image 1][]

查询结果

### 实现：核心思想，手动新建Bean与Mapper。 ###

1.自动生成代码model与mapper，包括test.user test1.project  
2.新建一个bean命名为UserPro，其属性为上述所需的3个字段

public class UserPro {
        private Integer id;
        private String name;
        private String projectname;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getProjectname() {
            return projectname;
        }
    
        public void setProjectname(String projectname) {
            this.projectname = projectname;
        }
    
    }

3.新建一个mapper命名为UserProMapper,返回新建的bean: UserPro

@Repository
    public interface UserProMapper {
    
        @Select("select user.id,user.name,projectName from test.user left join test1.project on user.`projectId`=project.id limit 1")
        UserPro findProName();
    }

4.基本已经实现核心的内容了，接下来就是用controller实现调用关系

@Controller
    public class testController {
    
        @Autowired
        private StuClassMapper stuClassMapper;
        @Autowired
        private UserMapper userMapper;
    
        @Autowired
        private UserProMapper userProMapper;
    
        @RequestMapping("/test")
        @ResponseBody
        public String  getStuClass(){
            User user=userMapper.findByUserId();
            System.out.println(user.getName());
            return user.getName();
        }
    
        @RequestMapping("/test2")
        @ResponseBody
        public String  getStuClassByJoin(){
    
            System.out.println(userMapper.findByUserIdJoin());
            return "userMapper.findByUserIdJoin()";
        }
    
        @RequestMapping("/test3")
        @ResponseBody
        public UserPro getProName(){
    
            UserPro userPro=userProMapper.findProName();
            return userPro;
        }
    
        @RequestMapping("/test4")
        @ResponseBody
        public String  findUserNameByUserIdJoin(){
            return userMapper.findUserNameByUserIdJoin();
        }
    
    }

5.启动项目，在浏览器输入：[http://localhost:8091/test3][http_localhost_8091_test3]

![Image 1][]

执行结果

大家也发现了，controller中有很多的路由匹配，这是模拟了其他若干场景。另外为了方便，Mapper返回的查询结果条数限制为1：

# test
    select * from test.user where id in(select userId from test1.stuClass) limit 1;
    # test2
    select count(*) from test.user left join test1.stuClass on user.id=stuClass.userId
    # test3 
    select user.id,user.name,projectName from test.user left join test1.project on user.`projectId`=project.id;
    # test4
    select stuClass.userId from test.user left join test1.stuClass on user.id=stuClass.userId limit 1
    # ？
    select * from test.user left join test1.stuClass on user.id=stuClass.userId;

源码链接：[https://gitee.com/neimenggudaxue/MultityTablesCurd.git][https_gitee.com_neimenggudaxue_MultityTablesCurd.git]

作者：迷糊银儿  
链接：https://www.jianshu.com/p/b171e6518e7f  
來源：简书  
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

[Image 1]: 
[http_localhost_8091_test3]: http://localhost:8091/test3
[https_gitee.com_neimenggudaxue_MultityTablesCurd.git]: https://gitee.com/neimenggudaxue/MultityTablesCurd.git