MyBatis Mapper XML file (iv)-JDBC types and nested queries

Supported JDBC types
For future reference, MyBatis supports the following JDBC types by including the Jdbctype enumerated type.
BIT
FLOAT
CHAR
TIMESTAMP
Other
UNDEFINED
TINYINT
REAL
VARCHAR
BINARY
Blob
NVARCHAR
SMALLINT
DOUBLE
LongVarChar
VARBINARY
Clob
NCHAR
INTEGER
NUMERIC
DATE
LongVarBinary
BOOLEAN
NCLOB
BIGINT
DECIMAL
Time
Null
CURSOR
ARRAY

Construction method
For most data transfer objects (data Transfer object,dto) types, properties can work, and like most of your domain models, directives may be where you want to make immutable classes. It is often appropriate for immutable classes to have a table that contains reference or query data that is rarely or substantially unchanged. Construction method injection allows you to set the value of a property for a class at initialization time without exposing the public method. MyBatis also supports private properties and private JavaBeans properties for this purpose, but some people prefer the constructor method injection. The constructor method element supports this.
Take a look at the following construction method:

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

In order to inject the result into the construction method, MyBatis needs to locate the corresponding construction method in some way. In the following example, MyBatis searches for a construction method that declares three parameters, in the order of Java.lang.Integer, java.lang.String, and Int.

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

When you are working with a construction method with multiple parameters, the order of the ARG elements is maintained to be error-prone. In order to use the name of the constructor parameter to reference the formal parameter, you can add @Param annotations or compile the project using the '-parameters ' compile option and enable useactualparamname (this option is turned on by default). The following example is still valid for the same construction method, although the second and third parameter sequences do not match the order declared in the constructor method.

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

If there is a property with the same name and the same type, then its javatype can be omitted.
The remaining attributes and rules and the fixed ID and result elements are the same.

column: The class name from the database, or the renamed column label. This is the same string that is normally passed to the 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 aliases above). If you map to a javabean,mybatis you can usually determine the type. However, if you are mapping to HASHMAP, then you should explicitly specify javatype to guarantee the desired behavior.
Jdbctype: The type in the list of supported JDBC types before this table. The JDBC type is just a column that needs to be processed to insert, update, and delete operations that may be empty. This is the need for JDBC, jdbctype rather than MyBatis. If you are programming 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. With this property, you can override the default type processor. This property value is either the fully qualified name of the class or the implementation of a type processor, or a type alias.
Select: The ID of the mapping statement that is used to load the properties of the complex type, and the data retrieved from column as the argument for this SELECT statement. Please refer to the Association label for details.
Resultmap: The ID of Resultmap, which can map nested result sets to an appropriate object tree, is similar to the Select property, which enables mapping the results of a multi-table join operation into a single resultset. Such resultset will correctly map the result set containing duplicate or partial data duplication to the nested object tree. To implement it, MyBatis allows you to "concatenate" resultmap to resolve problems with nested result sets. For more information, please refer to the association element below.
name: Constructs the name of the method parameter. You can write the ARG element in any order by specifying a specific name. See above for explanations.

Association

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

The correlation element handles the "have one" type of relationship. For example, in our example, a blog has a user. Correlation 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 calculate it yourself), and if necessary, the JDBC type, if you want to overwrite or get the result value also need type controller.

The difference in association is that you need to tell MyBatis how to load the association. MyBatis There are two different ways to do this:
Nested query: Returns the expected complex type by executing another SQL mapping statement.

Nested results: Use nested result mappings to handle a subset of duplicate union results. First, let's look at the attributes of this element. All you will see is that it differs from the normal result mapping only by the Select and Resultmap properties.

Property: The field or attribute that is mapped to the column result. If a match exists, and the property JavaBeans is the same as the given name, then it will be used. Otherwise MyBatis will look for the field given the name. In both cases, you can use the usual point-and-click Complex property navigation. For example, you can map something like this: "username", or map to something complex: "Address.street.number".

