This article introduces ssm In frame mybatis The problem of implementing multi table associated query . Major modifications mapper.xml and Entity Define two places ,mapper.xml Return format needs to be defined in , and Entity To define the mutual reference and other relations of related table classes in .

One , One to one Association

 1.1, Put forward requirements

   According to the class id 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 is teacher Entity class corresponding to table .


package me.gacl.domain; /** * @author gacl * definition teacher Entity class corresponding to table */ public class
Teacher { // Defining the properties of an entity class , And teacher 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 is class Entity class corresponding to table


package me.gacl.domain; /** * @author gacl * definition class Entity class corresponding to table */ public class
Classes { // Defining the properties of an entity class , And class Field correspondence in the table private int id; //id===>c_id private String
name; //name===>c_name /** * class There is one in the table teacher_id field , So in Classes Class teacher attribute ,
* For maintenance teacher and class One to one relationship between , Through this teacher 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, definition sql Mapping files classMapper.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 this mapper Specify a unique namespace,namespace The value of is customarily set to the package name +sql Map filename , This will ensure namespace The value of is unique
for example namespace="me.gacl.mapping.classMapper" namely me.gacl.mapping( Package name )+classMapper(classMapper.xml File removal suffix )
--> <mapper namespace="me.gacl.mapping.classMapper"> <!-- According to the class id Query class information ( Information with teachers )
##1. Joint 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 above teacher_id --> <!--
Mode 1 : 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> <!--
use resultMap 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 2 : nested queries : By executing another SQL 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 query teacher_id Value of --> <select id="getClass2"
parameterType="int" resultMap="ClassResultMap2"> select * from class where
c_id=#{id} </select> <!-- use resultMap 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>
stay conf.xml Registered in file classMapper.xml
<mappers> <!-- register classMapper.xml file ,
classMapper.xml be located me.gacl.mapping Under this bag , therefore resource 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(); /** * mapping sql Identity string for , *
me.gacl.mapping.classMapper yes classMapper.xml In file mapper Tagged namespace Value of property , *
getClass yes select Tagged id Property value , adopt select Tagged id Property value to find the SQL */ String statement =
"me.gacl.mapping.classMapper.getClass";// mapping sql Identity string for
// Perform query operation , Automatically encapsulate query results into Classes Object return Classes clazz =
sqlSession.selectOne(statement,1);// query class In the table id by 1 Records of
// use SqlSession End of execution SQL Need to close after SqlSession 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(); /** * mapping sql Identity string for , *
me.gacl.mapping.classMapper yes classMapper.xml In file mapper Tagged namespace Value of property , *
getClass2 yes select Tagged id Property value , adopt select Tagged id Property value to find the SQL */ String statement =
"me.gacl.mapping.classMapper.getClass2";// mapping sql Identity string for
// Perform query operation , Automatically encapsulate query results into Classes Object return Classes clazz =
sqlSession.selectOne(statement,1);// query class In the table id by 1 Records of
// use SqlSession End of execution SQL Need to close after SqlSession 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 Used in association 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 requirements

   according to classId 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 * definition student Entity class corresponding to table */ public class
Student { // Defining properties , and student 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, modify Classes class , Add a List<Student>
students attribute , Use a List<Student> Set attribute represents the students owned by the class , as follows :


package me.gacl.domain; import java.util.List; /** * @author gacl *
definition class Entity class corresponding to table */ public class Classes { // Defining the properties of an entity class , And class Field correspondence in the table private int
id; //id===>c_id private String name; //name===>c_name /** *
class There is one in the table teacher_id field , So in Classes Class teacher attribute , *
For maintenance teacher and class One to one relationship between , Through this teacher Attribute can tell which teacher is in charge of this class */ private Teacher
teacher; // Use a List<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, modify sql Mapping files classMapper.xml

   Add the following SQL Mapping information


<!-- according to classId Query the corresponding class information , Including students , teacher --> <!-- Mode 1 : 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 appoint students 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 2 : nested queries : By executing another SQL 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 query teacher_id Value of SELECT *
FROM student WHERE class_id=1 //1 It's from the first query c_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(); /** * mapping sql Identity string for , *
me.gacl.mapping.classMapper yes classMapper.xml In file mapper Tagged namespace Value of property , *
getClass3 yes select Tagged id Property value , adopt select Tagged id Property value to find the SQL */ String statement =
"me.gacl.mapping.classMapper.getClass3";// mapping sql Identity string for
// Perform query operation , Automatically encapsulate query results into Classes Object return Classes clazz =
sqlSession.selectOne(statement,1);// query class In the table id by 1 Records of
// use SqlSession End of execution SQL Need to close after SqlSession 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(); /** *
mapping sql Identity string for , *
me.gacl.mapping.classMapper yes classMapper.xml In file mapper Tagged namespace Value of property , *
getClass4 yes select Tagged id Property value , adopt select Tagged id Property value to find the SQL */ String statement =
"me.gacl.mapping.classMapper.getClass4";// mapping sql Identity string for
// Perform query operation , Automatically encapsulate query results into Classes Object return Classes clazz =
sqlSession.selectOne(statement,1);// query class In the table id by 1 Records of
// use SqlSession End of execution SQL Need to close after SqlSession 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 Used in collection Tag to solve one to many associated queries ,ofType Property specifies the object type of the element in the collection .