Mybatis-Mapper XML Files

The true power of MyBatis is in the Mapped Statements. This is where the magic happens. For all of their power, the Mapper XML files are relatively simple. Certainly if you were to compare them to the equivalent JDBC code, you would immediately see a savings of 95% of the code. MyBatis was built to focus on the SQL, and does its best to stay out of your way.

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

1、cache – Configuration of the cache for a given namespace.
2、cache-ref – Reference to a cache configuration from another namespace.
3、resultMap – The most complicated and powerful element that describes how to load your objects from
   the database result sets.
4、parameterMap – `Deprecated`! Old-school way to map parameters. Inline parameters are preferred and
   this element may be removed in the future. Not documented here.
5、sql – A reusable chunk of SQL that can be referenced by other statements.
6、insert – A mapped INSERT statement.
7、update – A mapped UPDATE statement.
8、delete – A mapped DELETE statement.
9、select – A mapped SELECT statement.

The next sections will describe each of these elements in detail, starting with the statements themselves.

select

The select statement is one of the most popular elements that you'll use in MyBatis. Putting data in a database isn't terribly valuable until you get it back out, so most applications query far more than they modify the data. For every insert, update or delete, there are probably many selects. This is one of the founding principles of MyBatis, and is the reason so much focus and effort was placed on querying and result mapping. The select element is quite simple for simple cases. For example:

  SELECT * FROM PERSON WHERE ID = #{id}

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.

Notice the parameter notation:

#{id}

This tells MyBatis to create a PreparedStatement parameter. With JDBC, such a parameter would be identified by a "?" in SQL passed to a new PreparedStatement, something like this:

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

Of course, there's a lot more code required by JDBC alone to extract the results and map them to an instance of an object, which is what MyBatis saves you from having to do. There's a lot more to know about parameter and result mapping. Those details warrant their own section, which follows later in this section.

The select element has more attributes that allow you to configure the details of how each statement should behave.

Select Attributes
1、id:	A unique identifier in this namespace that can be used to reference this statement.

2、parameterType: The fully qualified class name or alias for the parameter that will be passed into this statement. This attribute is optional because MyBatis can calculate the TypeHandler to use out of the actual parameter passed to the statement. Default is unset.

3、parameterMap：This is a deprecated approach to referencing an external parameterMap. Use inline parameter mappings and the parameterType attribute.

4、resultType: The fully qualified class name or alias for the expected type that will be returned from this statement. Note that in the case of collections, this should be the type that the collection contains, not the type of the collection itself. Use resultType OR resultMap, not both.

5、resultMap: A named reference to an external resultMap. Result maps are the most powerful feature of MyBatis, and with a good understanding of them, many difficult mapping cases can be solved. Use resultMap OR resultType, not both.

6、flushCache: Setting this to true will cause the local and 2nd level caches to be flushed whenever this statement is called. Default: false for select statements.

7、useCache: Setting this to true will cause the results of this statement to be cached in 2nd level cache. Default: true for select statements.

8、timeout:	This sets the number of seconds the driver will wait for the database to return from a request, before throwing an exception. Default is unset (driver dependent).

9、fetchSize: This is a driver hint that will attempt to cause the driver to return results in batches of rows numbering in size equal to this setting. Default is unset (driver dependent).

10、statementType: Any one of STATEMENT, PREPARED or CALLABLE. This causes MyBatis to use Statement, PreparedStatement or CallableStatement respectively. Default: PREPARED.

11、resultSetType: Any one of FORWARD_ONLY|SCROLL_SENSITIVE|SCROLL_INSENSITIVE|DEFAULT(same as unset). Default is unset (driver dependent).

12、databaseId: In case there is a configured databaseIdProvider, MyBatis will load all statements with no databaseId attribute or with a databaseId that matches the current one. If case the same statement if found with and without the databaseId the latter will be discarded.

13、resultOrdered: This is only applicable for nested result select statements: If this is true, it is assumed that nested results are contained or grouped together such that when a new main result row is returned, no references to a previous result row will occur anymore. This allows nested results to be filled much more memory friendly. Default: false.

14、resultSets: This is only applicable for multiple result sets. It lists the result sets that will be returned by the statement and gives a name to each one. Names are separated by commas.
insert, update and delete
The data modification statements insert, update and delete are very similar in their implementation:






Insert, Update and Delete Attributes
1、id: A unique identifier in this namespace that can be used to reference this statement.

