This article introducesssm In framemybatis The problem of implementing multi table associated query. Major modificationsmapper.xml andEntity Define two places,mapper.xml Return format needs to be defined in, andEntity To define the mutual reference and other relations of related table classes in.

One, One to one Association

 1.1, Put forward demand

   According to classid Query class information( Information with teachers)

1.2, Create tables and data

   Create a teacher table and a class table, Here we assume that a teacher is only responsible for teaching one class, So the relationship between teachers and classes is one-to-one.


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(20),
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);
The relationship between the tables is as follows:

  

1.3, Defining entity classes

  1,Teacher class,Teacher Class isteacher Entity class corresponding to table.


package me.gacl.domain; /** * @author gacl * Definitionteacher Entity class corresponding to table */ public class
Teacher { // Defining the properties of an entity class, Andteacher Field correspondence in the table private int id; //id===>t_id private
String name; //name===>t_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,Classes class,Classes Class isclass Entity class corresponding to table


package me.gacl.domain; /** * @author gacl * Definitionclass Entity class corresponding to table */ public class
Classes { // Defining the properties of an entity class, Andclass Field correspondence in the table private int id; //id===>c_id private String
name; //name===>c_name /** * class There is one in the tableteacher_id field, So inClasses Classteacher attribute,
* Used for maintenanceteacher andclass One to one relationship between, Through thisteacher Attribute can tell which teacher is in charge of this class */ private Teacher
teacher; 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; } @Override
public String toString() { return "Classes [id=" + id + ", name=" + name + ",
teacher=" + teacher+ "]"; } }
     1.4, Definitionsql Mapping fileclassMapper.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"> <!--
For thismapper Specify a uniquenamespace,namespace The value of is customarily set to the package name+sql Map filename, This will ensurenamespace The value of is unique
for examplenamespace="me.gacl.mapping.classMapper" Namelyme.gacl.mapping( Package name)+classMapper(classMapper.xml File removal suffix)
--> <mapper namespace="me.gacl.mapping.classMapper"> <!-- According to classid Query class information( Information with teachers)
##1. Linked table query SELECT * FROM class c,teacher t WHERE c.teacher_id=t.t_id AND
c.c_id=1; ##2. Execute two queries SELECT * FROM class WHERE c_id=1; //teacher_id=1 SELECT *
FROM teacher WHERE t_id=1;// Use the aboveteacher_id --> <!--
One way: Nested results: Using nested result maps to handle a subset of duplicate union results Encapsulate the data of the join table query( Remove duplicate data) select * from class c,
teacher t where c.teacher_id=t.t_id and c.c_id=1 --> <select id="getClass"
parameterType="int" resultMap="ClassResultMap"> select * from class c, teacher
t where c.teacher_id=t.t_id and c.c_id=#{id} </select> <!--
UseresultMap Mapping one-to-one correspondence between entity classes and fields --> <resultMap type="me.gacl.domain.Classes"
id="ClassResultMap"> <id property="id" column="c_id"/> <result property="name"
column="c_name"/> <association property="teacher"
javaType="me.gacl.domain.Teacher"> <id property="id" column="t_id"/> <result
property="name" column="t_name"/> </association> </resultMap> <!--
Mode two: nested queries: By executing anotherSQL Mapping statements to return the expected complex type SELECT * FROM class WHERE c_id=1; SELECT *
FROM teacher WHERE t_id=1 //1 From the previous queryteacher_id Value --> <select id="getClass2"
parameterType="int" resultMap="ClassResultMap2"> select * from class where
c_id=#{id} </select> <!-- UseresultMap Mapping one-to-one correspondence between entity classes and fields --> <resultMap
type="me.gacl.domain.Classes" id="ClassResultMap2"> <id property="id"
column="c_id"/> <result property="name" column="c_name"/> <association
property="teacher" column="teacher_id" select="getTeacher"/> </resultMap>
<select id="getTeacher" parameterType="int"
resultType="me.gacl.domain.Teacher"> SELECT t_id id, t_name name FROM teacher
WHERE t_id=#{id} </select> </mapper>
stayconf.xml Registered in fileclassMapper.xml
<mappers> <!-- registerclassMapper.xml file,
classMapper.xml Be locatedme.gacl.mapping Under this package, thereforeresource finish writing sth.me/gacl/mapping/classMapper.xml
--> <mapper resource="me/gacl/mapping/classMapper.xml"/> </mappers>
1.5, Write unit test code
package me.gacl.test; import me.gacl.domain.Classes; import
me.gacl.util.MyBatisUtil; import org.apache.ibatis.session.SqlSession; import
org.junit.Test; public class Test3 { @Test public void testGetClass(){
SqlSession sqlSession = MyBatisUtil.getSqlSession(); /** * mappingsql Identity string for, *
me.gacl.mapping.classMapper yesclassMapper.xml In filemapper Labellingnamespace Attribute value, *
getClass yesselect Labellingid Attribute value, adoptselect Labellingid Property value to find theSQL */ String statement =
"me.gacl.mapping.classMapper.getClass";// mappingsql Identity string for
// Perform query operation, Automatically encapsulate query results intoClasses Object return Classes clazz =
sqlSession.selectOne(statement,1);// queryclass In the tableid by1 Records
// UseSqlSession ExecutionSQL Need to close afterSqlSession sqlSession.close();
System.out.println(clazz);// Print results:Classes [id=1, name=class_a, teacher=Teacher
[id=1, name=teacher1]] } @Test public void testGetClass2(){ SqlSession
sqlSession = MyBatisUtil.getSqlSession(); /** * mappingsql Identity string for, *
me.gacl.mapping.classMapper yesclassMapper.xml In filemapper Labellingnamespace Attribute value, *
getClass2 yesselect Labellingid Attribute value, adoptselect Labellingid Property value to find theSQL */ String statement =
"me.gacl.mapping.classMapper.getClass2";// mappingsql Identity string for
// Perform query operation, Automatically encapsulate query results intoClasses Object return Classes clazz =
sqlSession.selectOne(statement,1);// queryclass In the tableid by1 Records
// UseSqlSession ExecutionSQL Need to close afterSqlSession sqlSession.close();
System.out.println(clazz);// Print results:Classes [id=1, name=class_a, teacher=Teacher
[id=1, name=teacher1]] } }
1.6,MyBatis One to one association query summary

  MyBatis Use inassociation Tag to solve one-to-one associated query,association The following attributes are available for tags:

