Packt Publishing “Java Persistence With MyBatis3” published

Hurray…My first book “Java Persistence with MyBatis3” is published. I would like to thank Packt Publishers for giving me this opportunity to write on my favorite framework MyBatis.

For most of the software applications data persistence is a key and important aspect. In Java land we have many ways of implementing persistence layer starting from low level JDBC to fancy ORM frameworks.
JDBC is too low level API and needs to write a lot of boilerplate code. On the other hand we have full fledged ORM frameworks like JPA(Hibernate, EclipseLink etc) which hides the complexity of working with SQL directly by letting developers work with Objects and generate SQL based on the RDBMS(Dialect) being used. But each approach has its own set of pros and cons, there is no one size fits all solutions. There are many large applications that are using Hibernate successfully and there are many other applications which got screwed up by using Hibernate/JPA incorrectly. It is not the problem with JPA/Hibernate, it is simply because JPA/Hibernate may not be best fit for those applications or developers don’t understood them properly.

So what if I don’t want to use low level JDBC API and can’t go for full fledged ORMs? MyBatis to the rescue.

MyBatis is a SQL Mapper framework which simplify data persistence logic by hiding all the low level JDBC code and provides easy to use API. Nothing more, nothing less..No magic… You need to write SQL by yourself.(Whenever I hear someone saying “By using ORM frameworks we don’t need to know SQL, we can use HQL and ORM will take care of it”, I just LOL)

So what exactly MyBatis offers you?
1. Manage Resources: Based on the provided configuration, MyBatis takes care of creating Connection(pool), Statement/PreparedStatement/CallableStatement, ResultSet and closing them once the task is done.

2. SQL Results <-> Java Beans: For SELECT statement MyBatis will take care of looping through ResultSet and populating Java Objects. For DML statements we can pass Java Objects as inputs and MyBatis will unwrap the data and put in placeholders based on provided mapping.

3. Supports Mapping of One-To-Many/One-To-One mapping: MyBatis supports mapping of SQL ResultSet into Java Object graph based on One-To-Many/One-To-One mapping.

4. Caching Support: Caching SQL results is very common requirement for any non trivial applications. MyBatis provides in-built support for Caching. In addition to that MyBatis provides support for popular cache libraries like EHCache, HazelCast etc.

5. Integration with other frameworks: MyBatis is very lightweight and works well with popular IOC frameworks like Spring, Guice.

6. Annotation based Mappers: For those XML haters, MyBatis provides Annotation based mapping also.

I feel like as Gradle is sweet spot in between Ant and Maven, MyBatis is sweetspot between JDBC and ORMs(JPA/Hibernate/EclipseLink).

http://www.packtpub.com/java-persistence-with-mybatis-3/book

Java Persistence With MyBatis3 book covers:

Chapter 1 – Getting Started with MyBatis:
Introduces MyBatis persistence framework and explains the advantages of using MyBatis instead of plain JDBC. We will also look at how to create a project, install MyBatis framework dependencies with and without the Maven build tool, configure, and use MyBatis.

Chapter 2 – Bootstrapping MyBatis:
Covers how to bootstrap MyBatis using XML and Java API-based configuration. We will also learn various MyBatis configuration options such as type aliases, type handlers, global settings, and so on.

Chapter 3 – SQL Mappers Using XML: 
This chapter goes in-depth into writing SQL mapped statements using the Mapper XML files. We will learn how to configure simple statements, statements with one-to-one, one-to-many relationships and mapping results using ResultMaps. We will also learn how to build dynamic queries, paginated results, and custom ResultSet handling.

Chapter 4 – SQL Mappers Using Annotations:
This chapter covers writing SQL mapped statements using annotations. We will learn how to configure simple statements, statements with one-to-one and one-to-many relationships. We will also look into building dynamic queries using SqlProvider annotations.

Chapter 5 – Integration with Spring:
This chapter covers how to integrate MyBatis with Spring framework. We will learn how to install Spring libraries, register MyBatis beans in Spring ApplicationContext, inject SqlSession and Mapper beans, and use Spring’s annotation-based transaction handling mechanism with MyBatis.

If anybody is interested in reviewing the book, please let me know.

MyBatis Tutorial : Part4 – Spring Integration

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

MyBatis-Spring is a subproject of MyBatis and provides Spring integration support which drastically simplifies the MyBatis usage. For those who are familiar with Spring’s way of Dependency Injection process, using MyBatis-Spring is a very simple.

