From the IBM WebSphere Developer Technical Journal.

Introduction

Object relational mappers (ORM) comes in different flavors. In Java™, most of the popularity goes to those that implement full domain model mapping, the goal of which is to map a whole layer of objects and behaviors to database tables. Popular ORMs include:

• Hibernate
• JDO
• EJB Entities 3
• EJB Entity Beans 2.x
• TopLink

Each of the above is classified as a full domain model mapper, where tables are mapped to objects, object state is maintained, objects follow a connected model (as client components interact with the object, underlying database operations are implied) either all or some of the time, and an abstract query language works against the object model. In turn, these frameworks generate JDBC or SQL code under the covers.

There are some cases, though, when you may decide to use straight JDBC instead. Some reasons might be:

• Developer's knowledge and comfort of SQL. Object query languages still do not relieve you from having to know SQL, since you often need to know how to fine tune query languages.
• Object relational mappers are heavyweight for certain types of applications. For example, batch applications that must execute many update operations serially are often better off executing SQL statement in sequence without all the extra object hydration.
• Stored procedures are needed or already exist. There are many valid reasons for using stored procedures. In some scenarios, they reduce the amount of network IO because SQL statements can be executed in sequence at the database.
• The DBA is king. Many development organizations have strict rules about SQL and who may define it. Sometimes only database administrators are authorized to create and fine tune SQL for performance.
• Former environment. For example, applications are being migrated from a platform where SQL queries are already fined tuned and tested.

The general solution in these cases seems to be: use JDBC. Developers frequently require guidance when building JDBC code; bad JDBC code often results in having scattered data access code all over the place. To achieve what they need to, developers often end up developing some kind of custom JDBC framework or wrapper. This can occur when, for example:

1. Applications are running in a J2EE™ platform. Java objects still need to be passed as Data Transfer Objects from the business logic tier to the view tier, so there needs to be some code that moves Result Set Data to Data Transfer Objects and from Data Transfer Objects to SQL updates, inserts, or deletes.
2. Applications still want a layer of abstractions. Just because one uses JDBC, it does not absolve you from having to layer code correctly.
3. Applications want to externalize SQL from their Java code for fine tuning.
4. Redundancy is inevitable. When writing JDBC, developers often find themselves writing the same mundane code over and over again such as acquiring connections, preparing statements, looping through results sets, and various other JDBC specific elements.

Good news

There is an object relational mapper out there that is a hidden gem. Instead of creating a full domain model, its job is to map Java objects directly to SQL statements. This framework is called iBatis, and its goal is to implement 80% of the JDBC boilerplate code you would otherwise have to do yourself. In addition, it provides a simple mapping and API layer that lets developers quickly develop data access code.

Back to top

What is iBatis?

iBatis is an open source object relational mapper whose job is to map objects to SQL statements. Using a simple concept called SQL maps, the goal is to map a Java objects (like the one shown below) to SQL statements:

public class Customer implements Serializable {
	
	private String name;
	private int customerId;
	private CustomerOrder customerOrder;

	
	public Customer()
	{
	    System.out.println("Creating CustomerBO...");
	}
	/**
	 * @return Returns the customerId.
	 */
	public int getCustomerId() {
		return customerId;
	}
	/**
	 * @param customerId The customerId to set.
	 */
	public void setCustomerId(int customerId) {
		this.customerId = customerId;
	}
	/**
	 * @return Returns the name.
	 */
	public String getName() {
		return name;
	}
	/**
	 * @param name The name to set.
	 */
	public void setName(String name) {
		this.name = name;
	}
	
      
    /**
     * @return Returns the customerOrder.
     */
    public CustomerOrder getCustomerOrder()
    {
        return customerOrder;
    }
    /**
     * @param customerOrder The customerOrder to set.
     */
    public void setCustomerOrder(CustomerOrder customerOrder)
    {
        this.customerOrder = customerOrder;
    }
}

You would define this mapping inside a file called an SQLMap. Here's an example:

