动态SQL

一、搭建环境

# 创建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;

package com.comprehensive.pojo;

import java.util.Date;

//Blog实体类
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date create_time;
    private int views;

    public Blog() {}

    public Blog(String id, String title, String author, Date create_time, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.create_time = create_time;
        this.views = views;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreate_time() {
        return create_time;
    }

    public void setCreate_time(Date create_time) {
        this.create_time = create_time;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id='" + id + '\'' +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", create_time=" + create_time +
                ", views=" + views +
                '}';
    }
}

//BlogMapper接口
public interface BlogMapper {
    void addBlog(Blog blog); //插入数据，顺便测试环境
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.comprehensive.dao.BlogMapper">

    <!-- 构建环境 -->
    <insert id="addBlog" parameterType="com.comprehensive.pojo.Blog">
        insert into mybatis.blog
        (id, title, author, create_time, views)
        VALUES
        (#{id}, #{title}, #{author}, #{create_time}, #{views})
    </insert>

</mapper>

//插入数据，顺便测试环境
public class Test_buildEnvironment {
    @Test
    public void test() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(ID_Util.getId());
        blog.setTitle("MyBatis如此简单");
        blog.setAuthor("comprehensive");
        blog.setCreate_time(new Date());
        blog.setViews(9999);
        mapper.addBlog(blog);

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

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

        blog.setId(ID_Util.getId());
        blog.setTitle("微服务如此简单");
        blog.setAuthor("狂神说");
        mapper.addBlog(blog);

        sqlSession.commit();
        sqlSession.close();
    }
}

二、动态SQL实现

动态SQL之if

public interface BlogMapper {
    List<Blog> queryByIf(Map<String, Object> map);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.comprehensive.dao.BlogMapper">

    <!-- 动态SQL if -->
    <select id="queryByIf" parameterType="map" resultType="com.comprehensive.pojo.Blog">
        select * from mybatis.blog
        <where>
            <if test="title != null">
                title=#{title}
            </if>
            <if test="author != null">
                and author=#{author}
            </if>
        </where>
    </select>

</mapper>

动态SQL之choose

public interface BlogMapper {
    List<Blog> queryByChoose(Map<String, Object> map);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.comprehensive.dao.BlogMapper">

    <!-- 动态SQL choose -->
    <select id="queryByChoose" parameterType="map" resultType="com.comprehensive.pojo.Blog">
        select * from mybatis.blog
        <where>
            <choose>
                <when test="views != null">
                    views=#{views}
                </when>
                <when test="title != null">
                    title=#{title}
                </when>
                <otherwise>
                    author=#{author}
                </otherwise>
            </choose>
        </where>
    </select>

</mapper>

动态SQL之set

public interface BlogMapper {
    void updateBySet(Map<String, Object> map);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.comprehensive.dao.BlogMapper">

    <!-- 动态SQL set -->
    <update id="updateBySet" 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>

</mapper>

动态SQL之trim

具体的可以查看官方文档

动态SQL之foreach

public interface BlogMapper {
    List<Blog> queryByForeach(Map<String, Object> map);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.comprehensive.dao.BlogMapper">

    <!-- 动态SQl foreach -->
    <select id="queryByForeach" parameterType="map" resultType="com.comprehensive.pojo.Blog">
        select * from mybatis.blog
        <where>
            <foreach collection="id_list" item="id" open="id in(" separator="or" close=")">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>