Configuring MyBatis 3 With MySQL In Java Projects

What is MyBatis?

MyBatis is a Higher version of iBatis. Its lightweight persistence framework which is mapping SQL databases and objects in Java, .NET, and Ruby on Rails.

MyBatis support for custom SQL, Views, advanced mappings, stored procedures and declarative data caching.It is the new version of the iBatis Data Mapper Java Framework, which allows you to use a relational database with object-oriented applications.

In single line, we can describe MyBatis as a SQL Mapping Framework for Java, .NET and Ruby on Rails.

MyBatis Design Aspects:

MyBatis comes with the following design aspects:

• Simplicity:
  MyBatis is widely considered as the simplest persistence framework.
• Fast Development: MyBatis’s aspects is useful to facilitate hyper-fast.
• Portability: MyBatis can be implemented for almost all language or platform like Java, Ruby, and C# for Microsoft .NET.
• Independent Interfaces: It provides database-independent interfaces and APIs that help the rest of the application remain independent of any persistence-related resources.
• Open Source: Its free and an open source software.

Why MyBatis?

As we all are familier with today’s market conditions.A lot of freamworks are available for Database Mapping Architecrture like Hibernet then surely one question must comes into mind why we use MyBatis?
If we observe following few suitiations then we got the answer.
Suppose if

• you want to create your own SQL’s and are willing to maintain them,
• your environment is driven by relational data model,
• you have to work existing and complex schema’s

then positively MyBatis is a better solution.

Also If you are using stored procedures, well you can do it in Hibernate but it is little difficult in comparision of MyBatis. As an alternative solution MyBatis maps results sets to objects, so no need to care about table structures.

Prepare a Database for sample example:

• Start MySQL Server to prepare database.

• Run following queries in MySQL using command prompt or IDE like SQLyog or PhpMyAdmin.

  CREATE DATABASE IF NOT EXISTS studentdb;
  
  USE studentdb;
  
  /*Table structure for table Student */
  
  DROP TABLE IF EXISTS Student;
  
  CREATE TABLE Student (
  rollNo INT(11) NOT NULL AUTO_INCREMENT,
  studentName VARCHAR(30) DEFAULT NULL,
  marks INT(11) DEFAULT NULL,
  PRIMARY KEY (rollNo)
  );
  
  /*Data for the table Student */
  
  INSERT INTO Student(rollNo,studentName,marks) VALUES (1,'John P',82),(2,'Abdul K',94),(3,'Raju S',35);

Let’s get started:

To see how Tomcat, MyBatis and MySQL work together we’re going to create a small java application in Eclipse called “MyBatisDemo”.You can download the Sample Project Here.

• Create New Java Project in Eclipse named as “MyBatisDemo”.
• Create new folder under project named as “lib” for external jar files.
• Now create a new package structure like –
  com.batis.mybatisdao
  com.batis.mybatisdao.tests
  com.batis.mybatisdao.tests.dao
  com.batis.mybatisdao.tests.model
  Now your eclipse project structure look like a following screens-
  

  

• Configuration XML – Create “mybatisConfig.xml” under src folder.

  Consider the following:
  

  • We are going to use JDBC to access the database studentdb.
    
  • JDBC driver for MySQL is “com.mysql.jdbc.Driver”.
    
  • Connection URL is “jdbc:mysql://localhost:3306/studentdb”.
    
  • We would use username and password is “root” and “mobisoft<".
    
  • Our sql statement mappings for all the operations.
    
  Based on the above assumption we have to create an XML configuration file with name as mybatisConfig.xml with the following content.

  <?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="dbcp.properties"/>
  <settings>
  <setting name="useGeneratedKeys" value="true"/>
  </settings>
  <typeAliases>
  <typeAlias alias="Student" type="com.batis.mybatisdao.tests.model.Student"/>
  </typeAliases>
  <environments default="mobisoft">
  <environment id="mobisoft">
  <transactionManager type="JDBC"/>
  <dataSource type="POOLED">
  <property name="driver" value="${db.driver}"/>
  <property name="url" value="${db.url}"/>
  <property name="username" value="${db.user}"/>
  <property name="password" value="${db.pass}"/>
  </dataSource>
  </environment>
  </environments>
  <mappers>
  <mapper resource="com/batis/mybatisdao/tests/model/StudentMapper.xml" />
  </mappers>
  </configuration>
  
  

  Based on the mybatisConfig.xml file we have to create an properties file with name as dbcp.properties file on same location for database credentials with the following content.

  db.url=jdbc:mysql://localhost/studentdb
  db.driver=com.mysql.jdbc.Driver
  db.user=root
  db.pass=mobisoft

