九.动态sql

1.数据库中建表并插入数据

结果如下：

创建一个IDutil工具类

放在utils下和MyBatisUtils同级

public class IDUtil {
    public static String genId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

创建实体类

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

编写接口层

import java.util.List;
import java.util.Map;
public interface BlogMapper {
     int addBlog(Blog blog);
}

<?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.sy.dao.BlogMapper">
</mapper>

核心配置文件

<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--mapUnderscoreToCamelCase自动开启驼峰命名规则映射（true|false）-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

//类型别名
<typeAliases>
        <package name="com.sy.pojo"/>
</typeAliases>

    <!--绑定接口-->
    <mappers>
        <mapper resource="com/sy/dao/BlogMapper.xml"/>
    </mappers>

sql插入数据

BlogMapper.xml文件中

<insert id="addBlog" parameterType="blog">
        insert into blog (id, title, author, create_time, views)
        values (#{id},#{title},#{author},#{createTime},#{views});
 </insert>

初始化博客数据（插入）

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

        Blog blog = new Blog();
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("定扯说");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Java如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring如此简单");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("微服务如此简单");mapper.addBlog(blog);

        session.close();

    }

用IF语句查询博客

where标签

编写接口

List<Blog> queryBlogIF(Map map);

<select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <if test="title != null">
                and title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
   </select>

测试

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

        HashMap map = new HashMap();


        map.put("title","Java如此简单");
        map.put("author","定扯说");



       mapper.queryBlogIF(map);


        sqlSession.close();
    }

“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉

set标签

int updateBlog(Map map);

<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 queryBlogIF(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        map.put("id","3");
         map.put("title","Java如此究极无敌螺旋简单");   
        map.put("author","定胡扯说");

        mapper.updateBlog(map);
        sqlSession.close();
    }

Choose查询

List<Blog> queryBlogCHOOSE(Map map)

编写接口

<select id="queryBlogCHOOSE" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="title != null">
                    and 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();
    }

sql片段

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

    </sql>

    <select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <include refid="if-t-a"></include>
        </where>
    </select>

注意：
①、最好基于 单表来定义 sql 片段，提高片段的可重用性
②、在 sql 片段中不要包括 where

Foreach遍历查询

List<Blog> queryBlogForeach(Map map);

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

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

        HashMap map = new HashMap();
        ArrayList<Integer> ids = new ArrayList<>();
        map.put("ids",ids);
        ids.add(1);
        ids.add(2);
        ids.add(3);
        ids.add(4);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blogs);
        }
        sqlSession.close();


    }
}

动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错

trim

where元素等价的自定义 trim 元素为：

…

prefixOverrides前缀
suffixOverrides后缀

来看看与set元素等价的自定义trim元素吧：

…
</trim