MyBatis 简要指南

环境配置

略，见与 Spring Boot 的整合。

Hello World

每个MyBatis应用程序都以SqlSessionFactory的一个实例为中心。可以使用SqlSessionFactoryBuilder获取SqlSessionFactory实例。SqlSessionFactoryBuilder可以从XML配置文件或配置类的自定义准备实例构建SqlSessionFactory实例。

Building SqlSessionFactory with 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>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="org/mybatis/example/BlogMapper.xml"/>
  </mappers>
</configuration>

Acquiring a SqlSession from SqlSessionFactory：

String resource = "org/mybatis/example/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory =
  new SqlSessionFactoryBuilder().build(inputStream);

try (SqlSession session = sqlSessionFactory.openSession()) {
  BlogMapper mapper = session.getMapper(BlogMapper.class);
  Blog blog = mapper.selectBlog(101);
  // 或者
  Blog blog = session.selectOne(
  "org.mybatis.example.BlogMapper.selectBlog", 101);
}

Exploring Mapped SQL Statements：

<?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="org.mybatis.example.BlogMapper">
  <select id="selectBlog" resultType="Blog">
    select * from Blog where id = #{id}
  </select>
</mapper>

还可以使用 注解：

package org.mybatis.example;
public interface BlogMapper {
  @Select("SELECT * FROM blog WHERE id = #{id}")
  Blog selectBlog(int id);
}

对于简单的语句，注释要干净得多，但是对于更复杂的语句，Java注释既有限又混乱。因此，如果必须执行复杂的操作，最好使用XML映射语句。

• SqlSessionFactoryBuilder: 一旦创建了SqlSessionFactory，就没有必要保留它。
• SqlSessionFactory: 一旦创建，SqlSessionFactory应该在应用程序执行期间存在。应该很少或没有理由去处理或重新创建它。最好不要在应用程序运行时多次重建SqlSessionFactory。
• SqlSession: 每个线程都应该有自己的SqlSession实例。SqlSession的实例不能被共享，也不是线程安全的。因此，最好的范围是请求或方法范围。永远不要在静态字段甚至类的实例字段中保留对SqlSession实例的引用。永远不要在任何托管范围内保留对SqlSession的引用
• Mapper Instance: mapper实例的最佳作用域是方法作用域。也就是说，应该在使用它们的方法中请求它们，然后丢弃它们。它们不需要显式地关闭。

Configuration XML

当使用了 MyBatis Plus 之后，就可以在 application.propertites 中配置。

properties

当然这个 XML 本身里面，就可以引用 properties 的值，即外部化配置。

<!-- mybatis-config.xml -->
<properties resource="org/mybatis/example/config.properties">
  <property name="username" value="dev_user"/>  <!-- 重写 -->
  <property name="password" value="F2Fa3!33TYyg"/>
</properties>

<dataSource type="POOLED">
  <property name="driver" value="${driver}"/>
  <property name="url" value="${url}"/>
  <property name="username" value="${username}"/>
  <property name="password" value="${password}"/>
</dataSource>

The username and password in this example will be replaced by the values set in the properties elements. The driver and url properties would be replaced with values contained from the config.properties file.

可以指定默认值：

<properties resource="org/mybatis/example/config.properties">
  <!-- ... -->
  <property name="org.apache.ibatis.parsing.PropertyParser.enable-default-value" value="true"/> <!-- Enable this feature (default value) -->
</properties>

<dataSource type="POOLED">
  <!-- ... -->
  <property name="username" value="${username:ut_user}"/> <!-- If 'username' property not present, username become 'ut_user' 冒号可以换成自己定义的符号，防止冲突 -->
</dataSource>

settings

所有的设置都在 官方文档 列出来了。

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods"
    value="equals,clone,hashCode,toString"/>
</settings>

typeAliases

类型别名是Java类型的短名称。它只与XML配置相关，它的存在只是为了减少完全限定类名的冗余类型化。例如：

<typeAliases>
  <typeAlias alias="Author" type="domain.blog.Author"/>
  <typeAlias alias="Blog" type="domain.blog.Blog"/>
  <typeAlias alias="Comment" type="domain.blog.Comment"/>
  <typeAlias alias="Post" type="domain.blog.Post"/>
  <typeAlias alias="Section" type="domain.blog.Section"/>
  <typeAlias alias="Tag" type="domain.blog.Tag"/>
</typeAliases>

