【Mybatis】使用结果嵌套方式完成一对多映射

【一对多的重要意义】

以公司与雇员为例，如果不用一对多，至少需要访问DB两次，一次按取公司的相关属性，第二次取公司下的雇员们，而采用一对多后，你会发现一次DB访问就够了。

例程下载：https://files.cnblogs.com/files/heyang78/MybatisCli220527.rar?t=1653593808

【目的】

完成一个公司Company包含多个雇员Emp的一对多映射。

【窍门】

理清结果集与实体类的映射关系，分清哪些会分类汇总，哪些会进行组内聚合，进而理解MyBatis在背后帮了什么忙。 

【建表充值部分】

create table company(
    id number(10),
    name nvarchar2(20),
    primary key(id)
);

insert into company(id,name) values(1,'google');
insert into company(id,name) values(2,'microsoft');

create table emp(
    id number(10),
    cid number(10),
    name nvarchar2(20),
    primary key(id)
);

insert into emp(id,cid,name) values(1,1,'Andy');
insert into emp(id,cid,name) values(2,1,'Bill');
insert into emp(id,cid,name) values(3,2,'Cindy');
insert into emp(id,cid,name) values(4,2,'Douglas');

【实体类部分】

Company类：

package com.hy.entity;

import java.util.List;

public class Company {
    private long id;
    private String name;
    private List<Emp> emps;

    public String toString(){
        StringBuilder sb=new StringBuilder();

        sb.append("Company id="+id);
        sb.append(" name="+name);

        for(Emp emp:emps){
            sb.append(" emp="+emp.toString());
        }

        return sb.toString();
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }
}

Emp类：

package com.hy.entity;

public class Emp {
    private long id;
    private String name;

    public String toString(){
        return String.format("Emp id=%d name=%s",id,name);
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

【Mapper接口】

package com.hy.dao;

import com.hy.entity.Company;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface CompanyMapper {
    Company fetchOne(long id);
}

【Mapper.xml】

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hy.dao.CompanyMapper">
    <select id="fetchOne" resultMap="map1">
        select c.id as cid,c.name as cname,e.id as eid,e.name as ename
        from company c,emp e
        where c.id=e.cid
        and c.id=#{id}
    </select>

    <resultMap id="map1" type="com.hy.entity.Company">
        <result column="cid" property="id"/>
        <result column="cname" property="name"/>

        <collection property="emps" ofType="com.hy.entity.Emp">
            <result column="eid" property="id"/>
            <result column="ename" property="name"/>
        </collection>
    </resultMap>
</mapper>

resultMap顾名思义是结果集的映射，当id=1时SQL语句是：

select c.id as cid,c.name as cname,e.id as eid,e.name as ename
from company c,emp e
where c.id=e.cid
and c.id=1

而出来的结果集是：

SQL> select c.id as cid,c.name as cname,e.id as eid,e.name as ename
  2  from company c,emp e
  3  where c.id=e.cid
  4  and c.id=1;

       CID CNAME                       EID ENAME
---------- -------------------- ---------- --------------------
         1 google                        1 Andy
         1 google                        2 Bill

对照一下Map1里写的，cid给Company的id，cname给Company的name，EID给Emp的id，ename给Emp的name。

对于这样两行的结果集怎么变成一个Company的实例的呢？

我的理解是Mybatis背后 对于同等值就相当于进行group by分类汇总，而emps部分的处理相当于做了listagg组内聚合。

【启动程序】

package com.hy;

import com.hy.dao.CompanyMapper;
import com.hy.entity.Company;
import com.hy.entity.Emp;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.io.InputStream;
import java.util.List;

@SpringBootApplication
public class MyApplication  implements CommandLineRunner {
    public static void main(String[] args) {
        SpringApplication.run(MyApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(inputStream);

        try (SqlSession session = sqlSessionFactory.openSession()) {
            CompanyMapper mapper = session.getMapper(CompanyMapper.class);

            Company google= mapper.fetchOne(1);
            System.out.println(google);

            Company microsoft= mapper.fetchOne(2);
            System.out.println(microsoft);
        }
    }
}

【执行结果】

Company id=1 name=google emp=Emp id=1 name=Andy emp=Emp id=2 name=Bill
Company id=2 name=microsoft emp=Emp id=3 name=Cindy emp=Emp id=4 name=Douglas

【参考文档】

https://blog.csdn.net/qq_45173404/article/details/107672660

END