2、parameterType: The fully qualified class name or alias for the parameter that will be passed into this statement. This attribute is optional because MyBatis can calculate the TypeHandler to use out of the actual parameter passed to the statement. Default is unset.

3、parameterMap: This is a deprecated approach to referencing an external parameterMap. Use inline parameter mappings and the parameterType attribute.

4、flushCache: Setting this to true will cause the 2nd level and local caches to be flushed whenever this statement is called. Default: true for insert, update and delete statements.

5、timeout: This sets the maximum number of seconds the driver will wait for the database to return from a request, before throwing an exception. Default is unset (driver dependent).

6、statementType: Any one of STATEMENT, PREPARED or CALLABLE. This causes MyBatis to use Statement, PreparedStatement or CallableStatement respectively. Default: PREPARED.

7、useGeneratedKeys:(insert and update only) This tells MyBatis to use the JDBC getGeneratedKeys method to retrieve keys generated internally by the database (e.g. auto increment fields in RDBMS like MySQL or SQL Server). Default: false.

8、keyProperty:(insert and update only) Identifies a property into which MyBatis will set the key value returned by getGeneratedKeys, or by a selectKey child element of the insert statement. Default: unset. Can be a comma separated list of property names if multiple generated columns are expected.

9、keyColumn:(insert and update only) Sets the name of the column in the table with a generated key. This is only required in certain databases (like PostgreSQL) when the key column is not the first column in the table. Can be a comma separated list of columns names if multiple generated columns are expected.

10、databaseId: In case there is a configured databaseIdProvider, MyBatis will load all statements with no databaseId attribute or with a databaseId that matches the current one. If case the same statement if found with and without the databaseId the latter will be discarded.
The following are some examples of insert, update and delete statements.

  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})



  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}



  delete from Author where id = #{id}


As mentioned, insert is a little bit more rich in that it has a few extra attributes and sub-elements that allow it to deal with key generation in a number of ways.
First, if your database supports auto-generated key fields (e.g. MySQL and SQL Server), then you can simply set useGeneratedKeys="true" and set the keyProperty to the target property and you're done. For example, if the Author table above had used an auto-generated column type for the id, the statement would be modified as follows:

  insert into Author (username,password,email,bio)
  values (#{username},#{password},#{email},#{bio})


If your database also supports multi-row insert, you can pass a list or an array of Authors and retrieve the auto-generated keys.

  insert into Author (username, password, email, bio) values
  
    (#{item.username}, #{item.password}, #{item.email}, #{item.bio})
  


MyBatis has another way to deal with key generation for databases that don't support auto-generated column types, or perhaps don't yet support the JDBC driver support for auto-generated keys.
Here's a simple (silly) example that would generate a random ID (something you'd likely never do, but this demonstrates the flexibility and how MyBatis really doesn't mind):

  
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  
  insert into Author
    (id, username, password, email,bio, favourite_section)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})


In the example above, the selectKey statement would be run first, the Author id property would be set, and then the insert statement would be called. This gives you a similar behavior to an auto-generated key in your database without complicating your Java code.
The selectKey element is described as follows:


selectKey Attributes
1、keyProperty: The target property where the result of the selectKey statement should be set. Can be a comma separated list of property names if multiple generated columns are expected.

2、keyColumn: The column name(s) in the returned result set that match the properties. Can be a comma separated list of column names if multiple generated columns are expected.

3、resultType: The type of the result. MyBatis can usually figure this out, but it doesn't hurt to add it to be sure. MyBatis allows any simple type to be used as the key, including Strings. If you are expecting multiple generated columns, then you can use an Object that contains the expected properties, or a Map.

4、order: This can be set to BEFORE or AFTER. If set to BEFORE, then it will select the key first, set the keyProperty and then execute the insert statement. If set to AFTER, it runs the insert statement and then the selectKey statement – which is common with databases like Oracle that may have embedded sequence calls inside of insert statements.

5、statementType: Same as above, MyBatis supports STATEMENT, PREPARED and CALLABLE statement types that map to Statement, PreparedStatement and CallableStatement respectively.
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. Different property values can vary in include instances. For example:
 ${alias}.id,${alias}.username,${alias}.password 

The SQL fragment can then be included in another statement, for example:

  select
    ,
    
  from some_table t1
    cross join some_table t2

  ${prefix}Table



  from
    



  select
    field1, field2, field3