Mybatis-动态sql-02

1.if语句

BlogMapper:

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

BlogMapper.xml:

    <select id="queryBlog" parameterType="map" resultType="com.kakafa.pojo.Blog">
        select * from mybatistest.blog where 1=1
        <if test="title!=null">
            and title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
    </select>

测试：

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

        HashMap map=new HashMap();
        map.put("title","Blur");

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

    }

2.常用标签

• where标签：
  

    <select id="queryBlog" parameterType="map" resultType="com.kakafa.pojo.Blog">
        select * from mybatistest.blog
        <where>
        <if test="title!=null">
            title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
        </where>
    </select>

• choose标签(相当于switch标签)：只要遇到成立的条件就退出，后面的条件就不会判断了

    <select id="queryBlog" parameterType="map" resultType="com.kakafa.pojo.Blog">
        select * from mybatistest.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>

• set标签：

• trim标签：

trim--where

    <select id="queryBlog" parameterType="map" resultType="com.kakafa.pojo.Blog">
        select * from mybatistest.blog
        <trim prefix="where" prefixOverrides="and|or">

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

trim--set

    <update id="updateBlog" parameterType="map" >
        update  mybatistest.blog
        <trim prefix="set" suffixOverrides=",">
                <if test="title!=null">
                    title=#{title},
                </if>
                <if test="author!=null">
                    author=#{author}
                </if>
        </trim>
        where id=#{id};
    </update>

3.SQL片段

• 原来的：

   <select id="queryBlog" parameterType="map" resultType="com.kakafa.pojo.Blog">
        select * from mybatistest.blog
        <where>
        <if test="title!=null">
            title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
        </where>
    </select>  

• 把公共的sql部分提取出来：

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

    <select id="queryBlog" parameterType="map" resultType="com.kakafa.pojo.Blog">
        select * from mybatistest.blog
        <where>
           <include refid="if-title-author"></include>
        </where>
    </select>

4.foreach

<!--select * from mybatistest.blog where 1=1 and (id=1 or id=2 or id=3)-->
    <select id="queryBlogByForeach" parameterType="map" resultType="com.kakafa.pojo.Blog">
        select * from mybatistest.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>