* property: The name of the object property
* javaType: Type of object property
* column: Corresponding foreign key field name
* select: Results encapsulated with another query
Two, One to many Association

2.1, Put forward demand

   according toclassId Query the corresponding class information, Including students, Teacher

2.2, Create tables and data

   In the above one-to-one association query demonstration, We have created class and teacher tables, So create another student table here
<> 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, Defining entity classes

  1,Student class


package me.gacl.domain; /** * @author gacl * Definitionstudent Entity class corresponding to table */ public class
Student { // Defining attributes, andstudent Field correspondence in the table private int id; //id===>s_id private String
name; //name===>s_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 "Student [id=" + id + ", name=" + name + "]"; } }
      2, modifyClasses class, Add oneList<Student>
students attribute, Use oneList<Student> Set attribute represents the students owned by the class, as follows:


package me.gacl.domain; import java.util.List; /** * @author gacl *
Definitionclass Entity class corresponding to table */ public class Classes { // Defining the properties of an entity class, Andclass Field correspondence in the table private int
id; //id===>c_id private String name; //name===>c_name /** *
class There is one in the tableteacher_id field, So inClasses Classteacher attribute, *
Used for maintenanceteacher andclass One to one relationship between, Through thisteacher Attribute can tell which teacher is in charge of this class */ private Teacher
teacher; // Use oneList<Student> Set attribute represents the students owned by the class 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 "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher + ",
students=" + students + "]"; } }
      2.4, modifysql Mapping fileclassMapper.xml

   Add the followingSQL Mapping information


<!-- according toclassId Query the corresponding class information, Including students, Teacher --> <!-- One way: Nested results: Using nested result maps to handle a subset of duplicate union results
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 --> <select id="getClass3" parameterType="int"
resultMap="ClassResultMap3"> 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} </select> <resultMap
type="me.gacl.domain.Classes" id="ClassResultMap3"> <id property="id"
column="c_id"/> <result property="name" column="c_name"/> <association
property="teacher" column="teacher_id" javaType="me.gacl.domain.Teacher"> <id
property="id" column="t_id"/> <result property="name" column="t_name"/>
</association> <!-- ofType Appointstudents Object types in Collections --> <collection
property="students" ofType="me.gacl.domain.Student"> <id property="id"
column="s_id"/> <result property="name" column="s_name"/> </collection>
</resultMap> <!-- Mode two: nested queries: By executing anotherSQL Mapping statements to return the expected complex type SELECT * FROM class WHERE
c_id=1; SELECT * FROM teacher WHERE t_id=1 //1 From the previous queryteacher_id Value SELECT *
FROM student WHERE class_id=1 //1 It's from the first queryc_id Value of field --> <select id="getClass4"
parameterType="int" resultMap="ClassResultMap4"> select * from class where
c_id=#{id} </select> <resultMap type="me.gacl.domain.Classes"
id="ClassResultMap4"> <id property="id" column="c_id"/> <result property="name"
column="c_name"/> <association property="teacher" column="teacher_id"
javaType="me.gacl.domain.Teacher" select="getTeacher2"></association>
<collection property="students" ofType="me.gacl.domain.Student" column="c_id"
select="getStudent"></collection> </resultMap> <select id="getTeacher2"
parameterType="int" resultType="me.gacl.domain.Teacher"> SELECT t_id id, t_name
name FROM teacher WHERE t_id=#{id} </select> <select id="getStudent"
parameterType="int" resultType="me.gacl.domain.Student"> SELECT s_id id, s_name
name FROM student WHERE class_id=#{id} </select>
      2.5, Write unit test code


