一.什么是动态Sql
动态SQL是一种在运行时构建和执行SQL语句的技术。通常情况下,静态SQL是预先定义好的SQL语句,而动态SQL允许根据程序运行时的条件和需求来动态地生成SQL语句。
动态SQL的主要优点是灵活性和适应性。它可以根据不同的情境和需求生成不同的SQL语句,从而提供更高的灵活性和可重用性。例如,在一个搜索功能中,用户可能根据不同的条件来进行搜索,使用动态SQL可以根据用户输入的条件来构建相应的查询语句。
二.动态Sql的作用是什么
-
搜索和过滤:动态SQL可以根据用户输入的条件构建查询语句,实现灵活的搜索和过滤功能。例如,在一个电子商务网站上,用户可以根据商品名称、价格范围、分类等条件进行搜索,动态SQL可以根据用户选择的条件生成相应的查询语句。
-
分页和排序:动态SQL可以根据用户请求的分页参数和排序规则来生成查询语句,实现数据的分页和排序功能。通过动态SQL,可以根据不同的分页参数和排序规则生成不同的查询语句,从而满足用户对数据展示的需求。
-
动态表名和列名:有时候需要在SQL语句中使用动态的表名或列名,例如在某些情况下需要根据用户选择的条件查询不同的表,或者根据程序运行时的逻辑动态选择列。动态SQL可以根据运行时的条件来生成包含动态表名和列名的SQL语句。
-
动态拼接SQL片段:有时候,需要根据一系列运行时的条件,动态地拼接SQL片段来实现特定的查询需求。例如,在构建复杂的查询条件时,可以使用动态SQL来根据不同的条件拼接SQL片段,从而生成最终的查询语句。
三.动态Sql的具体标签
1.if标签
<if></if>
类似于java中的if判断
属性:
test:表示判断条件
2.where标签
<where></where>
使用时自动在头部添加where,如果没有条件就不会添加,使用于需要判断的aql语句
属性:
没有属性
3.choose标签
<choose><when></when><otherwise></otherwise></choose>
类似于java中的switch语句,when代表的就是case otherwise表示defult
属性:
choose: 没有属性,是最外围标签
when: test:表示判断条件
otherwise:没有属性
4.set标签
<set><set>
使用于修改语句,执行时会去掉前后多余的逗号(,)
属性:
没有属性
5.trim标签
<trim></trim>
功能非常强大,使用非常平凡,通常需要配合其他标签使用,用于添加前/后缀和去除前/后缀
属性:
suffixOverrides: 去除后缀,智能去除内容,使用 | 进行分割 比如:
suffixOverrides="and|or" 表示智能去除and和or
suffix: 添加后缀 prefix: 添加前缀 prefixOverrides:去除前缀,智能去除内容,使用 | 进行分割 比如: prefixOverrides="and|or" 表示智能去除and和or
6.foreach标签
<foreach></foreach>
表示循环,可以用于遍历集合,批量新增和删除
属性:
collection:表示你需要遍历的集合名称 item: 表示给单个的数据起别名 separator: 每次循环需要添加的后缀 open: 进入foreach添加的前缀 close: foreach结束退出添加的后缀 index:记录遍历的个数
7.分页查询
查询语句 limit 起始位置,查询的条数
使用关键字limit进行分页查询
使用注解的方式给查询传入参数
@Param("pageIndex")Ingter pageIndex,@Param("pageSize")Ingter pageSize
四.动态操作数据
1.批量删除
<delete id="deleteManySupplier" parameterType="java.util.List"> delete from `t_supplier` where id in <foreach collection="ids" open="(" close=")" separator="," item="id"> #{id} </foreach> </delete>
2.批量新增
<insert id="insertManySupplier" parameterType="java.util.List"> insert into `t_supplier`(<include refid="insertSupplier"></include>) values <foreach collection="sysUsers" item="user" separator=","> (#{user.supCode}, #{user.supName})<!-- #{user.supDesc}, #{user.supContact}, #{user.supPhone}, #{user.supAddress}, #{user.supFax}, #{user.createdUserId}, #{user.createdTime}, #{user.updatedUserId}, #{user.updatedTime}--> </foreach> </insert>
3.动态修改
<update id="updateManySupplier" parameterType="Supplier"> update `t_supplier` <set> <if test="sysUser.supCode!=null"> supCode=#{sysUser.supCode}, </if> <if test="sysUser.supName!=null"> supName=#{sysUser.supName}, </if> <if test="sysUser.supDesc!=null"> supDesc=#{sysUser.supDesc}, </if> <if test="sysUser.supContact!=null"> supContact=#{sysUser.supContact}, </if> </set> <where> <if test="sysUser.id!=0"> id=#{sysUser.id} </if> </where> </update>
4.动态查询
<select id="selectManySupplier" resultType="Supplier"> select * from `t_supplier` <where> <trim suffixOverrides="and|or"> <if test="sysUser.supCode!=null"> supCode=#{sysUser.supCode} and </if> <if test="sysUser.supName!=null"> supName=#{sysUser.supName} and </if> <if test="sysUser.supDesc!=null"> supDesc=#{sysUser.supDesc} and </if> </trim> </where> </select>
五.标签的搭配使用
-
<if>
标签:用于根据条件动态生成SQL语句的一部分。可以通过使用条件判断、比较运算符和逻辑运算符来控制SQL语句的生成。 -
<choose>
、<when>
和<otherwise>
标签:用于实现类似于 switch-case 语句的选择逻辑。可以根据不同的条件选择性地生成不同的SQL语句片段。 -
<trim>
标签:用于修剪生成的SQL语句片段。可以去除多余的空白字符和SQL关键字,并自动处理连接条件的逻辑。 -
<set>
标签:用于在更新语句中动态生成 SET 子句。可以根据传入的字段值动态生成更新语句的一部分。 -
<foreach>
标签:用于循环遍历集合或数组,并将其中的元素作为参数插入到SQL语句中。常用于批量插入或更新操作。
六.sql片段
SQL片段是指一个完整的SQL语句中的一部分,可以看作是SQL语句的子集。SQL片段通常用于构建复杂的查询条件、动态的排序规则或者其他需要动态生成的代码块。
使用SQL片段的优点之一是可以将SQL逻辑进行模块化和重用,提高代码的可维护性和可读性。它可以独立地定义和调用,在需要时可以通过参数传递给SQL查询语句。这样,在多个地方使用相同的SQL逻辑时,只需调用对应的SQL片段即可,避免了重复编写和维护相同的SQL代码。
在不同的数据库和SQL框架中,有不同的实现方式来创建和使用SQL片段。例如,在MySQL中可以使用存储过程或函数来定义SQL片段;在Oracle中可以使用PL/SQL中的游标或子程序来实现;而在许多ORM(对象关系映射)框架中,也提供了特定的工具和方法来处理SQL片段。
使用sql片段来存放查询新增的参数名称来简化代码,在使用include进行应用
<sql id="insertSupplier">
supCode,
supName,
supDesc,
supContact,
supPhone,
supAddress,
supFax,
createdUserId,
createdTime,
updatedUserId,
updatedTime
</sql>
<include refid="insertSupplier"></include>