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
一、if标签
需求:多条件查询。
可能的条件包括:品牌(brand)、指导价格(guide_price)、汽车类型(car_type)
如果第一个条件为空,剩下两个条件不为空,会是怎样呢?
List<Car> cars = mapper.selectByMultiCondition("", 20.0, "燃油车");
执行结果:
报错了,SQL语法有问题,where后面出现了and。这该怎么解决呢?
- 可以where后面添加一个恒成立的条件。
执行结果:
如果三个条件都是空,有影响吗?
List<Car> cars = mapper.selectByMultiCondition("", null, "");
执行结果
三个条件都不为空呢?
二、where标签
where标签的作用:让where子句更加动态智能。
- 所有条件都为空时,where标签保证不会生成where子句。
- 自动去除某些条件前面多余的and或or。
继续使用if标签中的需求。
第一步、CarMapper接口
/**
* 根据多条件查询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
<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>
第三步、运行测试类方法
@Test
public void testSelectByMultiConditionWithWhere(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", 50.2, "电车");System.out.println(cars);
}
第四步、查看执行结果
如果所有条件都是空呢?
它可以自动去掉前面多余的and,那可以自动去掉前面多余的or吗?
xml文件:
<select id="selectByMultiConditionWithWhere" resultType="car">select * from t_car<where><if test="brand != null and brand != ''">or 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>
测试方法:
@Testpublic void testSelectByMultiConditionWithWhere(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", 50.2, "电车");System.out.println(cars);}
运行结果:
它可以自动去掉前面多余的and或or,那可以自动去掉后面多余的and吗?
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>
测试方法:
// 让最后一个条件为空@Testpublic void testSelectByMultiConditionWithWhere(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", 50.2, "");System.out.println(cars);}
运行结果:
很显然,后面多余的and是不会被去除的。
三、trim标签
trim标签的属性:
- prefix:在trim标签中的语句前添加内容
- suffix:在trim标签中的语句后添加内容
- prefixOverrides:前缀覆盖掉(去掉)
- suffixOverrides:后缀覆盖掉(去掉)
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>
运行测试方法、
@Test
public void testSelectByMultiConditionWithTrim(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);List<Car> cars = mapper.selectByMultiConditionWithTrim("丰田", 20.0, "");System.out.println(cars);
}
查看执行结果、
如果所有条件为空,where会被加上吗?
测试方法、
@Testpublic void testSelectByMultiConditionWithTrim(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);List<Car> cars = mapper.selectByMultiConditionWithTrim("", null, "");System.out.println(cars);}
执行结果、(不会)
四、set标签
主要使用在update语句当中,用来生成set关键字,同时去掉最后多余的“,”
比如我们只更新提交的不为空的字段,如果提交的数据是空或者"",那么这个字段我们将不更新。
第一步、CarMapper接口
/**
* 更新信息,使用set标签
* @param car
* @return
*/
int updateWithSet(Car car);
第二步、CarMapper.xml文件
<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>
第三步、运行测试方法
@Test
public void testUpdateWithSet(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);Car car = new Car(38L,"1001","丰田霸道2",10.0,"",null);int count = mapper.updateWithSet(car);System.out.println(count);SqlSessionUtil.openSession().commit();
}
第四步、查看执行结果
五、choose when otherwise
这三个标签是在一起使用的:
<choose><when></when><when></when><when></when><otherwise></otherwise>
</choose>
等同于:
if(){}else if(){}else if(){}else if(){}else{}
只有一个分支会被选择!!!!
需求:先根据品牌查询,如果没有提供品牌,再根据指导价格查询,如果没有提供指导价格,就根据生产日期查询。
第一步、CarMapper接口
/**
* 使用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);
第二步、CarMapper.xml文件
<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>
第三步、运行测试方法
@Test
public void testSelectWithChoose(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);List<Car> cars = mapper.selectWithChoose("", null, "");System.out.println(cars);
}
第四步、查看执行结果
六、foreach标签
循环数组或集合,动态生成sql,比如这样的SQL:
delete from t_car where id in(1,2,3);
delete from t_car where id = 1 or id = 2 or id = 3;
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','新能源')
1、批量删除
-
用in来删除
第一步、CarMapper接口
/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach(@Param("ids") Long[] ids);
第二步、CarMapper.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>
第三步、运行测试类方法
@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();
}
第四步、查看执行结果
-
用or来删除
第一步、CarMapper接口
/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach2(@Param("ids") Long[] ids);
第二步、CarMapper.xml
<delete id="deleteBatchByForeach2">delete from t_car where<foreach collection="ids" item="id" separator="or">id = #{id}</foreach>
</delete>
第三步、运行测试类方法
@Test
public void testDeleteBatchByForeach2(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);int count = mapper.deleteBatchByForeach2(new Long[]{30L, 31L});System.out.println("删除了几条记录:" + count);SqlSessionUtil.openSession().commit();
}
第四步、查看执行结果
2、批量添加
第一步、CarMapper接口
/**
* 批量添加,使用foreach标签
* @param cars
* @return
*/
int insertBatchByForeach(@Param("cars") List<Car> cars);
第二步、CarMapper.xml
<insert id="insertBatchByForeach">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 testInsertBatchByForeach(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);Car car1 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");Car car2 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");Car car3 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");List<Car> cars = Arrays.asList(car1, car2, car3);int count = mapper.insertBatchByForeach(cars);System.out.println("插入了几条记录" + count);SqlSessionUtil.openSession().commit();
}
第四步、查看执行结果
七、sql标签与include标签
sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用:代码复用。易维护。
mybatis-007-select模块中的CarMapper.xml
<sql id="carCols">id,car_num carNum,brand,guide_price guidePrice,produce_time produceTime,car_type carType</sql><select id="selectAllRetMap" resultType="map">select <include refid="carCols"/> from t_car
</select><select id="selectAllRetListMap" resultType="map">select <include refid="carCols"/> carType from t_car
</select><select id="selectByIdRetMap" resultType="map">select <include refid="carCols"/> from t_car where id = #{id}
</select>