【第三阶段 day21】Mybatis关联关系(一对一,一对多) Mybatis子查询

1.Mybatis 关联关系—一对一

1.1 创建表

1.1.1 创建员工表emp

1.1.2 创建部门表dept

1.2 pojo

package com.jt.pojo;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;

@Data
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {
    private Integer empId;
    private String empName;
    private Dept dept;
}

package com.jt.pojo;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

@Data
@Accessors(chain = true)
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
    private Integer deptId;
    private String deptName;
    private List<Emp> emps;//一个部门对应多个员工
}

1.3 相关配置与接口

1.4 常见表关系

秘诀:从一头出发,看向另一头 所在位置不同,结果不同

• 一对一 eg:一位公民对应一个身份证
• 一对多 eg:一个部门对应多个员工
• 多对多 eg:一个老师对应多个学生,一个学生对应多个老师

1.5 SQL语句

SELECT e.emp_id,e.emp_name,d.dept_id,d.dept_name FROM emp e
INNER JOIN dept d ON e.dept_id=d.dept_id

1.6 EmpMapper接口

1.7 EmpMapper.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">

<!--namespace是mybaits映射文件的唯一标识,与接口对应-->
<mapper namespace="com.jt.mapper.EmpMapper">
        <select id="findAll" resultMap="empRM">
            SELECT e.emp_id,e.emp_name,d.dept_id,d.dept_name FROM emp e
            INNER JOIN dept d ON e.dept_id=d.dept_id
        </select>
         <resultMap id="empRM" type="Emp">
             <id property="empId" column="emp_id"></id>
             <result property="empName" column="emp_name"></result>
             <association property="dept" javaType="Dept">
                 <id property="deptId" column="dept_id"></id>
                 <result property="deptName" column="dept_name"></result>
             </association>
         </resultMap>


</mapper>

1.8 测试

2.Mybatis 关联关系—一对多

2.1 DeptMapper接口

List<Dept> findAll2();

2.2 DeptMapper.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">

<!--namespace是mybaits映射文件的唯一标识,与接口对应-->
<mapper namespace="com.jt.mapper.DeptMapper">
    <select id="findAll2" resultMap="deptRM">
        SELECT d.dept_id,d.dept_name,e.emp_id,e.emp_name FROM emp e
            INNER JOIN dept d ON e.dept_id=d.dept_id
    </select>
    <resultMap id="deptRM" type="Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="emps" ofType="Emp">
            <id property="empId" column="emp_id"></id>
            <result property="empName" column="emp_name"></result>
        </collection>
    </resultMap>

</mapper>

2.3 测试

3.Mybatis子查询

3.1 DeptMapper接口

List<Dept> findAll3();

3.2 DeptMapper.xml

 <select id="findAll3" resultMap="depRM">
        select * from dept
    </select>
    <resultMap id="depRM" type="Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="emps" ofType="Emp" select="sel" column="dept_id"/>
    </resultMap>
    <select id="sel" resultMap="emRM">
        select * from emp where dept_id=#{dept_id}
    </select>
    <resultMap id="emRM" type="Emp">
        <id property="empId" column="emp_id"></id>
        <result property="empName" column="emp_name"></result>
    </resultMap>

3.3 测试