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

	}
}


   9 Comments


  1. Joy Su
      July 15, 2013

    Very nice, thank you !!!!

    Just one thing, in the mapper.xml file, the parameterType is confusing, not sure how it is set.

  2. Siva Prasad Reddy
      July 15, 2013

    You don't need to set it explicitly. It is an indicator to specify what type of object you need to pass while invoking that mapped statement. Setting parameterType is completely optional, as MyBatis can figure it by Reflection.

  3. Senthil Muthiah
      September 20, 2013

    Hi Great. Can you please explain in detail what is the use of line no 18 to 24 in UserMapper.xml file

  4. KML H2
      January 7, 2014

    muchas gracias, me fue de gran ayuda

  5. PosTi85
      May 21, 2014

    Thank you. This example is a nice extra for the current official documentation.

  6. sharath kumar .K.S
      July 15, 2014

    Thanks, It's a nice example

  7. feel good
      January 5, 2015

    Thanks a lot. i was looking for this type of example.

  8. vijju
      March 30, 2015

    Thank you Siva. Its a very good example.

  9. Raichand Ray
      December 23, 2015

    Hi,
    I am a newbie.Please write a tutorial with maven,netbeans8,springsecurity4,spring4.2,hibernate4,primefaces5,mysql5 CRUD logout features.A search page and result get displayed in primefaces datatable.Each record in data table has a select checkbox and edit link.when user clicks the edit button corresponding record opens in a dialog box with save and cancel button. after clicking save or cancel button the dialog box closes.

    Thanks
    Raichand Ray

Leave a Reply

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