Article catalogue
1, Query requirements
(1) Associated query for three tables
- Class schedule
- Teacher table
- Student list
- A class is equipped with one teacher, and a class has multiple students
(2) Query class information by class number
- The inquired class is required to bring teacher information and student information of the class
(3) Query all class information
- The class information required to be queried only includes the teacher's information, not the student's information
2, Create database table
(1) Create teacher table
-
Execute SQL statement and create teacher table t_teacher
CREATE TABLE `t_teacher` ( `t_id` int(11) NOT NULL AUTO_INCREMENT, `t_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
Insert 3 records
INSERT INTO `t_teacher` VALUES ('1', 'Liu Xiaoyun'); INSERT INTO `t_teacher` VALUES ('2', 'Zheng Tonghua'); INSERT INTO `t_teacher` VALUES ('3', 'Ming Jun Li');
(2) Create class table
- Execute SQL statement to create class table t_class
CREATE TABLE `t_class` ( `c_id` int(11) NOT NULL AUTO_INCREMENT, `c_name` varchar(20) DEFAULT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`c_id`), KEY `fk_teacher_id` (`teacher_id`), CONSTRAINT `fk_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `t_teacher` (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
Insert 3 records
INSERT INTO `t_class` VALUES ('1', '2020 Software class 1', '3');
INSERT INTO `t_class` VALUES ('2', '2020 Software class 2', '2');
INSERT INTO `t_class` VALUES ('3', '2020 Software class 3', '1');
(3) Create student table
- Execute SQL statement to create student table t_student
CREATE TABLE `t_student` ( `s_id` int(11) NOT NULL AUTO_INCREMENT, `s_name` varchar(30) DEFAULT NULL, `s_gender` varchar(10) DEFAULT NULL, `s_age` int(11) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`s_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Execute SQL statement and insert 9 records
INSERT INTO `t_student` VALUES (1, 'Tong Dawei', 'male', 20, 1); INSERT INTO `t_student` VALUES (2, 'Li Yufang', 'female', 19, 1); INSERT INTO `t_student` VALUES (3, 'Zheng Daling', 'male', 19, 2); INSERT INTO `t_student` VALUES (4, 'Wen Xiaoling', 'male', 18, 1); INSERT INTO `t_student` VALUES (5, 'Wu Wenjing', 'female', 19, 2); INSERT INTO `t_student` VALUES (6, 'Xiao Wenyan', 'female', 18, 3); INSERT INTO `t_student` VALUES (7, 'Wen Jun Yang', 'male', 20, 3); INSERT INTO `t_student` VALUES (8, 'Tang Yuhan', 'female', 19, 2); INSERT INTO `t_student` VALUES (9, 'Jin Yuxing', 'female', 20, 2);
3, Create the entity class corresponding to the database table
(1) Create teacher entity class
package net.wh.mybatis.bean;
public class Teacher {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
(2) Create student entity class
package net.wh.mybatis.bean;
public class Student {
private int id;
private String name;
private String gender;
private int age;
private Clazz clazz;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Clazz getClazz() {
return clazz;
}
public void setClazz(Clazz clazz) {
this.clazz = clazz;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", age=" + age +
", clazz=" + clazz +
'}';
}
}
- The fifth field of the student table is class_id, but the fifth attribute of the student entity class is not the class number, but the class class (clazz), which has not been created at this time, so the code will report an error.
- Instead of outputting the complete class information through the class name in the class. CLAZ () method, it outputs the class information through the class name Getname() get.
(3) Create class entity class
package net.wh.mybatis.bean;
import java.util.List;
public class Clazz {
private int id;
private String name;
private Teacher teacher;
private List<Student> students;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Clazz{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher.getName() +
", students=" + students +
'}';
}
}
In the toString() method, the output of the teacher is not the complete information of the teacher entity class, but the name of the teacher through the teacher Getname() get.
4, Create class mapper profile
- Create the class mapper configuration file clazzmapper. In the resources/mapper directory xml
<?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="net.wh.mybatis.mapper.ClazzMapper">
<!--Three table Association query-->
<select id="findById" parameterType="int" resultMap="clazzResultMap">
SELECT * FROM t_teacher INNER JOIN t_class ON t_class.teacher_id = t_teacher.t_id
INNER JOIN t_student ON t_class.c_id = t_student.class_id WHERE c_id = #{id};
</select>
<!--Define class result mapping-->
<resultMap id="clazzResultMap" type="Clazz">
<result property="id" column="c_id"/>
<result property="name" column="c_name"/>
<!--one-on-one-->
<association property="teacher" column="teacher_id" javaType="Teacher">
<result property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
<!--One to many-->
<collection property="students" ofType="Student">
<result property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="gender" column="s_gender"/>
<result property="age" column="s_age"/>
<association property="clazz" column="class_id" javaType="Clazz">
<result property="name" column="c_name"/>
</association>
</collection>
</resultMap>
<!--###########################################################-->
<!--Realize one-to-one association through nested query-->
<select id="findAll" resultMap="clazzResultMap2">
SELECT * FROM t_class;
</select>
<!--Define class result mapping-->
<resultMap id="clazzResultMap2" type="Clazz">
<result property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
SELECT t_id id, t_name name FROM t_teacher WHERE t_id = #{id};
</select>
</mapper>
5, Modify MyBatis configuration file
- Configure class, teacher and student entity class aliases and class mapper configuration files in the configuration
6, Define class mapping interface ClazzMapper
package net.wh.mybatis.mapper;
import net.wh.mybatis.bean.Clazz;
import java.util.List;
public interface ClazzMapper {
Clazz findById(int id);
List<Clazz> findAll();
}
7, Create test class TestClazzMapper
package net.wh.mybatis.mapper;
import net.wh.mybatis.bean.Clazz;
import net.wh.mybatis.bean.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
public class TestClazzMapper {
private SqlSession sqlSession; // SQL session
private ClazzMapper clazzMapper; // Class mapper
@Before
public void init() {
try {
// Read MyBatis configuration file as character input stream
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
// Building SQL session factory based on MyBatis configuration file
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
// Obtain SQL session using SQL session factory
sqlSession = factory.openSession();
// Using SQL session to obtain class mapper object
clazzMapper = sqlSession.getMapper(ClazzMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testFindById() {
int id = 1;
Clazz clazz = clazzMapper.findById(id);
System.out.println(clazz);
System.out.println("Class number[" + id + "]Students:");
List<Student> students = clazz.getStudents();
students.forEach(student -> System.out.println(student));
}
@Test
public void testFindAll() {
List<Clazz> clazzes = clazzMapper.findAll();
clazzes.forEach(clazz -> System.out.println(clazz));
}
@After
public void destroy() {
// Close SQL session
sqlSession.close();
}
}
(1) Run the test method testFindById() to view the results
(2) Run the test method testFindAll() to view the results
ent -> System.out.println(student));
}
@Test
public void testFindAll() {
List<Clazz> clazzes = clazzMapper.findAll();
clazzes.forEach(clazz -> System.out.println(clazz));
}
@After
public void destroy() {
// Close SQL session
sqlSession.close();
}
}
## (1) Run the test method testFindById() to view the results [External chain picture transfer...(img-Likh7vD7-1621158583521)] ## (2) Run the test method testFindAll() to view the results 