MyBatis学习笔记5

动态SQL之IF语句

1.接口

List<Blog> queryBlogIF(Map map);

2.xml文件

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

3.测试

@Test
    public void queryBlogID(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","微服务");
        map.put("author","狂神说");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

choose使用

<select id="queryBlogChoose" parameterType="Map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title!=null">
                    title=#{title}
                </when>
                <when test="author!=null">
                    author=#{author}
                </when>
                <otherwise>
                  and views=#{views}
                </otherwise>
            </choose>

        </where>
    </select>

测试

public void queryBlogChoose(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        //map.put("title","微服务");
        //map.put("author","狂神说");
        map.put("views","9999");
        List<Blog> blogs = mapper.queryBlogChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

set设置

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

测试

public void updateBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","微服务");
        map.put("author","狂神说");
        map.put("id","1");
        int blogs = mapper.updateBlog(map);
        System.out.println(blogs);
        sqlSession.close();
    }

sql复用

1.使用SQL标签抽取公共部分

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

2.在需要使用的地方，使用Include标签引用

<update id="updateBlog" parameterType="map">
        update mybatis.blog
        <set>
            <include refid="if-title-author"/>
        </set>

        where id=#{id}
    </update>

注：1.最好基于单表定义SQL片段

2.不存在where 标签

foreach遍历

<select id="queryBlogforeach" parameterType="map" resultType="blog">
       select * from mybatis.blog
       <where>
           <foreach collection="ids" item="id" open="and (" close=")" separator="or">
               id=#{id}
           </foreach>
       </where>
   </select>

测试

@Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        ArrayList<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);

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

缓存

MyBatis 内置了一个强大的事务性查询缓存机制，它可以非常方便地配置和定制。为了使它更加强大而且易于配置，我们对 MyBatis 3 中的缓存实现进行了许多改进。

默认情况下，只启用了本地的会话缓存，它仅仅对一个会话中的数据进行缓存。要启用全局的二级缓存，只需要在你的 SQL 映射文件中添加一行：

一级缓存默认开启，在一个sqlsession中

二级缓存需手动开启是全局缓存

1.配置文件

<setting name="cacheEnabled" value="true"/>

2.Mapper

 <cache eviction="FIFO"
           flushInterval="60000"
           size="512"
           readOnly="true"/>

3.测试

@Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user1 = mapper.getUser(1);
        System.out.println(user1);
        sqlSession.close();

        System.out.println("+++++++++++++++++++");
        sqlSession.close();
        SqlSession sqlSession2 = MybatisUtils.getSqlSession();
        UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
        User user2 = mapper2.getUser(1);
        System.out.println(user2);
        System.out.println(user1==user2);
        sqlSession2.close();

    }