First let us see the process of using MyBatis without Spring.

1. Create SqlSessionFactory using SqlSessionFactoryBuilder by passing mybatis-config.xml which contains DataSource properties, List of Mapper XMLs and TypeAliases etc.

2. Create SqlSession object from SqlSessionFactory

3. Get Mapper instance from SqlSession and execute queries.

4. Commit or rollback the transaction using SqlSession object.

With MyBatis-Spring, most of the above steps can be configured in Spring ApplicationContext and SqlSession or Mapper instances can be injected into Spring Beans. Then we can use Spring’s TransactionManagement features without writing transaction commit/rollback code all over the code.

Now let us see how we can configure MyBatis+Spring integration stuff.

Step#1: Configure MyBatis-Spring dependencies in pom.xml

  
  <dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>4.10</version>
   <scope>test</scope>
  </dependency>
  
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.1.1</version>
  </dependency>
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.1.1</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-context-support</artifactId>
   <version>3.1.1.RELEASE</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-test</artifactId>
   <version>3.1.1.RELEASE</version>
   <scope>test</scope>
  </dependency>
  <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.21</version>
             <scope>runtime</scope>
         </dependency>
  <dependency>
   <groupId>cglib</groupId>
   <artifactId>cglib-nodep</artifactId>
   <version>2.2.2</version>
  </dependency>

Step#2: You don’t need to configure Database properties in mybatis-config.xml.

We can configure DataSource in Spring Container and use it to build MyBatis SqlSessionFactory.

Instead of SqlSessionFactoryBuilder, MyBatis-Spring uses org.mybatis.spring.SqlSessionFactoryBean to build SqlSessionFactory.

We can pass dataSource, Mapper XML files locations, typeAliases etc to SqlSessionFactoryBean.

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="${jdbc.driverClassName}"/>
  <property name="url" value="${jdbc.url}"/>
  <property name="username" value="${jdbc.username}"/>
  <property name="password" value="${jdbc.password}"/>
 </bean>
 
 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="typeAliasesPackage" value="com.sivalabs.mybatisdemo.domain"/>
    <property name="mapperLocations" value="classpath*:com/sivalabs/mybatisdemo/mappers/**/*.xml" />
 </bean>

Step#3: Configure SqlSessionTemplate which provides ThreadSafe SqlSession object.

 <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
   <constructor-arg index="0" ref="sqlSessionFactory" />
 </bean>

Step#4: To be able to inject Mappers directly we should register org.mybatis.spring.mapper.MapperScannerConfigurer and configure the package name where to find Mapper Interfaces.

 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
   <property name="basePackage" value="com.sivalabs.mybatisdemo.mappers" />
 </bean>

Step#5: Configure TransactionManager to support Annotation based Transaction support.

 
 <tx:annotation-driven transaction-manager="transactionManager"/>
 
 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
 </bean>

Step#6: Update the Service classes and register them in Spring container.

package com.sivalabs.mybatisdemo.service;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.sivalabs.mybatisdemo.domain.User;
import com.sivalabs.mybatisdemo.mappers.UserMapper;

@Service
@Transactional
public class UserService
{
 @Autowired
 private SqlSession sqlSession; //This is to demonstrate injecting SqlSession object
 
 public void insertUser(User user) 
 {
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  userMapper.insertUser(user);
 }

 public User getUserById(Integer userId) 
 {
  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  return userMapper.getUserById(userId);
 }
 
}
package com.sivalabs.mybatisdemo.service;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.sivalabs.mybatisdemo.domain.Blog;
import com.sivalabs.mybatisdemo.mappers.BlogMapper;

@Service
@Transactional
public class BlogService
{
 @Autowired
 private BlogMapper blogMapper; // This is to demonstratee how to inject Mappers directly
 
 public void insertBlog(Blog blog) {
  blogMapper.insertBlog(blog);
 }
 
 public Blog getBlogById(Integer blogId) {
  return blogMapper.getBlogById(blogId);
 }
 
 public List<Blog> getAllBlogs() {
  return blogMapper.getAllBlogs();
 }
}

Note: When we can directly inject Mappers then why do we need to inject SqlSession objects? Because SqlSession object contains more fine grained method which comes handy at times.

