mybatis--动态SQL

 

一、动态SQL基础介绍

基于OGNL表达式

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

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

• if
• trim
• where
• set
• choose（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=?

如何处理

• if
• 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>

 

五、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>