preface

• This article explains the result mapping of Mybatis. If you want to use Mybatis flexibly in enterprise development, you must be proficient in this part.

What is result mapping?

• Simply put, it is how the fields returned by an SQL query statement correspond to the attributes in the Java entity class.
• The following SQL statement is used to query the user id, Department id and attending doctor id of the patient:

  <select id='selectPatientInfos' resultType='com.xxx.domain.PatientInfo'>
    select user_id,dept_id,doc_id from patient_info;
  </select>

• The Java entity class PatientInfo is as follows:

@Data
public class PatientInfo{
  private String userId;
  private String deptId;
  private String docId;
}

• The purpose of the programmer writing this SQL is to query the user_id,dept_id,doc_id is assigned to userid, deptid and docid in the entity class respectively. This is a simple result mapping.

How to map?

• There are many ways of result mapping in Myabtis, which will be introduced one by one below.

Alias mapping

• This is simple. Keep the fields returned by the SQL query the same as the Java entity class. For example, the SQL in the above example can be written as:

<select id='selectPatientInfos' resultType='com.xxx.domain.PatientInfo'>
   select user_id as userId,
   dept_id as deptId,
   doc_id as docId
   from patient_info; 
</select>

• In this way, you can successfully map with the attributes in the entity class.

Hump mapping

• Mybatis provides the way of hump naming mapping, such as user in the database_ The ID field can be automatically mapped to the userId attribute. Then the SQL of the query can be changed as follows:

<select id='selectPatientInfos' resultType='com.xxx.domain.PatientInfo'>
    select user_id,dept_id,doc_id from patient_info;
  </select>

• How to open it? After integration with SpringBoot, it is actually very simple to open it. There are two ways: one is to open it in the configuration file, and the other is to open it in the configuration class.

Configuration file enable hump mapping

• Only in application Add the following line of code to the properties file:

mybatis.configuration.map-underscore-to-camel-case=true

Enable hump mapping in the configuration class [a brief understanding, which will be highlighted in the following source code chapters]

• This method requires you to have a certain understanding of the source code. As mentioned in the previous introductory tutorial, if Mybatis and Springboot are integrated with a starter, there must be an automatic configuration class. The automatic configuration class of Mybatis is MybatisAutoConfiguration. There is such a piece of code as follows:
  

• @The annotation ConditionalOnMissingBean means that the configuration will take effect only when there is no Bean object SqlSessionFactory in the IOC container; The line of code "apply configuration (factory)" is to create an org apache. ibatis. session. Configuration is assigned to SqlSessionFactoryBean. After analyzing the source code, it should be clear that it is nothing more than creating a SqlSessionFactory in the container and setting the properties. The following code:

    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //set up data sources
        sqlSessionFactoryBean.setDataSource(dataSource);
        //Set the location of the xml file
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATOIN));
        //Create Configuration
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // Enable hump naming mapping
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setDefaultFetchSize(100);
        configuration.setDefaultStatementTimeout(30);
        sqlSessionFactoryBean.setConfiguration(configuration);
        //Register typehandler with mybatis
        sqlSessionFactoryBean.setTypeHandlers(typeHandlers());
        return sqlSessionFactoryBean.getObject();
    }

• Note: if there is no special customization for SqlSessionFactory, you don't mind rewriting it, because it will automatically overwrite the configuration in the automatic configuration class.

resultMap mapping

• What is resultMap? Simply put, it is a Map like structure, which corresponds the fields in the database to the attribute fields in the JavaBean, so that one-to-one mapping can be achieved.
• How to write the above example using resultMap? As follows:

<!--Create a resultMap mapping-->
<resultMap id="patResultMap" type="com.xxx.domain.PatientInfo">
  <id property="userId" column="user_id" />
  <result property="docId" column="doc_id"/>
  <result property="deptId" column="dept_id"/>
</resultMap>

