MyBatis XML mapping file

Mapper XML file

The real power of MyBatis lies in its mapping statements and its magic. B ecause of its unusually powerful, the maper's XML files are relatively simple. I f you compare it to JDBC code with the same functionality, you'll immediately find that nearly 95% of the code is saved. MyBatis is built for SQL and does better than the normal approach.

SQL mapping files have very few top-level elements (in the order in which they should be defined):

• cache - Cache configuration for a given namespace.
• cache-ref a reference to other namespace cache configurations.
• resultMap the most complex and powerful element that describes how objects are loaded from a database result set.
• sql - A re-useable statement block that can be referenced by other statements.
• insert - Map insert statements
• update - Map update statement
• delete - Map delete statements
• select - Map query statements

The next section describes the details of each element, starting with the statement itself.

select

Query statements are one of the most commonly used elements in MyBatis, and the value of just keeping data in the database is not great, and if it can be re-extracted, most applications are more frequent than queries. F or each insert, update, or delete operation, there are usually multiple query operations. T his is one of MyBatis's fundamental principles and the reason for focusing and effort on queries and result mapping. T he select element of a simple query is very simple. Like what:

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

This statement, called selectPerson, accepts an int (or Integer) type of argument and returns an object of hashMap type, where the key is the column name and the value is the corresponding value in the result row.

Note the parameter symbol:

#{id}

This tells MyBatis to create a preprocessed statement argument that, through JDBC, is identified in SQL by a "?" and passed to a new preprocessed statement, as this is:

    // Similar JDBC code, NOT MyBatis…
    String selectPerson = "SELECT * FROM PERSON WHERE ID=?";
    PreparedStatement ps = conn.prepareStatement(selectPerson);
    ps.setInt(1,id);

Of course, this requires a lot of separate JDBC code to extract the results and map them to the object instance, which is where MyBatis saves you time. We need to dig deeper into parameters and result mapping, details of which we'll look at below.

Select elements have many properties that allow you to configure to determine the details of the role of each statement.

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

Select Attributes

Property	Describe
Id	A unique identifier in the namespace that can be used to refer to this statement.
parameterType	A fully qualified or alias of the argument class that will pass in this statement. This property is optional because MyBatis can infer parameters for specific incoming statements through TypeHandler, with the default value being unset.
resultType	A fully qualified or alias for a class of the expected type returned from this statement. N ote In the case of a collection, it should be the type that the collection can contain, not the collection itself. Use resultType or resultMap, but not at the same time.
resultMap	A named reference to the outer resultMap. T he mapping of the result set is the most powerful feature of MyBatis, and with a good understanding of it, many complex mapping scenarios can be solved. Use resultMap or resultType, but not at the same time.
flushCache	Set it to true, and whenever a statement is called, the local cache and the secondary cache are emptied, the default: false.
useCache	Setting it to true will result in the result of this statement being cached at the secondary level, with the default: true for the select element.
timeout	This setting is the number of seconds the driver waits for the database to return the requested result before throwing the exception. The default is unset (dependency-driven).
fetchSize	This is an attempt to affect the number of resulting rows returned by the driver each time in bulk and this setting value is equal. The default is unset (dependency-driven).
statementType	STATEMENT, PREPARED or CALLABLE one. This allows MyBatis to use Statement, PreparedState, or CallableState, respectively, with the default: PREPARED.
resultSetType	FORWARD_ONLY, SCROLL_SENSITIVE one SCROLL_INSENSITIVE, the default value is unset (dependency-driven).
databaseId	If databaseIdProvider is configured, MyBatis loads all statements without databaseId or matching the current databaseId;
resultOrdered	This setting applies only to nested result select statements: if true, it assumes that the nested result set or grouping is included, so that when a main result row is returned, there is no reference to the previous result set. T his allows you to get nested result sets without running out of memory. Default: false.
resultSets	This setting applies only to multi-result sets, which list the result sets returned after the statement is executed and give each result set a name separated by a comma.

insert, update, and delete

The implementation of the data change statement insert, update, and delete is very close:

<insert
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<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, Update, and Delete properties

