The example explains the Java MyBatis Framework to the Data Association query in MySQL _java

MyBatis provides advanced associative query capabilities that make it easy to map the result sets obtained by a database to a defined Java Bean. Here's an example to show how mybatis is dealing with complex mappings of common one-to-many and many-to-many relationships.
Design a simple blog system, a user can open more than one blog, in the blog can publish articles, allow comments, can be tagged for articles. The blog system is mainly composed of the following tables:
Author Table: Author information sheet, record author's information, username and password, mailbox, etc.
Blog table: Blogs table, an author can open multiple blogs, that is, the relationship between author and blog is more than a pair.
Post table: The article records the table, records the article publication time, the title, the body and so on information; a blog can have a lot of articles, blog and post is a one-to-many relationship.
Comments Table: Article comment form, record article comment, an article can have many comments: Post and comments correspondence is one-to-many.
Tag table: Label table, indicating the classification of the article label, an article can have more than one label, and a tag can be applied to different articles, so the relationship between tag and post is many-to-many relationship; (a many-to-many relationship between tag and post is represented by the Post_tag table)
Post_tag table: Record the correspondence between the article and the label.

In general, we create the JavaBean (or Pojo) corresponding to the structure of each table to complete the basic CRUD operations on the table.

The above JavaBean definition of a single table sometimes does not meet the requirements of the business. In business, a blog object should have its author's information and a list of articles, as shown in the following illustration:

