9 one to many and many to one processing
Many to one processing
Multiple students correspond to one teacher
Database design
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, 'Teacher Zeng');
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', 'Xiao Ming', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', 'Xiao Hong', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', 'Xiao Zhang', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', 'petty thief', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', 'Xiao Wang', '1');
Build test environment
1. Writing entity classes
Student.java
package com.zzb.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 + '\'' +
", teacher=" + teacher +
'}';
}
}
Teacher.java
package com.zzb.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;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
2. Write Mapper interface corresponding to entity class
package com.zzb.dao;
public interface StudentMapper {
}
package com.zzb.dao;
public interface TeacherMapper {
}
3. Write the mapper corresponding to the interface XML configuration file
<?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.zzb.dao.StudentMapper">
</mapper>
<?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.zzb.dao.TeacherMapper">
</mapper>
Nested processing by query
1. Add method to StudentMapper interface
// Get student list and corresponding teacher information public List<Student> getStudents();
2. Write the corresponding Mapper configuration file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zzb.dao.StudentMapper">
<!--Nested by query-->
<select id="getStudents" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--association Association attribute property Attribute name javaType Attribute type column Column names in tables with more than one side-->
<association property="teacher" column="tid" javaType="Teacher" select = "getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{tid}
</select>
</mapper>
3. Register Mapper in MyBatis configuration file
<!--Binding interface-->
<mappers>
<mapper class="com.zzb.dao.StudentMapper"/>
</mappers>
be careful:
<resultMap id="StudentTeacher" type="Student">
<!--association Association attribute property Attribute name javaType Attribute type column Column names in tables with more than one side-->
<association property="teacher" column="{id = tid, name = name}" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
From here id，When there is only one attribute, any value can be written below
association in column Multi parameter configuration:
column="{key=value,key=value}"
In fact, it is the form of key value pairs, key Pass it on to the next one sql The name of the value, value yes sql The field name of the query.
-->
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{id} and name = #{name}
</select>
4. Testing
@Test
public void getStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudents();
for(Student student : studentList){
System.out.println(student);
}
sqlSession.close();
}
Test results:
Student{id=1, name='Xiao Ming', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=2, name='Xiao Hong', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=3, name='Xiao Zhang', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=4, name='petty thief', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=5, name='Xiao Wang', teacher=Teacher{id=1, name='Teacher Zeng'}}
Nested query by result
1. StudentMapper interface method preparation
public List<Student> getStudents2();
2. Write the corresponding studentmapper XML configuration file
<!--Nest by result-->
<select id="getStudents2" resultMap="StudentTeacher2">
SELECT S.id AS sid, s.name AS sname, t.id AS tid, t.name AS tname
FROM student AS s, teacher AS t
WHERE s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
3. Register Mapper in MyBatis configuration file
<mapper class="com.zzb.dao.StudentMapper"/>
4. Testing
@Test
public void getStudent2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudents2();
for(Student student : studentList){
System.out.println(student);
}
sqlSession.close();
}
Test results:
Student{id=1, name='Xiao Ming', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=2, name='Xiao Hong', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=3, name='Xiao Zhang', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=4, name='petty thief', teacher=Teacher{id=1, name='Teacher Zeng'}}
Student{id=5, name='Xiao Wang', teacher=Teacher{id=1, name='Teacher Zeng'}}
Summary:
Nested processing by query is like subquery in SQL
Nested processing according to the results is like a join table query in SQL
One to many processing
module: mybatis-06
A teacher has more than one student
1. Entity class writing
Student.java
package com.zzb.pojo;
// pom. The lombok package is introduced into XML to simplify the writing of entity classes
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher.java
package com.zzb.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
Like the above many to one, build a test environment!
Nested query by result
1. TeacherMapper interface writing method
package com.zzb.dao;
import com.zzb.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
public interface TeacherMapper {
// Get all students under the specified teacher
Teacher getTeacher2(@Param("tid") int id);
}
2. Write Mapper configuration file corresponding to the interface
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--configuration-->
<mapper namespace="com.zzb.dao.TeacherMapper">
<!--Nest by result-->
<select id="getTeacher2" resultMap="TeacherStudent2">
SELECT S.id AS sid, s.name AS sname, t.id AS tid, t.name AS tname
FROM student AS s, teacher AS t
WHERE s.tid = t.id;
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
3. Register mapper in mybatis configuration file
<!--Binding interface-->
<mappers>
<mapper class="com.zzb.dao.TeacherMapper"/>
</mappers>
4. Testing
@Test
public void getTeacher2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
Test results:
Teacher(id=1, name=Teacher Zeng, students=[Student(id=1, name=Xiao Ming, tid=1), Student(id=2, name=Xiao Hong, tid=1), Student(id=3, name=Xiao Zhang, tid=1), Student(id=4, name=petty thief, tid=1), Student(id=5, name=Xiao Wang, tid=1)])
Nested by query
1. TeacherMapper interface writing method
// Get all students under the specified teacher
Teacher getTeacher(@Param("tid") int id);
2. Write Mapper configuration file corresponding to the interface
<!--Nested by query-->
<select id="getTeacher" resultMap="TeacherStudent">
select * from teacher
</select>
<resultMap id="TeacherStudent" type="teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudent"/>
</resultMap>
<select id="getStudent" resultType="Student">
select * from student where tid = #{id}
</select>
3. Register mapper in mybatis configuration file
<!--Binding interface-->
<mappers>
<mapper class="com.zzb.dao.TeacherMapper"/>
</mappers>
4. Testing
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
Test results:
Teacher(id=1, name=Teacher Zeng, students=[Student(id=1, name=Xiao Ming, tid=1), Student(id=2, name=Xiao Hong, tid=1), Student(id=3, name=Xiao Zhang, tid=1), Student(id=4, name=petty thief, tid=1), Student(id=5, name=Xiao Wang, tid=1)])
Summary:
1. association Association
2. Collection collection
3. Therefore, association is used for one-to-one and many to one relationships, while collection is used for one to many relationships
4. Both JavaType and ofType are used to specify the object type
- JavaType is used to specify the type of property in pojo
- ofType specifies the type mapped to pojo in the List collection property.
Note:
1. Ensure the readability of SQL and make it easy to understand as much as possible
2. According to the actual requirements, try to write SQL statements with higher performance
3. Pay attention to the inconsistency between attribute name and field
4. Pay attention to the correspondence between fields and attributes in one to many and many to one
5. Try to use Log4j and check your errors through the log