MyBatis-动态SQL

动态SQL

有的场景不能把sql语句写死了，比如查询条件有可能一个，也有可能多个。。。

SqlSessionUtil:

public class SqlSessionUtil {
    private static SqlSessionFactory sqlSessionFactory;
    private static ThreadLocal<SqlSession> local = new ThreadLocal<>();

    private SqlSessionUtil() {
    }

    static {
        try {
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static SqlSession openSqlSession() {
        SqlSession sqlSession = local.get();
        if (sqlSession == null) {
            sqlSession = sqlSessionFactory.openSession();
            local.set(sqlSession);
        }
        return sqlSession;
    }

    /**
     * 关闭sqlSession
     * 移除线程
     *
     * @param sqlSession
     */
    public static void close(SqlSession sqlSession) {
        if (sqlSession != null) {
            sqlSession.close();
            local.remove();
        }
    }
}

1.if标签

需求：多条件查询

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

接口方法：

//多个参数依旧是使用@Param()注解
List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

CurMapper.xml:

<select id="selectByMultiCondition" resultType="Car">
    select * from t_car where 1=1
    <!--
        1. if标签中test属性是必须的。
        2. if标签中test属性的值是false或者true。
        3. 如果test是true，则if标签中的sql语句就会拼接。反之，则不会拼接。
        4. test属性中可以使用的是：
            使用@Param注解，那么test中要出现的是@Param注解指定的参数名。@Param("brand")，那么这里只能使用brand
            使用POJO，那么test中出现的是POJO类的属性名。
        5. 在mybatis的动态SQL当中，不能使用&&，只能使用and。
    	6. 为什么要在where后面写 1=1 ，这是一个恒为true的表达式，如果三个if都不满足，那么sql语句就变成 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>
</select>

测试：

@Test
public void testSelectByMultiCondition() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    // 假设三个条件都不是空
    //List<Car> cars = mapper.selectByMultiCondition("比亚迪", 2.0, "电车");
    // 假设三个条件都是空
    List<Car> cars = mapper.selectByMultiCondition("", null, "");
    // 假设后两个条件不为空，第一个条件为空
    //List<Car> cars = mapper.selectByMultiCondition("", 2.0, "新能源");

    // 假设第一个条件不是空，后两个条件是空
    //  List<Car> cars = mapper.selectByMultiCondition("比亚迪", null, "");

    cars.forEach(car -> {
        System.out.println(car);
    });
    sqlSession.close();
}

2.where标签

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

• 所有条件都为空时，where标签保证不会生成where子句。
• 自动去除某些条件前面多余的and或or。
• 可以结合if标签使用：没有条件就不会出现where，有条件则会根据条件数量添加if

接口方法：

List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

CurMapper.xml:

<select id="selectByMultiConditionWithWhere" resultType="Car">
    select * from t_car
    <!--
    1. 自动去掉前面多余的and和or
    2. 条件为空时，不会有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>

测试：

@Test
public void selectByMultiConditionWithWhere() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);

    // 假设三个条件都不是空
    //sql : select * from t_car WHERE brand like '%比亚迪%' and guide_price > '2.0' and car_type = '电车'
    List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", 2.0, "电车");

    // 假设三个条件都是空
    //sql:select * from t_car
    //  List<Car> cars = mapper.selectByMultiConditionWithWhere("", null, "");

    // 假设后两个条件不为空，第一个条件为空
    //List<Car> cars = mapper.selectByMultiConditionWithWhere("", 2.0, "新能源");

    // 假设第一个条件不是空，后两个条件是空
    //  List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", null, "");

    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

3.trim标签

trim标签的属性：

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

接口方法：

List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

CurMapper.xml:

<select id="selectByMultiConditionWithTrim" resultType="Car">
    select * from t_car
    <!--
        prefix：加前缀
        suffix：加后缀
        prefixOverrides：删除前缀
        suffixOverrides：删除后缀
    -->
    <!--prefix="where" 是在trim标签所有内容的前面添加 where-->
    <!--suffixOverrides="and|or" 把trim标签中内容的后缀and或or去掉
    假设第一个if满足，第二、三个if不满足，按理说是select * from t_car where brand like "%"?"%" or
    实际上是select * from t_car where brand like "%"?"%" , 这就是trim把后缀的or去掉了
    -->
   
    <trim prefix="where" suffixOverrides="and|or">
        <if test="brand != null and brand != ''">
            brand like "%"#{brand}"%" or
        </if>
        <if test="guidePrice != null and guidePrice != ''">
            guide_price > #{guidePrice} and
        </if>
        <if test="carType != null and carType != ''">
            car_type = #{carType}
        </if>
    </trim>

</select>

测试：

@Test
public void selectByMultiCondition() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);

    // 假设三个条件都不是空
    // List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪",2.0,"电车");

    // 假设三个条件都是空
    //List<Car> cars = mapper.selectByMultiConditionWithTrim("", null, "");

    // 假设后两个条件不为空，第一个条件为空
   // List<Car> cars = mapper.selectByMultiConditionWithTrim("", 2.0, " ");

    // 假设第一个条件不是空，后两个条件是空
      List<Car> cars = mapper.selectByMultiConditionWithTrim("比亚迪", null, "");

    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

3.set标签

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

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

接口方法：

int updateBySet(Car car);

CurMapper.xml:

<update id="updateBySet">
    update t_car
    <!--update语句，每个字段直接要写， 那么要是最后多了，set就会帮我们去掉-->
    <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>

测试：

@Test
public void updateBySet() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    //这儿我们只修改最后一个字段
    //只有一个 if满足
    
    Car car = new Car(39L, null, null, null, null, "燃油车");
    int i = mapper.updateBySet(car);
    if (i > 0) {
        System.out.println("修改成功");
    }
    sqlSession.commit();
    sqlSession.close();
}

修改前：

修改后：

3.foreach标签

3.1批量删除

sql:

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

• delete from t_car where id = 1 or id = 2 or id = 3;

接口方法：

//使用in实现批量删除
int deleteBatchByForeach(@Param("ids") Long[] ids);
//使用or实现批量删除
int deleteBatchByForeach2(@Param("ids") Long[] ids);

CurMapper.xml:

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

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

测试：

@Test
public void deleteBatchByForeach() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Long[] ids = {36L,37L,38L};
    int count = mapper.deleteBatchByForeach(ids);
    System.out.println("删除了几条记录：" + count);
    sqlSession.commit();
    sqlSession.close();
}
@Test
public void testDeleteBatchByForeach2(){
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Long[] ids = {36L,37L,38L};
    int count = mapper.deleteByIds2(ids);
    System.out.println("删除了几条记录：" + count);
    sqlSession.commit();
    sqlSession.close();
}

3.2批量添加

接口方法：

int insertBatchByForeach(@Param("cars") List<Car> cars);

CurMapper.xml:

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

测试：

@Test
public void insertBatch() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = new ArrayList<>();
    Car car1 = new Car(null, "100086", "比亚迪重庆", 2.0, "2022-2-2", "电车");
    Car car2 = new Car(null, "100086", "比亚迪天津", 2.0, "2022-2-2", "电车");
    Car car3 = new Car(null, "100086", "比亚迪北京", 2.0, "2022-2-2", "电车");
    cars.add(car1);
    cars.add(car2);
    cars.add(car3);
    int i = mapper.insertBatch(cars);
    if (i > 0) {
        System.out.println("删除成功");
    }
    sqlSession.commit();
    sqlSession.close();
}