Mybatis——動态SQL

• 一、什麼是動态SQL
• 二、用到的資料庫
• 三、動态SQL編寫（if,choose,update,foreach）與測試
• 四、SQL片段的使用
• 五、源碼

一、什麼是動态SQL

動态SQL就是根據不同的條件生成不同的SQL語句

二、用到的資料庫

三、動态SQL編寫（if,choose,update,foreach）與測試

poji:

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

}
           

mapper接口:

List<Blog> queryBlogIF(Map map);
    List<Blog> queryBlogChoose(Map map);
    int updateBlog(Map map);
    List<Blog> queryBlogForeach(Map map);
           

接口實作:

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

    <select id="queryBlogChoose" parameterType="map" resultType="com.yyx.pojo.Blog">
        select * from blog
        <where>
            <choose>
                <when test="title!=null">
                    title=#{title}
                </when>
                <when test="author!=null">
                    and author=#{author}
                </when>
                <otherwise>
                    and views=#{views}
                </otherwise>
            </choose>
        </where>
    </select>
    <update id="updateBlog" parameterType="map">
        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>
    <select id="queryBlogForeach" parameterType="map" resultType="com.yyx.pojo.Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>
           

注：

1.如果第二個if成立where标簽會自動舍棄and保證SQL正常，若第一個與後面的同時成立則不舍棄

2.set 元素會動态地在行首插入 SET 關鍵字，并會删掉額外的逗号

Test:

@Test
    public void ifTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title", "Java");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();

    }
    @Test
    public void chooseTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
//        map.put("views","9999");
//        map.put("author","狂神說");
        map.put("title", "Java");
        List<Blog> blogList = mapper.queryBlogChoose(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
    }
    @Test
    public void updateTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("id", "590da2980f9a419aac348222a9de4646");
        map.put("title", "Java如此簡單");
        mapper.updateBlog(map);
        sqlSession.commit();
        sqlSession.close();
    }
 @Test
    public void foreachTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        ArrayList<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(3);
        map.put("ids",ids);
        List<Blog> blogList = mapper.queryBlogForeach(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        sqlSession.close();
    }
           

四、SQL片段的使用

有的時候我們可能将一些功能的部分抽取出來，友善複用

1. 使用SQL标簽提取公共部分
2. 在需要使用的地方使用include标簽引用即可

<sql id="blogSqlIf">
        <if test="title!=null">
            title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
    </sql>
    <select id="queryBlogIF" parameterType="map" resultType="com.yyx.pojo.Blog">
        select * from blog
        <where>
            <include refid="blogSqlIf"></include>
        </where>
    </select>
           

五、源碼

github

gitee碼雲

點個star吧!