1、动态条件查询。<where>标签会智能地将第一个不为空的值对应语句前面的and删去。
<!-- 动态条件查询-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status != null">and status = #{status}</if>
<if test="companyName != null and companyName != ''">and company_name like #{companyName}</if>
<if test="brandName != null and brandName != ''">and brand_name like #{brandName}</if>
</where>
</select>
2、插入后返回主键。通过useGeneratedKeys和keyProperty的设置可以返回主键id,在测试代码中通过调用get方法得到。如brand.getId();
<!--插入后返回主键-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand (brand_name, company_name, ordered, description, status)
values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>
3、动态更新sql语句。在此案例中如果最后的status为空,<set>标签会智能地将不为空的最后一个值对应的语句后面的逗号删去。
<!--动态更新sql语句-->
<update id="update">
update tb_brand
<set>
<if test="brandName != null and brandName != ''">
brand_name = #{brandName},
</if>
<if test="companyName != null and companyName != ''">
company_Name = #{companyName},
</if>
<if test="ordered != null">
ordered = #{ordered},
</if>
<if test="description != null and description != ''">
description = #{description},
</if>
<if test="status != null">
status = #{status}
</if>
where id = #{id};
</set>
</update>
4、批量删除动态语句。separator用于设置内部分隔符,open设置集合前字符,close设置集合后字符。
<!--批量删除动态语句-->
<delete id="deleteByIds">
delete
from tb_brand
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
;
</delete>