Mybatis--动态sql
if
代码语言：javascript
复制<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
代码语言：javascript
复制<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>
代码语言：javascript
复制<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
代码语言：javascript
复制<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
代码语言：javascript
复制<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）
代码语言：javascript
复制<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
代码语言：javascript
复制<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>
