iBatis example – ibatis tutorial for beginners

iBatis (currently known as MyBatis) is another popular persistence framework (like hibernate) which automates the mapping between SQL database objects (e.g. tables) and objects in Java, .NET, and Ruby on Rails. This mapping is created and maintained using xml configuration files. These configuration files contain various SQL statements and other framework related options.

The main benefits of iBatis over other persistence frameworks is that it provides a greater flexibility and control over SQL statements writing. It means that any database expert can write these optimized statements and java developer just need to merge it is xml files.

In iBatis tutorial, We will learn to develop our first hello world application using iBatis. I will suggest you to follow below steps yourself but if you are in hurry, directly download the source code.

1. Create maven application in eclipse

To do this, open command prompt and navigate to workspace. Run below command:

mvn archetype:generate -DgroupId=com.howtodoinjava.ibatis.demo -DartifactId=ibatisHelloWorld
-DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

This command will create a default maven project in your workspace. Now convert it to eclipse project.

mvn eclipse:eclipse

Import the project in eclipse.

2. iBatis maven dependencies

Below dependencies are required for adding iBatis to project.

<dependency>
	<groupid>org.apache.ibatis</groupid>
	<artifactid>ibatis-sqlmap</artifactid>
	<version>2.3.4.726</version>
</dependency>

I am using MySQL as database. So adding database connecting driver support.

<dependency>
	<groupid>mysql</groupid>
	<artifactid>mysql-connector-java</artifactid>
	<version>5.1.9</version>
</dependency>

For logging purpose, add log4j support.

<dependency>
	<groupid>log4j</groupid>
	<artifactid>log4j</artifactid>
	<version>1.2.17</version>
</dependency>

3. Create Database

Create new database demoDB in mysql and create a new table USERINFO. We will use this table for data persistence.

CREATE TABLE USERINFO
(
ID INT,
NAME VARCHAR(100),
EMAIL VARCHAR(50),
PASSWORD VARCHAR(16),
STATUS INT
);

4. Create model class

Model class which will map to created table in database. This class is essentially a POJO and it will have exactly one field for corresponding column in database table.

package com.howtodoinjava.ibatis.demo.dto;

public class UserTEO
{
    private Integer id;
    private String name;
    private String email;
    private String password;
    private int status;

    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    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 getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public int getStatus() {
        return status;
    }
    public void setStatus(int status) {
        this.status = status;
    }
}

5. DAO – SqlMapClient

This will be implemented using an interface UserDao and implementation class UserDaoIbatis. It can be different names based on your choice.

package com.howtodoinjava.ibatis.demo.dao;

import com.howtodoinjava.ibatis.demo.dto.UserTEO;
import com.ibatis.sqlmap.client.SqlMapClient;

