9. 多表查询
9.1 一对一
分次查询
mapper
// 一对一的分次查询
User selectUserById(@Param("id") Integer id);
mapper.xml
<!--
一对一的分次查询
1. 查询user信息
select * from user where id = ?
2. 查询user_detail的信息
select * from user_detail where user_id = ?
使用ResultMap可以帮助我们去做复杂映射
-->
<!--
结果映射,需要在这里指出第二次查询的坐标
-->
<resultMap id="userMap" type="com.cskaoyan.vo.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="nickname" property="nickname"/>
<result column="gender" property="gender"/>
<result column="age" property="age"/>
<!--
property: 指代当前resultMap里面成员变量的名字
javaType: 指成员变量的类型
select: 指查询的sql语句的坐标
column: 指传递给第二个sql语句的列名,column只能传递一列过去
-->
<association property="userDetail"
javaType="com.cskaoyan.vo.UserDetail"
select="com.cskaoyan.mapper.UserMapper.selectUserDetailByUserId"
column="id"
/>
</resultMap>
<!--查询入口 -->
<select id="selectUserById" resultMap="userMap">
select <include refid="all_column"/>
from user
where id = #{id}
</select>
<!-- 第二次查询 -->
<select id="selectUserDetailByUserId" resultType="com.cskaoyan.vo.UserDetail">
select id,user_id as userId,height,weight,pic
from user_detail
where user_id = #{id}
</select>
连接查询
连接查询其实就是通过一个连接查询的sql语句来一次把所有的信息都查出来,然后去封装
mapper
// 一对一的连接查询
User selectUserByIdUserCrossJoin(@Param("id") Integer id);
mapper.xml
<!--
一对一的连接查询
-->
<resultMap id="userCrossMap" type="com.cskaoyan.vo.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="nickname" property="nickname"/>
<result column="gender" property="gender"/>
<result column="age" property="age"/>
<!-- 映射 userDetail-->
<association property="userDetail" javaType="com.cskaoyan.vo.UserDetail">
<id column="did" property="id"/>
<result column="userId" property="userId"/>
<result column="height" property="height"/>
<result column="weight" property="weight"/>
<result column="pic" property="pic"/>
</association>
</resultMap>
<select id="selectUserByIdUserCrossJoin" resultMap="userCrossMap">
SELECT
u.id as id,
u.username as username,
u.nickname as nickname,
u.gender as gender,
u.age as age,
d.id as did,
d.user_id as userId,
d.height as height,
d.weight as weight,
d.pic as pic
FROM
USER AS u
LEFT JOIN user_detail AS d ON u.id = d.user_id
WHERE
u.id = #{id}
</select>
9.2 一对多
分次查询
mapper
// 一对多的分次查询
// 查询的结果是需要一个班级对象
// 1. select * from clazz where id = ?
// 2. select * from student where clazz_id = ?
Clazz selectClazzByIdWithStudents(@Param("id") Integer id);
mapper.xml
<resultMap id="clazzMap" type="com.cskaoyan.vo.Clazz">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!-- 封装student List-->
<collection property="studentList"
ofType="com.cskaoyan.vo.Student"
select="com.cskaoyan.mapper.ClazzMapper.selectStudentListByClazzId"
column="id"
/>
</resultMap>
<!-- 查询的入口 -->
<select id="selectClazzByIdWithStudents" resultMap="clazzMap">
select id,name from clazz
where id = #{id}
</select>
<!-- 第二次查询的SQL语句-->
<select id="selectStudentListByClazzId" resultType="com.cskaoyan.vo.Student">
select id,name,age,gender,clazz_id as clazzId
from student
where clazz_id = #{id}
</select>
连接查询
mapper
// 一对多的连接查询
Clazz selectClazzByIdWithStudentsUseCrossJoin(@Param("id") Integer id);
mapper.xml
<resultMap id="clazzCrossMap" type="com.cskaoyan.vo.Clazz">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
<collection property="studentList" ofType="com.cskaoyan.vo.Student">
<id column="sid" property="id"/>
<result column="sname" property="name"/>
<result column="age" property="age"/>
<result column="gender" property="gender"/>
<result column="clazzId" property="clazzId"/>
</collection>
</resultMap>
<!-- 一对多的连接查询 -->
<select id="selectClazzByIdWithStudentsUseCrossJoin" resultMap="clazzCrossMap">
SELECT
c.id as cid,
c.name as cname,
s.id as sid,
s.name as sname,
s.age as age,
s.gender as gender,
s.clazz_id as clazzId
FROM
clazz AS c
LEFT JOIN student AS s ON c.id = s.clazz_id
WHERE
c.id = #{id}
</select>
9.3 多对多
其实和一对多的逻辑是一样的,只有SQL语句有点区别
多对多的时候,只在一个类的一方维护一个list就行,如果双方都维护list,就套娃了
分次查询
mapper
// 多对多 分次查询
// 通过学生id查询学生信息及其选课信息
Student selectStudentById(@Param("id") Integer id);
mapper.xml
<resultMap id="studentMap" type="com.cskaoyan.vo.Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<result column="gender" property="gender"/>
<result column="clazz_id" property="clazzId"/>
<collection
property="courseList"
ofType="com.cskaoyan.vo.Course"
column="id"
select="com.cskaoyan.mapper.StudentMapper.selectCourseListByStudentId"
/>
</resultMap>
<!-- 查询入口-->
<select id="selectStudentById" resultMap="studentMap">
select id,name,age,gender,clazz_id from student
where id = #{id}
</select>
<!-- 第二次查询 通过学生id查询其课程信息-->
<select id="selectCourseListByStudentId" resultType="com.cskaoyan.vo.Course">
SELECT
c.id as id,
c.name as name,
c.teacher_name as teacherName,
c.score as score
FROM
s_c AS sc
LEFT JOIN course AS c ON sc.cid = c.id
WHERE
sc.sid = #{id}
</select>
连接查询
多对多连接查询和一对多的连接查询是一样的,区别是一对多的连接查询连接两张表,多对多的连接查询连接三张表
mapper
// 多对多 连接查询
Student selectStudentByIdUseCrossJoin(@Param("id") Integer id);
mapper.xml
<resultMap id="studentCrossMap" type="com.cskaoyan.vo.Student">
<id column="sid" property="id"/>
<result column="sname" property="name"/>
<result column="age" property="age"/>
<result column="gender" property="gender"/>
<result column="clazzId" property="clazzId"/>
<collection property="courseList" ofType="com.cskaoyan.vo.Course">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
<result column="teacherName" property="teacherName"/>
<result column="score" property="score"/>
</collection>
</resultMap>
<select id="selectStudentByIdUseCrossJoin" resultMap="studentCrossMap">
SELECT
s.id as sid,
s.name as sname,
s.age as age,
s.gender as gender,
s.clazz_id as clazzId,
c.id as cid,
c.name as cname,
c.teacher_name as teacherName,
c.score as score
FROM
student AS s
LEFT JOIN s_c AS sc ON s.id = sc.sid
LEFT JOIN course AS c ON c.id = sc.cid
WHERE
s.id = #{id}
</select>
10. 懒加载
在用到的时候才去加载,如果没有用到对应的数据,就不去加载
懒加载是指在分次查询的时候,我们有两条SQL语句,假如没有懒加载,那么两条sql语句会在调用到接口的时候就立马执行,假如开启了懒加载, 那么第二次sql语句的内容,只有在用到的时候才会去加载,如果没有调用到,那么就不会去执行第二次sql语句
懒加载的目的:提高Mybatis的性能
懒加载默认是关闭的。
如何配置呢?
-
全局配置
在Mybatis-config.xml中开启懒加载
<settings> <!--懒加载总开关--> <setting name="lazyLoadingEnabled" value="true"/> </settings>
-
局部配置
我们可以使用局部配置去覆盖全局配置。换句话来说,其实就是当配置了局部配置的时候,以局部配置为准,当没有配置局部配置的时候,以全局配置为准
懒加载两个注意事项
- 开启懒加载之后,直接输入user的成员变量不会触发第二条sql的执行
- 如果选择debug模式会触发第二条语句
11. 缓存
11.1 一级缓存
一级缓存默认开启的,Mybatis目前也没有提供一个开关让我们关闭
Mybatis的一级缓存是一个SqlSession级别的缓存
换句话来说,只要是同一个SqlSession,去查询同一个内容,那么在第一次查询的时候,就会把结果放入缓存中,后续的查询请求都会从缓存中来取
// 测试同一个Mapper
@Test
public void testCacheLevelOneWithSameMapper(){
User user1 = userMapper.selectUserByIdUserCrossJoin(1); // 从数据库获取结果,放到缓存
System.out.println("第一查询结束...");
User user2 = userMapper.selectUserByIdUserCrossJoin(1); // 直接取一级缓存
System.out.println("第二查询结束...");
User user3 = userMapper.selectUserByIdUserCrossJoin(1); // 直接取一级缓存
System.out.println("第三查询结束...");
User user4 = userMapper.selectUserByIdUserCrossJoin(1); // 直接取一级缓存
System.out.println("第四查询结束...");
}
// 同一个SqlSession,不同的mapper
@Test
public void testCacheLevelOneWithSameSqlSession(){
UserMapper userMapper1 = sqlSession.getMapper(UserMapper.class);
UserMapper userMapper2 = sqlSession.getMapper(UserMapper.class);
UserMapper userMapper3 = sqlSession.getMapper(UserMapper.class);
User user1 = userMapper1.selectUserByIdUserCrossJoin(1);// 从数据库获取结果,放到缓存
System.out.println("第一查询结束...");
User user2 = userMapper2.selectUserByIdUserCrossJoin(1);// 取缓存
System.out.println("第二查询结束...");
User user3 = userMapper3.selectUserByIdUserCrossJoin(1);// 取缓存
System.out.println("第三查询结束...");
}
// 不同的SqlSession
@Test
public void testCacheLevelOneWithUnsameSqlSession(){
SqlSession sqlSession1 = sqlSessionFactory.openSession();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
SqlSession sqlSession3 = sqlSessionFactory.openSession();
UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
UserMapper userMapper3 = sqlSession3.getMapper(UserMapper.class);
User user1 = userMapper1.selectUserByIdUserCrossJoin(1); // 从数据库获取结果,放到缓存
System.out.println("第一查询结束...");
User user2 = userMapper2.selectUserByIdUserCrossJoin(1); // 从数据库获取结果,放到缓存
System.out.println("第二查询结束...");
User user3 = userMapper3.selectUserByIdUserCrossJoin(1); // 从数据库获取结果,放到缓存
System.out.println("第三查询结束...");
}
一级缓存什么时候失效呢?
当我们的数据库发生了增删改以后,得让一级缓存失效。
当执行SqlSession.commit()
或者是 SqlSession.close()
的时候,缓存会失效
11.2 二级缓存
二级缓存是Mybatis给我们提供的一个额外的功能,需要手动开启
Mybatis的二级缓存是一个namespace级别的缓存,不受一级缓存的限制
Mybatis的二级缓存默认是关闭的,需要我们手动开启
如何开启呢?
-
开启缓存开关
<settings> <!-- 二级缓存的总开关 默认是开启的 --> <setting name="cacheEnabled" value="true"/> </settings>
-
配置namespace
-
给对象实现序列化接口
如何自动生成序列化id呢?
测试案例:
// 测试二级缓存
@Test
public void testCacheLevelTwo(){
SqlSession sqlSession1 = sqlSessionFactory.openSession();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
SqlSession sqlSession3 = sqlSessionFactory.openSession();
UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
UserMapper userMapper3 = sqlSession3.getMapper(UserMapper.class);
User user1 = userMapper1.selectUserByIdUserCrossJoin(1); // 从数据库获取结果,放到一级缓存
System.out.println("第一查询结束...");
sqlSession1.commit(); // 把一级缓存的结果提交到二级缓存
User user2 = userMapper2.selectUserByIdUserCrossJoin(1); // 取二级缓存
System.out.println("第二查询结束...");
User user3 = userMapper3.selectUserByIdUserCrossJoin(1); // 取二级缓存
System.out.println("第三查询结束...");
}
总结一下:在工作中,我们不建议大家使用二级缓存,那么使用什么缓存呢?
现在一般使用Redis来作为数据库的缓存