MyBatis 动态SQL 缓存（Cache）关联关系-嵌套查询

Author：Exchanges

Version：9.0.2

目录

• 一、动态SQL【重点】
  • 1.1 环境准备
  • 1.2 < sql >
  • 1.3 < if > 和 < where >
  • 1.4< set >
  • 1.5 < trim >
  • 1.6 < foreach >
• 二、缓存（Cache）【重点】
  • 2.1 一级缓存
  • 2.2 二级缓存
    • 2.2.1 开启全局缓存
    • 2.2.2 指定Mapper缓存
• 三、关联关系-嵌套查询【了解】
  • 3.1 环境准备
  • 3.2 延迟加载
    • mybatis-config.xml中开启延迟加载

一、动态SQL【重点】

MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接成完整的SQL之后再执行，以达到SQL复用、简化编程的效果。

1.1 环境准备

1.创建表：

CREATE TABLE `t_car`  (
  `id` INT(11) PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(256),
  `brand` VARCHAR(256),
  `price` DOUBLE,
  `color` VARCHAR(256),
  `num` INT(11)
) ENGINE = INNODB  CHARACTER SET = utf8

INSERT INTO t_car(NAME,brand,price,color,num) VALUES('迈腾','大众',200000,'黑色',5000);
INSERT INTO t_car(NAME,brand,price,color,num) VALUES('帕萨特','大众',200000,'黑色',6000);
INSERT INTO t_car(NAME,brand,price,color,num) VALUES('卡宴','保时捷',700000,'白色',2000);
INSERT INTO t_car(NAME,brand,price,color,num) VALUES('奥迪Q3','奥迪',300000,'白色',3000);
INSERT INTO t_car(NAME,brand,price,color,num) VALUES('宝马X5','宝马',500000,'黑色',2000);
INSERT INTO t_car(NAME,brand,price,color,num) VALUES('雅阁','本田',180000,'黑色',3000);

2.创建实体类

package com.qf.pojo;

import lombok.Data;

@Data
public class Car {

    private Integer id;
    private String name;
    private String brand;
    private Double price;
    private String Color;
    private Integer num;

}

3.创建QueryVo（以三个查询条件为例）

package com.qf.vo;

import lombok.Data;

@Data
public class QueryVo {

    private String brand;
    private Double price;
    private Integer num;

}

4.创建CarMapper以及CarMapper.xml，编写相关代码测试动态SQL标签

1.2 < sql >

在CarMapper中添加查询所有的方法

public List<Car> findAll();

在CarMapper.xml中添加查询所有的方法

<?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.qf.mapper.CarMapper">

    <resultMap id="carMap" type="com.qf.pojo.Car">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="brand" column="brand"></result>
        <result property="price" column="price"></result>
        <result property="color" column="color"></result>
        <result property="num" column="num"></result>
    </resultMap>
    
    <!-- sql片段 -->
    <sql id="baseSql">
        select id,name,brand,price,color,num from t_car
    </sql>

    <!-- 查询所有 -->
    <select id="findAll" resultMap="carMap">
        <!-- 引入sql片段 -->
        <include refid="baseSql"></include>
    </select>

</mapper>

在测试类中进行测试，如果使用MybatisUtil工具类测试需提前引入

@Test
public void testFindAll() throws Exception{
    InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //------------------------------------------

    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = carMapper.findAll();

    System.out.println(cars);
    //------------------------------------------
    //关闭
    sqlSession.close();
    inputStream.close();
}

1.3 < if > 和 < where >

在CarMapper中添加相关方法

public List<Car> findCar(QueryVo queryVo);

在CarMapper.xml中添加相关代码

<!-- 条件查询 -->
<select id="findCar" resultMap="carMap">
    <include refid="baseSql"></include>
    <!-- where标签可以忽略前 and | or -->
    <where>
        <if test="brand != null and brand != ''">
            brand = #{brand}
        </if>
        <if test="price != null and price != ''">
            and price > #{price}
        </if>
        <if test="num != null and num != ''">
            and num > #{num}
        </if>
    </where>
</select>

在测试类中进行测试