For Example: If we want to get count of how many records got updated by an Update query we can use SqlSession as follows:

int updatedRowCount = sqlSession.update("com.sivalabs.mybatisdemo.mappers.UserMapper.updateUser", user);

So far I didn’t find a way to get the row update count without using SqlSession object.

PS: You can have your interface insert/update/delete methods returning int, then MyBatis returns the number of records updated as an integer.

Step#7 Write JUnit Tests to test UserService and BlogService.

package com.sivalabs.mybatisdemo;

import java.util.List;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.sivalabs.mybatisdemo.domain.User;
import com.sivalabs.mybatisdemo.service.UserService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class SpringUserServiceTest 
{
 @Autowired
 private UserService userService;
 
    @Test
 public void testGetUserById() 
 {
  User user = userService.getUserById(1);
  Assert.assertNotNull(user);
  System.out.println(user);
  System.out.println(user.getBlog());
 }
        
    @Test
    public void testUpdateUser() 
    {
     long timestamp = System.currentTimeMillis();
  User user = userService.getUserById(2);
  user.setFirstName("TestFirstName"+timestamp);
     user.setLastName("TestLastName"+timestamp);
     userService.updateUser(user);
  User updatedUser = userService.getUserById(2);
  Assert.assertEquals(user.getFirstName(), updatedUser.getFirstName());
  Assert.assertEquals(user.getLastName(), updatedUser.getLastName());
 }
    
}
package com.sivalabs.mybatisdemo;

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

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.sivalabs.mybatisdemo.domain.Blog;
import com.sivalabs.mybatisdemo.domain.Post;
import com.sivalabs.mybatisdemo.service.BlogService;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class SpringBlogServiceTest 
{
 @Autowired
 private BlogService blogService;
 
 @Test
 public void testGetBlogById() 
 {
  Blog blog = blogService.getBlogById(1);
  Assert.assertNotNull(blog);
  System.out.println(blog);
  List<Post> posts = blog.getPosts();
  for (Post post : posts) {
   System.out.println(post);
  }
 }
    
    @Test
    public void testInsertBlog() 
    {
     Blog blog = new Blog();
     blog.setBlogName("test_blog_"+System.currentTimeMillis());
     blog.setCreatedOn(new Date());
     
     blogService.insertBlog(blog);
     Assert.assertTrue(blog.getBlogId() != 0);
     Blog createdBlog = blogService.getBlogById(blog.getBlogId());
     Assert.assertNotNull(createdBlog);
     Assert.assertEquals(blog.getBlogName(), createdBlog.getBlogName());
    }
    
}

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();
 }
}

MyBatis Tutorial: Part-2: CRUD operations Using Annotations

In this post I will explain how to perform CRUD operations using MyBatis Annotation support without need of Queries configuration in XML mapper files.

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

Step#1: Create a table BLOG and a java domain Object Blog.

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;
package com.sivalabs.mybatisdemo.domain;

import java.util.Date;

public class Blog {

 private Integer blogId;
 private String blogName;
 private Date createdOn;
 
 @Override
 public String toString() {
  return "Blog [blogId=" + blogId + ", blogName=" + blogName
    + ", createdOn=" + createdOn + "]";
 }
 //Seeters and getters
}

Step#2: Create UserMapper.java interface with SQL queries in Annotations.

package com.sivalabs.mybatisdemo.mappers;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.sivalabs.mybatisdemo.domain.Blog;

public interface BlogMapper 
{
 @Insert("INSERT INTO BLOG(BLOG_NAME, CREATED_ON) VALUES(#{blogName}, #{createdOn})")
 @Options(useGeneratedKeys=true, keyProperty="blogId")
 public void insertBlog(Blog blog);
 
 @Select("SELECT BLOG_ID AS blogId, BLOG_NAME as blogName, CREATED_ON as createdOn FROM BLOG WHERE BLOG_ID=#{blogId}")
 public Blog getBlogById(Integer blogId);
 
 @Select("SELECT * FROM BLOG ")
 @Results({
  @Result(id=true, property="blogId", column="BLOG_ID"),
  @Result(property="blogName", column="BLOG_NAME"),
  @Result(property="createdOn", column="CREATED_ON")  
 })
 public List<Blog> getAllBlogs();
 
 @Update("UPDATE BLOG SET BLOG_NAME=#{blogName}, CREATED_ON=#{createdOn} WHERE BLOG_ID=#{blogId}")
 public void updateBlog(Blog blog);
 