您还可以指定一个包，MyBatis将在其中搜索bean。例如：

<typeAliases>
  <package name="domain.blog"/>
</typeAliases>

Each bean found in domain.blog , if no annotation is found, will be registered as an alias using uncapitalized non-qualified class name of the bean. That is domain.blog.Author will be registered as author . If the @Alias annotation is found its value will be used as an alias. See the example below:

@Alias("author")
public class Author {
    ...
}

常见Java类型有许多内置的类型别名。它们都不区分大小写，请注意由于重载名称而对原语的特殊处理。

typeHandlers

Since version 3.4.5, MyBatis supports JSR-310 (Date and Time API) by default.

You can override the type handlers or create your own to deal with unsupported or non-standard types. To do so, implement the interface org.apache.ibatis.type.TypeHandler or extend the convenience class org.apache.ibatis.type.BaseTypeHandler and optionally map it to a JDBC type.

Handling Enums

default TypeHandlers：可见官网文件。

If you want to map an Enum , you'll need to use either EnumTypeHandler or EnumOrdinalTypeHandler .

默认情况下，MyBatis使用EnumTypeHandler将Enum值转换为它们的名称。

注意:EnumTypeHandler是特殊的，与其他处理程序不同，它不仅处理一个特定的类，而且还处理任何扩展Enum的类，但是我们可能不想存储名称。我们的DBA可能会坚持使用整数代码。这很简单：将EnumOrdinalTypeHandler添加到配置文件中的类型处理程序中，现在每个RoundingMode都将使用其序号值映射到一个整数。

<!-- mybatis-config.xml -->
<typeHandlers>
  <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler"
    javaType="java.math.RoundingMode"/>
</typeHandlers>

但是，如果您希望将相同的枚举映射到一个地方的字符串，并映射到另一个地方的整数，该怎么办呢？

auto-mapper将自动使用EnumOrdinalTypeHandler，因此如果我们想要回到使用普通的旧式普通的EnumTypeHandler，我们必须告诉它，方法是显式地设置类型处理程序来使用那些SQL语句。【示例，见官网，使用了 resultMap 】

objectFactory

每次MyBatis创建一个结果对象的新实例时，它都会使用一个ObjectFactory实例来完成这项工作。默认ObjectFactory仅使用默认构造函数实例化目标类，如果存在参数映射，则使用参数化构造函数。如果您想要覆盖ObjectFactory的默认行为，您可以创建自己的行为。【见官网】

plugins

MyBatis允许您在执行映射语句时拦截对特定点的调用。默认情况下，MyBatis允许插件拦截的方法调用。

• Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
• ParameterHandler (getParameterObject, setParameters)
• ResultSetHandler (handleResultSets, handleOutputParameters)
• StatementHandler (prepare, parameterize, batch, update, query)

// ExamplePlugin.java
@Intercepts({@Signature(
  type= Executor.class,
  method = "update",
  args = {MappedStatement.class,Object.class})})
public class ExamplePlugin implements Interceptor {
  private Properties properties = new Properties();

  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    // implement pre-processing if needed
    Object returnObject = invocation.proceed();
    // implement post-processing if needed
    return returnObject;
  }

  @Override
  public void setProperties(Properties properties) {
    this.properties = properties;
  }
}

<!-- mybatis-config.xml -->
<plugins>
  <plugin interceptor="org.mybatis.example.ExamplePlugin">
    <property name="someProperty" value="100"/>
  </plugin>
</plugins>

上面的插件将拦截对Executor实例上的“update”方法的所有调用，该实例是负责映射语句的低级执行的内部对象。

除了使用插件修改核心MyBatis行为外，您还可以完全覆盖配置类。不过，这可能会对MyBatis的行为产生严重影响，所以要谨慎使用。

environments

MyBatis可以配置多个环境。这有助于您出于各种原因将SQL映射应用到多个数据库。例如，您的开发、测试和生产环境可能有不同的配置。

While you can configure multiple environments, you can only choose ONE per SqlSessionFactory instance.

因此，如果希望连接两个数据库，需要创建SqlSessionFactory的两个实例，每个实例一个。对于三个数据库，您需要三个实例，等等。

要指定要构建的环境，只需将其作为一个可选参数传递给SqlSessionFactoryBuilder。

// 指定环境
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment, properties);

// 如果省略该环境，则加载默认环境
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, properties);

环境元素定义如何配置环境：

