MyBatis Study Summary (v)--Implement related table query

First, one to the other 1.1, put forward the demand

Query class information based on class ID (with teacher's information)

1.2. Create tables and data

Create a teacher's table and class table, here we assume that a teacher is only responsible for teaching a class, then the relationship between the teacher and the class is a one-off relationship.

Press CTRL + C to copy the code<textarea>CREATE TABLE Teacher (t_id INT PRIMARY KEY auto_increment, T_name VARCHAR (20)); CREATE TABLE Class (c_id int PRIMARY KEY auto_increment, C_name VARCHAR (), teacher_id int); ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher (t_id); Insert into teacher (t_name) VALUES (' Teacher1 '), insert into teacher (t_name) VALUES (' Teacher2 '), insert into class (C_name , teacher_id) VALUES (' Class_a ', 1), INSERT into Class (C_name, teacher_id) VALUES (' Class_b ', 2);</textarea>Press CTRL + C to copy the code

The relationships between tables are as follows:

　　

1.3. Defining entity Classes

1, teacher class, teacher class is the teacher table corresponding entity class.

1 package me.gacl.domain; 2  3/** 4  * @author gacl 5  * Define the entity class corresponding to the Teacher table 6  */7 public class Teacher {8  9     //define attributes for entity classes, with Teache The fields in the R table correspond to the ten     private int IDs;            Id===>t_id11     private String name;    Name===>t_name12 public     int getId () {         setId return id;15}16 ~ public     void 18< (int id) C14/>this.id = id;19     }20 public     String getName () {         return name;23}24 public     void SetName (string name) {         this.name = name;27     }28 @Override30 public     String toString () {         Return "Teacher [id=" + ID + ", name=" + name + "]";     }33}

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

 1 package me.gacl.domain; 2 3/** 4 * @author GACL 5 * defines the entity class corresponding to class table 6 */7 public class Classes {8 9//define attributes for entity classes, corresponding to the fields in the class table Priv            ate int id;    Id===>c_id11 private String name; Name===>c_name12 the/**14 * Class table has a teacher_id field, so define a teacher attribute in the classes class, 15 * for maintaining teacher and class         A one-to-ones relationship, through this Teacher property can know which teacher is responsible for the class */17 private Teacher teacher;18 public int getId () {20 Return id;21}22-public void setId (int id) {this.id = id;25}26 The public String getName () { name;29}30 to public void SetName (String name) {this.name = name;33}34 P         Ublic Teacher Getteacher () {}38 return teacher;37 setteacher, public void (Teacher Teacher) {40  This.teacher = teacher;41}42 @Override44 public String toString () {return ' Classes [id= ' + ID + ", name=" + name + ", teacher=" + teacher+ "]";46}47} 

1.4. Define SQL Mapping File Classmapper.xml

 1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <!-- Specifying a unique Namespace,namespace value for this mapper is customarily set to the package name +sql the map file name, so that the value of namespace is guaranteed to be unique 4 for example namespace= " Me.gacl.mapping.classMapper "is me.gacl.mapping (package name) +classmapper (classmapper.xml file removal suffix) 5--6 <mapper Namespace= "Me.gacl.mapping.classMapper" > 7 8 <!--9 Query class information (with teacher's information) based on class ID # #1. The table query is a SELECT * from class C,teacher T WHERE c.teacher_id=t.t_id and c.c_id=1;12 # #2.  Perform two queries from the class WHERE c_id=1; teacher_id=115 SELECT * FROM teacher WHERE t_id=1;//uses the above obtained TEACHER_ID16-->17 <!--19 Way One: Nested results: Using nested result mappings to process subsets of duplicate Union results 20 encapsulates data from a table query (removing duplicate data). 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 "&GT;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 one by one correspondence between entity 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 <as              Sociation 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 <!--37 Way two: Nested query: Returns the expected complex type by executing another SQL mapping statement. SELECT * FROM class WHERE c_id=1;39 select * F ROM teacher WHERE t_id=1//1 is the teacher_id value from the previous query.-->41 <select id= "GetClass2" parametertype= "int" res ultmap= "CLASSRESULTMAP2" >42 select * from class where c_id=#{id}43 </select>44 <!--using result Map maps the one by one correspondence between entity classes and fields--&Gt;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=" Teac her_id "select=" Getteacher "/>49 </resultmap>50 wuyi <select id=" Getteacher "parametertype=" int "R Esulttype= "Me.gacl.domain.Teacher" >52 select t_id ID, t_name name from Teacher WHERE t_id=#{id}53 </sel ect>54 </mapper>

Registering in the Conf.xml file Classmapper.xml

<mappers>        <!--register classmapper.xml file,         Classmapper.xml is located under Me.gacl.mapping This package, so resource written 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 @Test11 public void Testgetclass () {sqlsession sqlsession = Mybatisut Il.getsqlsession (); 13/**14 * Mapping SQL identification String, * Me.gacl.mapping.classMapper is the Classmapper.xml file in the map The value of the namespace property of the per tag, where * GetClass is the id attribute value of the Select tag, the ID property value of the Select tag can be found to perform the SQL17 */18 String Stat ement = "Me.gacl.mapping.classMapper.getClass";//Map SQL identification string 19//Execute query operation, automatically encapsulate query result as Classes object return to Classes CL Azz = Sqlsession.selectone (statement,1);//query for records with ID 1 in class table 21//You need to close SqlSession22 sqlses after executing SQL with sqlsession Sion.close (); System.out.println (clazz);//Print Result: Classes [id=1, Name=class_a, Teacher=teacher [Id=1, Name=teacher1 ]]24}25 @Test27 public void TestGetClass2 () {sqlsession sqlsession = MYbatisutil.getsqlsession (); 29/**30 * Mapped SQL identification string, * Me.gacl.mapping.classMapper is classmapper.x The value of the namespace property of the mapper tag in the ML file, and the value of the id attribute of the Select tag is GetClass2, and the value of the id attribute of the Select tag can be found to perform the SQL33 */34 St         Ring statement = "ME.GACL.MAPPING.CLASSMAPPER.GETCLASS2";//Map SQL Identification string 35//Execute query operation, automatically encapsulate query result as classes object return 36         Classes clazz = Sqlsession.selectone (statement,1);//query for records with ID 1 in class Table 37//You need to close sqlsession after executing SQL with SqlSession38 Sqlsession.close (); System.out.println (clazz);//Print Result: Classes [id=1, Name=class_a, Teacher=teacher [id=1, Nam E=teacher1]]40}41}

1.6, MyBatis one-to-one correlation query summary

MyBatis uses association tags to resolve one-to-one correlation queries, association tags are available with the following properties:

• Property: The name of the object attribute
• Javatype: Type of object property
• Column: The foreign key field name that corresponds to
• Select: Use another query to encapsulate the results

Two or one-to-many correlation 2.1, ask for demand

According to ClassID query the corresponding class information, including students, teachers

2.2. Create tables and data

In the one-to-one correlation query demo above, we've created class tables and teacher tables, so we'll create a student table here.

CREATE TABLE Student (    s_id int PRIMARY KEY auto_increment,     s_name VARCHAR (),     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); I Nsert into student (S_name, class_id) VALUES (' Student_e ', 2); INSERT into student (S_name, class_id) VALUES (' Student_f ', 2) ;

　　

2.3. Defining entity Classes

1, Student class

1 package me.gacl.domain; 2  3/** 4  * @author gacl 5  * Define the entity class corresponding to the Student table 6  */7 public class Student {8  9     //define properties, and Student table The field corresponds to the     private int id;            Id===>s_id11     private String name;    Name===>s_name12     public     int getId () {         id;15}16, public     void setId (int id) {         this.id = id;19     }20 public     String getName () {         return name;23     }24 public void SetName (string name) {         this.name = name;27     }28 @Override30 public     String toString () {         Return "Student [id=" + ID + ", name=" + name + "]";     }33}

2, modify the Classes class, add a List<student> students property, use a List<student> collection property to represent the class-owned students, as follows:

 1 package me.gacl.domain; 2 3 Import java.util.List; 4 5/** 6 * @author GaCl 7 * Defines the entity class corresponding to class table 8 */9 public class Classes {10 11//define attributes for entity classes, corresponding to the fields in the class table Priv            ate int id;    ID===&GT;C_ID13 private String name; Name===>c_name14 the/**16 * Class table has a teacher_id field, so define a teacher attribute in the classes class, 17 * for maintaining teacher and class A one to one relationship, through this Teacher property can know which teacher is responsible for the class */19 private Teacher teacher;20//Use a list<student> set property to indicate class Some students are private list<student> students;22-public int getId () {}26 return id;25 Publi  c void setId (int id) {this.id = id;29}30 to public String GetName () {return name;33}34         40 public void SetName (String name) {this.name = name;37}38 The public Teacher getteacher () return teacher;41}42 public void Setteacher (teacher teacher) {this.teacher = teacher;45} list& PublicLt Student> getstudents () {students;49}50, public void setstudents (list<student> studen TS) {this.students = students;53}54 @Override56 public String toString () {# # return ' Cl  Asses [id= "+ ID +", name= "+ name +", teacher= "+ teacher58 +", students= "+ students +"] "; 59}60 }

2.4. ModificationSQL mapping file Classmapper.xml

Add the following SQL mapping information

 1 <!--2 According to ClassID query the corresponding class information, including students, teachers 3--and 4 <!--5 Way one: nested results: Using nested result mappings to handle a subset of duplicate 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 t Ype= "me.gacl.domain.Classes" id= "ClassResultMap3" >12 <id property= "id" column= "c_id"/>13 <resu Lt 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 collection-->19 <collection property= "Students" OftypE= "Me.gacl.domain.Student" >20 <id property= "id" column= "s_id"/>21 <result property= "NA Me "column=" S_name "/>22 </collection>23 </resultmap>24 <!--26 Way two: Nested query: Pass   Executes another SQL mapping statement to return the expected complex type, select * from class where c_id=1;28 select * from teacher where t_id=1      1 is the value of the teacher_id that was obtained from the previous query. SELECT * FROM student WHERE class_id=1//1 is the value of the c_id field obtained from the first query-->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 p roperty= "id" column= "c_id"/>36 <result property= "name" column= "C_name"/>37 <association proper         ty= "Teacher" column= "teacher_id" javatype= "Me.gacl.domain.Teacher" select= "GetTeacher2" ></association>38 <collection property="Students" oftype= "Me.gacl.domain.Student" column= "c_id" select= "getstudent" ></collection>39 </         resultmap>40 <select id= "getTeacher2" parametertype= "int" resulttype= "Me.gacl.domain.Teacher" >42 SELECT t_id ID, t_name name from teacher WHERE t_id=#{id}43 </select>44 <select "id=  Dent "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 @Test11 public void TestGetClass3 () {sqlsession sqlsession = Mybatisu Til.getsqlsession (); 13/**14 * Mapped SQL identification string, * Me.gacl.mapping.classMapper is classmapper.xml file in MA The value of the namespace property of the Pper tag, where * GETCLASS3 is the id attribute value of the Select tag, the id attribute value of the Select tag can be found to perform the SQL17 */18 String St  Atement = "ME.GACL.MAPPING.CLASSMAPPER.GETCLASS3";//Mapping SQL identification string 19//Execute query operation, automatically encapsulate query result as Classes object return to the Classes Clazz = Sqlsession.selectone (statement,1);//query for records with ID 1 in class table 21//You need to close SQLSESSION22 SQL after executing SQL with sqlsession Session.close (); 23//Print Result: Classes [id=1, Name=class_a, Teacher=teacher [Id=1, Name=teacher1], students=[student [i D=1, Name=student_a], student [id=2, Name=student_b], student [id=3, name=student_c]]]24 SysTem.out.println (clazz)}26 @Test28 public void TestGetClass4 () {sqlsession sqlsession = My Batisutil.getsqlsession (); 30/**31 * Mapped SQL identification string, * Me.gacl.mapping.classMapper is CLASSMAPPER.XM The value of the namespace property of the mapper tag in the file, where * GETCLASS4 is the id attribute value of the Select tag, the id attribute value of the Select tag can be found to perform the SQL34 */35 Str ing statement = "ME.GACL.MAPPING.CLASSMAPPER.GETCLASS4";//Mapping SQL Identification string 36//Execute query operation, automatically encapsulate query result as classes object return PNS C         Lasses clazz = Sqlsession.selectone (statement,1);//query for records with ID 1 in class Table 38//You need to close sqlsession after executing SQL with SqlSession39 Sqlsession.close (); 40//Print Result: Classes [id=1, Name=class_a, Teacher=teacher [Id=1, Name=teacher1], Students=[stud ent [Id=1, Name=student_a], student [id=2, Name=student_b], student [id=3, name=student_c]]]41 System.out.println ( Clazz); 42}43}

2.6, MyBatis one-to-many correlation query summary

The collection tag is used in MyBatis to resolve a one-to-many association query, and the OfType property specifies the object type of the element in the collection.

Http://www.cnblogs.com/xdp-gacl/p/4264440.html

MyBatis Study Summary (v)--Implement related table query