 @Delete("DELETE FROM BLOG WHERE BLOG_ID=#{blogId}")
 public void deleteBlog(Integer blogId);
 
}

Step#3: Configure BlogMapper in mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
 <properties resource="jdbc.properties"/>
 <environments default="development">
  <environment id="development">
    <transactionManager type="JDBC"/>
    <dataSource type="POOLED">
   <!-- <property name="driver" value="com.mysql.jdbc.Driver"/>
   <property name="url" value="jdbc:mysql://localhost:3306/mybatis-demo"/>
   <property name="username" value="root"/>
   <property name="password" value="admin"/> -->
   <property name="driver" value="${jdbc.driverClassName}"/>
   <property name="url" value="${jdbc.url}"/>
   <property name="username" value="${jdbc.username}"/>
   <property name="password" value="${jdbc.password}"/>
    </dataSource>
  </environment>
  </environments>
  <mappers>
    <mapper class="com.sivalabs.mybatisdemo.mappers.BlogMapper"/>
  </mappers>
</configuration>

Step#4: Create BlogService.java

package com.sivalabs.mybatisdemo.service;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.sivalabs.mybatisdemo.domain.Blog;
import com.sivalabs.mybatisdemo.mappers.BlogMapper;

public class BlogService
{
  
 public void insertBlog(Blog blog) {
  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
  try{
  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
  blogMapper.insertBlog(blog);
  sqlSession.commit();
  }finally{
   sqlSession.close();
  }
 }

 
 public Blog getBlogById(Integer blogId) {
  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
  try{
  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
  return blogMapper.getBlogById(blogId);
  }finally{
   sqlSession.close();
  }
 }

 
 public List<Blog> getAllBlogs() {
  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
  try{
  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
  return blogMapper.getAllBlogs();
  }finally{
   sqlSession.close();
  }
 }

 
 public void updateBlog(Blog blog) {
  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
  try{
  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
  blogMapper.updateBlog(blog);
  sqlSession.commit();
  }finally{
   sqlSession.close();
  }  
 }

 
 public void deleteBlog(Integer blogId) {
  SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
  try{
  BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
  blogMapper.deleteBlog(blogId);
  sqlSession.commit();
  }finally{
   sqlSession.close();
  }
  
 }

}

Step#5: Create JUnit Test for BlogService methods

package com.sivalabs.mybatisdemo;

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

import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;

import com.sivalabs.mybatisdemo.domain.Blog;
import com.sivalabs.mybatisdemo.service.BlogService;

public class BlogServiceTest 
{
 private static BlogService blogService;
 
 @BeforeClass
    public static void setup() 
 {
  blogService = new BlogService();
 }
 
 @AfterClass
    public static void teardown() 
 {
  blogService = null;
 }
 
    @Test
 public void testGetBlogById() 
 {
  Blog blog = blogService.getBlogById(1);
  Assert.assertNotNull(blog);
  System.out.println(blog);
 }
    
    @Test
    public void testGetAllBlogs() 
    {
  List<Blog> blogs = blogService.getAllBlogs();
  Assert.assertNotNull(blogs);
  for (Blog blog : blogs) 
  {
   System.out.println(blog);
  }
  
 }
    
    @Test
    public void testInsertBlog() 
    {
     Blog blog = new Blog();
     blog.setBlogName("test_blog_"+System.currentTimeMillis());
     blog.setCreatedOn(new Date());
     
     blogService.insertBlog(blog);
  Assert.assertTrue(blog.getBlogId() != 0);
  Blog createdBlog = blogService.getBlogById(blog.getBlogId());
  Assert.assertNotNull(createdBlog);
  Assert.assertEquals(blog.getBlogName(), createdBlog.getBlogName());
  
 }
    
    @Test
    public void testUpdateBlog() 
    {
     long timestamp = System.currentTimeMillis();
  Blog blog = blogService.getBlogById(2);
  blog.setBlogName("TestBlogName"+timestamp);
     blogService.updateBlog(blog);
  Blog updatedBlog = blogService.getBlogById(2);
  Assert.assertEquals(blog.getBlogName(), updatedBlog.getBlogName());
 }
    
