mybatis动态sql

1.动态SQL

【官方声明】

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

1.1 IF关键字

1.1.1 动态SQL测试

dao层接口 => 【BlogsMapper】

import java.util.List;
import java.util.Map;

public interface BlogsMapper {
	// 创建【插入测试数据】的方法
    int addBlog(Blogs blog);

    // 创建【实现动态SQL查询】的方法
    List<Blogs> getBlogsByDynamicSQL(Map<String,String> map);
}

XML映射文件 => 【BlogsMapper.xml】

<select id="getBlogsByDynamicSQL">
    <!-- 注意： where 1 = 1 尽量不使用 -->
	select * from test.blog where 1 = 1
    <!-- <if>标签： 当向数据库发送的请求中，【title】字段不为空时，则添加【title】字段的查询过滤条件 -->
    <if test="title != null">
        and title = #{title}
    </if>
    <!-- <if>标签： 当向数据库发送的请求中，【author】字段不为空时，添加【author】字段的查询过滤条件 -->
    <if test="author != null">
        and author = #{author}
    </if>
</select>

测试实现类 => 【DaoTest】

@Test
public void getBlogsByDynamicSQL(){
    
    MyBatisUtils mybatis = new MyBatisUtils();
    SqlSession sqlSession = mybatis.getSqlSession();
    BlogsMapper mapper = sqlSession.getMapper(BlogsMapper.class);
    
    List<Blogs> blogs = mapper.getBlogsByDynaticSQL();
    for (Blogs blog : blogs){
        System.out.print(blog);
    }
    
    sqlSession.close();
}

1.2 where、set，Foreach

foreach用法

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！

提示： 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  <where>
    <foreach item="item" index="index" collection="list"
        open="ID in (" separator="," close=")" nullable="true">
          #{item}
    </foreach>
  </where>
</select>

IF【官方实例】
- 失败案例
  - 前面几个例子已经合宜地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例，这次我们将 “state = ‘ACTIVE’” 设置成动态条件，看看会发生什么。
```
<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>
</select>
```
  - 如果没有匹配的条件会怎么样？最终这条 SQL 会变成这样：
```
SELECT * FROM BLOG
WHERE
```
  - 这会导致查询失败。如果匹配的只是第二个条件又会怎样？这条 SQL 会是这样:
```
SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’
```
- 成功案例
  - 这个查询也会失败。这个问题不能简单地用条件元素来解决。这个问题是如此的难以解决，以至于解决过的人不会再想碰到这种问题。MyBatis 有一个简单且适合大多数场景的解决办法。而在其他场景中，可以对其进行自定义以符合需求。而这，只需要一处简单的改动：
```
<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>
```

【官方声明】

<where>标签：
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

<trim>标签：

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

//此案例实际上就是where这个标签的工作原理，在拼接sql前方/开头添加where，并去掉拼接语句整体最前方的and/or 单词
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  <!-- 【prefixOverrides】属性：忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。-->
</trim>

<set>标签：

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。

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

1.2.1 测试

Dao层接口添加实现方法 => 【BlogsMapper】
```
int updateBlogInfoBySet(Map map);
```

XML映射文件 => 【BlogsMapper.xml】

<update id="updateBlogInfoBySet" parameterType="blogs">
    update test.blog 
    <set>
    	<if test="title != null"> title = #{title}</if>
        <if test="author != null"> author = #{author}</if>
        <if test="create_time != null"> create_time = #{createTime}</if>
        <if test="views != null"> views = #{views></if>
    </set>
</update>

测试实现类 => 【DaoTest】

@Test
public void dynamicSqlUpdateBySet(){
    MyBatisUtils myBatisUtils = new MyBatisUtils();
    SqlSession sqlSession = myBatisUtils.getSqlSession();
    BlogsMapper mapper = sqlSession.getMapper(BlogsMapper.class);

    HashMap<String,String> map = new HashMap<String, String>();
    map.put("title","updatedTitle");
    map.put("author","updatedAuthor");
    map.put("createTime", String.valueOf(new Date()));
    map.put("views","1");
    map.put("id","5bde3e48b521443bb40524988456a668");

    int i = mapper.updateBlogInfoBySet(map);
    if (i > 0 ){
        System.out.println("Update Succeed!");
    }
    sqlSession.close();
}

14.3 choose、when、otherwise

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

例子

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

1.3.1 测试

1.3.1.1 环境搭建

dao层接口添加方法 => 【BlogsMapper】
```
List<Blogs> queryBlogsByChoose(Map map);
```

xml映射文件 => 【BlogsMapper.xml】

//choose标签类似于Java中的switch
<select id="queryBlogsByChoose" resultType="blogs" parameterType="map">
    select * from test.blog
    <!-- <choose>标签： 选择性返回
         |-   <when>标签： 当其内部条件成立时返回
         |-   <otherwise>标签： 当所有条件都不满足时执行
	-->
 <sql id="allchoose">
     <where>
         <choose>
             <when test="title!=null">
                 title=#{title}
             </when>
             <when test="author!=null">
                 and author=#{author}
             </when>
             <otherwise>
                 and views=5000
             </otherwise>
         </choose>
     </where>
 </sql>
 
 <select id="queryBlogChoose" parameterType="map" resultType="blog">
     select * from mybatis.blog
     <include refid="allchoose"></include>
 </select>

