Mybatis复习：多对一、一对多
参考视频：
尚硅谷2019年10月份线下班最新MyBatis教程
一、自定义映射
0、pom.xml配置
依赖：
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!--Mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!--Mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
</dependencies>
打包配置：为能打包java目录中的文件，需要添加以下配置
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*</include>
</includes>
</resource>
</resources>
...
</build>
1、准备数据库表
学生表：
老师表：
2、创建实体类（Student、Teacher）
代码：
public class Student {
private int id;
private String sname;
private String email;
private int age;
private Teacher teacher;
public Student(int id, String sname, String email, int age, Teacher teacher) {
this.id = id;
this.sname = sname;
this.email = email;
this.age = age;
this.teacher = teacher;
}
public Student() {
}
...
}
public class Teacher {
private int id;
private String tname;
public Teacher(int id, String tname) {
this.id = id;
this.tname = tname;
}
public Teacher() {
}
....
}
3、创建Mapper接口和映射文件
代码：
public interface StudentMapper {
List<Student> getAllStudent();
}
<?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.suffle.mapper.StudentMapper">
<select id="getAllStudent" resultType="com.suffle.entity.Student">
select s.id,s.sname,s.age,s.email,s.tid,t.tname from student s left join teacher t on s.tid = t.id
</select>
</mapper>
4、jdbc.properties和Mybatis核心配置文件
文件位置：
代码：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="jdbc.properties"></properties>
<!--输出日志文件到控制台-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.suffle.mapper"/>
</mappers>
</configuration>
5、创建测试类
文件位置：
public class MybatisTest {
@Test
public void test01() throws IOException {
String configPath = "Mybatis-Config.xml";
InputStream is = Resources.getResourceAsStream(configPath);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<Student> list = studentMapper.getAllStudent();
for (Student student : list){
System.out.println(student+"\n");
}
}
}
6、测试结果
测试结果：
问题：teacher属性没有查出来
原因：teacher属性没有被正确的映射
解决方案：自定义结果映射
1）编写自定义结果映射
resultMap：自定义映射，处理复杂的表关系
id：设置主键的映射关系
result：设置非主键的映射关系
column：查询的字段名
property：结果类的属性名
<resultMap id="Student" type="com.suffle.entity.Student">
<!--
id：主键
column：SQL结果的字段名
property：实体类的属性名
-->
<id column="id" property="id"/>
<result column="sname" property="sname"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
<result column="tid" property="teacher.id"/>
<result column="tname" property="teacher.tname"/>
</resultMap>
2）使用该结果映射
<select id="getAllStudent" resultMap="Student">
select s.id,s.sname,s.age,s.email,s.tid,t.tname from student s left join teacher t on s.tid = t.id
</select>
3）测试
二、使用association
association：把查询结果的部分字段，包装成一个属性
property：属性名
javaType：该属性类的全限定名
代码：
<resultMap id="Student" type="com.suffle.entity.Student">
<id column="id" property="id"/>
<result column="sname" property="sname"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
<association property="teacher" javaType="com.suffle.entity.Teacher">
<id column="tid" property="id"/>
<result column="tname" property="tname"/>
</association>
</resultMap>
三、分步查询
1）准备TeacherMapper接口和映射文件
public interface TeacherMapper {
Teacher getTeacherById(@Param("id") int id);
}
<select id="getTeacherById" resultType="com.suffle.entity.Teacher">
select id,tname from teacher where id = #{id}
</select>
2）结果映射
select：分步查询的SQL语句（全限定名+方法名）
column：查询条件（必需是查询语句中的结果字段）
<resultMap id="Student" type="com.suffle.entity.Student">
<id column="id" property="id"/>
<result column="sname" property="sname"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
<association property="teacher" select="com.suffle.mapper.TeacherMapper.getTeacherById" column="tid"/>
</resultMap>
<select id="getStudentById" resultMap="Student">
select id,sname,email,age,tid from student where id = #{id}
</select>
<select id="getAllStudent" resultMap="Student">
select id,sname,email,age,tid from student
</select>
四、分步查询的延迟加载
问题：什么是延迟加载？
先测试如下代码
@Test
public void test02() throws IOException {
...
Student student = studentMapper.getStudentById(2);
String studentName = student.getSname();
System.out.println("studentName:"+studentName);
}
输出日志：
从测试结果我们发现，在没有访问teacher的情况下，还是执行了teacher的查询
而延迟加载，就是直到我们用到某个字段的信息的时候，再对其进行加载（查询）
懒加载相关配置：
1）配置Mybatis-Config.xml
<settings>
<!--输出日志文件到控制台-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--开启懒加载-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--关闭一次性加载全部资源-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
2）测试
@Test
public void test02() throws IOException {
...
Student student = studentMapper.getStudentById(2);
String studentName = student.getSname();
System.out.println("studentName:"+studentName);
System.out.println("======================================");
Teacher teacher = student.getTeacher();
System.out.println("teacher:"+teacher);
}
结果：
五、一对多 自定义映射
首先，重新设计一下实体类。
public class Student {
private int id;
private String sname;
private String email;
private int age;
private int tid;
public Student(int id, String sname, String email, int age, int tid) {
this.id = id;
this.sname = sname;
this.email = email;
this.age = age;
this.tid = tid;
}
...
}
package com.suffle.entity;
import java.util.List;
public class Teacher {
private int id;
private String tname;
List<Student> students;
public Teacher(int id, String tname,List<Student> students) {
this.id = id;
this.tname = tname;
this.students = students;
}
public Teacher() {
}
...
}
设计SQL语句：
SELECT t.id,t.tname,s.id as sid,s.sname,s.email,s.age
FROM teacher t
LEFT JOIN student s
ON t.id=s.tid
WHERE t.id = #{id}
运行测试SQL语句
自定义映射
collection：用于映射集合，处理一对多、多对多的关系
ofType：集合中的类型，不用指定javaType
<resultMap id="Teacher" type="com.suffle.entity.Teacher">
<id column="id" property="id"/>
<result column="tname" property="tname"/>
<collection property="students" ofType="com.suffle.entity.Student">
<id column="sid" property="id"/>
<result column="sname" property="sname"/>
<result column="email" property="email"/>
<result column="age" property="age"/>
<result column="id" property="tid"/>
</collection>
</resultMap>
<select id="getTeacherById" resultMap="Teacher">
SELECT t.id,t.tname,s.id as sid,s.sname,s.email,s.age FROM teacher t LEFT JOIN student s ON t.id=s.tid WHERE t.id = #{id}
</select>
测试
测试代码
@Test
public void test03() throws IOException {
...
TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.getTeacherById(1);
System.out.println(teacher);
}
六、一对多 分步查询
1）给StudentMapper添加方法
List getStudentsBytId(@Param(“tid”) int tid);
2）StudentMapper映射
<select id="getStudentsBytId" resultType="com.suffle.entity.Student">
SELECT id,sname,email,age,tid
FROM student
WHERE tid = #{tid}
</select>
3）TeacherMapper映射
<resultMap id="Teacher" type="com.suffle.entity.Teacher">
<id column="id" property="id"/>
<result column="tname" property="tname"/>
<collection property="students" select="com.suffle.mapper.StudentMapper.getStudentsBytId" column="id"/>
</resultMap>
<select id="getTeacherById" resultMap="Teacher">
SELECT id,tname
FROM teacher
WHERE id = #{id}
</select>
结果：
关于collection、association中的column属性：
1、属性的值作为查询条件，必需是查询语句结果中的字段
2、可以通过集合传递多个查询条件，格式：
column="{键=值}"
注意：此时的键必需对应分步查询语句中的占位符中设置的名称（如果写错了会出错），而值则是作为查询条件的字段
关于分步查询的延迟加载：
有时我们不需要所有的分步查询操作都进行延迟加载，只需在不用延迟加载的分步查询中加入:
fetch = "eager"
8625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
