catalogue
1.2 nested processing by query
1. Many to one processing
1.1 database design
Entity class:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
//Students need to associate with a teacher
private Teacher teacher;
}@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
}1.2 nested processing by query
Studentmap and studentmappe r xml :
public interface StudentMapper {
//Query all student information and corresponding teacher information
List<Student> findAllStudent();
}<?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.rk.dao.StudentMapper">
<!--
Requirements: obtain the information of all students and corresponding teachers
1. Get information about all students
2. Obtain the information of the teacher according to the teacher ID - > of the obtained student information
3. Think about the problem. In this way, the student's result set should include the teacher. How should we deal with it? In the database, we generally
Use association query?
1. Make a result set mapping: StudentTeacher
2. The type of studentteacher result set is Student
3. The attribute of teachers in students is teacher, and the corresponding database is tid. Multiple [1,...) students are associated with a teacher = > one to one, one to many
4. Check the official website and find: association - a complex type of association; Use it to process associative queries
-->
<select id="findAllStudent" resultMap="StudentTeacher">
select * from student
</select>
<resultMap id="StudentTeacher" type="student">
<!-- Association Association property property property name javaType property type column
Column names in the table on the side of -- >
<association property="teacher" column="tid" javaType="teacher" select="getTeacher"></association>
</resultMap>
<!--
When the id passed here has only one attribute, any value can be written below
Multi parameter configuration of column in association:
column="{key=value,key=value}"
In fact, it is in the form of key value pairs. Key is the value name passed to the next sql, and value is the field name of the sql query in fragment 1 -- >
<select id="getTeacher" resultType="teacher">
select * from teacher where id=#{id} / / this #{ID} can be any name
</select>
</mapper>
Test:
@Test
public void testStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> allStudent = mapper.findAllStudent();
for (Student student : allStudent) {
System.out.println(student);
}
}Test results:
1.3 nesting by result
studentmapper and stedentmapper xml
public interface StudentMapper {
//Query all student information and corresponding teacher information
List<Student> findAllStudent2();
}<?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.rk.dao.StudentMapper">
<select id="findAllStudent2" resultMap="StudentTeacherd2">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid=t.id;
</select>
<resultMap id="StudentTeacherd2" type="student">
<id property="id" column="sid"></id>
<result property="name" column="sname"></result>
<association property="teacher" javaType="teacher">
<result property="name" column="tname"></result>
</association>
</resultMap>
</mapper>Note: the mapped column in resultMap is the alias queried by sql statement. If there is no alias, the column is s.id, s.name and t.name
For nested objects, javaType="teacher" is written
Test results:
2. One to many processing
Entity class:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
//A teacher has more than one student
private List<Student> students;
}2.1. Nesting by result
TeacherMapper and TeacherMapper xml:
public interface TeacherMapper {
//Get all students under the specified teacher
Teacher getTeacherBytid(@Param("tid") int id);
}<mapper namespace="com.rk.dao.TeacherMapper">
<!--Nested query by result-->
<select id="getTeacherBytid" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid=t.id and t.id=#{tid};
</select>
<resultMap id="TeacherStudent" type="teacher">
<result property="id" column="tid"></result>
<result property="name" column="tname"></result>
<!--object:association aggregate:collection
javaType:Specifies the type of the property
Collection, we use ofType
-->
<collection property="students" ofType="student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<result property="tid" column="tid"></result>
</collection>
</resultMap>
</mapper>For collection, use collection
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
Test:
@Test
public void testTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacherBytid = mapper.getTeacherBytid(2);
System.out.println(teacherBytid);
}result:
3. Summary
1. association Association
2. Collection collection
3. Therefore, association is used for one-to-one and many to one, 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. Note the correspondence between fields and attributes in one to many and many to one
5. Try to use Log4j to view your own errors through the log