An example explains the association query of data in MySQL by Java’s mybatis framework

Mybatis provides advanced association query function, which can easily map the result set obtained from the database to the defined java bean. The following is an example to show how mybatis handles common one-to-many and many to one complex mappings. Design a simple blog system, a user can open multiple blogs, publish articles in the blog, allow comments, and tag articles. The blog system is mainly composed of the following tables: author table: author information table, recording the author's information, user name and password, email, etc. Blog table: blog table. An author can open multiple blogs, that is, the relationship between author and blog is one to many. Post table: article record table, which records the publication time, title, text and other information of the article; There can be many articles under a blog. The relationship between blog and post is one to many. Comments table: the article comments table records the comments of articles. An article can have many comments: the correspondence between post and comments is one to many. Tag table: tag table, which represents the tag classification of articles. An article can have multiple tags, and one tag can be applied to different articles. Therefore, the relationship between tag and post is many to many; (the many to many relationship between tag and post is reflected in the post_tag table) post_tag table: records the corresponding relationship between articles and tags.

In general, we will create a corresponding JavaBean (or POJO) according to the structure of each table to complete the basic crud operation on the table.

The above JavaBean definition of a single table sometimes can not meet the business needs. In business, a blog object should have the information of its author and a list of articles, as shown in the following figure:

If you want to get an instance of such a class, there are at least the following steps: 1 Query the blog information in the blog table through the ID of the blog, and assign the queried blogid and title to the blog object; 2. According to the queried authorid in the blog information, go to the author table to obtain the corresponding author information, obtain the author object, and then assign it to the blog object; 3. Query the corresponding post article list in the post table according to the blogid, and assign the list < post > object to the blog object; In this case, the query statement should be called at least three times at the bottom. Please see the following code:

As can be seen from the above code, it is troublesome to obtain a bloginfo object. You need to call the database query three times to get the required information, and then assemble the bloginfo object.

Nested statement query mybatis provides a mechanism called nested statement query, which can greatly simplify the above operations. The configuration and code are as follows:

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

Principle of nested statement query in the above code, mybatis will execute the following process: 1 First execute the statement corresponding to querybloginfobyid to obtain the resultset result set from the blog table; 2. Take out the next valid record in the resultset, and then build a corresponding bloginfo object through the data of this record according to the mapping specification defined in the resultmap. 3. When assigning a value to the author attribute in bloginfo, it is found that there is an associated query. At this time, mybatis will first execute the select query statement to get the returned result, and set the result to the author attribute of bloginfo; 4. When assigning values to posts of bloginfo, the process is similar to that described above. 5. Repeat step 2 until resultset next () == false； The following is the schematic diagram of the construction and assignment process of bloginfo object:

This kind of associated nested query has a very good function: you can reuse select statements and construct complex objects through the 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.

The disadvantages of N + 1 problem are also obvious: the so-called n + 1 problem. The associated nested query displays a result set, and then performs the associated query according to each record of the result set. Now assume that the nested query is just one (that is, there is an association tag inside resultmap). Now the number of returned results in the query result set is n, then the associated query statement will be executed n times, plus one query in the returned result set, and the database needs to be accessed n + 1 times. If n is large, such database access consumption is very large! Therefore, users using this nested statement query must consider it Consider carefully to ensure that the n value is not very large. Taking the above example as an example, the select statement itself will return com foo. bean. BlogMapper. Querybloginfobyid is a result set with a number of 1. Because it has two associated query statements, it needs to access the database 1 * (1 + 1) = 3 times.

Nested result query the query of nested statement will lead to the uncertainty of database access times, which may affect the performance. Mybatis also supports a query of nested results: for the query of one to many, many to many and many to one, mybatis finds the results from the database at one time through joint query, and then converts the results and constructs the required objects according to its one to many, many to one and many to many relationship and the configuration in resultmap. Redefine the result map of bloginfo resultmap

The corresponding SQL statements are as follows:

Execution steps of nested result query: 1 Perform the join operation according to the corresponding relationship of the table to obtain the result set; 2. Assemble and assign the returned result set in memory according to the result set information and the resultmap definition information of bloginfo to construct bloginfo; 3. Return the constructed result list < bloginfo > result. For the associated result query, if it is a many-to-one relationship, configure it through the form such as < association property = "author" column = "blog_author_id" javatype = "com. Foo. Bean. Author" > and mybatis will use the author corresponding to the column property_ The ID value is used to fetch data from memory and encapsulated into an author object; If it is a one to many relationship, such as the relationship between blog and post, it is configured through the form of < collection property = "posts" column = "blog_post_id" oftype = "com. Foo. Bean. Post" > and mybatis through blog_ ID to get the post object from memory and package it into list < post >; For the query of associated results, you only need to query the database once, and then the integration and assembly of results are all placed in memory. The above demonstrates one to many and many to one mapping object processing by querying all the information of blog.

PS: self association mapping example: entity class