MyBatis 如何进行 1对1 和 1对多的关联查询？

灰太狼 2022-12-14 13:37 156阅读 0赞

*  在 Mapper xml 中的标签里使用可以完成 1对1 关联查询
 *  在 Mapper xml 中的标签里使用可以完成 1对多 关联查询

//sql
    create table user (
    id int primary key,
    name varchar(400)
    );
    insert user info VALUES(1, 'ConstXiong1');
    
    create table info (
    user_id int primary key,
    name varchar(400)
    );
    insert into info VALUES(1, '大熊');
    
    create table article (
    user_id int,
    title varchar(400)
    );
    insert into article VALUES(1, '文章1');
    insert into article VALUES(1, '文章2');
    
    
    
    
    //Mapper xml
    
    < select id="selectUserWithInfo" resultMap="UserWithInfo">
        select user.id, user.name, info.user_id, info.name as info_name from user,info where user.id = info.user_id
    < /select>
    
    
    < select id="selectUserWithArticles" resultMap="UserWithArticles">
        select user.id, user.name, article.user_id, article.title from user,article where user.id = article.user_id
    < /select>
    
    < resultMap id="UserWithInfo" type="constxiong.po.User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        
        <association property="info" javaType="constxiong.po.Info">
            <id property="userId" column="user_id"/>
            <result property="name" column="info_name"/>
        </association>
    < /resultMap>
    
    < resultMap id="UserWithArticles" type="constxiong.po.User">
        < id property="id" column="id"/>
        < result property="name" column="name"/>
        
        < collection property="articles" ofType="constxiong.po.Article">
            < result property="userId" column="user_id"/>
            < result property="title" column="title"/>
        < /collection>
    < /resultMap>
    
    //User.java
    /**
     * 用户表模型
     */
    public class User {
        private Integer id;
    
        private String name;
    
        private String mc;
    
        private Info info;
    
        private List<Article> articles;
    
        public User() {
        }
    
        public User(Integer id, String name) {
            this.id = id;
            this.name = name;
        }
    
        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 getMc() {
            return mc;
        }
    
        public void setMc(String mc) {
            this.mc = mc;
        }
    
        public Info getInfo() {
            return info;
        }
    
        public void setInfo(Info info) {
            this.info = info;
        }
    
        public List<Article> getArticles() {
            return articles;
        }
    
        public void setArticles(List<Article> articles) {
            this.articles = articles;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", mc='" + mc + '\'' +
                    ", info=" + info +
                    ", articles=" + articles +
                    '}';
        }
    }
    
    
    //测试代码
    System.out.println("------ selectUserWithInfo ------");
    user = userMapper.selectUserWithInfo();
    System.out.println(user);
    
    System.out.println("------ selectUserWithArticles ------");
    user = userMapper.selectUserWithArticles();
    System.out.println(user);
    
    //打印
    ------ selectUserWithInfo ------
    User{id=1, name='ConstXiong1', mc='null', info=Info{userId=1, name=大熊}, articles=null}
    ------ selectUserWithArticles ------
    User{id=1, name='ConstXiong1', mc='null', info=null, articles=[Article{userId=1, title='文章1'}, Article{userId=1, title='文章2'}]}

完整 Demo：

[https://javanav.com/val/a9fe4555c1614b40b0da07afeabf2a66.html][https_javanav.com_val_a9fe4555c1614b40b0da07afeabf2a66.html]

--------------------

### 【Java面试题与答案】整理推荐 ###

*  [基础与语法][Link 1]
 *  [集合][Link 2]
 *  [网络编程][Link 3]
 *  [并发编程][Link 4]
 *  [Web][]
 *  [安全][Link 5]
 *  [设计模式][Link 6]
 *  [框架][Link 7]
 *  [算法与数据结构][Link 8]
 *  [异常][Link 9]
 *  [文件解析与生成][Link 10]
 *  [Linux][]
 *  [MySQL][]
 *  [Oracle][]
 *  [Redis][]
 *  [Dubbo][]

[https_javanav.com_val_a9fe4555c1614b40b0da07afeabf2a66.html]: https://javanav.com/val/a9fe4555c1614b40b0da07afeabf2a66.html
[Link 1]: https://www.javanav.com/interview/93b0069472fd479393006c0e73043fc4.html
[Link 2]: https://www.javanav.com/interview/4147d95e693945e7a50345fd33ec817e.html
[Link 3]: https://www.javanav.com/interview/68a57ffc78de4133bb890bdd502ac418.html
[Link 4]: https://www.javanav.com/interview/36a24fa3258e45c89873f474ce914912.html
[Web]: https://www.javanav.com/interview/4e27cb63adb440c6825a159d35cf4010.html
[Link 5]: https://www.javanav.com/interview/03affb2ce3f04fda8853f714e5764906.html
[Link 6]: https://www.javanav.com/interview/07a32a9ff17a4006a53550ce658cafed.html
[Link 7]: https://www.javanav.com/interview/5c466456e9b54ab0a749b5948e51f1d3.html
[Link 8]: https://www.javanav.com/interview/943f917041ee4f05add24539f5b861b2.html
[Link 9]: https://www.javanav.com/interview/d3f058cfc05d4c6dadd821b622e84d36.html
[Link 10]: https://www.javanav.com/interview/67a50996c28f418e83b109ea42fc3eff.html
[Linux]: https://www.javanav.com/interview/00cccb81729540d1923bd0a0ef49533a.html
[MySQL]: https://www.javanav.com/interview/d311e78502a845f1a4bb307bd6aec2b3.html
[Oracle]: https://www.javanav.com/interview/5f29e9677edf4a16be3dce30d257c9ab.html
[Redis]: https://www.javanav.com/interview/066c62b3af4d4827b2243a822c3084a9.html
[Dubbo]: https://www.javanav.com/interview/9a3e7a7740324757abaab953f7ebc455.html