Continued →

7. Many to one processing

Many to one understanding:

• Multiple students correspond to one teacher

• For students, it is a many to one phenomenon, that is, a teacher is associated with students!

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, 'Miss Wang');
​
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', 'Xiao Ming', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', 'Xiao Hong', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', 'Xiao Zhang', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', 'petty thief', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', 'Xiao Wang', '1');

Build test environment

1. IDEA installing Lombok plug-in

2. Introduce Maven dependency

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
 <groupId>org.projectlombok</groupId>
 <artifactId>lombok</artifactId>
 <version>1.16.10</version>
</dependency>

3. Add comments to the code

@Data //GET,SET,ToString, parameterless construction
public class Teacher {
   private int id;
   private String name;
}
@Data
public class Student {
   private int id;
   private String name;
   //Multiple students can be the same teacher, that is, many to one
   private Teacher teacher;
}

4. Write Mapper interfaces corresponding to entity classes [two]

• Whether there is a need or not, it should be written down for later needs!

public interface StudentMapper {
}
public interface TeacherMapper {
}

5. Write Mapper corresponding to Mapper interface XML configuration file [two]

• Whether there is a need or not, it should be written down for later needs!

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
       PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
       "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yi.mapper.StudentMapper">
​
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
       PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
       "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yi.mapper.TeacherMapper">
​
</mapper>

Nested processing by query

1. Add method to StudentMapper interface

//Get the information of all students and corresponding teachers
public List<Student> getStudents();

2. Write the corresponding Mapper file

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
       PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
       "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yi.mapper.StudentMapper">
​
   <!--
   Requirements: obtain the information of all students and corresponding teachers
   Idea:
       1. Get information about all students
       2. Teachers according to the student information obtained ID->Get the teacher's information
       3. Think about the problem. In this way, the result set of students should include teachers. How to deal with it? We usually use association query in the database?
           1. Make a result set mapping: StudentTeacher
           2. StudentTeacher The result set is of type Student
           3. The attributes of teachers in students are teacher，The corresponding database is tid. 
              Multiple [1,...)A student is associated with a teacher=> One to one, one to many
           4. Check the official website to find: association – A complex type of Association; Use it to process associative queries
   -->
   <select id="getStudents" resultMap="StudentTeacher">
    select * from student
   </select>
   <resultMap id="StudentTeacher" type="Student">
       <!--association Association properties property Attribute name javaType Attribute type column Column names in tables with more than one side-->
       <association property="teacher"  column="tid" javaType="Teacher" select="getTeacher"/>
   </resultMap>
   <!--
   From here id，When there is only one attribute, any value can be written below
   association in column Multi parameter configuration:
       column="{key=value,key=value}"
       It's actually in the form of key value pairs, key Pass it on to the next one sql The name of the value, value It's in clip 1 sql The field name of the query.
   -->
   <select id="getTeacher" resultType="teacher">
      select * from teacher where id = #{id}
   </select>
​
</mapper>

3. After writing, go to the Mybatis configuration file and register Mapper!

4. Notes:

<resultMap id="StudentTeacher" type="Student">
   <!--association Association properties property Attribute name javaType Attribute type column Column names in tables with more than one side-->
   <association property="teacher"  column="{id=tid,name=tid}" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
From here id，When there is only one attribute, any value can be written below
association in column Multi parameter configuration:
   column="{key=value,key=value}"
   It's actually in the form of key value pairs, key Pass it on to the next one sql The name of the value, value It's in clip 1 sql The field name of the query.
-->
<select id="getTeacher" resultType="teacher">
  select * from teacher where id = #{id} and name = #{name}
</select>

5. Testing

@Test
public void testGetStudents(){
   SqlSession session = MybatisUtils.getSession();
   StudentMapper mapper = session.getMapper(StudentMapper.class);
​
   List<Student> students = mapper.getStudents();
​
   for (Student student : students){
       System.out.println(
               "Student name:"+ student.getName()
                       +"\t teacher:"+student.getTeacher().getName());
  }
}

Nested processing by result

In addition to the above way, are there any other ideas?

We can also nest according to the results;

1. Interface method preparation

public List<Student> getStudents2();

2. Write the corresponding mapper file

