【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).总结
测试结果:测试通过