动态SQL

<if> 用于条件判断，决定是否包含某个SQL片段。

if+set

<set> 用于动态生成 SET 子句，自动处理多余的逗号。

<!-- 更新用户信息 -->
<update id="edit" >UPDATE smbms_user<set><if test="userCode != null">userCode = #{userCode},</if><if test="userName != null">userName = #{userName},</if><if test="gender != null">gender = #{gender},</if><if test="birthday != null">birthday = #{birthday},</if><if test="address != null">address = #{address}</if></set>WHERE id = #{id}
</update>

if+where

<where> 用于动态生成 WHERE 子句，自动处理多余的AND或OR关键字。

<!-- 多条件分页查询 -->
<select id="findAll" resultType="User">select id,userCode,userName,gender,birthday,address,creationDatefrom smbms_user<where><if test="userName != null and userName != ''">and userName like concat('%', #{userName}, '%')</if><if test="address != null and address != ''">and address like concat('%', #{address}, '%')</if><if test="beginDate != null and beginDate != ''">and creationDate &gt;= #{beginDate}</if><if test="endDate != null and endDate != ''">and creationDate &lt;= #{endDate}</if></where>order by creationDate desclimit #{offset}, #{pageSize}
</select>

if+trim

<trim> 用于控制 SQL 片段的前缀和后缀，以及移除多余的字符。

• prefix：前缀，添加到生成的SQL片段前面的字符串。
• suffix：后缀，添加到生成的SQL片段后面的字符串。
• prefixOverrides：对 trim 包含内部的首部进行指定内容的忽略
• suffixOverrides：对 trim 包含内部的尾部进行指定内容的忽略

<trim prefix="where" prefixOverrides="and|or"></trim>

动态生成WHERE子句，确保生成的SQL语句中WHERE关键字后面没有多余的AND或OR关键字。

<trim prefix="set" suffixOverrides="," suffix="where id=#{id}"><if test="uname != null">uname=#{uname},</if><if test="upwd != null">upwd=#{upwd},</if>
</trim>

动态生成SET子句，并确保生成的SQL语句中SET关键字后面没有多余的逗号，并且在最后加上WHERE子句。

<!-- 添加用户 -->
<insert id="save">INSERT INTO smbms_user (userCode,userName,<trim suffixOverrides=","><if test="gender != null">gender,</if><if test="birthday != null">birthday,</if><if test="address != null">address,</if></trim>)VALUES (#{userCode},#{userName},<trim suffixOverrides=","><if test="gender != null">#{gender},</if><if test="birthday != null">#{birthday},</if><if test="address != null">#{address}</if></trim>)
</insert>

或者

INSERT INTO smbms_user
<trim prefix="(" suffix=")" suffixOverrides=","><if test="userCode != null">userCode,</if><if test="userName != null">userName,</if><if test="gender != null">gender,</if><if test="birthday != null">birthday,</if><if test="address != null">address,</if>
</trim>
VALUES
<trim prefix="(" suffix=")" suffixOverrides=","><if test="userCode != null">#{userCode},</if><if test="userName != null">#{userName},</if><if test="gender != null">#{gender},</if><if test="birthday != null">#{birthday},</if><if test="address != null">#{address}</if>
</trim>

第一段代码适用于某些字段（如 userCode，userName）总是必须提供的情况，固定字段和动态字段分开处理。

第一段代码适用于所有字段都可以为空的情况，所有字段都动态处理。

foreach

循环遍历集合参数

• collection：接收的集合参数
• open/close：以何打开/关闭
• separator：间隔符号
• item：单个变量名

/*** 批量删除* @param ids id列表* @return 受影响行数*/
int deleteByList(List<Integer> ids);

<!-- 删除多个用户 -->
<delete id="deleteByList" >DELETE FROM smbms_user WHERE id in<foreach collection="list"  open="("  separator=","  close=")"  item="id">#{id}</foreach>
</delete>

collection 除了传递 list 还可以传递 array、map

/*** 多查询 根据用户ID查询地址* @param ids id列表* @return 地址列表*/
List<Address> findAddressArray(Integer[] uids);List<Address> findAddressMap(Map<String,Object> map);

<select id="findAddressArray" resultType="Address">SELECT id,contact,addressDesc,postCode FROM smbms_address WHERE userId in<foreach collection="array"  open="("  separator=","  close=")"  item="uid">#{uid}</foreach>
</select>
<select id="findAddressMap" resultType="Address">SELECT id,contact,addressDesc,postCode FROM smbms_address WHERE userId in<foreach collection="uids"  open="("  separator=","  close=")"  item="uid">#{uid}</foreach>and addressDesc like concat('%', #{addressDesc}, '%')
</select>

public void findAddressArray(){addressDao.findAddressArray(new Integer[]{1,2});
}
public void findAddressMap(){Map<String,Object> map = new Map<String,Object>();map.put("addrDesc","西城区");map.put("uids",Arrays.asList(1,2,3));addressDao.findAddressArray(map);
}

choose

<select id="findActiveBlogLike" resultType="Blog">SELECT * FROM BLOG WHERE state = ‘ACTIVE’<choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND author_name like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose>
</select>

sql_227">sql

使用<sql>标签定义一个可重用的SQL片段。

<sql id="query_user_where"><where><if test="userName != null and userName != ''">and userName like concat('%', #{userName}, '%')</if><if test="address != null and address != ''">and address like concat('%', #{address}, '%')</if><if test="beginDate != null and beginDate != ''">and creationDate &gt;= #{beginDate}</if><if test="endDate != null and endDate != ''">and creationDate &lt;= #{endDate}</if></where>
</sql>

在查询中使用<include>标签引入定义的SQL片段。

<!-- 分页查询 -->
<select id="findAll" resultType="User">select id,userCode,userName,gender,birthday,address,creationDatefrom smbms_user<include refid="query_user_where"/>order by creationDate desclimit #{offset}, #{pageSize}
</select><!-- 查询列表总数 -->
<select id="findListCount" resultType="int">select count(1) as countfrom smbms_user<include refid="query_user_where"/>
</select>