MyBatis使用XML映射文件

<cache/>

<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>

2.1.1.自定义cache

使用自定义的cache：

<cache type="com.domain.something.MyCustomCache"/>

自定义cache要实现org.apache.ibatis.cache.Cache接口，MyBatis的Cache接口是这样的：

public interface Cache {
    String getId();
    int getSize();
    void putObject(Object key, Object value);
    Object getObject(Object key);
    boolean hasKey(Object key);
    Object removeObject(Object key);
    void clear();
  }

实现Cache接口：

public class MyCustomCache implements Cache{
  // ...
}

2.2.cache-ref

引用其他命名空间的缓存配置。

<cache-ref namespace="com.wong.mybatis.SomeMapper"/>

public class User {
  //...
  public User(Integer id, String username, int age) {
  //...
  }
  //...
}

<constructor>
  <idArg column="id" javaType="int"/>
  <arg column="username" javaType="String"/>
  <arg column="age" javaType="_int"/>
</constructor>

<constructor>
  <idArg column="id" javaType="int" name="id" />
  <arg column="age" javaType="_int" name="age" />
  <arg column="username" javaType="String" name="username" />
</constructor>

<resultMap id="blogResult" type="Blog">
  <association property="author" column="author_id" javaType="Author" select="selectAuthor"/>
</resultMap>
<!--加载Author-->
<select id="selectAuthor" resultType="Author">
  SELECT * FROM AUTHOR WHERE ID = #{id}
</select>
<!--加载Blog-->
<select id="selectBlog" resultMap="blogResult">
  SELECT * FROM BLOG WHERE ID = #{id}
</select>

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author" resultMap="authorResult" />
</resultMap>




<resultMap id="authorResult" type="Author">
  <!--指定id可以提高性能，没有指定，MyBatis也能工作-->
  <id property="id" column="author_id"/>
  <result property="username" column="author_username"/>
  <result property="password" column="author_password"/>
  <result property="email" column="author_email"/>
  <result property="bio" column="author_bio"/>
</resultMap>




<select id="selectBlog" resultMap="blogResult">
  select
  B.id as blog_id,
  B.title as blog_title,
  B.author_id as blog_author_id,
  A.id as author_id,
  A.username as author_username,
  A.password as author_password,
  A.email as author_email,
  A.bio as author_bio
  from Blog B left outer join Author A on B.author_id = A.id
  where B.id = #{id}
</select>

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author" javaType="Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
    <result property="password" column="author_password"/>
    <result property="email" column="author_email"/>
    <result property="bio" column="author_bio"/>
  </association>
</resultMap>

（4）collection：复杂类型的集合。collection元素与association元素很像，但后者是解决“has-one"，前者是解决”has-many“这种关系。举个例子：

Blog有许多Posts（帖子），在Blog类里，帖子被定义列表：




private List<Post> posts;




那么resultＭap就应该这样写：
<collection property="posts" ofType="domain.blog.Post">
  <id property="id" column="post_id"/>
  <result property="subject" column="post_subject"/>
  <result property="body" column="post_body"/>
</collection>

与association元素一样，collection元素也有两种方式来处理结果集：

<resultMap id="blogResult" type="Blog">
  <collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="selectPostsForBlog"/>
</resultMap>




<select id="selectBlog" resultMap="blogResult">
  SELECT * FROM BLOG WHERE ID = #{id}
</select>
<select id="selectPostsForBlog" resultType="Post">
  SELECT * FROM POST WHERE BLOG_ID = #{id}
</select>

<select id="selectBlog" resultMap="blogResult">
  select
  B.id as blog_id,  
  B.title as blog_title,  
  B.author_id as blog_author_id,  
  P.id as post_id,  
  P.subject as post_subject,  
  P.body as post_body,  
  from Blog B  
  left outer join Post P on B.id = P.blog_id  
  where B.id = #{id}
</select>

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post">    
  <!--注意此id是可以提高性能的-->    
  <id property="id" column="post_id"/>    
  <result property="subject" column="post_subject"/>    
  <result property="body" column="post_body"/>  
  </collection>
 </resultMap>

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post" resultMap="blogPostResult" columnPrefix="post_"/>
</resultMap>




<resultMap id="blogPostResult" type="Post">
  <id property="id" column="id"/>
  <result property="subject" column="subject"/>
  <result property="body" column="body"/>
</resultMap>

collection处理多个resultSet的情况（调用存储过程）：

和前面association一样，我们可以调用一个存储过程来执行两个查询，并返回两个结果集，一个是Blogs的，别一个是Posts的：

SELECT * FROM BLOG WHERE ID = #{id}
SELECT * FROM POST WHERE BLOG_ID = #{id}

在xml映射文件中，使用resultSets指定每个结果集的名字，多个结果集名称之间用逗号隔开：

