动态SQL

最新推荐文章于 2025-01-16 10:16:27 发布

代码代码快快显灵

最新推荐文章于 2025-01-16 10:16:27 发布

阅读量1k

点赞数 22

分类专栏： MySQL 文章标签： sql java 数据库 1024程序员节

本文链接：https://blog.csdn.net/gege_0606/article/details/142900801

版权

MySQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

动态SQL是MyBatis中的一个非常重要的特性，它可以帮助生成灵活的SQL语句，以应对不同的查询需求。以下是动态SQL的一些知识点详细介绍：

1. 什么是动态SQL

动态SQL指的是根据不同的输入参数或条件，动态生成SQL语句的能力。MyBatis 提供了一些标签，用于在映射文件中编写动态 SQL 语句，以满足灵活的查询、插入、更新、删除等需求。

2. 动态SQL标签

MyBatis 提供了以下常用的动态SQL标签：

`1. <if>` 标签：根据条件来决定是否包含某段 SQL。


    /**
     * 根据多条件查询Car
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

<!--#{}里面写的是对应类名的变量名--> 
<select id="selectByMultiCondition" resultType="car">
        select * from t_car where
        <if test="brand != null and brand != ''">
            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>

    @Test
    public void testSelectByMultiCondition(){
        CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByMultiCondition("丰田", 20.0, "燃油车");
        System.out.println(cars);
    }

使用的注意事项

参数与占位符的匹配：
- #{} 里面应该填的是 @Param 注解中重命名后的参数名。如果是简单类型参数（如 String、Integer 等），则直接使用参数名。
- 如果参数是一个对象（如 Car 对象），那么应该使用对象的属性名，例如 #{car.brand}。
条件拼接问题：
- 在 <if> 标签中，拼接的条件可能存在语法错误，特别是在多个条件之间需要正确处理 AND 或者 OR。
- 如果第一个 <if> 中的条件不成立，而第二个或第三个条件成立，那么生成的 SQL 可能会有多余的 AND，需要小心处理。
例如
- 如果所有条件（brand、guidePrice、carType）都不满足，则 where 1=1 依然存在，这虽然能避免空 WHERE 的问题，但 1=1 是多余的，且在有大量条件时会显得非常繁琐。
- 如果不使用 where 1=1，而是直接在 <if> 标签中加上 AND，则如果第一个条件不成立可能会生成以下错误 SQL：
```
select * from t_car where AND ...（错误）
```

解决方案

为了避免上述错误，可以使用 MyBatis 提供的 <where> 或 <trim> 标签来生成动态 SQL：

<where> 标签：自动处理 WHERE 和条件之间的 AND 或 OR，避免生成空 WHERE 子句或多余的逻辑运算符。
<trim> 标签：可以手动设置前缀（如 WHERE），并去除前缀中多余的逻辑运算符

`2. <choose>`、`<when>` 和 `<otherwise>` 标签：

类似于 switch-case，用于多条件选择。

/**
* 使用choose when otherwise标签查询
* @param brand
* @param guidePrice
* @param produceTime
* @return
*/
List<Car> selectWithChoose(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("produceTime") String produceTime);

<select id="selectWithChoose" resultType="car">
  select * from t_car
  <where>
    <choose>
      <when test="brand != null and brand != ''">
        brand like #{brand}"%"
      </when>
      <when test="guidePrice != null and guidePrice != ''">
        guide_price >= #{guidePrice}
      </when>
      <otherwise>
        produce_time >= #{produceTime}
      </otherwise>
    </choose>
  </where>
</select>

只有一个分支会被选择！！！！如果所有 <when> 标签的条件都不成立，MyBatis 会自动执行 <otherwise> 标签中的逻辑，这就相当于 Java 中的 default 部分。

`3. <where>` 标签：

用于拼接 WHERE 子句，自动处理条件的前置逻辑，如 AND 或 OR。

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

<select id="findUserByParams" parameterType="map" resultType="User">
    SELECT * FROM user
    <where>
        <if test="username != null">
            username = #{username}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

<where> 标签可以确保生成的 SQL 以 WHERE 开头，并且会自动处理冗余的 AND。

`4. <trim>` 标签：

可以在 SQL 片段前后加上或去除指定的字符，比如加上 WHERE 或去除多余的 AND。

trim标签的属性：

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

/**
* 根据多条件查询Car，使用trim标签
* @param brand
* @param guidePrice
* @param carType
* @return
*/
List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

<select id="selectByMultiConditionWithTrim" resultType="car">
  select * from t_car
  <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>

如果字段都为空，那么最终的 SQL 语句是：

SELECT * FROM t_car

这意味着没有任何过滤条件，结果是查询 t_car 表中的所有数据。

`5.<set>` 标签：

确保生成的 SQL 中，不会因为字段之间的逗号导致语法错误。