   @Test
   public void testDeleteBlog() 
   {
     Blog blog = blogService.getBlogById(4);
     blogService.deleteBlog(blog.getBlogId());
  Blog deletedBlog = blogService.getBlogById(4);
  Assert.assertNull(deletedBlog);
 }
}

MyBatis Tutorial: Part1 – CRUD Operations

MyBatis is an SQL Mapper tool which greatly simplifies the database programing when compared to using JDBC directly.

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

Step1: Create a Maven project and configure MyBatis dependencies.

<project xmlns="http://maven.apache.org/POM/4.0.0" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>com.sivalabs</groupId>
 <artifactId>mybatis-demo</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>mybatis-demo</name>
 <url>http://maven.apache.org</url>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 </properties>

 <build>
  <plugins>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>2.3.2</version>
    <configuration>
     <source>1.6</source>
     <target>1.6</target>
     <encoding>${project.build.sourceEncoding}</encoding>
    </configuration>
   </plugin>
  </plugins>
 </build>

 <dependencies>
  <dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>4.10</version>
   <scope>test</scope>
  </dependency>
  
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.1.1</version>
  </dependency>
  <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.21</version>
             <scope>runtime</scope>
         </dependency>
 </dependencies>
</project>

Step#2: Create the table USER and a Java domain Object User 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,
  PRIMARY KEY  (user_id),
  UNIQUE KEY Index_2_email_uniq (email_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

package com.sivalabs.mybatisdemo.domain;
public class User 
{
 private Integer userId;
 private String emailId;
 private String password;
 private String firstName;
 private String lastName;
 
 @Override
 public String toString() {
  return "User [userId=" + userId + ", emailId=" + emailId
    + ", password=" + password + ", firstName=" + firstName
    + ", lastName=" + lastName + "]";
 }
 //setters and getters 
}

Step#3: Create MyBatis configuration files.

a) Create jdbc.properties file in src/main/resources folder 

  jdbc.driverClassName=com.mysql.jdbc.Driver
  jdbc.url=jdbc:mysql://localhost:3306/mybatis-demo
  jdbc.username=root
  jdbc.password=admin

b) Create mybatis-config.xml file in src/main/resources folder

 
  <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
  <configuration>
   <properties resource="jdbc.properties"/>
   <typeAliases>
    <typeAlias type="com.sivalabs.mybatisdemo.domain.User" alias="User"></typeAlias>
   </typeAliases>
   <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">    
     <property name="driver" value="${jdbc.driverClassName}"/>
     <property name="url" value="${jdbc.url}"/>
     <property name="username" value="${jdbc.username}"/>
     <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
    </environments>
    <mappers>
   <mapper resource="com/sivalabs/mybatisdemo/mappers/UserMapper.xml"/>
    </mappers>
  </configuration>

Step#4: Create an interface UserMapper.java in src/main/java folder in com.sivalabs.mybatisdemo.mappers package.

  package com.sivalabs.mybatisdemo.mappers;

  import java.util.List;
  import com.sivalabs.mybatisdemo.domain.User;

  public interface UserMapper 
  {

   public void insertUser(User user);
   
   public User getUserById(Integer userId);
   
   public List<User> getAllUsers();
   
   public void updateUser(User user);
   
   public void deleteUser(Integer userId);
   
  }

