Mybatis-day10-动态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>

 

二、常用标签

 choose(when\otherwise)

只是想从多个条件中选择一个使用

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

trim(where\set)

解决插入之后where语句错误的问题

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>
————————————————————————————————————————————————————————————————————————————————————
<!--实例-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>

set 解决updata应用

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

et 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。

如果 where 、set元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 、set元素的功能。

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
---------------------------------------------------------------
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

 

三、FOREACH

<sql id=""/>

<include refid=""/>

实现代码块的复用

Mybatis-day10-动态sql

 

 遍历一个集合(sql语句中的in)foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。

Mybatis-day10-动态sql

 

上一篇:SpringBoot整合达梦数据库


下一篇:【springboot2】数据库连接操作