多对一

多对一保存

以员工类与部门类为例，员工和部门是多对一关系

注：先保存一方，再保存多方

/**
 * 员工类
 */
public class Employee {
    private Long id;
    private String name;
    private Integer age;
    private Dept dept;
    //getter、setter、toString
}
复制代码

/**
 * 部门类
 */
public class Dept {
    private Long id;
    private String name;
    //getter、setter、toString
}
复制代码

DeptMapper.xml:一定要返回主键id，不然员工无法关联部门

<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
   INSERT INTO t_dept(name) VALUES (#{name})
</insert>
复制代码

EmployeeMapper.xml

<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
   INSERT INTO t_employee(name, age, dept_id) VALUES (#{name},#{age},#{dept.id})
</insert>
复制代码

测试多对一保存

@Test
public void testSave() throws Exception{
    SqlSession sqlSession = MybatisUtils.openSession();
    EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    Dept dept = new Dept();
    dept.setName("开发部");
    //先保存一方
    deptMapper.save(dept);
    Employee e1 = new Employee();
    e1.setName("张三");
    e1.setAge(22);
    e1.setDept(dept);
    Employee e2 = new Employee();
    e2.setName("李四");
    e2.setAge(23);
    e2.setDept(dept);
    //保存多方
    employeeMapper.save(e1);
    employeeMapper.save(e2);
    sqlSession.commit();
}
复制代码

多对一关联对象的映射

在resultMap标签中使用association 标签映射

<resultMap id="employeeResultMap" type="cn.itsource._02many2one.domain.Employee">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <!--
        映射关联对象 association
        property="dept"  映射Employee中的dept属性
        javaType="cn.itsource._02many2one.domain.Dept  dept属性对应的类型
        注意：当你使用了association映射之后，默认映射规则失效
    -->
    <association property="dept" javaType="cn.itsource._02many2one.domain.Dept">
        <id column="did" property="id"/>
        <result column="dname" property="name"/>
    </association>
</resultMap>

<select id="selectAll" resultMap="employeeResultMap">
    select e.id,e.name,e.age,d.id did,d.name dname
    from t_employee e join t_dept d
    on e.dept_id = d.id
</select>
复制代码

多对一子查询封装对象

子查询方式效率很低，建议使用关联查询方式

DeptMapper.xml

<select id="selectById" resultType="cn.itsource._02many2one.domain.Dept">
    SELECT * FROM t_dept WHERE id=#{id}
</select>
复制代码

EmployeeMapper.xml

<resultMap id="employeeResultMap2" type="cn.itsource._02many2one.domain.Employee">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <!--
        把dept_id这一列对应的值，到DeptMapper中selectById该sql语句中进行查询，把查询出来的对象
        封装到Employee中的dept属性中
    -->
    <association property="dept" column="dept_id"
                 select="cn.itsource._02many2one.mapper.DeptMapper.selectById"/>
</resultMap>

<select id="selectAll2" resultMap="employeeResultMap2">
    SELECT * FROM t_employee
</select>
复制代码

一对多

一对多保存

部门与员工是一对多关系

注：先保存一方，再保存多方

/**
 * 部门类
 */
public class Dept implements Serializable {
    private Long id;
    private String name;
    private List<Employee> employees = new ArrayList<>();
    //getter、setter、toString
}
复制代码

/**
 * 员工类
 */
public class Dept implements Serializable {
    private Long id;
    private String name;
    private Integer age;
    //getter、setter、toString
}
复制代码

DeptMapper.xml

<insert id="save" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
   INSERT INTO t_dept(name) VALUES (#{name})
</insert>
复制代码

EmployeeMapper.xml

<insert id="save">
    INSERT INTO t_employee(name, age, dept_id) VALUES (#{e.name},#{e.age},#{deptId})
</insert>
复制代码

测试保存

@Test
public void testSave() throws Exception{
    SqlSession sqlSession = MybatisUtils.openSession();
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    Dept dept = new Dept();
    dept.setName("测试部门");
    Employee e1 = new Employee();
    e1.setName("乔峰");
    e1.setAge(22);

    Employee e2 = new Employee();
    e2.setName("楚楚");
    e2.setAge(33);

    dept.getEmployees().add(e1);
    dept.getEmployees().add(e2);
    //保存一方
    deptMapper.save(dept);
    for (Employee employee : dept.getEmployees()) {
        //保存多方
        employeeMapper.save(employee, dept.getId());
    }
    sqlSession.commit();
}
复制代码

一对多关联查询

一对多关联查询一定要排序，这是mybatis底层的一个bug

<resultMap id="deptResultMap" type="cn.itsource._03one2many.domain.Dept">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <!--
        映射集合要使用collection
        注意：使用了collection映射之后，默认映射规则失效
    -->
    <collection property="employees" ofType="cn.itsource._03one2many.domain.Employee">
        <id column="eid" property="id"/>
        <result column="ename" property="name"/>
        <result column="eage" property="age"/>
    </collection>
</resultMap>

<select id="selectAll" resultMap="deptResultMap">
    SELECT d.id,d.name,e.id eid,e.name ename,e.age eage
    FROM t_dept d JOIN t_employee e
    ON d.id = e.dept_id
    ORDER BY d.id
</select>
复制代码

测试一对多关联查询

/**
 * 关联查询
 */
@Test
public void testQuery() throws Exception {
    SqlSession sqlSession = MybatisUtils.openSession();
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    List<Dept> depts = deptMapper.selectAll();
    for (Dept dept : depts) {
        System.out.println(dept);
    }
}
复制代码

一对多子查询

EmployeeMapper.xml

<select id="selectByDeptId" resultType="cn.itsource._03one2many.domain.Employee">
    SELECT * FROM t_employee WHERE dept_id=#{deptId}
</select>
复制代码

DeptMapper.xml

<resultMap id="deptResultMap2" type="cn.itsource._03one2many.domain.Dept">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <collection property="employees" ofType="cn.itsource._03one2many.domain.Employee"
                column="id"
                select="cn.itsource._03one2many.mapper.EmployeeMapper.selectByDeptId"/>
</resultMap>

<select id="selectAll2" resultMap="deptResultMap2">
    SELECT * FROM t_dept
</select>
复制代码

测试一对多子查询

/**
 * 子查询
 * @throws Exception
 */
@Test
public void testQuery2() throws Exception {
    SqlSession sqlSession = MybatisUtils.openSession();
    DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
    List<Dept> depts = deptMapper.selectAll2();
    for (Dept dept : depts) {
        System.out.println(dept);
    }
}
复制代码