<!--
Nested processing by query results
 Idea:
   1. Directly query the results and map the result set
-->
<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>
​
<resultMap id="StudentTeacher2" type="Student">
   <id property="id" column="sid"/>
   <result property="name" column="sname"/>
   <!--Associated object property Associated objects in Student Properties in entity classes-->
   <association property="teacher" javaType="Teacher">
       <result property="name" column="tname"/>
   </association>
</resultMap>

3. Go to mybatis config file to inject [it should be handled here]

4. Testing

@Test
public void testGetStudents2(){
   SqlSession session = MybatisUtils.getSession();
   StudentMapper mapper = session.getMapper(StudentMapper.class);
​
   List<Student> students = mapper.getStudents2();
​
   for (Student student : students){
       System.out.println(
               "Student name:"+ student.getName()
                       +"\t teacher:"+student.getTeacher().getName());
  }
}

Summary

Nested processing by query is like subquery in SQL

Nested processing according to the results is like a join table query in SQL

8. One to many processing

One to many understanding:

• A teacher has more than one student

• For teachers, it is a one to many phenomenon, that is, having a group of students (Collection) under a teacher!

Entity class writing

@Data
public class Student {
   private int id;
   private String name;
   private int tid;
}
@Data
public class Teacher {
   private int id;
   private String name;
   //One teacher has more than one student
   private List<Student> students;
}

..... As before, build a test environment!

Nested processing by result

1. TeacherMapper interface writing method

//Get the designated Teacher and all students under the teacher
public Teacher getTeacher(int id);

2. Write Mapper configuration file corresponding to the interface

<mapper namespace="com.yi.mapper.TeacherMapper">
​
   <!--
   thinking:
       1. Find out the students from the student list and the teacher list id，Student name, teacher name
       2. Map the result set of the queried operations
           1. Set, use collection!
               JavaType and ofType Are used to specify the object type
               JavaType Is used to specify pojo Type of property in
               ofType The specified is mapped to list In collection properties pojo Type of.
   -->
   <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=#{id}
   </select>
​
   <resultMap id="TeacherStudent" type="Teacher">
       <result  property="name" column="tname"/>
       <collection property="students" ofType="Student">
           <result property="id" column="sid" />
           <result property="name" column="sname" />
           <result property="tid" column="tid" />
       </collection>
   </resultMap>
</mapper>

3. Register the Mapper file in the mybatis config file

<mappers>
   <mapper resource="mapper/TeacherMapper.xml"/>
</mappers>

4. Testing

@Test
public void testGetTeacher(){
   SqlSession session = MybatisUtils.getSession();
   TeacherMapper mapper = session.getMapper(TeacherMapper.class);
   Teacher teacher = mapper.getTeacher(1);
   System.out.println(teacher.getName());
   System.out.println(teacher.getStudents());
}

Nested processing by query

1. TeacherMapper interface writing method

public Teacher getTeacher2(int id);

2. Write Mapper configuration file corresponding to the interface

<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id = #{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
   <!--column Is a one to many foreign key , Write the column name of a primary key-->
   <collection property="students" javaType="ArrayList" ofType="Student" column="id" select="getStudentByTeacherId"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
  select * from student where tid = #{id}
</select>

3. Register the Mapper file in the mybatis config file

4. Testing

@Test
public void testGetTeacher2(){
   SqlSession session = MybatisUtils.getSession();
   TeacherMapper mapper = session.getMapper(TeacherMapper.class);
   Teacher teacher = mapper.getTeacher2(1);
   System.out.println(teacher.getName());
   System.out.println(teacher.getStudents());
}

Summary

1. association Association

2. Collection collection

3. Therefore, association is used for one-to-one and many to one, while collection is used for one to many relationships

4. Both JavaType and ofType are used to specify the object type

• JavaType is used to specify the type of property in pojo

• ofType specifies the type mapped to pojo in the list collection property.

Note:

1. Ensure the readability of SQL and make it easy to understand as much as possible

2. According to the actual requirements, try to write SQL statements with higher performance

3. Pay attention to the inconsistency between attribute name and field

4. Note the correspondence between fields and attributes in one to many and many to one

5. Try to use Log4j to view your own errors through the log