DB2 UDB, WebSphere, and iBATIS

Create Java and J2EE applications that use iBATIS with DB2 UDB V8.1 and WebSphere Studio Application Developer V5.1.2

Learn how to use iBATIS in Java™ applications and J2EE™ applications. This article covers the iBATIS syntax, accessing data sources, setting up WebSphere® Studio Application Developer Version 5.1.2 projects to support iBATIS, and querying DB2® Universal Database™ Version 8.1 (DB2) using iBATIS.


Owen Cline (owenc@us.ibm.com), Certified Consulting IT Specialist - Software Services for WebSphere, IBM

Owen Cline photoOwen Cline is a member of IBM Software Services for the WebSphere team based in San Diego, CA. He has over 20 years of software development experience. He holds four software patents, has written IBM Redbooks, and has presented at multiple technical conferences. For the past five years, Owen has specialized in J2EE architecture, application development, and deployment with a special emphasis on the WebSphere platform. In addition, he has also worked on many high-profile Web sites over the past few years.

03 February 2005

Overview of iBATIS

IBM DB2 e-kit for Database Professionals

Learn how easy it is to get trained and certified for DB2 for Linux, UNIX, and Windows with the IBM DB2 e-kit for Database Professionals. Register now, and expand your skills portfolio, or extend your DBMS vendor support to include DB2.

iBATIS is an open source object-to-relational mapping data layer that has gained popularity in the Java and J2EE worlds. iBATIS has two variants -- which are completely separate -- SQL Maps and Data Access Objects (DAO). Both of these are described below. This article focuses strictly on SQL Maps. Because there is not much documentation available on how to get started using iBATIS with WebSphere or DB2, this article should help fill the void.


The SQL Maps framework will help you to significantly reduce the amount of Java code that you normally need to access a relational database. SQL Maps map JavaBeans to SQL statements using an XML descriptor. Simplicity is the biggest advantage of SQL Maps over other frameworks and object relational mapping tools. You need only be familiar with JavaBeans, XML, and SQL to use SQL Maps. There is no complex scheme required to join tables or execute complex queries. Using SQL Maps, you have the full power of real SQL at your fingertips.

The SQL Map API allows programmers to easily map JavaBeans objects to PreparedStatement parameters and ResultSets. The philosophy behind SQL Maps is straighforward: provide a simple framework to provide 80% of JDBC functionality using only 20% of the code.

The SQL Maps framework uses XML descriptors to map JavaBeans, Map implementations, primitive wrapper types (String, Integer), and even XML documents to an SQL statement. The following is a high-level description of the lifecycle:

  1. Provide an object as a parameter (either a JavaBean, Map, or primitive wrapper). The parameter object will be used to set input values in an UPDATE statement, or WHERE clause values in a query (etc.).
  2. Execute the mapped statement. This step is where the magic happens. The SQL Maps framework will create a PreparedStatement instance, set any parameters using the provided parameter object, execute the statement, and build a result object from the ResultSet.
  3. In the case of an update, the number of rows effected is returned. In the case of a query, a single object or a collection of objects is returned. Like parameters, result objects can be a JavaBean, a Map, a primitive type wrapper, or XML.

iBATIS Data Access Objects

When developing robust Java applications, it is often a good idea to isolate the specifics of your persistence implementation behind a common API. Data Access Objects (DAOs) allow you to create simple components that provide access to your data without revealing the specifics of the implementation to the rest of your application. Using DAOs allows your application to be dynamically configured to use different persistence mechanisms. If you have a complex application with a number of different databases and persistence approaches involved, DAOs can help you create a consistent API for the rest of your application to use.

The iBATIS Data Access Objects API can be used to help hide persistence layer implementation details from the rest of your application by allowing dynamic, pluggable DAO components to be swapped in and out easily. For example, you could have two implementations of a particular DAO, one that uses the iBATIS SQL Maps framework to persist objects to the database, and another that uses the Hibernate framework. Another example would be a DAO that provides caching services for another DAO. Depending on the situation (for example, limited database performance versus limited memory), either the cache DAO could be plugged in or the standard un-cached DAO could be used. These examples show the convenience that the DAO pattern provides; however, more important is the safety that DAO provides. The DAO pattern protects your application from being tied to a particular persistence approach. In the event that your current solution becomes unsuitable (or even unavailable), you can then create new DAO implementations to support a new solution, without having to modify any code in the other layers of your application.

