24-动态SQL常用标签

动态SQL常用标签

choose (when,otherwise)

接口

    List<Blog> queryBlogChoose(Map map);

对应的xml

 <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 queryBlogChoose(){
        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();
    }

trim (where,set)

where 元索只会在至少有一个子元素的条件返回SQL子句的情况下才去插入WHERE"子句。
而且，若语句的开头为AND'或OR '，where元素也会将它们去除.

set元素会动态前置SET关键字，同时也会删掉无关的逗号，
因为用了条件语句之后很可能就会在生成的SQL语句的后面留下这些逗号。

代码演示

接口

   //更新blog
    int updateBlog(Map map);

对应的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  updateBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap  map= new HashMap();
        //只要满足一个就结束了
        map.put("title","java如此简单3");
        //map.put("author","狂神说");
        map.put("id","23d85cfbec954fc8930c2299616e54f6");

       mapper.updateBlog(map);

        sqlSession.close();
    }

使用trim可以实现where也可以实现set功能  了解，少用！

处理where语法：
    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <trim prefix="where" prefixOverrides="and">
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
        </trim>
    </select>


处理set语法：
    <update id="updateBlog" parameterType="map">
        update mybatis.blog
        <trim prefix="set" suffixOverrides=",">
            <if test="title != null">
                title=#{title},
            </if>
            <if test="author != null">
                author=#{author}
            </if>
        </trim>
        where id=#{id}
    </update>

第二种处理set语法：
   <update id="updateBlog" parameterType="map">
        update mybatis.blog
        <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
            <if test="title != null">
                title=#{title},
            </if>
            <if test="author != null">
                author=#{author}
            </if>
        </trim>
        
    </update>

所谓的动态SQL，本质还是SQL语句，只是我们可以在SQL层面，去执行一个逻辑代码