【MyBatis】MyBatis基础教程(8)

前言：

本文内容：动态SQL和IF语句、动态SQL常用标签、动态SQL和Foreach

推荐免费MyBatis基础教程视频：【狂神说Java】Mybatis最新完整教程IDEA版通俗易懂_哔哩哔哩_bilibili

动态SQL和IF语句

BlogMapper.java

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

BlogMapper.xml

<?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.jokerdig.mapper.BlogMapper">
<!--动态SQL中if实现条件查询-->
<!--    where标签可以帮我们智能的去除不需要的and或or-->
    <select id="queryBlogIF" 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>
</mapper>

BlogTest.java

@Test
    public void test1(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        // 添加title改变查询结果
        map.put("title","Hey,Joker");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

动态SQL常用标签

Chose…When…OtherWise

BlogMapper.java

// 查询 chose
List<Blog> queryBlogChose(Map map);

BolgMapper.xml

<!--动态SQL中chose实现条件查询-->
   <select id="queryBlogChose" parameterType="map" resultType="Blog">
       select * from blog
       <where>
           <choose>
               <when test="title!=null">
                   title = #{title}
               </when>
               <when test="author!=null">
                   and author=#{author}
               </when>
               <otherwise>
                   and views = #{views}
               </otherwise>
           </choose>
       </where>
   </select>

BlogTest.java

@Test
  public void test2(){
      SqlSession sqlSession = MyBatisUtil.getSqlSession();
      BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

      HashMap map = new HashMap();
      // 添加title改变查询结果
      map.put("author","JokerDaxiong");
      List<Blog> blogs = mapper.queryBlogChose(map);
      for (Blog blog : blogs) {
          System.out.println(blog);
      }

      sqlSession.close();
  }

Set

更新时去除多余逗号

BlogMapper.java

 // 更新博客 使用set标签
int updateBlog(Map map);

BolgMapper.xml

<!--    更新博客 使用set标签-->
    <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title!=null">
                title=#{title},
            </if>
            <if test="author!=null">
                author=#{author},
            </if>
        </set>
        where id = #{id}
    </update>

BlogTest.java

@Test
public void test3(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    HashMap map = new HashMap();
    // 添加title改变查询结果
    map.put("title","测试set");
    map.put("author","JokerDaxiong");
    map.put("id","3");
    mapper.updateBlog(map);
    sqlSession.close();
}

trim(where,set)

trim包括了where和set,trim可以通过自定义trim元素来定制where元素的功能。(使用并不是很多)

<!--where-->
<trim prefix="where" prefixOverrides="and|or">
.....
</trim>
<!--set-->
<trim prefix="set" suffixOverrides=",">
.....
</trim>

动态SQL和Foreach

SQL片段

有的时候，我们可能会把一些公共的SQL提取出一些SQL片段

 <!--   提取公共sql片段-->
    <sql id="queryColl">
        <if test="title!=null">
            and title = #{title}
        </if>
        <if test="author!=null">
            and author = #{author}
        </if>
    </sql>
<!--    使用include标签引用sql片段-->
    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        select * from blog
        <where>
           <include refid="queryColl"></include>
        </where>
    </select>

注意事项

• 最好基于单表来定义SQL片段
• 不要存在where和set标签

Foreach

动态SQL的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建in条件语句的时候

官方举例

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

练习

BlogMapper.java

// 查询 id 为 123的信息 使用foreach
List<Blog> queryBlogForEach(Map map);

BlogMapper.xml

<!--    使用foreach遍历-->
    <select id="queryBlogForEach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

BlogTest.java

@Test
   public void test4(){
       SqlSession sqlSession = MyBatisUtil.getSqlSession();
       BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

       HashMap map = new HashMap();
       ArrayList<Integer> ids = new ArrayList<Integer>();
       ids.add(1);
       ids.add(2);
       map.put("ids",ids);
       List<Blog> blogs = mapper.queryBlogForEach(map);
       for (Blog blog : blogs) {
           System.out.println(blog);
       }
       sqlSession.close();
   }

动态SQL就是在拼接SQL语句，我们只要保证SQL的正确性，按照SQL的格式，去排列组合就可以了