【MyBatis】动态SQL第二篇

5.choose when otherwise

1).应用场景

• 主要用于多条件查询

2).语法格式

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

等同于:

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

}

3).测试代码

CarMapper

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

CarMapper.xml

  <select id="selectByChoose" resultType="com.powernode.mybatis.pojo.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>
                    car_type = #{carType}
                </otherwise>
            </choose>
        </where>
    </select>

Test

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

4).总结

测试结果：

结论:只有一个分支会被选择,相当于Java语言中的if,else语句

6.foreach标签

1).应用场景

1.批量删除

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

2.批量添加

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','新能源')

2).作用

• 函数的参数类型是循环数组或集合，动态生成sql。

3).语法格式

  <foreach 
  collection="集合或数组" 
  item="集合或数组中的元素" 
  separator="分隔符" 
  open="foreach标签中所有内容的开始" 
  close="foreach标签中所有内容的结束"> 
  ...
   </foreach>

4).测试代码

• 以in删除为例

CarMapper

int deleteByIds(@Param("ids") Long[] ids);

CarMapper.xml

 <delete id="deleteByIds">
 
        delete from t_car where id in
            <foreach collection="ids" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>

    </delete>

Test

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

5).总结

测试结果：测试通过