(三十四)mybatis关联关系(单向多对一、单向一对多、单向多对多)

mybatis关系

关系应用

需要解决的问题:

1. 怎么使用数据库表设计表示数据之间的关系
2. 怎么使用java类设计来表示对象之间的关系
3. 怎么通过mybatis来映射上面两者

关联关系分类

导航性:通过A对象的某一个属性可以访问到B,就说A可以导航到B

• 单向:通过A属性导航到B,B不能导航到B
• 双向:A,B可以互相导航

多重性:

• 一多一
• 一对多
• 多对一
• 多对多

实现

1.明确需求
2.表设计
3.实体类设计
一般看需求,决定关联书记邢定义睡那边,什么类型
4.Mapper接口和Mapper.xml

单向多对一

多个员工对应一个部门

需求:根据员工查询员工,并知道该员工所在部门

• 定义好类Employee, Department

@Data                            
public class Employee {
    private Long id;
    private String name;
    private Department dept; //关联属性}
---------------------------------------------------
@Data
public class Department {
    private Long id;
    private String name;}

• 定义EmployeeMapper,DepartmentMapper接口方法

public interface EmployeeMapper {
   Employee get(Long id);}
public interface DepartmentMapper {
    Department get(Long id);}

• EmployeeMapper.xml

<resultMap id="baseResultMap" type="Employee">
        <!--自定义封装规则id主键,result非主键-->
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="dept_id" property="dept.id"/>
   <!--告诉mybatis 帮我们发送sql 封装数据
   select 指定发送什么sql
   column 发送SQL的参数值来源于上一条sql执行结果的哪个列的值
        property 指定封装到员工对象的哪个属性
        -->
        <association select="cn.k.mapper.DepartmentMapper.get" column="dept_id" property="dept"/>
    </resultMap>

<select id="get" resultMap="baseResultMap">
SELECT id, name, dept_id FROM employee WHERE id = #{id}
</select>

• DepartmentMapper.xml

<select id="get" resultType="cn.k.domain.Department">
    SELECT id, name FROM department WHERE id = #{id}
</select>

返回值不使用resultTpye的原因:数据库表中并没有dept这个列,dept是部门表封装的对象,使用resultType是告诉mybatis用什么类型对象来封装查询的数据,结果集的列名要与对象属性名一直,类型也要匹配

所以,当查询的结果集的列名与封装对象属性名不一致的时候,使用resultMap来解决

使用association可以帮我们发送额外sql,用于关联属性非集合类型

Collection ,用于关联属性集合类型

• 测试类

//手动
不使用association加入额外sql
@Test
    public void get1(){
        SqlSession session = MyBatisUtil.getSession();
        DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
        EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
        Employee employee = employeeMapper.get1(1L);
        //1.先获取员工部门的id
        Long deptId = employee.getDept().getId();
        //发送sql,拿到这个员工id去查询对应的部门,利用mybatis帮我们封装成一个部门对象
        Department dept = departmentMapper.get(deptId);
        System.out.println(dept);
        //这个部门对象,设置员工对象的dept部门属性上
        employee.setDept(dept);
        System.out.println(employee);
        session.close();
    }    

//自动,mybatis帮我们封装,使用association
@Test
    public void get() {
        SqlSession session = MyBatisUtil.getSession();
        EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
        Employee employee = employeeMapper.get(1L);
        System.out.println(employee);
        session.close();
    }

补充: 查询: 额外SQL,在resultMap配置

  <association select="命名空间.SQLid值" column="上一条sql执行结果的哪个列的值" property="指定封装到员工对象的哪个属性"/>

额外sql查询N+1问题

如果要查询所有员工以及其部门,那么在查询时,会发送N+1条语句,这样大大降低了查询的速度与性能

改进:写查询所有对应表的sql

单向一对多

从部门角度,一个部门对应多个员工

需求:根据部门id,来查询好对应部门id的员工信息

• 定义实体类,找出一对多的关系

public class Department {
    private Long id;
    private String name;
    //关联属性 几何 对象直接new 畜类,能够避免空指针
    private List<Employee> employees=new ArrayList<>();
}
----------------------------------------------------------------
    public class Employee {
    private Long id;
    private String name;
    private Long deptId; //该属性用来封装员工部门的id值,不是关联属性
}

• 定义mapper接口

public interface DepartmentMapper {
    Department get(Long id);}
---------------------------------------------
 public interface EmployeeMapper {
     List<Employee> queryByDeptId(Long id);}   

• mapper.xml

