MyBatis Tutorial: Part 3 – Mapping Relationships

In this post let us see how to use MyBatis ResultMap configuration to map relationships.

MyBatis Tutorial: Part1 – CRUD Operations
MyBatis Tutorial: Part-2: CRUD operations Using Annotations
MyBatis Tutorial: Part 3 – Mapping Relationships
MyBatis Tutorial : Part4 – Spring Integration

To illustrate we are considering the following sample domain model:
There will be Users and each User may have a Blog and each Blog can contain zero or more posts.

The Database structure of the three tables are as follows:

CREATE TABLE user (
  user_id int(10) unsigned NOT NULL auto_increment,
  email_id varchar(45) NOT NULL,
  password varchar(45) NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) default NULL,
  blog_id int(10) unsigned default NULL,
  PRIMARY KEY  (user_id),
  UNIQUE KEY Index_2_email_uniq (email_id),
  KEY FK_user_blog (blog_id),
  CONSTRAINT FK_user_blog FOREIGN KEY (blog_id) REFERENCES blog (blog_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE blog (
  blog_id int(10) unsigned NOT NULL auto_increment,
  blog_name varchar(45) NOT NULL,
  created_on datetime NOT NULL,
  PRIMARY KEY  (blog_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE post (
  post_id int(10) unsigned NOT NULL auto_increment,
  title varchar(45) NOT NULL,
  content varchar(1024) NOT NULL,
  created_on varchar(45) NOT NULL,
  blog_id int(10) unsigned NOT NULL,
  PRIMARY KEY  (post_id),
  KEY FK_post_blog (blog_id),
  CONSTRAINT FK_post_blog FOREIGN KEY (blog_id) REFERENCES blog (blog_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here I am going to explain how to fetch and map *-has-One and One-To-Many result mappings.

package com.sivalabs.mybatisdemo.domain;

public class User 
{
 private Integer userId;
 private String emailId;
 private String password;
 private String firstName;
 private String lastName;
 private Blog blog;
 //setters and getters
}
package com.sivalabs.mybatisdemo.domain;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Blog {

 private Integer blogId;
 private String blogName;
 private Date createdOn;
 private List<Post> posts = new ArrayList<Post>();
 //setters and getters
}
package com.sivalabs.mybatisdemo.domain;

import java.util.Date;

public class Post 
{
 private Integer postId;
 private String title;
 private String content;
 private Date createdOn;
 //setters and getters
}

In mybatis-config.xml, configure type aliases for beans.

<typeAliases>
  <typeAlias type="com.sivalabs.mybatisdemo.domain.User" alias="User"/>
  <typeAlias type="com.sivalabs.mybatisdemo.domain.Blog" alias="Blog"/>
  <typeAlias type="com.sivalabs.mybatisdemo.domain.Post" alias="Post"/>  
</typeAliases>


*-has-One Result Mapping: 

In UserMapper.xml, configure sql queries and result maps as follows:

<mapper namespace="com.sivalabs.mybatisdemo.mappers.UserMapper">

 <resultMap type="User" id="UserResult">
    <id property="userId" column="user_id"/>
    <result property="emailId" column="email_id"/>
    <result property="password" column="password"/>
    <result property="firstName" column="first_name"/>
    <result property="lastName" column="last_name"/>
    <association property="blog" resultMap="BlogResult"/>
   </resultMap>
   
 <resultMap type="Blog" id="BlogResult">
    <id property="blogId" column="blog_id"/>
    <result property="blogName" column="BLOG_NAME"/>
    <result property="createdOn" column="CREATED_ON"/>    
   </resultMap>
 
  <select id="getUserById" parameterType="int" resultMap="UserResult">
     
     SELECT 
      U.USER_ID, U.EMAIL_ID, U.PASSWORD, U.FIRST_NAME, U.LAST_NAME, 
      B.BLOG_ID, B.BLOG_NAME, B.CREATED_ON
  FROM USER U LEFT OUTER JOIN BLOG B ON U.BLOG_ID=B.BLOG_ID
  WHERE U.USER_ID = #{userId}
  </select>
  
  <select id="getAllUsers" resultMap="UserResult">
   SELECT 
     U.USER_ID, U.EMAIL_ID, U.PASSWORD, U.FIRST_NAME, U.LAST_NAME, 
     B.BLOG_ID, B.BLOG_NAME, B.CREATED_ON
 FROM USER U LEFT OUTER JOIN BLOG B ON U.BLOG_ID=B.BLOG_ID
  </select>
  
</mapper>

In JUnit Test, write a method to test the association loading.

public void getUserById() 
{
 SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
 try{
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  User user = userMapper.getUserById(1);
  System.out.println(user.getBlog());
 }finally{
  sqlSession.close();
 }
}

One-To-Many Results Mapping: 

In BlogMapper.xml configure Blog to Posts relationship as follows:

<mapper namespace="com.sivalabs.mybatisdemo.mappers.BlogMapper">

 <resultMap type="Blog" id="BlogResult">
    <id property="blogId" column="blog_id"/>
    <result property="blogName" column="BLOG_NAME"/>
    <result property="createdOn" column="CREATED_ON"/>
    <collection property="posts" ofType="Post" resultMap="PostResult" columnPrefix="post_"></collection>
   </resultMap>
   
   <resultMap type="Post" id="PostResult">
    <id property="postId" column="post_id"/>
    <result property="title" column="title"/>
    <result property="content" column="content"/>
    <result property="createdOn" column="created_on"/>
   </resultMap>
   
  <select id="getBlogById" parameterType="int" resultMap="BlogResult">
     
     SELECT 
      b.blog_id, b.blog_name, b.created_on, 
      p.post_id as post_post_id, p.title as post_title, p.content as post_content, p.created_on as post_created_on
  FROM blog b left outer join post p on b.blog_id=p.blog_id
     WHERE b.BLOG_ID=#{blogId}
  </select>
  
  <select id="getAllBlogs" resultMap="BlogResult">
   SELECT 
    b.blog_id, b.blog_name, b.created_on as blog_created_on, 
     p.post_id as post_post_id, p.title as post_title, p.content as post_content, p.created_on as post_created_on
 FROM blog b left outer join post p on b.blog_id=p.blog_id
  </select>
  
</mapper>

In JUnit Test, write a test method to test blog-to-posts relationship mapping.

public void getBlogById() 
{
 SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
 try{
 BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
 Blog blog = blogMapper.getBlogById(1);
 System.out.println(blog);
 List<Post> posts = blog.getPosts();
 for (Post post : posts) {
  System.out.println(post);
 }
 }finally{
  sqlSession.close();
 }
}


   One Comment


  1. Hui Wang
      December 17, 2013

    Rather than creating BlogResult in two different mapper files, you should be able create one and reference it with full qualified name in another. For example, if you define BlogResult in BlogMapper.xml file, you can reference it from UserMapper.xml file with full qualified result id, com.sivalabs.mybatisdemo.mappers.BlogMapper.BlogResult.

Leave a Reply

Your email address will not be published. Required fields are marked *