动态 SQL

目的:为了摆脱在不同条件拼接 SQL 语句的痛苦

在不同条件在生成不同的SQL语句

本质上仍然是SQL语句,不过是多了逻辑代码去拼接SQL,只要保证SQL的正确性按照格式去排列组合

可以先写好SQL语句

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

if,          where(可以自动去除多余的and)

    
        select * from mybatis.blog
        
        <if test="title !=null">
            and title=#{title}
        if>
        <if test="author !=null">
            and author=#{author}
        if>
        
    

choose(when, otherwise)

从多个条件中选择一个使用,有点像 Java 中的 switch 语句

    <select id="queryBlog_choose" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
        <choose>
            <when test="title !=null ">
                title=#{title}
            when>
            <when test="author!=null ">
                author=#{author}
            when>





        choose>
        where>
    select>

set(可以去除多余的逗号,  )    [update]

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

SQL片段(提取共有的SQL语句,达到便捷复用的作用)  [尽量只要if判断的就行]

    <sql id="oo_sb">
        <if test="title != null">
            title=#{title},
        if>
        <if test="author != null">
            author=#{author},
        if>
    sql>





 <include refid="oo_sb">include>

    <select id="queryBlogget1_3" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <foreach collection="ids" item="id_"
                     open="id in (" separator="," close=")">
                #{id_}
            foreach>
        where>
    select>

SQL:select * from mybatis.blog where id in(1,2,3)


ids=通过万能map传入的集合名称  
item=遍历的每个元素的值
open 前缀
separator 分隔
close  后缀
 #{iid}     前缀和后缀里面的东西  等于遍历的元素
collection取值不用加#{}

方法二:

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

SQL:select * from mybatis.blog where(id=? or id=?)

//测试代码片段

        HashMap map = new HashMap();
            ArrayList ids = new ArrayList<>();
            ids.add(1);
            ids.add(2);
            ids.add(3);
            map.put("ids",ids);
            List blogs=  mapper.queryBlogget1_3(map);