if標(biāo)簽與where標(biāo)簽

if標(biāo)簽

test如果為true就會(huì)拼接查詢條件，否則不會(huì)

• 當(dāng)沒有使用@Param，test出現(xiàn)arg0/param1
• 當(dāng)使用@Param，test為@Param指定的值
• 當(dāng)使用Pojo，test為對(duì)象的屬性名

select * from car where
<if test="name!=null || name!='' ">name like concat('%',${name},'%')
</if>
<if test="price!=null || price!='' ">and price=#{price}
</if>
...

注意日期不能判斷為空字符串

如何所有條件都不滿足上述代碼會(huì)報(bào)錯(cuò)

解決

如果用 where 1=1 后面判斷必須加and

select * from car where 1=1
<if test="name!=null || name!='' ">and name like concat('%',${name},'%')
</if>
<if test="price!=null || price!='' ">and price=#{price}
</if>
...

where標(biāo)簽

• 所有條件都為空時(shí)，where子句不會(huì)生成
• 自動(dòng)去掉前面多余的and，or

select * from car 
<where><if test="name!=null || name!='' ">name like concat('%',${name},'%')</if><if test="price!=null || price!='' ">and price=#{price}</if>
</where>
...

---

trim標(biāo)簽

• prefix在標(biāo)簽前面動(dòng)態(tài)的添加屬性值
• suffix在標(biāo)簽后面動(dòng)態(tài)的添加屬性值
• suffixOverrides去除標(biāo)簽內(nèi)容后面中指定的屬性值
• prefixOverrides去除標(biāo)簽內(nèi)容前面中指定的屬性值

select * from car 
<trim prefix="where" suffixOverrides="and | or" prefixOverrides="" suffix=""><if test="name!=null || name!='' ">name like concat('%',${name},'%')</if><if test="price!=null || price!='' ">and price=#{price}</if>
</trim>

---

set標(biāo)簽

• 主要用在update標(biāo)簽中，只會(huì)提交不為空的條件
• 可以動(dòng)態(tài)去除語句中多余的，

update  car 
<set><if test="name!=null || name!='' ">name=#{name} , </if><if test="price!=null || price!='' ">price=#{price} ,</if>
</set>
where id=#{id}

---

choose when otherwise標(biāo)簽

一般在多條件中只執(zhí)行某一個(gè)條件查詢

用法類似與 if else if else

selecr * from car
<where><choose><when test="name!=null || name!='' ">name=#{name} </when><when test="price!=null || price!='' ">price=#{price} </when><otherwise>id=#{id} <otherwise></choose>
<where>

因?yàn)橹粫?huì)滿足一種查詢條件所有不需要加and

---

forEach標(biāo)簽

• collection為循環(huán)列表
• item為循環(huán)元素
• separator為循環(huán)元素之間的分隔符
• open為標(biāo)簽前面加屬性值
• close為標(biāo)簽后面加屬性值

批量刪除

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

批量插入

insert into  car( name, price)
values<foreach collection="list" item="item" separator=",">(#{item.name},#{item.price})</foreach>

---

sql與include標(biāo)簽

主要用于字段的封裝和復(fù)用

<sql id="CarSql">id,car_num as  carNum,brand,guide_price as guidePrice,produce_time as produceTime,car_type as carType
</sql><select id="selectAll" resultType="com.example.webapplication.pojo.Car">select<include refid="CarSql"></include>from car
</select>