动态 SQL 的使用与优化

最新推荐文章于 2024-08-15 09:54:59 发布

Disper_

最新推荐文章于 2024-08-15 09:54:59 发布

阅读量639

点赞数 7

文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/disper1110/article/details/140831049

版权

动态 SQL 允许根据不同条件拼接 SQL 语句，从而实现对数据库的更精确操作。在这里，我们将探讨如何使用动态 SQL 的标签（如 <if>、<choose>、<where>、<set>、<foreach> 和 <bind>）在映射文件或注解中实现这一功能，并优化代码的可读性和可维护性。

1. 使用 `<if>` 标签

语法

<if test="条件"> 满足条件的语句 </if>

示例

在查询课程时，可以使用 <if> 标签根据传入参数动态拼接 SQL 语句：

<select id="findCourseAll" resultType="Course">
    SELECT * FROM course WHERE 1=1
    <if test="cid != 0">
        AND cid = #{cid}
    </if>
    <if test="cname != null">
        AND cname = #{cname}
    </if>
    <if test="tid != null">
        AND tid = #{tid}
    </if>
</select>

2. 使用 `<choose>` 标签

语法

<choose>
    <when test="条件">满足条件的语句</when>
    <otherwise>满不满足条件的语句</otherwise>
</choose>

示例

通过 <choose> 标签，可以实现类似 Java 中 switch 的结构：
条件有先后顺序，越优先要先写一旦匹配后面不执行

<select id="findCourseAllChoose" resultType="com.yk.bean.Course">
    <include refid="cousql"/>
    <where>
        <choose>
            <when test="cname != null">
                AND cname=#{cname}
            </when>
            <otherwise>
                AND 1=1
            </otherwise>
        </choose>
    </where>
</select>

3. 使用 `<where>` 标签

特性

动态添加 WHERE 关键字: 若无条件则不添加；有条件时自动添加 WHERE。
自动去除冗余的 AND 或 OR: 例如，对于开头为 AND 的语句进行处理。

示例

<select id="findCourseAllWhere" resultType="Course" parameterType="course">
    SELECT * FROM course
    <where>
        <if test="cid != 0">
            AND cid = #{cid}
        </if>
        <if test="cname != null">
            AND cname = #{cname}
        </if>
        <if test="tid != null">
            AND tid = #{tid}
        </if>
    </where>
</select>

4. 使用 `<set>` 标签

特性

主要用于更新操作，其功能和 <where> 类似，以 SET 开头，支持动态字段更新。该标签会自动处理内容前的逗号。

示例

<update id="updateCourseSet" parameterType="course">
    UPDATE course
    <set>
        <if test="cname != null">
            cname = #{cname},
        </if>
        <if test="tid != null">
            tid = #{tid},
        </if>
    </set>
    <where>
        cid = #{cid}
    </where>
</update>

5. 使用 `<foreach>` 标签

特性

item: 当前元素
index: 元素索引
collection: 传入的数组或集合
open: 开始符
close: 结束符
separator: 元素间隔符

示例

对于数组参数：

<select id="findCourseAllFor" resultType="com.yk.bean.Course">
    <include refid="cousql"/>
    <where>
        <foreach collection="array" item="x" open="(" separator="," close=")">
            #{x}
        </foreach>
    </where>
</select>

对于集合参数：

<insert id="addStudent">
    INSERT INTO student(sname, birthday, ssex, classid)
    VALUES
    <foreach collection="list" item="stu" separator=",">
        (#{stu.sname}, #{stu.birthday}, #{stu.ssex}, #{stu.classid})
    </foreach>
</insert>

6. 使用 `<bind>` 标签

<bind> 标签用于定义上下文变量，在动态 SQL 中提升灵活性。
${}和#{}的区别：

${} ：字符串的替换不能防止sql注入
#{} ：预处理sql语句 ?占位 preparedstatment进行防止sql注入

模糊查询
方案一：concat
select * from student where sname like concat('%',#{v},'%')
方案二：在业务层传入
select * from student where sname like #{v}
方案三： sql语法
select * from student where sname like "%"#{v}"%"

示例

<select id="findCourseAllS" resultType="student">
    <bind name="keyn" value="'%' + _parameter + '%'" />
    SELECT * FROM student WHERE sname LIKE #{keyn}
</select>

7. 主键回填

在插入操作中，可以使用 useGeneratedKeys 属性来实现主键的自动回填。

示例

<insert id="insertClass" parameterType="com.yk.bean.ClassNa" useGeneratedKeys="true" keyProperty="classid">
    INSERT INTO class (classname)
    VALUES (#{classname})
</insert>