Getting Started with MyBatis 3: CRUD Operations Example with XML Mapper
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.
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>
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---");
}
}
---Data saved--- ---Data updated--- id:1, Name:Dhananjaypur, District:Varanasi ---Data deleted---