@Test
public void testFindCar() throws Exception{
    InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //------------------------------------------
    //条件
    String brand = null;
    Double price = 200000.0;
    Integer num = 1000;

    QueryVo queryVo = new QueryVo();
    queryVo.setBrand(brand);
    queryVo.setPrice(price);
    queryVo.setNum(num);


    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = carMapper.findCar(queryVo);

    System.out.println(cars);
    //------------------------------------------
    //关闭
    sqlSession.close();
    inputStream.close();

}

1.4< set >

在CarMapper中添加相关方法

public void update(Car car);

在CarMapper.xml中添加相关代码

<update id="update">
    UPDATE t_car
    <!-- set标签可以忽略逗号 -->
    <set>
        <if test="name != null and name != ''">
            NAME = #{name},
        </if>
        <if test="color != null and color != ''">
            color = #{color}
        </if>
    </set>
    <where>
        id = #{id}
    </where>
</update>

在测试类中进行测试

@Test
public void testUpdate() throws Exception{
    InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //------------------------------------------

    Car car = new Car();
    car.setId(6);
    car.setName("桑塔纳");
    car.setColor("白色");

    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    carMapper.update(car);

    //提交
    sqlSession.commit();
    //------------------------------------------
    //关闭
    sqlSession.close();
    inputStream.close();

}

1.5 < trim >

< trim prefix=”” suffix=”” prefixOverrides=”” suffixOverrides=”” >代替< where > 、< set >

同样是修改，写法不同而已

<update id="update">
    UPDATE t_car
    <!-- trim标签可以替换where和set标签 -->
    <trim prefix="set" suffixOverrides=",">
        <if test="name != null and name != ''">
            NAME = #{name},
        </if>
        <if test="color != null and color != ''">
            color = #{color}
        </if>
    </trim>
    <trim prefix="where" prefixOverrides="and | or">
        id = #{id}
    </trim>

</update>

1.6 < foreach >

对数组或者集合进行遍历操作

在CarMapper中添加相关方法

//批量删除操作
//public void deleteByIds(Integer [] arr);
//public void deleteByIds(List<Integer> ids);
public void deleteByIds(@Param("ids") Set<Integer> ids);

在CarMapper.xml中添加相关代码

<!-- 参数为数组时，collection = array -->
<!-- 参数为List集合时，collection = list -->
<!-- 参数为Set集合时，collection = 方法参数中自定义的名称 -->
<delete id="deleteByIds">
    delete from t_car
    <where>
        id in
        <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </where>
</delete>

在测试类中进行测试

@Test
public void testDeleteByIds() throws Exception{
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//------------------------------------------

//Integer[] arr = new Integer[]{5,6};
//Integer[] arr = {5,6};

//List<Integer> ids = Arrays.asList(3,4);

    HashSet<Integer> set = new HashSet<>();
    set.add(1);
    set.add(2);

    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    //carMapper.deleteByIds(arr);
    //carMapper.deleteByIds(ids);
    carMapper.deleteByIds(set);

    //提交
    sqlSession.commit();
    //------------------------------------------
    //关闭
    sqlSession.close();
    inputStream.close();

}

二、缓存（Cache）【重点】

内存中的一块存储空间，服务于某个应用程序，旨在将频繁读取的数据临时保存在内存中，便于二次快速访问。

无缓存：用户在访问相同数据时，需要发起多次对数据库的直接访问，导致产生大量IO、读写硬盘的操作，效率低下

有缓存：首次访问时，查询数据库，将数据存储到缓存中；再次访问时，直接访问缓存，减少IO、硬盘读写次数、提高效率

2.1 一级缓存

SqlSession级别的缓存，同一个SqlSession的发起多次同构查询，会将数据保存在一级缓存中。

注：无需任何配置，默认开启一级缓存

在CarMapper中添加相关方法

//查询单个
public Car findById(Integer id);

在CarMapper.xml中添加相关代码

<!-- 查询单个 -->
<select id="findById" resultMap="carMap">
    <!-- 引入sql片段 -->
    <include refid="baseSql"></include>
    <where>
        id = #{id}
    </where>
</select>

在测试类中进行测试

//测试一级缓存sqlSession
@Test
public void testFindById() throws Exception{
    InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //------------------------------------------

    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    Car car = carMapper.findById(5);
    //如果清空或者提交，则一级缓存就没有了
    //当调用SqlSession的修改，添加，删除，commit()，close()等方法时也会清空一级缓存。
    sqlSession.clearCache();//清空缓存
    Car car2 = carMapper.findById(5);

    //证明SqlSession级别的一级缓存存在
    System.out.println(car == car2);
    //------------------------------------------
    //关闭
    sqlSession.close();
    inputStream.close();
}

