动态SQL

动态SQL
if 标签
where 标签
trim 标签
set 标签
choose when otherwise
foreach 标签
sql 标签与include 标签

十二、★动态SQL

准备工作：
- 模块名：mybatis-009-dynamic-sql
- 打包方式：jar
- 引入依赖：mysql驱动依赖、mybatis依赖、logback依赖、junit依赖。
- 引入配置文件：jdbc.properties、mybatis-config.xml、logback.xml
- 创建 pojo 类：Car
- 创建 Mapper 接口：com.f.mybatis.mapper.CarMapper
- 创建 Mapper 接口对应的映射文件：com/f/mybatis/mapper/CarMapper.xml
- 创建单元测试：CarMapperTest
- 创建工具类：SqlSessionUtil

12.1 if 标签

需求：多条件查询。可能的条件包括：品牌（brand）、指导价格（guide_price）、汽车类型（car_type）。

还是像 10.1 小节所说的三步走：

package com.f.mybatis.mapper;

import com.f.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author fzy
 * @date 2024/1/10 15:29
 */
public interface CarMapper {
    /**
     * 多条件查询
     * @param brand 品牌
     * @param guidePrice 指导价
     * @param carType 汽车类型
     * @return
     */
    List<Car> selectByMultiCondition(@Param("brand") String brand,
                                     @Param("guidePrice") Double guidePrice,
                                     @Param("carType") String carType);
}

<?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.f.mybatis.mapper.CarMapper">
    <select id="selectByMultiCondition" resultType="Car">
        SELECT *
        FROM t_car
        WHERE 1 = 1 # 1 = 1是为了避免下面三个test都为false的情况
        <!--
            1.if标签中的test属性是必须的
            2，if标签中test属性的值是true或者false
            3.如果test是true，则if标签中的sql内容就会拼接，反正则不会拼接。
            4.test中可以使用的是：
                多参数：当使用了@Param注解，那么test中要出现的就是@Param注解指定的参数名，例如@Param("brand")，那么这里只能使用brand
                多参数：当没有使用@Param注解，那么test中要出现的是：param1、param2、param3... arg0、arg1、arg2...
                单参数：当使用了POJO，那么test中要出现的就是POJO类的属性名
            5.在mybatis的动态sql的test表达式中，不能使用&&，只能使用and
        -->
        <if test="brand != null and brand != ''">
            AND brand like "%"#{brand}"%"
        </if>
        <if test="guidePrice != null and guidePrice != ''">
            AND guide_price > #{guidePrice}
        </if>
        <if test="carType != null and carType != ''">
            AND car_type = #{carType}
        </if>
    </select>
</mapper>

@Test
public void testSelectByMultiCondition() {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = mapper.selectByMultiCondition("问界", null, "新能源");
    cars.forEach(car -> {
        System.out.println(car);
    });
    SqlSessionUtil.close(sqlSession);
}

12.2 where 标签

where 标签的作用：让 where 子句更加动态智能。

所有条件都为空时，where 标签保证不会生成 where 子句。
自动去除某些条件前面多余的 and 或 or。

package com.f.mybatis.mapper;

import com.f.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author fzy
 * @date 2024/1/10 15:29
 */