• Student POJO Class:
  

  Based on the Following POJO Class specification, the following class has 3 properties: rollNo, studentName, and marks. These match up exactly with the column names in the select statement in XML Mapper File.
  Now create one class under com.batis.mybatisdao.tests.model package named as “Student” with the following content.

  
  package com.batis.mybatisdao.tests.model;
  
  public class Student {
  private int rollNo;
  private String studentName;
  private int marks;
  
  public int getRollNo() {
       return rollNo;
  }
  
  public void setRollNo(int rollNo) {
     this.rollNo = rollNo;
  }
  
  public String getStudentName() {
       return studentName;
  }
  
  public void setStudentName(String studentName) {
       this.studentName = studentName;
  }
  
  public int getMarks() {
      return marks;
  }
  
  public void setMarks(int marks) {
     this.marks = marks;
  }
  
  @Override
  public String toString() {
       return "[Status] " + "(" + rollNo + ") " + studentName + " Marks : "+marks ;
  }
  }
  

• XML Mapper File:
  Now its time to wirte a mapper file of Student POJO Class which is useful for CRUD Operations of Student records from Database. Create new xml file under com.batis.mybatisdao.tests.model package named as “StudentMapper.xml” with the following content.

  <?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="mobisoft">
  
  	<select id="getAllStudent" parameterType="int" resultType="Student">
  		select rollNo as "rollNo", studentName as "studentName", marks as "marks"
  		from studentdb.Student order by rollNo
  	</select>
  
  	<insert id="createStudent" keyColumn="rollNo" useGeneratedKeys="true" parameterType="Student">
  		insert into studentdb.Student (studentName, marks)
  		values (#{studentName}, #{marks})
  	</insert>
  
  	<update id="updateStudent" parameterType="Student">
  		update studentdb.Student set studentName = #{studentName}, marks = #{marks}
  		where rollNo = #{rollNo}
  	</update>
  
  	<delete id="deleteStudent" parameterType="int">
  		delete from studentdb.Student where rollNo = #{rollNo}
  	</delete>
  </mapper>
  

• Creating DAO Class:

  Now create One Dao Class under the com.batis.mybatisdao.tests.dao package named as “StudentDao” with the following content to acces generic methods from MyBatis framework. Its a simple example DAO which uses the MyBatisDAO Framework.

  package com.batis.mybatisdao.tests.dao;
  
  import org.apache.ibatis.session.SqlSessionFactory;
  
  import com.batis.mybatisdao.JdbcBaseBatisDAO;
  import com.batis.mybatisdao.tests.model.Student;
  
  public class StudentDAO extends JdbcBaseBatisDAO<Student, Integer> {
  
      public StudentDAO(SqlSessionFactory containerSessionFactory) {
  	   super(containerSessionFactory);
      }
  }
  
  

• Implementation of Basic Interface for POJO’s:

  Here we implement one generic interface through which we can access any defined method for any type of objects.
  Now create new interface under the com.batis.mybatisdao package named as “BaseBatisDao” with the following content –

  package com.batis.mybatisdao;
  
  import java.util.ArrayList;
  
  import org.apache.ibatis.exceptions.PersistenceException;
  
  public interface BaseBatisDao<Obj, PK>{
  	public ArrayList<Obj> getAllStudentList() throws PersistenceException;
  	public int insertStudent(Obj student) throws PersistenceException;
  	int updateStudentDetails(Obj student) throws PersistenceException;
  	int deleteStudentFromDB(PK rollNo) throws PersistenceException;
  }
  
  

• Implementation of Jdbc Class:

  It is a abstract class through which we can implements above generic interface for implementation of all define methods in interface.
  Now create new abstract class under the com.batis.mybatisdao package named as “JdbcBaseBatisDAO” with the following content –

  package com.batis.mybatisdao;
  
  import java.util.ArrayList;
  
  import org.apache.ibatis.exceptions.PersistenceException;
  import org.apache.ibatis.session.SqlSession;
  import org.apache.ibatis.session.SqlSessionFactory;
  import org.apache.log4j.Logger;
  
  public abstract class JdbcBaseBatisDAO<Obj, PK> implements BaseBatisDao<Obj, PK> {
  
      private static Logger log = Logger.getLogger(JdbcBaseBatisDAO.class);
      private static final String NAMESPACE = "mobisoft";
  
      private SqlSessionFactory sqlSessionFactory;
  
      public static final String PREFIX_SELECT_QUERY = "get";
      public static final String PREFIX_INSERT_QUERY = "create";
      public static final String PREFIX_UPDATE_QUERY = "update";
      public static final String PREFIX_DELETE_QUERY = "delete";
  
     public JdbcBaseBatisDAO(SqlSessionFactory sf) {
  	this.sqlSessionFactory = sf;
  	if (sf == null)
  	    log.error("Error: sessionFactory loading failed.");
      }
  
      @SuppressWarnings("unchecked")
      public ArrayList<Obj> getAllStudentList() throws PersistenceException {
  
  	SqlSession session = sqlSessionFactory.openSession();
  	ArrayList<Obj> studentList = null;
  	try {
  	    String query = NAMESPACE + "." + PREFIX_SELECT_QUERY + "AllStudent";
  	     studentList = (ArrayList<Obj>) session.selectList(query);
  	} finally {
  	    session.close();
  	}
  	return studentList;
      }
  
      public int insertStudent(Obj student) throws PersistenceException {
  	SqlSession session = sqlSessionFactory.openSession();
  	Integer rollNo = null;
  	try {
  	    String query = NAMESPACE + "." + PREFIX_INSERT_QUERY + "Student";
  	    rollNo = (Integer) session.insert(query, student);
  	    session.commit();
  	} finally {
  	    session.close();
  	}
  	return rollNo;
      }
  
      public int updateStudentDetails(Obj student) throws PersistenceException {
  
  	SqlSession session = sqlSessionFactory.openSession();
  	Integer rollNo = null;
  	try {
  	    String query = NAMESPACE + "." + PREFIX_UPDATE_QUERY + "Student";
  	    rollNo = session.update(query, student);
  	    session.commit();
  
  	} finally {
  	    session.close();
  	}
  	return rollNo;
      }
  
      public int deleteStudentFromDB(PK rollNo) throws PersistenceException {
  	SqlSession session = sqlSessionFactory.openSession();
  	Integer status = null;
  	try {
  	    String query = NAMESPACE + "." + PREFIX_DELETE_QUERY + "Student";
  	    status = session.delete(query, rollNo);
  	    session.commit();
  	} finally {
  	    session.close();
  	}
  	return status;
      }
  }
  
  

• Create Testing MyBatis Demo class:

  Now create new class under the com.batis.mybatisdao.tests package named as “TestStudentDAO” with the following content to test the MyBatis Demo which we create till now.

  package com.batis.mybatisdao.tests;
  
  import java.io.Reader;
  import java.util.ArrayList;
  
  import org.apache.ibatis.io.Resources;
  import org.apache.ibatis.session.SqlSessionFactory;
  import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  import org.apache.log4j.Logger;
  import org.junit.BeforeClass;
  import org.junit.Test;
  
  import com.batis.mybatisdao.tests.dao.StudentDAO;
  import com.batis.mybatisdao.tests.model.Student;
  
  public class TestStudentDAO {
  
      private static Logger log = Logger.getLogger(TestStudentDAO.class);
      private static SqlSessionFactory sf;
      private static StudentDAO studentDao;
  
      @BeforeClass
      public static void setUp() throws Exception {
  
  	log.info(" Mybatis setup is in progress...");
  	String resource = "mybatisConfig.xml";
  	Reader reader = Resources.getResourceAsReader(resource);
  	sf = new SqlSessionFactoryBuilder().build(reader, "mobisoft");
  	studentDao = new StudentDAO(sf);
  	log.info(" Connection Established Successfully.");
      }
  
      @Test
      public static void getAllStudents() {
  
  	ArrayList<Student> list = studentDao.getAllStudentList();
  	log.info("Student List: ");
  	printStudentList(list);
  
      }
  
      public static void createStudent() {
  
  	Student student = new Student();
  	student.setStudentName("Mayur");
  	student.setMarks(82);
  	Integer rollNo = studentDao.insertStudent(student);
  	log.info("insert Student Details: " + rollNo);
  
      }
  
      public static void updateStudent() {
  
  	Student student = new Student();
  	student.setRollNo(1);
  	Integer result = studentDao.updateStudentDetails(student);
  	log.info("update Student Details: " + result);
      }
  
      public static void deleteStudent() {
  
  	Integer rollNo = studentDao.deleteStudentFromDB(3);
  	log.info("delete Student's rollNo: " + rollNo);
  
      }
  
      private static void printStudentList(ArrayList<?> studentList) {
  	for (int i = 0; i < studentList.size(); i++) {
  	    log.info(studentList.get(i).toString());
  	}
      }
  
      public static void main(String arg[]) {
  	try {
  	    setUp();
  	    // updateStudent();
  	    // createStudent();
  	    // deleteStudent();
  	    getAllStudents();
  	} catch (Exception e) {
  	    e.printStackTrace();
  	}
      }
  }
  
  

  Now our MyBatisDemo Application is ready to built and deploy.

• Compilation and Run:

  Now right click on “TestStudentDAO” class and click on “Run as” than click on “Java Appilcation” then our application run successfully and i am using apache log4j to do all the the loggings.

  This is what happen on my java console,

  INFO [main] (TestStudentDAO.java:26) - Mybatis setup is in progress...
  INFO [main] (TestStudentDAO.java:31) - Connection Established Successfully.
  INFO [main] (TestStudentDAO.java:40) - Student List:
  INFO [main] (TestStudentDAO.java:78) - [Status] (1) John P Marks : 82
  INFO [main] (TestStudentDAO.java:78) - [Status] (2) Abdul K Marks : 94
  INFO [main] (TestStudentDAO.java:78) - [Status] (3) Raju S Marks : 35

Technical pros of MyBatis:

Here are few pros of using MyBatis:

• Supports Stored procedures.
• Supports Inline SQL.
• Supports Dynamic SQL.
• Supports O/RM.

Summary of MyBatis:

MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results.It is a easiest way to build better database oriented application more quickly, efficiently with less code.

References for Blog:

1. http://www.mybatis.org/core/

Download the Sample Project:Here