<environments default="development">
  <environment id="development">
    <transactionManager type="JDBC">
      <property name="..." value="..."/>
    </transactionManager>
    <dataSource type="POOLED">
      <property name="driver" value="${driver}"/>
      <property name="url" value="${url}"/>
      <property name="username" value="${username}"/>
      <property name="password" value="${password}"/>
    </dataSource>
  </environment>
</environments>

注意这里的关键部分：

• The default Environment ID (e.g. default="development").
• The Environment ID for each environment defined (e.g. id="development").
• The TransactionManager configuration (e.g. type="JDBC")
• The DataSource configuration (e.g. type="POOLED")

default Environment ID和Environment ID是自解释的。您可以随意命名它们，只要确保默认与其中一个匹配即可。

TransactionManager

There are two TransactionManager types (i.e. type="[JDBC|MANAGED]") that are included with MyBatis:

• JDBC – This configuration simply makes use of the JDBC commit and rollback facilities directly. It relies on the connection retrieved from the dataSource to manage the scope of the transaction.
• MANAGED – This configuration simply does almost nothing. It never commits, or rolls back a connection. Instead, it lets the container manage the full lifecycle of the transaction (e.g. a JEE Application Server context). By default it does close the connection. However, some containers don’t expect this, and thus if you need to stop it from closing the connection, set the "closeConnection" property to false.

NOTEIf you are planning to use MyBatis with Spring there is no need to configure any TransactionManager because the Spring module will set its own one overriding any previously set configuration.

dataSource

The dataSource element configures the source of JDBC Connection objects using the standard JDBC DataSource interface.

Most MyBatis applications will configure a dataSource as in the example. However, it’s not required. Realize though, that to facilitate Lazy Loading, this dataSource is required.

There are three built-in dataSource types (i.e. type="[UNPOOLED|POOLED|JNDI]"):

使用 DataSource pools 连接池与否。

UNPOOLED，不使用，每次连接每次关闭和打开。

POOLED，使用。This is a popular approach for concurrent web applications to achieve the fastest response.

JNDI，for use with containers such as EJB or Application Servers that may configure the DataSource centrally or externally and place a reference to it in a JNDI context.

You can plug any 3rd party DataSource by implementing the interface org.apache.ibatis.datasource.DataSourceFactory 。

databaseIdProvider

MyBatis能够根据您的数据库供应商执行不同的语句。多数据库供应商的支持基于映射语句databaseId属性。MyBatis将加载所有没有databaseId属性或具有与当前属性匹配的databaseId的语句。

<databaseIdProvider type="DB_VENDOR">
  <property name="SQL Server" value="sqlserver"/>
  <property name="DB2" value="db2"/>
  <property name="Oracle" value="oracle" />
</databaseIdProvider>

mappers