If you want to get an instance of such a class, at least a few steps:
1. Through the blog ID to query blog information, the query to the blogID and title to the blog object;
2. According to the query to the blog information in the Authorid to author table to obtain the corresponding author information, get author object, and then assign to the blog object;
3. According to the blogId to the Post table query the corresponding Post article list, the list <Post> object to the blog object;
In this case, at least three queries are called at the bottom of the list, see the following code:

 * * * Get Bloginfo object via blogID/public static bloginfo ordinaryqueryontest (String Blog 
 ID) {BigDecimal id = new BigDecimal (blogId); 
 sqlsession session = Sqlsessionfactory.opensession (); 
 Bloginfo bloginfo = new Bloginfo (); 1. According to blogID query blog object, set the value to bloginfo blog blog = (blog) session.selectone (" 
 Com.foo.bean.BlogMapper.selectByPrimaryKey ", id); 
 Bloginfo.setblogid (Blog.getblogid ()); 
  
 Bloginfo.settitle (Blog.gettitle ()); 2. According to the Authorid in the blog, enter the database query Author information, set the results to the Bloginfo object Author Author = (Author) session.selectone (" 
 Com.foo.bean.AuthorMapper.selectByPrimaryKey ", Blog.getauthorid ()); 
  
 Bloginfo.setauthor (author); 3. Query posts object, set into Bloginfo list posts = session.selectlist ("Com.foo.bean.PostMapper.selectByBlogId", Blog.getblogid 
 ()); 
 Bloginfo.setposts (posts); 
 Prints an object in the form of a JSON string Jsonobject object = new Jsonobject (bloginfo); 
 System.out.println (Object.ToString ()); 
return bloginfo; } 

From the above code can be seen, want to get a Bloginfo object more trouble, a total of three times to call the database query, get the information needed, and then assemble the Bloginfo object.

Nested statement Query
MyBatis provides a mechanism called nested statement queries that can greatly simplify the above operation, adding configuration and code as follows:

<resultmap type= "Com.foo.bean.BlogInfo" id= "Bloginfo" > <id column= "blog_id" property= 
 "BlogId"/> 
 <result column= "title" property= "title"/> <association property= "Author" column= "blog_author_id" 
  javatype= "Com.foo.bean.Author" select= "Com.foo.bean.AuthorMapper.selectByPrimaryKey" > 
 </ association> 
 <collection property= "posts column=" blog_id "oftype=" Com.foo.bean.Post "select= 
  " Com.foo.bean.PostMapper.selectByBlogId "> 
 </collection> 
</resultMap> 
 
<select id= "Querybloginfobyid" resultmap= "Bloginfo" parametertype= "Java.math.BigDecimal" > 
 SELECT 
 b.blog_id, 
 B.title, 
 b.author_id as blog_author_id from 
 Louluan. BLOG B 
 where b.blog_id = #{blogid,jdbctype=decimal} 
</select> 

 
 * * * Get Bloginfo object via 
 blogId 
/public static Bloginfo Nestedqueryontest (String blogId) 
{ 
 BigDecimal id = new BigDecimal (blogId); 
 sqlsession session = Sqlsessionfactory.opensession (); 
 Bloginfo bloginfo = new Bloginfo (); 
 Bloginfo = (bloginfo) session.selectone ("com.foo.bean.BlogMapper.queryBlogInfoById", id); 
 Jsonobject object = new Jsonobject (bloginfo); 
 System.out.println (Object.ToString ()); 
 return bloginfo; 
} 

The preceding query can be fully implemented through the above code. Here we only need bloginfo = (bloginfo) session.selectone ("com.foo.bean.BlogMapper.queryBlogInfoById", id) in the code; A complex Bloginfo object can be obtained by one sentence.

The principle of nested statement query
in the above code, MyBatis performs the following process:
1. Execute the Querybloginfobyid corresponding statement to obtain the resultset result set from the blog table;
2. Remove the next valid record from the ResultSet, and then build the corresponding Bloginfo object from the recorded data according to the mapping specification defined by RESULTMAP.
3. When you want to assign value to the author attribute in Bloginfo, there is an associated query, at this time MyBatis will execute the SELECT query statement, get the result of return, set the result to Bloginfo author attribute;
4. Similar processes are also available when assigning values to bloginfo posts.
5. Repeat the 2 steps until resultset. Next () = = false;
The following is a schematic diagram of the Bloginfo object construction assignment process:

One very good thing about this associative nested query is that you can reuse a SELECT statement to construct complex objects with a combination of simple SELECT statements. The two SELECT statements nested above Com.foo.bean.AuthorMapper.selectByPrimaryKey and com.foo.bean.PostMapper.selectByBlogId can be used independently.

N+1 problem
Its disadvantages are also more obvious: the so-called n+1 problem. The associated nested query displays a result set, and then associates the query against each record in the result set.
Now assume that the nested query on one (that is, Resultmap inside a association tag), the result set of the query returned the number of bars N, then the associated query will be executed n times, plus its own return result set query 1 times, a total need to access the database n+1 times. If n is relatively large, such database access consumption is very large! Therefore, users who use this nested statement query must consider careful consideration to ensure that n values are not very large.
Take the example above, the SELECT statement itself returns the result set of the Com.foo.bean.BlogMapper.queryBlogInfoById bar number 1, and because it has two associated statement queries, it needs a total Access database 1* (1+1) = 3 database.

Nested result Query
queries for nested statements can cause database access to be erratic, potentially affecting performance. MyBatis also supports a query that nested results: that is, for a one-to-many, Many-to-many, many-to-many query, mybatis through a joint query, the results from the database for a one-time detection, and then according to its one-to-many, Many-to-many, many-to-many relationship and Resultmap in the configuration, Transform the results to build the objects that are needed.
Redefining the result mappings for Bloginfo Resultmap

<resultmap type= "Com.foo.bean.BlogInfo" id= "Bloginfo" > <id column= "blog_id" property= "BlogId"/> <resu Lt column= "title" property= "title"/> <association property= "Author" column= "blog_author_id" Com.foo.bean.Author "> <id column=" author_id "property=" Authorid "/>" <result column= "user_name" property= "  
  UserName "/> <result column= password" property= "password"/> <result "email" column= "email" property= <result column= "biography" property= "biography"/> </association> <collection property= "Posts" Colum N= "blog_post_id" oftype= "Com.foo.bean.Post" > <id column= "post_id" property= "PostID"/> <result column= "bl  og_id "property=" BlogId "/> <result column=" create_time "property=" Createtime "/> <result column=" Subject " property= "Subject"/> <result column= "Body" property= "the Body" and "the/>" the <result "column=" Draft "Property=" draft 
 T </collection> </resUltmap> 

 

The corresponding SQL statements are as follows:

<select id= "Queryallbloginfo" resultmap= "Bloginfo" > 
 select 
  b.blog_id, 
  b.title 
  As blog_author_id, 
  a.author_id, 
  a.user_name, 
  A.password, 
  a.email, a.biography 
  , 
  p.post_ IDS, 
  p.blog_id as blog_post_id, 
 p.create_time, 
  p.subject, 
  p.body, 
  p.draft from 
BLOG B Left 
OUTER JOIN AUTHOR A 
 On b.author_id = a.author_id left 
OUTER JOIN POST P on 
 p.blog_id = b.blog_id 
</select> 

 
 * * Get all the information for all 
 blogs 
/public static Bloginfo Nestedresultontest () 
{ 
 sqlsession session = Sqlsessionfactory.opensession (); 
 Bloginfo bloginfo = new Bloginfo (); 
 Bloginfo = (bloginfo) session.selectone ("Com.foo.bean.BlogMapper.queryAllBlogInfo"); 
 Jsonobject object = new Jsonobject (bloginfo); 
 System.out.println (Object.ToString ()); 
 return bloginfo; 
} 

Execution steps for nested result queries:
1. According to the table's correspondence, carries on the join operation, obtains to the result set;
2. Based on the information of the result set and the RESULTMAP definition information of the bloginfo, the result set is assembled, assigned in memory and constructed bloginfo;
3. Returns the list<bloginfo> result of the constructed result.
For the associated result query, if it is a many-to-many relationship, the property= "author" column= "blog_author_id" javatype= "Com.foo.bean.Author" through the form like <association > Configuration, MyBatis will take the data from the memory by the AUTHOR_ID value corresponding to the column property, and encapsulate it into author object;
If it is a one-to-many relationship, such as the relationship between blog and post, through the form such as <collection property= "posts" column= "blog_post_id" oftype= "Com.foo.bean.Post" > Configuration, MyBatis through the blog_id to take the Post object in memory, encapsulated into list<post>;
For queries associated with results, you only need to query the database once, and then consolidate and assemble the results in memory.
The above is through the query blog All information to demonstrate One-to-many and Many-to-many mapping object processing.

PS: Example of Self association mapping:
entity Classes

public class Module {private int id; 
 Private String key; 
 private String name; 
 Private Module Parentmodule; 
 Private list<module> childrenmodules; 
 Private String URL; 
 private int sort; 
 Private String Show; 
 
 Private String del; 
 public int getId () {return id; 
 The public void setId (int id) {this.id = ID; 
 Public String Getkey () {return key; 
 public void Setkey (String key) {this.key = key; 
 Public String GetName () {return name; 
 public void SetName (String name) {this.name = name; 
 Public Module Getparentmodule () {return parentmodule; 
 The public void Setparentmodule (Module parentmodule) {this.parentmodule = Parentmodule; 
 Public String GetUrl () {return URL; 
 public void SetUrl (String url) {this.url = URL; 
 public int Getsort () {return sort; 
 public void Setsort (int sort) {this.sort = sort; 
 Public String Getshow () {return show; 
 }
 public void Setshow (String show) {this.show = Show; 
 Public String Getdel () {return del; 
 public void Setdel (String del) {this.del = del; 
 Public list<module> Getchildrenmodules () {return childrenmodules; 
 public void Setchildrenmodules (list<module> childrenmodules) {this.childrenmodules = Childrenmodules; 

 } 
}

XML code:

<mapper namespace= "Com.sagaware.caraccess.mapper.ModuleMapper" > <resultmap type= "Module" id= "  
  Moduleresultmap "> <id property=" id "column=" module_id "/> <result" key "property=" column= "Module_key <result property= "name" column= "Module_name"/> <result property= "url" column= "Module_url"/> <resu Lt property= "Sort" column= "Module_sort"/> <result property= "show" column= "Module_show"/> <result propert Y= "del" column= "Module_del"/> <!--query Parent module--> <association property= "Parentmodule" column= "Module_par" ent_id "select=" Getmodulesbyid "/> <!--query Sub module--> <collection property=" childrenmodules "column=" mod ule_id "select=" getchildrenmodues "/> </resultMap> <select id=" GetModules "parametertype=" String "R esultmap= "Moduleresultmap" > select * from Tb_module where module_id=2 </select> <select id= "Getmodul Esbyid "parametertype=" int "resultmap="Moduleresultmap "> select * from tb_module where module_id = #{module_id} </select> <select id=" Getch Ildrenmodues "parametertype=" int "resultmap=" Moduleresultmap "> select * from tb_module where module_parent_id = #{mo 
 DULE_ID} </select> </mapper>