动态sql

什么是动态sql？

动态生成的sql语句

什么时候用动态sql？

当需要查询的条件不确定时使用动态sql

动态sql有什么作用？

简化sql语句的编写

已下以Mybatis mapper.xml为例

trim:

属性：prefix suffix (prefixOverridessuffixOverrides)覆盖忽略

更灵活的去除关键字

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

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

foeach

Collection=数组array,集合list,map-map里的key值    item=传入对象名

<!-- 根据用户角色列表，获取该角色列表下用户列表信息-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>

<!-- 查询用户列表(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>