we’re ready to define our mapped SQL statements. But first, we need to tell MyBatis where to find them. Java doesn’t really provide any good means of auto-discovery in this regard, so the best way to do it is to simply tell MyBatis where to find the mapping files. You can use classpath relative resource references, fully qualified url references (including file:/// URLs), class names or package names. For example:

<!-- Using classpath relative resources -->
<mappers>
  <mapper resource="org/mybatis/builder/AuthorMapper.xml"/>
  <mapper resource="org/mybatis/builder/BlogMapper.xml"/>
  <mapper resource="org/mybatis/builder/PostMapper.xml"/>
</mappers>
<!-- Using url fully qualified paths -->
<mappers>
  <mapper url="file:///var/mappers/AuthorMapper.xml"/>
  <mapper url="file:///var/mappers/BlogMapper.xml"/>
  <mapper url="file:///var/mappers/PostMapper.xml"/>
</mappers>
<!-- Using mapper interface classes -->
<mappers>
  <mapper class="org.mybatis.builder.AuthorMapper"/>
  <mapper class="org.mybatis.builder.BlogMapper"/>
  <mapper class="org.mybatis.builder.PostMapper"/>
</mappers>
<!-- Register all interfaces in a package as mappers -->
<mappers>
  <package name="org.mybatis.builder"/>
</mappers>

Mapper XML Files

The true power of MyBatis is in the Mapped Statements.

The Mapper XML files have only a few first class elements (in the order that they should be defined):

• cache – Configuration of the cache for a given namespace.
• cache-ref – Reference to a cache configuration from another namespace.
• resultMap – The most complicated and powerful element that describes how to load your objects from the database result sets.
• sql – A reusable chunk of SQL that can be referenced by other statements.
• insert – A mapped INSERT statement.
• update – A mapped UPDATE statement.
• delete – A mapped DELETE statement.
• select – A mapped SELECT statement.

select

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

This statement is called selectPerson, takes a parameter of type int (or Integer), and returns a HashMap keyed by column names mapped to row values.

<select
  id="selectPerson"
  parameterType="int 可省略，可全限定" 
  resultType="hashmap 若是集合，则应是集合包含的元素的类型而非集合本身 【二选一】"
  resultMap="personResultMap 【二选一】"
  flushCache="false"
  useCache="true"
  timeout="10 unset"
  fetchSize="256 默认 unset（driver dependent）"
  statementType="PREPARED"
  resultSetType="FORWARD_ONLY unset"
  databaseId=""
  resultOrdered="false  for nested result"
  resultSets="for multiple result sets(多个结果集，逗号隔开)">

具体解释，还看官网。

Result maps are the most powerful feature of MyBatis.

insert, update and delete

<insert
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20"
  useGeneratedKeys="false (insert and update only) "
  keyProperty="自动生成属性，可用逗号分隔多个(insert and update only) "
  keyColumn="自动生成属性对应表的列，当表的第一列不是key时，可用逗号分隔多个(insert and update only) "
  databaseId="">

<update
  id="updateAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<delete
  id="deleteAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

示例（多行插入）：

<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username, password, email, bio) values
  <foreach item="item" collection="list" separator=",">
    (#{item.username}, #{item.password}, #{item.email}, #{item.bio})
  </foreach>
</insert>

示例（可定制生成key）：

<insert id="insertAuthor">
  <selectKey keyProperty="id" 
             resultType="int 可选，MyBatis can usually figure this out" 
             order="BEFORE" 
             statementType="PREPARED">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
  insert into Author
    (id, username, password, email,bio, favourite_section)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
</insert>

sql

This element can be used to define a reusable fragment of SQL code that can be included in other statements. It can be statically (during load phase) parametrized.

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

patameters

<insert id="insertUser" parameterType="User">
  insert into users (id, username, password)
  values (#{id}, #{username}, #{password})
</insert>

If a parameter object of type User was passed into that statement, the id, username and password property would be looked up and their values passed to a PreparedStatement parameter.

与MyBatis的其他部分一样，javaType几乎总是可以从参数对象中确定，除非该对象是HashMap。

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

尽管有这些强大的选项，但大多数情况下，您只需指定属性名，MyBatis就会找出其余的选项。最多，您将为可空列指定jdbcType。

String Substitution

By default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject an unmodified string into the SQL Statement.

For example, for ORDER BY, you might use something like this: ORDER BY ${columnName}

String Substitution can be very useful when the metadata(i.e. table name or column name) in the sql statement is dynamic, for example:

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

而不是：

@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);

@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);

@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);

// and more "findByXxx" method

这样，直接调用：

User userOfId1 = userMapper.findByColumn("id", 1L);
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");

注意，接受来自用户的输入并将其提供给未以这种方式修改的语句是不安全的。这将导致潜在的SQL注入攻击，因此您应该不允许用户在这些字段中输入，或者始终执行自己的转义和检查。

Result Maps

resultMap元素是MyBatis中最重要、最强大的元素。

虽然HashMap在很多情况下都很有用，但是它并不是一个很好的域模型。您的应用程序将使用javabean或pojo(普通的旧Java对象)作为域模型。

Such a JavaBean could be mapped to a ResultSet just as easily as the HashMap .

<select id="selectUsers" resultType="com.someapp.model.User">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

记住，类型别名是你的朋友。使用它们，这样您就不必一直键入类的完全限定路径。例如:

<!-- In Config XML file -->
<typeAlias type="com.someapp.model.User" alias="User"/>

<!-- In SQL Mapping XML file -->
<select id="selectUsers" resultType="User">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

在这些情况下，MyBatis会在后台自动创建ResultMap，以便根据名称将列自动映射到JavaBean属性。

作为外部resultMap是什么样子的，因为这是解决列名不匹配的另一种方法。

<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="username" column="user_name"/>
  <result property="password" column="hashed_password"/>
</resultMap>

<select id="selectUsers" resultMap="userResultMap">
  select user_id, user_name, hashed_password
  from some_table
  where id = #{id}
</select>

