动态SQL

动态SQL

动态SQL

  • 基于OGNL表达式
  • 完成多条件查询的逻辑实现
  • 用于实现动态SQL的元素主要有
    • if
    • trim
    • where
    • set
    • choose(when、otherwise)
    • foreach

if

参数判断:实现简单的条件判断

<select id="getUserList" resultMap="UserMap">
	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>

where

简化SQL语句中where条件判断

智能处理and和or

<select id="getUserList" resultType="User">
	select * from smbms_user u
    <where>
		<if test="userRole != null">
			and u.userRole = #{userRole}
		</if>
		<if test="userName != null and userName != ''">
			and u.userName like CONCAT('%',#{userName},'%')
		</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>
	</set>
	where id + #{id}
</update>

trim

属性

  • prefix
  • suffix
  • prefixOverrides
  • suffixOverrides

更灵活地去除多余的关键字

替代where和set

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

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>
	</trim>
</update>

foreach

迭代一个集合,通常用与in条件

属性

  • item
  • index
  • collection:必须指定
    • list
    • array
    • map-key
  • open
  • separator
  • close
public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);

public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);

public List<User> getUserByRoleId_foreach_map(Map<String,Object> roleMap);
<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>

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

<select id="getUserByRoleId_foreach_map" resultMap="userMapByRole">
	select * from smbms_user where userRole in
		<foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
			#{roleList}
		</foreach>
</select>

choose(when、otherwise)

相当于Java中switch语句

当when有条件满足的时候,就跳出choose

<choose>
	<when test="条件1">...</when>
	<when test="条件2">...</when>
	<when test="条件3">...</when>
	...
	<otherwise>...</otherwise>
</choose>
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值