Using iBATIS in a Java application

There is a fully configured Java application that uses iBATIS in the source code download for this article. The project is named TestIbatis.

The first thing that you will have to do is configure iBATIS to connect to DB2 using the DB2 JDBC driver. That involves configuring two iBATIS configuration files: sql-map-config.xml and database.properties. These two configuration files will have to be placed on the Java application's classpath at runtime.

Setting up the required JAR files

Here is the .classpath file to show you the libraries required by your iBATIS-enabled Java application:

Listing 1. .classpath file

<?xml version="1.0" encoding="UTF-8"?>
    <classpathentry kind="src" path=""/>
    <classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
    <classpathentry kind="lib" path="ibatis-sqlmap-2.jar"/>
    <classpathentry kind="lib" path="ibatis-common-2.jar"/>
    <classpathentry kind="lib" path="dom.jar"/>
    <classpathentry kind="lib" path="sax.jar"/>
    <classpathentry kind="lib" path="xmlParserAPIs-2-4-0.jar"/>
    <classpathentry kind="lib" path="commons-logging.jar"/>
    <classpathentry kind="lib" path="db2java.zip"/>
    <classpathentry kind="lib" 
		path="C:/Program Files/IBM/WebSphere Studio/Application Developer/
    <classpathentry kind="lib" path="xercesImpl-2-4-0.jar"/>
    <classpathentry kind="lib" 
		path="C:/0_Good To Know/iBatis/lib/optional/logging/log4j-1.2.8.jar"/>
    <classpathentry kind="output" path=""/>


Setting up sql-map-config.xml

sql-map-config.xml is the main configuration file for iBATIS. It defines both the JDBC driver parameters and the SQL mapping files to bind the Java objects to the database schema.

Listing 2. sql-map-config.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" 


	<properties resource="database.properties"/>


  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property value="${driver}" name="JDBC.Driver"/>
      <property value="${url}" name="JDBC.ConnectionURL"/>
      <property value="${username}" name="JDBC.Username"/>
      <property value="${password}" name="JDBC.Password"/>
      <property value="15" name="Pool.MaximumActiveConnections"/>
      <property value="15" name="Pool.MaximumIdleConnections"/>
      <property value="1000" name="Pool.MaximumWait"/>
  <sqlMap resource="Employee.xml"/>


Setting up database.properties

database.properties, the configuration file, defines the JDBC driver class to use, the user name and password to use, and the JDBC URL to use. Notice that we are connecting to the DB2 SAMPLE database.

Listing 3. sql-map-config.xml

# Database Connectivity Properties



Setting up Employee.xml

For every Java object that will be persisted using iBATIS, you need to configure an XML mapping file. I chose to map the EMPLOYEE table in the DB2 SAMPLE database to a Java object called Employee.

Listing 4. Employee.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" 

<sqlMap namespace="Employee">

	<resultMap id="getEmployeesResult" class="com.orc.ibatis.Employee">
		<result property="empno" column="EMPNO"/>
		<result property="firstname" column="FIRSTNME"/>
		<result property="lastname" column="LASTNAME"/>

	<select id="getEmployee" resultClass="com.orc.ibatis.Employee">
			EMPNO as empno,
			FIRSTNME as firstname,
			LASTNAME as lastname
		WHERE EMPNO = #empno#

	<select id="getEmployees" resultMap="getEmployeesResult">



Setting up Employee.java

Be aware that iBATIS expects the Java class to conform to the typical Java Bean pattern (attributes with setters and getters).

