Mybatis动态SQL

什么是动态SQL：

根据不同的条件生成不同的SQL语句
官方文档地址

IF语句

BlogMapper.xml
通过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>

测试

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

        HashMap map = new HashMap();
//        map.put("title","Java如此简单");
        map.put("author","狂神说");

        List<Blog> blogs = mapper.queryBlogIf(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

where

上面例子中的mapper.xml变为

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

测试

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

        HashMap map = new HashMap();
//        map.put("title","Java如此简单");
        map.put("author","狂神说");

        List<Blog> blogs = mapper.queryBlogIf(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
}

and被自动去掉
**如果没有给map传入参数

choose、when、otherwise

mapper.xml
有title就按title查，没有title有author就按author查，两者都没有就按views查

<select id="queryBlogChoose" parameterType="map" resultType="blog">
         select * from mybatis.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>

测试

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

        HashMap map = new HashMap();
          map.put("title","Java如此简单");
//        map.put("author","狂神说");
          map.put("views",9999);

        List<Blog> blogs = mapper.queryBlogChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

set

mapper.xml

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

        where id = #{id}
    </update>

测试

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

        HashMap map = new HashMap();
          map.put("title","Java如此简单2");
          map.put("author","狂神说");
//          map.put("views",9999);
        map.put("id","7ac18489a06442898f09faf72f5f0bb0");
       mapper.updateBlog(map);


        sqlSession.close();
    }

trim、where、set