Common label descriptions in XML Mapping Files in mybatis

Time:2022-1-14

The SQL mapping file has only a few top-level elements (listed in the order that 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 to load objects from the database result set.
  • Parametermap – obsolete! Old style parameter mapping. A better approach is to use inline parameters, which may be removed in the future.
  • SQL – a block of repeatable terms that can be referenced by other statements.
  • Insert – map insert statement
  • Update – map update statement
  • Delete – map delete statement
  • Select – map query statement

select


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

This statement is called selectperson. It accepts an int (or integer) parameter and returns an object of HashMap type. 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 Preparedstatement parameter. In JDBC, such a parameter will be represented by a “?” in SQL To be identified and passed to a new preprocessing statement, like this:

//Approximate JDBC code, non mybatis code
 String selectPerson = "SELECT * FROM PERSON WHERE ID=?";
 PreparedStatement ps = conn.prepareStatement(selectPerson);
 ps.setInt(1,id);
 <select
  
   parameterType="int"
   parameterMap="deprecated"
   resultType="hashmap"
   resultMap="personResultMap"
   flushCache="false"
   useCache="true"
   timeout="10"
   fetchSize="256"
   statementType="PREPARED"
   resultSetType="FORWARD_ONLY">
attribute describe
id A unique identifier in the namespace that can be used to reference this statement.
parameterType The fully qualified name or alias of the parameter class that will be passed into this statement. This property is optional because mybatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset.
parameterMap This is an obsolete method that references an external parametermap. Please use inline parameter mapping and parametertype property.
resultType The fully qualified name or alias of the class of the expected type returned from this statement. Note that if a collection is returned, it should be set to the type contained in the collection, not the collection itself. You can use resulttype or resultmap, but not both.
resultMap Named reference to the external resultmap. The mapping of result sets is the most powerful feature of mybatis. If you understand it thoroughly, many complex mapping situations can be solved. You can use resultmap or resulttype, but not both.
flushCache When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is false.
useCache Setting it to true will cause the results of this statement to be cached by the secondary cache. The default value is 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 an exception. The default value is unset (drive dependent).
fetchSize This is a prompt to the driver. Try to make the number of result lines returned by the driver in batch each time equal to this setting value. The default value is unset (drive dependent).
statementType One of state, prepared or callable. This will make mybatis use statement, Preparedstatement or callablestatement respectively. The default value is prepared.
resultSetType FORWARD_ ONLY,SCROLL_ SENSITIVE, SCROLL_ Either intrinsic or default (equivalent to unset). The default value is unset (dependent driver).
databaseId If the database vendor ID (databaseidprovider) is configured, mybatis will load all statements without databaseid or matching the current databaseid; If there are statements with or without, the statements without will be ignored.
resultOrdered This setting is only applicable to nested result select statements: if it is true, it is assumed that nested result sets or groups are included. In this way, when a main result row is returned, there will be no reference to the previous result set. This makes it possible to get nested result sets without running out of memory. Default: false.
resultSets This setting applies only to multiple result sets. It will list the result sets returned after the statement is executed and give each result set a name separated by commas.

Insert, update and delete

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


<insert
  
   parameterType="domain.blog.Author"
   flushCache="true"
   statementType="PREPARED"
   keyProperty=""
   keyColumn=""
   useGeneratedKeys=""
   timeout="20">
 ​
 <update
  
   parameterType="domain.blog.Author"
   flushCache="true"
   statementType="PREPARED"
   timeout="20">
 ​
 <delete
  
   parameterType="domain.blog.Author"
   flushCache="true"
   statementType="PREPARED"
   timeout="20">
attribute describe
id A unique identifier in the namespace that can be used to represent this statement.
parameterType The fully qualified class name or alias of the parameter to be passed into the statement. This attribute is optional because mybatis can infer the parameters of the specific incoming statement through the type processor. The default value is unset.
parameterMap This is an obsolete method that references an external parametermap. Please use inline parameter mapping and parametertype property.
flushCache When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is true (for 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 an exception. The default value is unset (drive dependent).
statementType State, prepared or callable. This will make mybatis use statement, Preparedstatement or callablestatement respectively. The default value is prepared.
useGeneratedKeys (only useful for insert and update) this will make mybatis use the getgeneratedkeys method of JDBC to retrieve the primary key generated inside the database (such as the auto increment field of relational database management systems such as MySQL and SQL Server). The default value is false.
keyProperty (only useful for insert and update) uniquely mark an attribute. Mybatis will set its key value through the return value of getgeneratedkeys or through the selectkey sub element of the insert statement. The default value is unset. If you want multiple generated columns, you can also have a comma separated list of attribute names.
keyColumn (only useful for insert and update) set the column name in the table through the generated key value. This setting is only necessary in some databases (such as PostgreSQL). It needs to be set when the primary key column is not the first column in the table. If you want to use more than one generated column, you can also set it to a comma separated list of attribute names.
databaseId If the database vendor ID (databaseidprovider) is configured, mybatis will load all statements without databaseid or matching the current databaseid; If there are statements with or without, the statements without will be ignored.

Generate primary key


 <selectKey
   keyProperty="id"
   resultType="int"
   order="BEFORE"
   statementType="PREPARED">
attribute describe
keyProperty The target attribute of the result of the selectkey statement should be set. If you want multiple generated columns, you can also have a comma separated list of attribute names.
keyColumn The name of the column in the returned result set that matches the property. If you want multiple generated columns, you can also have a comma separated list of attribute names.
resultType Type of result. Mybatis can usually be inferred, but it won’t be a problem to write it for more accuracy. Mybatis allows any simple type to be used as the type of primary key, including string. If you want to act on multiple generated columns, you can use an object or a map that contains the desired attributes.
order This can be set to before or after. If it is set to before, it will first generate the primary key, set the keyproperty, and then execute the insert statement. If it is set to after, the insert statement is executed first, and then the statement in the selectkey – this is similar to the behavior of Oracle database. There may be embedded index calls inside the insert statement.
statementType As before, mybatis supports the mapping types of state, prepared and callable statements, representing Preparedstatement and callablestatement types respectively.

 <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="id">
   SELECT LAST_INSERT_ID() AS id
 </selectKey>

sql

This element can be used to define reusable SQL code segments that can be included in other statements. It can be set statically (when loaded). You can set different values to parameter placeholders in different include statements. For example:


<sql> ${alias}.id,${alias}.username,${alias}.password </sql>
 <select 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>

Result mapping

The resultmap element is the most important and powerful element in mybatis. In some cases, it allows you to perform operations that JDBC does not support. In fact, when writing mapping code for complex statements such as connections, a resultmap can replace thousands of lines of code that achieve the same function. The design idea of resultmap is that there is no need to configure explicit result mapping for simple statements, but only need to describe their relationship for more complex statements.

Define a class with three attributes in JavaBean: ID, username and hashedpassword, and then in mapper In XML, these attributes will correspond to the column names in the select statement. Such a JavaBean can be mapped to the resultset, which is as simple as mapping to HashMap.


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

Like the third example in the third example, you can also define an external resultmap, which is another way to solve the column name mismatch.


 <resultMap type="User">
   <id property="id" column="user_id" />
   <result property="username" column="user_name"/>
   <result property="password" column="hashed_password"/>
 </resultMap>
 ​
 <select resultMap="userResultMap">
   select user_id, user_name, hashed_password
   from some_table
   where id = #{id}
 </select>

Result map

  • Constructor – used to inject results into the constructor when instantiating a class
  • Idarg – ID parameter; Marking the results as IDS can help improve overall performance
  • Arg – will be injected into a normal result of the constructor
  • ID – an ID result; Marking the results as IDS can help improve overall performance
  • Result – the normal result injected into a field or JavaBean property
  • Association – a complex type of Association; Many results will be packaged into this type
  • Nested result mapping – the association itself can be a resultmap element or a reference from elsewhere
  • Collection – a collection of complex types
  • Nested result mapping – the collection itself can be a resultmap element or a reference from elsewhere
  • Discriminator – uses the result value to determine which resultmap to use
  • Case – result mapping based on certain values
  • Nested result mapping – the case itself can be a resultmap element, so it can have the same structure and elements, or reference one from elsewhere.
attribute describe
id A unique identifier in the current namespace that identifies a result mapping.
type The fully qualified name of a class, or a type alias (for built-in type aliases, refer to the table above).
autoMapping If this property is set, mybatis will turn on or off automatic mapping for this result mapping. This property overrides the global property automappingbehavior. Default: unset.

id & result


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

These are the most basic contents of result mapping. Both the ID and result elements map the value of a column to an attribute or field of a simple data type (string, int, double, date, etc.).

The only difference between the two is that the result represented by the ID element will be the identification attribute of the object, which will be used when comparing object instances. This can improve the overall performance, especially when caching and nested result mapping (that is, connection mapping).

attribute describe
property Fields or properties mapped to column results. If the JavaBean used to match has a property with the given name, it will be used. Otherwise, mybatis will look for the field with the given name. In either case, you can use the usual dot separated form for complex attribute navigation. For example, you can map something simple: “username”, or to something complex: “address. Street. Number”.
column The column name in the database, or the alias of the column. In general, this and is passed to resultset The parameters of the getString (columnname) method are the same.
javaType The fully qualified name of a Java class or a type alias (for built-in type aliases, refer to the table above). If you map to a JavaBean, mybatis can usually infer types. However, if you are mapping to a HashMap, you should explicitly specify a javatype to ensure that the behavior is consistent with expectations.
jdbcType JDBC type. For the supported JDBC types, see “supported JDBC types” after this table. You only need to specify the JDBC type on columns that may perform inserts, updates, and deletions and allow null values. This is a JDBC requirement, not a mybatis requirement. If you are programming directly for JDBC, you need to specify this type for columns that may have null values.
typeHandler We discussed the default type processor earlier. Using this property, you can override the default type processor. This property value is the fully qualified name of a type processor implementation class, or a type alias.

cache

By default, only local session caching is enabled, which only caches the data in one session. To enable global L2 caching, just add a line to your SQL mapping file: < cache / >

Basically.The effect of this simple statement is as follows:

  • The results of all select statements in the mapping statement file will be cached.
  • All insert, update, and delete statements in the mapping statement file flush the cache.
  • The cache will use the least recently used (LRU) algorithm to clear the unnecessary cache.
  • The cache does not refresh regularly (that is, there is no refresh interval).
  • The cache holds 1024 references to a list or object, regardless of what the query method returns.
  • The cache is treated as a read / write cache, which means that the obtained objects are not shared and can be safely modified by the caller without interfering with potential modifications made by other callers or threads.

The prompt cache only works on the statements in the mapping file where the cache tag is located. If you mix Java API and XML mapping files, the statements in the common interface will not be cached by default. You need to use the @ cachenamespaceref annotation to specify the cache scope.

These attributes can be modified through the attributes of the cache element. For example:


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

This more advanced configuration creates a FIFO cache, which is refreshed every 60 seconds. It can store up to 512 references of the result object or list, and the returned objects are considered read-only. Therefore, modifying them may conflict with callers in different threads.

Available purge strategies are:

  • LRU – least recently used: removes 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 more actively based on garbage collector status and weak reference rules.

The default purge policy is LRU.

The flush interval property can be set to any positive integer. The set value should be a reasonable amount of time in milliseconds. The default is not set, that is, there is no refresh interval. The cache will only be refreshed when the statement is called.

The size (number of references) attribute can be set to any positive integer. Pay attention to the size of the object to be cached and the memory resources available in the running environment. The default value is 1024.

The readonly property can be set to true or false. A read-only cache returns the same instance of the cache object to all callers. Therefore, these objects cannot be modified. This provides a significant performance improvement. The read-write cache returns (through serialization) a copy of the cache object. It will be slower, but safer, so the default value is false.

Indicates that the L2 cache is transactional. This means that when sqlsession completes and commits, or completes and rolls back, but the insert / delete / UPDATE statement with flushcache = true is not executed, the cache will be updated.

Use custom cache

In addition to the above customized caching methods, you can also completely override the caching behavior by implementing your own caching or creating adapters for other third-party caching schemes.


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

example:

The class specified by the type attribute must implement org mybatis. cache. The cache interface and provides a constructor that accepts a string parameter as an ID. This interface is one of many complex interfaces in the mybatis framework, but the behavior is very simple.


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

Dynamic SQL

  • Mybatis dynamic SQL allows us to write dynamic SQL in the form of XML tags in the XML Mapping file to complete the functions of logical judgment and dynamic splicing of SQL.
  • Mybatis provides nine dynamic SQL Tags: < if / >, < choose / >, < when / >, < otherwise / >, < trim / >, < where / >, < set / >, < foreach / >, < bind / >.
  • Its execution principle is to use the expression of ognl to calculate the value of the expression from the SQL parameter object, and dynamically splice SQL according to the value of the expression, so as to complete the function of dynamic SQL.

if

What dynamic SQL usually does is include part of the where clause according to the condition. For example:


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

This statement provides an optional function to find text. If “title” is not passed in, all blogs in “active” status will be returned; Conversely, if “title” is passed in, the “title” column will be vaguely searched and the blog result will be returned (the “title” parameter value can contain some masks or wildcards).

Optional search is performed through the two parameters “title” and “author”:


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

Sometimes we don’t want to apply to all conditional statements, but just choose one of them. In this case, mybatis provides a choose element, which is a bit like a switch statement in Java.

This time, you can search by “title” if “title” is provided, and by “author” if “author” is provided. If neither is provided, all qualified blogs will be returned (in fact, the administrator may select the blog list according to a certain strategy, rather than returning a large number of meaningless random results).


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

If the above examples are not satisfied, they will be pieced together into an SQL statement, resulting in failure to query, such as select * from blog where


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

The where element inserts the “where” clause only when the condition of at least one child element returns the SQL clause. Also, if statement starts with the “and” or “, where element removes them.

If the where element does not play according to the normal routine, we can customize the function of the where element by customizing the trim element. For example, the user-defined trim element equivalent to the where element is:


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

The prefixoverrides property ignores text sequences separated by pipes (note that spaces in this example are also necessary). Its function is to remove all contents specified in the prefixoverrides property and insert the contents specified in the prefix property.

A similar solution for dynamically updating statements is called set. The set element can be used to dynamically include columns that need to be updated and discard other columns. For example:


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

Here, the set element will dynamically prefix the set keyword and delete irrelevant commas, because these commas are likely to be left behind the generated SQL statement after the conditional statement is used. (Note: because the “if” element is used, if the last “if” does not match and the previous match, there will be a comma left at the end of the SQL statement)

If you are interested in the code of the custom trim element equivalent to the set element, this is its true face:


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

Note that here we delete the suffix value and add the prefix value.

foreach

Another common operation requirement of dynamic SQL is to traverse a collection, usually when constructing in conditional statements. For example:


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

The foreach element is very powerful. It allows you to specify a collection and declare collection items and index variables that can be used in the element body. It also allows you to specify the beginning and end strings and place separators between iteration results. This element is smart, so it doesn’t accidentally attach extra delimiters.

be careful

You can pass any iteratable object (such as list, set, etc.), map object, or array object to foreach as a set parameter. When an iteratable object or array is used, index is the number of current iterations, and the value of item is the element obtained in this iteration. When using a map object (or a collection of map. Entry objects), index is the key and item is the value.

bind

The bind element can create a variable from an ognl expression and bind it to a context. For example:


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

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer.