MyBatis study notes - 07 many to one and many to one

I Many to one

1. Description

  • Multiple students correspond to one teacher
  • For students, it is related to... Multiple students and one teacher [many to one]
  • For teachers, a collection, a teacher, has many students [one to many]

2. Cases

1. Data sheet preparation
  1. Student table

    CREATE TABLE `student` (
      `id` int(10) NOT NULL,
      `name` varchar(30) DEFAULT NULL,
      `tid` int(10) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fktid` (`tid`),
      CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
  2. Teacher table

    CREATE TABLE `teacher` (
      `id` int(10) NOT NULL,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
2. Environmental construction
  1. Create entity classes based on data table fields

    public class Student {
        private int id;
        private String name;
        private Teacher teacher;
    }
    
    
    public class Teacher {
        private int id;
        private String name;
    }
    
    
  2. Create Mapper interface

  3. Create mapper XML file

  4. Bind Mapper interface or Mapper. In the core configuration file XML file

3. Nested processing by query (sub query)

1. Ideas
  1. Find out all student information
  2. Find the corresponding teacher - > sub query according to the student's tid
2. Steps
  1. Step 1: query all student information, and the return value type is StudentTeacher

    <select id="getStudents" resultMap="StudentTeacher">
        select * from student
    </select>
    
  2. Step 2: query the corresponding teacher information through id

    <select id="getTeacher" resultType="teacher">
        select * from teacher where id = #{id}
    </select>
    
  3. Step 3: Associate

    <resultMap id="StudentTeacher" type="Student">
       <!--The entity class attribute corresponds to the field in the database-->
       <result property="id" column="id"/>
       <result property="name" column="name"/>
       <!--
           Student In table teacher Corresponding to in the database tid field
           Because it is a complex attribute, So pass javaType Specifies the type of property
           Then call getTeacher Method nested query Teacher
    
       -->
       <!--association: relation-->
       <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    

4. Nested processing according to results (joint table query)

  • code
    <resultMap id="StudentTeacher2" type="student">
        <result property="id" column="sid" />
        <result property="name" column="sname" />
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname" />
        </association>
    </resultMap>
    <!--Implemented through a tag-->
    <select id="getStudents2" resultMap="StudentTeacher2">
        select s.id sid, s.name sname, t.name tname
        from student s, teacher t
        where s.tid = t.id;
    </select>
    

II One to many

1. Entity class

  1. Student

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

    public class Teacher {
        private int id;
        private String name;
        /**
         * A teacher has more than one student
         */
        private List<Student> students;
    }
    

2. Nested processing by query (sub query)

  1. Step 1: query the teacher with the specified ID

    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id = #{tid}
    </select>
    
  2. Step 2: query students through tid

    <select id="getStudentByTid" resultType="Student">
        select * from student where tid = #{tid}
    </select>
    
  3. Step 3: Associate

    <resultMap id="TeacherStudent2" type="teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTid" column="id" />
    </resultMap>
    

3. Nested processing according to results (joint table query)

  • code
    <!--Nested query by result -->
    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--
            association object; collection aggregate
            javaType: Specify type properties
            Generic information in collection, We use ofType obtain
            -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid, s.name sname, t.name tname, t.id tid
        from student s, teacher t
        where s.tid = t.id and t.id = #{tid};
    </select>
    

III Summary

  1. association - association [many to one]
  2. Set - collection [one to many]
  3. javaType & ofType
    1. JavaType is used to specify the type of attribute in the entity class
    2. ofType is used to specify the pojo type mapped to List or collection, and the constraint type in generic type!

explain:

  1. This paper refers to the courseware of Mybatis of madness
  2. Course link

Tags: Mybatis

Posted by stevebrett on Sat, 21 May 2022 17:26:13 +0300