public interface CarMapper {
    /**
     * 多条件查询，使用where标签，让where子句更加智能
     *
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,
                                              @Param("guidePrice") Double guidePrice,
                                              @Param("carType") String carType);
}

<?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.f.mybatis.mapper.CarMapper">
    <select id="selectByMultiConditionWithWhere" resultType="Car">
        SELECT * FROM t_car
        /*where标签是专门负责where子句动态生成的*/
        <where>
            <if test="brand != null and brand != ''">
                AND brand like "%"#{brand}"%"
            </if>
            <if test="guidePrice != null and guidePrice != ''">
                AND guide_price > #{guidePrice}
            </if>
            <if test="carType != null and carType != ''">
                AND car_type = #{carType}
            </if>
        </where>
    </select>
</mapper>

@Test
public void testSelectByMultiConditionWithWhere() {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = mapper.selectByMultiConditionWithWhere("问界", null, "新能源");
    cars.forEach(car -> {
        System.out.println(car);
    });
    SqlSessionUtil.close(sqlSession);
}

12.3 trim 标签

trim 标签的属性：

prefix：在 trim 标签中的语句前添加内容
suffix：在 trim 标签中的语句后添加内容
prefixOverrides：前缀覆盖掉（去掉）
suffixOverrides：后缀覆盖掉（去掉）

package com.f.mybatis.mapper;

import com.f.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author fzy
 * @date 2024/1/10 15:29
 */
public interface CarMapper {
    /**
     * 多条件查询，使用trim标签
     *
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,
                                             @Param("guidePrice") Double guidePrice,
                                             @Param("carType") String carType);
}

<?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.f.mybatis.mapper.CarMapper">
    <select id="selectByMultiConditionWithTrim" resultType="Car">
        SELECT * FROM t_car
        <!--
            prefix: 加前缀
            prefixOverrides: 删除前缀
            suffix: 加后缀
            suffixOverrides: 删除后缀
            <trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""></trim>
        -->
        <!--prefix="where" 是在trim标签所有内容的前面添加 where-->
        <!--suffixOverrides="and|or" 是把trim标签中内容的后缀and或or去掉-->
        <trim prefix="where" suffixOverrides="and|or">
            <if test="brand != null and brand != ''">
                brand like "%"#{brand}"%" and
            </if>
            <if test="guidePrice != null and guidePrice != ''">
                guide_price >= #{guidePrice} and
            </if>
            <if test="carType != null and carType != ''">
                car_type = #{carType}
            </if>
        </trim>
    </select>
</mapper>

@Test
public void testSelectByMultiConditionWithTrim() {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = mapper.selectByMultiConditionWithTrim("问界", null, "新能源");
    cars.forEach(car -> {
        System.out.println(car);
    });
    SqlSessionUtil.close(sqlSession);
}

12.4 set 标签

主要使用在 update 语句当中，用来生成 set 关键字，同时去掉最后多余的 , 。

比如我们只更新提交的不为空的字段，如果提交的数据是空或者 ""，那么这个字段我们将不更新。（set + if）

package com.f.mybatis.mapper;

import com.f.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author fzy
 * @date 2024/1/10 15:29
 */
public interface CarMapper {
    /**
     * 更新数据，使用set标签
     *
     * @param car
     * @return
     */
    int updateBySet(Car car);
}

<?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.f.mybatis.mapper.CarMapper">
    <update id="updateBySet">
        UPDATE t_car
        <set>
            <if test="carNum != null and carNum != ''">car_num = #{carNum},</if>
            <if test="brand != null and brand != ''">brand = #{brand},</if>
            <if test="guidePrice != null and guidePrice != ''">guide_price = #{guidePrice},</if>
            <if test="produceTime != null and produceTime != ''">produce_time = #{produceTime},</if>
            <if test="carType != null and carType != ''">car_type = #{carType},</if>
        </set>
        <where>
            id = #{id}
        </where>
    </update>
</mapper>

@Test
public void testUpdateBySet() {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Car car = new Car();
    car.setId(4L);
    car.setCarType("氢能");
    int count = mapper.updateBySet(car);
    System.out.println(count);
    sqlSession.commit();
    SqlSessionUtil.close(sqlSession);
}

12.5 choose when otherwise

这三个标签是在一起使用的：

<choose>
  <when></when>
  <when></when>
  <when></when>
  <otherwise></otherwise>
</choose>

等同于：

if(){
    
}else if(){
    
}else if(){
    
}else if(){
    
}else{

}

只有一个分支会被选择。

需求：先根据品牌查询，如果没有提供品牌，再根据指导价格查询，如果没有提供指导价格，就根据汽车类型查询。

package com.f.mybatis.mapper;

import com.f.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author fzy
 * @date 2024/1/10 15:29
 */
public interface CarMapper {
    /**
     * 使用choose when otherwise标签
     *
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByChoose(@Param("brand") String brand,
                             @Param("guidePrice") Double guidePrice,
                             @Param("carType") String carType);
}

<?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.f.mybatis.mapper.CarMapper">
    <select id="selectByChoose" resultType="Car">
        SELECT * FROM t_car
        <where>
            <choose>
                <when test="brand != null and brand != ''">brand = #{brand}</when>
                <when test="guidePrice != null and guidePrice != ''">guide_price > #{guidePrice}</when>
                <otherwise>car_type = #{carType}</otherwise>
            </choose>
        </where>
    </select>
</mapper>

@Test
public void testSelectByChoose() {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = mapper.selectByChoose("问界", null, "新能源");
    cars.forEach(car -> {
        System.out.println(car);
    });
    SqlSessionUtil.close(sqlSession);
}

12.6 foreach 标签

循环数组或集合，动态生成 sql。

批量删除

package com.f.mybatis.mapper;

import com.f.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author fzy
 * @date 2024/1/10 15:29
 */
public interface CarMapper {
    /**
     * 批量删除，使用foreach标签
     *
     * @param ids
     * @return
     */
    int deleteByIds(@Param("ids") Long[] ids);
}

<?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.f.mybatis.mapper.CarMapper">
    <delete id="deleteByIds">
        DELETE FROM t_car
        WHERE id in
        <!--
            foreach标签的属性：
                collection：指定数组或者集合
                item：代表数组或集合中的元素
                separator：循环之间的分隔符
                open: foreach循环拼接的所有sql语句的最前面以什么开始。
                close: foreach循环拼接的所有sql语句的最后面以什么结束。
        -->
        <!--
            当没有@Param("ids")时，collection="ids" 报错，报错信息是
            Parameter 'ids' not found. Available parameters are [array, arg0]
            说明mybatis存数组的时候，默认是
                map.put("array", 数组);
                map.put("arg0", 数组);
        -->
        <foreach collection="ids" item="aaaaa" separator="," open="(" close=")">
            #{aaaaa}
        </foreach>
    </delete>
</mapper>

@Test
public void testDeleteByIds() {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Long[] ids = {1L, 2L, 3L};
    int count = mapper.deleteByIds(ids);
    System.out.println(count);
    sqlSession.commit();
    SqlSessionUtil.close(sqlSession);
}

批量添加

package com.f.mybatis.mapper;

import com.f.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author fzy
 * @date 2024/1/10 15:29
 */
public interface CarMapper {
    /**
     * 批量插入，使用foreach标签
     *
     * @param cars
     * @return
     */
    int insertCars(@Param("cars") List<Car> cars);
}

<?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.f.mybatis.mapper.CarMapper">
    <insert id="insertCars">
        INSERT INTO t_car
        VALUES
        <foreach collection="cars" item="car" separator=",">
            (null, #{car.carNum}, #{car.brand}, #{car.guidePrice}, #{car.produceTime}, #{car.carType})
        </foreach>
    </insert>
</mapper>

@Test
public void testInsertCars() {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = new ArrayList<>();
    cars.add(new Car(null, "4444", "宝马", 55.00, "2010-01-01", "汽油"));
    cars.add(new Car(null, "5555", "福特", 25.00, "2020-11-01", "汽油"));
    mapper.insertCars(cars);
    sqlSession.commit();
    SqlSessionUtil.close(sqlSession);
}

12.7 sql 标签与include 标签

sql 标签用来声明 sql 片段。

include 标签用来将声明的 sql 片段包含到某个 sql 语句当中。

作用：代码复用，易维护。

<sql id="carColumnName">id,car_num AS carNum,brand,guide_price AS guidePrice,produce_time AS produceTime,car_type AS carType</sql>

<select id="selectAllRetMap" resultType="map">
  select <include refid="carColumnName"/> from t_car
</select>

<select id="selectAllRetListMap" resultType="map">
  select <include refid="carColumnName"/> carType from t_car
</select>

<select id="selectByIdRetMap" resultType="map">
  select <include refid="carColumnName"/> from t_car where id = #{id}
</select>