=========================
MyBatis关联映射之一多关联
=========================

部门->员工

1 数据表

1.1 部门

create table t_department(
    id int primary key auto_increment, <--
    name varchar(30) not null unique,     |
    description varchar(100)              |
);                                        |
                                          |
1.2 员工                                  |
                                          |
create table t_employee(                  |
    number char(5) primary key,           |
    fullname varchar(20) not null,        |
    age int not null,                     |
    department int not null --------------
);

2 实体类

2.1 部门

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Department {
    private int id;
    private String name;
    private String description;
}

2.2 员工

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Employee {
    private String number;
    private String fullname;
    private int age;
    private int department;
}

3 DAO

3.1 部门

public interface DepartmentDao {
    public int addDepartment(Department department);
}

3.2 员工

public interface EmployeeDao {
    public int addEmployee(Employee employee);
}

4 映射

<typeAlias type="cn.tedu.association.pojo.Department" alias="Department" />
<typeAlias type="cn.tedu.association.pojo.Employee" alias="Employee" />

<mapper resource="mappers/DepartmentMapper.xml" />
<mapper resource="mappers/EmployeeMapper.xml" />

4.1 部门

<mapper namespace="cn.tedu.association.dao.DepartmentDao">
    <insert id="addDepartment" useGeneratedKeys="true" keyProperty="id">
        insert into t_department(name, description)
        values(#{name}, #{description})
    </insert>
</mapper>

4.2 员工

<mapper namespace="cn.tedu.association.dao.EmployeeDao">
    <insert id="addEmployee">
        insert into t_employee(number, fullname, age, department)
        values(#{number}, #{fullname}, #{age}, #{department})
    </insert>
</mapper>

5 测试

public class DepartmentDaoTest {
    @Test
    public void testAddDepartment() {
        DepartmentDao departmentDao = MyBatisUtil.getMapper(DepartmentDao.class);
        Department department = new Department(0, "研发部", "研发软件产品", null);
        assertEquals(1, departmentDao.addDepartment(department));

        EmployeeDao employeeDao = MyBatisUtil.getMapper(EmployeeDao.class);
        assertEquals(1, employeeDao.addEmployee(
            new Employee("1001", "张飞", 25, department.getId())));
        assertEquals(1, employeeDao.addEmployee(
            new Employee("1002", "关羽", 30, department.getId())));
        assertEquals(1, employeeDao.addEmployee(
            new Employee("1003", "赵云", 20, department.getId())));
    }
}

6 连接查询

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class DepartmentEmployees {
    private Department department;
    private List<Employee> employees;
}

public interface DepartmentDao {
    ...
    public DepartmentEmployees getDepartmentEmployees(String name);
    ...
}

<typeAliases>
    ...
    <typeAlias type="cn.tedu.association.pojo.DepartmentEmployees" alias="DepartmentEmployees" />
    ...
</typeAliases>

<mapper namespace="cn.tedu.association.dao.DepartmentDao">
    ...
    <!-- 连接查询 -->
    <resultMap id="departmentEmployees" type="DepartmentEmployees">
        <result column="id" property="department.id" />
        <result column="name" property="department.name" />
        <result column="description" property="department.description" />
        <collection property="employees" ofType="Employee">
            <result column="number" property="number" />
            <result column="fullname" property="fullname" />
            <result column="age" property="age" />
            <result column="department" property="department" />
        </collection>
    </resultMap>
    <select id="getDepartmentEmployees" resultMap="departmentEmployees">
        select id, name, description, number, fullname, age, department
        from t_department inner join t_employee on id = department
        where name = #{name}
    </select>
    ...
</mapper>

public class DepartmentDaoTest {
    ...
    @Test
    public void testGetDepartmentEmployees() {
        DepartmentDao departmentDao = MyBatisUtil.getMapper(DepartmentDao.class);
        DepartmentEmployees departmentEmployees = departmentDao.getDepartmentEmployees("研发部");
        assertNotEquals(null, departmentEmployees);
        System.out.println(departmentEmployees);
    }
    ...
}

运行测试用例。

例程：Association

7 子查询

public interface EmployeeDao {
    ...
    public List<Employee> getEmployees(int department);
    ...
}

<mapper namespace="cn.tedu.association.dao.EmployeeDao">
    ...
    <select id="getEmployees" resultType="Employee">
        select number, fullname, age, department from t_employee
        where department = #{department}
    </select>
    ...
</mapper>

<mapper namespace="cn.tedu.association.dao.DepartmentDao">
    ...
    <!-- 子查询 -->
    <resultMap id="departmentEmployees" type="DepartmentEmployees">
        <result column="id" property="department.id" />
        <result column="name" property="department.name" />
        <result column="description" property="department.description" />
        <association column="id" property="employees"
                     select="cn.tedu.association.dao.EmployeeDao.getEmployees" />
    </resultMap>
    <select id="getDepartmentEmployees" resultMap="departmentEmployees">
        select id, name, description from t_department
        where name = #{name}
    </select>
    ...
</mapper>

运行测试用例。

例程：Association