一 . 初识动态SQL
① 什么是动态SQL ?
动态SQL是一种在运行时根据不同条件生成不同SQL语句的技术。而传统的静态SQL是预先编写好的固定SQL语句,无法根据不同条件进行灵活的调整。
② 动态SQL的优势 ?
动态SQL允许在应用程序中根据不同的需求和条件来构建SQL语句。它可以根据用户输入、运行时数据、逻辑判断等动态地生成不同的SQL查询或更新语句。
③ 动态SQL的作用 ?
(1) 动态拼接条件:根据用户提供的条件,将其添加到SQL语句中进行筛选。
(2) 动态排序:根据用户选择的排序方式,在SQL语句中动态添加ORDER BY子句。
(3) 动态字段选择:根据用户选择的字段,在SQL语句中动态添加SELECT子句。
(4) 防止SQL注入:通过参数化查询等技术,可以有效防止恶意用户通过输入特殊字符导致的SQL注入攻击
二 . 动态SQL的常用标签及使用方法
① 常用标签
② 使用方法
(1) if 标签 :
if 标签主要用来做判断 ,而它的属性 test 表示 逻辑表达式完成条件
<select id="getSuppliersAll" resultType="pojo.Supplier" parameterType="Map">
select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
where
<if test="supCode!=null">
and supCode = #{supCode}
</if>
<if test="supName!=null">
and supName = #{supName}
</if>
</select>
以上代码表示的是,判断supCode和supName两个属性是否为空,如果不为空就添加进SQL语句中。--- 但是我们也能看出些许端倪,要是 supCode和supName两个属性前都带有一个and拼接,这样会导致where后面直接跟and,显然这样会报SQL语句的错!
那我们该如何解决呢?
a . 可以在where条件后跟一个 1=1 的正确等式 ,这样 SQL语句拼接的时候就不会让where后直接跟and了
<select id="getSuppliersAll" resultType="pojo.Supplier" parameterType="Map">
select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
where 1=1
<if test="supCode!=null">
and supCode = #{supCode}
</if>
<if test="supName!=null">
and supName = #{supName}
</if>
</select>
b . 使用第二个标签 -- where
(2) where标签 :
作用 : 替换where关键字,会动态的去掉第一个条件前的 and, 如果所有的参数没有值则不加where关键字
<select id="getSuppliersAll" resultType="pojo.Supplier" parameterType="Map">
select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
<where>
<if test="supCode!=null">
and supCode = #{supCode}
</if>
<if test="supName!=null">
and supName = #{supName}
</if>
</where>
</select>
注:使用where标签需要注意的是,需要给每个条件前都加上 and 关键字。
(3) choose标签:
条件分支:
when:用于定义条件成立时执行的代码块。它包含一个 test
属性,用于指定该条件分支的判断条件
otherwise:用于定义默认的代码块,当所有的 <when>
条件都不成立时,将行 <otherwise>
中定义的代码块
<select id="getSuppliersAll" resultType="pojo.Supplier">
select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
<where>
<choose> <!--相当于Switch-->
<when test="supCode!=null"> <!--相当于case-->
and supCode = #{supCode}
</when>
<when test="supName!=null"> <!--相当于case-->
and supName = #{supName}
</when>
</choose>
</where>
</select>
(4) foreach标签:
用来迭代任何可迭代的对象(如数组,集合)。
collection 属性:
mybatis会将数组参数,封装为一个Map集合。
默认:array = 数组
使用@Param注解改变map集合的默认key的名称
item 属性:本次迭代获取到的元素。
separator 属性:集合项迭代之间的分隔符。 foreach 标签不会错误地添加多余的分隔符。也就是最后一次迭代不会加分隔符。
open 属性:该属性值是在拼接SQL语句之前拼接的语句,只会拼接一次
close 属性:该属性值是在拼接SQL语句拼接后拼接的语句,只会拼接一次
----- foreach标签主要作用是做循环遍历,可以用来实现批量新增,批量删除等操作
(5) set 标签:
作用: 主要用于实现动态修改的操作
<!--动态修改-->
<update id="UpdateManySupplier">
update t_supplier
<set>
<if test="supCode!=null">
supCode = #{supCode},
</if>
<if test="supName!=null">
supName = #{supName},
</if>
<if test="supPhone!=null">
supPhone = #{supPhone},
</if>
</set>
where id = #{id}
</update>
通过使用 set 标签,你可以在动态SQL中灵活地操作变量的值,并根据需要进行赋值、计算或者从查询结果中获取数据。这样能够提高查询的灵活性和可扩展性,使得动态SQL更加强大和适应各种场景的需求。
(6) trim标签:
作用:trim
标签允许你在模板引擎或XML处理器中对字符串进行修剪操作,包括去除空白字符、去除指定的前缀和后缀,以及根据条件进行修剪。它提供了一种方便和灵活的方式来处理和清理字符串数据。
<!--动态查询-->
<select id="getSuppliersAll" resultType="pojo.Supplier">
select supCode,supName,supContact,supPhone,supFax,createdTime from t_supplier
<trim prefix="where" prefixOverrides="and|or" suffix=" LIMIT #{index},#{pageSize}">
<if test="supCode!=null">
and supCode = #{supCode}
</if>
<if test="supName!=null">
and supName = #{supName}
</if>
</trim>
</select>
需要注意的是: prefix
和 suffix
属性是可选的,可以根据需要选择性地添加前缀和后缀。而 prefixOverrides
和 suffixOverrides
属性则提供了更加灵活的方式来根据条件进行字符串修剪操作
三 . 总结
以下是对动态SQL的总结:
-
灵活性:动态SQL允许根据不同的条件和参数来构建不同的SQL语句,从而满足不同的查询需求。可以动态地拼接、修改或生成SQL语句,使得查询操作更加灵活和可变化。
-
参数化查询:动态SQL可以使用参数化查询方式,将用户提供的输入作为参数传递给SQL语句,以防止SQL注入等安全问题,并提高查询的性能和重用性。
-
动态表名和列名:动态SQL可以根据需要动态指定表名和列名,使得查询可以在不同的表或列上进行操作,增强了代码的复用性和适应性。
-
条件判断和流程控制:动态SQL支持条件判断和流程控制语句,如if、case等,可以根据不同的条件选择不同的SQL逻辑和操作。
-
动态数据过滤:动态SQL可以根据条件动态地添加数据过滤条件,实现灵活的数据筛选和过滤,提高查询结果的准确性和有效性。
-
存储过程和触发器:动态SQL可以用于存储过程和触发器中,以实现更加灵活和可控的业务逻辑操作。
总而言之,动态SQL提供了一种灵活、可变化和可扩展的方式来构建和执行SQL语句。它在数据库查询和数据操作中具有广泛的应用,能够满足不同场景下的需求,并简化了开发过程,提高了系统的可维护性和性能。