Many to one processing
Many to one understanding:
Multiple students correspond to one teacher
If for students, it is a many to one phenomenon, that is, associate a teacher from students!
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, 'Miss Qin');
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. IDEA installing Lombok plug-in
2. Introduce Maven dependency
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.10</version> </dependency>
3. Add comments to the code
@Data //GET,SET,ToString, parameterless construction
public class Teacher {
private int id;
private String name;
}
@Data
public class Student {
private int id;
private String name;
//Multiple students can be the same teacher, that is, many to one
private Teacher teacher;
}
4. Write Mapper interfaces corresponding to entity classes [two]
Whether there is a need or not, it should be written down for later needs!
public interface StudentMapper {
}
public interface TeacherMapper {
}
5. Write Mapper corresponding to Mapper interface XML configuration file [two]
Whether there is a need or not, it should be written down for later needs!
<?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.kuang.mapper.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.kuang.mapper.TeacherMapper">
</mapper>
Nested processing by query
1. Add method to StudentMapper interface
//Get the information of all students and corresponding teachers public List<Student> getStudents();
2. Write the corresponding Mapper 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.kuang.mapper.StudentMapper">
<!--
Demand: obtain the information of all students and corresponding teachers
Idea:
1. Get information about all students
2. According to the student information obtained by the teacher ID->Get the teacher's information
3. Think about the problem. In this way, the result set of students should include teachers. How to deal with it? We usually use association query in the database?
1. Make a result set mapping: StudentTeacher
2. StudentTeacher The result set is of type Student
3. The attributes of teachers in students are teacher，The corresponding database is tid.
Multiple [1,...)The student is associated with a teacher=> One to one, one to many
4. Check the official website to find: association – A complex type of Association; Use it to process associative queries
-->
<select id="getStudents" resultMap="StudentTeacher">
select * from student
</select>
<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="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
Pass it 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 It's in clip 1 sql The field name of the query.
-->
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{id}
</select>
</mapper>
3. After writing, go to the Mybatis configuration file and register Mapper!
4. Notes:
<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=tid}" 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 It's in clip 1 sql The field name of the query.
-->
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{id} and name = #{name}
</select>
5. Testing
@Test
public void testGetStudents(){
SqlSession session = MybatisUtils.getSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents();
for (Student student : students){
System.out.println(
"Student name:"+ student.getName()
+"\t teacher:"+student.getTeacher().getName());
}
}
Nested processing by result
In addition to the above way, are there any other ideas?
We can also nest according to the results;
1. Interface method compilation
public List<Student> getStudents2();
2. Write the corresponding mapper file
<!--
Nested processing by query results
Idea:
1. Directly query the results and map the result set
-->
<select id="getStudents2" resultMap="StudentTeacher2" >
select s.id sid, s.name sname , t.name tname
from student s,teacher t
where s.tid = t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
<!--Associated object property Associated objects in Student Properties in entity classes-->
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
3. Go to mybatis config file to inject [it should be handled here]
4. Testing
@Test
public void testGetStudents2(){
SqlSession session = MybatisUtils.getSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> students = mapper.getStudents2();
for (Student student : students){
System.out.println(
"Student name:"+ student.getName()
+"\t teacher:"+student.getTeacher().getName());
}
}
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
One to many understanding:
A teacher has more than one student
For teachers, it is a one to many phenomenon, that is, having a group of students (Collection) under a teacher!
Entity class writing
@Data
public class Student {
private int id;
private String name;
private int tid;
}
@Data
public class Teacher {
private int id;
private String name;
//One teacher has more than one student
private List<Student> students;
}
... as before, build a testing environment!
Nested processing by result
1. TeacherMapper interface writing method
//Get the designated Teacher and all students under the teacher public Teacher getTeacher(int id);
2. Write Mapper configuration file corresponding to the interface
<mapper namespace="com.kuang.mapper.TeacherMapper">
<!--
thinking:
1. Find out the students from the student list and the teacher list id，Student name, teacher name
2. Map the result set of the queried operation
1. Set, use collection!
JavaType and ofType Are used to specify the object type
JavaType Is used to specify pojo Type of property in
ofType The specified is mapped to list In collection properties pojo Type of.
-->
<select id="getTeacher" 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=#{id}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<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 file in mybatis config file
<mappers> <mapper resource="mapper/TeacherMapper.xml"/> </mappers>
4. Testing
@Test
public void testGetTeacher(){
SqlSession session = MybatisUtils.getSession();
TeacherMapper mapper = session.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher.getName());
System.out.println(teacher.getStudents());
}
Nested processing by query
1. TeacherMapper interface writing method
public Teacher getTeacher2(int id);
2. Write Mapper configuration file corresponding to the interface
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id = #{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<!--column Is a one to many foreign key , Write the column name of a primary key-->
<collection property="students" javaType="ArrayList" ofType="Student" column="id" select="getStudentByTeacherId"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from student where tid = #{id}
</select>
3. Register Mapper file in mybatis config file
4. Testing
@Test
public void testGetTeacher2(){
SqlSession session = MybatisUtils.getSession();
TeacherMapper mapper = session.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher.getName());
System.out.println(teacher.getStudents());
}
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 attribute.
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
One to many and many to one are difficulties for many people. We must do a lot of practice and understanding!
Meet the crazy God
Welcome to join me for wechat communication and discussion (Please add notes on csdn)
