MyBatis使用动态SQL

学习目标：

MyBatis使用动态SQL

---

学习内容：

MyBatis使用动态SQL及标签内容

---

学习时间：

---

学习产出：

动态SQL

• 介绍

动态 SQL 是 MyBatis 的强大特性之一。

如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

1、环境搭建

• sql

create table blog
(
	id varchar(50) not null comment '博客id',
    title varchar(100) not null comment '博客blog标题',
    author varchar(30) not null comment '博客作者',
    create_time datetime not null comment '创建时间',
    views int(30) not null comment '浏览量'
)engine=InnoDB default charset=utf8;

• Utils：增加一个IDUtils用来随机生成id

public class IDUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
    @Test
    public void Test() {
        System.out.println(IDUtils.getId());
    }
}

• Mapper

BlogMapper

public interface BlogMapper {
    int addBlog(Blog blog);
}

• XML

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--引入外部配置文件-->
    <properties resource="db.properties">
        <property name="username" value="root"/>
        <property name="password" value="tmj20000509"/>
    </properties>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <typeAlias type="com.mybatis.pojo.Blog" alias="Blog"/>
    </typeAliases>
    <!--修改默认环境即可更换环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--需要绑定到相应的类-->
    <mappers>
        <mapper resource="com/mybatis/mapper/BlogMapper.xml"/>
    </mappers>
</configuration>

BlogMapper.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.mybatis.mapper.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into blog(id,title,author,create_time,views)
        values(#{id},#{title},#{author},#{createTime},#{views});
    </insert>
</mapper>

• POJO

public class Blog {
   private String id;
   private String title;
   private String author;
   private Date createTime;//属性名和字段名不一致(使用mapUnderscoreToCamelCase开启驼峰命名)
   private int views;
}

使用mapUnderscoreToCamelCase开启驼峰命名

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

• Test

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

        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("Hello1");
        blog.setAuthor("童一");
        blog.setCreateTime(new Date());
        blog.setViews(99);
        mapper.addBlog(blog);
        sqlSession.commit();

        blog.setId(IDUtils.getId());
        blog.setTitle("Hello2");
        mapper.addBlog(blog);
        sqlSession.commit();

        blog.setId(IDUtils.getId());
        blog.setTitle("Hello3");
        mapper.addBlog(blog);
        sqlSession.commit();
    }

2、标签的使用（与jstl类似）

1、if标签的使用

• Mapper
  BlogMapper

public interface BlogMapper {

    int addBlog(Blog blog);

    public List<Blog> searchBlogIF(Map map);
}

• XML
  BlogMapper.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.mybatis.mapper.BlogMapper">
    <select id="searchBlogIF" parameterType="map" resultType="Blog">
            select * from blog
            <where>
                <if test="title !=null">
                    and title=#{title}
                </if>
                <if test="author != null">
                    and author=#{author}
                </if>
            </where>
        </select>
</mapper>

• Test

@Test
    public void searchBlogTest(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,String> map = new HashMap<String,String>();
        map.put("title","Hello1");
        map.put("author","童一");
        List<Blog> blogs = mapper.searchBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
    }

2、Choose标签的使用

• Mapper

BlogMapper

 //choose标签
    public List<Blog> searchBlogChoose(Map map);

• XML

BlogMapper.xml

<!--choose相当于SQL语句中的switch，当when中的条件满足时选择第一个满足的执行，否则执行otherwise-->
    <select id="searchBlogChoose" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    author = #{author}
                </when>
                <otherwise>
                    views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

• Test

@Test
    public void searchBlogChooseTest(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,String> map = new HashMap<String,String>();
        map.put("title","Hello1");
        map.put("author","童一");
        map.put("views","999");
        List<Blog> blogs = mapper.searchBlogChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
    }

3、set标签的使用

• Mapper

BlogMapper

    //set标签
     public void updateBlogSet(Map map);

• XML

BlogMapper.xml

<!--set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号-->
    <update id="updateBlogSet" parameterType="Blog">
        update 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>

• Test

@Test
    public void updateBlogSet(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,String> map = new HashMap<String,String>();
        map.put("title","H");
        map.put("author","童一@");
        map.put("views","999");
        map.put("id","35e243b568df4cef9777455384ddc26f");
        mapper.updateBlogSet(map);
        sqlSession.commit();
    }

4、trim标签

trim标签可以在prefix中定义要使用的标签，如set，if等，并在suffixOverrides后缀覆盖中
设置要覆盖的内容

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

5、sql标签

• Mapper

BlogMapper

    //sql标签
    public List<Blog> searchBlogSql(Map map);

• XML

BlogMapper.xml

 <!--sql标签可以实现SQL语句的复用
        SQL标签会抽取公共的部分，在需要使用该语句的地方使用include标签即可
    -->
    <sql id="searchBlog">
        <if test="title !=null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
    </sql>
    <select id="searchBlogSql" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <include refid="searchBlog"></include>
        </where>
    </select>

• Test

 @Test
    public void searchBlogSqlTest(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,String> map = new HashMap<String,String>();
        List<Blog> blogs=mapper.searchBlogSql(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.commit();
    }

6、foreach标签

• Mapper
  BlogMapper

    //foreach标签
    public List<Blog> searchBlogForeach(Map map);

• XML

BlogMapper.xml

<!--foreach标签：与in配合
    item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，
    open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束;
    -->
    <select id="searchBlogForeach" parameterType="map" resultType="Blog">
        select * from blog where id in
        <foreach item="id" collection="ids" index="1" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <!--或者与where标签配合-->
    <select id="searchBlogForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach item="id" collection="ids" open="(" separator="," close=")">
                id=#{id}
            </foreach>
        </where>
    </select>

• Test

@Test
    public void searchBlogForeachTest(){
        SqlSession sqlSession = MyBatisUtil.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);
        ids.add(3);
        //ids集合中没有数据是会取出所有的元素
        map.put("ids",ids);
        List<Blog> blogs=mapper.searchBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.commit();
    }

动态SQL就是在拼接SQL语句，只要保证SQL的正确性，按照SQL的格式去组合即可