Javatype: The fully qualified name of a Java class, or a type alias (refer to the list of built-in type aliases above). If you map to a javabean,mybatis you can usually determine the type. However, if you are mapping Javatype to HASHMAP, then you should explicitly specify javatype to ensure the desired behavior.

jdbctype: The type in the list of supported JDBC types before this table. The JDBC type is just a column that needs to be processed to insert, update, and delete operations that may be empty. This is the need for JDBC, jdbctype rather than MyBatis. If you are programming 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. With this property, you can override the default Typehandler type processor. This property value is either the fully qualified name of the class or the implementation of a type processor, or a type alias.

Associated Nested queries

Column: The class name from the database, or the renamed column label. This is the same string that is normally passed to the resultset.getstring (ColumnName) method. Column Note: To process a composite primary key, you can specify multiple column names to be passed to the nested query statement through the syntax of column= "{prop1=col1,prop2=col2}". This causes PROP1 and PROP2 to be set to the target nested query statement in the form of a Parameter object.

Select: The ID of another mapping statement that can load the complex type required for this property mapping. Gets the value of the column specified in the column property that will be passed to the target SELECT statement as a parameter. There is a detailed example behind the table. Select Note: To process a composite primary key, you can specify multiple column names to be passed to the nested query statement through the syntax of column= "{prop1=col1,prop2=col2}". This causes PROP1 and PROP2 to be set to the target nested query statement in the form of a Parameter object.

fetchtype: Optional. Valid values are lazy and eager. If used, it supersedes 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 what the "Selectauthor" statement should be used to load its Author property. All other properties will be loaded automatically, assuming their columns and property names match.

This is a simple approach, but will not perform well for large data collections and lists. The problem is the "n+1 query problem" that we are familiar with. To summarize, the n+1 query problem can be caused by:

• 
  You execute a separate SQL statement to get a list of results (that is, "+1").

• 
  For each record returned, you execute a query statement to load the details for each (that is, "N").

This problem can result in hundreds of thousands of SQL statements being executed. This is not usually expected. MyBatis can delay loading such queries is a benefit, so you can scatter the consumption of these statements running concurrently. However, if you load a list and then quickly iterate to access the nested data, you'll call all the lazy loads, and this behavior can be pretty bad.

So there's another way.
Associated Nested results

Resultmap: This is the ID of the result map, which can map the associated nested results to an appropriate object graph. This is an alternative way to invoke another query statement. This allows you to combine multiple tables to synthesize into resultmap a separate result set. Such a result set may contain duplicates, the repeating group of the data needs to be decomposed, and reasonably mapped to a nested object graph. To make it easy, MyBatis lets you "link" the result map to handle nested results. An example would be easy to follow, and there is an example behind the table.
columnprefix: When connecting multiple tables, you will have to use column aliases to avoid repeating column names in resultset. Specifies that Columnprefix allows you to map column names to an external result set. Take a look at the following example.
notnullcolumn: By default, child objects are created only when at least one column is mapped to a non-empty property. Specifying a non-empty column for this property will change the default behavior, so that MyBatis will only create a child object if the columns are not empty. You can specify multiple column names, separated by commas. Default value: Not set (unset).
automapping: If used, MyBatis will enable or disable auto-mapping when mapping results to the current property. This property overrides the global auto-map behavior. Note that it has no effect on the external result set, so this is meaningless in the Select or Resultmap property. Default value: Not set (unset).

In the above you have seen an example of a very complex nested association. Here's a very simple example to illustrate how it works. Instead of executing a separate statement, we combine the blog table with the author table together, like this:

<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 federated query, and take protection to ensure that all results are renamed with a unique and clear name. This makes the mapping very simple. Now we can map this result:

<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 nested result mappings. You should always specify one or more properties that can uniquely identify the result. In fact, if you don't specify it, MyBatis can still work, but there are serious performance problems. When you can uniquely identify the result, choose as few properties as possible. The primary key is an obvious choice (even if it is a composite primary key).

