Recently, my project has used MyBatis to handle one-to-many mapping relationship. The following two methods use nested query methods for collections. Let’s learn these two methods carefully.
Aggregation elements are used to deal with "one-to-many" relationships. You need to specify the attributes of the mapping Java entity class, the javaType of the attribute (usually ArrayList); the type of the object in the list of the object (Java entity class); the column name of the corresponding database table;
Different situations need to tell MyBatis how to load an aggregation. MyBatis can be loaded in two ways:
select: Execute an additional mapping SQL statement to return a Java entity type. More flexible but will execute multiple nested SQL statements.
resultMap: Use a nested result map to process query result sets through join and map to Java entity type.
The two loading formats are as follows:
Nested queries for collections (select)
<collection property="Java attribute name" ofType="Another Java class name" javaType="ArrayList" column="Associate primary keyID(For nested queriesSQLPassing parameters in statements，Multiple separated by commas)" select="Another select map SQL ID"/> <select parameterType="int" resultType="Another Java class name" > SQLStatement </select>
<resultMap type="Blog">
<collection property="posts" javaType=”ArrayList” column="blog_id" ofType="Post" select="selectPostsForBlog"/>
</resultMap>
<select parameterType="int" resultMap="blogResult">
SELECT * FROM BLOG WHERE ID = #{id}
</select>
<select parameterType="int" resultType="Author">
SELECT * FROM POST WHERE BLOG_ID = #{id}
</select>
Note: The value of the column attribute must be the same as the column name in the corresponding SQL query statement. MyBatis will use the value of the column queryed by the first SQL statement to use the entry parameters of the aggregated SQL mapping statement. Since the value of each column that is queried from the first SQL statement will be used to execute another SQL statement, the aggregated SQL statement will be executed multiple times.
Although this method is simple, it is not satisfactory for large data sets or list queries. This problem is called "N+1 Selects Problem". To summarize, the N+1 selection problem arises like this:
You execute a single SQL statement to get a list of records ("+1").
For each record in the list, a joint select statement is executed to load more detailed information ("N") of each record.
This problem can lead to the execution of thousands of SQL statements and is therefore not always desirable.
In the example above, MyBatis can use lazy loading of these queries, so these queries can immediately save overhead. However, if you iterate over accessing nested data immediately after loading a list, this will call all the lazy loading, so the performance will get very bad.
In view of this, there is another way.
Nested Results for Collection
<resultMap type="Java class name"> <collection property="Java attribute name" ofType="Another Java class name" javaType="ArrayList" resultMap="Other resultMap ID"/> </resultMap> <resultMap="Other resultMap ID" type="Another Java class name"> <id property="id" column="Associated Primary Key ID"/> .... </resultMap>
<select parameterType="int" resultMap="blogResult">
select
as blog_id,
as blog_title,
B.author_id as blog_author_id,
as post_id,
as post_subject,
as post_body,
from Blog B
left outer join Post P on = P.blog_id
where = #{id}
</select>
Similarly, we connect the Blog and Post tables together, and ensure that the column label names are unique and unambiguous when mapping. How easy it is to map a collection of Blog and Post together now:
<resultMap 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, the id element is very important.
If you want the result map to be more reusable, you can use the following:
<resultMap type="Blog"> <id property="id" column="blog_id" /> <result property="title" column="blog_title"/> <collection property="posts" ofType="Post" resultMap="blogPostResult"/> </resultMap> <resultMap type="Post"> <id property="id" column="post_id"/> <result property="subject" column="post_subject"/> <result property="body" column="post_body"/> </resultMap>
Note: The value of the column attribute must be the same as the column name of the corresponding SQL query statement.
This is the article about the two implementation methods of collection gathering of mybatis resultMap. For more related contents of mybatis resultMap collection, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!