Property	Describe
Id	A unique identifier in a namespace that can be used to represent this statement.
parameterType	A fully qualified class name or alias for the argument to be passed in. This property is optional because MyBatis can infer parameters for specific incoming statements through TypeHandler, with the default value being unset.
flushCache	Set it to true, which causes the local cache and the secondary cache to be emptied whenever a statement is called, with the default: true (corresponding to insert, update, and delete statements).
timeout	This setting is the number of seconds the driver waits for the database to return the requested result before throwing the exception. The default is unset (dependency-driven).
statementType	STATEMENT, PREPARED or CALLABLE one. This allows MyBatis to use Statement, PreparedState, or CallableState, respectively, with the default: PREPARED.
useGeneratedKeys	This (useful only for insert and update) allows MyBatis to use JDBC's getGeneratedKeys method to remove the primary key generated from within the database (for example, auto-increment fields for relationship database management systems such as MySQL and SQL Server), the default: false.
keyProperty	Uniquely tagged a property (useful only for insert and update), MyBatis sets its key value either through the return value of getGeneratedKeys or through the selectKey child element of the insert statement, by default: unset. If you want to get more than one generated column, you can also have a comma-separated list of property names.
keyColumn	(useful only for insert and update) set the column name in the table by generating key values, which are only required in some databases (like PostgreSQL) and need to be set when the primary key column is not the first column in the table. If you want to get more than one generated column, you can also have a comma-separated list of property names.
databaseId	If databaseIdProvider is configured, MyBatis loads all statements without databaseId or matching the current databaseId;

Here's an example of insert, update, and delete statements:

<insert id="insertAuthor">
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

<update id="updateAuthor">
  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>

<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>

As mentioned earlier, the configuration rules for inserting statements are richer, with additional properties and child elements in the insert statement to handle the generation of primary keys, and there are several ways to build them.

First, if your database supports fields that automatically generate primary keys, such as MySQL and SQL Server, you can set useGeneratedKeys to "true" and then set keyProperty to the target property. For example, if the Author table above already uses an auto-generated column type for id, the statement can be modified to:

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

If your database also supports multi-row insertion, you can also pass in an Autos array or collection and return the auto-generated primary key.

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

In the example above, the selectKey element will run first, author's id will be set, and then the insert statement will be called. This gives you a behavior similar to that of the database to handle automatically generated primary keys, avoiding complicating Java code.

The selectKey element is described as follows:

<selectKey
  keyProperty="id"
  resultType="int"
  order="BEFORE"
  statementType="PREPARED">

SelectKey's property

Property	Describe
keyProperty	The selectKey statement result should be set by the target property. If you want to get more than one generated column, you can also have a comma-separated list of property names.
keyColumn	The name of the column in the return result set that matches the property. If you want to get more than one generated column, you can also have a comma-separated list of property names.
resultType	The type of result. M yBatis can usually be worked out, but there's nothing wrong with writing it for more certainty. M yBatis allows any simple type to be used as a primary key type, including strings. If you want to act on more than one generated column, you can use an Object or a Map that contains the desired properties.
order	This can be set to BEFORE or AFTER. I f set to BEFORE, it first selects the primary key, sets keyProperty, and then executes the insert statement. If set to AFTER, the insert statement is executed first, followed by the selectKey element - similar to a database like Oracle, where there may be embedded index calls inside the insert statement.
statementType	As before, MyBatis supports the mapping types of STATEMENT, PREPARED, and CALLABLE statements, representing the PreparedState and CallableState types, respectively.

Sql

This element can be used to define reusable SQL snippppy and can be included in other statements. I t can be parameterized statically (during the loading phase). D ifferent property values can vary in the include instance. Like what:

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

This SQL fragment can be included in other statements, such as:

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

Property values can also be used for property values in the include refid property or in the include clause, such as:

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

Parameters

What you see in all the previous statements is an example of a simple parameter, which is actually a very powerful element of MyBatis, and for a simple approach, about 90% of the case parameters are very few, such as:

<select id="selectUsers" resultType="User">
  select id, username, password
  from users
  where id = #{id}
</select>

The example above illustrates a very simple named parameter mapping. T he argument type is set to User so that it can be set to anything. N ative or simple data types, such as integers and strings, because there are no related properties, it is completely replaced with parameter values. H owever, if you pass in a complex object, the behavior will be a little different. Like what:

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

If parameter objects of the User type are passed to a statement, the id, username, and password properties are looked up and their values are passed into the parameters of the preprocessed statement.

This is good and simple for passing parameters to a statement, but parameter mapping doesn't do much more than that.

First, like the rest of MyBatis, parameters can specify a special data type.

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

Like the rest of MyBatis, javaType can usually be determined from the parameter object, as long as the object is not a HashMap. Then javaType should be determined to ensure that the correct type of processor is used.

NOTE If null is passed as a value, JDBC Type is required for all columns that may be empty. You can study this situation yourself by reading the JavaDocs documentation for the setNull() method of the preprocessed statement.

To customize type handling later, you can also specify a special type processor class (or alias), such as:

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

Although it may seem increasingly cumbersome to configure, it is actually rare to set them up.

For numeric types, there is also a decimal reserved digit setting to determine the number of digits retained after the decimal point.

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