<sqlMap namespace="Customer">

	<parameterMap id="updateCustomerOrderParamMap"
		class="com.ibm.ibatis.sample.bo.CustomerOrder">
		<parameter property="orderId" />
		<parameter property="customerId" />
	</parameterMap>
	    
	<resultMap 
	     class="com.ibm.persistence.ibatis.bo.Customer"
	     id="customerOrderMap">
	     <result property="customerId" 
	        column="CUST_ID"
	        nullValue="0" />
	     <result property="customerOrder.orderId" 
	         column="OPEN_ORDER_ID"
   	         nullValue="0" />
	    <result property="customerOrder.customerId" 
	      column="CUST_ID"
	      nullValue="0" />
	 </resultMap>


	<statement id="checkCustomer"
	     parameterClass="java.lang.Integer" 
	     resultMap="customerOrderMap">
	     SELECT CUST_ID, OPEN_ORDER_ID FROM CUSTOMER_USER.CUSTOMER WHERE CUST_ID = #customerId# FOR UPDATE
	</statement>

	<statement id="getOrderId"
	     parameterClass="java.lang.Integer" 
	     resultClass="java.lang.Integer">
	     SELECT ORDER_ID FROM CUSTOMER_USER.ORDER WHERE CUSTOMER_ID = #customerId# AND STATUS='OPEN'
	</statement>

	<statement id="insertOrder"
	     parameterClass="java.lang.Integer">
	     INSERT INTO CUSTOMER_USER.ORDER (STATUS, TOTAL, CUSTOMER_ID)VALUES('OPEN',0,#customerId#)
	</statement>


	<statement id="updateCustomerOrderId"
	     parameterMap="updateCustomerOrderParamMap">
	     UPDATE CUSTOMER_USER.CUSTOMER SET OPEN_ORDER_ID = ? WHERE CUST_ID = ?
	</statement>

</sqlMap>

iBatis defines parameter maps as inputs to statements and result maps for mapping SQL ResultSets. These maps are assigned to statements which can then be executed. Applications can interact with the iBatis API to perform SQL operations as shown below:

//Access SQL client Maop
SqlMapClient sqlMapClient = OrderEntryConfig.getSqlMapInstance();
      
      //1.  Retrieve total using SUM() function in SQL
      Integer total = (Integer)sqlMapClient.queryForObject("getOrderTotal",new Integer
      (customerOrder.getOrderId()));
      //2.  Set total in CustomerOrder Object
      customerOrder.setTotal(total);
      //3. Update Total column in DB
      sqlMapClient.update("updateOrderTotal",customerOrder);

As you can see, executing SQL statements has never been easier. iBatis supports most of what any JDBC application wants:

• JDBC transaction demarcation, which includes support for delegating to JDBC transactional API, container managed transactions in an EJB container, or user transaction objects in JTA transactions.
• Overrides default mapping of data types, and the ability to create custom type mappers.
• Mapping of complex joins to complex object graphs, including various Java collection types.
• The ability to load data into HashMaps where column names are not known at query time.
• Lazy loading of object graphs.
• JDBC batch statements.
• Mapping to stored procedures.
• Dynamic SQL mappings, where SQL statements can be constructed based on the state of a JavaBean.
• Caching of results and the ability to delegate to other caching mechanisms, such as IBM WebSphere® Application Server dynacache.
• An additional DAO framework; iBatis comes with a full implementation of the DAO pattern that can be used optionally in addition to the data mapper.

iBatis supports many other features as well, although it does not try to compete with full fledged ORMs, which have abstract query languages, map OO constructs (such as inheritance) to complex table relationships, or have fully managed object state. iBatis simply enables you to move data directly from SQL to plain and simple disconnected objects.

My intention here is simply to make developers aware of iBatis as a powerful and feature rich ORM for people who like to write their own SQL. Detailed reference information is available below.

In cases where you decide that a full ORM is not needed and you lean toward using JDBC, consider using iBatis instead. You will get nearly all the benefits of JDBC without the excessive coding, plus have a layered architecture and the ability to reuse your fine-tuned SQL queries.

Resources

• iBatis project site
  
  
• IBatis downloads and documentation
  
  
• DB2 UDB, WebSphere, and iBATIS.
  
  
• IBM WebSphere: Deployment and Advanced Configuration
  
  
• Patterns of Enterprise Architecture
  
  

About the author

Roland Barcia is a Consulting IT Specialist for IBM Software Services for WebSphere. He is a co-author of IBM WebSphere: Deployment and Advanced Configuration. For more information about Roland, see his blog.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• What is iBatis?
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into WebSphere on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers