Mybatis中的动态SQL

1. 概述

动态SQL：MyBatis对sql语句进行动态的拼接。

比如：

• 我们要查询姓名中带 M 和 高于 1000的员工信息；
• 可能有时候我们需要不带条件查询；
• 可能有时候我们需要模糊查询；
• 可能有时候需要根据多条件查询；
• 动态SQL可以帮助我们解决这些问题。
• 通过Mybatis提供的各种标签方法实现动态拼接sql。

以下业务场景，需要SQL语句是动态的，例如：

• 批量删除
  • 

  • delete from t_car where id in(1,2,3,4,5,6,......这里的值要是动态的，根据用户选择的id不同，值是不同的);

• 多条件查询

  • 

  • select * from t_car where brand like '丰田%' and guide_price > 30 and .....;

创建模块：mybatis-008-dynamic-sql

打包方式：jar

引入依赖：mysql驱动依赖、mybatis依赖、junit依赖、logback依赖

pojo：com.powernode.mybatis.pojo.Car

mapper接口：com.powernode.mybatis.mapper.CarMapper

引入配置文件：mybatis-config.xml、jdbc.properties、logback.xml

mapper配置文件：com/powernode/mybatis/mapper/CarMapper.xml

编写测试类：com.powernode.mybatis.test.CarMapperTest

拷贝工具类：SqlSessionUtil

2. if标签

• 作用：使用 if标签的test属性设置的判断条件，动态拼接SQL 语句。

• test属性：为true时拼接if标签中的sql语句；为false时不会。

  • test属性中如何设置判断条件:

    • 当传入pojo对象时，直接写POJO对象的属性名。

    • 当使用了@Param注解，使用这个注解指定的别名，如@Param("brand")，那么这里写brand。

    • 当没有使用参数注解时，只能写param1,param2,param3,arg0,arg1,arg2...

• 在mybatis的动态sql语句中，不能使用&&，只能使用and

需求：进行动态多条件查询。

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

CarMapper.java


package com.powernode.mybatis.mapper;

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

import java.util.List;

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);
}

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">
<!-- 语法格式：在sql语句的下面同级使用if双标签，if双标签里面写要动态拼接的SQL语句。-->
<mapper namespace="org.example.mapper.CarMapper">
    <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>
</mapper>

CarMapperTest.java


package com.powernode.mybatis.test;

import com.powernode.mybatis.mapper.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.junit.Test;

import java.util.List;

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

执行结果：

如果第一个条件为空，剩下两个条件不为空：会出现SQL语句拼接错误的问题，导致SQL语法错误，where后面出现了第二个条件的and。

修改测试程序为：

CarMapperTest.java


List<Car> cars = mapper.selectByMultiCondition("", 20.0, "燃油车");

执行结果：

这该怎么解决呢？

• 可以在where语句后面添加一个恒成立的条件。

执行结果：

• 如果三个条件都为空：也没有什么影响

CarMapperTest.java

List<Car> cars = mapper.selectByMultiCondition("", null, "");

执行结果：

• 三个条件都不为空呢？

CarMapperTest.java


List<Car> cars = mapper.selectByMultiCondition("丰田", 20.0, "燃油车");

执行结果：

3. where标签

作用：动态生成where子句

• 如果where标签里面所有的if标签都不成立，就不会生成where子句；只要有一个条件成立就会在SQL语句中拼接where关键字。

• where标签可以自动去除 条件 前面多余的and或or，但不可以自动去除条件后面多余的and

继续使用if标签中的需求：

CarMapper.java


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

CarMapper.xml

<!-- 语法格式：在sql语句的下面同级使用where双标签 -->
<select id="selectByMultiConditionWithWhere" resultType="car">
  select * from t_car
  <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>

CarMapperTest.java


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

运行结果：

如果所有条件都是空：它可以自动去掉前面多余的and

CarMapperTest.java


List<Car> cars = mapper.selectByMultiConditionWithWhere("", null, "");

运行结果：

它可以自动去掉前面多余的and，但不可以自动去掉后面多余的and：

CarMapper.xml


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

CarMapperTest.java


// 让最后一个条件为空
List<Car> cars = mapper.selectByMultiConditionWithWhere("丰田", 20.0, "");

运行结果：

4. set标签

背景：要使用set标签，因为当提交的数据是空或""时，会把表中记录的对应字段也更新为空。

CarMapper接口

    /**
     * 根据id更新记录
     * @param car
     * @return
     */
    int updateById(Car car);

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="org.example.mapper.CarMapper">
    <update id="updateById">
        update t_car
        set
            car_num = #{carNum},
            brand = #{brand},
            guide_price = #{guidePrice},
            produce_time = #{produceTime},
            car_Type = #{carType}
        where
            id = #{id}
    </update>
</mapper>

    @Test
    public void testUpdateById(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(18L,null,"丰田霸道",null,null,"燃油车");
        mapper.updateById(car);
        sqlSession.commit();
        sqlSession.close();
    }

结果：

---

• 作用：动态生成UPDATE语句的SET子句。

  • 如果set标签内的所有if标签都不成立，不会拼接set关键字，只要有一个条件成立就会在SQL语句中拼接set关键字。

  • 会自动剔除sql语句末尾不相关的逗号

• 语法格式：sql语句下面同级使用set标签，然后在嵌套if标签来指定动态生成哪些sql语句。

CarMapper接口


    /**
     * 使用set标签来更新记录
     * @param car
     * @return
     */
    int updateBySet(Car car);

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="org.example.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}
    </update>
<mapper/>

CarMapperTest.java


    @Test
    public void testupdateBySet(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(18L,null,"丰田霸道",null,null,null);
        mapper.updateBySet(car);
        sqlSession.commit();
        sqlSession.close();
    }

执行结果：

5. trim标签

语法格式：和SQL语句同级，在其下面写trim标签。

trim标签的属性：

• prefix：给trim标签中的sql语句前面添加内容，例如prefix="where"，给trim标签中所有内容的前面加上where

• suffix：在trim标签中的sql语句后添加内容

• prefixOverrides：删除前缀

• suffixOverrides：删除后缀，例如suffixOverrides="where|or"，把trim标签中所有内容的后缀去掉and或or

CarMapper接口


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

CarMapper.xml


<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>

CarMapperTest.java


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

当所有条件为空，where就不会被加上:

CarMapperTest.java


List<Car> cars = mapper.selectByMultiConditionWithTrim("", null, "");

运行结果：

6. foreach标签

• 作用：当传入数组或集合到Mapper文件中，使用foreach标签，遍历数组或集合中的每个元素，循环生成sql语句，实现动态生成sql。

• foreach标签主要用于批量删除和批量插入。

• foreach标签中有如下属性：

  • collection属性:要遍历的数组或集合名（指定mapper方法的数组或集合名）

  • item属性：用来代表数组或集合中的每个元素

  • separator属性：循环遍历的每对象间用什么分隔。

  • open属性:开始遍历前以什么开始

  • close属性:遍历完后以什么结束

应用：批量删除

先来看看以前如何批量删除数据库表中的记录。

delete from t_car where id in(1,2,3);
delete from t_car where id = 1 or id = 2 or id = 3;

下面使用foreach标签来批量删除。

• 用in集合运算符来删除

CarMapper接口


    /**
     * 批量删除，使用foreach标签
     * @param ids
     * @return
     */
    int deleteByIds(@Param("ids") Long[] ids);

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="org.example.mapper.CarMapper">
    <delete id="deleteByIds">
        <!-- delete from t_car where id in(1,2,3); -->
        delete from t_car where id in(
            <foreach collection="ids" item="id" separator=",">
                #{id}
            </foreach>
            )
    </delete>
</mapper>

CarMapperTest.java


    @Test
    public void testDeleteByIds(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids = {13L,14L,15L};
        mapper.deleteByIds(ids);
        sqlSession.commit();
        sqlSession.close();
    }

运行结果：

• 用or来删除

CarMapper接口


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

CarMapper.xml


<delete id="deleteBatchByForeach2">
  <!-- delete from t_car where id = 1 or id = 2 or id = 3; -->
  delete from t_car where
  <foreach collection="ids" item="id" separator="or">
    id = #{id}
  </foreach>
</delete>

CarMapperTest.java


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

运行结果：

应用：批量添加

先来看看以前如何批量添加记录到数据库表中。

insert into t_car values
  (null,'1001','凯美瑞',35.0,'2010-10-11','燃油车'),
  (null,'1002','比亚迪唐',31.0,'2020-11-11','新能源'),
  (null,'1003','比亚迪宋',32.0,'2020-10-11','新能源')

下面使用foreach标签来批量添加。

CarMapper接口


    /**
     * 批量插入，一次多次插入多条car记录
     * @param cars
     * @return
     */
    int insertBatch(@Param("cars")List<Car> cars);

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="org.example.mapper.CarMapper">
    <insert id="insertBatch">
        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/>

CarMapperTest.java


    @Test
    public void testInsertBatch(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car1 = new Car(null,"1200","帕萨特1",30.0,"2002-06-99","燃油车");
        Car car2 = new Car(null,"1200","帕萨特2",30.0,"2002-06-99","燃油车");
        Car car3 = new Car(null,"1200","帕萨特3",30.0,"2002-06-99","燃油车");
        List<Car> cars = new ArrayList<>();
        cars.add(car1);
        cars.add(car2);
        cars.add(car3);
        mapper.insertBatch(cars);
        sqlSession.commit();
        sqlSession.close();
    }

执行结果：

7. choose标签

choose标签没什么用，这里不述说。主要掌握if、where、foreach、set标签即可，trim标签用的也少。

8、SQL片段

把在sql中频繁使用的代码抽取出来，组成一个sql片段，然后在使用的地方包含进入即可。

sql片段里面不要包含where标签。

• sql标签：用来定义sql片段

• include标签：用来引用sql片段

• 作用：使代码能够复用，易维护。

<sql id="feildSql">
    empno,ename,job,mgr,hiredate,sal,comm,deptno
</sql>

<sql id="whereSql">
    <if test="ename != null and ename != ''">
        ename like concat('%',#{ename},'%')
    </if>
    <if test="sal != null">
        and sal=#{sal}
    </if>
    <if test="deptno != null">
        and deptno=#{deptno}
    </if>
</sql>

<select id="selectUseSql" parameterType="com.gs.entity.Emp" resultType="com.gs.entity.Emp">
    select
    <include refid="feildSql"></include>
    from emp
    <where>
        <include refid="whereSql"></include>
    </where>
</select>