Now, the example above uses an external result mapping element to map the association. This allows Author result mappings to be reused. However, if you do not need to reuse it, or you simply refer to all of your results mapped into a single described result map. You can nest result mappings. Here are the same examples that are used in this way:

<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 a blog has a co-author? The SELECT statement will look like this:

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

The resultmap of calling author again will be 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 name in the result differs from the column name in Resultmap. You need to specify columnprefix to reuse resultmap that map co-author results.

<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 have seen how to handle the "have a" type association. But what about "a lot of"? 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>

The function of the collection element is almost identical to the association. In fact, they are very similar, and the similarities and differences of documents are superfluous. So we focus more on their differences.

Let's go on to the example above, a blog with only one author. But blogs have lots of articles. In the blog class, this can be represented by the following notation:

Private List posts;
To map the nested result collection to the list, we use the collection element. Just like associative elements, we can use nested queries from joins, or nest 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="Post">
  SELECT * FROM POST WHERE BLOG_ID = #{id}
</select>

Here you should pay attention to a lot of things, but most of the code is very similar to the above related elements. First, you should be aware that we are using a collection element. Then pay attention to the new "OfType" attribute. This property is important for distinguishing between JavaBean (or field) attribute types and the types that the collection contains. So you can read the following map:

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

Read as: "The collection of posts in the ArrayList of the Post type. ”
The Javatype property is not required because MyBatis will be counted for you in many cases. So you can shorten the wording:

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

Nested results of the collection
At this point, you can guess how the nested result of the collection works, because it is exactly the same as the association, except that it applies a "OfType" attribute 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 have joined the blog and article tables, and have focused on guaranteeing features and simple mapping of result column labels. Now use the article Mapping Collection Mapping blog, can be simply written 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, remember the importance of the ID element, and if you don't remember it, read the related section above.
Similarly, if you refer to a longer form that allows for more reuse of your result mappings, you can use this alternative mapping:

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

There is no limit to the depth, breadth, or association of the content you are mapping. When mapping them, you should keep their performance in the brain. The unit tests and performance tests that your app will have to do before it finds the best method. Fortunately MyBatis allows you to change your mind later without having a small (or any) effect on your code.

Advanced associations and collection mappings are a matter of depth. The documentation can only be introduced to you here. With a little connection, you will soon know how to use them.

Identification device

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

Sometimes a single database query might return a number of different (but hopefully somewhat associative) result sets of data types. Discriminator elements are designed to handle this situation, as well as inheritance hierarchies that include classes. The discriminator is very easy to understand because it behaves much like a switch statement in the Java language.

The definition discriminator specifies the column and Javatype properties. The column is where MyBatis looks for comparison values. Javatype is the appropriate type to be used to ensure equivalence testing (although strings are useful in many cases). Like what:

  <resultmap id= "Vehicleresult" type= "Vehicle"; 
  <id property= "id" column= "id"/> 
 & Nbsp;<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 compares the value of its vehicle type. If it matches an instance of any discriminator, then the result mapping specified by this instance is used. In other words, this is exactly the remaining result mapping is ignored (unless it is extended, as discussed in the second example). If none of the instances match, then MyBatis only uses the result mappings defined outside the discriminator block. So, if Carresult is declared as follows:

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

Then only the Doorcount property will be loaded. This step completes completely allowing a separate group of discriminator instances, although it may not have anything to do with the parent result mapping. In this case, of course we know there is a relationship between cars and vehicles, such as Car is a Vehicle instance. Therefore, we want the remaining properties to also be loaded. The simple changes to the result mappings we set are as follows.

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

Now the properties of both Vehicleresult and Carresult are loaded.
Although some people once discovered that this external mapping definition would be somewhat annoying. So there is 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>

Remember that these are result mappings, and if you don't specify any results, then MyBatis will automatically match the columns and attributes for you. So most of these examples are very lengthy, but they are not needed. In other words, many databases are complex and we are unlikely to be able to rely on them for all the examples.

Follow the public number: it haha (it_haha), learn more content.

MyBatis Mapper XML file (iv)-JDBC types and nested queries