Creating CRUD Operation in MyBatis

There are following steps to create CRUD Operation :

Step-1 Create the SqlMapConfig.xml

  • This file contains the Database Configuration details and contain transaction detail.

  • There are two TransactionManager types (i.e. type="[JDBC|MANAGED]") that are included with MyBatis :

    • JDBC
      This configuration simply makes use of the JDBC commit and rollback facilities directly.It relies on the connection retrieved from the dataSource to manage the scope of the transaction.

    • MANAGED
      This configuration simply does almost nothing. It never commits, or rolls back a connection. Instead, it lets the container manage the full lifecycle of the transaction (e.g. Spring or a JEE Application Server context). By default it does close the connection. However, some containers don't expect this, and thus if you need to stop it from closing the connection, set the closeConnection property to false.

SqlMapConfig.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>
<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/amit"/>
<property name="username"
value="root"/>
<property name="password"
value="root"/>
</dataSource>

</environment>
</environments>
<mappers>
<mapper resource =
"EmployeeMapper.xml"/>
</mappers>
</configuration>
				

Environments Tag

MyBatis can be configured with multiple environments. For example, you might have a different configuration for your Development, Test and Production environments.

Transaction Manager tag

There are two TransactionManager types :
  • JDBC -
    It simply makes use of the JDBC commit and rollback facilities directly. It relies on the connection retrieved from the dataSource to manage the scope of the transaction.
  • MANAGED -
    It simply does almost nothing. It never commits, or rolls back a connection. Instead, it lets the container manage the full lifecycle of the transaction (e.g. EJB, Spring).

DataSource Tag

It contains the Database Connectivity Details such as URL , Driver , Userid , Password details.

Step-2 Create POJO Class

Employee.java
package com.srivastava.mybatis.dto;
public class Employee 
{
	private int empid;
	private String name;
	private String designation;
	private String phone;
	private String email;
	private double salary;
	public int getEmpid() 
	{
		return empid;
	}
	public void setEmpid(int empid) 
	{
		this.empid = empid;
	}
	public String getName() 
	{
		return name;
	}
	public void setName(String name) 
	{
		this.name = name;
	}
	public String getDesignation() 
	{
		return designation;
	}
	public void setDesignation
		(String designation) 
	{
		this.designation = designation;
	}
	public String getPhone() 
	{
		return phone;
	}
	public void setPhone(String phone) 
	{
		this.phone = phone;
	}
	public String getEmail() 
	{
		return email;
	}
	public void setEmail(String email) 
	{
		this.email = email;
	}
	public double getSalary() 
	{
		return salary;
	}
	public void setSalary(double salary) 
	{
		this.salary = salary;
	}
	@Override
	public String toString() 
	{
		return "Employee [empid=" + empid + 
		", name=" + name + ", designation="
		+ designation + ", phone=" + phone
		+ ", email=" + email + ", salary="
		+ salary + "]\n";
	}
}

Step-3 Create a Mapper Class

  • This xml contains the sql queries.
  • This file is a mapping file for out Employee POJO class.
<?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="employee-ns">
<resultMap type="com.srivastava.mybatis.dto.Employee"
	id="employeeResult">
<result property="empid" column="emp_id"
	javaType="java.lang.Integer"
	jdbcType="INTEGER" />
<result property="name" column="emp_name"
	javaType="java.lang.String"
	jdbcType="VARCHAR" />
<result property="designation" column="desig"
	javaType="java.lang.String"
	jdbcType="VARCHAR" />
<result property="phone" column="phone"
	javaType="java.lang.String"
	jdbcType="VARCHAR" />
<result property="email" column="email"
	javaType="java.lang.String"
	jdbcType="VARCHAR" />
<result property="salary" column="salary"
	javaType="java.lang.Double"
	jdbcType="DOUBLE" />

</resultMap>
<select id="getAll" resultMap=
	"employeeResult">
select * from employee
</select>
<select id="getById" resultMap=
	"employeeResult" parameterType="int">
select * from employee where emp_id=#{id}
</select>
<select id="getByIdAndName" 
	resultMap="employeeResult"
	parameterType=
	"com.srivastava.mybatis.dto.Employee">
select * from employee where emp_id=#{empid}
	and emp_name=#{name}
</select>
<insert id="empInsert" parameterType=
	"com.srivastava.mybatis.dto.Employee">
insert into employee 
	(emp_id,emp_name,desig,phone,email,salary)
values(#{empid},#{name},#{designation},
	#{phone},#{email},#{salary});
</insert>
<delete id="empDelete" parameterType="int">
delete from employee where emp_id=#{id};
</delete>
<update id="empUpdate" parameterType=
"com.srivastava.mybatis.dto.Employee">
update employee set emp_name=#{name} , 
desig = #{designation} , phone=#{phone},
email=#{email} where emp_id=#{empid};
</update>
</mapper>
		
		

The resultMap element fetch the data from the JDBC ResultSets, so developer is not require to fetch the data from the resultset also not require to add the resultset result into an object.
    It has three Tag for Every CRUD Operation :
  1. For Create it has insert tag , you can pass the parameter by using e.g #{id}.
  2. For Read it has select tag For Update it has update tag
  3. For Delete it has delete tag.