Siva

MyBatis Tutorial: Part1 – CRUD Operations

Share this post:

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

<strong>MyBatis Tutorial: Part1 – CRUD Operations</strong>

<strong>MyBatis Tutorial: Part-2: CRUD operations Using Annotations</strong>

<strong>MyBatis Tutorial: Part 3 – Mapping Relationships</strong>

<strong>MyBatis Tutorial : Part4 – Spring Integration</strong>

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;
    //setters and getters 
    
    @Override
    public String toString() {
        return "User [userId=" + userId + ", emailId=" + emailId
        + ", password=" + password + ", firstName=" + firstName
        + ", lastName=" + lastName + "]";
    }
    
}

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"/>
   </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);
	}
}
Share this post:

Related content

comments powered by Disqus