动态SQL常用场景
- 批量删除
delete from t_car where id in(1,2,3,4,5,6,......这里的值是动态的,根据用户选择的 id不同,值是不同的);
- 多条件查询
哪些字段会作为查询条件是不确定的,根据用户而定 select * from 1 t_car where brand like '丰田%' and guide_price > 30 and .....;
- if 标签
List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
存在一些连接词的问题例如 and 和 or,当brand 为空时可能出现 select * from t_car where and…这种语法报错情况,所以需要结合其他 标签使用<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>
- where 标签
解决了if 标签存在的一些问题,where标签的作用:让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>
所有条件都为空时,where标签保证不会生成where子句。
自动去除某些条件前面多余的and或or,注意后面的 and 和 or 是不会被去除的 - trim 标签
trim标签的属性:- prefix:在 SQL 语句的开头添加指定的前缀。
- suffix:在 SQL 语句的结尾添加指定的后缀。
- prefixOverrides:去掉 SQL 语句开头的指定前缀。
- suffixOverrides:去掉 SQL 语句结尾的指定后缀。
所有条件为空时,不会添加前缀,比where标签更加灵活,可以去除结尾的连接词<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>
- set标签
主要使用在update语句当中,用来生成set关键字,同时去掉最后多余的“,”int updateWithSet(Car car);
<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 = #{gui dePrice},</if> <if test="produceTime != null and produceTime != ''">produce_time = # {produceTime},</if> <if test="carType != null and carType != ''">car_type = #{carType},</i f> </set> where id = #{id} </update>
- choose when otherwise
这三个标签是一起使用的,类似于 if else 嵌套选择<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>
- foreach 标签
循环数组或集合,动态生成sql,比如这样的SQL:- 用 in 实现批量删除
int deleteBatchByForeach(@Param("ids") Long[] ids);
<!-- 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>
- 用 or 实现批量删除
<delete id="deleteBatchByForeach2"> delete from t_car where <foreach collection="ids" item="id" separator="or"> id = #{id} </foreach> </delete>
- 批量添加
int insertBatchByForeach(@Param("cars") List<Car> cars);
<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>
- 用 in 实现批量删除
- sql标签与include标签
sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用:代码复用。易维护。<sql id="carCols">id,car_num carNum,brand,guide_price guidePrice,produce_t ime 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>