Many to one processing and one to many processing

Time:2021-12-8

In the two tables created above, the relationship between students and teachers is many to one for students and one to many for teachers. Dealing with such relatively complex relationships in mybatis requires objects (associations) and collections.

Many to one

  • Entity class

    public class Student2 {
      private int id;
      private String name;
      //The relationship between students and teachers is many to one, and teachers need to be associated among students
      private Teacher2 teacher;
    }
    public class Teacher2 {
    private int id;
    private String name;
    }
  • Interface

    public interface Student2Mapper {
      //Get all student information and his teachers
      List<Student2> getStudent();
      List<Student2> getStudent2();
    }
  • XML configuration

    <!-- Nesting by result -- >
      <select id="getStudent" resultMap="StudentTeacher">
          select s.id sid,s.name sname,t.name tname,t.id tid
          from student s,teacher t
          where s.tid = t.id
      </select>
    
      <resultMap id="StudentTeacher" type="Student2">
          <result property="id" column="sid"/>
          <result property="name" column="sname"/>
          <association property="teacher" javaType="Teacher2">
              <result property="id" column="tid"/>
              <result property="name" column="tname"/>
          </association>
      </resultMap>
    
      <!-- Method through subquery -- >
      <select id="getStudent2" resultMap="StudentTeacher2">
          select * from student
      </select>
    
      <resultMap id="StudentTeacher2" type="Student2">
          <!--  Complex attributes need to be handled separately: association set: Collection -- >
          <association property="teacher" column="tid" javaType="Teacher2" select="getTeacher"/>
      </resultMap>
      <select id="getTeacher" resultType="Teacher2">
          select * from teacher where id = #{tid}
      </select>
  • One to many

From the teacher’s point of view, entity classes are as follows

public class Student {
    private int id;
    private String name;
    private int tid;
}

public class Teacher {
  private int id;
 private String name;
 private List<Student> students;
}
List<Teacher> getTeacher2(@Param("tid") int id);
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hudu.dao.TeacherMapper">
    <select id="getTeacher" resultType="Teacher">
        select * from teacher
    </select>

    <!-- Nest by result -- >
    <select id="getTeacher2" resultMap="TeacherStudent">
        SELECT s.id sid,s.name sname,t.name tname,t.id tid
        FROM teacher t,student s
        where t.id = s.tid and t.id = #{tid}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!-- For complex attributes, we need to deal with the object: association collection: Collection separately
        Javatype = "" specifies the type of property. ""
        We use oftype to get the generic information in the collection
        -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <select id="getTeacher3" resultMap="TeacherStudent3">
        select * from teacher where id = #{tid}
    </select>

    <resultMap id="TeacherStudent3" type="Teacher">
        <!--<result property="id" column="id"/>-->
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid = #{tid};
    </select>
</mapper>

This work adoptsCC agreement, reprint must indicate the author and the link to this article