iBatis hello world example with source code

iBatis 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.

Ads by Google

The main advantage of iBatis over other persistence frameworks is : 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 this post, 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.

Step 1) Create a eclipse java project using maven

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. If you feel any problem, follow the steps given in this post.

Step 2) Add 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>

Now update the eclipse project using command :

mvn eclipse:eclipse

Step 3) 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
);

Step 4) Create the 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;
    }
}

Step 5) Write Dao layer

This will be implemented using an interface UserDao.java and implementation class UserDaoIbatis.java. It can be different names based on your choice. I am not writing code for UserDao.java due to space constraint but it is available in attached source code. UserDaoIbatis.java is like this:

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();
		}
	}
}

Step 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.java.

<?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>

Step 7) Test the code

To test the code, 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 hello world application, 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.

user-created-ibatis

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.

Download sourcecode

happy Learning !!

11 thoughts on “iBatis hello world example with source code”

  1. Hi Lokesh
    I have a problem where I can see in the logs that its preparing the insert statement but not executing the insert which is throwing an error .
    Can you please provide insight as to why this could be happening?
    Thanks in advance for your help

      1. [APP] [2014-08-01 15:40:31.486] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] {conn-100328} Connection
        [APP] [2014-08-01 15:40:31.487] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] {conn-100328} Preparing Statement: insert into table1 ( userName, userId, userAddress, userPhone ) values ( ?,?,?,? )
        [APP] [2014-08-01 15:40:31.487] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1c8b69b] for key [org.apache.commons.dbcp.BasicDataSource@737611] bound to thread [http-8080-Processor22]
        [APP] [2014-08-01 15:40:31.590] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1c8b69b] for key [org.apache.commons.dbcp.BasicDataSource@737611] bound to thread [http-8080-Processor22]
        [APP] [2014-08-01 15:40:31.620] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1c8b69b] for key [org.apache.commons.dbcp.BasicDataSource@737611] bound to thread [http-8080-Processor22]
        [APP] [2014-08-01 15:40:31.621] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [INFO ] Dao Ended: [userDaoImpl#createuser] Execution Time=[179ms]

  2. Hi Lokesh, Could you please provide some idea on how to do transaction management using multiple database (SQL Server & DB2) in iBatis with Spring. I have implemented tx in multiple databases using iBatis with Struts 1 (or plain Java) in Hewitt. No idea how to do when integrating with Spring.

  3. Tks, very usefull, but one question, why when i see the table in the database can see the changes? it always told me that is empty. I can see the same logs that you show …

Note:- In comment box, please put your code inside [java] ... [/java] OR [xml] ... [/xml] tags otherwise it may not appear as intended.

Leave a Reply

Your email address will not be published. Required fields are marked *


+ four = 5

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>