mybatis provides advanced association query functions, which can easily map the result sets obtained by the database to defined Java Beans. The following is an example to show how Mybatis deals with common one-to-many and many-to-one relationship complex mappings.
Design a simple blog system where a user can open multiple blogs, post articles in the blog, allow comments, and tag articles. The blog system mainly consists of the following tables:
Author table: Author information table, record the author's information, username and password, email address, 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, recording the post publication time, title, text and other information; there can be many articles under a blog, and the relationship between Blog and Post is one-to-many.
Comments table: Article comment table, record article comments, 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 the article. An article can have multiple tags, and a tag can be applied to different articles, so the relationship between Tag and Post is a many-to-many relationship; (The many-to-many relationship between Tag and Post is reflected through the Post_Tag table)
Post_Tag table: Records the correspondence between articles and tags.
Generally speaking, we will create a corresponding JavaBean (or Pojo) based on the structure of each table to complete the basic CRUD operation of the table.
The above JavaBean definition of a single table sometimes cannot meet business needs. In business, a Blog object should have information about its author and a list of articles, as shown in the figure below:
If you want to get an instance of such a class, you need at least a few steps:
1. Query the Blog information through the Blog id in the Blog table, and assign the query blogId and title to the Blog object;
2. According to the query 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. According to blogId, query the corresponding Post article list in the Post table and assign the List<Post> object to the Blog object;
In this way, at least three query statements are called at the bottom layer. Please see the following code:
/* * Get the BlogInfo object through blogId*/ public static BlogInfo organicQueryOnTest(String blogId) { BigDecimal id = new BigDecimal(blogId); SqlSession session = sqlSessionFactory.openSession(); BlogInfo blogInfo = new BlogInfo(); //1. Query the Blog object according to blogid and 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 to query Author information and set the result to the blogInfo object Author author = (Author)session.selectOne("com.foo.bean.AuthorMapper.selectByPrimaryKey",blog.getAuthorId()); blogInfo.setAuthor(author); //3. Query the posts object and set it into blogInfo List posts = session.selectList("com.foo.bean.PostMapper.selectByBlogId",blog.getBlogId()); blogInfo.setPosts(posts); //Print the object in the form of a JSON string JSONObject object = new JSONObject(blogInfo); System.out.println(object.toString()); return blogInfo; }From the above code, we can see that it is more troublesome to get a BlogInfo object. You have to call the database query three times in total, 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, and add 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 through 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 previous query can be fully realized through the above code. Here we only need blogInfo = (BlogInfo)session.selectOne("com.foo.bean.BlogMapper.queryBlogInfoById",id); in the code, we can get a complex blogInfo object.
The principle of nested statement query
In the above code, Mybatis will perform the following process:
1. First execute the corresponding statement of queryBlogInfoById to obtain the ResultSet result set from the Blog table;
2. Take out the next valid record of the ResultSet, and then build the corresponding BlogInfo object based on the mapping specifications defined by the resultMap.
3. When you want to assign the author attribute in BlogInfo, you find 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 the posts of BlogInfo, similar processes are also present.
5. Repeat 2 steps until ResultSet. next () == false;
The following is a schematic diagram of the blogInfo object construction assignment process:
A very good function of this kind of associated nested query is that it can reuse the select statement and construct complex objects through the combination between 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 quite obvious: the so-called N+1 problem. The associated nested query shows a result set, and then the associated query is performed based on each record of this result set.
Now suppose there is only one nested query (that is, there is an association tag inside the resultMap), and the number of entries returned by the query is N, then the associated query statement will be executed N times, plus the query itself returns the result set to query once, and a total of N+1 times is required to access the database. If N is relatively large, such a database access consumption is very large! Therefore, users who use this kind of nested statement query must consider carefully to ensure that the N value is not very large.
As an example, the select statement itself will return a result set with com.foo.bean.BlogMapper.queryBlogInfoById with 1. Since it has two related statement queries, it needs to access the database 1* (1+1)=3 times in total.
Nested result query
Querying nested statements can cause uncertain database access times, which may affect performance. Mybatis also supports a kind of nested results query: that is, for the query of one-to-many, many-to-many, and many-to-one situations, Mybatis searches the results from the database at one time through joint query, and then converts the results based on its one-to-many, many-to-one, many-to-many relationship and configuration in ResultMap, and builds the required objects.
Redefine the result map of BlogInfo
<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"> <id column="author_id" property="authorId"/> <result column="user_name" property="userName"/> <result column="password" property="password"/> <result column="email" property="email"/> <result column="biography" property="biography"/> </association> <collection property="posts" column="blog_post_id" ofType="com.foo.bean.Post"> <id column="post_id" property="postId"/> <result column="blog_id" property="blogId"/> <result column="create_time" property="createTime"/> <result column="subject" property="subject"/> <result column="body" property="body"/> <result column="draft" property="draft"/> </collection> </resultMap>
The corresponding SQL statements are as follows:
<select id="queryAllBlogInfo" resultMap="BlogInfo"> SELECT B.BLOG_ID, B.TITLE, B.AUTHOR_ID AS BLOG_AUTHOR_ID, A.AUTHOR_ID, A.USER_NAME, A.PASSWORD, A.EMAIL, A.BIOGRAPHY, P.POST_ID, 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 information about 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 query:
1. Perform join operations based on the corresponding relationship of the table to obtain the result set;
2. According to the result set information and the resultMap definition information of BlogInfo, assemble and assign the returned result set in memory to construct BlogInfo;
3. Return the constructed result List<BlogInfo> result.
For the associated result query, if it is a many-to-one relationship, it is configured by the <association property="author" column="blog_author_id" javaType="com.foo.bean.Author">. Mybatis will fetch data from memory through the author_id value corresponding to the column property and encapsulate it into an Author object;
If it is a one-to-many relationship, just like the relationship between Blog and Post, it is configured by the <collection property="posts" column="blog_post_id" ofType="com.foo.bean.Post">, MyBatis uses blog_Id to fetch Post objects in memory and encapsulate them into List<Post>;
For querying associated results, you only need to query the database once, and then the integration and assembly of the results are all placed in memory.
The above is to demonstrate one-to-many and many-to-one mapping object processing by querying all Blog information.
ps: Example of self-association mapping:
Entity Class
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; } 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; } 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 property="key" column="module_key"/> <result property="name" column="module_name"/> <result property="url" column="module_url"/> <result property="sort" column="module_sort"/> <result property="show" column="module_show"/> <result property="del" column="module_del"/> <!-- Query the parent module--> <association property="parentModule" column="module_parent_id" select="getModulesById" /> <!-- Query the submodule--> <collection property="childrenModules" column="module_id" select="getChildrenModules" /> </resultMap> <select id="getModules" parameterType="String" resultMap="moduleResultMap"> select * from tb_module where module_id=2 </select> <select id="getModulesById" parameterType="int" resultMap="moduleResultMap"> select * from tb_module where module_id = #{module_id} </select> <select id="getChildrenModues" parameterType="int" resultMap="moduleResultMap"> select * from tb_module where module_parent_id = #{module_id} </select> </mapper>