Related articles

MyBatis series summary: MyBatis series

preface

  • Take a look at the result set description on the website

  • The one we use most often is result, but in real life, we may encounter associations of complex types! This is where association and collection are used

  • Here are the preconditions

  • Student table

    • CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(20) 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
Copy the code

  • The teacher table

    • CREATE TABLE `teacher` (
  `id` int(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Copy the code

  • Add test data

  • Entity class

    • @Data
public class Student {
    private Integer id;
    private String name;
    // A teacher class needs to be associated
    private Teacher teacher;
}
Copy the code
    • @Data
public class Teacher {
    private int id;
    private String name;
}
Copy the code

  • Other with the previous article to keep the same! There are many ways to choose

One, many on one

  • First, let’s check the students normally and see what happens:

    • public interface StudentMapper {
        List<Student> getStudent(a);
}
Copy the code
    •     <select id="getStudent" resultType="Student">
        select * from student
    </select>
Copy the code
    •     @Test
    public void getStudent(a){
        SqlSession session = MybatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudent();
        students.forEach(student -> {
            System.out.println(student);
        });
        session.close();
    }
Copy the code

  • Execution result:

  • As a result, the normal return in a query with this particular nested class is NULL. Let’s use nested mappings to solve this problem.

(1) Query nesting (subquery)

  • Note: There can be only one resultMap and one resultType. Cannot exist simultaneously.

  • Modify the mapper XML

    •     <select id="getStudent" resultMap="Student">
        select * from student
    </select>

    <resultMap id="Student" type="com.dy.pojo.Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result> <! -- Object using assiociation--> <! <association property="teacher" column="tid"
                     javaType="com.dy.pojo.Teacher"
                     select="getTeacher"></association>
    </resultMap>

    <select id="getTeacher" resultType="com.dy.pojo.Teacher">
        select * from teacher where id = #{tid};
    </select>

Copy the code

  • Execution result:

  • Nice! Perfect problem solving!

(2) select * from table where results are nested

  • mapper.xml:

    •     <select id="getStudent2" resultMap="Student">        select s.id sid,s.name sname,t.name tname        from student s,teacher t where s.tid=t.id;    </select>    <resultMap id="Student" type="com.dy.pojo.Student">        <result property="id" column="sid"></result>        <result property="name" column="sname"></result>        <association property="teacher" javaType="com.dy.pojo.Teacher">            <result property="name" column="tname"></result>        </association>    </resultMap>
Copy the code

  • Execution result:

Two, one to many

  • Basic premise:

    • @Data@Alias("Teacher2")public class Teacher2 {    private int id;    private String name;    privateList<Student1> studentList; }Copy the code
    • @Datapublic class Student1 {    private Integer id;    private String name;    private inttid; }Copy the code

    1, results nested (join table query)

    • mapper.xml

      •     <select id="getTeacher" resultMap="Teacher2">        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 = #{id};    </select>    <resultMap id="Teacher2" type="com.dy.pojo2.Teacher2">        <result property="id" column="tid"></result>        <result property="name" column="tname"></result> <! Oftype --> <collection property="studentList" ofType="com.dy.pojo2.Student1">            <result property="id" column="sid"></result>            <result property="name" column="sname"></result>        </collection>    </resultMap>
Copy the code

    • Execution result:

②, query nesting (subquery)

  • mapper.xml

    •     <select id="getTeacher2" resultMap="Teacher2">        select * from teacher where id = #{id}    </select>    <resultMap id="Teacher2" type="com.dy.pojo2.Teacher2">        <collection property="studentList" column="id" javaType="ArrayList"                    ofType="com.dy.pojo2.Student1"                    select="getStudentByTeacherId"></collection>    </resultMap>    <select id="getStudentByTeacherId" resultType="com.dy.pojo2.Student1">        select * from student where tid = #{id}    </select>
Copy the code

  • Execution result:

  • Perfect!

Third, summary

  • Association-association Many-to-one

  • Collection – Collection one-to-many

  • javaType & ofType

    • JavaType is used to specify attribute types in an entity
    • OfType maps to types in list, constraint types in generics

  • Note:

    • Ensure that the SQL is readable, as easy as possible to understand
    • If the problem is not easy to troubleshoot, use logs
    • Distinguish between resultMap and resultType.

I see no ending, but I will search high and low

If you think I blogger writes good! Writing is not easy, please like, follow, comment to encourage the blogger ~hahah