04_MyBatis动态SQL

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

1.1 if

但是会出现问题:

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

Preparing: select * from blog where and title = ? and author = ? 

用Where解决问题

1.2 Where

1.3 choose

1.4 Set

<update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id};
    </update>

1.5 sql片段

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。
提取SQL片段:

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

引用SQL片段:

 <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <include refid="if"></include>
        </set>
        where id = #{id};
    </update>

1.6 Foreach

<?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.xxxx.dao.BlogMapper">
    <!--
    collection:指定输入对象中的集合属性
    item:每次遍历生成的对象
    open:开始遍历时的拼接字符串
    close:结束时拼接的字符串
    separator:遍历对象之间需要拼接的字符串
    select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
   <select id="queryBlogForeach" parameterType="map" resultType="com.xxxx.pojo.Blog">
       select * from blog
       <where>
           <foreach collection="ids"  item="id" open="and (" close=")" separator="or">
           id=#{id}
           </foreach>
       </where>
   </select>
</mapper>

public class DaoTest {
    //注意导包：org.apache.log4j.Logger
    static Logger logger = Logger.getLogger(DaoTest.class);
    @Test
    public  void test(){
       try(SqlSession session = MyBatisUtils.getSqlSesion();){
           BlogMapper mapper = session.getMapper(BlogMapper.class);
           Map<String, List> map = new HashMap<>();
           List<Integer> ids = new ArrayList<>();
           ids.add(1);
           ids.add(2);
           ids.add(3);
           map.put("ids" , ids);
           List<Blog> blogs = mapper.queryBlogForeach(map);
           for (Blog blog : blogs) {
               System.out.println(blog);
           }
       }
    }
}