美文网首页java基础学习
mybatis实现跨库多表查询

mybatis实现跨库多表查询

作者: 迷糊银儿 | 来源:发表于2018-06-07 10:18 被阅读2296次

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


    test数据库的user表
    test1数据库的project表

    需要实现的SQL查询

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

    实现:核心思想,手动新建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

    执行结果
    大家也发现了,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

    相关文章

      网友评论

        本文标题:mybatis实现跨库多表查询

        本文链接:https://www.haomeiwen.com/subject/lvdisftx.html