mybatis 动态SQL

mybatis的动态SQL是什么？

根据不同的条件生成不同的SQL语句

1. SQL语句上增加了逻辑判断

2. 按照格式，排列组合标签，拼接SQL语句

建议：先在MySql中书写SQL语句，确保SQL语句的正确性和有效性，然后再修改为动态SQL

 

一个比较完整的生成动态SQl的例子

包含了if、choose、set、foreach等标签

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xiahui.dao.BlogMapper">
   <insert id="addBlog" parameterType="Blog">
       insert into blog(`id`,`title`,`author`,`create_time`,`views`) values (#{id},#{title},#{author},#{createTime},#{views})
   </insert>

    <select id="getBlogByIf" parameterType="map" resultType="Blog">
        select * from blog where 1=1
        <if test="title!=null">
            and title= #{title}
        </if>
        <if test="author!=null">
            and author= #{author}
        </if>
    </select>

    <select id="getBlogByChoose" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title!=null">
                    and title = #{title}
                </when>
                <when test="author!=null">
                    and author = #{author}
                </when>
                <otherwise>
                    views >= #{views}
                </otherwise>
            </choose>
        </where>
    </select>

    <update id="modifyBlog" parameterType="Blog">
        update blog
        <set>
            <include refid="if-title-author"></include>
        </set>
        where id=#{id}
    </update>

    <sql id="if-title-author">
        <if test="title!=null">
            title=#{title},
        </if>
        <if test="author!=null">
            author= #{author},
        </if>
    </sql>

    <!--select * from blog where 1=1 and (id=1 or id=2 or id=3)-->
    <select id="getBlogByForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

</mapper>

 

 拆开来看

where 与 if 标签

注意：

1、如果if条件都不满足，将返回所有的数据

2、if 会自动去掉多余的语句连接词（and, or） 

    <select id="getBlogByIf" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <if test="title!=null">
                and title= #{title}
            </if>
            <if test="author!=null">
                and author= #{author}
            </if>
        </where>
    </select>

 

 

Choose标签

相当于java语言的switch语句，when->case, otherwise->default

    <select id="getBlogByChoose" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title!=null">
                    and title = #{title}
                </when>
                <when test="author!=null">
                    and author = #{author}
                </when>
                <otherwise>
                    views >= #{views}
                </otherwise>
            </choose>
        </where>
    </select>

 

Set标签

    <update id="modifyBlog" parameterType="Blog">
        update blog
        <set>
            <include refid="if-title-author"></include>
        </set>
        where id=#{id}
    </update>

此处用到了sql片段 "if-title-author"

它是这样定义的

    <sql id="if-title-author">
        <if test="title!=null">
            title=#{title},
        </if>
        <if test="author!=null">
            author= #{author},
        </if>
    </sql>

使用<sql></sql>标签提取通用的sql语句片段，在需要的地方使用<include></include>标签导入它，这样做可以提升代码的复用性

注意：sql语句片段中不要有where

 

foreach标签

也就是遍历集合中的每个元素，类似java的foreach语句:

for(var id: ids){

}

    <!--select * from blog where 1=1 and (id=1 or id=2 or id=3)-->
    <select id="getBlogByForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

 

trim标签

在包含的内容前加上前缀，或在其后加上后缀。

where, set 两个标签都是基于trim标签实现的

 

参考 https://www.bilibili.com/video/BV1NE411Q7Nx?p=25