测试实现类 => 【DaoTest】 (按测试情况进行配置 )

14.3.1.2 测试结果

单个条件成立

测试实现类 => 【DaoTest】

@Test
public void dynamicSqlChoose(){
    MyBatisUtils myBatisUtils = new MyBatisUtils();
    SqlSession sqlSession = myBatisUtils.getSqlSession();
    BlogsMapper mapper = sqlSession.getMapper(BlogsMapper.class);

    HashMap<String,String> map = new HashMap<String, String>();
    //创建单条件成立的集合
    map.put("title","MyBatis");
    System.out.println(mapper.queryBlogsByChoose(map));

    sqlSession.close();
}

多个条件成立

测试实现类 => 【DaoTest】

@Test
    public void dynamicSqlChoose(){
        MyBatisUtils myBatisUtils = new MyBatisUtils();
        SqlSession sqlSession = myBatisUtils.getSqlSession();
        BlogsMapper mapper = sqlSession.getMapper(BlogsMapper.class);

        HashMap<String,String> map = new HashMap<String, String>();
        //创建多个条件满足的集合
        map.put("title","MyBatis");
      	map.put("author","Camelot");
        System.out.println(mapper.queryBlogsByChoose(map));

        sqlSession.close();
    }

<when>都为false，<otherwise>为true

测试实现类 => 【DaoTest】

@Test
public void dynamicSqlChoose(){
    MyBatisUtils myBatisUtils = new MyBatisUtils();
    SqlSession sqlSession = myBatisUtils.getSqlSession();
    BlogsMapper mapper = sqlSession.getMapper(BlogsMapper.class);

    //创建空集合传入
    HashMap<String,String> map = new HashMap<String, String>();
    System.out.println(mapper.queryBlogsByChoose(map));

    sqlSession.close();
}

结论

<choose>标签会在多个条件都满足的情况下，仅会返回第一个传参的返回值。

但当其他条件都不满足时，可以添加<otherwise>标签，用于返回一个固有值。

14.4 Trim

Trim可以自定义SQL语句中的规范，当<where>标签、<set>标签不满足时，可以使用Trim自定义。

14.4.1 Trim自定义测试

使用Trim复写<where>、<set>规则

14.4.1.1 <trim>实现<where>

Dao层接口 => 【BlogsMapper】

List<Blogs> queryBlogsByTrim(Map<String,String> map)

XML映射器 => 【BlogsMapper.xml】

<select id="queryBlogsByTrim" parameterType="blogs">
	select * from test.blog
    <trim prefix="WHERE" prefixOverride="AND |OR ">//prefix去前缀
        <if test="titleMap != null"> AND title = #{titleMap}</if>
        <if test="authorMap != null"> OR author = #{authorMap}</if>
    </trim>
</select>

测试实现类 => 【DaoTest】

@Test
public void dynamicSqlSelectByTrim(){
    MyBatisUtils mybatis = new MyBatisUtils;
    SqlSession sqlSession = mybatis.getSqlSession();
    BlogsMapper mapper = sqlSession.getMapper(BlogsMapper.class);

    HashMap<String,String> map = new HashMap<String,String>();
    map.put("titleMap","MyBatis");
    map.put("authorMap","Altria");

    for (Blogs blog : mapper.queryBlogsByTrim(map)) {
        System.out.println(blog);
    }
	
    sqlSession.close();
}

1.4.1.2 <trim>实现<set>

Dao层接口 => 【BlogsMapper】

int updateBlogInfoByTrim(Map<String,String> map)

XML映射文件 => 【BlogsMapper.xml】

<update id="updateBlogInfoByTrim" parameterType="map">
	update test.blog
    <trim prefix="SET" suffixOverride=",">//suffix去后缀
        <if test="titleMap != null"> title = #{titleMap},</if>
        <if test="authorMap != null"> author = #{authorMap},</if>
    </trim>
    where id = #{idMap}
</update>

测试实现类 => 【DaoTest】

@Test
public void dynamicSqlUpdateByTrim(){
    
    MyBatisUtils mybatis = new MyBatisUtils();
    SqlSession sqlSession = mybatis.getSqlSession();
    BlogsMapper mapper = mybatis.getMapper(BlogsMapper.class);
    
    Map<String,String> map = new HashMap<String,String>();
    map.put("authorMap","Altria");
    map.put("titleMap","Spring Framework Updated");
    map.put("idMap","5aa45402bc764755b3ae406be6b27d33");
    
    int i = mapper.updateBlogInfoByTrim(map);
    if( i > 0 ){
        System.out.println("Update Succeed!");
    }
}

posted @ 2022-03-24 22:15 Cn_FallTime 阅读(42) 评论(0) 编辑收藏举报

刷新页面返回顶部

Loading

CnFallTime

FallTime的垃圾堆

mybatis动态sql

1.动态SQL

1.1 IF关键字

1.1.1 动态SQL测试

1.2 where、set，Foreach

1.2.1 测试

14.3 choose、when、otherwise

1.3.1 测试

1.3.1.1 环境搭建

14.3.1.2 测试结果

14.4 Trim

14.4.1 Trim自定义测试

14.4.1.1 <trim>实现<where>

1.4.1.2 <trim>实现<set>

公告