Advanced Result Map

<!-- Very Complex Result Map -->
<resultMap id="detailedBlogResultMap" type="Blog">
  <constructor>
    <idArg column="blog_id" javaType="int"/>
  </constructor>
  <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"/>
    <result property="favouriteSection" column="author_favourite_section"/>
  </association>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <association property="author" javaType="Author"/>
    <collection property="comments" ofType="Comment">
      <id property="id" column="comment_id"/>
    </collection>
    <collection property="tags" ofType="Tag" >
      <id property="id" column="tag_id"/>
    </collection>
    <discriminator javaType="int" column="draft">
      <case value="1" resultType="DraftPost"/>
    </discriminator>
  </collection>
</resultMap>

resultMap 元素有很多子元素：

• constructor

  - used for injecting results into the constructor of a class upon instantiation

  idArg
  arg
  

• id – an ID result; flagging results as ID will help improve overall performance

• result – a normal result injected into a field or JavaBean property

• association

  – a complex type association; many results will roll up into this type

  • nested result mappings – associations are resultMap s themselves, or can refer to one

• collection

  – a collection of complex types

  • nested result mappings – collections are resultMap s themselves, or can refer to one

• discriminator

  – uses a result value to determine which resultMap to use

  • case

    – a case is a result map based on some value

    • nested result mappings – a case is also a result map itself, and thus can contain many of these same elements, or it can refer to an external resultMap.

id 和 result：这些是最基本的结果映射。id和结果都将单个列值映射到简单数据类型(String、int、double、Date等)的单个属性或字段。两者之间的惟一区别是，id将把结果标记为标识符属性，以便在比较对象实例时使用。这有助于提高总体性能，特别是缓存和嵌套结果映射(即连接映射)的性能。

javaType，MyBatis can usually figure out the type if you're mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behaviour.

The JDBC type is only required for nullable columns upon insert, update or delete.

Supported JDBC Types

Java types：

通常，包含很少或从不更改的引用或查找数据的表适合于不可变类。构造函数注入允许您在实例化时设置类的值，而无需公开公共方法。当您处理具有许多参数的构造函数时，维护arg元素的顺序很容易出错。从3.4.3开始，通过指定每个参数的名称，您可以按任何顺序编写arg元素。

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

javaType can be omitted if there is a property with the same name and type.

The association element deals with a "has-one" type relationship.

Where the association differs is that you need to tell MyBatis how to load the association. MyBatis can do so in two different ways:

• Nested Select: By executing another mapped SQL statement that returns the complex type desired.
• Nested Results: By using nested result mappings to deal with repeating subsets of joined results.

非常重要:id元素在嵌套结果映射中扮演非常重要的角色。您应该始终指定一个或多个可用于唯一标识结果的属性。事实是，如果您不使用MyBatis，它仍然可以工作，但是要付出严重的性能代价。选择尽可能少的能够唯一标识结果的属性。主键是一个显而易见的选择(即使是组合键)。

示例：

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

<!-- 内部化 -->
<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>

<!-- 在连接多个表时，必须使用列别名来避免在ResultSet中出现重复的列名。 -->
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author"
    resultMap="authorResult" />
  <association property="coAuthor"
    resultMap="authorResult"
    columnPrefix="co_" />
</resultMap>

The collection element deals with a "has-many" type relationship.

集合元素的工作方式几乎与关联相同。

新的“ofType”属性。这个属性对于区分JavaBean(或field)属性类型和集合包含的类型是必要的：

<resultMap id="blogResult" type="Blog">
  <collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="selectPostsForBlog"/>  <!-- Read as: "A collection of posts in an ArrayList of type Post." javaType属性实际上是不必要的，因为MyBatis会在大多数情况下为您解决这个问题。 -->
</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>

示例 Nested Results for Collection:

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post">
    <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>

示例 Multiple ResultSets for Collection:

SELECT * FROM BLOG WHERE ID = #{id}

SELECT * FROM POST WHERE BLOG_ID = #{id}

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

<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="blog_id">
    <id property="id" column="id"/>
    <result property="subject" column="subject"/>
    <result property="body" column="body"/>
  </collection>
</resultMap>

有时，单个数据库查询可能返回许多不同(但可能有些相关)数据类型的结果集。discriminator元素被设计来处理这种情况和其他情况，包括类继承层次结构。识别器非常容易理解，因为它的行为很像Java中的switch语句。

