MyBatis Notes (5)

1. The many-to-one

What is many-to-one?

There are many students for one teacher

1.1 Database Design

CREATE TABLE `teacher` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`, `name`) VALUES (1.'Joe');

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;


INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1'.'Ming'.'1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2'.'little red'.'1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3'.'zhang'.'1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4'.'xiao li'.'1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5'.'wang'.'1');
Copy the code

1.2 Setting up a test environment

Write the entity class POJO

public class Teacher {
    private int id;
    private String name;
    
    // No arguments
    // There are parameters
    // get and set methods
    / / the toString method
}
Copy the code

public class Student {
    private int id;
    private String name;
    // Multiple students can be the same teacher, i.e. many to one
    private Teacher teacher;
    
    // No arguments
    // There are parameters
    // get and set methods
    / / the toString method
}
Copy the code

Write the Mapper interface for the entity class

public interface StudentMapper {}Copy the code

public interface TeacherMapper {}Copy the code

Write the mapper.xml configuration file corresponding to the interface


        
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="top.linzeliang.mapper.StudentMapper">
 
</mapper>
Copy the code


        
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="top.linzeliang.mapper.TeacherMapper">
 
</mapper>
Copy the code

1.3 Processing by query nesting

Add methods to StudentMapper

// Get information about all students and their teachers
public List<Student> getStudents(a);
Copy the code

Write the corresponding Mapper file

<! -- Demand: To obtain the information of all students and corresponding teachers ideas: 1. To obtain the information of all students 2. 3. Think about the problem, so that the result set of students should contain the teacher, how to deal with it, we generally use relational query in the database? Create a result set mapping StudentTeacher 2. StudentTeacher The result set type is Student 3. The attribute of the teacher in the student is teacher, which corresponds to tid in the database. Association - a complex type of association; use it to handle associative queries --><select id="getStudents" resultMap="StudentMap">    select *    from student;</select><resultMap id="StudentMap" type="student">    <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/></resultMap><! Select * from association column where id = '0' and id = '0' Column ="{key=value,key=value}" is a key-value pair. Key is the value name passed to the next SQL, and value is the field name of the SQL query in section 1. --><select id="getTeacher" resultType="Teacher">    select *    from teacher    where id = #{tid};</select>
Copy the code

test

1.4 Nesting according to results

Personally, I think this is easier to understand

Interface method writing

Public List
        
          getStudents();
        
Copy the code

Write the corresponding Mapper configuration file

<select id="getStudents" resultMap="StudentMap">    select t1.id id, t1.name sname, t2.id tid, t2.name tname    from student t1,    teacher t2    where t1.tid = t2.id;</select><resultMap id="StudentMap" type="student">    <id property="id" column="id"/>    <result property="name" column="sname"/>    <association property="teacher" javaType="Teacher">        <id property="id" column="tid"/>        <result property="name" column="tname"/>    </association></resultMap>
Copy the code

test

2. One to many

There are many students for every teacher

2.1 Build a test environment

Writing entity classes

public class Student {    private int id;    private String name;    private int tid;        // get/set // toString
Copy the code

public class Teacher {    private int id;    private String name;    Private List
        
          students; private List
         
           students; // get/set // toString
         
        
Copy the code

2.2 Processing by query nesting

TeacherMapper interface writing method

Public Teacher getTeacher(int id); public Teacher getTeacher(int id);
Copy the code

Write the Mapper configuration file corresponding to the interface (with the ofType attribute)

<select id="getTeacher" resultMap="TeacherMap">    select *    from teacher    where id = #{id};</select><resultMap id="TeacherMap" type="Teacher">    <id property="id" column="id"/>    <result property="name" column="name"/>    <! -- column is a one-to-many foreign key and writes the column name of the primary key of one -->    <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudent"/></resultMap><select id="getStudent" resultType="Student">    select *    from student    where tid = #{id}</select>
Copy the code

test

2.3 Nesting according to results

TeacherMapper writing method

Public Teacher getTeacher(int id); public Teacher getTeacher(int id);
Copy the code

Write the Mapper configuration file corresponding to the interface

<select id="getTeacher" resultMap="TeacherMap">    select t1.id tid, t1.name tname, t2.id sid, t2.name sname    from teacher t1,    student t2    where t1.id = t2.tid    and t1.id = #{id};</select><resultMap id="TeacherMap" type="Teacher">    <id property="id" column="tid"/>    <result property="name" column="tname"/>    <collection property="students" ofType="Student">        <id property="id" column="sid"/>        <result property="name" column="sname"/>        <result property="tid" column="tid"/>    </collection></resultMap>
Copy the code

test

3. The ResultMap summary

If the attribute is a JavaBean object, use association,
If the property is a collection, use a collection
Association is used for one-to-one and many-to-one. Collecion applies to one-to-many relationships
Both javaType and ofType are used to specify object types
- JavaType is the type used to specify attributes in poJOs
- OfType refers to the type of POJOs mapped to the list collection properties