Getting Started with MyBatis 3: CRUD Operations Example with XML Mapper

By Arvind Rai, March 17, 2015
On this page we will learn MyBatis 3 CRUD operations with XML Mapper using MySQL step by step. CRUD is create, retrieve, update and delete data. MyBatis is an persistence framework that maps POJO and table for the data transaction. MyBatis is strong persistence framework to handle data transactions like hibernate. To start with MyBatis, we need to create a MyBatis configuration file which we need to keep in our classpath of project. This file is read by MyBatis first. Here we do database environment settings, XML Mapper configurations and many other as required. In XML Mapper we configure mapper namespace and queries like select , insert, update and delete with an id. We call these queries in our DAO using namespace and id. MyBatis reads configuration file using SqlSessionFactoryBuilder and returns SqlSessionFactory that creates SqlSession. Using MyBatis SqlSession, we perform select, insert, update and delete operations. Find the complete example for CRUD Operations using MyBatis.

Software Required to Run Example

Find the required software to run MyBatis example.
1. Java 7
2. Eclipse
3. Gradle
4. MySQL

Project Structure in Eclipse

Find the print screen of project structure in eclipse.
Getting Started with MyBatis 3: CRUD Operations Example with XML Mapper

Table Schema

We have created a table for our CRUD operation.
Table: village
CREATE TABLE `village` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`district` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB; 

Gradle to Resolve JAR Dependencies

Find the Gradle file to resolve the JAR dependencies of MyBatis and MySQL.
build.gradle
apply plugin: 'java'
apply plugin: 'eclipse'
archivesBaseName = 'concretepage'
version = '1' 
repositories {
    mavenCentral()
}
dependencies {
    compile 'org.mybatis:mybatis:3.2.8'
    compile 'mysql:mysql-connector-java:5.1.34'
}  

Create POJO

Find the POJO which we are using in our MyBatis demo project to map table and its columns.
Village.java
package com.concretepage;
public class Village {
	private Integer id;
	private String name;
	private String district;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getDistrict() {
		return district;
	}
	public void setDistrict(String district) {
		this.district = district;
	}
} 

MyBatis Configuration XML File

MyBatis configuration class has different elements. Find some of them.

<configuration>: This is the root element for different configuration in MyBatis configuration file.
<typeAliases>: Configures an alias for the POJO which will be used in XML Mapper. This is for our easiness not to use fully qualified class name in XML Mapper.
<environment>: This element configures the database settings like driver name, username, password etc.
<mappers>: Using this element we configure XML Mapper in MyBatis configuration file.

Find the configuration file.
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>
  <typeAliases>
     <typeAlias type="com.concretepage.Village" alias="village"/>
  </typeAliases>  
  <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/concretepage"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/concretepage/VillageMapper.xml" />
  </mappers>
</configuration> 

MyBatis Mapper XML File

We need to create a Mapper where we define our CRUD operation as SQL query.

<mapper> : This is the root element of XML Mapper.
<resultMap> : Using this element we map table column with POJO property.
<select> : Configures select query.
<insert> : Configures insert query.
<update> : Configures update query.
<delete> : Configures delete query.

The namespace of mapper and id of select, insert etc element is used together to create statement to run query. Find XML Mapper which is being used in our example.
VillageMapper.xml
<?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.concretepage.VillageMapper" >
	<resultMap id="villageResult" type="village">
		<id property="id" column="id" />
		<result property="name" column="name"/>
		<result property="district" column="district"/>
	</resultMap>
	<select id="selectVillage" resultType="village" parameterType="int" resultMap="villageResult">
	    SELECT id, name, district from village WHERE id = #{id}
	</select>
	<insert id="insertVillage" parameterType="village"  keyProperty="id" useGeneratedKeys="true">
		INSERT into village(name,district) VALUES(#{name}, #{district}) 	    
	</insert>
	<update id="updateVillage" parameterType="village">
		UPDATE village SET name=#{name}, district =#{district} WHERE id =#{id}  	    
	</update>
	<delete id="deleteVillage" parameterType="int">
		DELETE FROM village WHERE id =#{id}  	    
	</delete>
</mapper> 
The statement for select query will be com.concretepage.VillageMapper.selectVillage which will used in our DAO class. And in the same way statement can be created for insert, update and delete operation.

Utility Class to fetch SqlSessionFactory using SqlSessionFactoryBuilder

Create a utility class that will provide a method to return SqlSessionFactory .

SqlSessionFactoryBuilder : Builds the MyBatis configuration file.
SqlSessionFactory : Factory class to create SqlSession.
Resources : MyBatis provides this class to access file.

Find the utility class.
MyBatisUtil.java
package com.concretepage;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisUtil {
 	private static SqlSessionFactory sqlSessionFactory;
	static {
		String resource = "mybatis-config.xml";
		InputStream inputStream;
		try {
			inputStream = Resources.getResourceAsStream(resource);
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static SqlSessionFactory getSqlSessionFactory(){
		return sqlSessionFactory;
	}
} 

Use MyBatis SqlSession insert, update, delete, selectOne for CRUD Operation

In our DAO class, we are creating methods for CRUD operation. MyBatis provides different API to handle it.

SqlSession: MyBatis session to run queries.
SqlSession.insert(): Executes insert statement for the given input object.
SqlSession.update(): Executes update statement for the given input object.
SqlSession.delete(): Executes delete statement for the given input object.
SqlSession.selectOne(): Executes select statement for the given input object and returns POJO.

Find our DAO class.
VillageDAO.java
package com.concretepage;
import org.apache.ibatis.session.SqlSession;
public class VillageDAO {
	public void save(Village village){
	  SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();	
	  session.insert("com.concretepage.VillageMapper.insertVillage", village);
	  session.commit();
	  session.close();
	}
	public void update(Village village){
	  SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();	
	  session.update("com.concretepage.VillageMapper.updateVillage", village);
	  session.commit();
	  session.close();
	}
	public void delete(Integer id){
	  SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();	
	  session.delete("com.concretepage.VillageMapper.deleteVillage", id);
	  session.commit();
	  session.close();
	}
	public Village getData(Integer id) {
	  SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();	
	  Village village = session.selectOne("com.concretepage.VillageMapper.selectVillage", id);
	  session.close();
	  return village;
	}
} 

Run Application

Finally we will test our MyBatis application.
RunMybatis.java
package com.concretepage;
public class RunMybatis {
	public static void main(String[] args) {
	  VillageDAO villageDAO = new VillageDAO();	
	 //insert	
	  Village village = new Village();
	  village.setName("Crossing Republic");
	  village.setDistrict("Ghaziabad");
	  villageDAO.save(village);
	  System.out.println("---Data saved---");
	  //update
	  village = new Village();
	  village.setId(1);
	  village.setName("Dhananjaypur");
	  village.setDistrict("Varanasi");
	  villageDAO.update(village);
	  System.out.println("---Data updated---");
	  //select
	  village = villageDAO.getData(1);
	  System.out.println("id:"+village.getId()+", Name:"+village.getName()+", District:"+village.getDistrict());
	  //delete
	  villageDAO.delete(1);
	  System.out.println("---Data deleted---");
	}
} 
Find the output.
---Data saved---
---Data updated---
id:1, Name:Dhananjaypur, District:Varanasi
---Data deleted--- 
I am done now. Happy Learning!.

Download Complete Source Code

POSTED BY
ARVIND RAI
ARVIND RAI







©2024 concretepage.com | Privacy Policy | Contact Us