IBatis (MyBatis): Handling Joins: Advanced Result Mapping, Association, Collections, N+1 Select Problem
This tutorial will present examples using advanced result mappings, how to handle mappings with association, collections, the n+1 problem, and more.
Join the DZone community and get the full member experience.
Join For FreeThis tutorial will walk you through how to setup iBatis (MyBatis) in a simple Java project and will present examples using advanced result mapings, how to hadle mappings with association, collections, n+1 problem and will show how to configure these relationships using XML configuration and annotations.
Pre-Requisites
For this tutorial I am using:
IDE: Eclipse (you can use your favorite one)
DataBase: MySQL
Libs/jars: Mybatis, MySQL conector and JUnit (for testing)
This is how your project should look like:
Sample Database
Please run the script into your database before getting started with the project implementation. You will find the script (with dummy data) inside the sql folder.
1 – POJOs – Beans
I represented the beans here with a UML model, but you can download the complete source code in the end of this article.
The goal of this post is to demonstrate how to retrieve all the blog information from the database, but as you can see, the class Blog contains an association (Author) and a collection of Posts (and it contains a collection of Tags). And we are going to try to retrieve all this information at once.
So we are going to demonstrate One-to-one, one-to-many and many-to-many relationships using iBatis/Mybatis.
2 – Advanced Result Mapping
The result map on the code above is an advanced result mapping. As I already mentioned on previous posts, the resultMap element is the most important and powerful element in MyBatis.
MyBatis was created with one idea in mind: Databases aren’t always what you want or need them to be. While we’d love every database to be perfect 3rd normal form or BCNF, they aren’t. And it would be great if it was possible to have a single database map perfectly to all of the applications that use it, it’s not. Result Maps are the answer that MyBatis provides to this problem.
The resultMap element has a number of sub-elements and a structure worthy of some discussion. The following is a conceptual view of the resultMap element.
- constructor – used for injecting results into the constructor of a class upon instantiation
- id – an ID result; flagging results as ID will help improve overall performance
- result – a normal result injected into a field or JavaBean property
- association – a complex type association; many results will roll up into this type
- collection – a collection of complex types
- discriminator – uses a result value to determine which resultMap to use.
Best Practice: Always build ResultMaps incrementally. Unit tests really help out here. If you try to build a gigantic resultMap like the one above all at once, it’s likely you’ll get it wrong and it will be hard to work with. Start simple, and evolve it a step at a time. And unit test! The downside to using frameworks is that they are sometimes a bit of a black box (open source or not). Your best bet to ensure that you’re achieving the behaviour that you intend, is to write unit tests. It also helps to have them when submitting bugs.
Our goal is to write the following result map:
But let’s take a step at the time. We are going to start retrieving only the Blog data, so our initial result map and query is going to look like this:
So far, so good. Let’s take another step.
Association
Now let’s also try to retrieve the Author data.
The association element deals with a “has-one” type relationship. For example, in our example, a Blog has one Author. An association mapping works mostly like any other result. You specify the target property, the column to retrieve the value from, the javaType of the property (which MyBatis can figure out most of the time), the jdbcType if necessary and a typeHandler if you want to override the retrieval of the result values.
Where the association differs is that you need to tell MyBatis how to load the association. MyBatis can do so in two different ways:
- Nested Select: By executing another mapped SQL statement that returns the complex type desired.
- Nested Results: By using nested result mappings to deal with repeating subsets of joined results.
We are going to take a look at the Nested Select first.
Here is our resultMap with Author association.
Take a look at the select=”selectAuthor” atribute. This means MyBatis is going to execute the author select statment to retrieve all the authors that belong to the blog. To make the relationship between blog and author we specify the column=”idBlog”, so we can filter the authors list.
Note that we set the javaType=”Author”. We are using an Alias (remember?). This is because the columns we are retrieving from database match with Author atributes, so we do not need to specify a resultMap for author.
That’s it. We have two select statements: one to load the Blog, the other to load the Author, and the Blog’s resultMap describes that the “selectAuthor” statement should be used to load its author property.
All other properties will be loaded automatically assuming their column and property names match.
While this approach is simple, it will not perform well for large data sets or lists. This problem is knownas the “N+1 Selects Problem”. In a nutshell, the N+1 selects problem is caused like this:
- You execute a single SQL statement to retrieve a list of records (the “+1”).
- For each record returned, you execute a select statement to load details for each (the “N”).
This problem could result in hundreds or thousands of SQL statements to be executed. This is notalways desirable.The upside is that MyBatis can lazy load such queries, thus you might be spared the cost of thesestatements all at once. However, if you load such a list and then immediately iterate through it toaccess the nested data, you will invoke all of the lazy loads, and thus performance could be very bad.
We are going to show how to avoid the N+1 Select Problem later.
Collection
We are retrieving Blog and Author information from database. So we have to retrieve the Post information now. And a Blog contains a list of Posts, and a Post contains a list of Tags. We are dealing with two relationships here: first one is a one-to-many (Blog-Post) and the second one is a many-to-many (Post-Tag). We are going to show you how to do it.
We are also going to use a Nested Select to retrieve Posts.
Let’s take a look at the resultMap with Post collection:
The collection element works almost identically to the association. In fact, it’s so similar, to document the similarities would be redundant. So let’s focus on the differences.To continue with our example above, a Blog only had one Author. But a Blog has many Posts.
To map a set of nested results to a List like this, we use the collection element. Just like the association element, we can use a nested select, or nested results from a join.
There are a number things you’ll notice immediately, but for the most part it looks very similar to the association element we learned about above. First, you’ll notice that we’re using the collection element. Then you’ll notice that there’s a new “ofType” attribute. This attribute is necessary to distinguish between the JavaBean (or field) property type and the type that the collection contains.
To handle the Many-to-Many relationship between Post and Tag, we are also going to use a collection element, but we don’t need to use nested results for it:
And we are done! Let’s see how the Blog.xml file looks like:
Solution to N+1 Selects Problem
As you could read above, the N+1 Selects Problem can happen while you are retrieving data.
How to solve it?
Using Nested Results: By using nested result mappings to deal with repeating subsets of joined results.
What we have to do is to write a single query to retrieve all the data (Blog + Author + Posts + Tags), and hadle the mapping in a single ResultMapping.
Very Important: id elements play a very important role in Nested Result mapping. You should alwaysspecify one or more properties that can be used to uniquely identify the results. The truth is that MyBatis will still work if you leave it out, but at a severe performance cost. Choose as few properties as possible that can uniquely identify the result. The primary key is an obvious choice (even if composite).
3 – BlogDAO
4 – Annotations
@Result
A single result mapping between a column and a property or field.Attributes: id, column, property, javaType, jdbcType, typeHandler, one, many.The id attribute is a boolean value that indicates that the property should be used for comparisons (similar to <id> in the XML mappings).The one attribute is for single associations, similar to <association>, and the many attribute is for collections, similar to <collection>. They are named as they are to avoid class naming conflicts.
@One
A mapping to a single property value of a complex type.Attributes: select, which is the fully qualified name of a mapped statement (i.e. mapper method) that can load an instance of the appropriate type.Note: You will notice that join mapping is not supported via the Annotations API. This is due to the limitation in Java Annotations that does not allow for circular references.
@Many
A mapping to a collection property of a complex types.Attributes: select, which is the fully qualified name of a mapped statement (i.e. mapper method) that can load a collection of instances of the appropriate types.Note: You will notice that join mapping is not supported via the Annotations API. This is due to the limitation in Java Annotations that does not allow for circular references.
5 – SqlMapConfig.xml
This is how our SqlMapConfig.xml looks like:
6 - MyBatisConnectionFactory
Download
If you want to download the complete sample project, you can get it from my GitHub account: https://github.com/loiane/ibatis-handling-joins
If you want to download the zip file of the project, just click on download:
Opinions expressed by DZone contributors are their own.
Comments