【MyBatis08】MyBatis处理关联关系(多表连接):一对多

2 一对多关系

2.1 数据准备

  • 建表

CREATE TABLE t_departments(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    location VARCHAR(100)
)DEFAULT CHARSET =utf8;

CREATE TABLE t_employees(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50),
    salary DOUBLE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES t_departments(id)
)DEFAULT CHARSET =utf8;
  • 插入测试数据
INSERT INTO t_departments VALUES(1,"sales","Beijin"),(2,"development","Shanghai");
INSERT INTO t_employees VALUES(1,"Jack",10000.5,1),(2,"Carina",20000.5,1),
                              (3,"Chachawan",9000.5,2),(4,"Hello",8000.5,2);

 

 

  • 建立对应实体

Department:

public class Department {
    private Integer id;
    private String name;
    private String location;

    public Department() {
    }
    ......

Employee:

 

public class Employee {
    private Integer id;
    private String name;
    private Double salary;

    public Employee() {
    }
    ......

2.2 添加关系属性

Department:

public class Department {
    private Integer id;
    private String name;
    private String location;

    private List<Employee> employees;//部门中所有员工的信息

    public Department() {
    }
  • private List<Employee> employees;

    • 由于部门中的员工数目不止一个,所有如果此处仍然使用和一对一关系中类似的private Employee employees;的话显然不能存储所有的员工

    • 因此此处需要使用一个List来存储所有的员工

Employee:

public class Employee {
    private Integer id;
    private String name;
    private Double salary;

    private Department department;//员工所从属的部门信息

    public Employee() {
    }
  • private Department department;
    • 员工只会从属于一个部门,此处与一对一关系中的关联属性一致

2.3 进行关联查询

DAO接口

Department queryDepartmentById(@Param("id") Integer 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.qianglj.test2.dao.DepartmentDao">

    <resultMap id="dept_emp" type="Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="location" column="location"/>
        <collection property="employees" ofType="Employee">
            <id property="id" column="empId"/>
            <result property="name" column="empName"/>
            <result property="salary" column="empSalary"/>
        </collection>
    </resultMap>
    <select id="queryDepartmentById" resultMap="dept_emp">
        select td.id,td.name,td.location,
               te.id empId,te.name empName,te.salary empSalary
        from t_departments td
        join t_employees te
        on td.id = te.dept_id
        where td.id = #{id}
    </select>
</mapper>

  • <collection property="employees" ofType="Employee">
    • collection标签,表示映射的是集合

    • ofType属性,表示返回的类型是集合里面的泛型类型

测试方法

        DepartmentDao departmentDao = MyBatisUtil.getMapper(DepartmentDao.class);
        Department department = departmentDao.queryDepartmentById(1);
        List<Employee> employees = department.getEmployees();
        for (Employee employee : employees) {
            System.out.println(employee);
        }

测试结果

Employee{id=1, name='Jack', salary=10000.5}
Employee{id=2, name='Carina', salary=20000.5}

2.4 双向查询

由Passport去关联查询Department的形式与一对一关系完全一致,此处不再赘述,仅展示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.qianglj.test2.dao.EmployeeDao">
    <resultMap id="emp_dept" type="Employee">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="salary" column="salary"/>
        <association property="department" javaType="Department">
            <id property="id" column="deptId"/>
            <result property="name" column="deptName"/>
            <result property="location" column="deptLocation"/>
        </association>
    </resultMap>
    <select id="queryEmployeeById" resultMap="emp_dept">
        select te.id, te.name, te.salary,
               td.id deptId, td.name deptName, td.location deptLocation
        from t_employees te
        join t_departments td
        on te.dept_id = td.id
        where te.id = #{id}
    </select>
</mapper>

 

版权声明:
作者:jackqiang
链接:http://www.jackqiang.com/framework/mybatis/1945/one2many/
来源:JackQiang's
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>
文章目录
关闭
目 录