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