resultMap mapping

• What is resultMap? Simply put, it is a Map like structure that maps 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 (refer to the table above for built-in type aliases). If you map to a JavaBean, MyBatis can usually infer the type. However, if you map to a HashMap, you should explicitly specify the javaType to ensure that the behavior is consistent with the expectation.
  • 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 a JDBC requirement, not a MyBatis requirement. 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 a 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. The 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

• association element processing has a type of relationship. For example, in our example, an employee belongs to a department. association result mapping works like other types of mapping. You need to specify the target attribute name and the javaType of the attribute (often MyBatis can infer it by itself). 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: using 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 and is passed 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 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 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 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, picked up 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

• Collections, as the name suggests, deal with associations that have many types.
• The attributes are similar to those in association and will not be repeated.
• For example, how to write SQL for 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 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, picked up separately, and then used association Medium resultMap Attribute assignment-->
		<id column="id" property="id"/>
		<result column="name" property="name"/>
  <!--
  ofType: Specify the 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}


## epilogue

Xiaobian is also very touched. If he has been in small and medium-sized companies and has not been in contact with large-scale Internet architecture design, it may be difficult to reach the technical and cognitive height of senior architects only by reading books. Learning from powerful people is the most effective way to reduce time exploration and energy waste.

The industry we choose has to continue to learn and eat youth food.

Although you may often see that programmers earn hundreds of thousands a year, after all, not most of them have the aura of famous schools or large enterprises such as Alibaba and Huawei. Older people are more likely to be laid off.

**[CodeChina Open source project: [first tier big factory] Java Analysis of interview questions+Core summary learning notes+Latest explanation Video]](https://codechina.csdn.net/m0_60958482/java-p7)**

Share a wave of learning materials compiled by Xiaobian!

To everyone who wants to learn Java Little buddy, used to improve yourself.

![Insert picture description here](https://img-blog.csdnimg.cn/img_convert/ac4ca613fb2ff57bc54f486886349feb.png)

It is said that programmers earn hundreds of thousands a year, but after all, not most of them have the aura of famous schools or large enterprises such as Alibaba and Huawei. Older people are more likely to be laid off.

**[CodeChina Open source project: [first tier big factory] Java Analysis of interview questions+Core summary learning notes+Latest explanation Video]](https://codechina.csdn.net/m0_60958482/java-p7)**

Share a wave of learning materials compiled by Xiaobian!

To everyone who wants to learn Java Little buddy, used to improve yourself.

[External chain picture transfer...(img-45XGAJsV-1630909088844)]

> This article ends here. My favorite friends can help me with some praise and comments. Thank you for your support!