【SSM】MyBatis(十.动态sql)

1.if

    <select id="selectByMultiCondition" resultType="Car">select * from t_car where 1 = 1<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>

2.where

where标签的作用：让where子句更加动态智能。
● 所有条件都为空时，where标签保证不会生成where子句。
● 自动去除某些条件前面多余的and或or。

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

3.trim

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

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

4.set

主要使用在update语句当中，用来生成set关键字，同时去掉最后多余的“,”
比如我们只更新提交的不为空的字段，如果提交的数据是空或者""，那么这个字段我们将不更新。

    <update id="updateBySet" parameterType="long">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>whereid = #{id}</update>

5. choose when otherwise

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

6.foreach

6.1 批量删除

<!--
collection：集合或数组
item：集合或数组中的元素
separator：分隔符
open：foreach标签中所有内容的开始
close：foreach标签中所有内容的结束
--><delete id="deleteByIds">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>

6.2 批量增加

    <insert id="insertBatch">insert into t_carvalues<foreach collection="cars" item="car" separator=",">(null, #{car.carNum}, #{car.brand}, #{car.guidePrice}, #{car.produceTime}, #{car.carType})</foreach></insert>

7.sql

sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用：代码复用。易维护。