Finally, the mode property allows you to specify IN, OUT, or INOUT parameters. I f the argument is OUT or INOUT, the true value of the parameter object property will be changed, as you would expect when you get the output parameter. I f mode is OUT (or INOUT) and jdbcType is CURSOR (that is, Oracle's REFCURSOR), you must specify a resultMap to map the result set to the parameter type. Note that the javaType property here is optional, and if the white space on the left is the CURSOR type of jdbcType, it is automatically set to the result set.

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

MyBatis also supports many advanced data types, such as structures, but when you register the out parameter you must tell it the statement type name. For example (again, in practice, you can't line up like this):

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

Although all of these powerful options are often simply assigned property names, other things MyBatis will infer for itself that at most you need to specify jdbcType

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

String replacement

By default, using the syntax in the format of ? causes MyBatis to create preprocessed statement properties and safely set values, such as ?. T his is safer, faster, and usually preferred, but sometimes you just want to insert an unchanged string directly into the SQL statement. For example, like ORDER BY, you can use this:

ORDER BY ${columnName}

MyBatis does not modify or escape strings here.

NOTE accepts content from the user in this way and provides the same string in the statement as unsafe, resulting in a potential SQL injection attack, so either the user is not allowed to enter these fields or escapes and verifys itself.

Result Maps

The resultMap element is the most important and powerful element in MyBatis. I t's the one that keeps you away from 90% of the JDBC code that needs to take data out of the result set, and in some cases allows you to do things that JDBC doesn't support. I n fact, writing code that is similar to combining complex statements to map these equivalents may cross thousands of lines of code. ResultMap is designed so that simple statements do not require explicit result mapping, and many complex statements do need to describe their relationships.

You've seen an example of a simple mapping statement, but there's no clear resultMap. Like what:

<select id="selectUsers" resultType="map">
  select id, username, hashedPassword
  from some_table
  where id = #{id}
</select>

Such a statement simply functions on the key where all columns are automatically mapped to HashMap, which is specified by resultType property. T his is useful in many cases, but HashMap does not describe a domain model very well. Y our program will then use JavaBeans or POJOs (Plain Old Java Objects, normal Java objects) as the domain model. M yBatis supports both. Take a look at this JavaBean:

    package com.someapp.model;
    public class User {
      private int id;
      private String username;
      private String hashedPassword;

      public int getId() {
        return id;
      }
      public void setId(int id) {
        this.id = id;
      }
      public String getUsername() {
        return username;
      }
      public void setUsername(String username) {
        this.username = username;
      }
      public String getHashedPassword() {
        return hashedPassword;
      }
      public void setHashedPassword(String hashedPassword) {
        this.hashedPassword = hashedPassword;
      }
    }

Based on the JavaBean specification, the above class has three id username hashedPassword These are precisely matched to the column name in the select statement.

Such a JavaBean can be mapped to the result set as easily as it is to HashMap.

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

Remember that the type alias is your partner. U se them so that you don't have to enter the full path of the class. Like what:

<!-- In mybatis-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>

In these cases, MyBatis automatically creates a ResultMap behind the scenes that maps the columns to JavaBean properties based on the property name. I f the column names don't exactly match, you can match the labels on the column names using the alias of the select word (a basic SQL attribute). Like what:

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

The best parts of ResultMap you've learned a lot about, but you haven't really seen one yet. T hese simple examples don't need to be more than you can see. Just for example reasons, let's look at what the outer resultMap looks like in the last example, which is another way to resolve column name mismatches.

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

The statement that refers to it uses the resultMap property (note that we removed the resultType property). Like what:

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

If only the world were always that simple.

Advanced result mapping

MyBatis created the idea that databases don't always be what you want or need. O ur favorite databases are preferably the third paradigm or BCNF mode, but they are sometimes not. I f there might be a separate database map, all applications can use it, which is great, but sometimes it's not. Result mapping is the answer myBatis provides to address this question.

For example, how do we map the following statement?

<!-- Very Complex Statement -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
  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,
       A.favourite_section as author_favourite_section,
       P.id as post_id,
       P.blog_id as post_blog_id,
       P.author_id as post_author_id,
       P.created_on as post_created_on,
       P.section as post_section,
       P.subject as post_subject,
       P.draft as draft,
       P.body as post_body,
       C.id as comment_id,
       C.post_id as comment_post_id,
       C.name as comment_name,
       C.comment as comment_text,
       T.id as tag_id,
       T.name as tag_name
  from Blog B
       left outer join Author A on B.author_id = A.id
       left outer join Post P on B.id = P.blog_id
       left outer join Comment C on P.id = C.post_id
       left outer join Post_Tag PT on PT.post_id = P.id
       left outer join Tag T on PT.tag_id = T.id
  where B.id = #{id}
</select>

You might want to map it to a smart object model that contains a blog written by an author, lots of blog posts, zero or more comments and tags per blog post. B elow is a complete example of complex result mapping (assuming authors, blogs, blog posts, comments and tags are all alias of type) and let's take a look. B ut don't be nervous, we'll explain step by step. At first it looked daunting, but it was actually very simple.

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

The resultMap element has many child elements and a structure worth discussing. The following is a conceptual view of the resultMap element

resultMap

• constructor - The class is used to inject results into the construction method when instantiated
  • idArg - ID parameters; Marking results as IDs can help improve overall performance
  • arg - injects a normal result into the construction method
• id - an ID result; Marking results as IDs can help improve overall performance
• result - The normal result injected into a field or JavaBean property
• association - a complex type association; M any results will be wrapped into this type
  • Embedding the result map - the association of the result map itself, or refer to one
• collection - a set of complex types
  • Embedding the result map - the set of the result map itself, or refer to one
• discriminator Use the result value to decide which result map to use
  • case Result mapping based on certain values
    • Embedding result mapping - The result of this scenario also maps itself, so it can contain many similar elements, or it can refer to an external result map.

ResultMap Attributes

Property	Describe
id	A unique identifier in this namespace that can be used to reference this result map.
type	A completely specific Java class name, or a type alias (see the list of built-in type aliases in the table above).
autoMapping	If present, MyBatis will enable or disable the automatic operation of this ResultMap. T his property autoMappingBehavior Default: Not set.

Best practices Typically step-by-step result mapping. T he real help of unit testing is here. I f you try to create a huge result map like the one above at a time, there may be errors and it is difficult to control it to work. S tart simpler, step by step. A nd unit tests! T he disadvantage of using this framework is that they are sometimes black boxes (whether source code is visible). Y ou determine that the best option for you to implement the desired behavior is to write unit tests. It can also help you get an error when submitting.

The following section details each element.

id & result

<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>

These are the most basic elements of the result map. both id and result map the values of a separate column to individual properties or fields for simple data types (character strings, integers, double floats, dates, etc.).

The only difference between the two is that the result of the id represents will be the identity properties that are used when comparing object instances. This helps improve overall performance, especially caching and embedding result mapping (i.e., federation mapping).

Each has some properties:

​ Id and Result Attributes ​

Property	Describe
property	A field or property that maps to a column result. I f the properties of JavaBeans that match exist with the same name as the given name are used. O therwise MyBatis will look for a field with a given name property. I n both cases, you can navigate with complex properties that are typically point-based. For example, you can map something like this: "username" or something complicated: "address.street.number"
column	The column name obtained from the database, or the renamed label of the column name. This is also the same string resultSet.getString(columnName) method parameter.
javaType	A fully qualified name for a Java class, or a type alias (refer to the list of built-in type alias above). I f you map to a JavaBean, MyBatis can usually determine the type. However, if you map to HashMap, you should explicitly specify javaType to guarantee the desired behavior.
jdbcType	The type in the list of JDBC types supported after this table. T he JDBC type is only for columns that need to be inserted, updated, and deleted that may be empty. T his is what JDBC jdbcType needs, not MyBatis. If you program directly with JDBC, you need to specify this type - but only for values that may be empty.
typeHandler	We discussed the default type processor earlier. W ith this property, you can override the type processor that is recognized. This property value is either a fully qualified name for the class or an implementation of a type handler, or a type alias.

The type of JDBC supported

For future reference, MyBatis supports the following JDBC type by including the jdbcType enumeraled type.

BIT	FLOAT	CHAR	TIMESTAMP	OTHER	UNDEFINED
TINYINT	REAL	VARCHAR	BINARY	BLOG	NVARCHAR
SMALLINT	DOUBLE	LONGVARCHAR	VARBINARY	CLOB	NCHAR
INTEGER	NUMERIC	DATE	LONGVARBINARY	BOOLEAN	NCLOB
BIGINT	DECIMAL	TIME	NULL	CURSOR	ARRAY

The construction method

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

For most data transfer object (Data Transfer Object, DTO) types, properties can work, and like most of your domain models, instructions may be where you want to use a static class. T ables that typically contain references or query data with little or no change are appropriate for a static class. C onstruct method injection allows you to set the value of a property for a class at initialization without exposing public methods. M yBatis also supports private properties and private JavaBeans genus to do this, but some prefer construction method injection. The construction method element supports this.

Take a look at this construction method:

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

In order to inject results into this construction method, MyBatis needs to identify the construction method by the type of its arguments. J ava does not have a method for self-checking (reflection) parameter names. So when you create a construction method element, make sure that the parameters are sequential and that the data type is determined.

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

The remaining properties and rules are the same as the fixed id and result elements.

Property	Describe
column	The class name from the database, or the renamed column label. This is the same string resultSet.getString(columnName) method.
javaType	A fully qualified name for a Java class, or a type alias (refer to the list of built-in type alias above). I f you map to a JavaBean, MyBatis can usually determine the type. However, if you map to HashMap, you should explicitly specify javaType to guarantee the desired behavior.
jdbcType	The type in the list of JDBC types supported before this table. T he JDBC type is simply a column that needs to be processed for insertion, update, and delete operations that may be empty. T his is what JDBC needs, jdbcType instead of MyBatis. If you program directly with JDBC, you need to specify this type - but only for values that may be empty.
typeHandler	We discussed the default type processor earlier. W ith this property, you can override the default type processor. This property value is either a fully qualified name for a class or an implementation of a type processor, or a type alias.
select	The ID of another mapping statement that loads the complex types required for this property mapping. T he value retrieved from the column specified in the column property is passed as an argument to the target select statement. For more information, see Associated Elements.
resultMap	This is the ID of ResultMap, which maps the nested results of this parameter to the appropriate object diagram. T his is an alternative to calling another select statement. I t allows you to connect multiple tables to a single result set. S uch a DesignSet would contain duplicate, duplicate data sets that need to be decomposed and correctly mapped to nested object diagrams. T o achieve this, MyBatis allows results to be mapped "chained" together to handle nested results. For more information, see the associated elements below.

关联

<association property="author" column="blog_author_id" javaType="Author">
  <id property="id" column="author_id"/>
  <result property="username" column="author_username"/>
</association>

The association element handles relationships of the "one" type. F or example, in our example, a blog has a user. A ssociation mapping works on this result. You specify the target property to get the column of the value, the java type of the property (in many cases MyBatis can figure it out for itself), and the jdbc type if needed, and the type controller is required if you want to override or get the result value.

The difference in an association is that you need to tell MyBatis how to load the association. MyBatis has two different approaches to this:

• Nested queries: Returns the expected complex type by executing another SQL mapping statement.
• Nested results: Use nested result mapping to process a subset of repeated union results. F irst, let's look at the properties of this element. All you'll see is that it's and ordinary only by select and

The result mapping of the resultMap property is different.

Property	Describe
property	A field or property that maps to a column result. I f the properties that match the existing property JavaBeans with the same name as the given name are used. O therwise MyBatis will look for a field with a given name. I n both cases, you can navigate with complex properties that are typically point-based. For example, you can map a number of East Wests like this: " username " or "address.street.number" to a complex East West.
javaType	A fully qualified name for a Java class, or a type alias (refer to the column table of built-in type alias above). I f you map to a JavaBean, MyBatis can usually determine the type. However, if the javaType fruit you map to HashMap, then you should explicitly specify javaType to guarantee the desired behavior.
jdbcType	The type in the list of JDBC types supported before this table. T he JDBC type is simply a column that needs to be processed for insertion, update, and delete operations that may be empty. T his is what JDBC needs, jdbcType instead of MyBatis. If you program directly with JDBC, you need to specify this type - but only for values that may be empty.
typeHandler	We discussed the default type processor earlier. W ith this property, you can override the default typeHandler type processor. This property value is either a fully qualified name for a class or an implementation of a type processor, or a type alias.

The associated nested query

Property	Describe
column	The class name from the database, or the renamed column label. T his is the same string resultSet.getString(columnName) method. C olumn Note : To re-connect the primary key, you can pass the syntax of column= " {prop1=col1,prop2=col2} " to the nested query term by referred to as more than one column name. This causes prop1 and prop2 to be set as parameter objects to target nested query statements.
select	Another mapping statement, ID, can load the complex types required for this property mapping. T he value of the column specified in the column property is passed to the target select statement as an argument. T here is a detailed example after the table. S elect Note : To handle the primary key, you can pass the syntax of column= " {prop1=col1,prop2=col2} " to nested query terms by pointing out more than one column name. This causes prop1 and prop2 to be set as parameter objects to target nested query statements.
fetchType	Optional. T he valid values are lazy and eager. If present, it overrides the global configuration parameter lazyLoadingEnabled

Example:

<resultMap id="blogResult" type="Blog">
  <association property="author" column="author_id" javaType="Author" select="selectAuthor"/>
</resultMap>

<select id="selectBlog" resultMap="blogResult">
  SELECT * FROM BLOG WHERE ID = #{id}
</select>

<select id="selectAuthor" resultType="Author">
  SELECT * FROM AUTHOR WHERE ID = #{id}
</select>

We have two query statements: one to load the blog, the other to load the author, and the result map of the blog describes the "selectAuthor" statement that should be used to load its author property.

All other properties will be loaded automatically, assuming that their columns and property names match.

This approach is simple, but will not work well for large data collections and lists. T he problem is what we know as the "N1 query problem". In a nutscape, the problem with the N1 query can be caused by this:

• You executed a separate SQL statement to get a list of the results (i.e., """""""
• For each record returned, you execute a query statement to load details for each (that is, "N").

This problem can cause hundreds of SQL statements to be executed. This is usually not expected.

MyBatis can delay loading such queries as a benefit, so you can spread the cost of these statements running at the same time. However, if you load a list and then iterate quickly to access the nested data, you will call all the latency loads, which can be bad behavior.

So there's another way.

The result of the association's nesting

Property	Describe
resultMap	This is the ID of the result map, which maps the associated nested results into a suitable object diagram. T his is an alternative way to call another query statement. T his allows you to combine multiple tables to resultMap a separate result set for resultMap. S uch a result set may contain duplicates, and duplicate groups of data need to be dissographed and reasonably mapped to a nested object graph. T o make it easier, MyBatis lets you "chain" the results to handle nested results. An example can be easily replicated, and there is an example behind the table.
columnPrefix	When you connect multiple tables, you must use column aliases to avoid duplicate column names in the result set. S pecifying columnPrefix you to map these columns to resultMap See the example explained later in this section.
notNullColumn	By default, sub-objects are created only if at least one column mapped to a child object property is not empty. W ith this property, you can change this behavior by specifying which columns must have a value, so that MyBatis creates sub-objects only when any of these columns are not empty. Y ou can use commas as separators to specify multiple column names. Default: Not set.
autoMapping	If present, MyBatis enables or disables automatic mapping when mapping results to this property. T his property autoMappingBehavior N ote that it has no resultMap so it select to use it with the select or resultMap properties. Default: Not set.

Above you have seen an example of a very complex nested association. H ere's a very simple example of how it works. Instead of executing a separate statement, we union the blog table with the author table, like:

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

Note this federation query, and take protection to ensure that all results are renamed with unique and clear names. T his makes mapping very simple. Now we can map the results:

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author" column="blog_author_id" javaType="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>

In the example above, you can see that the author association of the blog represents the "authorResult" result map to load the author instance.

Very important: The id element plays a very important role in nesting the overmap. Y ou should generally specify one or more properties that can be used to uniquely identify results. I n fact, if you leave her, myBatis can still work if you have a serious performance problem. T he fewer properties you select, the better, and they can uniquely identify the results. Primary key is an obvious choice (although it is a union primary key).

Now, the example above uses an external result mapping element to map the association. T his allows author result mapping to be reused. H owever, if you don't need to reuse it, or if you simply reference all your results mapped into a single description of the result map. Y ou can nest result maps. Here's the same example of using this approach:

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

What if the blog has a co-author? Select statement is as follows:

<select id="selectBlog" resultMap="blogResult">
  select
    B.id            as blog_id,
    B.title         as blog_title,
    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,
    CA.id           as co_author_id,
    CA.username     as co_author_username,
    CA.password     as co_author_password,
    CA.email        as co_author_email,
    CA.bio          as co_author_bio
  from Blog B
  left outer join Author A on B.author_id = A.id
  left outer join Author CA on B.co_author_id = CA.id
  where B.id = #{id}
</select>

Recall that Author's design map is defined as follows:

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

Because the column names in the results are different from the columns defined in the solutionMap, you need to specify columnPrefix to re-use the resultMap in order to map the results of Auto.

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

Above you've seen how to handle "there's a" type association. B ut what about "a lot of ones"? The following section is to discuss this topic.

Collection

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

Collection elements function almost exactly the same as associations. I n fact, they are similar, and the document's similarities and differences are redundant. So we pay more attention to their differences.

Let's move on to the example above, a blog with only one author. B ut blogs have a lot of articles. In the blog class, this can be represented by the following writing:

    private List posts;

To map nested results to a list, we use collection elements. Just like association elements, we can use nested queries from the connection, or nested results.

Nested queries for collections

First, let's look at using nested queries to load articles for blogs.

<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="Blog">
  SELECT * FROM POST WHERE BLOG_ID = #{id}
</select>

There are a lot of things you should pay attention to here, but most of the code is very similar to the associated elements above. F irst, you should note that we are using collection elements. T hen pay attention to the new "ofType" property. T his property is important to distinguish between JavaBean (or field) property types and the types contained in the collection. So you can read the following map:

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

Read: "A collection of posts in ArrayList of post type."

The javaType property is not needed because MyBatis will work it out for you in many cases. So you can shorten the writing:

<collection property="posts" column="id" ofType="Post" select="selectPostsForBlog"/>

The nested result of the collection

At this point, you can guess how the nested results of the collection work because it is exactly the same as the association, except that it should use an "ofType" property

First, let's look at the SQL:

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

Once again, we've combined blog and article tables, and focused on the guaranteed features, resulting in a simple mapping of column labels. Now in the article mapping collection mapping blog, you can simply write as:

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

Again, to remember the importance of the id element, if you don't remember, read the related section above.

Similarly, if you refer to a longer form that allows you to reuse the result map more, you can use the following alternative map:

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

Note This has no restrictions on the depth, breadth, or association and collection union of the content you map. W hen mapping them, you should keep their performance in the brain. Y our app has to do unit tests and performance tests until it finds the best way. Fortunately, myBatis allows you to change your mind later without having a small (or any) impact on your code.

Advanced associations and collection mapping are a deep topic. T he documentation can only introduce you to this. With a little connection, you'll soon know how they're used.

The identifier

<discriminator javaType="int" column="draft">
  <case value="1" resultType="DraftPost"/>
</discriminator>

Sometimes a separate database query may return a lot of different (but hopefully some association) result sets of data types. T he identifier elements are designed to handle this situation, as well as the inheritance hierarchy that includes classes. The identifier is very easy to understand because it behaves much like a switch statement in the Java language.

The definition identifier specifies the column and javaType properties. C olumns are where MyBatis looks for comparison values. J avaType is the appropriate type that needs to be used to guarantee equivalent testing (although strings can be useful in many cases). Like what:

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

In this example, MyBatis gets each record from the result set, and then compares the values of its vehicle type. I f it matches an instance of any of the identifiers, use the result map specified by that instance. I n other words, doing so is completely the remaining result mapping ignored (unless it is extended, which is discussed in the second example). I f none of the instances match, MyBatis uses only the result mapping defined outside the identifier block. So, if carResult declares as follows:

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

Then only the doorCount property will be loaded. W hen this step is complete, a separate group of identifier instances is allowed in its entirety, although the parent result mapping may have little to do with it. I n this case, of course, we know that there is a relationship between cars and vehicles, such as Car is an Example of Vehicle. T herefore, we want the remaining properties to be loaded as well. The simple changes to the result mapping we set are as follows.

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

Now the properties of vehicleResult and carResult are loaded.

Although some people once found this definition of external mapping somewhat boring. S o there's another syntax to do a concise mapping style. Like what:

<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" resultType="carResult">
      <result property="doorCount" column="door_count" />
    </case>
    <case value="2" resultType="truckResult">
      <result property="boxSize" column="box_size" />
      <result property="extendedCab" column="extended_cab" />
    </case>
    <case value="3" resultType="vanResult">
      <result property="powerSlidingDoor" column="power_sliding_door" />
    </case>
    <case value="4" resultType="suvResult">
      <result property="allWheelDrive" column="all_wheel_drive" />
    </case>
  </discriminator>
</resultMap>

To remember that these are result maps, if you don't specify any results, MyBatis will automatically match columns and properties for you. S o most of these examples are very lengthy, but they are not really needed. That is, many databases are complex, and we are unlikely to be able to rely on it for all examples.

Automatic mapping

As you saw in the previous section, in a simple scenario, MyBatis can automatically map query results for you. I f you encounter a complex scenario, you need to build a result map. B ut as you'll see in this section, you can also use a mixture of these two strategies. Let's take a deeper look at how automatic mapping works.

When query results are automatically mapped, MyBatis gets the column name returned by sql and looks for properties with the same name in the java class (case is ignored). This means that mybatis assigns the value of the ID to the id if it finds the .ID?column and the .id?property.

Typically database columns are named after capital words, which are underlined, while java properties generally follow the hump nomenclale. In order to enable automatic mapping between these two naming methods, mapUnderscoreToCamelCase set to true.

Automaps work even under specific result maps. I n this case, for each result map, all resultSet-provided columns are automatically mapped if they are not manually mapped. M anual mapping is not processed until the automatic mapping is complete. In the following example, the id and userName columns are automatically mapped, and _hashedpassword columns are mapped according to the configuration.

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

There are three automapping levels:

• NONE - Auto-mapping is disabled and only manual mapping properties are set.
• PARTIAL - Results are automatically mapped, except for those within those nested result maps (connections).
• FULL - Automatically map everything

默认值是 PARTIAL for a reason. W ith FULL, automatic mapping is performed when the connection results are processed, and the connection retrieves data from multiple different entities in the same row, which may result in unwanted mapping. To understand the risks, check out the following example:

<select id="selectBlog" resultMap="blogResult">
  select
    B.id,
    B.title,
    A.username,
  from Blog B left outer join Author A on B.author_id = A.id
  where B.id = #{id}
</select>

<resultMap id="blogResult" type="Blog">
  <association property="author" resultMap="authorResult"/>
</resultMap>

<resultMap id="authorResult" type="Author">
  <result property="username" column="author_username"/>
</resultMap>

Both Blog and Author are automatically mapped in the results. N ote, however, that Author has an id property, and there is a column named id in ResultSet, so Author's id will be populated with blog id, which is not what you expect. Therefore, full needs to be used with caution.

By adding the autoMapping property, you can ignore the automapping level configuration, and you can enable or disable the resultMap specified by automapping.

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

Cache

MyBatis includes a very powerful query caching feature that can be easily configured and customized. Many of the improvements to the cache implementation in MyBatis 3 have been implemented, making it more powerful and easy to configure.

Cache is not turned on by default, except for local session caching, which enhances cashing and processing loop dependencies is also a must. To turn on secondary caching, you need to add a line to your SQL mapping file:

<cache/>

Literally that's it. The effect of this simple statement is as follows:

• All select statements in the mapping statement file will be cached.
• All insert, update, and delete statements in the mapping statement file refresh the cache.
• The cache is recovered using the Least RecentLy Used (LRU, the least recently used) algorithm.
• Depending on the schedule (such as no Flush Interval, there are no refresh intervals), the cache is not refreshed in any chronological order.
• The cache stores 1024 references to a list collection or object, regardless of what the query method returns.
• The cache is treated as read/write, meaning that object retrieval is not shared and can be safely modified by the caller without interfering with potential modifications made by other callers or threads.

All of these properties can be modified by caching the properties of the element. Like what:

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

This more advanced configuration creates a FIFO cache and refreshes it every 60 seconds, counting 512 references to the resulting object or list, and returning objects that are considered read-only, so modifying them between callers in different threads can cause conflicts.

The available retract strategies are:

• LRU Least used recently: Remove objects that have not been used for the longest time.
• FIFO First in, first out: Remove objects in the order they enter the cache.
• SOFT - Soft reference: Removes objects based on garbage collector status and soft reference rules.
• WEAK Weak References: Remove objects based on garbage collector state and weak reference rules more aggressively.

The default is the LRU.

FlushInterval (refresh interval) can be set to any positive integer, and they represent a reasonable period of time in the form of milliseconds. The default is not to set, that is, there is no refresh interval, and the cache only refreshes when the statement is called.

size can be set to any positive integer, keeping in mind the number of objects you cache and the number of available memory resources for your running environment. The default is 1024.

ReadOnly (read-only) properties can be set to true or false. A read-only cache returns the same instance of the slow-save object to all callers. T herefore, these objects cannot be modified. T his provides an important performance advantage. T he readable cache returns a copy of the cached object (serialized). This is slower, but safe, so the default is false.

Use a custom cache

In addition to these custom caching methods, you can also completely override the cache behavior by implementing your own cache or creating adapters for other third-party caching scenarios.

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

This illustration shows how to use a custom slow-down. T ype genus means that the class must implement the org.mybatis.cache.Cache interface. This interface is one of many complex interfaces in the MyBatis framework, but simply give it what it does.

    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();
    }

To configure your cache, the simple and public JavaBeans property configures your cache implementation, and passes the property through the cache element, for example, the following code calls a method called "setCacheFile" in your cache implementation:

<cache type="com.domain.something.MyCustomCache">
  <property name="cacheFile" value="/tmp/my-custom-cache.tmp"/>
</cache>

You can use all the simple types as properties of JavaBeans, and MyBatis converts them.

It is important to remember that cache configurations and cache instances are bound in the namespace of SQL mapping files. T herefore, all statements in the same namespace are the same as the cache of bindings. S tatements can modify and cache the way they interact, or use two simple properties to completely exclude them on the basis of the statement's statement. By default, statements can be configured like this:

<select ... flushCache="false" useCache="true"/>
<insert ... flushCache="true"/>
<update ... flushCache="true"/>
<delete ... flushCache="true"/>

Because those are the defaults, you obviously can't explicitly configure a statement in this way. C onversely, if you want to change the default behavior, you can only set the flushCache and useCache properties. F or example, in some cases you might want to exclude querying the cache for specific statement results, or you might want a query statement to refresh the cache. Similarly, you may have some update statements that rely on execution without having to flush the cache.

Refer to the cache

Recall that in the previous section, the only cache of this particular namespace is used or refreshed with statements within the same namespace. M aybe sometime in the future, you'll want to share the same cache configuration and instances in the namespace. In this case, you can use the cache-ref element to refer to another cache.

<cache-ref namespace="com.someone.application.data.SomeMapper"/>