<!--use resultMap Mapping results to com.xxx.domain.PatientInfo this Bean in-->
<select id='selectPatientInfos' resultMap='patResultMap'>
    select user_id,dept_id,doc_id from patient_info;
  </select>

• In fact, it is very simple to create a < resultMap >, and then specify the resultMap with the < Select > tag.

• The attributes of < resultmap > are as follows:

  • id: uniquely identifies the resultMap and the same mapper Cannot duplicate in XML
  • Type: Specifies the type of JavaBean, which can be a full class name or an alias

• The attributes of the sub tag < result > are as follows:

  • column: the name of the field returned by SQL
  • Property: the name of the property in the JavaBean
  • javaType: the fully qualified name of a Java class or a type alias (for the built-in type alias, please 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.
  • JDBC type: 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 the requirement of JDBC, not MyBatis. If you are programming directly for JDBC, you need to specify this type for columns that can be null.
  • typeHandler: this property value is the fully qualified name of a type processor implementation class, or the type alias.
  • resultMap: the ID of the result map, which can map the associated nested result set to an appropriate object tree. It can be used as an alternative to using additional select statements.

summary

• The above lists three mapping methods, namely alias mapping, hump mapping and resultMap mapping.
• You think it's over? If the world is so simple, dream, ha ha!!!
  

Advanced result mapping

• MyBatis was created with the idea that databases cannot always be what you want or need. We hope that every database has a good third paradigm or BCNF paradigm, but they are not all that way. It would be great to have a database mapping mode that perfectly fits all applications, but it's a pity that it doesn't. And ResultMap is MyBatis's answer to this question.
• We know that there are one-to-one, many to one, one to many and many to many relationships in the database. How can this relationship be reflected and mapped successfully in Mybatis?

Association

• The association element handles a type of relationship. For example, in our example, an employee belongs to a department. Association result mapping works in much the same way as other types of mapping. You need to specify the target attribute name and the javaType of the attribute (MyBatis can infer it by itself in many cases). If necessary, you can also set the JDBC type. If you want to override the process of obtaining the result value, you can also set the type processor.
• The difference between associations is that you need to tell MyBatis how to load associations. MyBatis loads associations in two different ways:
  • Nested Select query: load the desired complex type by executing another SQL mapping statement.
  • Nested result mapping: use nested result mapping to handle duplicate subsets of connected results.
• First, let's look at the properties of this element. You will find that, compared with ordinary result mapping, it is only different in the "select" and "resultMap" attributes.
  • Property: the field or property mapped to the column result. If the JavaBean used to match has a property with the given name, it will be used.
  • javaType: the fully qualified name of a Java class or a type alias (for built-in type aliases, refer to the table above)
    JDBC type: JDBC type. You only need to specify the JDBC type on the columns that may perform insertion, update and deletion and allow null values
  • typeHandler: with this property, you can override the default type handler. This property value is the fully qualified name of a type processor implementation class, or a type alias.
    Column: the name of the column in the database, or the alias of the column. In general, this is the same as passing to {resultset The parameters of the getString (columnname) method are the same. Note: when using composite primary key, you can use the syntax of "column="{prop1=col1,prop2=col2} "to specify multiple column names passed to nested Select query statements. This will cause prop1 and prop2 to be set as parameter objects corresponding to the parameters of the nested Select statement.
  • Select: the ID of the mapping statement used to load the complex type attribute. It will retrieve data from the column specified by the column attribute and pass it to the target select statement as a parameter. Please refer to the following examples for details. Note: when using composite primary key, you can use the syntax of column="{prop1=col1,prop2=col2}" to specify multiple column names passed to nested select query statements. This will cause prop1 and prop2 as parameter objects to be set as parameters corresponding to nested select statements.
  • fetchType: optional. Valid values are ， lazy ， and ， eager. When a property is specified, the global configuration parameter lazyloading enabled is ignored in the mapping and the value of the property is used.

example

• For example, if an employee belongs to a department, the database table will add a department id to the employee table as a logical foreign key.
• Create employee JavaBean s

@Data
public class User {
	private Integer id;
	private String username;
	private String password;
	private Integer age;
  private Integer deptId;
  //department
	private Department department;   
}

• Department JavaBean

@Data
public class Department {
	private Integer id;
	private String name;
}

• Then we want to query all the User information and the Department information. The sql statement at this time is: select * from user u left join department d on u.department_id=d.id;. But if we use this statement to query in mybaits, what is the result type returned? If it is of User type, the query result will return data of department type, which will certainly not correspond to it. Here comes < resultmap >!!!

Associated nested Select query [can be ignored]

• How to query employees and their departments in Mybatis? The code is as follows:

<resultMap id="userResult" type="com.xxx.domain.User">
	<id column="id" property="id"/>
	<result column="password" property="password"/>
	<result column="age" property="age"/>
	<result column="username" property="username"/>
  <result column="dept_id" property="deptId"/>
  <!--Association query, select nested queries -->
  <association property="department" column="dept_id" javaType="com.xxx.domain.Department" select="selectDept"/>
</resultMap>

<!--Query employee-->
<select id="selectUser" resultMap="userResult">
  SELECT * FROM user WHERE id = #{id}
</select>

<!--Query Department-->
<select id="selectDept" resultType="com.xxx.domain.Department ">
  SELECT * FROM department WHERE ID = #{id}
</select>

• It's that simple. There are two select statements, one for loading employees and the other for loading departments.
• Although this method is very simple, it does not perform well on large data sets or large data tables. This problem is called N+1} query problem. Generally speaking, the N+1 query problem is like this:
  • You execute a separate SQL statement to get a list of results (that is + 1).
  • For each record returned from the list, you execute a select query statement to load the details (that is, N) for each record.
• This problem can cause hundreds of SQL statements to be executed. Sometimes, we don't want such consequences.

Associated nested result mapping [key]

• The < Association > tag can also directly nest the result mapping. At this time, the query of Mybatis is as follows:

<!-- definition resultMap -->
<resultMap id="UserDepartment" type="com.xxx.domain.User" >
	<id column="user_id" property="id"/>
	<result column="password" property="password"/>
	<result column="age" property="age"/>
	<result column="username" property="username"/>
  <result column="dept_id" property="deptId"/>

	<!--
		property: appoint User Corresponding department attribute name in
		javaType: Specify the type, which can be the full class name or alias
	 -->
	<association property="department" javaType="com.xx.domain.Department">
    <!--appoint Department The attribute mapping in can also be used here. It can be carried out separately and then used association Medium resultMap Attribute assignment-->
		<id column="id" property="id"/>
		<result column="dept_name" property="name"/>
	</association>
</resultMap>

<!-- 
	resultMap: Specify above resultMap of id Value of
 -->
 <select id="findUserAndDepartment" resultMap="UserDepartment">
 	select 
   u.id as user_id,
   u.dept_id,
   u.name,
   u.password,
   u.age,
   d.id,
   d.name as dept_name
   from user u left join department d on u.department_id=d.id
 </select>

summary

• So far, one type of association has been completed. You can complete it by learning to use < Association >.
• Note: the associated nested Select query is not recommended. N+1 is a major problem. Although Mybatis provides the function of delayed loading, it is still not recommended and is not commonly used in enterprise development.

collection

• There are many types of association.
• The attributes are similar to those in association and will not be repeated.
• For example, how to write SQL when querying all employees in a department? As follows:

select * from department d left join user u on u.department_id=d.id;

• The User entity class at this time is as follows:

@Data
public class User {
 private Integer id;
 private String username;
 private String password;
 private Integer age;
 private Integer deptId; 
}

• At this time, the Department entity class is as follows:

@Data
public class Department {
 private Integer id;
 private String name;
 private List<User> users;
}

• Similar to association, there are also two ways. We can use nested Select queries or map collections based on nested results of joins.

Nested Select query of set [can be ignored]

• Not very important. The query is as follows:

<resultMap id="deptResult" type="com.xxx.domain.Department">
  <!--appoint Department The attribute mapping in can also be used here. It can be carried out separately and then used association Medium resultMap Attribute assignment-->
		<id column="id" property="id"/>
		<result column="name" property="name"/>
  <!--
  ofType: Specify actual JavaBean Full type or alias of
  select: Specify nested select query
  javaType: The type of the collection can be left blank, Mybatis It can be inferred
-->
  <collection property="users" javaType="java.util.ArrayList" column="id" ofType="com.xxx.doamin.User" select="selectByDeptId"/>
</resultMap>

<select id="selectDept" resultMap="deptResult">
  SELECT * FROM department  WHERE ID = #{id}
</select>

<select id="selectByDeptId" resultType="com.xxx.domain.User">
  SELECT * FROM user WHERE dept_id = #{id}
</select>

• Note: there is an attribute ofType different from association. This attribute is very important. It is used to distinguish the type of JavaBean (or field) attribute from the type of collection storage.

Nested result mapping of sets [ key ]

• Now you may have guessed how the nested result mapping of the collection works -- except for the new ， ofType ， attribute, it is exactly the same as the associated.
• The Mybatis query is as follows:

<!--Departmental resultMap-->
<resultMap id="deptResult" type="com.xxx.domain.Department">
  <!--appoint Department The attribute mapping in can also be used here. It can be carried out separately and then used association Medium resultMap Attribute assignment-->
		<id column="dept_id" property="id"/>
		<result column="dept_name" property="name"/>
  <!--
  ofType: Specify actual JavaBean Full type or alias of
  resultMap: Name of the designated employee resultMap
-->
  <collection property="users" ofType="com.xxx.doamin.User" resultMap='userResult'/>
</resultMap>

<!--Employee resultMap-->
<resultMap id="userResult" type="com.xxx.domain.User">
    <id column="user_id" property="id"/>
   <result column="password" property="password"/>
   <result column="age" property="age"/>
   <result column="username" property="username"/>
</resultMap>

<select id="selectDeptById" resultType="com.xxx.domain.Department">
  select 
  d.id as dept_id,
  d.name as dept_name,
  u.id as user_id,
  u.password,
  u.name
  from department d left join user u on u.department_id=d.id
  where d.id=#{id}
</select>

last

After reading, you can leave a message below to discuss what you don't understand
Thank you for watching.
If you think the article is helpful to you, remember to pay attention to me and give me some praise and support!

Author: Bu Cai Chen

last

These materials hope to be a reference for Java development friends and avoid detours. The focus of this article is whether you have gained and grown, and the rest are not important. I hope readers can keep this in mind.

Share another wave for free My Java topic interview real topic + video learning explanation + Java advanced learning book

r u on u.department_id=d.id
where d.id=#{id}

## last
 You can leave a message below to discuss what you don't understand after reading.
Thank you for watching.
If you think the article is helpful to you, remember to pay attention to me and give me some praise and support!

>Author: Bu Cai Chen
# last

I hope these materials will be helpful to you Java Development friends have reference and avoid detours. The focus of this article is whether you have harvest and growth. The rest is not important. I hope readers can keep this in mind.

[External chain picture transfer...(img-9hdgxtwr-1623570989693)]

[External chain picture transfer...(img-wE7pKydm-1623570989695)]

**Share another wave for free[my Java Real topic interview+Video learning details+Java Advanced learning books](https://docs.qq.com/doc/DSmxTbFJ1cmN1R2dB)**

In fact, there are all the interview topics mentioned in the first 25 interview topics. All the interviews mentioned above+I've been doing all kinds of notes and materials for more than three months. It's really not easy to collect and sort them out. There are a lot of my own knowledge summary. It is because it is very troublesome that I am interested in the above learning review materials,