2.2 二级缓存

SqlSessionFactory级别的缓存，同一个SqlSessionFactory构建的SqlSession发起的多次同构查询，会将数据保存在二级缓存中。

注：在sqlSession.commit()或者sqlSession.close()之后生效。

2.2.1 开启全局缓存

< settings >是MyBatis中极为重要的调整设置，他们会改变MyBatis的运行行为，其他详细配置可参考官方文档。

<configuration>
	<properties .../>
  	
  	<!-- 注意书写位置 -->
    <settings>
        <setting name="cacheEnabled" value="true"/> <!-- mybaits-config.xml中开启全局缓存（默认开启） -->
    </settings>
  
  	<typeAliases></typeAliases>
</configuration>

2.2.2 指定Mapper缓存

在CarMapper.xml中添加相关代码

<!-- 开启当前二级缓存 -->
<cache/>

在测试类中进行测试

//测试二级缓存sqlSession
@Test
public void testFindById2() throws Exception{
    InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    SqlSession sqlSession1 = sqlSessionFactory.openSession();
    CarMapper carMapper1 = sqlSession1.getMapper(CarMapper.class);
    Car car1 = carMapper1.findById(7);
    sqlSession1.close();

    SqlSession sqlSession2 = sqlSessionFactory.openSession();
    CarMapper carMapper2 = sqlSession2.getMapper(CarMapper.class);
    Car car2 = carMapper2.findById(7);

    //------------------------------------------
    //关闭
    //sqlSession1.close();
    //sqlSession2.close();
    inputStream.close();
}

三、关联关系-嵌套查询【了解】

3.1 环境准备

可以用之前的员工和部门的案例，只需要修改对应的Mapper.xml文件即可

EmployeeMapper.xml

<?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.qf.mapper.EmployeeMapper">

    <!-- 通过resultMap结果映射，解决属性名和列名不一致问题 -->
    <resultMap id="employeeMap" type="com.qf.pojo.Employee">
        <!-- 主键 -->
        <id property="id" column="employeeId"></id>
        <!-- 非主键属性 -->
        <result property="name" column="employeeName"></result>
        <result property="salary" column="salary"></result>
        <result property="deptId" column="dept_id"></result>

        <association property="department" javaType="com.qf.pojo.Department"
        select="com.qf.mapper.DepartmentMapper.findById" column="dept_id">
        </association>

    </resultMap>

    <!-- 注意：表中如果有相同列名，在查询时需要设置不同别名，否则会被覆盖 -->
    <select id="findById" resultMap="employeeMap">
        SELECT e.id employeeId,e.name employeeName,e.salary,e.dept_id
        FROM t_employee e WHERE e.id = #{id}
    </select>

</mapper>

DepartmentMapper.xml

<?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.qf.mapper.DepartmentMapper">

<resultMap id="departmentMap" type="com.qf.pojo.Department">
    <!-- 主键 -->
    <id property="id" column="id"></id>
    <!-- 非主键属性 -->
    <result property="name" column="name"></result>
    <result property="location" column="location"></result>

</resultMap>

	<select id="findById" resultMap="departmentMap">
        SELECT d.id,d.NAME,d.location
        FROM t_department d WHERE d.id = #{id}
    </select>

</mapper>

测试

package com.qf.test;

import com.qf.mapper.DepartmentMapper;
import com.qf.mapper.EmployeeMapper;
import com.qf.pojo.Department;
import com.qf.pojo.Employee;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.InputStream;

public class MyBatisTest {

    @Test
    public void testFindByEmployeeId() throws Exception{
        //构建sqlSessionFactory
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //获取DepartmentMapper对象
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

        //调用方法
        Employee employee = employeeMapper.findById(3);

        //System.out.println(employee.getDepartment().getName());

        //关闭
        sqlSession.close();
        inputStream.close();

    }

}

3.2 延迟加载

mybatis-config.xml中开启延迟加载

<settings>
	<setting name="lazyLoadingEnabled" value="true"/> <!-- 开启延迟加载（默认false） -->
</settings>

注意：开启延迟加载后，如果不使用及联数据，则不会触发及联查询操作，有利于加快查询速度、节省内存资源。