<select id="selectBlog" resultSets="blogs,posts" resultMap="blogResult">
  {call getBlogsAndPosts(#{id,jdbcType=INTEGER,mode=IN})}
</select>

将posts集合填充到对象：

<resultMap id="blogResult" type="Blog">
  <id property="id" column="id" />
  <result property="title" column="title"/>
  <collection property="posts" ofType="Post" resultSet="posts" column="id" foreignColumn="id">
    <id property="id" column="id"/>
    <result property="subject" column="subject"/>
    <result property="body" column="body"/>
  </collection>
</resultMap>

collection的相关属性：

2.4.sql

这个元素是用于定义一些可重用的SQL片段，这些片段可以被包含在其他的语句中。如定义以下SQL片段：

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

<select id="selectUsers" resultType="map">  select  <include refid="userColumns">    <property name="alias" value="t1"/>  </include>,  <include refid="userColumns">    <property name="alias" value="t2"/>  </include>  from some_table t1  cross join some_table t2</select>

属性值也可以用于include refid属性里，或者include子句内的属性值，如：

<sql id="sometable">
  ${prefix}Table
</sql>




<sql id="someinclude">
  from
  <include refid="${include_target}"/>
</sql>




<select id="select" resultType="map">
  select
  field1, field2, field3
  <include refid="someinclude">
    <property name="prefix" value="Some"/>
    <property name="include_target" value="sometable"/>
  </include>
</select>

2.6.select

<select
id="selectPerson"
parameterType="int"
parameterMap="deprecated"
resultType="hashmap"
resultMap="personResultMap"
flushCache="false"
useCache="true"
timeout="10"
fetchSize="256"
statementType="PREPARED"
resultSetType="FORWARD_ONLY">

select元素的属性：

select举列：

<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM PERSON WHERE ID = #{id}
</select>

2.7.参数类型

一般来说，我们可以通过parameterType指定参数类型，如果指定的类型与数据库中的类型不一样的话，MyBatis提供一种方式给我们指定参数类型，格式：

#{property,javaType=int,jdbcType=NUMERIC}

 javaType通常由参数对象决定，除非对象是一个HashMap。指定javaType可以确保使用正确的TypeHandler。jdbcType是 JDBC需要的，用于所有可以为空的列，如果null作为一个值传递的话，就应该指定jdbcType。

我们还可以指定TypeHandler类或别名：

#{age,javaType=int,jdbcType=NUMERIC,typeHandler=MyTypeHandler}

对于数字类型，有一个numericScale可以决定精确到小数点后几位：

#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}

mode模式属性可以是IN 、OUT、INOUT，如果是OUT或INOUT，那么参数对象属性的真实值会被改变。如果模式是OUT或INOUT ，并且jdbcType=CURSOR

，那么你必须指定一个resultMap来映射ResultSet到参数类型，此时javaType属性是可选的：

#{department, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=departmentRes}

MyBatis也支持高级数据类型，如结构体structs,但你必须告知语句类型名字：

#{middleInitial, mode=OUT, jdbcType=STRUCT, jdbcTypeName=MY_TYPE, resultMap=departmentRes}

大多数时候，我们都只简单地指定属性名，然后MyBatis就会帮我们搞掂剩下的这些。因此，我们只需要为可空的列指定jdbcType就可以了，如：

#{firstName}
#{middleInitial,jdbcType=VARCHAR}
#{lastName}

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

3.1

3.2

3.3

3.4

还可以把数据库连接的信息从application.yml文件中移到MyBatis的配置文件mybatis-mapper-config.xml中：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>




    <!-- 引用db.properties配置文件 -->
    <properties resource="db.properties"/>
    <!-- 
        development : 开发模式
        work : 工作模式
     -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="POOLED">
                <!-- value属性值引用db.properties配置文件中配置的值 -->
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${name}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    
</configuration>

4.1.if

<select id="findActiveBlogWithTitleLike" resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

再如：

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
      state = #{state}
    </if>
    <if test="title != null">
      AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
      AND author_name like #{author.name}
    </if>
  </where>
</select>

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  <trim prefix="WHERE" prefixOverrides="AND |OR ">
    <if test="state != null">
      state = #{state}
    </if>
    <if test="title != null">
      AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
      AND author_name like #{author.name}
    </if>
  </trim>
</select>

<update id="updateAuthorIfNecessary">
  update Author
  <set>
    <if test="username != null">username=#{username},</if>
    <if test="password != null">password=#{password},</if>
    <if test="email != null">email=#{email},</if>
    <if test="bio != null">bio=#{bio}</if>
  </set>
  where id=#{id}
</update>

<update id="updateAuthorIfNecessary">
  update Author
  <!--要插入set子句，如果后面的值是以“，”逗号开头，则要去掉先-->
  <trim prefix="SET" suffixOverrides=",">
    <if test="username != null">username=#{username},</if>
    <if test="password != null">password=#{password},</if>
    <if test="email != null">email=#{email},</if>
    <if test="bio != null">bio=#{bio}</if>
  </trim>
  where id=#{id}
</update>

@Update({"<script>",
"update Author",
" <set>",
"<if test='username != null'>username=#{username},</if>",
"<if test='password != null'>password=#{password},</if>",
"<if test='email != null'>email=#{email},</if>",
"<if test='bio != null'>bio=#{bio}</if>",
" </set>",
"where id=#{id}",
"</script>"})
void updateAuthorValues(Author author);

4.6.bind

bind元素可以让你在OGNL表达式之外，创建一个变量，并将其绑定到上下文中：

说白了就是连接多个数据库。如果有一个databaseIdProvider配置了“_databaseId"变量并且对于动态代码来说是可用的。那么，我们可以根据不同的数据库提供商来建不同的语句，如：

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
insert into users values (#{id}, #{name})
</insert>