多表查询

简述

多表查询是MyBatis的重要内容。多表查询主要分为连接查询和分次查询，顾名思义，连接查询就是一次性查询完所有的sql语句；分次查询就是分为2次甚至更多次查询sql。从简易程度上讲，连接查询更简便更省事，但是当数据量非常大比如千万级时，连接查询因其存在交叉连接或是回表的操作，使得效率非常低，所以这就产生了分次查询。比如1千万条sql，如果是连接查询，那么查询次数最多可以为1千万×1千万；而如果是分次查询，则有可能降至1千万+1千万。因此，分次查询虽然比较麻烦，但却是必须掌握的。

内容

一对一

  • 连接查询

  • 分次查询

    一对多

  • 连接查询

  • 分次查询

    多对多

  • 连接查询

  • 分次查询

image.png

image.png

image.png

  1. package com.simon;
  3. import com.simon.bean.Student;
  4. import com.simon.bean.User;
  5. import com.simon.bean.UserVO;
  6. import org.apache.ibatis.io.Resources;
  7. import org.apache.ibatis.session.SqlSession;
  8. import org.apache.ibatis.session.SqlSessionFactory;
  9. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  10. import org.junit.AfterClass;
  11. import org.junit.BeforeClass;
  12. import org.junit.Test;
  14. import java.io.IOException;
  15. import java.io.InputStream;
  16. import java.util.ArrayList;
  17. import java.util.HashMap;
  18. import java.util.List;
  20. public class MultiTableTest {
  22.     public static SqlSession sqlSession = null;
  23.     static Mapper mapper;
  25.     @BeforeClass
  26.     public static void init() throws IOException {
  27.         InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  28.         SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
  29.         SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
  30.         sqlSession = sqlSessionFactory.openSession();
  31.         mapper = sqlSession.getMapper(Mapper.class);
  32.     }
  34.     @AfterClass
  35.     public static void destory(){
  36.         sqlSession.commit();
  37.         sqlSession.close();
  39.     }
  42.     //多表查询：
  43.     //连接查询：一对一、一对多、多对多
  44.     //分次查询：一对一、一对多、多对多
  45.     //模板：一对一：用户和用户详情    一对多：班级和学生s     多对多：学生和选课名称
  46.     @Test
  47.     public void testOne2OneTable(){
  48.         User user = mapper.selectAllByUserTableId(8);
  49.         System.out.println(user);
  50.     }
  51.     @Test
  52.     public void testOne2OneTable2(){
  53.         User user = mapper.selectAllByUserTableId2(8);
  54.         System.out.println(user);
  55.     }
  58.     @Test
  59.     public void testOne2MultiTable(){
  60.         List<Clazz> clazzs = (List<Clazz>) mapper.selectClazzByClazzTableName("一班");
  61.         System.out.println(clazzs);
  62.     }
  63.     @Test
  64.     public void testOne2MultiTable2(){
  65.         List<Clazz> clazzs = (List<Clazz>) mapper.selectClazzByClazzTableName2("一班");
  66.         System.out.println(clazzs);
  67.     }
  71.     @Test
  72.     public void testMulti2Multi(){
  73.         Student student = mapper.selectStudentByStudentName("曹操");
  74.         System.out.println(student);
  75.     }
  76.     @Test
  77.     public void testMulti2Multi2(){
  78.         Student student = mapper.selectStudentByStudentName2("曹操");
  79.         System.out.println(student);
  80.     }
  83. }
  1. package com.simon;
  3. import com.simon.bean.Student;
  4. import com.simon.bean.User;
  5. import com.simon.bean.UserVO;
  6. import org.apache.ibatis.annotations.Param;
  8. import java.util.ArrayList;
  9. import java.util.List;
  10. import java.util.Map;
  12. public interface Mapper {
  14.     User selectAllByUserTableId(@Param("id") Integer id);
  15.     User selectAllByUserTableId2(@Param("id") Integer id);
  17.     List<Clazz> selectClazzByClazzTableName(@Param("clazzName") String clazzName);
  18.     List<Clazz> selectClazzByClazzTableName2(@Param("clazzName") String clazzName);
  20.     Student selectStudentByStudentName(@Param("studentName") String studentName);
  21.     Student selectStudentByStudentName2(@Param("studentName") String studentName);
  24. }
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.simon.Mapper">
  6.     <sql id="all_columns">
  7.         u.id as id,
  8.         u.username as username,
  9.         u.password as password,
  10.         u.age as age,
  11.         ud.id as udid,
  12.         ud.user_id as user_id,
  13.         ud.pic as pic,
  14.         ud.sign as sign
  15.     </sql>
  17.     <sql id="one2multi_columns">
  18.         c.id as id,
  19.         c.name as name,
  20.         s.id as sid,
  21.         s.name as sname,
  22.         s.clazzid as clazzid
  23.     </sql>
  25.     <!--一对一，连接查询-->
  26.     <resultMap id="selectUser" type="com.simon.bean.User">
  27.         <id column="id" property="id"/>
  28.         <result column="username" property="username"/>
  29.         <result column="password" property="password"/>
  30.         <result column="age" property="age"/>
  31.         <association property="userDetail"  javaType="com.simon.bean.UserDetail">
  32.             <result column="udid" property="id"/>
  33.             <result column="user_id" property="userId"/>
  34.             <result column="pic" property="pic"/>
  35.             <result column="sign" property="sign"/>
  36.         </association>
  37.     </resultMap>
  38.     <select id="selectAllByUserTableId" resultMap="selectUser">
  39.         select <include refid="all_columns"/> from user as u inner join user_detail as ud
  40.         on u.id = ud.user_id and u.id = #{id};
  41.     </select>
  43.     <!--一对多，连接查询-->
  44.     <resultMap id="selectClazz" type="com.simon.Clazz">
  45.         <id column="id" property="id"/>
  46.         <result column="name" property="name"/>
  48.         <collection property="students" ofType="com.simon.Students">
  49.             <result column="sid" property="id"/>
  50.             <result column="sname" property="name"/>
  51.             <result column="clazzid" property="clazzId"/>
  52.         </collection>
  53.     </resultMap>
  55.     <select id="selectClazzByClazzTableName" resultMap="selectClazz">
  56.         SELECT
  57.             <include refid="one2multi_columns"/>
  58.         FROM
  59.             clazz AS c
  60.             LEFT JOIN students AS s ON c.id = s.clazzid
  61.             AND c.NAME = #{clazzName};
  62.     </select>
  64.     <!--多对多，连接查询-->
  65.     <resultMap id="selectStudent" type="com.simon.bean.Student">
  66.         <id column="id" property="id"/>
  67.         <result column="name" property="name"/>
  68.         <result column="age" property="age"/>
  70.         <collection property="course" ofType="com.simon.bean.Course">
  71.             <result column="cid" property="id"/>
  72.             <result column="cname" property="name"/>
  73.         </collection>
  74.     </resultMap>
  75.     <select id="selectStudentByStudentName" resultMap="selectStudent">
  76.         SELECT
  77.             s.id AS id,
  78.             s.NAME AS NAME,
  79.             s.age AS age,
  80.             c.id AS cid,
  81.             c.NAME AS cname
  82.         FROM
  83.             student AS s
  84.             LEFT JOIN s_c AS sc ON s.id = sc.sid
  85.             LEFT JOIN course AS c ON sc.cid = c.id
  86.         WHERE
  87.             s.NAME = #{studentName};
  88.     </select>
  91.     <!--一对一，分次查询-->
  92.     <resultMap id="selectUser2" type="com.simon.bean.User">
  93.         <id column="id" property="id"/>
  94.         <result column="username" property="username"/>
  95.         <result column="password" property="password"/>
  96.         <result column="age" property="age"/>
  98.         <association property="userDetail" javaType="com.simon.bean.UserDetail"
  99.                      select="selectUserDetailById" column="id"/>
  100.     </resultMap>
  101.     <select id="selectAllByUserTableId2" resultMap="selectUser2">
  102.         select id,username,password,age from user where id = #{id}
  103.     </select>
  105.     <select id="selectUserDetailById" resultType="com.simon.bean.UserDetail">
  106.         SELECT
  107.             id AS id,
  108.             user_id AS uid,
  109.             pic AS pic,
  110.             sign AS sign
  111.         FROM
  112.             user_detail
  113.         WHERE
  114.             user_id = #{uid}
  115.     </select>
  118.     <!--一对多，分次查询-->
  119.     <!--javaType<ofType>-->
  120.     <resultMap id="selectClazz2" type="com.simon.Clazz">
  121.         <id column="id" property="id"/>
  122.         <result column="name" property="name"/>
  124.         <collection property="students" javaType="list" column="id2"
  125.                     select="selectStudentListByClazzId" />
  126.     </resultMap>
  127.     <select id="selectClazzByClazzTableName2" resultMap="selectClazz2">
  128.         select id as id2,name from clazz as c where c.name = #{clazzName}
  129.     </select>
  131.     <select id="selectStudentListByClazzId" resultType="com.simon.Students">
  132.         select id,name,clazzid as clazzId from students where clazzid = #{clazzId}
  133.     </select>
  135.   <--多对多，分次查询--/>
  136.     <resultMap id="selectStudent2" type="com.simon.bean.Student">
  137.         <id column="id" property="id"/>
  138.         <result column="name" property="name"/>
  139.         <result column="age" property="age"/>
  141.         <collection property="course" ofType="com.simon.bean.Course" javaType="list"
  142.                     column="id" select="selectCourseList">
  144.         </collection>
  145.     </resultMap>
  146.     <select id="selectStudentByStudentName2" resultMap="selectStudent2">
  147.         SELECT
  148.             id,
  149.             name,
  150.             age
  151.         FROM
  152.             student
  153.         WHERE
  154.             name = #{studentName}
  155.     </select>
  157.     <select id="selectCourseList" resultType="com.simon.bean.Course">
  158.         SELECT
  159.             c.id AS id,
  160.             c.name AS name
  161.         FROM
  162.             s_c AS sc
  163.             LEFT JOIN course AS c ON sc.cid = c.id
  164.         WHERE
  165.             sc.sid = #{id};
  166.     </select>
  167. </mapper>

