动态SQL


动态SQL就是根据不同条件生成不同SQL语句

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦

例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。

利用动态 SQL,可以彻底摆脱这种痛苦。

1.环境搭建

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

创建一个基础工程

  1. 导包

  2. 编写配置文件

  3. 编写实体类

    @Data
    public class Blog {
       private int id;
       private String tittle;
       private String author;
       private Date createTime;//不要用Sql的包
       private int views;
    
    }
    

    如果实体类与数据库的名字对应不上

    在核心配置文件的settings中配置

    <setting name="mapUnderscoreToCamelCase" value="true"/>
    

    image-20200726132505583

  4. 编写对应Mapper接口和XML文件

2.动态Sql常用标签

2.1动态Sql之IF

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

运用if标签进行判断

标签跟JSTL表达式有点像,都是在test里进行判断,然后通过传进来的map取值

2.2Where标签

在一些sql语句书写中,有可能会出现以下情况

select * from mybatis.blog where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author!=null">
        and author = #{author}
    </if>

在正规Sql书写中是不允许出现1=1的,而一旦去掉1=1,我们又会陷入另一难题,就是如果我们进行判定的情况下,语句前方会加上and

变成了where and 这种格式,显然这是错误的。

为了解决这一问题,我们可以使用Where标签,这个标签会自动检测符合条件的语句,并且判断这是否是第一个跟着的子语句,如果是,它会自动去掉and。

如果没有字句符合条件,他会自己将where去掉。

 <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author!=null">
                and author = #{author}
 </where>

推荐在每个where下的语句加上and

2.3Choose、When、OtherWise

这是一个类似if的组合标签,区别是if标签如果条件满足就会直接拼接sql。

而在Choose标签下的When标签从上往下如果有一个条件满足则会终止拼接sql

如果都没有满足的则会走OtherWise标签

如果没写OtherWise标签则会执行初始SQL。

<where>
    <choose>
        <when test="title!=null">
            and title=#{title}
        </when>
        <when test="author !=null">
            and author=#{author}
        </when>
        <otherwise>
            and views=#{views}
        </otherwise>
    </choose>
</where>

2.4Set

在更新操作时,通常要在where前去掉逗号,而在where前的update语句需要添加逗号

在Set下面就可以自动帮助我们去除多余逗号

<update id="updateBlog" parameterType="blog">
    update mybatis.blog
    <set>
        <if test="title!=null">
            title = #{title},
        </if>
        <if test="author!=null">
            author = #{author},
        </if>
    </set>
    <where>
        id = #{id}
    </where>
</update>

跟where一样,推荐在每个set内的语句后都加上逗号

3. SQL片段

可以将一些重复的部分抽取出来,方便服用

  1. 使用SQL标签抽取公共部分
    <sql id="if-title-author">
       <if test="title != null">
           and title = #{title}
       </if>
       <if test="author!=null">
           and author = #{author}
       </if>
    </sql>
    
  2. 在使用的地方使用include标签引用即可。
    <where>
      <include refid="if-title-author"></include>
    </where>
    

注意:

  1. 最好在单表内使用
  2. 不要带上where

4. ForEach

如果我们想查询一个1,2,3号数据,可以写固定的,但是如果需要代码复用,就需要动态的改变,我们可以通过foreach来进行查询

首先,我们传入一个map,map中包含一个list,我们可以在列表中加入要查询的id。

在foreach中:

collection代表列表的名字,也就是map中的key,因为如果传入map会自动找对应的key来取值。

item代表我们传入数值的参数名(也就是一个迭代器)

open代表开始拼接的字符(注意:这里的【and(】中间需要空格,否则where标签无法识别这是一个and

close代表结束拼接的字符

separator代表中间分割的字符(分隔符左右会自动补空格

<!--select * from mybatis.blog where 1=1 and(id = 1 or id = 2 or id = 3 )-->
<!--我们传递的一个map,map可以存放一个list-->
<select id="queryBlogForEach" parameterType="map" resultType="blog">
    select * from mybatis.blog

    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>

</select>

动态SQL只是在拼接SQL语句,mybatis帮助我们省略了一些细节性的小东西(比如空格,逗号)

面试高频:

  • Mysql引擎
  • innoDB底层原理
  • 索引
  • 索引优化

醉后不知天在水,满船清梦压星河