MyBatis 多对一的处理
多对一的理解:
- 多个学生对应一个老师
- 如果对于学生这边,就是一个多对一的现象,即从学生这边关联一个老师!
1.数据库设计
use mybatis;
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, '秦老师');
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', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`,`tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`,`tid`) VALUES ('5', '小王', '1');
2.搭建测试环境
【Lombok的使用】
- IDEA安装Lombok插件
- 引入Maven依赖
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
3.在代码中增加注解
package com.kuang.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
package com.kuang.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
- 编写实体类对应的Mapper接口
package com.kuang.dao;
import com.kuang.pojo.Student;
import java.util.List;
public interface StudentMapper {
//获取所有学生及对应老师的信息
List<Student> getAllStudent();
//获取所有学生及对应老师的信息2,对照组
List<Student> getAllStudent2();
}
package com.kuang.dao;
import com.kuang.pojo.Teacher;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
@Select("select * from teacher")
List<Teacher> getTeacher();
}
- 编写Mapper接口对应的 mapper.xml配置文件
resources.com.kuang.dao.StudentMapper.xml
<?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.kuang.dao.StudentMapper">
<!--
需求:获取所有学生及对应老师的信息
思路:
1. 获取所有学生的信息
2. 根据获取的学生信息的老师ID->获取该老师的信息
3. 思考问题,这样学生的结果集中应该包含老师,该如何处理呢,数据库中我们一般
使用关联查询?
1. 做一个结果集映射:StudentTeacher
2. StudentTeacher结果集的类型为 Student
3. 学生中老师的属性为teacher,对应数据库中为tid。
多个 [1,...)学生关联一个老师=> 一对一,一对多
4. 查看官网找到:association – 一个复杂类型的关联;使用它来处理关联查
-->
<select id="getAllStudent" resultMap="StudentTeacher">
select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<association property="teacher" column="tid" javaType="Teacher" select="getAllTeacher"/>
</resultMap>
<select id="getAllTeacher" resultType="Teacher">
select * from teacher where id = #{id}
</select>
<!--
方法2,按查询结果嵌套处理
思路:
1. 直接查询出结果,进行结果集的映射 -->
<select id="getAllStudent2" resultMap="StudentTeacher2">
select s.id as sid,s.name as sname,t.name as 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"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
resources.com.kuang.dao.TeacherMapper.xml
<?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.kuang.dao.TeacherMapper">
</mapper>
3.按查询嵌套处理
- 给StudentMapper接口增加方法
//获取所有学生及对应老师的信息
List<Student> getAllStudent();
- 编写对应的Mapper文件
<mapper namespace="com.kuang.dao.StudentMapper">
<!--
需求:获取所有学生及对应老师的信息
思路:
1. 获取所有学生的信息
2. 根据获取的学生信息的老师ID->获取该老师的信息
3. 思考问题,这样学生的结果集中应该包含老师,该如何处理呢,数据库中我们一般
使用关联查询?
1. 做一个结果集映射:StudentTeacher
2. StudentTeacher结果集的类型为 Student
3. 学生中老师的属性为teacher,对应数据库中为tid。
多个 [1,...)学生关联一个老师=> 一对一,一对多
4. 查看官网找到:association – 一个复杂类型的关联;使用它来处理关联查
-->
<select id="getAllStudent" resultMap="StudentTeacher">
select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<association property="teacher" column="tid" javaType="Teacher" select="getAllTeacher"/>
</resultMap>
<select id="getAllTeacher" resultType="Teacher">
select * from teacher where id = #{id}
</select>
</mapper>
- 编写完毕去Mybatis配置文件中,注册Mapper!
<mappers>
<mapper class="com.kuang.dao.StudentMapper"/>
</mappers>
- 注意点说明:
@Test
public void testStudentTeacher() {
SqlSession session = MyBatisUtils.getSqlSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> allStudent = mapper.getAllStudent();
for (Student student : allStudent) {
System.out.println(student);
}
session.close();
}
易错点说明
在这里别名注意添加,特别容易出错,而且找不到原因。
4.按结果嵌套处理
- 接口方法编写
List<Student> getAllStudent2();
- 编写对应的Mapper文件
<!--
方法2,按查询结果嵌套处理
思路:
1. 直接查询出结果,进行结果集的映射 -->
<select id="getAllStudent2" resultMap="StudentTeacher2">
select s.id as sid,s.name as sname,t.name as 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"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
- 去mybatis-config文件中注入
<typeAlias type="com.kuang.pojo.Student" alias="Student"/>
- 测试
@Test
public void testStudentTeacher() {
SqlSession session = MyBatisUtils.getSqlSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> allStudent = mapper.getAllStudent();
for (Student student : allStudent) {
System.out.println(student);
}
session.close();
}
总结
- 按照查询进行嵌套处理就像SQL中的子查询
- 按照结果进行嵌套处理就像SQL中的联表查询