Mybatis--动态sql

if

<select id="getUserList" resultMap="userList">
select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole = r.id 
<if test="userRole != null">and u.userRole = #{userRole} </if>
​
<if test="userName != null and userName != ''">and u.userName like CONCAT ('%',#{userName},'%') </if>
​
</select>

trim

<select resultType="User" id="getUserList">
select * from smbms_user 
​
<trim prefixOverrides="and | or" prefix="where">
​
<if test="userName != null and userName != ''">and userName like CONCAT ('%',#{userName},'%') </if>
​
<if test="userRole != null">and userRole = #{userRole} </if>
​
</trim>
​
</select>

<update id="modify" parameterType="User">
update smbms_user 
​
<trim prefix="set" suffix="where id = #{id}" suffixOverrides=",">
​
<if test="userCode != null">userCode=#{userCode},</if>
​
<if test="userName != null">userName=#{userName},</if>
​
<if test="userPassword != null">userPassword=#{userPassword},</if>
​
<if test="gender != null">gender=#{gender},</if>
​
<if test="birthday != null">birthday=#{birthday},</if>
​
<if test="phone != null">phone=#{phone},</if>
​
<if test="address != null">address=#{address},</if>
​
<if test="userRole != null">userRole=#{userRole},</if>
​
<if test="modifyBy != null">modifyBy=#{modifyBy},</if>
​
<if test="modifyDate != null">modifyDate=#{modifyDate},</if>
​
</trim>
​
</update>

where

<select resultType="User" id="getUserList">
select * from smbms_user 
<where>
<if test="userName != null and userName != ''">and userName like CONCAT ('%',#{userName},'%') </if>
<if test="userRole != null">and userRole = #{userRole} </if>
</where>
​
</select>

set

<update id="modify" parameterType="User">
update smbms_user 
​
<set>
​
<if test="userCode != null">userCode=#{userCode},</if>
​
<if test="userName != null">userName=#{userName},</if>
​
<if test="userPassword != null">userPassword=#{userPassword},</if>
​
<if test="gender != null">gender=#{gender},</if>
​
<if test="birthday != null">birthday=#{birthday},</if>
​
<if test="phone != null">phone=#{phone},</if>
​
<if test="address != null">address=#{address},</if>
​
<if test="userRole != null">userRole=#{userRole},</if>
​
<if test="modifyBy != null">modifyBy=#{modifyBy},</if>
​
<if test="modifyDate != null">modifyDate=#{modifyDate}</if>
​
</set>
where id = #{id} 
</update>

choose（when、otherwise）

<select resultType="User" id="getUserList_choose">
select * from smbms_user where 1=1 
​
<choose>
​
<when test="userName != null and userName != ''">and userName like CONCAT ('%',#{userName},'%') </when>
​
<when test="userCode != null and userCode != ''">and userCode like CONCAT ('%',#{userCode},'%') </when>
​
<when test="userRole != null">and userRole=#{userRole} </when>
​
​
<otherwise>
​
<!-- and YEAR(creationDate) = YEAR(NOW()) -->
​
and YEAR(creationDate) = YEAR(#{creationDate}) 
</otherwise>
​
</choose>
​
</select>

foreach

<select id="getUserByConditionMap_foreach_map" resultMap="userMapByRole">
select * from smbms_user where gender = #{gender} and userRole in 
<foreach close=")" separator="," open="(" item="roleMap" collection="roleIds">#{roleMap} </foreach>
</select>
​
<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
select * from smbms_user where userRole in 
<foreach close=")" separator="," open="(" item="roleIds" collection="array">#{roleIds} </foreach>
</select>
​
<select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
select * from smbms_user where userRole in 
<foreach close=")" separator="," open="(" item="roleList" collection="list">#{roleList} </foreach>
</select>