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来作为数据库的缓存