Mybatis 动态SQL

Mybatis 动态SQL

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

建立实体类：

 代码：

package com.xiaofu.pojo;
import java.util.Date;

public class Blog {
    private int id;
    private String title;
    private String author;
    private Date createTime;
    private int views;

    public Blog() {
    }
    public Blog(int id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id=" + id +
                ", title=" + title +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }
}

建立接口和对应xml

 在核心配置文件中注册 创建的BlogMapper.xml

2.if语句：

 编写一个接口：

package com.xiaofu.dao;
import com.xiaofu.pojo.Blog;
import java.util.List;
import java.util.Map;

public interface BlogMapper {

    //查询博客
    List<Blog> queryBlogIf(Map map);

}

 用 if 实现接口：

<?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.xiaofu.dao.BlogMapper">
        <select id="queryBlogIf" parameterType="map" resultType="com.xiaofu.pojo.Blog">
            select * from blog where 1=1

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

        </select>
<!--     <if test="title != null"> 如果title不等于空 就在sql后面加上 and title = #{title} -->
</mapper>

编写测试类：

 @Test
    public void getlist(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","数据库"); //查询title为数据库的所有信息
//        map.put("author","老王") //查询author为老王的所有信息;
        List<Blog> blogs = mapper.queryBlogIf(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

现在数据库有这些数据：

 运行测试类：

 查询出来了 title 等于 数据库的 数据

3.其他动态SQL常用标签：

choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员精选的 Blog）。

<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

 set

set标签 - 解决更新数据表时字符串拼接逗号”,”问题；

没有使用 if 标签时，如果有一个参数为 null，都会导致错误。

当在 update 语句中使用 if 标签时，如果最后的 if 没有执行，则或导致逗号多余错误。

使用 set 标签可以将动态的配置 set 关键字，和剔除追加到条件末尾的任何不相关的逗号。

#如果最后的if没有执行，会导致逗号多余错误。

<update id="updateUser">
  update User SET
      <if test="name != null">
          name =#{name},
      </if>
      <if test="age != null">
          age=#{age},
      </if>
      <if test="phone != null">
          phone =#{phone},
      </if>
      <if test="class != null">
          class=#{class}
      </if>
  where id=#{id}
</update>

<!--    set标签除掉条件末尾的逗号-->
    <update id="updateUser">
        update User
        <set>
            <if test="name != null">
                name=#{name},
            </if>
            <if test="age != null">
                age=#{age},
            </if>
            <if test="phone != null">
                phone=#{phone},
            </if>
            <if test="class != null">
                adress=#{adress}
            </if>
            where class=#{class}
        </set>
    </update>

where标签 - 解决if标签拼接字符串AND符号问题

where 标签只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，

where 元素也会将它们去除。这样我们就不用在where后面加一个1=1了

当 if 标签较多时，这样的组合可能会导致错误。 如下：

<select id="getRoleListWhere" parameterType="Role" resultMap="roleResultMap">
    SELECT * FROM role WHERE
    <if test="name!=null and name!=' ' ">
        name = #{name}
    </if>
    <if test="roletype!=null and roletype!=' ' ">
        AND roletype = #{roletype}
    </if>
    <if test="userid!=null">
        AND userid = #{userid}
    </if>
</select>

当 name 值为 null 时，查询语句会出现 “WHERE AND” 的情况，解决该情况除了将"WHERE"改为“WHERE 1=1”之外，还可以利用 where标签。

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以 AND 或 OR 开头的，则它会剔除掉。

<select id="selectRoleListByRole" parameterType="Role" resultMap="roleResultMap">
    <include refid="myselect"></include>
    <where>
        <if test="name!=null and name!=''">
            name = #{name}
        </if>
        <if test="roletype!=null and roletype!=''">
            AND roletype = #{roletype}
        </if>
        <if test="userid!=null">
            AND userid = #{userid}
        </if>
    </where>
</select>

trim

set 和 where 其实都是 trim 标签的一种类型， 该两种功能都可以使用 trim 标签进行实现。如果你不满足于where和set实现的功能你就可以使用trim来自定义实现。其中trim有四个主要的属性，分别是：

• prefix：在sql语句加上前缀
• suffix：在sql语句加上后缀
• prefixOverrides：去除指定的前缀内容，比如：prefixOverrides=“AND | OR”，去除sql语句多余的前缀"AND"或者"OR"。
• suffixOverrides：去除指定的后缀内容，比如：suffixOverrides=","，去除sql语句多余的逗号。

自定义实现3.1的set，去除update语句后缀多余的逗号

<update id="updateUser" resultType="User">
    update user set 
    <trim  suffixOverrides=",">
        <if test="class != null">
             class = #{class},
        </if>
        <if test="phone != null and adress != null">
            age > #{age},
        </if>
    </trim> 
    where class=#{class}
</update>

自定义实现3.2的where，去除where子句前缀多余的AND

<select id="findUser" resultType="User">
    select * from user
    <trim prefix="WHERE" prefixOverrides="AND | OR">
        <if test="class != null">
            AND class = #{class}
        </if>
        <if test="phone != null and adress != null">
            AND age > #{age}
        </if>
    </trim>
</select>

sql标签 - 可以提取重复sql语句片段

当多种类型的查询语句的查询字段或者查询条件相同时，可以将其定义为常量，方便调用。为求 <select> 结构清晰也可将 sql 语句分解。

<sql id="myselect">
    SELECT * FROM role
</sql>

include - 用于引用定义的常量

<resultMap type="Role" id="roleRM">
    <id property="id" column="idrole" />
</resultMap>
<select id="selectAllRole" resultMap="roleRM">
    <include refid="myselect"></include>
</select>

<select id="selectRoleListByids" resultMap="roleRM">
    <include refid="myselect"></include></select>

<--  <include refid="myselect"></include>  引用上面写好的sql片段-->

foreach

主要用于构建 in 条件，可在 sql 中对集合进行迭代。也常用到批量删除、添加等操作中。

 - collection：collection 属性的值有三个分别是 list、array、map 三种，分别对应的参数类型为：List、数组、map 集合。

 - item：表示在迭代过程中每一个元素的别名

 - index：表示在迭代过程中每次迭代到的位置（下标）

 - open：前缀

 - close：后缀

 - separator：分隔符，表示迭代时每个元素之间以什么分隔

<select id="findUser" resultType="User">
  SELECT * FROM user
    where class in
    <foreach item="item" index="index" collection="classList" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>