Java学习 day47_Mybatis

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>
    

  • 局部配置

    我们可以使用局部配置去覆盖全局配置。换句话来说,其实就是当配置了局部配置的时候,以局部配置为准,当没有配置局部配置的时候,以全局配置为准

    在这里插入图片描述

懒加载两个注意事项

  1. 开启懒加载之后,直接输入user的成员变量不会触发第二条sql的执行
  2. 如果选择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来作为数据库的缓存

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值