public class UserDaoIbatis implements UserDao
{
	@Override
	public UserTEO addUser(UserTEO user, SqlMapClient sqlmapClient) {
		try
		{
			Integer id = (Integer)sqlmapClient.queryForObject("user.getMaxId");
			id = id == null ? 1 : id + 1;
			user.setId(id);
			user.setStatus(1);
			sqlmapClient.insert("user.addUser", user);
			user = getUserById(id, sqlmapClient);
			return user;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public UserTEO getUserById(Integer id, SqlMapClient sqlmapClient) {
		try
		{
			UserTEO user = (UserTEO)sqlmapClient.queryForObject("user.getUserById", id);
			return user;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void deleteUserById(Integer id, SqlMapClient sqlmapClient) {
		try
		{
			sqlmapClient.delete("user.deleteUserById", id);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
}

6. Write sqlmaps and sqlmap config

This is the main part. In sqlmap config, we need to give config settings like databse connection properties and path to sqlmaps where we write real sql queries.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
        PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
    
    <settings useStatementNamespaces="true"/>
    
    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
          <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
          <property name="JDBC.ConnectionURL"  value="jdbc:mysql://localhost:3306/demoDB"/>
          <property name="JDBC.Username" value="root"/>
          <property name="JDBC.Password" value="lg225295"/>
        </dataSource>
      </transactionManager>
    
    <sqlMap resource="user.xml"/>

</sqlMapConfig>

Lets write sqlmap with queries in it and map the result to UserTEO.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap  PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> 
<sqlMap namespace="user"> 

	<typeAlias alias="USER" type="com.howtodoinjava.ibatis.demo.dto.UserTEO" />

	<resultMap id="userResultMap" class="USER">
		<result property="id" column="ID" />
  		<result property="name" column="NAME" />
  		<result property="email" column="EMAIL" />
  		<result property="password" column="PASSWORD" />
  		<result property="status" column="STATUS" />
	</resultMap>
	
	<select id="getUserById" parameterClass="java.lang.Integer" resultMap="userResultMap">
		  SELECT * FROM USERINFO WHERE ID = #value#
	</select>
	
	<select id="getMaxId" resultClass="java.lang.Integer">
		  SELECT MAX(ID) FROM USERINFO
	</select>
	
	<insert id="addUser" parameterClass="USER">
		INSERT INTO USERINFO (ID,NAME,EMAIL,PASSWORD,STATUS)
		 VALUES(#id#,#name#,#email#,#password#,#status#);
	</insert>
	
	<delete id="deleteUserById" parameterClass="java.lang.Integer">
		  DELETE FROM USERINFO WHERE ID = #value#
	</delete>
	
</sqlMap>

7. iBatis Demo

To test iBatis hello world example, we need to create sqlmapClient which will act as EntityManager in JPA. It will connect to database and execute the required queries and update the result back to data mappers.

SQLMapClient can be constructed in different ways in different frameworks, but as this is ibatis example application so I am creating it using code.

package com.howtodoinjava.ibatis.demo;

import java.io.Reader;

import com.howtodoinjava.ibatis.demo.dao.UserDao;
import com.howtodoinjava.ibatis.demo.dao.UserDaoIbatis;
import com.howtodoinjava.ibatis.demo.dto.UserTEO;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class TestMain 
{
	public static void main(String[] args) throws Exception
	{
		//Initialize dao
		UserDao manager = new UserDaoIbatis();

		//Create the SQLMapClient
		Reader reader = Resources.getResourceAsReader("sql-maps-config.xml");
		SqlMapClient sqlmapClient = SqlMapClientBuilder.buildSqlMapClient (reader);

		//Create a new user to persist
		UserTEO user = new UserTEO();
		user.setId(1);
		user.setName("Demo User");
		user.setPassword("password");
		user.setEmail("demo-user@howtodoinjava.com");
		user.setStatus(1);

		//Add the user
		manager.addUser(user,sqlmapClient);

		//Fetch the user detail
		UserTEO createdUser = manager.getUserById(1, sqlmapClient);
		System.out.println(createdUser.getEmail());

		//Lets delete the user
		manager.deleteUserById(1, sqlmapClient);
	}
}

Running above main method will execute the sql command and this can be verified using logs generated.

DEBUG [main] - Created connection 12589126.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {conn-100000} Preparing Statement:      SELECT MAX(ID) FROM USERINFO
DEBUG [main] - {pstm-100001} Executing Statement:      SELECT MAX(ID) FROM USERINFO
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [MAX(ID)]
DEBUG [main] - {rset-100002} Result: [null]
DEBUG [main] - Returned connection 12589126 to pool.
DEBUG [main] - Checked out connection 12589126 from pool.
DEBUG [main] - {conn-100003} Connection
DEBUG [main] - {conn-100003} Preparing Statement:    INSERT INTO USERINFO (ID,NAME,EMAIL,PASSWORD,STATUS)    VALUES(?,?,?,?,?);
DEBUG [main] - {pstm-100004} Executing Statement:    INSERT INTO USERINFO (ID,NAME,EMAIL,PASSWORD,STATUS)    VALUES(?,?,?,?,?);
DEBUG [main] - {pstm-100004} Parameters: [1, Demo User, demo-user@howtodoinjava.com, password, 1]
DEBUG [main] - {pstm-100004} Types: 
DEBUG [main] - Returned connection 12589126 to pool.
DEBUG [main] - Checked out connection 12589126 from pool.
DEBUG [main] - {conn-100005} Connection
DEBUG [main] - {conn-100005} Preparing Statement:      SELECT * FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100006} Executing Statement:      SELECT * FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100006} Parameters: [1]
DEBUG [main] - {pstm-100006} Types: 
DEBUG [main] - {rset-100007} ResultSet
DEBUG [main] - {rset-100007} Header: [ID, NAME, EMAIL, PASSWORD, STATUS]
DEBUG [main] - {rset-100007} Result: [1, Demo User, demo-user@howtodoinjava.com, password, 1]
DEBUG [main] - Returned connection 12589126 to pool.
DEBUG [main] - Checked out connection 12589126 from pool.
DEBUG [main] - {conn-100008} Connection
DEBUG [main] - {conn-100008} Preparing Statement:      SELECT * FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100009} Executing Statement:      SELECT * FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100009} Parameters: [1]
DEBUG [main] - {pstm-100009} Types: 
DEBUG [main] - {rset-100010} ResultSet
DEBUG [main] - {rset-100010} Header: [ID, NAME, EMAIL, PASSWORD, STATUS]
DEBUG [main] - {rset-100010} Result: [1, Demo User, demo-user@howtodoinjava.com, password, 1]
DEBUG [main] - Returned connection 12589126 to pool.
demo-user@howtodoinjava.com
DEBUG [main] - Checked out connection 12589126 from pool.
DEBUG [main] - {conn-100011} Connection
DEBUG [main] - {conn-100011} Preparing Statement:      DELETE FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100012} Executing Statement:      DELETE FROM USERINFO WHERE ID = ?
DEBUG [main] - {pstm-100012} Parameters: [1]
DEBUG [main] - {pstm-100012} Types: 
DEBUG [main] - Returned connection 12589126 to pool.

Happy Learning !!