MyBatis学习（五）

动态sql

动态SQL是MyBatis的强大特性之一。
如果你使用过JDBC或其它类似的框架，你应该能理解根据不同条件拼接SQL语句有多痛苦，
例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态SQL并非一件易事，但借助可用于任何SQL映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
使用 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。
-------------------
if
choose (when, otherwise)
trim (where, set)
foreach
-------------

1.搭建环境：创建数据库blog

CREATE TABLE `blog`(
  `id` VARCHAR(50) NOT NULL COMMENT '博客id',
  `title` VARCHAR(100) NOT NULL COMMENT '博客标题',
  `author` VARCHAR(30) NOT NULL COMMENT '博客作者',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

2.创建对应的实体类Blog

package com.chen.pojo;
import lombok.Data;
import java.util.Date;

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

3.创建接口及方法

package com.chen.Dao;

import com.chen.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {

    //插入信息
    int addBlog(Blog blog);

    //使用if进行条件查询
    List<Blog> getBlogIf(Map map);

    //choose (when, otherwise)
    List<Blog> getBlogChoose(Map map);

    //set
    int upDateBlog(Map map);

    //foreach
    List<Blog> getBlogForeach(Map map);
}

4.创建对应的mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.chen.Dao.BlogMapper">
	
    <insert id="addBlog" parameterType="blog">
        insert into mybatis.blog (id, title, author, create_time, views)
        values (#{id}, #{title}, #{author}, #{createTime}, #{views});
    </insert>
<--=============================-->
	 <!--if-->
    <select id="getBlogIf" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <if test="title!=null">
                and title like "%"#{title}"%"
            </if>
            <if test="author!=null">
                and author =#{author}
            </if>
        </where>
    </select>


    <!--choose (when, otherwise) 按照顺序执行-->
    <select id="getBlogChoose" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
           <choose>

               <when test="title !=null">
                   title like "%"#{title}"%"
               </when>

               <when test="author!=null">
                  author =#{author}
               </when>

               <otherwise>
                  views =#{views}
               </otherwise>

           </choose>
        </where>
    </select>


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


    <!--foreach-->
    <select id="getBlogForeach" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
         <!--
	       collection:指定输入对象中的集合属性
	       item:每次遍历生成的对象
	       open:开始遍历时的拼接字符串
	       close:结束时拼接的字符串
	       separator:遍历对象之间需要拼接的字符串
	       select * from blog where 1=1 and (id=1 or id=2 or id=3)
	        -->
            <foreach collection="ids" item="id" open="(" separator="or" close=")">
                id = #{id}
            </foreach>
        </where>
    </select>
</mapper>

5.测试

package com.chen;

import com.chen.Dao.BlogMapper;
import com.chen.Utils.MyBatisID;
import com.chen.Utils.MyBatisUtils;
import com.chen.pojo.Blog;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.*;

public class MyTest {

    @Test
    public void addBlogTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(MyBatisID.getID());
        blog.setTitle("为了mybatis");
        blog.setAuthor("陈少");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        blogMapper.addBlog(blog);

        blog.setId(MyBatisID.getID());
        blog.setTitle("为了java");
        blogMapper.addBlog(blog);

        blog.setId(MyBatisID.getID());
        blog.setTitle("为了jdbc");
        blogMapper.addBlog(blog);

        blog.setId(MyBatisID.getID());
        blog.setTitle("为了hehe");
        blogMapper.addBlog(blog);

        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("title","java");
        List<Blog> blogList = mapper.getBlogIf(map);

        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();

        map.put("views",9999);
        map.put("author","陈少");
        map.put("title","java");
        //map.put("views",0);
        List<Blog> blogList = mapper.getBlogChoose(map);

        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();

        map.put("views",111);
        map.put("author","陈少2");
        map.put("title","javaweb");
        map.put("id","b319357cd8b840f880848e1b63fb4548");
        int i = mapper.upDateBlog(map);
        System.out.println(i);


        sqlSession.close();
    }

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

        Map<String, Object> map = new HashMap<String, Object>();
        List<Integer > ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        map.put("ids",ids);
        List<Blog> blogForeach = mapper.getBlogForeach(map);
        for (Blog blog : blogForeach) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
}

小结

其实动态 sql 语句的编写往往就是一个拼接的问题，
为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错。
多在实践中使用才是熟练掌握它的技巧。