动态sql

动态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>