 <resultMap id="baseResultMap" type="Department">
     <id column="id" property="id"/>
     <result column="name" property="name"/>
     <collection select="cn.k.mapper.EmployeeMapper.queryByDeptId"  column="id" property="employees"/>
    </resultMap>
    <select id="get" resultMap="baseResultMap">
        select id, name
        from department
        where id = #{id}
    </select>
-------------------------------------------------------------------
<select id="queryByDeptId" resultType="Employee">
    select id,name,dept_id deptId from employee where dept_id=#{deptId}
</select>

• 测试类

 @Test
    public void testGet(){
        SqlSession session = MyBatisUtil.getSession();
        DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
        EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
        Department department = departmentMapper.get(1L);
        System.out.println(department);
        /*Long departmentId = department.getId();
        List<Employee> employee = employeeMapper.queryByDeptId(departmentId);
        department.setEmployees(employee);
        System.out.println(department);*/
        session.close();
    }

单向多对多

需求:根据id查询学生,并查询老师

多对多关系,需要建立中间表

• 定义实体类,并找出关联关系

@Data
public class Student {
    private Long id;
    private String name;
    //关联属性
    //需求:根据id查询学生并查询其老师
    private List<Teacher> teachers =new ArrayList<>();
}
-------------------------------------------------------
@Data
public class Teacher {
    private Long id;
    private String name;
}

• 定义StudentMapper,TeacherMapper接口

public interface StudentMapper {
    //保存
    void save(Student student);
    //保存关系数据
    void insertRelation(@Param("teacherId") Long teacherId, @Param("studentId") Long studentId);
    //查询
    Student get(Long id);
}
--------------------------------------------------------
public interface TeacherMapper {
    //保存
    void save(Teacher teacher);
}

• Mapper.xml

StudentMapper.xml
<mapper namespace="cn.k.mapper.StudentMapper">
    <insert id="save" useGeneratedKeys="true" keyProperty="id">
        insert into student(name)
        values (#{name})
    </insert>

    <insert id="insertRelation">
        insert into teacher_student(student_id, teacher_id)
        values (#{studentId}, #{teacherId})
    </insert>

    <resultMap id="bashResultMap" type="Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <!--关联属性,让mybatis额外发送sql-->
        <collection select="cn.k.mapper.TeacherMapper.queryByStudentId"
                    property="teachers" column="id"/>
    </resultMap>

    <select id="get" resultMap="bashResultMap">
        select id, name
        from student
        where id = #{id}
    </select>
</mapper>

--------------------------------------------------------
TeacherMapper.xml
<mapper namespace="cn.k.mapper.TeacherMapper">
    <insert id="save" useGeneratedKeys="true" keyProperty="id">
    insert into teacher (name) values (#{name})
    </insert>

    <select id="queryByStudentId" resultType="Teacher">
        select *from teacher_student ts join teacher t on ts.teacher_id = t.id where ts.student_id=#{studentId}
</select>
</mapper>

useGeneratedKeys=true 获取数据库保存数据的的主键值 keyProperty=“id” 主键设置设置对象的 id 属性

• 测试类

@Test
    public void testSave() {
        SqlSession session = MyBatisUtil.getSession();
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
        Teacher t1 = new Teacher();
        t1.setName("貂蝉老师");
        Teacher t2 = new Teacher();
        t2.setName("大乔老师");
        //设置
        Student s1 = new Student();
        s1.setName("小罗");
        Student s2 = new Student();
        s2.setName("小蔡");

        //添加老师
        s1.getTeachers().add(t1);
        s1.getTeachers().add(t2);
        s2.getTeachers().add(t1);
        s2.getTeachers().add(t2);
       //添加
        teacherMapper.save(t1);
        teacherMapper.save(t2);
        studentMapper.save(s1);
        studentMapper.save(s2);
       //往中间表添加学生与老师间的关系
  for (Teacher teacher:s1.getTeachers()) {         studentMapper.insertRelation(teacher.getId(),s1.getId()); }
  for (Teacher teacher:s2.getTeachers()) {        studentMapper.insertRelation(teacher.getId(),s2.getId());}
   session.commit();
   session.close();
    }

@Test   //根据学生id得到了学生的对应老师是谁
    public void testGet() {
        SqlSession session = MyBatisUtil.getSession();
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        Student student = studentMapper.get(4L);
        System.out.println(student);
        session.close();
    }

多对多删除的删除要注意的是,删除数据的同时,需要把对应关系也删除掉 ,硬删除执行delete语句,软删除 执行update语句