Mybatis associated Table query, Mybatis associated table

Mybatis associated Table query, Mybatis associated table
1. One-to-one association 1.1.

Query Class information by class id (with Teacher Information)

1.2 create tables and data

Create a teacher table and a class table. Assume that a teacher is only responsible for teaching one class, the relationship between the teacher and the class is one-to-one.

 1 CREATE TABLE teacher( 2     t_id INT PRIMARY KEY AUTO_INCREMENT,  3     t_name VARCHAR(20) 4 ); 5 CREATE TABLE class( 6     c_id INT PRIMARY KEY AUTO_INCREMENT,  7     c_name VARCHAR(20),  8     teacher_id INT 9 );10 ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);    11 12 INSERT INTO teacher(t_name) VALUES('teacher1');13 INSERT INTO teacher(t_name) VALUES('teacher2');14 15 INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);16 INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

The relationship between tables is as follows:

　　

1.3 define object classes

1. Teacher class. The Teacher class is the entity class corresponding to the teacher table.

1 package me. gacl. domain; 2 3/** 4 * @ author gacl 5 * defines the object class 6 */7 public class teacher {8 9 // defines the attributes of the object class, 10 private int id corresponding to the field in the teacher table; // id ==> t_id11 private String name; // name ==> t_name12 13 public int getId () {14 return id; 15} 16 17 public void setId (int id) {18 this. id = id; 19} 20 21 public String getName () {22 return name; 23} 24 25 public void setName (String name) {26 this. name = name; 27} 28 29 @ Override30 public String toString () {31 return "Teacher [id =" + id + ", name =" + name + "]"; 32} 33}

2. Classes class. The Classes class is the entity class corresponding to the class table.

1 package me. gacl. domain; 2 3/** 4 * @ author gacl 5 * defines the object class 6 */7 public class Classes {8 9 // defines the attributes of the object class, 10 private int id corresponding to the fields in the class table; // id ==> c_id11 private String name; // name ==> c_name12 13/** 14 * class table has a teacher_id field. Therefore, a teacher attribute is defined in the Classes class, 15 * It is used to maintain the one-to-one relationship between the teacher and the class. With this teacher attribute, we can know which Teacher is responsible for this class 16 */17 private teacher; 18 19 public int getId () {20 return id; 21} 22 23 public void setId (int id) {24 this. id = id; 25} 26 27 public String getName () {28 return name; 29} 30 31 public void setName (String name) {32 this. name = name; 33} 34 35 public Teacher getTeacher () {36 return teacher; 37} 38 39 public void setTeacher (Teacher teacher) {40 this. teacher = teacher; 41} 42 43 @ Override44 public String toString () {45 return "Classes [id =" + id + ", name =" + name + ", teacher = "+ teacher +"] "; 46} 47

1.4 define the SQL ing file classMapper. xml

1 <? Xml version = "1.0" encoding = "UTF-8"?> 2 <! DOCTYPE mapper PUBLIC "-// mybatis.org//DTD Mapper 3.0 //" http://mybatis.org/dtd/mybatis-3-mapper.dtd "> 3 <! -- Specify a unique namespace for this mapper. Set the namespace value to the package name + SQL ing file name. This ensures that the namespace value is unique. For example, namespace = "me. gacl. mapping. classMapper "is me. gacl. mapping (package name) + classMapper (classMapper. remove the Suffix from the xml file) 5 --> 6 <mapper namespace = "me. gacl. mapping. classMapper "> 7 8 <! -- 9 query the class information based on the class id (with the Teacher's Information) 10 ## 1. join table Query 11 SELECT * FROM class c, teacher t WHERE c. teacher_id = t. t_id AND c. c_id = 1; 12 13 #2. execute two queries: 14 SELECT * FROM class WHERE c_id = 1; // teacher_id = 115 SELECT * FROM teacher WHERE t_id = 1; // use the preceding teacher_id16 --> 17 18 <! -- 19 Method 1: Nested results: Use nested result ing to process the subset of duplicate Union Results 20 encapsulate the data queried in the join table (remove duplicate data) 21 select * from class c, teacher t where c. teacher_id = t. t_id and c. c_id = 122 --> 23 <select id = "getClass" parameterType = "int" resultMap = "ClassResultMap"> 24 select * from class c, teacher t where c. teacher_id = t. t_id and c. c_id = # {id} 25 </select> 26 <! -- Use resultMap to map the one-to-one correspondence between object classes and fields --> 27 <resultMap type = "me. gacl. domain. classes "id =" ClassResultMap "> 28 <id property =" id "column =" c_id "/> 29 <result property =" name "column =" c_name "/> 30 <association property = "teacher" javaType = "me. gacl. domain. teacher "> 31 <id property =" id "column =" t_id "/> 32 <result property =" name "column =" t_name "/> 33 </association> 34 </resultMap> 35 36 <! -- 37 Method 2: Nested query: return the expected complex type by executing another SQL ing statement 38 SELECT * FROM class WHERE c_id = 1; 39 SELECT * FROM teacher WHERE t_id = 1 // 1 is the teacher_id value obtained FROM the previous query 40 --> 41 <select id = "getClass2" parameterType = "int" resultMap =" classResultMap2 "> 42 select * from class where c_id = # {id} 43 </select> 44 <! -- Use resultMap to map the one-to-one correspondence between object classes and fields --> 45 <resultMap type = "me. gacl. domain. classes "id =" ClassResultMap2 "> 46 <id property =" id "column =" c_id "/> 47 <result property =" name "column =" c_name "/> 48 <association property = "teacher" column = "teacher_id" select = "getTeacher"/> 49 </resultMap> 50 51 <select id = "getTeacher" parameterType = "int" resultType =" me. gacl. domain. teacher "> 52 SELECT t_id id, t_name name FROM teacher WHERE t_id =#{ id} 53 </select> 54 55 </mapper>

Register classMapper. xml in the conf. xml file

<Mappers> <! -- Register classMapper. xml file, classMapper. xml is located in me. gacl. in the mapping package, the resource is written as me/gacl/mapping/classMapper. xml --> <mapper resource = "me/gacl/mapping/classMapper. xml "/> </mappers>

1.5 write unit test code

1 package me. gacl. test; 2 3 import me. gacl. domain. classes; 4 import me. gacl. util. myBatisUtil; 5 import org. apache. ibatis. session. sqlSession; 6 import org. junit. test; 7 8 public class Test3 {9 10 @ Test11 public void testGetClass () {12 SqlSession sqlSession = MyBatisUtil. getSqlSession (); 13/** 14 * ing the SQL identity string, 15 * me. gacl. mapping. classMapper is classMapper. the namespace attribute value of the ER er tag in the xml file. 16 * getClass is the id attribute value of the select tag, you can find the SQL17 */18 String statement = "me. gacl. mapping. classMapper. getClass "; // ing the SQL identifier string 19 // execute the query operation. The query result is automatically encapsulated into a Classes object and 20 Classes clazz = sqlSession is returned. selectOne (statement, 1); // query records with id 1 in the class Table 21 // After SQL session is executed, You need to disable SqlSession22 SqlSession. close (); 23 System. out. println (clazz); // print the result: Classes [id = 1, name = class_a, teacher = Teacher [id = 1, name = teacher1] 24} 25 26 @ Test27 public void testGetClass2 () {28 SqlSession sqlSession = MyBatisUtil. getSqlSession (); 29/** 30 * ing SQL ID string, 31 * me. gacl. mapping. classMapper is classMapper. the namespace attribute value of the ER er tag in the xml file. 32 * getClass2 is the id attribute value of the select tag, you can find the SQL33 */34 String statement = "me. gacl. mapping. classMapper. getClass2 "; // ing the SQL ID string 35 // perform the query operation, and automatically encapsulate the query results into a Classes object to return 36 Classes clazz = sqlSession. selectOne (statement, 1); // query records with id 1 in the class Table 37 // After SQL session is executed, You need to disable SqlSession38 SqlSession. close (); 39 System. out. println (clazz); // print the result: Classes [id = 1, name = class_a, teacher = Teacher [id = 1, name = teacher1] 40} 41}

1.6. MyBatis one-to-one association query Summary

In MyBatis, the association tag is used to solve one-to-one association queries. The available attributes of the association tag are as follows:

• Property: name of the Object property
• JavaType: object property type
• Column: name of the corresponding foreign key field
• Select: Results encapsulated by another query

2. One-to-Multiple Association 2.1.

Query the corresponding class information based on classId, including students and teachers.

2.2 create tables and data

In the previous one-to-one association query demonstration, we have created a class table and a instructor table, so here we create another student table.

CREATE TABLE student(    s_id INT PRIMARY KEY AUTO_INCREMENT,     s_name VARCHAR(20),     class_id INT);INSERT INTO student(s_name, class_id) VALUES('student_A', 1);INSERT INTO student(s_name, class_id) VALUES('student_B', 1);INSERT INTO student(s_name, class_id) VALUES('student_C', 1);INSERT INTO student(s_name, class_id) VALUES('student_D', 2);INSERT INTO student(s_name, class_id) VALUES('student_E', 2);INSERT INTO student(s_name, class_id) VALUES('student_F', 2);

　　

2.3 define object classes

1. Student Class

1 package me. gacl. domain; 2 3/** 4 * @ author gacl 5 * defines the object class 6 */7 public class student {8 9 // defines the attribute, 10 private int id corresponding to the field in the student table; // id ==> s_id11 private String name; // name ==> s_name12 13 public int getId () {14 return id; 15} 16 17 public void setId (int id) {18 this. id = id; 19} 20 21 public String getName () {22 return name; 23} 24 25 public void setName (String name) {26 this. name = name; 27} 28 29 @ Override30 public String toString () {31 return "Student [id =" + id + ", name =" + name + "]"; 32} 33}

2. Modify the Classes Class, add a List <Student> students attribute, and use a List <Student> set attribute to indicate the students in the class, as shown below:

1 package me. gacl. domain; 2 3 import java. util. list; 4 5/** 6 * @ author gacl 7 * defines the object class corresponding to the class Table 8 */9 public class Classes {10 11 // defines the attributes of the object class, 12 private int id corresponding to the field in the class table; // id ==> c_id13 private String name; // name ==> c_name14 15/** 16 * class table has a teacher_id field. Therefore, a teacher attribute is defined in the Classes class, 17 * It is used to maintain the one-to-one relationship between the teacher and the class. With this teacher attribute, we can know which Teacher is responsible for this class's 18 */19 private teacher; 20 // use a List <Student> set attribute to indicate the class's students 21 private List <Student> students; 22 23 public int getId () {24 return id; 25} 26 27 public void setId (int id) {28 this. id = id; 29} 30 31 public String getName () {32 return name; 33} 34 35 public void setName (String name) {36 this. name = name; 37} 38 39 public Teacher getTeacher () {40 return teacher; 41} 42 43 public void setTeacher (Teacher teacher) {44 this. teacher = teacher; 45} 46 47 public List <Student> getStudents () {48 return students; 49} 50 51 public void setStudents (List <Student> students) {52 this. students = students; 53} 54 55 @ Override56 public String toString () {57 return "Classes [id =" + id + ", name =" + name + ", teacher = "+ teacher58 +", students = "+ students +"] "; 59} 60}

2.4 modify the SQL ing file classMapper. xml.

Add the following SQL ing information

1 <! --

2. query the corresponding class information based on classId, including students and instructors 3 --> 4 <! -- 5 Method 1: Nested results: Use nested result ing to process a subset of repeated Union results 6 SELECT * FROM class c, teacher t, student s WHERE c. teacher_id = t. t_id AND c. c_id = s. class_id AND c. c_id = 1 7 --> 8 <select id = "getClass3" parameterType = "int" resultMap = "ClassResultMap3"> 9 select * from class c, teacher t, student s where c. teacher_id = t. t_id and c. c_id = s. class_id and c. c_id = # {id} 10 </select> 11 <resultMap type = "me. gacl. domain. classes "id =" ClassRes UltMap3 "> 12 <id property =" id "column =" c_id "/> 13 <result property =" name "column =" c_name "/> 14 <association property =" teacher "column =" teacher_id "javaType =" me. gacl. domain. teacher "> 15 <id property =" id "column =" t_id "/> 16 <result property =" name "column =" t_name "/> 17 </association> 18 <! -- OfType specifies the object type in the students set --> 19 <collection property = "students" ofType = "me. gacl. domain. student "> 20 <id property =" id "column =" s_id "/> 21 <result property =" name "column =" s_name "/> 22 </collection> 23 </resultMap> 24 25 <! -- 26 Method 2: Nested query: return the expected complex type 27 SELECT * FROM class WHERE c_id = 1 by executing another SQL ing statement; 28 SELECT * FROM teacher WHERE t_id = 1 // 1 is the teacher_id value obtained FROM the previous query 29 SELECT * FROM student WHERE class_id = 1 // 1 is the c_id obtained FROM the first query field Value 30 --> 31 <select id = "getClass4" parameterType = "int" resultMap = "ClassResultMap4"> 32 select * from class where c_id = # {id} 33 </ select> 34 <resultMap type = "me. gacl. domain. classes "id =" ClassResultMap4 "> 35 <id property =" id "column =" c_id "/> 36 <result property =" name "column =" c_name "/> 37 <association property = "teacher" column = "teacher_id" javaType = "me. gacl. domain. teacher "select =" getteacher "> </association> 38 <collection property =" students "ofType =" me. gacl. domain. student "column =" c_id "select =" getStudent "> </collection> 39 </resultMap> 40 41 <select id =" getteacsp2 "parameterType =" int "resultType =" me. gacl. domain. teacher "> 42 SELECT t_id id, t_name name FROM teacher WHERE t_id = # {id} 43 </select> 44 45 <select id = "getStudent" parameterType = "int" resultType = "me. gacl. domain. student "> 46 SELECT s_id id, s_name name FROM student WHERE class_id =#{ id} 47 </select>

2.5 write unit test code

1 package me. gacl. test; 2 3 import me. gacl. domain. classes; 4 import me. gacl. util. myBatisUtil; 5 import org. apache. ibatis. session. sqlSession; 6 import org. junit. test; 7 8 public class Test4 {9 10 @ Test11 public void testGetClass3 () {12 SqlSession sqlSession = MyBatisUtil. getSqlSession (); 13/** 14 * ing the SQL identity string, 15 * me. gacl. mapping. classMapper is classMapper. the namespace attribute value of the ER er tag in the xml file. 16 * getClass3 is the id attribute value of the select tag, you can find the SQL17 */18 String statement = "me. gacl. mapping. classMapper. getClass3 "; // ing the SQL ID string 19 // perform the query operation, and automatically encapsulate the query results into a Classes object to return 20 Classes clazz = sqlSession. selectOne (statement, 1); // query records with id 1 in the class Table 21 // After SQL session is executed, You need to disable SqlSession22 SqlSession. close (); 23 // print the result: Classes [id = 1, name = class_a, teacher = Teacher [id = 1, name = teacher1], students = [Student [id = 1, name = student_A], Student [id = 2, name = student_ B], Student [id = 3, name = student_C] 24 System. out. println (clazz); 25} 26 27 @ Test28 public void testGetClass4 () {29 SqlSession sqlSession = MyBatisUtil. getSqlSession (); 30/** 31 * ing the SQL identifier string, 32 * me. gacl. mapping. classMapper is classMapper. the namespace attribute value of the ER er tag in the xml file. 33 * getClass4 is the id attribute value of the select tag, you can find the SQL34 */35 String statement = "me. gacl. mapping. classMapper. getClass4 "; // ing the SQL identifier string 36 // perform the query operation, and automatically encapsulate the query result into a Classes object to return 37 Classes clazz = sqlSession. selectOne (statement, 1); // query records with id 1 in the class Table 38 // After SQL session is executed, You need to disable SqlSession39 SqlSession. close (); 40 // print the result: Classes [id = 1, name = class_a, teacher = Teacher [id = 1, name = teacher1], students = [Student [id = 1, name = student_A], Student [id = 2, name = student_ B], Student [id = 3, name = student_C] 41 System. out. println (clazz); 42} 43}