<resultMap id="vehicleResult" type="Vehicle">
  <id property="id" column="id" />
  <result property="vin" column="vin"/>
  <result property="year" column="year"/>
  <result property="make" column="make"/>
  <result property="model" column="model"/>
  <result property="color" column="color"/>
  <discriminator javaType="int" column="vehicle_type">
    <case value="1" resultMap="carResult"/>
    <case value="2" resultMap="truckResult"/>
    <case value="3" resultMap="vanResult"/>
    <case value="4" resultMap="suvResult"/>
  </discriminator>
</resultMap>

<resultMap id="carResult" type="Car">
  <result property="doorCount" column="door_count" />
</resultMap>

<resultMap id="carResult" type="Car" extends="vehicleResult">
  <result property="doorCount" column="door_count" />
</resultMap>

Auto-mapping

在简单的情况下，MyBatis可以为您自动映射结果，而在其他情况下，您需要构建结果映射。

当自动映射结果时，MyBatis将获得列名并查找具有相同名称的属性，忽略大小写。

通常，数据库列使用大写字母和单词之间的下划线进行命名，而java属性通常遵循camelcase命名约定。要启用它们之间的自动映射，请将 mapUnderscoreToCamelCase 设置为true。

即使存在特定的结果映射，自动映射也可以工作。当这种情况发生时，对于每个结果映射，ResultSet中所有没有手动映射的列都将被自动映射，然后手动映射将被处理。在下面的示例中，将自动映射id和用户名列，并映射散列密码列。

<select id="selectUsers" resultMap="userResultMap">
  select
    user_id             as "id",
    user_name           as "userName",
    hashed_password
  from some_table
  where id = #{id}
</select>

<resultMap id="userResultMap" type="User">
  <result property="password" column="hashed_password"/>
</resultMap>

Dynamic SQL

if

在动态SQL中最常见的做法是有条件地包含where子句的一部分。例如

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

choose, when, otherwise

有时，我们不希望应用所有条件，而是希望在许多选项中只选择一种情况。类似于Java中的switch语句，MyBatis提供了一个choose元素。

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

trim, where, set

前面的例子很方便地避开了一个众所周知的动态SQL挑战。

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

where元素只知道在包含标记返回任何内容时插入“where”。此外，如果内容以“AND”或“or”开头，它知道如何去掉它。

如果where元素的行为与您希望的不完全一样，您可以通过定义自己的trim元素对其进行自定义。例如，与where元素等价的trim:

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

prefixOverrides属性接受要重写的以管道分隔的文本列表，其中空格是相关的。结果是删除了prefixOverrides属性中指定的任何内容，并插入了prefix属性中的任何内容。

对于动态更新语句有一个类似的解决方案，称为set。set元素可以用来动态地包括要更新的列，而不包括其他的列。例如

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

在这里，set元素将动态地添加set关键字，并消除任何可能在应用条件后跟踪值分配的无关逗号。

与之等价的 trim 为：

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

foreach

动态SQL的另一个常见需求是需要迭代一个集合，通常是为了构建一个IN条件。例如

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

foreach元素非常强大，它允许您指定一个集合、声明项和索引变量，这些变量可以在元素的主体中使用。它还允许您指定打开和关闭字符串，并在迭代之间添加分隔符。

script

要在带注解的mapper类中使用动态SQL，可以使用script元素。例如

@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);

bind

bind元素允许您从OGNL表达式中创建一个变量并将其绑定到上下文。例如

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

Multi-db vendor support

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

Pluggable Scripting Languages For Dynamic SQL

从3.2版开始，MyBatis就支持可插拔的脚本语言，因此可以插入语言驱动程序并使用该语言编写动态SQL查询。

Java API

MyBatis 3引入了许多重要的改进，使使用SQL映射变得更好。

从一开始，MyBatis就是一个XML驱动的框架。配置是基于XML的，映射语句是在XML中定义的。有了MyBatis 3，就有了新的选择。MyBatis 3构建在一个全面而强大的基于Java的配置API之上。这个配置API是基于XML的MyBatis配置的基础，也是新的基于注解的配置的基础。注解提供了一种简单的方法来实现简单的映射语句，而不需要引入大量的开销。

Logging

MyBatis通过使用内部日志工厂提供日志信息。

MyBtis 官方：Configuration XML

MyBtis 官方：Mapper XML FIles

MyBatis 官方：Dynamic SQL