<!--需求1：
根據(jù)作者名字和博客名字來(lái)查詢博客！
如果作者名字為空，那么只根據(jù)博客名字查詢，反之，則根據(jù)作者名來(lái)查詢
select * from blog where title = #{title} and author = #{author}
-->
<select id="queryBlogIf" parameterType="map" resultType="blog">
    select * from blog where
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

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

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

<!--注意set是用的逗號(hào)隔開(kāi)-->
<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>

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

<select id="queryBlogChoose" parameterType="map" resultType="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>

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <!--
        collection:指定輸入對(duì)象中的集合屬性
        item:每次遍歷生成的對(duì)象
        open:開(kāi)始遍歷時(shí)的拼接字符串
        close:結(jié)束時(shí)拼接的字符串
        separator:遍歷對(duì)象之間需要拼接的字符串
        select * from blog where 1=1 and (id=1 or id=2 or id=3)
        -->
        <foreach collection="ids" item="id" open="and (" close=")"
        separator="or">
            id=#{id}
        </foreach>
    </where>
</select>

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

<select id="queryBlogIf" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace-->
        <include refid="if-title-author"></include>
        <!-- 在這里還可以引用其他的 sql 片段 -->
    </where>
</select>

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

CREATE DATABASE `mybatis_study`;

USE `mybatis_study`;

CREATE TABLE `user`(
? `user_id` INT(20) NOT NULL PRIMARY KEY,
? `user_name` VARCHAR(30) DEFAULT NULL,
? `password` VARCHAR(30) DEFAULT NULL
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `user` (`user_id`,`user_name`,`password`) VALUES
(1, '張三', '123456'),
(2, 'admin', 'admin'),
(3, 'root', 'root');