preparation ： Create the required Data sheet
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO teacher(`id`, `name`) VALUES (1, 'Dr.Lee');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', 'Alice', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', 'Bob', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', 'Curry', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', 'David', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', 'Ellen', '1');
01 Many to one processing
What is many to one ？
Illustrate with examples , Multiple students in the student table of the database correspond to a teacher
stay IDEA According to the data table and test environment （ For one more ） establish Corresponding Entity class
com.hooi.pojo.Student
package com.hooi.pojo;
public class Student {
private int id;
private String name;
private Teacher teacher;
public Student() {
}
public Student(int id, String name, Teacher teacher) {
this.id = id;
this.name = name;
this.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 "Student{" +
"id=" + id +
", name='" + name + '\'' +
", teacherName=" + teacher.getName() +
'}';
}
}
com.hooi.pojo.Teacher
package com.hooi.pojo;
public class Teacher {
private int id;
private String name;
public Teacher() {
}
public Teacher(int id, String name) {
this.id = id;
this.name = 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;
}
}
test : keyword association
StudentMapper Interface ：
package com.hooi.dao;
import com.hooi.pojo.Student;
import java.util.List;
public interface StudentMapper {
// Look up all the information about the students The way 1
public abstract List<Student> getStudents();
// Look up all the information about the students The way 2
public abstract List<Student> getStuents2();
}
Corresponding Mapper The mapping file studentMapper.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="com.hooi.dao.StudentMapper">
<!-- The way 1 Nested query statements -->
<select id="getStudents" resultMap="S-T1">
select * from mybatis.student
</select>
<!-- Mapping result set -->
<resultMap id="S-T1" type="student">
<id column="id" property="id"/>
<result column="name" property="name" />
<!-- Associated database Teacher surface -->
<association column="tid" property="teacher" javaType="teacher" select="getTeacher"/>
</resultMap>
<!-- Inquire about Teacher In the table t.name-->
<select id="getTeacher" resultType="teacher">
select t.name from mybatis.teacher as t where id=#{id}
</select>
<!--=========================================================================-->
<!-- The way 2 Directly relate query results -->
<select id="getStudents2" resultMap="S-T2">
select s.id , s.name , t.name as tName from mybatis.student as s , mybatis.teacher as t
where s.tid = t.id
</select>
<resultMap id="S-T2" type="student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!-- Directly related Teacher-->
<association property="teacher" javaType="teacher" >
<!-- Correlation results -->
<result property="name" column="tName"/>
</association>
</resultMap>
</mapper>
Mybatis The configuration file
<mappers>
<mapper resource="com/hooi/dao/studentMapper.xml"/>
</mappers>
Junit Test code ：
package com.hooi.dao;
import com.hooi.pojo.Student;
import com.hooi.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class StudentMapperTest {
@Test
public void getStudents(){
SqlSession session = MybatisUtil.getSqlSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students) {
System.out.println(student);
}
session.close();
}
@Test
public void getStudents2(){
SqlSession session = MybatisUtil.getSqlSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents2();
for (Student student : students) {
System.out.println(student);
}
session.close();
}
}
test result ：
getStudents()test result
getStudents2()test result
02 One to many processing
What is one to many ？
Illustrate with examples , One teacher corresponds to many students
stay IDEA According to the data table and test environment （ One to many ） establish Corresponding Entity class
com.hooi.pojo.Student
package com.hooi.pojo;
public class Student {
private int id;
private String name;
private int tid;
public Student() {
}
public Student(int id, String name, int tid) {
this.id = id;
this.name = name;
this.tid = tid;
}
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 int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '}';
}
}
com.hooi.pojo.Teacher
package com.hooi.pojo;
import java.util.List;
public class Teacher {
private int id;
private String name;
private List<Student> students;
public Teacher() {
}
public Teacher(int id, String name, List<Student> students) {
this.id = id;
this.name = name;
this.students = 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 List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
}
test : keyword collection
TeacherMapper Interface ：
package com.hooi.dao;
import com.hooi.pojo.Teacher;
public interface TeacherMapper {
// according to ID Look up the teacher's information The way 1
public abstract Teacher getTeacher(int id);
// according to ID Look up the teacher's information The way 2
public abstract Teacher getTeacher2(int id);
}
Corresponding Mapper The mapping file teacherMapper.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="com.hooi.dao.TeacherMapper">
<!-- The way 1 Association result set -->
<select id="getTeacher" resultMap="T-S">
select t.name tname, s.id sid, s.name sname
from mybatis.teacher t, mybatis.student s
where t.id = s.tid and t.id =#{id}
</select>
<resultMap id="T-S" type="teacher">
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<id column="sid" property="id"/>
<result column="sname" property="name"/>
</collection>
</resultMap>
<!--=========================================================================-->
<!-- The way 2 Associated database -->
<select id="getTeacher2" resultMap="T-S2">
select * from mybatis.teacher
</select>
<resultMap id="T-S2" type="teacher">
<result property="name" column="name"/>
<collection column="id" property="students" javaType="ArrayList" ofType="student" select="getStudents"/>
</resultMap>
<select id="getStudents" resultType="student">
select * from mybatis.student where tid = #{id}
</select>
</mapper>
Mybatis The configuration file
<mappers>
<mapper resource="com/hooi/dao/teacherMapper.xml"/>
</mappers>
Junit Test code ：
package com.hooi.dao;
import com.hooi.pojo.Teacher;
import com.hooi.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class TeacherMapperTest {
@Test
public void getTeacher(){
SqlSession session = MybatisUtil.getSqlSession();
TeacherMapper mapper = session.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher.getName());
System.out.println(teacher.getStudents());
session.close();
}
@Test
public void getTeacher2(){
SqlSession session = MybatisUtil.getSqlSession();
TeacherMapper mapper = session.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher.getName());
System.out.println(teacher.getStudents());
session.close();
}
}
test result ：
getTeacher()test result
getTeacher2()test result
