Zero, Preface
This article is used to study one-to-many, many-to-one, and many-to-many relationships between tables and objects.
Two methods of xml file configuration and annotation configuration were studied
Super complete~
The entities used for research are students, teachers, courses
Note: The relationship between students and teachers should be many-to-many. This article uses many-to-one, that is, multiple students correspond to one teacher, and one teacher corresponds to multiple students. There is a many-to-many relationship between students and courses.
1. Preparation
1. Create a maven project and add dependencies
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>day10-2</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<!-- log4j log -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
2. Create the mybatis-config configuration file in the resources folder
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="jdbc.properties"/>
<settings>
<!--Map underscore to camel case student_id -> studentId -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!--Enable lazy loading-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--Load on demand-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<typeAliases>
<package name="com.wtp.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--Note that the interface name of the mapper interface must be the same as the name of the mapper.xml file and must be in the same folder before you can directly import the package -->
<package name="com.wtp.mapper"/>
</mappers>
</configuration>
3. Create jdbc.properties in the resources folder and introduce the log4j.xml file
jdbc.properties:
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/xsgl3?serverTimezone=UTC jdbc.username=root jdbc.password=123456
log4j.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \\
" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="info" />
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
4. Create t_student table and t_teacher table
When establishing a data table, for many-to-one or one-to-many relationships, the primary key of the multi-party entity should be added to the primary key of the single-party entity, so the created t_student table must have the field teacher_id as a foreign key to connect the two tables.
5. Create t_lesson table and student_lesson table
When establishing a multi-pair relationship, the primary keys of the two multi-party entities should be added to the intermediate table to facilitate three-table queries.
6. Create entity classes Student, Teacher, Lesson
package com.wtp.pojo;
import lombok.*;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private int studentId;
private String studentName;
private int age;
//Multiple students to one teacher
private teacher teacher;
//A student chooses multiple courses. There are multiple students in one course.
private List<Lesson> lessons;
}
package com.wtp.pojo;
import lombok.*;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
private Integer teacherId;
private String teacherName;
private List<Student> students;
}
package com.wtp.pojo;
import lombok.*;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Lesson {
private Integer lessonId;
private String lessonName;
//A course is selected by multiple students
private List<Student> students;
}
7. Create three interfaces StudentMapper, TeacherMapper, and StudentAndLessonMapper and corresponding mapping files
2. Attribute mapping
Whether it is one-to-many, many-to-one, or many-to-many, the queried entities need to be mapped when encapsulated into Java objects.
Look at a simple query:
<!--Student getStudentById(@Param("studentId") Integer studentId);-->
<select id="getStudentById" resultType="student">
select * from t_student where student_id = #{studentId}
</select>
operation result:
You can see that the teacher attribute among the queried students is null. Why is this attribute null?
In the process of learning jdbc, we should have learned about obtaining the number of columns in the result set through the result set, and then looping through the result set to obtain the field names of the found data, such as:
After obtaining the field name, you can create an object through reflection and assign values to the object’s attributes. If the queried field name is consistent with the object’s attribute name, then you can obtain the attribute and assign a value to it. If not, you cannot assign a value. The value is null
What is also used in mybatis is to obtain attribute assignments through reflection, so the mapping between entity attributes and fields in the table is required.
There is such a global configuration in the mybatis-config configuration file:
Able to map database naming rules (_ in the middle) to object attribute naming rules (camel case)
Note: There is no teacher attribute in the data table, so the teacher attribute in the student needs to define its own mapping and use the
3. Research on one-to-many and many-to-one relationships
1. Many to one
If we want to query the information about students and their corresponding teachers based on student IDs, we need to map the teacher attribute. There are three methods:
(1) Cascade assignment
<!--Cascading assignment processing mapping-->
<resultMap id="map1" type="student">
<id column="student_id" property="studentId"/>
<result column="student_name" property="studentName"/>
<result column="age" property="age"/>
<result column="teacher_id" property="teacher.teacherId"/>
<result column="teacher_name" property="teacher.teacherName"/>
</resultMap>
<!--Student getStudentAndTeacher(@Param("studentId") Integer studentId);-->
<select id="getStudentAndTeacher1" resultMap="map1">
select * from t_students
left join t_teacher t
on s.teacher_id = t.teacher_id
where student_id = #{studentId}
</select>
Customize mapping map1 in the configuration file. The return type is student. Map the queried data to the properties of the object. The processing method for the teacher property is to map the corresponding fields to the teacher.teacherId and teacher.teacherName properties.
（2）association
<!--association processing mapping-->
<resultMap id="map2" type="student">
<id column="student_id" property="studentId"/>
<result column="student_name" property="studentName"/>
<result column="age" property="age"/>
<association property="teacher" javaType="teacher" >
<id column="teacher_id" property="teacherId" />
<result column="teacher_name" property="teacherName" />
</association>
</resultMap>
<!--Student getStudentAndTeacher2(@Param("studentId") Integer studentId);-->
<select id="getStudentAndTeacher2" resultMap="map2">
select * from t_students
left join t_teacher t
on s.teacher_id = t.teacher_id
where student_id = #{studentId}
</select>
When dealing with teachers, it is different from but similar to the cascade. The association tag maps the teacher attribute, and javaType is the type of the mapped attribute (here is the alias).
(3) Step-by-step query
Step-by-step query The first step of querying students based on student ID is written in the configuration file corresponding to the StudentMapper interface:
<!--Step-by-step query-->
<resultMap id="map3" type="student">
<id column="student_id" property="studentId"/>
<result column="student_name" property="studentName"/>
<result column="age" property="age"/>
<association property="teacher" fetchType="lazy"
select="com.wtp.mapper.TeacherMapper.getStudentAndTeacherStep2"
column="teacher_id"
/>
</resultMap>
<!--Student getStudentAndTeacherStep1(@Param("studentId") Integer studentId);-->
<select id="getStudentAndTeacherStep1" resultMap="map3">
select * from t_student where student_id = #{studentId}
</select>
The second step of step-by-step query is to query the teacher based on the teacher_id condition after the first step of query, and write it in the configuration file corresponding to the TeacherMapper interface:
<!--Teacher getStudentAndTeacherStep2(@Param("teacherId") Integer teacherId);-->
<select id="getStudentAndTeacherStep2" resultType="teacher">
select * from t_teacher where teacher_id = #{teacherId}
</select>
Step-by-step query also uses the association tag. Use the select attribute in the tag to select the full class name and method name of the next sql to be executed. Column is the query condition for the next sql.
Step-by-step query splits complex sql into simple sql for step-by-step execution. This has the advantage of lazy loading.
Lazy loading
When executing the SQL of step-by-step query, if the data found in the second step is not used, then only the SQL of the first step will be executed, reducing memory consumption.
If only the student ID is used before opening: 
After opening: 
After turning on lazy loading, if the data queried in the second step is not used, only one sql will be executed.
How to enable lazy loading
<!--Enable lazy loading--> <setting name="lazyLoadingEnabled" value="true"/> <!--Load on demand--> <setting name="aggressiveLazyLoading" value="false"/>
1. Global configuration in the mybatis-config file:
The lazy loading function is affected by two properties, lazyLoadingEnabled, aggressiveLazyLoading
lazyLoadingEnabled is set to true to enable lazy loading
The aggressiveLazyLoading attribute defaults to false. When set to true, two sql statements will be executed regardless of whether the data in the second query is used.
2. In the fetchType attribute in the step-by-step tag association and collection, set it to lazy to represent delayed loading, and set it to eager to represent immediate loading.
How to write comments
Annotations can also be divided into step-by-step and non-step-by-step
Non-step-by-step:
@Results(id = "studentMap",value = {
@Result(column="student_id", property="studentId"),
@Result(column="student_name", property="studentName"),
@Result(column="age", property="age"),
@Result(column="teacher_id", property="teacher.teacherId"),
@Result(column="teacher_name", property="teacher.teacherName")
})
@Select("select * from t_student s\\
" +
" left join t_teacher t\\
" +
" on s.teacher_id = t.teacher_id\\
" +
" where student_id = #{studentId}")
Student getStudentAndTeacher3(@Param("studentId") Integer studentId);
Step by step:
@Results(id = "studentMap",value = {
@Result(column="student_id", property="studentId"),
@Result(column="student_name", property="studentName"),
@Result(column="age", property="age"),
@Result(property="teacher" , column = "teacher_id",
one = @One(select = "com.wtp.mapper.TeacherMapper.getStudentAndTeacherStep2"))
})
@Select("select * from t_student where student_id = #{studentId}")
//Distributed query of students and teachers. The first step is to query students.
Student getStudentAndTeacherStep1(@Param("studentId") Integer studentId);
//Distributed query of students and teachers. The second step is to query the teachers associated with the students.
@Select("select * from t_teacher where teacher_id = #{teacherId}")
Teacher getStudentAndTeacherStep2(@Param("teacherId") Integer teacherId);
Among them, one = @One can be replaced by many=@many. The underlying source code of these two annotations is the same, but they have the same function and different flags. They are equivalent to @Service identifying the Service layer and @Controller identifying the Controller layer. The functions are to create objects and add them to the IOC container. The functions of these two annotations point to the next step of sql
2. One-to-many
Query a piece of teacher information based on the teacher ID. The teacher information corresponds to a collection of students, and it is necessary to map the student collection.
（1）collection
<resultMap id="map1" type="Teacher">
<id column="teacher_id" property="teacherId"/>
<result column="teacher_name" property="teacherName"/>
<collection property="students" ofType="student">
<id property="studentId" column="student_id"/>
<result property="studentName" column="student_name"/>
<result property="age" column="age"/>
</collection>
</resultMap>
<!--Teacher getTeacherAndStudentById1(@Param("teacherId") Integer teacherId);-->
<select id="getTeacherAndStudentById1" resultMap="map1">
select * from t_teacher t
left join t_student s
on t.teacher_id = s.teacher_id
where t.teacher_id = #{teacherId}
</select>
Collection is the parent interface of the collection type. This tag can handle the attributes of the collection type. In fact, association can also be replaced by the collection tag, which is equivalent to having only one element in the collection, but the collection tag cannot be replaced by the association tag.
(2) Step-by-step query
Written in the configuration file corresponding to the TeacherMapper interface, the first step is to query the teacher based on the teacher ID:
<!--Step-by-step query-->
<resultMap id="stepMap" type="teacher">
<id column="teacher_id" property="teacherId"/>
<result column="teacher_name" property="teacherName"/>
<collection property="students"
select="com.wtp.mapper.StudentMapper.getTeacherAndStudentStep2"
column="teacher_id"
/>
</resultMap>
<!--Teacher getTeacherAndStudentStep1(@Param("teacherId") Integer teacherId);-->
<select id="getTeacherAndStudentStep1" resultMap="stepMap" >
select * from t_teacher where teacher_id = #{teacherId}
</select>
The second step is written in the file corresponding to the StudentMapper interface to find the corresponding student based on the passed teacher ID.
<!--Student getTeacherAndStudentStep2(@Param("teacherId") Integer teacherId);-->
<select id="getTeacherAndStudentStep2" resultType="student">
select * from t_student where teacher_id = #{teacherId}
</select>
Annotations
Step by step: TeacherMapper:
@Results(id = "teacherMap",value = {
@Result(column="teacher_id", property="teacherId"),
@Result(column="teacher_name", property="teacherName"),
@Result(property="students" ,column = "teacher_id",
many = @Many(select = "com.wtp.mapper.StudentMapper.getTeacherAndStudentStep2"))
})
@Select("select * from t_teacher where teacher_id = #{teacherId}")
//Query teachers and students step by step. The first step is to query teachers.
Teacher getTeacherAndStudentStep1(@Param("teacherId") Integer teacherId);
StudentMapper:
@Select("select * from t_student where teacher_id = #{teacherId}")
//Query teachers and students step by step. The second step is to query the students associated with the teacher.
Student getTeacherAndStudentStep2(@Param("teacherId") Integer teacherId);
3. Many-to-many
From the perspective of one-party query, it is all one-to-many. One-to-many query has two methods: collection and step-by-step query. Only the sql statement becomes a three-table joint query
xml file configuration:
<?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.wtp.mapper.StudentAndLessonMapper">
<resultMap id="studentMap" type="student">
<id column="student_id" property="studentId"/>
<result column="student_name" property="studentName"/>
<result column="age" property="age"/>
<collection property="lessons" ofType="lesson">
<id property="lessonId" column="lesson_id"/>
<result property="lessonName" column="lesson_name"/>
</collection>
</resultMap>
<!--Query the corresponding course based on studentId-->
<!--Student getStudentById(@Param("studentId") Integer studentId);-->
<select id="getStudentById" resultMap="studentMap">
select * from t_student
left join student_lesson
on t_student.student_id = student_lesson.student_id
left join t_lesson
on student_lesson.lesson_id = t_lesson.lesson_id
where t_student.student_id = #{studentId}
</select>
<resultMap id="lessonMap" type="lesson">
<id column="lesson_id" property="lessonId"/>
<result column="lesson_name" property="lessonName"/>
<collection property="students" ofType="student">
<id property="studentId" column="student_id"/>
<result property="studentName" column="student_name"/>
<result property="age" column="age"/>
</collection>
</resultMap>
<!--Query corresponding students based on lessonId-->
<!--Student getLessonById(@Param("lessonId") Integer lessonId);-->
<select id="getLessonById" resultMap="lessonMap">
select * from t_lesson
left join student_lesson
on t_lesson.lesson_id = student_lesson.lesson_id
left join t_student
on student_lesson.student_id = t_student.student_id
where t_lesson.lesson_id = #{lessonId}
</select>
</mapper>
Methods in the interface and annotation queries in the interface:
package com.wtp.mapper;
import com.wtp.pojo.Lesson;
import com.wtp.pojo.Student;
import org.apache.ibatis.annotations.*;
import java.util.Collection;
import java.util.List;
import java.util.Set;
public interface StudentAndLessonMapper {
//Query students and courses based on studentId
Student getStudentById(@Param("studentId") Integer studentId);
//Query courses and selected students based on lessonId
Lesson getLessonById(@Param("lessonId") Integer lessonId);
//Step-by-step query in annotation mode
@Results(id = "studentMap2",value = {
@Result(column="student_id", property="studentId"),
@Result(column="student_name", property="studentName"),
@Result(column="age", property="age"),
@Result(property="lessons" ,column = "student_id",
many = @Many(select = "getStudentByIdStep2"))
})
@Select("select * from t_student where student_id = #{studentId}")
Student getStudentByIdStep1(@Param("studentId") Integer studentId);
//Check the courses in the intermediate table based on the student ID
@Select("select * from student_lesson sl left join t_lesson l " +
"on sl.lesson_id = l.lesson_id " +
" where sl.student_id = #{studentId}")
Lesson getStudentByIdStep2(@Param("studentId") Integer studentId);
}