Step#5: Create UserMapper.xml file in src/main/resources folder in com.sivalabs.mybatisdemo.mappers package.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<mapper namespace="com.sivalabs.mybatisdemo.mappers.UserMapper">

  <select id="getUserById" parameterType="int" resultType="com.sivalabs.mybatisdemo.domain.User">
     SELECT 
      user_id as userId, 
      email_id as emailId , 
      password, 
      first_name as firstName, 
      last_name as lastName
     FROM USER 
     WHERE USER_ID = #{userId}
  </select>
  <!-- Instead of referencing Fully Qualified Class Names we can register Aliases in mybatis-config.xml and use Alias names. -->
   <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"/>   
   </resultMap>
  
  <select id="getAllUsers" resultMap="UserResult">
   SELECT * FROM USER
  </select>
  
  <insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="userId">
   INSERT INTO USER(email_id, password, first_name, last_name)
    VALUES(#{emailId}, #{password}, #{firstName}, #{lastName})
  </insert>
  
  <update id="updateUser" parameterType="User">
    UPDATE USER 
    SET
     PASSWORD= #{password},
     FIRST_NAME = #{firstName},
     LAST_NAME = #{lastName}
    WHERE USER_ID = #{userId}
  </update>
  
  <delete id="deleteUser" parameterType="int">
    DELETE FROM USER WHERE USER_ID = #{userId}
  </delete>
  
</mapper>

Step#6: Create MyBatisUtil.java to instantiate SqlSessionFactory.

package com.sivalabs.mybatisdemo.service;

import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisUtil 
{
 private static SqlSessionFactory factory;

 private MyBatisUtil() {
 }
 
 static
 {
  Reader reader = null;
  try {
   reader = Resources.getResourceAsReader("mybatis-config.xml");
  } catch (IOException e) {
   throw new RuntimeException(e.getMessage());
  }
  factory = new SqlSessionFactoryBuilder().build(reader);
 }
 
 public static SqlSessionFactory getSqlSessionFactory() 
 {
  return factory;
 }
}

Step#7: Create UserService.java in src/main/java folder.

package com.sivalabs.mybatisdemo.service;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.sivalabs.mybatisdemo.domain.User;
import com.sivalabs.mybatisdemo.mappers.UserMapper;

public class UserService
{

	public void insertUser(User user)
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
		try
		{
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			userMapper.insertUser(user);
			sqlSession.commit();
		} finally
		{
			sqlSession.close();
		}
	}

	public User getUserById(Integer userId)
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
		try
		{
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			return userMapper.getUserById(userId);
		} finally
		{
			sqlSession.close();
		}
	}

	public List<User> getAllUsers()
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
		try
		{
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			return userMapper.getAllUsers();
		} finally
		{
			sqlSession.close();
		}
	}

	public void updateUser(User user)
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
		try
		{
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			userMapper.updateUser(user);
			sqlSession.commit();
		} finally
		{
			sqlSession.close();
		}

	}

	public void deleteUser(Integer userId)
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
		try
		{
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
			userMapper.deleteUser(userId);
			sqlSession.commit();
		} finally
		{
			sqlSession.close();
		}

	}

}

Step#8: Create a JUnit Test class to test UserService methods.

package com.sivalabs.mybatisdemo;

import java.util.List;

import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;

import com.sivalabs.mybatisdemo.domain.User;
import com.sivalabs.mybatisdemo.service.UserService;

public class UserServiceTest
{
	private static UserService userService;

	@BeforeClass
	public static void setup()
	{
		userService = new UserService();
	}

	@AfterClass
	public static void teardown()
	{
		userService = null;
	}

	@Test
	public void testGetUserById()
	{
		User user = userService.getUserById(1);
		Assert.assertNotNull(user);
		System.out.println(user);
	}

	@Test
	public void testGetAllUsers()
	{
		List&lt;User&gt; users = userService.getAllUsers();
		Assert.assertNotNull(users);
		for (User user : users)
		{
			System.out.println(user);
		}

	}

	@Test
	public void testInsertUser()
	{
		User user = new User();
		user.setEmailId("test_email_" + System.currentTimeMillis() + "@gmail.com");
		user.setPassword("secret");
		user.setFirstName("TestFirstName");
		user.setLastName("TestLastName");

		userService.insertUser(user);
		Assert.assertTrue(user.getUserId() != 0);
		User createdUser = userService.getUserById(user.getUserId());
		Assert.assertNotNull(createdUser);
		Assert.assertEquals(user.getEmailId(), createdUser.getEmailId());
		Assert.assertEquals(user.getPassword(), createdUser.getPassword());
		Assert.assertEquals(user.getFirstName(), createdUser.getFirstName());
		Assert.assertEquals(user.getLastName(), createdUser.getLastName());

	}

	@Test
	public void testUpdateUser()
	{
		long timestamp = System.currentTimeMillis();
		User user = userService.getUserById(2);
		user.setFirstName("TestFirstName" + timestamp);
		user.setLastName("TestLastName" + timestamp);
		userService.updateUser(user);
		User updatedUser = userService.getUserById(2);
		Assert.assertEquals(user.getFirstName(), updatedUser.getFirstName());
		Assert.assertEquals(user.getLastName(), updatedUser.getLastName());
	}

	@Test
	public void testDeleteUser()
	{
		User user = userService.getUserById(4);
		userService.deleteUser(user.getUserId());
		User deletedUser = userService.getUserById(4);
		Assert.assertNull(deletedUser);

	}
}