Listing 5. Employee.java

 * Created on Nov 30, 2004
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
package com.orc.ibatis;

 * @author Owen Cline
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
public class Employee {
	private String empno;
	private String firstname;
	private String lastname;
	public void setEmpno(String empNo)
		this.empno = empNo;
	public void setFirstname(String firstName)
		this.firstname = firstName;
	public void setLastname(String lastName)
		this.lastname = lastName;

	public String getEmpno()
		return this.empno;
	public String getFirstname()
		return this.firstname;
	public String getLastname()
		return this.lastname;


Setting up log4j.properties

iBATIS supports an extensive logging facility based on log4j. Below is how you can configure your Java application to log to a file called testibatis.log.

Listing 6. log4j.properties


# SqlMap logging configuration...

log4j.appender.CON.layout.ConversionPattern=%d %p %C - %m%n

log4j.appender.FILE.layout.ConversionPattern=%d %p %t %c %C - %m%n

log4j.appender.NTLOG.layout.ConversionPattern=%d %p - %m%n

log4j.rootLogger=DEBUG, CON, FILE, NTLOG


Using iBATIS in a J2EE application

iBATIS supports using a JNDI data source and it can map to WebSphere's JTA service. To demonstrate this, we will create a stateless session bean to use iBATIS SQL Maps to access the DB2 SAMPLE database.

We will use the same Employee.java and Employee.xml files from the Java application example. The database.properties file is no longer required since we are using a JNDI datasource. The sql-map-config.xml file will change to use a JNDI data source and WebSphere's JTA service.

Therefore, you should set up a JAAS authentication entry, a JNDI data source, and add the location of the iBATIS configuration files to JVM classpath in your Test Server on which you will run the EJB project.

Setting up JAAS Authentication entry

In the Server Perspective of Application Developer, edit the TestServer configuration and navigate to the Security tab. Add a JAAS Authentication entry to be used for the data source you will next create.

Figure 1. Adding a JAAS Authentication entry

Setting up a JNDI Data source

You will add a DB2 data source now and use the JAAS Authentication entry you just created. In the Server Perspective of Application Developer, edit the TestServer configuration and navigate to the Data source tab.

Figure 2. Adding a JNDI Data source entry

Setting up JVM Classpath

When the EJB Project runs, it will have to locate the sql-map-config.xml and Employee.xml files. You should place them in a directory and then add that directory to your JVM classpath. In the Server Perspective of Application Developer, edit the TestServer configuration and navigate to the Environment tab.

Figure 3. Modify the JVM classpath

Setting up Resource Reference to data source

Now, you have to add a Resource Reference to the Session Bean in the EJB Project to map to the data source. In Application Developer, go to the J2EE Perspective and edit the EJB Deployment Descriptor of the project containing Session Bean. Then go to the References tab and add a Resource Reference to the Session Bean.

Figure 4. Resource Reference to data source in EJB Deployment Descriptor
Listing 7. ejb-jar.xml with Resource Reference added

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0
//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">
<ejb-jar id="ejb-jar_ID">
		<session id="IbatisSession">
			<resource-ref id="ResourceRef_1101949289722">


Setting up sql-map-config.xml

This is how you map iBATIS to a DB2 JNDI data source and map to WebSphere's JTA service:

Listing 8. sql-map-config.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" 


	<settings cacheModelsEnabled="true" lazyLoadingEnabled="false" 
	enhancementEnabled="true" maxSessions="64" maxTransactions="8" 
	maxRequests="128" />

	<transactionManager type="JDBC">
		<property name="UserTransaction" 
			value="java:comp/env/UserTransaction" />
		<dataSource type="JNDI">
			<property name="DataSource" 
				value="java:comp/env/SampleDB" />
	<sqlMap resource="Employee.xml" />




iBATIS is an easy framework to provide a persistence layer to either Java or J2EE applications. Using this article, you should be able to integrate iBATIS to DB2 UDB and WebSphere's JTA service.


source code referenced in articlesourcecode.zip  ( HTTP | FTP )5,136 KB



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management, WebSphere, IBM i
ArticleTitle=DB2 UDB, WebSphere, and iBATIS