动态拼接updatesql 动态拼接sql框架

转载

charlesc 2024-04-16 15:24:03

文章标签 动态拼接updatesql SQL 字符串 List 文章分类 架构后端开发

引言

JDBC或者其他框架，根据需要拼接SQL实现数据库检索功能；MyBatis提供对SQL语句动态的组装能力。

概述

Mybatis可以在配置文件中编写SQL语句，实现对数据库的操作。动态SQL是在原先SQL基础上加上判断条件，例如if等语句。

内容

一基本元素：mybatis常用的动态SQL元素

1 元素表

动态拼接updatesql 动态拼接sql框架_字符串

2 使用

（1）if元素，示例：根据角色名称模糊匹配查询角色。

<select id="findRoles" parameterType="string" resultMap="roleResultMap">
	select role_no, role_name, note from t_role where 1 = 1
	<if test="roleName !=null and roleName !='' ">
		and role_name like concat('%', #{roleName}, '%')
	</if>
</select>

（2）choose、when、otherwise元素：有时候判断条件结果不只是true或false，类似于java语言中的switch——case语句。

示例：当role编号为空，使用roleNo为条件查询；当role编号为空，而角色名称roleName不为空，则roleName作为条件进行模糊查询；若俩者都为空，要求备注不为空。三种查询情况，来看mybatis动态sql语句如何实现？

<select id="findRoles" parameterType="string" resultMap="roleResultMap">
	select role_no, role_name, note from t_role where 1 = 1
	<choose>
		<when test="testNO != null and roleNo !='' ">
			and role_no = #{roleNo}
		</when>
		<when test="roleName !=null and roleName !='' ">
			and role_name like concat('%', {roleName}, '%')
		</when>
		<otherwise>
			and note is not null
		</otherwise>
	</choose>
</select>

（3）trim、where、set元素

1）where：如果将if元素示例中的1=1去掉，SQL的语法就会异常，这时我们就可以用where元素起到同等作用。

<select id="findRoles" parameterType="string" resultMap="roleResultMap">
	select role_no, role_name, note from t_role 
		<where>
		<if test="roleName !=null and roleName !='' ">
			and role_name like concat('%', {roleName}, '%')
		</if>
		</where>
	</choose>
</select>

2）trim：

<select id="findRoles" parameterType="string" resultMap="roleResultMap">
	select role_no, role_name, note from t_role 
		<trim prefix="where" prefixOverrides="and">
		    <if test="roleName !=null and roleName !='' ">
			and role_name like concat('%', {roleName}, '%')
		    </if>
		</trim>
</select>

trim元素用来去掉一些特殊的字符串，prefix代表是语句的前缀，而prefixOverrides代表你需要去掉的字符串，本实例中trim与where元素起到同样的效果。

3）set：使用场景：更新一条记录的一个字段，性能最佳的办法是把主键和更新字段的值传给sql进行更新。mybatis通过set来设置这样的更新操作，具体示例如下：

<update id="updateRole" parameterType="role">
	update t_role
	<set>
		<if test="roleName != null and roleName !=''">
			role_name = #{roleName},
		</if>
		<if test="note != null and note !='' ">
			note = #{note}
		</if>
	</set>
	where role_no = #{roleNo}
</update>

（4）foreach元素：循环语句，用来遍历集合。能够很好的支持数组和List、Set接口的集合，对此提供遍历的功能。

<select id="findUserBySex" resultType="user">
	select * from t_user where sex in
	<foreach item="sex" index="index" collection="sexList" open="("separator="," close=")">
		#{sex}
	</foreach>
</select>

collection配置的sexList是传递进来的参数名称，可以是一个数组或List、Set等集合；item配置的是循环中当前的元素；index配置的是当前元素在集合的位置下标；open和close配置的是以什么符号将这些集合元素包装起来；separator是各个元素的间隔符。值得注意的是，sql中大量语句的in是非常消耗性能的，所以我们要预估一下collection对象的长度。

二 test属性：用于条件判断语句中，相当于判断真假，上面的示例基本是用它判断空和非空，我们还可以用它来判断字符串、数字和枚举等。

<select id="getRoleTest" parameterType="string" resultMap="roleResultMap">
	select role_no, role_name, note from t_role 
	<if test="type = 'Y'">
		where 1=1
	</if>
</select>

三 bind元素：通过OGNL表达式自定义一个上下文变量。例如模糊查询，mysql使用一个concat用“%”和参数相连接，然而oracle则是用连接符号“||”，这样就很难兼容，需要俩种形式。这样情况下，mybatis提供了bind元素，不必使用数据库的语言，只要使用mybatis的语言即可与所需参数相连，实现模糊查询。

1 使用bind绑定一个参数

<select id="findRole" resultType="com.liming.domain.mybatis.bean.RoleBean">
	<bind name="pattern" value="'%' + _parameter + '%'"/>
	select id, role_name as roleName, create_date as createDate, end_date as endDate From t_role
	where role_name like #{pattern}
</select>

2 多参数绑定

（1）定义接口方法

/*
  * 查询角色
  * @param roleName 角色名称
  * @param note 备注
  * @return 符合条件的角色
 */
public List<RoleBean> findRole(@Param("roleName")String roleName, @Param("note")String note);

（2）定义映射文件，定义俩个新的变量执行模糊查询

<select id="findRole" resultType="com.liming.domain.mybatis.bean.RoleBean">
	<bind name="pattern_roleName" value="'%' + roleName + '%'"/>
	<bind name="pattern_note" value ="'%' + note + '%'"/>
	select id, role_name as roleName, create_date as createDate, end_date as endDate from t_role
	where role_name like #{pattern_roleName}
	and note like #{pattern_note}
</select>