动态sql
if
<select id="listProduct" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat('%',#{name},'%')
</if>
</select>
where
自动去除多余的and和or
<select id="listProduct" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat('%',#{name},'%')
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
set
自动去除最后一个逗号
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
trim
和前两个差不多
prefix 前缀
prefixOverrides 去掉第一个
suffixOverrides 去掉最后一个
<select id="listProduct" resultType="Product">
select * from product_
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="name!=null">
and name like concat('%',#{name},'%')
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</trim>
</select>
条件分支
如果没有符合的,根据otherwise查询。
<select id="listProduct" resultType="Product">
SELECT * FROM product_
<where>
<choose>
<when test="name != null">
and name like concat('%',#{name},'%')
</when>
<when test="price !=null and price != 0">
and price > #{price}
</when>
<otherwise>
and id >1
</otherwise>
</choose>
</where>
</select>
foreach
可用于根据ID查询多个、删除多个
SELECT * FROM product_
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
bind
备用字符串
<select id="listProduct" resultType="Product">
<bind name="likename" value="'%' + name + '%'" />
select * from product_ where name like #{likename}
</select>