懒加载

简述

懒加载是为了提高分次查询效率的一种方式。在我们使用分次查询的时候，对于JavaBean的封装可能会分为两次。此时如果我们开启懒加载，那么第二次字段的封装会在我们需要它的时候才会去加载，也就是这时候才会去执行第二次sql查询。
懒加载分为全局懒加载和局部懒加载，这对概念类似于全局变量和局部变量，其用法也类似。懒加载的核心目的就是需要的时候才去加载，不需要的时候就不必去加载，这样就减轻了内存压力。

开启

  • 全局懒加载

mybatis-config.xml文件的settings中，加入：

  • 局部懒加载

在mapper.xml的resultMap的association标签中，加入fatchType属性：
fetchType = lazy 这个表示开启懒加载
fetchType=eager 这个表示关闭懒加载

缓存

简述

缓存类似于备份的概念。根据缓存级别的不同，分为一级缓存和二级缓存。一级缓存是sqlSession级别的，即会话级别，一旦会话结束，缓存就失效；二级缓存是namespace级别的，对应Java就是类，除非新建一个类，否则缓存就会一直存在。客户端访问的时候，先去二级缓存里面找，没找到再去一级缓存找，一级缓存也没找到，就去Mysql数据库里找。
image.png

一级缓存

image.png
image.png
总结，走一级缓存的情况：
同一个sqlSession，mapper可相同，也可不相同，同一个参数，都查询完再提交
不走一级缓存的情况：

  • 不同的sqlSession
  • 同一个sqlSession，不同的参数
  • 不是查询语句

    二级缓存

    开启

    1. <!--二级缓存开启-->
    2. <setting name="cacheEnabled" value="true" />

    序列化ID

    需要去给我们需要缓存的JavaBean实现序列化接口，生成序列化id
    开启idea设置
    MyBatis（下） - 图7image-20210402155823622.png
    MyBatis（下） - 图9
    MyBatis（下） - 图10
  1. 在我们需要开启二级缓存的mapper.xml中配置以下标签
    MyBatis（下） - 图11 image.png

