In this article lets have a brief introduction about Ibatis framework.
Before getting into IBatis, lets get to know the basics about ORM(Object Relational Mapping).
What is ORM ?
Consider a scenario in your project (or application) where you want to interact with a database. Assume your project is developed using an Object Oriented Language (say Java) and the database with which you are going to interact with is a Relational Database (say Oracle). Mostly we will use JDBC api to communicate with the database. In general, there is going to be an interaction between object oriented language and a relational database. Do you see a mismatch here? yes... Lets have a closer look in to what is happening in this interaction.
Java is an object oriented programming language, so mostly we will deal with classes, objects, object behavior and their relationship. The data base comes into picture when there is a need to store the data. For example, consider an Employee class with attributes like emp_id, emp_name, age, plot-No, street address, city and zip code. To store an employee object in to database, we can have a table EMPLOYEE with the above attributes in the Employee class as columns. This will work fine as there is a direct mapping between a class and a database table. Now, lets remove plot-No, street address, city and zip code attributes from Employee class and move them to a new class Address and the Employee class will have a reference to Address object. Now think about how will you make the changes in the DB side. You need to create a new table Address and this table should have a foreign key reference to emp_id from employee table. And when a user requests for Employee object, at the back-end both the employee table and Address table has to be queried to form the Employee object. Technically speaking, object composition is mapped to parent child relationship in relational model. Now, there seems to be a mismatch. It becomes really worse, if you start using other OOPs concepts like polymorphism, inheritance etc., there is not matching concepts available in relational model. So, there is a clear mismatch between oo programming and relational model when they need to interact with each other. To bridge this gap, we have a concept called ORM or Object Relational Mapping. ORM is a middle ware between the service layer and DB. It tries to a make a smooth integration between the two layers. There are many ORM tools available in market like TopLink, Hibernate, JPA, Ibatis etc,.
There are 3 different implementations of ORM
LightWeight Mapping: Here classes in the application are manually mapped to relational tables. The Sql/Jdbc logic is hidden from business logic. Stored Procedures can also be used with this application.
Medium Object Mapping: Here application is designed around an object model. Sql queries are generated using code-generation tool at build time. Associations between objects are taken care by the persistence mechanism. It is well suited for medium sized applications. Stored procedures are generally not supported in this application.
Full Object Mapping: Here sophisticated object modeling with inheritance, composition, polymorphism etc., Persistence classes generally remain as pojos. Efficient fetching and caching strategies are implemented transparently to the application.
Ibatis falls under the light weight mapping category. iBATIS couples objects with SQL statements using a XML descriptor. Simplicity is the biggest advantage of the iBATIS Data Mapper over object relational mapping tools.
How Ibatis Works?
IBatis framework mainly uses the following two XML files as descriptors:
1. SQLMapConfig.xml - Global configuration including DB user/passwd/Driver/ sqlmap files
2. SQLMap.xml – There can be any number of such files in a single application. This file is the place where domain objects are mapped to SQL statements. This descriptor uses parameter maps to map the inputs to the statements and the result maps for mapping SQL ResultSets. This file also contains the queries.
Ibatis like other ORM tools abstracts the user from JDBC related stuff to access the database and makes the developer to concentrate only on the business logic.
The following are the steps for creating an IBatis application.
1. First create the business objects required for the application.
2. Create the relational data model required for the application (i.e DB tables).
3. Create a SQLMapConfig.xml file as follows
<settings useStatementNamespaces="true"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="driverName"/>
<property name="JDBC.ConnectionURL" value="jdbcUrl"/>
<property name="JDBC.Username" value="userName"/>
<property name="JDBC.Password" value="passWord"/>
</dataSource>
</transactionManager>
<sqlMap resource="Routers.xml"/>
</sqlMapConfig>
4. Now create another XML file specifying the mapping between bean and SQL queries. This file should be specified in the
Here is the Routers.xml file
<resultMap id="result" class="Routers">
<result property="custId" column="cust_id"/>
<result property="routerId" column="router_id"/>
<result property="routerName" column="router_name" jdbcType="CLOB"/>
<result property="device_type" column="device_type"/>
</resultMap>
<select id="getDevice" parameterClass="Router" resultClass="Routers">
select cust_id,router_id ,router_name ,device_type from routers r where cust_id = #custId#
</select>
<insert id="insertDevice" parameterClass="com.techdive.Routers">
INSERT INTO Routers (ROUTER_ID, ROUTER_NAME, SYS_OBJ_ID) VALUES (#routeId#, #routerName#, #sysObjId)
</insert>
<update id="updateDevice" parameterClass="com.techdive.Routers">
UPDATE Routers SET ROUTER_NAME = #routerName#, SYS_OBJ_ID = #sysObjId# WHERE ROUTER_ID = #routeId#
</update>
<delete id="deleteDevice" parameterClass=" com.techdive.Routers ">
DELETE Routers WHERE ROUTER_ID=#routeId#
</delete>
</sqlMap>
The above file consist of
Com.techdive.Routers to the columns in the DB table Router. This tag has an id attribute to uniquely identify the resultMap.
Then we have tags for Select, insert, update and delete queries. Each tag will have an ID, parameterClass and resultClass (only for select queries). The parameterClass contains attributes for input to the select query’s where clause. The resultClass will contain the resultMap Id.
Here is the Routers class for your reference.
5. Now we need to call those sql statements in the XML files using there ids and passing the parameterClass parameters. Once the queries are run by the framework, the results are returned using the resultMap class specified. Here is a sample code to do the same.
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import com.ibatis.sqlmap.client.event.RowHandler;
import com.ibatis.sqlmap.engine.execution.BatchException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* A SqlMapper for SQL Map implementations that provides a convenient method to
* access the SqlMapClient implementation.
*/
public class SqlMapper
{
private static SqlMapper sqlMapper = null;
private static SqlMapClient sqlMap = null;
private SqlMapper()
{
}
public static SqlMapper getInstance()
{
try
{
if (sqlMapper == null)
{
sqlMapper = new SqlMapper();
Reader reader = Resources
.getResourceAsReader(Constants.CONFIG_FILE);
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
}
}
catch (Exception e)
{
System.out.println(e);
}
return sqlMapper;
}
public Object clone()
{
return new CloneNotSupportedException();
}
/**
* Executes a mapped SQL INSERT statement. Insert is a bit different from
* other update methods, as it provides facilities for returning the primary
* key of the newly inserted row (rather than the effected rows). This
* functionality is of course optional. <p/> The parameter object is
* generally used to supply the input data for the INSERT values.
*
* @param id
* The name of the statement to execute.
* @param parameterObject
* The parameter object (e.g. JavaBean, Map, XML etc.).
* @return The primary key of the newly inserted row. This might be
* automatically generated by the RDBMS, or selected from a sequence
* table or other source.
*/
public Object insert(String id, Object parameterObject)
{
try
{
return sqlMap.insert(id, parameterObject);
}
catch (SQLException e)
{
return null;
}
}
/**
* Executes a mapped SQL INSERT statement. Insert is a bit different from
* other update methods, as it provides facilities for returning the primary
* key of the newly inserted row (rather than the effected rows). This
* functionality is of course optional. <p/> This overload assumes no
* parameter is needed.
*
* @param id
* The name of the statement to execute.
* @return The primary key of the newly inserted row. This might be
* automatically generated by the RDBMS, or selected from a sequence
* table or other source.
*/
public Object insert(String id)
{
try
{
return sqlMap.insert(id);
}
catch (SQLException e)
{
return null;
}
}
/**
* Executes a mapped SQL UPDATE statement. Update can also be used for any
* other update statement type, such as inserts and deletes. Update returns
* the number of rows effected. <p/> The parameter object is generally used
* to supply the input data for the UPDATE values as well as the WHERE
* clause parameter(s).
*
* @param id
* The name of the statement to execute.
* @param parameterObject
* The parameter object (e.g. JavaBean, Map, XML etc.).
* @return The number of rows effected.
*/
public int update(String id, Object parameterObject)
{
try
{
return sqlMap.update(id, parameterObject);
}
catch (SQLException e)
{
return 0;
}
}
/**
* Executes a mapped SQL DELETE statement. Delete returns the number of rows
* effected. <p/> The parameter object is generally used to supply the input
* data for the WHERE clause parameter(s) of the DELETE statement.
*
* @param id
* The name of the statement to execute.
* @param parameterObject
* The parameter object (e.g. JavaBean, Map, XML etc.).
* @return The number of rows effected.
*/
public int delete(String id, Object parameterObject)
{
try
{
return sqlMap.delete(id, parameterObject);
}
catch (SQLException e)
{
return 0;
}
}
/**
* Executes a mapped SQL SELECT statement that returns data to populate a
* single object instance. <p/> The parameter object is generally used to
* supply the input data for the WHERE clause parameter(s) of the SELECT
* statement.
*
* @param id
* The name of the statement to execute.
* @param parameterObject
* The parameter object (e.g. JavaBean, Map, XML etc.).
* @return The single result object populated with the result set data.
*/
public Object queryForObject(String id, Object parameterObject)
{
try
{
return sqlMap.queryForObject(id, parameterObject);
}
catch (SQLException e)
{
return null;
}
}
/**
* Executes a mapped SQL SELECT statement that returns data to populate the
* supplied result object. <p/> The parameter object is generally used to
* supply the input data for the WHERE clause parameter(s) of the SELECT
* statement.
*
* @param id
* The name of the statement to execute.
* @param parameterObject
* The parameter object (e.g. JavaBean, Map, XML etc.).
* @param resultObject
* The result object instance that should be populated with
* result data.
* @return The single result object as supplied by the resultObject
* parameter, populated with the result set data.
*/
public Object queryForObject(String id, Object parameterObject,
Object resultObject)
{
try
{
return sqlMap.queryForObject(id, parameterObject, resultObject);
}
catch (SQLException e)
{
return null;
}
}
/**
* Executes a mapped SQL SELECT statement that returns data to populate a
* number of result objects. <p/> The parameter object is generally used to
* supply the input data for the WHERE clause parameter(s) of the SELECT
* statement.
*
* @param id
* The name of the statement to execute.
* @param parameterObject
* The parameter object (e.g. JavaBean, Map, XML etc.).
* @return A List of result objects.
*/
public List queryForList(String id, Object parameterObject)
{
try
{
return sqlMap.queryForList(id, parameterObject);
}
catch (SQLException e)
{
System.out.println("Exceptino in queryForList -- ");
e.printStackTrace();
return null;
}
}
/**
* Executes a mapped SQL SELECT statement that returns data to populate a
* number of result objects. <p/> This overload assumes no parameter is
* needed.
*
* @param id
* The name of the statement to execute.
* @return A List of result objects.
*/
public List queryForList(String id)
{
try
{
return sqlMap.queryForList(id);
}
catch (SQLException e)
{
return null;
}
}
}
The above class SqlMapper.java is a helper class which can be used to call the sql statements in the configuration files. Here is the sample code to call the sql statement.
{
private static SqlMapper sqlMap;
private RouterSqlMapDao()
{
sqlMap = SqlMapper.getInstance();
}
public List<Routers> getRouterList(Routers routers)
{
List<Routers> routersList;
routersList = (Integer) sqlMap.queryForList(getDevice, routers);
return routersList;
}
}
In the above class, consider the method getRoutersList(..) , it takes routers bean as a parameter, Internally it calls the select sql query identified by the name “getDevice” in the Routers.xml file. The list of records returned by the select query is mapped to Routers object and a list of routers bean is returned. In this way other sql DML queries may be called using IBatis.
Ibatis is best suited for applications which has complex sql queries and huge database objects. In this case it just abstracts the user from using sql/jdbc stuff in java and deal only with objects. iBATIS is best used when you need complete control of the SQL. It is also useful when the SQL queries need to be fine-tuned. iBATIS is also inappropriate for non-relational databases, because such databases do not support transactions and other key features that iBATIS uses.