DB2 UDB, WebSphere, and iBATIS

Document options Document options requiring JavaScript are not displayed Sample code Rate this page Help us improve this content

Level: Intermediate

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

03 Feb 2005

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.

Overview of iBATIS

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.

iBATIS SQL Maps

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

iBATIS iBATIS Web site

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.

Back to top

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:

<![CDATA[

<?xml version="1.0" encoding="UTF-8"?>
<classpath>
    <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/
    v5.1/runtimes/base_v51/lib/j2ee.jar"/>
    <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=""/>
</classpath>


]]>

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.

<![CDATA[

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" 
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

	<properties resource="database.properties"/>

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

  <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"/>
    </dataSource>
  </transactionManager>
  
  <sqlMap resource="Employee.xml"/>
  
</sqlMapConfig>


]]>

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.

<![CDATA[

####################################
# Database Connectivity Properties
####################################

driver=COM.ibm.db2.jdbc.app.DB2Driver
url=jdbc:db2:SAMPLE
username=admin
password=big1bird


]]>

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.

<![CDATA[

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" 
"http://www.ibatis.com/dtd/sql-map-2.dtd">

<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"/>
	</resultMap>

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

	<select id="getEmployees" resultMap="getEmployeesResult">
		SELECT * FROM EMPLOYEE
	</select>

</sqlMap>

]]>

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

<![CDATA[

/*
 * 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.

<![CDATA[

#log4j.debug=true

# SqlMap logging configuration...
#log4j.logger.com.ibatis=DEBUG
#log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
#log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
#log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
#log4j.logger.java.sql.Connection=DEBUG
#log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
#log4j.logger.java.sql.ResultSet=DEBUG

log4j.appender.CON=org.apache.log4j.ConsoleAppender
log4j.appender.CON.Threshold=INFO
log4j.appender.CON.layout=org.apache.log4j.PatternLayout
log4j.appender.CON.layout.ConversionPattern=%d %p %C - %m%n

logFile=c:/temp/testibatis.log
log4j.appender.FILE=org.apache.log4j.DailyRollingFileAppender
log4j.appender.FILE.File=${logFile}
log4j.appender.FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.FILE.layout.ConversionPattern=%d %p %t %c %C - %m%n

log4j.appender.NTLOG=org.apache.log4j.nt.NTEventLogAppender
log4j.appender.NTLOG.Threshold=FATAL
log4j.appender.NTLOG.layout=org.apache.log4j.PatternLayout
log4j.appender.NTLOG.layout.ConversionPattern=%d %p - %m%n

log4j.rootLogger=DEBUG, CON, FILE, NTLOG

]]>

Back to top

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.

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.

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.

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.

<![CDATA[

<?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">
	<display-name>IbatisEJB</display-name>
	<enterprise-beans>
		<session id="IbatisSession">
			<ejb-name>IbatisSession</ejb-name>
			<local-home>com.orc.ibatis.IbatisSessionLocalHome</local-home>
			<local>com.orc.ibatis.IbatisSessionLocal</local>
			<ejb-class>com.orc.ibatis.IbatisSessionBean</ejb-class>
			<session-type>Stateless</session-type>
			<transaction-type>Container</transaction-type>
			<resource-ref id="ResourceRef_1101949289722">
				<description></description>
				<res-ref-name>SampleDB</res-ref-name>
				<res-type>javax.sql.DataSource</res-type>
				<res-auth>Container</res-auth>
				<res-sharing-scope>Shareable</res-sharing-scope>
			</resource-ref>
		</session>
	</enterprise-beans>
</ejb-jar>

]]>

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:

<![CDATA[

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" 
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

	<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" />
		</dataSource>
	</transactionManager>
	<sqlMap resource="Employee.xml" />

</sqlMapConfig>

]]>

Back to top

Summary

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.

Back to top

Download

Description	Name	Size	Download method
source code referenced in article	sourcecode.zip	5,136 KB	FTP|HTTP

	Information about download methods

Resources

• Download the source code used in this article.
  
  
• iBATIS Web site
  

About the author

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

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top