基本写法

insert

• 取回自增主键

- useGeneratedKeys取出由数据库自动生成的主键，仅对支持主键自动生成的数据库有效，默认为 false
- keyProperty主键的名称，必须与useGeneratedKeys 一起使用，默认未设置

<insert id="insertUserNoId" useGeneratedKeys="true" keyProperty="id"
        parameterType="com.imooc.mybatis.model.User">
  INSERT INTO user(username,age,score) VALUES (#{username},#{age},#{score})
</insert>

• 批量插入

// 需要在数据库配置文件中url中添加参数allowMultiQueries=true
<insert id="addPersons">
    insert into person(username,email,gender) VALUES
    <foreach collection="persons" item="vo" separator=",">
        (#{vo.username},#{vo.email},#{vo.gender})
    </foreach>
</insert>

update

<update id="updateUserAgeById">
  UPDATE user SET age = #{age} WHERE id = #{id}
</update>

// set  主要用于动态更新时，智能消除逗号
<update id="dynamicUpdate" parameterType="Blog">
        update t_blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="content != null">
                content = #{content},
            </if>
        </set>
        where id = #{id}
</update>

delete

<delete id="deleteUserById">
  DELETE FROM user WHERE id = #{id}
</delete>

query

• 模糊查询

 <if test="_databaseId == 'mysql'">
      CONCAT('%',#{username},'%')
 </if>
 
 <if test="_databaseId == 'postgre' or _databaseId= 'oracle' ">
      '%' || #{username} || '%'
 </if>

• IN 查询

 <select id="selectUserInIds" resultType="com.imooc.mybatis.model.User">
  SELECT * FROM user
  WHERE id IN
  <foreach collection="collection" open="(" close=")" separator="," item="item" index="index">
    #{item}
  </foreach>
</select>

• 日期查询

jdbcType =DATE 日期精确到年月日 剩余分秒 填0补齐
jdbcType= TIMESTAMP 日期精确到时分秒
jdbcType 重要性 传入null值时，可以防止null空指针异常报错

<if test="startTime != null ">
   AND <![CDATA[ order_date >= #{startTime,jdbcType=DATE}  ]]>
</if>