MyBatis Tutorial – CRUD Operations and Mapping Relationships – Part 1

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

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<User> 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);   

 }
}

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

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

Reference: MyBatis Tutorial: Part1 – CRUD Operations from our JCG partner Siva Reddy at the My Experiments on Technology blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.