Mybatis 一对多关联关系

主要内容是
collection标签：用来处理一对多关联关系标签
property：封装关系属性名
javaType：关系属性类型
ofType：用来书写关系属性类型中泛型的类型

创建员工表 部门表

#部门表
create table t_dept(
	id int(6) PRIMARY key auto_increment,
	name VARCHAR(40)
);
#员工表
CREATE TABLE t_emp(
	id int(6) primary key auto_increment,
	ame VARCHAR(40),
	age int(3),
	bir TIMESTAMP,
	deptid int(6) REFERENCES t_dept(id)
);

员工和部门对应实体类

public class Emp {
    private Integer id;
    private String name;
    private Integer age;
    private Date bir;
    private Integer deptid;
    private Dept dept;
//set() get()   toString()
}
public class Dept {
    private Integer id;
    private String name;
    private List<Emp> emps;
	//set() get() toString()
}

1. 查询所有部门并查询出每个部门的员工信息

public interface DeptDAO {
    //查询所有部门并查询出每个部门的员工信息
    List<Dept> queryAll();
}

DeptDAO.xml

<mapper namespace="com.xxx.dao.DeptDAO">
    <resultMap id="deptMap" type="com.xxx.entity.Dept">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <!--封装员工信息
            collection:用来处理一对多关联关系标签
            property：封装关系属性名
            javaType：关系属性类型
            ofType：用来书写关系属性类型中泛型的类型
        -->
        <collection property="emps" javaType="java.util.List" ofType="com.xxx.entity.Emp">
            <id column="eid" property="id"/>
            <result column="ename" property="name"/>
            <result column="age" property="age"/>
            <result column="bir" property="bir"/>
            <result column="deptid" property="deptid"/>
        </collection>
    </resultMap>
    <!--查詢所有-->
    <select id="queryAll" resultMap="deptMap">
        SELECT
            t.id,
            t.name,
            e.id eid,
            e.name ename,
            e.age,
            e.bir,
            e.deptid
        FROM
          t_dept t
        LEFT JOIN  t_emp e ON t.id = e.deptid
    </select>
</mapper>

测试

public class TestDept {
    private InputStream in;
    private SqlSession session;
    private DeptDAO deptDAO;

    @Before
    public void init() throws IOException {
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2创建工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        session = factory.openSession();
        deptDAO = session.getMapper(DeptDAO.class);
    }
    @After
    public void destory() throws IOException {
        //提交事務
        session.commit();
        session.close();
        in.close();
    }

   @Test
    public void TestQueryAll(){
       deptDAO.queryAll().forEach(dept -> {
           System.out.println("部门信息"+dept);
           dept.getEmps().forEach(emp->{
               System.out.println("员工信息"+emp);
           });
           System.out.println("-----------------");
       });
    }
}

输出结果为：

部门信息Dept{id=1, name='研发部'}
员工信息Emp{id=1, name='小张1', age=34, bir=Thu Aug 22 18:33:14 CST 2019, deptid=1}
员工信息Emp{id=3, name='小丽1', age=45, bir=Fri Feb 01 18:34:09 CST 2019, deptid=1}
-----------------
部门信息Dept{id=2, name='教学部'}
员工信息Emp{id=2, name='小王2', age=23, bir=Tue Feb 28 18:33:40 CST 2017, deptid=2}
员工信息Emp{id=4, name='小李2', age=32, bir=Thu Jan 06 18:34:55 CST 2000, deptid=2}
-----------------

2. 查询员工信息并显示员工所在部门的信息

EmpDAO.java

public interface EmpDAO {
  //查询员工信息并显示员工所在部门的信息
  List<Emp>  queryAll();
}

EmpDAO.xml

<mapper namespace="com.xx.dao.EmpDAO">
    <resultMap id="empMap" type="com.xx.entity.Emp">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="bir" property="bir"/>
        <result column="deptid" property="deptid"/>
        <association property="dept" javaType="com.xx.entity.Dept">
            <id column="tid" property="id"/>
            <result column="tname" property="name"/>

        </association>
    </resultMap>
    <!--查詢所有-->
    <select id="queryAll" resultMap="empMap">
        SELECT
            e.id,
            e.NAME,
            age,
            bir,
            deptid,
            t.NAME tname,
            t.id tid
        FROM
            t_emp e
	    LEFT JOIN t_dept t ON e.deptid = t.id
    </select>
</mapper>

测试

public class TestEmp {
    private InputStream in;
    private SqlSession session;
    private EmpDAO empDAO;

    @Before
    public void init() throws IOException {
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2创建工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        session = factory.openSession();
        empDAO = session.getMapper(EmpDAO.class);
    }
    @After
    public void destory() throws IOException {
        //提交事務
        session.commit();
        session.close();
        in.close();
    }

   @Test
    public void TestQueryAll(){
       empDAO.queryAll().forEach(emp -> {
           System.out.println("员工信息"+emp+emp.getDept().getName()); });
    }
}

主配置文件中注册mapper

<mappers>
	<mapper resource="com/xx/dao/DeptDAO.xml"></mapper>
    <mapper resource="com/xx/dao/EmpDAO.xml"></mapper>
</mappers>

输出：

员工信息Emp{id=1, name='小张1', age=34, bir=Thu Aug 22 18:33:14 CST 2019, deptid=1}研发部
员工信息Emp{id=2, name='小王2', age=23, bir=Tue Feb 28 18:33:40 CST 2017, deptid=2}教学部
员工信息Emp{id=3, name='小丽1', age=45, bir=Fri Feb 01 18:34:09 CST 2019, deptid=1}研发部
员工信息Emp{id=4, name='小李2', age=32, bir=Thu Jan 06 18:34:55 CST 2000, deptid=2}教学部