一、动态SQL基础介绍

基于OGNL表达式

完成多条件查询等逻辑实现

用于实现动态SQL的元素主要有

iftrimwheresetchoose（when、otherwise）foreach

 

---

二、if

问题:当传入用户角色参数为空的时候，检索结果为空？

正确结果 所有用户角色下的用户数据

原因

select * from smbms_user u,smbms_role r where u.userRole = r.id and u.userName like CONCAT (‘%’, ‘’, ‘%’) and u.userRole = null;

如何处理

<select id=“getUserList” resultMap=“User”> select * from smbms_user where <if test=“userName!=null”> userName like CONCAT (‘%’,#{userName},’%’) </if> <if test=“userRole!=null”> and userRole = #{userRole} </if> </select>

 

 

三、where

问题:当只传入参数：用户角色，而不传入参数：用户名称的时候，控制台报SQL异常错误？

正确结果 所有用户角色下的用户数据

原因

select * from smbms_user where and userRole = ?

如何处理

<select id="getUserList" resultType="User"> 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> 简化SQL语句中where条件判断智能处理and和or

 

四、set

问题:更新用户表数据时，若某个参数为null时，会导致更新错误

正确结果 若某个参数为null，则不需要更新，保持数据库原值

原因

update smbms_user set username=?

如何处理

ifset <!-- 修改用户信息 --> <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>

 

五、trim

if+trim

使用if+trim替代if+set进行更新用户表数据，效果一样 <!-- 修改用户信息 --> <update id="modify" parameterType="User"> update smbms_user <trim prefix="set" suffixOverrides="," suffix="where id = #{id}"> <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>

 

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

 

六、foreach

 

 

<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_array --> <select id="getUserByRoleId_foreach_array" resultMap="userMapByRole"> select * from smbms_user where userRole in <foreach collection="array" item="roleIds" open="(" separator="," close=")"> #{roleIds} </foreach> </select> <!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list --> <select id="getUserByRoleId_foreach_list" resultMap="userMapByRole"> select * from smbms_user where userRole in <foreach collection="list" item="roleList" open="(" separator="," close=")"> #{roleList} </foreach> </select>

 

<!-- 根据用户角色列表(单参数)，获取该角色列表下用户列表信息-foreach_map --> <select id="getUserByRoleId_foreach_map" resultMap="userMapByRole"> select * from smbms_user where userRole in <foreach collection="rKey" item="roleMap" open="(" separator="," close=")"> #{roleMap} </foreach> </select>

 

七、choose(when、otherwise)

<!-- 查询用户列表(choose) --> <select id="getUserList_choose" resultType="User"> 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>