iBATIS Tutorial

iBATIS Introduction

iBATIS is a persistence framework which automates the mapping between SQL database objects (e.g. tables) and objects in Java (e.g. user defined class or collection object). This mapping is created and maintained using xml configuration files. These configuration files contain various SQL statements and other framework related options. Simplicity is the biggest advantage of the iBATIS Data Mapper over object relational mapping tools. To use iBATIS Data Mapper you rely on your own objects, XML, and SQL. With iBATIS Data Mapper you have the full power of both SQL and stored procedures at your fingertips.

iBATIS is a simple but complete framework that makes it easy for you to map your objects to your SQL statements or stored procedures. The goal of the iBATIS framework is to obtain 80% of data access functionality using only 20% of the code.

 

What is iBATIS?

A JDBC Framework

• Developers write SQL, iBATIS executes it using JDBC.
• No more try/catch/finally/try/catch. 

A SQL Mapper

• Automatically maps object properties to prepared statement parameters.
• Automatically maps result sets to objects.
• Support for getting rid of N+1 queries.

 

Why iBATIS Data Mapper is used?

Developers often create maps between objects within an application. One definition of a Mapper is an “object that sets up communication between two independent objects.”

A Data Mapper is a “layer of mappers that moves data between objects and a database while keeping them independent of each other and the mapper itself“.

You provide the database and the objects; iBATIS provides the mapping layer that goes between the two.

 

Advantages of iBATIS

• Supports stored procedures − iBATIS encapsulates SQL in the form of stored procedures so that business logic is kept out of the database, and the application is easier to deploy and test, and is more portable.
• Supports inline SQL − No precompiler is needed, and you have full access to all of the features of SQL.
• Supports dynamic SQL − iBATIS provides features for dynamically building SQL queries based on parameters.
• Supports O/RM − iBATIS supports many of the same features as an O/RM tool, such as lazy loading, join fetching, caching, runtime code generation, and inheritance.

 

iBatis Tutorial

In this post we will learn how to use iBatis framework in automation project. The major advantage of this framework is we can execute complex SQL statements easily without writing much of code. The execution is very reliable and is error free unless we do not make unnecessary logical mistakes.

Project Download

Step 1 Create a Maven Project or a Java Project using Eclipse.

For creating a Maven Project, please click here.

For creating a Java Project, please click here.

 

Step 2. Add external dependencies in to Maven project

In order to use iBatis we need to use ‘ibatis-sqlmap.jar’ or below maven dependency

For interacting with database we need to add ‘mysql-connector-java.jar’ or below maven dependency

To read configuration files we need ‘commons-configuration.jar’ file or below maven dependency

Please refer the below screenshot for adding dependencies:

 

Step 3. Set up a Local DataBase

We need a local/remote database server in case we don’t have one we can install mysql as mentioned in below steps and create a database with a table to query

1. Install mysql server using below url  http://dev.mysql.com/downloads/installer/
2. Set environment variables for installed mysql so that it can be executed from any directory
   PATH=C:\Program Files\MySQL\MySQL Server 5.5\bin;
3. Open cmd and type mysql and it will start mysql prompt to execute sql queries
4. We will change the default password for mysql root user by using below query: SET PASSWORD for ‘root’@’localhost’ = PASSWORD(‘password’);
5. We will create a new database, create a table and insert data into table for our testing

CREATE TABLE employee ( emp_id int(11) DEFAULT NULL, emp_name varchar(100) DEFAULT NULL, emp_salary int(11) DEFAULT NULL )
insert into employee (emp_id,emp_name,emp_salary) values(1,’olx’,100);
insert into employee (emp_id,emp_name,emp_salary) values(2,’uber’,300);
insert into employee (emp_id,emp_name,emp_salary) values(3,’safari’,300);

 

Step 4: Set Up Project

Since the Maven project is already created as mentioned in step1, with structure

• src/main/java – Any base class we need to add
• src/test/java – Any test case class we need to add

so we will add one more directory structure to handle resources file.

• src/main/resources/META-INF/

1) We will add an XML configuration file with name ‘SqlMapConfig.xml’ on above mentioned path with the following content. This is the file where we need to provide all configurations required for iBatis like

• jdbc mysql connector class : com.mysql.jdbc.Driver
• mysql data base ip/hostname and portnumber with name of default database to connect with: jdbc:mysql://127.0.0.1:3306/jmeter.
  Note : We used localhost ip here as we are querying locally installed mysql database and by default it runs on port number 3306
• mysql database server username and password
• Path of resource xml file where anyone can write independent queries for execution

The official documentation of file ‘SQLMapConfig.xml’ can be found at below url

https://ibatis.apache.org/docs/java/dev/com/ibatis/sqlmap/client/sql-map-config.txt

2) We will add a properties file “ibatis.properties” on above mentioned path which will contain data in key value pairs. This file will supply data (as mentioned in points under 4.1) to SqlMapConfig.xml

 

3) We will add ‘Resource XML‘(Common.xml). This is a file where we can write SQL queries to execute. For testing purpose I added 4 different statements which we come across while performing backend verification e.g. ‘select’, ‘insert’, ‘update’ and ‘delete’ statements

The official documentation of this file can be found at below URL

https://ibatis.apache.org/docs/java/dev/com/ibatis/sqlmap/client/sql-map.txt

 

Let’s quickly see the complete project structure.

Let’s quickly see the code we create a test case class in src/test/java folder

1. Create static variable to hold SQL Map config and Properties file
2. Create static object of SqlMapClient class
3. Before test begins we configuration ibatis by preparing sqlMapClient
   • Read db params from ibatis.properties file
   • Create a properties list to pass dynamic db params read from ibatis.properties file
   • Creating a reader instance to read sqlMapConfig.xml file
   • Building sqlMapClient to execute queries by passing data prepared in step 3.2 and 3.3

Project Download: Download the above project.

Step 5: Set Up & Executing Test

1) Select Statement Test : Create first test to execute select query by calling ‘queryForList’ method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.getEmpDetails<FileName.select statement id value in Common.xml>
• Map Object containing dynamic arguments to be passed in where clause of select query. If we don’t want to pass any argument we can pass null
• Store select SQL output in List<Map<String,Object>>
• Traverse the Map using for loop and get the data present for key

Execution results of ‘Select’ statement

 

2) Insert Statement Test : Create second test to execute insert query by calling ‘insert’ method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.addEmpDetails<FileName.insert statement id value in Common.xml>
• Map Object containing values to be inserted into table
• This Function returns the primary key of the newly inserted row, but we are not storing it.

Execution results of ‘Insert’ statement

 

3) Update Statement Test : Create third test to execute update query by calling ‘update method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.updateEmpDetails<FileName.update statement id value in Common.xml>
• Map Object containing dynamic arguments to be passed in where clause of update query. If we don’t want to pass any argument we can pass null.
• This Function returns the number of rows affected after update query is executed, but we are not storing it.

Execution results of ‘Update’ statement

4) Delete Statement Test : Create fouth test to execute delete query by calling ‘delete method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.deleteEmpDetails<FileName.delete statement id value in Common.xml>
• Map Object containing dynamic arguments to be passed in where clause of delete query. If we don’t want to pass any argument we can pass null.
• This Function returns the number of rows affected after delete query is executed, but we are not storing it.

Execution results of ‘Delete’ statement