主要使用在update语句当中，用来生成set关键字，同时去掉最后多余的“,” 比如我们只更新提交的不为空的字段，如果提交的数据是空或者""，那么这个字段我们将不更新。

/**
* 更新信息，使用set标签
* @param car
* @return
*/
int updateWithSet(Car car);

<update id="updateWithSet">
  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}
</update>

6. foreach标签

在 MyBatis 中，<foreach> 标签用于动态地遍历集合，例如数组、List、Set 等，以便生成动态 SQL。<foreach> 通常用于批量插入、更新或查询操作，是构建复杂 SQL 的强大工具。以下是关于 <foreach> 标签的详细介绍：

1. 基本语法和属性

<foreach> 标签的典型结构如下：

<foreach collection="集合" item="变量名" index="索引名" open="开始字符" close="结束字符" separator="分隔符">
    SQL 片段
</foreach>

collection：指定要遍历的集合。集合可以是数组、List、Set、Map 等，支持传递的参数名。
item：表示每次遍历的当前元素。
index（可选）：表示当前遍历的索引（对于 List、数组等来说是下标，对于 Map 是键）。
open（可选）：遍历生成的 SQL 片段的开头部分。
close（可选）：遍历生成的 SQL 片段的结尾部分。
separator（可选）：遍历元素之间的分隔符。

2. 示例用法

2.1 批量插入

假设我们有一个 car 表，我们想要一次性插入多条记录：


public interface CarMapper {
    // 批量插入汽车信息的方法
    int insertCars(@Param("carList") List<Car> carList);
}

<insert id="insertCars">
    INSERT INTO t_car (car_num, brand, guide_price, produce_time, car_type)
    VALUES
    <foreach collection="carList" item="car" separator=",">
        (#{car.carNum}, #{car.brand}, #{car.guidePrice}, #{car.produceTime}, #{car.carType})
    </foreach>
</insert>

解释：

collection="carList"：表示要遍历的集合名为 carList。
item="car"：每次遍历的元素存储在变量 car 中。
separator=","：每个 VALUES 片段之间使用逗号分隔。

假设 carList 中有三条记录，那么生成的 SQL 将类似于：

INSERT INTO t_car (car_num, brand, guide_price, produce_time, car_type)
VALUES
('C001', 'Toyota', 15000, '2023-01-01', 'SUV'),
('C002', 'Honda', 12000, '2022-12-01', 'Sedan'),
('C003', 'Ford', 18000, '2023-02-01', 'Truck')

2.2 批量更新

假设要批量更新汽车的价格，我们可以这样做：

<update id="updateCarPrices">
    <foreach collection="carList" item="car" separator=";">
        UPDATE t_car
        SET guide_price = #{car.guidePrice}
        WHERE car_num = #{car.carNum}
    </foreach>
</update>

解释：

这里的 <foreach> 标签用于批量更新每个汽车的价格。
separator=";"：生成的多个 UPDATE 语句之间用分号分隔。

生成的 SQL 将类似于：

UPDATE t_car SET guide_price = 15000 WHERE car_num = 'C001';
UPDATE t_car SET guide_price = 12000 WHERE car_num = 'C002';
UPDATE t_car SET guide_price = 18000 WHERE car_num = 'C003';

2.3 使用 `IN` 语句的查询

批量查询某些条件的记录，可以使用 IN 语句配合 <foreach> 标签：

<select id="selectCarsByTypes" resultType="car">
    SELECT * FROM t_car
    WHERE car_type IN
    <foreach collection="carTypes" item="type" open="(" separator="," close=")">
        #{type}
    </foreach>
</select>

解释：

collection="carTypes"：表示要遍历的集合名为 carTypes。
item="type"：每次遍历的当前元素是 type。
open="(" 和 close=")"：在生成的 SQL 片段两侧添加括号。
separator=","：每个元素之间用逗号分隔。

假设 carTypes 是 ["SUV", "Sedan", "Truck"]，那么生成的 SQL 将类似于：

SELECT * FROM t_car WHERE car_type IN ('SUV', 'Sedan', 'Truck')

2.4 用in来删除

/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach(@Param("ids") Long[] ids);

<!--
collection：集合或数组
item：集合或数组中的元素
separator：分隔符
open：foreach标签中所有内容的开始
close：foreach标签中所有内容的结束
-->
<delete id="deleteBatchByForeach">
  delete from t_car where id in
  <foreach collection="ids" item="id" separator="," open="(" close=")">
    #{id}
  </foreach>
</delete>

@Test
public void testDeleteBatchByForeach(){
    CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
    int count = mapper.deleteBatchByForeach(new Long[]{40L, 41L, 42L});
    System.out.println("删除了几条记录：" + count);
    SqlSessionUtil.openSession().commit();
}

2.5 用or来删除

/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach2(@Param("ids") Long[] ids);

<delete id="deleteBatchByForeach2">
  delete from t_car where
  <foreach collection="ids" item="id" separator="or">
    id = #{id}
  </foreach>
</delete>