第十二章MyBatis动态SQL

if标签与where标签

if标签

test如果为true就会拼接查询条件，否则不会

• 当没有使用@Param，test出现arg0/param1
• 当使用@Param，test为@Param指定的值
• 当使用Pojo，test为对象的属性名

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

注意日期不能判断为空字符串

如何所有条件都不满足上述代码会报错

解决

如果用 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标签

• 所有条件都为空时，where子句不会生成
• 自动去掉前面多余的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标签

• prefix在标签前面动态的添加属性值
• suffix在标签后面动态的添加属性值
• suffixOverrides去除标签内容后面中指定的属性值
• prefixOverrides去除标签内容前面中指定的属性值

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

• 主要用在update标签中，只会提交不为空的条件
• 可以动态去除语句中多余的，

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

一般在多条件中只执行某一个条件查询

用法类似与 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>

因为只会满足一种查询条件所有不需要加and

---

forEach标签

• collection为循环列表
• item为循环元素
• separator为循环元素之间的分隔符
• open为标签前面加属性值
• close为标签后面加属性值

批量删除

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

主要用于字段的封装和复用

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