1.简介
Mybatis 提供动态语句的功能来增强多条件变动的查询语句。
2.代码
if和where搭配使用:
<select id="query" resultType="a">select * from t_a<where><!-- where内没有条件满足,不转成where,有条件满足转成where。自动去掉多余的and和or --><if test="name != null">a_name = #{name}</if><if test="price != null and price > 100"><!-- >是 > <是 < -->and a_prirce = #{price}</if></where>
</select>
set标签用于去掉多余逗号,和自动添加set关键字:
<!-- set标签用于去掉多余逗号,和自动添加set关键字-->
<update id="update">update t_a<set><if test="aName != null">a_name = #{aName},</if><if test="aPrice != null">a_price = #{aPrice}</if>where a_id = #{aId}</set>
</update>
trim可以自定义添加关键字,去掉指定字符:
<select id="queryTrim" resultType="a">select * from t_a<!-- 自动添加where,去掉多余前缀的and和or --><trim prefix="where" prefixOverrides="and|or" sufixOverrides="and|or"><if test="name != null">a_name = #{name}</if><if test="price != null and price > 100"><!-- >是 > <是 < -->and a_prirce = #{price}</if></trim>
</select><select id="queryTrim" resultType="a">select * from t_a<!-- 自动添加where,去掉多余后缀的and和or --><trim prefix="where" sufixOverrides="and|or"><if test="name != null">a_name = #{name} and</if><if test="price != null and price > 100"><!-- >是 > <是 < -->a_prirce = #{price}</if></trim>
</select>
choose搭配when使用实现类似switch的功能:
<select id="queryChoose" resultType="a">select * from t_empwhere<!-- 如果name !=null 则用name查询,如果name == null 则用price查询,如果都不满足,则走otherwise分支 --><choose><when test="name != null">a_name = #{name} and</when><when test="salary != null">a_price = #{salary}</when><otherwise>1=1</otherwise></choose>
</select>
foreach实现批量查询:
<select id="queryBatch" resultType="a">select * from t_awhere a_id in<!-- 实现(id1,id2,id3...idn)--> <foreach collection="ids" open="(" separator="," close=")" item="id">#{id}</foreach>
</select>
sql标签组成片段,被其他地方引用:
<sql id="mySql">select * from t_a
</sql>
<select id="query" resultType="a"><include refid="mySql"/>
</select>