package me.gacl.test; import me.gacl.domain.Classes; import
me.gacl.util.MyBatisUtil; import org.apache.ibatis.session.SqlSession; import
org.junit.Test; public class Test4 { @Test public void testGetClass3(){
SqlSession sqlSession = MyBatisUtil.getSqlSession(); /** * mappingsql Identity string for, *
me.gacl.mapping.classMapper yesclassMapper.xml In filemapper Labellingnamespace Attribute value, *
getClass3 yesselect Labellingid Attribute value, adoptselect Labellingid Property value to find theSQL */ String statement =
"me.gacl.mapping.classMapper.getClass3";// mappingsql Identity string for
// Perform query operation, Automatically encapsulate query results intoClasses Object return Classes clazz =
sqlSession.selectOne(statement,1);// queryclass In the tableid by1 Records
// UseSqlSession ExecutionSQL Need to close afterSqlSession sqlSession.close(); // Print results: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]]] System.out.println(clazz); } @Test public void
testGetClass4(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); /** *
mappingsql Identity string for, *
me.gacl.mapping.classMapper yesclassMapper.xml In filemapper Labellingnamespace Attribute value, *
getClass4 yesselect Labellingid Attribute value, adoptselect Labellingid Property value to find theSQL */ String statement =
"me.gacl.mapping.classMapper.getClass4";// mappingsql Identity string for
// Perform query operation, Automatically encapsulate query results intoClasses Object return Classes clazz =
sqlSession.selectOne(statement,1);// queryclass In the tableid by1 Records
// UseSqlSession ExecutionSQL Need to close afterSqlSession sqlSession.close(); // Print results: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]]] System.out.println(clazz); } }
      2.6,MyBatis One to many association query summary

  MyBatis Use incollection Tag to solve one to many associated queries,ofType Property specifies the object type of the element in the collection.