使用

  1. // 测试二级缓存
  2. // Cache Hit Ratio [com.simon.mapper.UserMapper]: 0.0 这个表示二级缓存已经开启，但是没有命中
  3. @Test
  4. public void testSelectUserById5(){
  6.     SqlSession sqlSession1 = sqlSessionFactory.openSession();
  7.     SqlSession sqlSession2 = sqlSessionFactory.openSession();
  8.     SqlSession sqlSession3 = sqlSessionFactory.openSession();
  9.     SqlSession sqlSession4 = sqlSessionFactory.openSession();
  11.     UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
  12.     UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
  13.     UserMapper userMapper3 = sqlSession3.getMapper(UserMapper.class);
  14.     UserMapper userMapper4 = sqlSession4.getMapper(UserMapper.class);
  16.     User user1 = userMapper1.selectUserById(10); // 执行查询
  18.     sqlSession1.commit();                       // 把结果放入二级缓存
  20.     /**
  21.      * Cache Hit Ratio [com.simon.mapper.UserMapper]: 0.5
  22.      * Cache Hit Ratio [com.simon.mapper.UserMapper]: 0.6666666666666666
  23.      * Cache Hit Ratio [com.simon.mapper.UserMapper]: 0.75
  24.      * 
  25.      */
  26.     User user2 = userMapper2.selectUserById(10);
  27.     User user3 = userMapper3.selectUserById(10);
  28.     User user4 = userMapper4.selectUserById(10);
  30.     System.out.println(user1);
  31.     System.out.println(user2);
  32.     System.out.println(user3);
  33.     System.out.println(user4);
  35. }