This chapter provides an overview of Apache iBATIS. It covers how iBATIS implements the object-relational mapping techniques described in the previous chapters. We will briefly survey its history, discuss how it is used, and show several end-to-end examples of its use.
iBATIS is a popular framework for object-relational mapping that makes SQL, stored procedures, and legacy database schemas first-class citizens. The iBATIS philosophy is that SQL is mature, well-known, and tested in industry; thus, SQL and stored procedures are the best mechanism to query for an object with the iBATIS framework. This philosophy is realized in iBATIS with facilities for the direct manipulation of the SQL code. XML-based mapping statements are used to map the arguments and/or results of an SQL statement. Externalizing queries to XML Files makes it easy to be reviewed easily by database administrators.
iBATIS is a table gateway framework as described in “Patterns of Enterprise Architecture” [Fowler]. An object often represents an entire database table and is associated with inserts, updates, and queries for that type. Although iBATIS can support complex object-relational mapping concepts, such as the java.util.Map type and mapping objects to database joins through the use of advanced SQL, it lacks full object-relational mapping features to make it a Full Data Mapper. Such features include an object-oriented query language and implicit object persistence.
What is now iBATIS started as a small body of code that was used primarily as a shortcut to hand-coding JDBC for its author—Clinton Begin [Begin]. In 2002, Microsoft published a paper claiming that a .Net implementation of Sun’s Java PetStore application [MSDN] has orders of magnitude in performance improvement. Begin set out to prove this claim wrong by developing JPetStore—an agile Java EE variant complying with the same requirements as a traditional Java EE three-tier architecture (rather than the two-tier architecture allowing ASPs to directly invoke the database introduced by the .Net implementation).
Begin’s effort showed that a tiny persistence framework could be used for enterprise quality database access instead of entity EJBs. This library caught the attention of the open-source community as a viable approach to object-relational mapping, resulting in its own open-source project called the iBATIS Database Layer.
iBATIS is a simple framework with few dependencies. As shown in Figure 6.1, POJOs can be mapped to input parameters of an SQL statement or to the results of an SQL Statement executing a query. The statements are executed via JDBC. The SQL statements are externalized to XML files and become callable “mapped statements” in your Java code.
The iBATIS APIs are not part of any standard or specification. However, iBATIS is designed to be used with two significant standards—JDBC and Java EE. iBATIS utilizes JDBC for underlying database connectivity that is portable. iBATIS also reduces the complexity of using JDBC directly; for instance, it automatically releases JDBC resources for the user. iBATIS is also designed to be used within a Java EE web application, allowing it to use application data sources and transaction management through the Java Transaction API (JTA).
iBATIS requires J2SE 1.3 or higher, as well as JDBC 2.0 or higher. Nearly all providers of a Java runtime environment (JRE) provide JDBC with their Java distribution. The iBATIS library itself is delivered as a single JAR file (as of version 2.3) with the naming convention of ibatis-version.build.jar—for example, ibatis-2.30.667.jar.
Beyond JDBC 2.0, iBATIS has no dependencies; however, depending on a project’s requirements, other libraries may prove useful.
Table 6.1 illustrates some optional dependencies. For example, if you are in an environment where multiple JDBC drivers are used, the Jakarta Commons Database Connection Pool (DBCP) wrapper library can allow for unified pooling.
Table 6.1. iBATIS Dependencies
Description | When to Use | Dependency |
|---|---|---|
Legacy JDK Support | If you’re running less than JDK 1.4 and if your app server also doesn’t already supply these JARs. | JDBC 2.0 Extensions |
JTA 1.0.1a | ||
Xerces 2.4.0 | ||
iBATIS Backward Compatibility | If you’re using the old iBATIS (1.x) DAO framework or the old Data Mapper (1.x). | iBATIS DAO 1.3.1 |
Runtime Bytecode Enhancement | If you want to enable CGLIB 2.0 bytecode enhancement to improve lazy loading and reflection performance. | CGLIB 2.0 |
DataSource Implementation | If you want to use the Jakarta DBCP connection pool. | DBCP 1.1 |
Distributed Caching | If you want to use OSCache for centralized or distributed cache support. | OSCache 2.0.1 |
Logging Solution | If you want to utilize iBATIS debug logs. | Log4J 1.2.8 |
Jakarta Commons Logging |
If your application spans multiple Java Virtual Machines (JVMs), distributed caching can significantly improve performance. iBATIS supports distributed caching with OpenSymphony cache (OSCache), which is also an open-source offering. The Caching section in this chapter discusses caching in detail, including how to plug in any distributed caching service.
A final possible dependency is bytecode enhancement for lazy loading. Bytecode enhancement allows iBATIS to modify application bytecode at runtime. This allows iBATIS to optimize queries—especially large queries—to only the subset of results that are actually used by the end user. iBATIS supports CGLIB 2.0 for bytecode enhancement. Although it is completely optional, it improves the iBATIS implementation of lazy loading and reflection. Utilizing CGLIB is highly recommended if performance is a key requirement for your application.
In October 2004, iBATIS was voted into Apache and gained Apache incubator status. In May 2005, the project completed its incubation status and became an official Apache open-source project. iBATIS is distributed under the Apache 2.0 license. The latest source code, binaries, and documentation are always available at the iBATIS main site [iBATIS].
iBATIS is designed to be a simple and intuitive framework; however, even the most elegant library should be well documented. The sources in Table 6.2 provide comprehensive documentation about how to use iBATIS in practice.
Table 6.2. Available Literature
Source | Description | |
|---|---|---|
iBATIS IN ACTION [Begin] | Manning Publications Co. | Excellent comprehensive resource written by the founder of iBATIS |
Developer’s Guide [iBATIS 1] | Comprehensive documentation available in PDF form | |
iBATIS Tutorial [iBATIS 2] | ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2-Tutorial_en.pdf | An excellent introductory tutorial for getting started |
Wiki [iBATIS 3] | Excellent for platform-specific notes and information on new undocumented features | |
Improve persistence with Apache Derby and iBATIS: Part 1 |  A.6.1 A developerWorks tutorial on using iBATIS with the Derby Database | |
Improve persistence with Apache Derby and iBATIS: Part 2 | A.6.2 Part 2 of an article series that shows the JPetStore application written in iBATIS | |
Improve persistence with Apache Derby and iBATIS: Part 3 | A.6.3 Part 3 of an article series that shows some more advanced iBATIS features | |
DB2 UDB, WebSphere, and iBATIS | www.ibm.com/developerworks/db2/library/techarticle/dm-0502cline/ | A.6.4 Article describing using iBATIS with DB2 and WebSphere Application Server |
Tired of hand coding JDBC? Use iBatis as a data mapping framework instead. | www.ibm.com/developerworks/websphere/techjournal/0510_col_barcia/0510_col_barcia.html | A.6.5 A commentary on iBATIS giving a brief overview |
The programming model for iBATIS is a combination of (a) SQL specified in XML files called SQLMaps and (b) calls to instances of special classes, such as SQLMapClient, that invoke the query logic in the XML. iBATIS is designed to be simple to use for the 80% of applications that do not require complex object-relational mapping techniques.
The configuration of the iBATIS framework is centered around an XML property file called SqlMapConfig.xml. Listing 6.1 shows a basic example.
Example 6.1. Example SqlMap Configuration File
<sqlMapConfig>
<settings cacheModelsEnabled="true" enhancementEnabled="false"
maxSessions="64" maxTransactions="8" maxRequests="128"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property value="${driver}"
name="org.apache.derby.jdbc.EmbeddedDriver"/>
<property value="${url}" name="jdbc:derby:/opt/pwte/EXAMPLE"/>
<property value="15" name="Pool.MaximumActiveConnections"/>
<property value="15" name="Pool.MaximumIdleConnections"/>
<property value="1000" name="Pool.MaximumWait"/>
</dataSource>
</transactionManager>
<sqlMap resource="org/pwte/ibatis/Customer.xml"/>
<sqlMap resource="org/pwte/ibatis/Order.xml"/>
<sqlMap resource="org/pwte/ibatis/Product.xml"/>
<sqlMap resource="org/pwte/ibatis/LineItem.xml"/>
</sqlMapConfig>Setting up the framework in Java before executing a database operation is also quite straightforward. You use an SqlMapClientBuilder to create an instance of an SqlMapClient by loading the SqlMap configuration file. For example, the application code to invoke the query associated with the file shown in Listing 6.1 with a Customer with an ID of 100 is shown in Listing 6.2.
Example 6.2. iBATIS Initialization in Java
SqlMapClient dm = null;
Reader reader = null;
try {
reader = Resources.getResourceAsReader("SqlMapConfig.xml");
dm = SqlMapClientBuilder.buildSqlMapClient(reader);
AbstractCustomer customer =
(AbstractCustomer) dm.queryForObject("customer.query", 100);
// perform remainder of logic...
}
catch (IOException ioe) {
// perform appropriate error handling
}Listing 6.1 illustrates a configuration example for a simple Derby JDBC driver. In practice, JDBC DataSources managed by Java EE application servers are often used. This is fully supported by iBATIS. For instance, Listing 6.3 shows the configuration for a DataSource with the JNDI name of jdbc/Derby/DataSource.
Example 6.3. JNDI DataSource Configuration
<sqlMapConfig>
<settings cacheModelsEnabled="true" enhancementEnabled="false"
maxSessions="64" maxTransactions="8" maxRequests="128"/>
<transactionManager type="JTA">
<property name="UserTransaction"
value="java:comp/env/UserTransaction" />
<dataSource type="JNDI">
<property name="DataSource"
value="java:comp/env/jdbc/Derby/DataSource" />
</dataSource>
</transactionManager>
...
</sqlMapConfig>iBATIS uses a transparent connection model. Connections to the database are allocated and managed for you by the SqlMapClient in an effort to promote simplicity. In fact, iBATIS does not have an API to explicitly create a database connection. However, iBATIS does allow you to access the JDBC Connection object associated with the SqlMap in case you need to configure some advanced settings.
All iBATIS statements run inside a transaction. iBATIS supports four types of transactions: local, global, custom, and external. Local transactions use the standard JDBC API for transaction support (Listing 6.1 serves as an example of setting up local transactions). iBATIS can be configured to use other transaction managers, such as a JTA transaction manager for an application server (see Listing 6.3 for an example of configuring transactions with JTA). Another option for Application Servers is to set the transaction type to be External. In this case, iBATIS will assume some other API is handling transactions. This is the preferred option when using iBATIS in an EJB Container with Container Managed Transactions. Finally, iBATIS supports a standard interface to plug in your own custom transaction manager or a third-party transaction manager that is not supported by the SqlMap config.
By default, iBATIS automatically groups each statement into a transaction for you. However, it also supports user transactions—that is, transactions that are explicitly demarcated by the user through methods on the SqlMapClient. Listing 6.4 illustrates a user transaction that groups two updates for a BusinessCustomer. First, the Customer is updated to be a nonpartner; then, its openOrder is updated with a status of SUBMITTED. If a runtime exception occurs during either of these operations, the transaction is automatically rolled back. You can also explicitly call the rollback method on the SqlMapClient. When a programmer explicitly calls any transactional operations, iBATIS delegates the call to the underlying API. In the case of JDBC, it will toggle the autoCommit option from true to false and back as appropriate and delegate to the transaction on the underlying Connection. For the JTA option, it will call the corresponding demarcation methods on the UserTransactionOption. If you use the External option, calling those operations will have no effect.
Example 6.4. User Transaction Example
try {
dm.startTransaction();
AbstractCustomer customer = new BusinessCustomer();
customer.setCustomerId(100);
customer.setPartner(false);
dm.update(
"customer.business.update.partner", (AbstractCustomer) customer);
Map orderUpdateInput = new HashMap();
map.put("customerId",100);
map.put("orderStatus","SUBMITTED");
dm.update("customer.update.order.status", orderUpdateInput);
//Other logic that may end badly
dm.commitTransaction();
}
catch (SomeApplicationExceptionNeedingRollback e) {
dm.rollbackTransaction();
}
finally {
dm.endTransaction();
}iBATIS also fully supports standard JDBC transaction isolation levels. iBATIS will use the default for the JDBC driver or the isolation level configured by an application server’s datasource. To explicitly set an isolation level before starting a transaction, you can use code like that shown in Listing 6.5.
See your JDBC driver for the isolation levels associated with the Connection. Some of these are discussed in Chapter 3, “Designing Persistent Object Services” and Chapter 5, “JDBC.”
Any database operation accessed through iBATIS requires both an XML SqlMap statement and the Java code to invoke the statement. As an example, let’s create a ResidentialCustomer. Listing 6.6 illustrates the XML required.
Example 6.6. ResidentialCustomer Create SqlMap XML
<parameterMap id="customer.residential.map"
class="org.pwte.example.domain.ResidentialCustomer" >
<parameter property="name" />
<parameter property="householdSize" />
<parameter property="frequentCustomer"
typeHandler="org.pwte.ibatis.YesNoBoolTypeHandler" />
</parameterMap>
<insert id="customer.residential.insert"
parameterMap="customer.residential.map">
insert into CUSTOMER ( NAME, RESIDENTIAL_HOUSEHOLD_SIZE,
RESIDENTIAL_FREQUENT_CUSTOMER ) values ( ?, ?, ? )
</insert>Key Point
Notice that CUSTOMER_ID was not populated. This example assumes that key generation has been put in place such that a primary key is automatically generated. See the “Keys” section, later in this chapter, for a full discussion on key generation.
The insert statement contains conventional SQL. The parameterMap statement specifies which bean properties from the ResidentialCustomer JavaBean correspond to the statement’s parameters. Notice that the frequentCustomer property has a customer type handler. This is necessary because our database schema stores booleans as Y or N characters, and a type handler is needed to convert to and from Java strings. Type handlers are covered in detail in the section “Extending the Framework.”
The corresponding Java code to call the insert statement is shown in Listing 6.7.
Example 6.7. Invoking the ResidentialCustomer Create in Java Code
ResidentialCustomer customer = new ResidentialCustomer();
customer.setName("Peter Boyce");
customer.setHouseholdSize((short)6);
customer.setFrequentCustomer(true);
dm.insert("customer.residential.insert", customer);Notice how the first argument to the iBATIS insert API is the name of the SqlMap. Now consider another example in which we create an Order for this Customer and set the ResidentialCustomer’s openOrder attribute. Listing 6.8 illustrates the SqlMap XML needed.
Example 6.8. Order Create SqlMap XML
<parameterMap id="order.open.map" class="org.pwte.example.domain.Order">
<parameter property="customer.customerId"/>
<parameter property="status"
typeHandler="org.pwte.ibatis.StatusEnumTypeHandler"/>
<parameter property="total"/>
</parameterMap>
<insert id="order.open" parameterMap="order.open.map">
insert into ORDER_ (CUSTOMER_ID, STATUS, TOTAL) values (?, ?, ?)
</insert>
<update id="customer.open.order" parameterClass="int">
update CUSTOMER set OPEN_ORDER =
(select ORDER_ID from ORDER_ where CUSTOMER_ID = #value#)
where CUSTOMER_ID = #value#
</update>Notice how the parameter map to create a new order also requires a type handler for the status attribute. This is because the status attribute is an enumeration and iBATIS requires some extra information to determine how to map the enumeration to the database. Also notice that the customer.open.order statement takes in an integer primitive type rather than a JavaBean class and does not require a parameter map. In this case, iBATIS knows how to map the parameters to the database—it knows that the int passed in for CUSTOMER_ID should be converted to a JDBC integer type. Thus, because there is no special mapping such as the enumeration in the previous statement, #argument# syntax can be used instead of a parameter map.
Listing 6.9 shows the Java code to create a new Order for the ResidentialCustomer.
This section illustrates how read-only queries are performed with iBATIS. Listing 6.10 details the SqlMap statement for a simple retrieval operation of selecting a single customer by customer ID.
Example 6.10. Customer Retrieve SqlMap XML
<select id="customer.select"
resultMap="customer.select.map"
parameterClass="int">
select CUSTOMER_ID, NAME, OPEN_ORDER from CUSTOMER
where CUSTOMER_ID = #value#
</select>
<resultMap id="customer.select.map" class="business">
<result property="customerId"/>
<result property="name"/>
<result property="openOrder" select="order.select"
column="OPEN_ORDER"/>
</resultMap>Notice how the ResultMap for the customer.select statement has a nested query. The nested order.select query populates the customer’s openOrder JavaBean. Listing 6.11 shows the corresponding Java code to retrieve a customer and the resultMap section. Because openOrder points to another select statement, iBATIS will issue both queries (the select to the Customer table and another to the Order table) in response to one API call to the queryForObject method to the customer.select statement.
In Listing 6.11, iBATIS issues two queries; however, suppose you want to populate the Order and Customer based on the result of a single SQL Join between the CUSTOMER and ORDER tables. Listing 6.12 shows the SqlMap for such a query and Listing 6.13 shows the Java code.
Example 6.12. Active Customer Retrieve SqlMap XML
<resultMap id="customer.select.map" class="business">
<result property="customerId"/>
<result property="name"/>
<result property="openOrder"/>
<result="order.select.map" column="OPEN_ORDER"/>
</resultMap>
<resultMap id="order.select.map" class="order">
<result property="orderId"/>
<result property="total"/>
<result property="status"
typeHandler="org.pwte.ibatis.StatusEnumTypeHandler"/>
<result property="lineitems" select="lineitem.select"
column="ORDER_ID"/>
</resultMap>
<select id="customer.order.select"
resultMap="customer.select.map"
parameterClass="customer">
select CUSTOMER_ID, NAME, OPEN_ORDER
from CUSTOMER c
LEFT OUTER JOIN ORDER o
on c.OPEN_ORDER_ID = ORDER_ID
where CUSTOMER_ID = #customerId#
</select>If a query returns multiple customer rows, you can use the queryForList() method from iBATIS to get a List of Java Objects returned, as shown in Listing 6.13.
Updating a datasource with iBATIS is very similar to querying data as shown earlier. Consider the use case of changing the quantity of a LineItem. Listing 6.14 and Listing 6.15 illustrate how to change the quantity of a LineItem to 100. In the Update example, we show iBATIS’s capability to use “inline” input parameters (named parameters embedded directly in the query). iBATIS also supports the notion of using a parameter map in the XML config file too; we illustrate an example of this approach in Listing 6.22, later in the chapter.
Notice that the object passed in as an argument to the lineitem.update statement is a Map that is not defined in our domain model (a JavaBean as the key class could have been used just as well). The reason this is necessary is the impedance mismatch between the domain model and the database schema. In the domain model, the LineItem type does not have an orderId attribute, but in the database the primary key for the LINE_ITEM table uses both ORDER_ID and PRODUCT_ID. Thus, a custom key class must be used as the argument to our update and insert statements for LineItem.
As a final update example, suppose a Customer no longer has an openOrder, and thus its open order reference needs to be removed. In terms of our database schema, this means the OPEN_ORDER reference for that CUSTOMER needs to be set to NULL. Listing 6.16 and Listing 6.17 showcase how to implement and invoke such an update, respectively.
As with the other persistence operations, deletion semantics are deferred to standard SQL syntax. iBATIS assumes no responsibility for cascade deletion. It is assumed that cascade delete behavior is configured at the database level.
As an example of a delete statement, consider the case in which a LineItem needs to be deleted. Listing 6.18 and Listing 6.19 illustrate this use case, again with the configuration followed by the Java code to invoke it.
Notice the striking similarity to the update LineItem use case. Again, a Map object wrapper needs to be used because of the orderId domain modeling mismatch. The only significant difference is the change in the SQL statement.
Now suppose you need to delete an Order and all its associated LineItems, but because of database schema restrictions, you cannot configure the database to perform a cascade delete. This use case actually encompasses two iBATIS delete statements that manually perform the equivalent of a cascade delete as shown in Listing 6.20 and Listing 6.21.
Invoking stored procedures with iBATIS is very similar to other database operations. The only difference is that the SQL is replaced with the name of the stored procedure with its arguments. For instance, consider a stored procedure that swaps the Orders of two Customers. Listing 6.22 and Listing 6.23 illustrate how such a procedure can be invoked.
Example 6.22. Swap Order Stored Procedure SqlMap XML
<parameterMap id="swapParameters" class="map" >
<parameter property="first" jdbcType="VARCHAR"
javaType="java.lang.String" mode="INOUT"/>
<parameter property="second" jdbcType="VARCHAR"
javaType="java.lang.String" mode="INOUT"/>
</parameterMap>
<procedure id="swap.order" parameterMap="swapParameters" >
{call swap_customer_order (?, ?)}
</procedure>Notice that the update SqlMapClient API is used because the stored procedure does not return anything. If it did, queryForObject, queryForList, and the like could be used along with a corresponding result map.
iBATIS fully supports batch processing through methods on the SqlQueryMap. The startBatch() method causes batch mode to be entered, and the executeBatch() method causes the batch to be executed, returning the number of rows that were updated as part of the batch.
Listing 6.24 illustrates using batch statement to group two updates for a BusinessCustomer. First, the customer is updated to be a nonpartner; then, its OpenOrder is updated with a status of "SUBMITTED". The executeBatch() method call should return a value of 2, representing the number of updated database rows.
Example 6.24. Batch Statement Example
dm.startBatch(); AbstractCustomer customer = new BusinessParnter(); customer.setCustomerId(100); customer.setPartner(false); dm.update("customer.business.update.partner", (AbstractCustomer) customer); Map orderUpdateInput = new HashMap(); map.put("customerId",100); map.put("orderStatus","SUBMITTED"); dm.update("customer.update.order.status", orderUpdateInput); int rowsUpdated = dm.executeBatch();
iBATIS has two special extension points designed for custom type conversion and row processing: type handlers and row handlers. We’ve seen examples of a TypeHandler, because Boolean types in our domain model, such as BusinessCustomer.businessPartner, are represented as characters Y and N in the database. iBATIS does not provide a default mapping for Booleans to type CHAR; thus a TypeHandler must be used, which is an implementation of the TypeHandlerCallback interface (part of the com.ibatis.sqlmap.client.extensions package) shown in Listing 6.25.
The setParameter method converts a Java type to a database type, and getResult converts a database type to a Java type. valueOf is required by iBATIS to compare instances of the type and to test for existence (not null). Listing 6.26 shows how the type handler was implemented for our domain model to convert Boolean Java types to CHAR database types.
Example 6.26. YesNoBoolTypeHandler Implementation
public class YesNoBoolTypeHandler implements TypeHandlerCallback {
private static final String YES = "Y";
private static final String NO = "N";
public Object getResult(ResultGetter getter)
throws SQLException {
String s = getter.getString();
if (YES.equalsIgnoreCase(s)) {
return Boolean.TRUE;
} else if (NO.equalsIgnoreCase(s)) {
return Boolean.FALSE;
} else {
throw new SQLException(
"Unexpected value " + s + " found where "
+ YES + " or " + NO + " was expected.");
}
}
public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException {
boolean b = ((Boolean) parameter).booleanValue();
if (b) {
setter.setString(YES);
} else {
setter.setString(NO);
}
}
public Object valueOf(String s) {
if (YES.equalsIgnoreCase(s)) {
return Boolean.TRUE;
} else {
return Boolean.FALSE;
}
}
}The com.ibatis.sqlmap.client.event.RowHandler interface provides an even more versatile extension point since a row handler is executed on every row of the database query. As an example, suppose that AbstractCustomer is augmented with a timestamp field. This timestamp field is used by other layers of the application to get an indication of how “fresh” the data is in a Customer POJO. Thus, this timestamp should be automatically generated every time a row for a Customer is loaded. Listing 6.27 shows the RowHandler implementation and Listing 6.28 shows the Java code necessary to execute the query with a row handler.
Example 6.27. AppendTimestampRowHandler Implementation
public class AppendTimestampRowHandler implements RowHandler {
private AbstractCustomer customer;
public void handleRow(Object customerPojo) {
this.customer = (AbstractCustomer) customerPojo;
customer.setTimestamp(new Date());
}
public AbstractCustomer getCustomer() {
return customer;
}
}Notice that the same customer.select SqlMap statement is used from Listing 6.10. Note that the row handler is just a Java class that you can instantiate, add properties to, and add a complex constructor to. Also notice that after the query is executed, the Customer with the timestamp property is simply fetched from the row handler object with a JavaBean getter.
Figure 6.2 illustrates the various exceptions that the iBATIS framework can throw. The most common type of exception you’ll see is a NestedSQLException, which contains a nested exception. For example, it may be a RuntimeSQLException, which is caused by an error in the SQL or database schema, or an SqlMapException, which is caused by an error in an SqlMap. A NodeletException is caused by a parsing error for XML. A TransactionException occurs when there is an error in a transaction, and a BatchException occurs when there is an error in a batch statement.
As shown in the preceding section, an iBATIS developer utilizes a combination of Java and XML to implement object-relational mapping. Notice how pure SQL is utilized to define object queries. This section describes how various object-relational mapping fundamentals apply to iBATIS. Some of these were discussed in Chapter 3 when discussing design principles, and again in Chapter 5 when discussing JDBC.
Objects persisted and queried with iBATIS simply need to be JavaBeans. For example, the Order object is defined, as shown in Listing 6.29.
Example 6.29. Order JavaBean Class
public class Order {
protected int orderId;
protected BigDecimal total;
public static enum Status { OPEN, SUBMITTED, CLOSED }
protected Status status;
protected AbstractCustomer customer;
protected Set<LineItem> lineitems;
// getters and setters
}All three of the inheritance strategies discussed in Chapter 3—new table, subclass table, and superclass table—can be implemented with iBATIS. However, because iBATIS is simply a tool to bind the input and output of SQL queries to objects, inheritance mapping to the database is largely left up to the developer.
To illustrate how the three inheritance strategies can be realized with iBATIS, this section walks through persisting a ResidentialCustomer for each schema variation. Let’s take a look at the Java code required to persist a ResidentialCustomer to the CUSTOMER table. The Java code in Listing 6.30 simply populates a ResidentialCustomer bean and calls an iBATIS mapped statement to persist it.
Example 6.30. Java Code for Superclass Table Inheritance
SqlMapClient dm = ...
...
ResidentialCustomer customer = new ResidentialCustomer();
customer.setName("Bill Botzum");
customer.setHouseholdSize((short)4);
customer.setFrequentCustomer(true);
dm.insert("customer.residential.insert", customer);The corresponding iBATIS XML is equally straightforward. The Map XML for the superclass strategy is shown in Listing 6.31.
Example 6.31. SQLMap for Superclass Table Inheritance
<sqlMap>
<insert id="customer.residential.insert"
class="org.pwte.example.domain.ResidentialCustomer">
insert into CUSTOMER(RESIDENTIAL_NAME,
RESIDENTIAL_HOUSEHOLD_SIZE, RESIDENTIAL_FREQUENT_CUSTOMER)
values (#name#, #householdSize#, #frequentCustomer#)
</insert>
</sqlMap>Notice how iBATIS takes care of type conversion automatically. However, iBATIS allows you to override the default Java and JDBC type as well. The developer inserts a bean property from the ResidentialCustomer with the # syntax. There is not much proven here because the insert is to a single table.
Let’s move on to the Table-per-class strategy. In this variation, two insert SQL statements are required to persist a ResidentialCustomer—one to populate ABSTRACT_CUSTOMER and the other to populate RESIDENTIAL_CUSTOMER. Only one SQL statement can reside in an iBATIS mapped statement, thus the two separate mapped statements must be invoked at the Java level to persist the ResidentialCustomer. This presents a unique challenge—what if one insert statement succeeds and the other fails?
Listing 6.32 contains the solution, which is to use the iBATIS transaction API to execute both inserts as a single unit of work. A bit unwieldy, but still less code than the equivalent in JDBC.
Example 6.32. Java Code for New Table Inheritance
ResidentialCustomer customer = new ResidentialCustomer();
customer.setName("Bill Botzum");
customer.setHouseholdSize((short)4);
customer.setFrequentCustomer(true);
try{
dm.startTransaction();
dm.insert("customer.abstract.insert", (AbstractCustomer) customer);
dm.insert("customer.residential.insert", customer);
dm.commitTransaction();
} finally {
dm.endTransaction();
}Listing 6.33 shows the two mapped statements called by the Java code. The first inserts the customer’s name into the ABSTRACT_CUSTOMER table, and the second inserts the remaining data into the RESIDENTIAL_CUSTOMER table.
Example 6.33. SQLMaps for New Table Inheritance
<sqlMap>
<insert id="customer.abstract.insert"
parameterClass="org.pwte.domain.AbstractCustomer">
insert into ABSTRACT_CUSTOMER ( NAME ) values ( #name# )
</insert>
<insert id="customer.residential.insert"
parameterClass="org.pwte.domain.ResidentialCustomer">
insert into RESIDENTIAL_CUSTOMER (
RESIDENTIAL_HOUSEHOLD_SIZE,
RESIDENTIAL_FREQUENT_CUSTOMER )
values ( #householdSize#, #frequentCustomer# )
</insert>
</sqlMap>Finally, we illustrate the subclass table inheritance strategy.
The Java code for this variation is identical to Listing 6.30 for Superclass inheritance. The SQLMap is only slightly different, as shown in Listing 6.34, which simply populates RESIDENTIAL_CUSTOMER.
Example 6.34. SQLMap for Subclass Table Inheritance
<sqlMap> <insert id="customer.residential.insert" class="org.pwte.example.domain.ResidentialCustomer"> insert into RESIDENTIAL_CUSTOMER( NAME, RESIDENTIAL_HOUSEHOLD_SIZE, RESIDENTIAL_FREQUENT_CUSTOMER) values (#name#, #householdSize#, #frequentCustomer#) </insert> </sqlMap>
All the examples shown so far have been database inserts. Consider the case in which you need to query a customer by customerId. A problem arises: How can iBATIS determine whether to instantiate the ResidentialCustomer or BusinessCustomer concrete class? Fortunately, iBATIS does have some basic support for this use case by way of the discriminator tag.
A discriminator allows for an iBATIS result map to be resolved at runtime based on a field in a database table called a discriminator field. For example, consider the case in which you retrieve a Map of Customers. Some of these customers are residential and others are business customers. The SQLMap would be as shown in Listing 6.35.
Example 6.35. Discriminator Inheritance Example
<resultMap id="customer.result" class="java.util.HashMap">
<result property="customerId" column="CUSTOMER_ID"/>
<result property="name" column="NAME"/>
<discriminator column="TYPE" javaType="string">
<subMap value="RESIDENTIAL" resultMap="residential.result"/>
<subMap value="BUSINESS" resultMap="business.result"/>
</discriminator>
</resultMap>
<resultMap id="residential.result"
class="org.pwte.domain.ResidentialCustomer">
...
</resultMap>
<resultMap id="business.result"
class="org.pwte.domain.BusinessCustomer">
...
</resultMap>
<select id="customer.select" resultMap="customer.result">
select * from CUSTOMER
</select>Notice that the result map expects an additional database field called TYPE that should have a value of RESIDENTIAL or BUSINESS. A different class is instantiated depending on the value of this field.
This is an advanced example of a result map. You may want to look over the Programming Model section to familiarize yourself with iBATIS result maps a bit more before returning to this sample.
iBATIS has no notion of a key field the same way that SQL has no special semantics for database keys. For example, if you write a map statement that queries a table with a complex key, iBATIS is unaware of this. As far as it is concerned, you’ve simply mapped a multi-property JavaBean as the parameter for an SQL statement.
The iBATIS framework also does not support the generation of keys itself. It relies on the database to perform any automated key generation. However, it does support a <selectKey> construct to retrieve a newly generated key for insert statements, because this is such a common task in applications.
As an example of using the <selectKey>, let’s modify the schema for the ORDER table in the common example such that the orderId is auto-generated with the Derby identity construct as shown in Listing 6.36.
With this modification, every time a row is inserted into the ORDER table, a unique ORDER_ID is generated with an auto-increment. To illustrate how iBATIS can work with this form of key generation, Listing 6.37 shows the Java code that inserts an Order assuming the setup is complete.
Notice how the insert statement returns the generated orderId key. The key may be used later by the application in future customer-related queries. How iBATIS is configured to return this generated key can be seen in the corresponding SQLMap shown in Listing 6.38.
Example 6.38. Example SQLMap Insert for Identity Key Generation
<insert parameterClass="org.pwte.domain.Order">
insert into ( ... ) CUSTOMER values (
#status#,
#customerId#,
#total#
)
<selectKey keyProperty="orderId" resultClass="int" type="post">
select max(ORDER_ID) from ORDER
</selectKey>
</insert>The selectKey SQL is executed after the insert SQL, which is denoted by type="post". Key generation mechanisms are proprietary to each database provider. In the case of Derby, the identity key generation method is simply an auto-increment; thus, we used a max() function to get the newly generated key. As another example, Oracle and DB2 support key generation via sequences. Listing 6.39 shows the equivalent insert map statement for a sequence called order_sequence.
Example 6.39. Example SQLMap Insert for Sequence Key Generation
<insert parameterClass="org.pwte.domain.Order">
<selectKey keyProperty="orderId" resultClass="int" type="pre">
select nextVal('order_sequence')
</selectKey>
insert into ( ... ) ORDER values (
#status#,
#customerId#,
#total#)
</insert>Notice how, in this second example, the selectKey SQL is processed first. The nextVal() function is used to determine what the next key will be so it can be returned as the result of the insert mapped statement.
iBATIS supports all Java primitive types with the exception of the char type (which is not supported by JDBC, either). Table 6.3 details the type support for iBATIS.
Table 6.3. iBATIS Type Support
Java Type | Type Alias |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
iBATIS has default type aliases for these types, so you don’t have to type out the entire package names in a result or parameter map. As an example, consider the case in which a LineItem has a quantity attribute with a Java type of BigDecimal and has to be mapped to a Derby type of BIGINT in the database schema. The SqlMap XML for a select by primary key for LineItem is shown in Listing 6.40.
Example 6.40. Type Mapping Example
<resultMap id="lineitem.map" class="lineitem">
<result property="quantity" javaType="decimal"
jdbcType="BIGINT"/>
<result property="amount"/>
<result property="product" select="product.select"
column="product_id"/>
</resultMap>
<select id="lineitem.select" resultMap="lineitem.map"
parameterClass="wrapper">
select * from LINE_ITEM
where ORDER_ID = #orderId#,
PRODUCT_ID = #productId#
</result>
</resultMap>iBATIS can also support custom types through the use of type handlers. For example, Order uses a Java 5 enumeration, called Status, that needs to be mapped to a VARCHAR(9) column in the database with values of OPEN, SUBMITTED, or CLOSED. We’ve already seen how our org.pwte.ibatis.StatusEnumTypeHandler custom type handler can be used to perform this type mapping in Listing 6.8. Listing 6.41 shows the implementation of this type handler class.
Example 6.41. StatusEnumTypeHandler Implementation
public class StatusEnumTypeHandler implements TypeHandlerCallback {
private static final String
OPEN = "OPEN",
SUBMITTED = "SUBMITTED",
CLOSED = "CLOSED";
public Object getResult(ResultGetter getter) throws SQLException {
String status = getter.getString();
if(OPEN.equals(status)) {
return Order.Status.OPEN;
} else if(SUBMITTED.equals(status)) {
return Order.Status.SUBMITTED;
} else if(CLOSED.equals(status)) {
return Order.Status.CLOSED;
} else {
throw new SQLException(
"ORDER STATUS fetched from database is: "
+status
+"
STATUS must be OPEN, SUBMITTED, or CLOSED");
}
}
public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException {
Order.Status status = (Order.Status) parameter;
if(status.equals(Order.Status.OPEN)) {
setter.setString(OPEN);
} else if(status.equals(Order.Status.SUBMITTED)) {
setter.setString(SUBMITTED);
} else {
setter.setString(CLOSED);
}
}
public Object valueOf(String s) {
if(OPEN.equals(s)) {
return Order.Status.OPEN;
} else if(SUBMITTED.equals(s)) {
return Order.Status.SUBMITTED;
} else {
return Order.Status.CLOSED;
}
}
}For more information on type handlers, see the earlier section titled “Extending the Framework.”
In addition to java.util.Date, iBATIS supports nested JavaBeans as well as java.util.HashMap. java.sql.Time and java.sql.Timestamp are also supported, although they are discouraged (using java.util.Date is a best practice for portability). iBATIS supports only one collection type—java.util.List.
As an example, consider the case in which a Customer contains a nested Address complex type. A result map can have a <select/> node that calls a nested query. Listing 6.42 illustrates the retrieval of a Customer object containing an Address.
Example 6.42. Address Containment Example
<resultMap id="customer.select.map" class="business">
<result property="customerId"/>
<result property="name"/>
<result property="address.city" />
</resultMap>
<select id="customer.select" resultMap="customer.select.map"
parameterClass="customer">
select CUSTOMER_ID, NAME, CITY from CUSTOMER
where CUSTOMER_ID = #customerId#
</select>iBATIS allows you to match Object graphs with complex relationships. By grouping Result Maps, you can map objects to complex SQL Joins for Eager fetching, or force multiple SQL statements. Listing 6.43 shows an example of issuing a join between the Line Item and Product Table. The properties of the product will automatically populate the object.
Example 6.43. One-to-One Relationship with Join
<sqlMap namespace="PWTE">
<typeAlias alias="lineitem"
type="org.pwte.example.domain.LineItem"/>
<typeAlias alias="product" type="org.pwte.example.domain.Product"/>
<resultMap id="lineitem.map" class="lineitem">
<result property="quantity" jdbcType="BIGINT"/>
<result property="amount"/>
<result property="product.productId"/>
<result property="product.price"/>
<result property="product.description"/>
</resultMap>
<select id="product.select"
resultMap="product.select.map"
parameterClass="int">
select * from LINE_ITEM as l, PRODUCT as p
where p.PRODUCT_ID =l.PRODUCT_ID and l.ORDER_ID = #orderId#
</select>
</sqlMap>The same relationship can be done as shown in Listing 6.44. In this case, the product is defined as an attribute and points to a separate select statement.
Example 6.44. One-to-One Relationship with Multiple Selects
<sqlMap namespace="PWTE">
<typeAlias alias="lineitem"
type="org.pwte.example.domain.LineItem"/>
<resultMap id="lineitem.map" class="lineitem">
<result property="quantity" jdbcType="BIGINT"/>
<result property="amount"/>
<result property="product" select="product.select"
column="product_id"/>
</resultMap>
<select id="lineitems.select" resultMap="lineitem.map">
select * from LINE_ITEM where ORDER_ID = #value#
</select>
<typeAlias alias="product" type="org.pwte.example.domain.Product"/>
<resultMap id="product.select.map" class="product">
<result property="productId"/>
<result property="price"/>
<result property="description"/>
</resultMap>
<select id="product.select"
resultMap="product.select.map"
parameterClass="int">
select * from PRODUCT where PRODUCT_ID = #value#
</select>
</sqlMap>When an application calls the lineitem.select statement, its result map calls the product.select statement to populate the nested address bean. Notice how the ADDRESS_ID is propagated from the result of the Customer select statement to a parameter of the Address select statement.
iBATIS supports 1:N and M:N relationships using the join or multiple select as well. Listing 6.45 shows an example of a one-to-many relationship using a Join. M:N would be similar.
Example 6.45. One-to-Many Relationship with Multiple Selects
<resultMap id="order.select.map" class="order" groupBy="orderId">
<result property="orderId"/>
<result property="total"/>
<result property="status"
typeHandler="org.pwte.example.ibatis.StatusEnumTypeHandler"/>
<result property="lineitems" resultMap="lineitem.map"/>
</resultMap>
<resultMap id="lineitem.map" class="lineitem">
<result property="quantity" jdbcType="BIGINT"/>
<result property="amount"/>
<result property="productId"/>
</resultMap>
<select id="order.select"
resultMap="order.select.map"
parameterClass="int">
select o.ORDER_ID, o.TOTAL, o.STATUS,
l.quantity, l.amount, l.productId
from ORDERS,LINE_ITEM
where o.ORDER_ID = #value# and o.ORDER_ID = l.ORDER_ID
</select>In our end-to-end example, we show a more complicated object graph. This can be seen in Listing 6.60. The one-to-many multi-select would be similar to the one-to-one multi-select.
iBATIS is purely a data mapping framework and is not designed to perform validation or integrity logic. Constraints such as value range checks and referential integrity are expected to be implemented at the database layer (below) or application layer (above).
That being said, there are two features of iBATIS that lend themselves to support constraint logic. The first is defining a custom type handler. The custom handler, which ensures that the Order.Status enumeration is either OPEN, CLOSED, or SUBMITTED, is an excellent example of enforcing a constraint when a database type is mapped to a Java type with a custom type handler (see the earlier section “Extending the Framework”).
The second feature that can be used to enforce constraints is the row handler API. As an example, consider a new Boolean field added to Product called CATALOGUED. A Product referred to by a LineItem must be in a catalogued state. Now suppose that we want to check this constraint on update statements. Listing 6.46 details an SQLMap that updates Product.
As you can see, this is just a generic update statement that allows the application to update any field of a Product. Listing 6.47 shows a row handler designed to be invoked every time an update to a row in the PRODUCT table is executed.
Example 6.47. Example Row Handler
public void handleRow(Object valueObject) {
Product product = (Product) valueObject;
// initialize access to framework
SqlMapClient dm = ...
// query database for LineItem corresponding to this Product
List result = dm.queryForList(
"lineitem.select.byproduct", product.getProductId());
if(result.size() > 0 && product.getCatalogued() == false) {
log.error("Product with id="+product.getProductId()
+" is referred to by at least one LineItem, "
+ "but is not marked as Catalogued.");
}
if(result.size() == 0 && product.getCatalogued() == true) {
log.error("Product with id="+product.getProductId()
+" is not referred to by at least one LineItem, "
+"but is marked as Catalogued.");
}
}Notice that the row handler performs a query for any LineItems associated with the Product. It then checks for invalid states of Catalogued and logs errors if an invalid state is found. Listing 6.48 shows the application code used to associate this row handler with the update statement shown in Listing 6.46, assuming that the name of the row handler class is ProductUpdateRowHandler.
As with constraints, iBATIS is not intended to support derived attributes. The iBATIS philosophy is to keep the framework’s purpose solely for simple data mapping—derived attributes should be maintained in the database layer (with database triggers or with nested SQL select statements) or the application layer. However, as with constraints, a custom row handler can be used to automatically populate a field of a resulting JavaBean. For example, consider the LineItem.amount attribute, which is calculated by multiplying the quantity and corresponding price of the Product. Listing 6.49 shows a row handler that performs this calculation and exposes a property to get the resulting LineItem JavaBean.
Example 6.49. Derived Attribute Row Handler Example
public class LineItemRowHandler implements RowHandler {
private LineItem lineitem;
public void handleRow(Object valueObject) {
LineItem lineitem = (LineItem) valueObject;
BigDecimal price = lineitem.getProduct().getPrice();
// amount = price * quantity
BigDecimal amount = price.multiply(
new BigDecimal(lineitem.getQuantity()));
lineitem.setAmount(amount);
// set LineItem property on row handler
setLineitem(lineitem);
}
public LineItem getLineitem() {return lineitem;}
private void setLineitem(LineItem lineitem) {this.lineitem = lineitem;}
}Notice that after the row handler calculates the amount field for LineItem, it sets a LineItem property on itself. Listing 6.50 shows how the query statement is executed from application code and how the LineItem with the populated amount attribute is retrieved.
You may wonder why such a simple calculation should be done in a row handler and not in the application code after a standard SQLMap statement. In the case that the derived attribute calculation is only ever done in one place for the application, this may very well be a more straightforward and maintainable solution. However, having the row handler calculate a derived attribute allows for reuse and allows separation of application code from query logic.
Although iBATIS focuses on simplicity, it is a feature-rich framework designed to scale well with enterprise applications. Caching, lazy loading, and batching are supported.
Because the SQL is external to the Java source code, you can tune the SQL and not need to re-compile the associated Java code that invokes it. An example of tuning an SQL statement is changing the order of conditions in a where clause or reordering the joins.
iBATIS ships with a single JVM cache and allows you to extend to distributed caches.
iBATIS can be configured to cache the results of mapped statements. A cache model is a configured cache that is defined within SqlMap XML. For example, Listing 6.51 shows how to modify the customer.select statement to be cached using a Least Recently Used (LRU) algorithm.
Example 6.51. Single-JVM Caching Example
<cacheModel id="customer-cache" type ="LRU" readOnly="true" serialize="false">
<flushInterval hours="24"/>
<property name="cache-size" value="1000" />
</cacheModel>
<select id="customer.select" cacheModel="customer-cache"
resultMap="customer.select.map" parameterClass="int">
select CUSTOMER_ID, NAME, OPEN_ORDER from CUSTOMER
where CUSTOMER_ID = #value#
</select>This defines a cache that is flushed every 24 hours and has a depth of 1,000 entries. In addition to LRU, iBATIS supports three other cache types: First-In-First-Out (FIFO), MEMORY, and OSCache. The MEMORY implementation relies on the garbage collector to determine when objects should be flushed from the cache (often a good choice when memory is scarce). Additionally, a fully qualified class name can be used for the cache type, which indicates that a custom cache mechanism should be used. A custom cache and OSCache are covered in the next section.
iBATIS fully supports caching in distributed environments. OSCache is supported out-of-the-box. To configure SqlMap statements to be cached with OSCache, set the type attribute to OSCache in the cache model. Then, all that is required is a standard oscache.proerties file in the root of the project classpath. For more information, refer to www.opensymphony.com/oscache/.
As mentioned previously, a custom cache controller can also be used for the type attribute of a cache model. The class must implement the CacheController interface (part of the iBATIS cache package). Consider Listing 6.52, which shows the interface.
Example 6.52. iBATIS CacheController Interface
public interface CacheController {
public void flush(CacheModel cacheModel);
public Object getObject(CacheModel cacheModel, Object key);
public Object removeObject(CacheModel cacheModel, Object key);
public void putObject(
CacheModel cacheModel, Object key, Object object);
public void configure(Properties props);
}At a high level, an implementation of this interface needs to store objects based on keys and then either remove or retrieve them based on the same key. A CacheModel object is provided that is populated with cache model metadata, such as the readOnly attribute. A java.util.Property object also can be used as a standard mechanism for additional metadata.
iBATIS supports lazy loading of nested complex types by default. For example, when a Customer is fetched from the database, the nested select for its Orders is not invoked until a reference to the nested Set of Orders is made in the application. Lazy loading can be configured at the SqlMapConfig level with the settings tag. Listing 6.53 provides an example. These settings are then read by the byte code enhancer to achieve optimized lazy loading semantics.
iBATIS does not maintain optimistic locking strategies. Implementing optimistic locking is left to the developer, possibly using isolation levels as discussed in Chapter 3. Isolation levels are delegated to the underlying JDBC calls as discussed earlier in the “Transactions” section of this chapter.
The iBATIS philosophy is to make development with iBATIS as natural and painless as possible. The addition of iBATIS to a software project should not conflict with an existing development process.
iBATIS utilizes Java POJOs that follow the standard JavaBean convention. This makes defining objects with iBATIS seamless because the Java objects can be the very same objects as those in your domain model. If the domain model includes types that are not natively supported by iBATIS, such as the Order.Status enumeration in our domain model, type handlers can be employed.
Listing 6.54 and Listing 6.55 illustrate some of the POJOs used for the iBATIS implementation of the common example—AbstractCustomer and ResidentialCustomer. The rest of the POJOs are omitted, considering that they are also literal JavaBean representations of the common example’s domain model.
Several SQLMap XML files corresponding to the POJOs were developed to hold the required SQL queries. Listings 6.56 through 6.59 show the SQLMaps. We have two versions of the load customer: one that issues 2N+2 Selects where N is the number of LineItems, and another that uses a complex join across the tables. The Select in the Customer.xml in Listing 6.56 represents the 2N+2 Selects, and the CustomerLoad.xml in Listing 6.60 represents the Join.
Example 6.56. Customer.xml
<sqlMap namespace="PWTE">
<typeAlias alias="abstract"
type="org.pwte.example.domain.AbstractCustomer" />
<typeAlias alias="business"
type="org.pwte.example.domain.BusinessCustomer"/>
<typeAlias alias="residential"
type="org.pwte.example.domain.ResidentialCustomer"/>
<typeAlias alias="order" type="org.pwte.example.domain.Order" />
<resultMap id="customer.select.map" class="abstract">
<result property="customerId" column="CUSTOMER_ID"/>
<discriminator javaType="String" column="TYPE">
<subMap value="BUSINESS" resultMap="customer.business.map"/>
<subMap value="RESIDENTIAL" resultMap="customer.residential.map"/>
</discriminator>
</resultMap>
<resultMap id="customer.business.map" class="business">
<result property="customerId" column="CUSTOMER_ID"/>
<result property="name" column="NAME"/>
<result property="openOrder"
select="order.select" column="OPEN_ORDER"/>
<result property="volumeDiscount" column="BUSINESS_VOLUME_DISCOUNT"
typeHandler="org.pwte.example.ibatis.YesNoBoolTypeHandler"/>
<result property="businessPartner" column="BUSINESS_PARTNER"
typeHandler="org.pwte.example.ibatis.YesNoBoolTypeHandler"/>
<result property="description" column="BUSINESS_DESCRIPTION"/>
</resultMap>
<resultMap id="customer.residential.map" class="residential">
<result property="customerId" column="CUSTOMER_ID"/>
<result property="name" column="NAME"/>
<result property="openOrder"
select="order.select" column="OPEN_ORDER"/>
<result property="householdSize"
column="RESIDENTIAL_HOUSEHOLD_SIZE"/>
<result property="frequentCustomer"
column="RESIDENTIAL_FREQUENT_CUSTOMER"
typeHandler="org.pwte.example.ibatis.YesNoBoolTypeHandler"/>
</resultMap>
<select id="customer.select"
resultMap="customer.select.map"
parameterClass="int">
select CUSTOMER_ID, NAME, OPEN_ORDER, TYPE,
BUSINESS_VOLUME_DISCOUNT,
BUSINESS_PARTNER, BUSINESS_DESCRIPTION,
RESIDENTIAL_HOUSEHOLD_SIZE,
RESIDENTIAL_FREQUENT_CUSTOMER
from CUSTOMER where CUSTOMER_ID = #value#
</select>
<update id="customer.open.order" parameterClass="int">
update CUSTOMER set OPEN_ORDER =
(select ORDER_ID from ORDERS where CUSTOMER_ID = #value#)
where CUSTOMER_ID = #value#
</update>
<update id="customer.close.order" parameterClass="int">
update CUSTOMER set OPEN_ORDER = NULL where CUSTOMER_ID = #value#
</update>
<parameterMap id="customer.residential.map" class="residential" >
<parameter property="customerId" />
<parameter property="name" />
<parameter property="householdSize" />
<parameter property="frequentCustomer"
typeHandler="org.pwte.example.ibatis.YesNoBoolTypeHandler" />
</parameterMap>
<insert id="customer.residential.insert"
parameterMap="customer.residential.map">
insert into CUSTOMER (
CUSTOMER_ID, NAME,
RESIDENTIAL_HOUSEHOLD_SIZE,
RESIDENTIAL_FREQUENT_CUSTOMER )
values ( ?, ?, ?, ? )
</insert>
</sqlMap><sqlMap namespace="PWTE">
<typeAlias alias="order" type="org.pwte.example.domain.Order" />
<typeAlias alias="customer"
type="org.pwte.example.domain.AbstractCustomer" />
<parameterMap id="order.open.map" class="order">
<parameter property="customer.customerId"/>
<parameter property="status"
typeHandler="org.pwte.example.ibatis.StatusEnumTypeHandler"/>
<parameter property="total"/>
</parameterMap>
<resultMap id="order.select.map" class="order">
<result property="orderId"/>
<result property="total"/>
<result property="status"
typeHandler="org.pwte.example.ibatis.StatusEnumTypeHandler"/>
<result property="lineitems" select="lineitems.select"
column="ORDER_ID"/>
</resultMap>
<insert id="order.open" parameterMap="order.open.map">
insert into ORDERS (CUSTOMER_ID, STATUS, TOTAL) values (?, ?, ?)
<selectKey keyProperty="orderId" resultClass="int" type="post">
select max(ORDER_ID) from ORDERS
</selectKey>
</insert>
<select id="order.select" resultMap="order.select.map"
parameterClass="int">
select ORDER_ID, TOTAL, STATUS from ORDERS
where ORDER_ID = #value#
</select>
<update id="order.submit" parameterClass="int">
update ORDERS set STATUS = 'SUBMITTED' where ORDER_ID = #value#
</update>
</sqlMap><sqlMap namespace="PWTE">
<typeAlias alias="lineitem"
type="org.pwte.example.domain.LineItem"/>
<resultMap id="lineitem.map" class="lineitem">
<result property="quantity" jdbcType="BIGINT"/>
<result property="amount"/>
<result property="product" select="product.select"
column="product_id"/>
</resultMap>
<insert id="lineitem.insert" parameterClass="java.util.HashMap">
insert into LINE_ITEM values (
#orderId#, #lineitem.product.productId#,
#lineitem.quantity#, #lineitem.amount#)
</insert>
<update id="lineitem.update" parameterClass="java.util.HashMap">
update LINE_ITEM
set QUANTITY = #lineitem.quantity#, AMOUNT = #lineitem.amount#
where PRODUCT_ID = #lineitem.product.productId# and
ORDER_ID = #orderId#
</update>
<select id="lineitems.select" resultMap="lineitem.map">
select * from LINE_ITEM where ORDER_ID = #value#
</select>
<select id="lineitem.select" resultMap="lineitem.map"
parameterClass="java.util.HashMap">
select * from LINE_ITEM
where ORDER_ID = #orderId# and PRODUCT_ID = #productId#
</select>
<delete id="lineitem.delete" parameterClass="java.util.HashMap">
delete from LINE_ITEM
where ORDER_ID = #orderId# and PRODUCT_ID = #productId#
</delete>
</sqlMap>Example 6.59. Product.xml
<sqlMap namespace="PWTE">
<typeAlias alias="product" type="org.pwte.example.domain.Product" />
<resultMap id="product.select.map" class="product">
<result property="productId"/>
<result property="price"/>
<result property="description"/>
</resultMap>
<select id="product.select"
resultMap="product.select.map"
parameterClass="int">
select * from PRODUCT where PRODUCT_ID = #value#
</select>
<select id="product.all" resultClass="java.util.HashMap">
select * from PRODUCT
</select>
</sqlMap>Example 6.60. CustomerLoad.xml
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="PWTE">
<typeAlias alias="abstract"
type="org.pwte.example.domain.AbstractCustomer" />
<typeAlias alias="business"
type="org.pwte.example.domain.BusinessCustomer"/>
<typeAlias alias="residential"
type="org.pwte.example.domain.ResidentialCustomer"/>
<typeAlias alias="order" type="org.pwte.example.domain.Order" />
<resultMap id="customer.with.openOrder.select.map" class="abstract">
<result property="customerId" column="CUSTOMER_ID"/>
<discriminator javaType="String" column="TYPE">
<subMap value="BUSINESS"
resultMap="customer.with.openOrder.business.map"/>
<subMap value="RESIDENTIAL"
resultMap="customer.with.openOrder.residential.map"/>
</discriminator>
</resultMap>
<resultMap id="customer.with.openOrder.business.map"
class="business" groupBy="customerId">
<result property="customerId" column="CUSTOMER_ID"/>
<result property="name" column="NAME"/>
<result property="volumeDiscount"
column="BUSINESS_VOLUME_DISCOUNT"
typeHandler="org.pwte.example.ibatis.YesNoBoolTypeHandler"/>
<result property="businessPartner"
column="BUSINESS_PARTNER"
typeHandler="org.pwte.example.ibatis.YesNoBoolTypeHandler"/>
<result property="description" column="BUSINESS_DESCRIPTION"/>
<result property="openOrder.orderId" column="ORDER_ID"
nullValue="0"/>
<result property="openOrder.total" column="TOTAL" nullValue="0"/>
<result property="openOrder.status"
typeHandler="org.pwte.example.ibatis.StatusEnumTypeHandler"
column="STATUS" nullValue="null"/>
<result property="openOrder.lineitems"
resultMap="lineitem.product.map"/>
</resultMap>
<resultMap id="customer.with.openOrder.residential.map"
class="residential" groupBy="customerId">
<result property="customerId" column="CUSTOMER_ID"/>
<result property="name" column="NAME"/>
<result property="openOrder.orderId" column="ORDER_ID"
nullValue="0"/>
<result property="openOrder.total" column="TOTAL" nullValue="0"/>
<result property="openOrder.status"
typeHandler="org.pwte.example.ibatis.StatusEnumTypeHandler"
column="STATUS" nullValue="null"/>
<result property="openOrder.lineitems"
resultMap="lineitem.product.map"/>
<result property="householdSize"
column="RESIDENTIAL_HOUSEHOLD_SIZE"/>
<result property="frequentCustomer"
column="RESIDENTIAL_FREQUENT_CUSTOMER"
typeHandler="org.pwte.example.ibatis.YesNoBoolTypeHandler"/>
</resultMap>
<resultMap id="lineitem.product.map" class="lineitem" >
<result property="quantity" jdbcType="BIGINT"/>
<result property="amount"/>
<result property="product.productId" column="PRODUCT_ID"/>
<result property="product.price" column="PRICE"/>
<result property="product.description" column="DESCRIPTION"/>
</resultMap>
<select id="customer.with.openOrder.select"
resultMap="customer.with.openOrder.select.map"
parameterClass="int">
SELECT
c.CUSTOMER_ID, c.OPEN_ORDER, c.NAME,
c.BUSINESS_VOLUME_DISCOUNT,
c.BUSINESS_PARTNER, c.BUSINESS_DESCRIPTION,
c.RESIDENTIAL_HOUSEHOLD_SIZE,
c.RESIDENTIAL_FREQUENT_CUSTOMER, c.TYPE,
o.ORDER_ID,o.STATUS,o.TOTAL,l.PRODUCT_ID,
l.ORDER_ID, l.QUANTITY, l.AMOUNT,
p.PRODUCT_ID,p.PRICE,p.DESCRIPTION
FROM CUSTOMER c LEFT OUTER JOIN
(ORDERS o LEFT OUTER JOIN
(LINE_ITEM l JOIN PRODUCT p ON l.PRODUCT_ID = p.PRODUCT_ID)
ON o.ORDER_ID = l.ORDER_ID)
ON c.OPEN_ORDER = o.ORDER_ID
WHERE c.CUSTOMER_ID = #value#
</select>
</sqlMap>Figure 6.3 shows all the Exceptions for our services. See the downloadable sample or refer to Chapter 3 for details.
This section details the iBATIS implementation of the services from the common example, basically utilizing most of the programming model and ORM features we discussed in the previous sections.
The code for the service interface—CustomerOrderServices—is omitted, considering that it is unchanged from the listing in Chapter 3. Nor do we show here the code to initialize the iBATIS SqlMap. The code is basically the same as the code found in the section titled “Initialization.” Our service has a special utility for accessing the SqlMap. Examine the downloadable source code for more details. In the next sub-sections, we will look at the iBATIS style implementations associated with the loadCustomer: openOrder, addLineItem, removeLineItem, and submitOrder operations.
The loadCustomer method executes the SQL Join defined in the customer.with.openOrder.select in CustomerLoad.xml. This is shown in Listing 6.61.
Example 6.61. loadCustomer
public AbstractCustomer loadCustomer(int customerId)
throws CustomerDoesNotExistException, GeneralPersistenceException {
// 1. (Re-)Initialize iBATIS Data Mapper
SqlMapClient dm = getDataMapper();
// 2. Retrieve Customer
AbstractCustomer customer = null;
try {
List<AbstractCustomer> list = dm.queryForList(
"customer.with.openOrder.select",
customerId
);
if(list.size() > 0)
customer = list.get(0);
else
throw new CustomerDoesNotExistException();
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
return customer;
}The Open Order service operation implementation creates an order Java Object and executes the appropriate named SQL. Listing 6.62 shows the complete method.
Example 6.62. openOrder
public Order openOrder(int customerId)
throws CustomerDoesNotExistException,
OrderAlreadyOpenException,
GeneralPersistenceException {
// 1. (Re-)Initialize iBATIS Data Mapper
SqlMapClient dm = getDataMapper();
Order order = null;
try {
dm.startTransaction();
// 2. Retrieve Customer using load routine
AbstractCustomer customer = loadCustomer(customerId);
// 3. Ensure Customer does not already
// have Order in 'OPEN' state
if(customer.getOpenOrder() != null &&
customer.getOpenOrder().getOrderId() != 0)
throw new OrderAlreadyOpenException();
// 4. Create and persist Order
order = new Order();
order.setCustomer(customer);
order.setStatus(Order.Status.OPEN);
order.setTotal(new BigDecimal(0.00));
int orderId = (Integer) dm.insert("order.open", order);
order.setOrderId(orderId);
// 5. Update Customer with reference
// to 'OPEN' Order
dm.update("customer.open.order", customerId);
dm.commitTransaction();
return order;
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
finally {
try {
dm.endTransaction();
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
}
}The addLineItem operation implementation first checks to see whether the Product exists by executing the product.select statement. It then queries to check whether a LineItem already exists and updates the quantity if it does; otherwise, it creates a new LineItem. Because this code is rather long, we break it into two parts—one that does the validation and the other that does the update. Listing 6.63 shows the implementation of addLineItem validations.
Example 6.63. addLineItem Validation Implementations
public LineItem addLineItem(
int customerId, int productId, long quantity)
throws CustomerDoesNotExistException,
OrderNotOpenException,
ProductDoesNotExistException,
GeneralPersistenceException {
// 1. (Re-)Initialize iBATIS Data Mapper
SqlMapClient dm = getDataMapper();
LineItem lineitem = null;
try {
dm.startTransaction();
// 2. Retrieve Customer and Validate
AbstractCustomer customer = loadCustomer(customerId);
// 3. Ensure Customer has an 'OPEN' Order
if (customer.getOpenOrder() == null ||
customer.getOpenOrder().getOrderId() == 0)
throw new OrderNotOpenException();
// 4. Retrieve Product and Validate
Product product = null;
product = (Product) dm.queryForObject(
"product.select", productId);
if(product == null)
throw new ProductDoesNotExistException();
// See Listing 6.64 for steps 5-9.
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
finally{
try {
dm.endTransaction();
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
}
}Listing 6.64 continues the implementation logic of the addLineItem service operation by populating the appropriate LineItem instance, depending on whether it already exists or not. For readability, the indent is different from it should be if “unrolled” into Listing 6.63.
Example 6.64. addLineItem Code to Populate an Existing or New LineItem
// 5. Check whether Order has existing LineItem
Map keys = new HashMap();
keys.put("orderId", customer.getOpenOrder().getOrderId());
keys.put("productId", productId);
lineitem = (LineItem) dm.queryForObject("lineitem.select", keys);
// 6. If LineItem already exists update quantity and amount
if (lineitem != null) {
lineitem.setQuantity(lineitem.getQuantity() + quantity);
lineitem.setAmount(product.getPrice().multiply(
new BigDecimal(lineitem.getQuantity())));
Map wrapper = new HashMap();
wrapper.put("lineitem", lineitem);
wrapper.put("orderId", customer.getOpenOrder().getOrderId());
// 7. Update LineItem
dm.update("lineitem.update", wrapper);
}
// 8. If LineItem does not exist, create it
else {
BigDecimal amount = product.getPrice().multiply(
new BigDecimal(quantity));
lineitem = new LineItem();
lineitem.setProduct(product);
lineitem.setQuantity(quantity);
lineitem.setAmount(amount);
// 9. Persist LineItem
Map wrapper = new HashMap();
wrapper.put("orderId", customer.getOpenOrder().getOrderId());
wrapper.put("lineitem", lineitem);
dm.insert("lineitem.insert", wrapper);
}
dm.commitTransaction();
return lineitem;The removeLineItem service operation implementation must ensure that the LineItem and OpenOrder exist before deleting the associated row. Listing 6.65 shows the Java code that uses iBATIS queries defined earlier in the “Define Objects” section. It also omits the code for validation of the Customer that is the same as in steps 2-3 of the addLineItem operation shown in Listing 6.63.
Example 6.65. removeLineItem
public void removeLineItem(int customerId, int productId)
throws CustomerDoesNotExistException,
OrderNotOpenException,
ProductDoesNotExistException,
NoLineItemsException,
GeneralPersistenceException {
// 1. (Re-)Initialize iBATIS Data Mapper
SqlMapClient dm = getDataMapper();
try {
dm.startTransaction();
// 2. Validate as in steps 2-4 of Listing 6.63
// 3. Create Key Class for Deletion
HashMap keys = new HashMap();
keys.put("orderId", customer.getOpenOrder().getOrderId());
keys.put("productId", productId);
// 4. Delete LineItem
dm.delete("lineitem.delete", keys);
dm.commitTransaction();
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
finally{
try {
dm.endTransaction();
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
}Finally, the submitOrder method implementation changes the status of the order and removes it from the openOrder property of the abstract customer class. Listing 6.66 shows the submitOrder implementation.
Example 6.66. submitOrder
public void submit(int customerId)
throws CustomerDoesNotExistException,
OrderNotOpenException,
NoLineItemsException,
GeneralPersistenceException {
// 1. (Re-)Initialize iBATIS Data Mapper
SqlMapClient dm = getDataMapper();
try {
dm.startTransaction();
// 2. Validate as in steps 2-4 of Listing 6.63
// 3. Ensure Order has LineItems
if(customer.getOpenOrder().getLineitems() == null ||
customer.getOpenOrder().getLineitems().size() == 0)
throw new NoLineItemsException();
// 4. Update Order status to 'SUBMITTED'
dm.update("order.submit", customer.getOpenOrder().getOrderId());
// 5. Remove Customer openOrder reference
dm.update("customer.close.order", customerId);
dm.commitTransaction();
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
finally{
try {
dm.endTransaction();
}
catch(SQLException sqle) {
throw new GeneralPersistenceException(sqle);
}
}
}Packaging of iBATIS JavaBeans is straightforward; however, packaging of the SqlMap XML files may require some minor considerations. These files can be packaged anywhere within an application as long as two requirements are met: (1) they are on the classpath, and (2) they are defined in the global iBATIS configuration file. The most common packaging approach is to put an SqlMap XML file in the same package as its corresponding JavaBeans. For instance, in our common example code, customer.xml is in same package as AbstractCustomer.java.
In the case of the common example, the iBATIS configuration is fairly standard. Listing 6.67 shows the sql-map-config file used.
Example 6.67. sql-map-config.xml
<sqlMapConfig>
<properties resource="database.properties"/>
<settings cacheModelsEnabled="true" enhancementEnabled="false"
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="org/pwte/example/ibatis/Customer.xml"/>
<sqlMap resource="org/pwte/example/ibatis/Order.xml"/>
<sqlMap resource="org/pwte/example/ibatis/Product.xml"/>
<sqlMap resource="org/pwte/example/ibatis/LineItem.xml"/>
</sqlMapConfig>iBATIS lends itself well to unit-testing frameworks such as JUnit. For example, if a simple JDBC datasource is used for initial unit testing, switching later to a JNDI-defined datasource is straightforward. iBATIS has no requirements for a container or external libraries. We use the same Unit Test throughout. You can examine the source found on the download site for more details. See Appendix A for instructions on downloading and executing the test cases.
iBATIS is an excellent framework to bridge the gap between a legacy database schema and modern object-oriented domain models. iBATIS works well in standalone or contained-based enterprise applications. The learning curve for both a developer and an administrator is not as steep as other frameworks because of its loyalty to standard SQL.
iBATIS can certainly scale to large enterprise applications; however, some advanced features of full object-relational mapping frameworks are left to the developer to implement. For example, every update to the database with iBATIS is explicit. If an object’s attribute is changed, the developer must explicitly call another SqlMap statement to synchronize the change with the database. Full ORM frameworks often detect changes to POJOs with reflection or special byte-code enhancement, which implicitly update the database.
iBATIS also lacks an object-oriented query language (OQL), which helps to insulate the developer from needing to know the database schema. With an OQL of some sort, developers can query for objects based on their knowledge of the domain model, which as we discussed in Chapter 3, serves as a common vocabulary for the functional requirements of the application. With iBATIS, the developer needs to understand both the domain model and the database schema, as well as how they are mapped to each other.
However, an advantage of iBATIS is that the queries can be reviewed and optimized by a DBA because they are in a separate XML file.
Looking forward, iBATIS is currently designing the next generation of its framework, which would be version 3.0. It is taking a look at Test Driven Development and the use of Java.
A.6.1 | Improve persistence with Apache Derby and iBATIS, Part 1: Initial configuration, semantics, and a simple test |
This developerWorks tutorial is part one of a three part series that provides specific details on how to use iBATIS with the Derby Database. | |
A.6.2 | Improve persistence wit Apache Derby and iBATIS, Part 2: Data definition in Derby |
This is part 2 of the tutorial series that shows the JPetStore application written in iBATIS using the Derby Database. | |
Improve persistence with Apache Derby and iBATIS, Part 3: Transactions, caching, and dynamic SQL | |
This is part 3 of the tutorial series that shows some more advanced iBATIS features needed for enterprise applications. | |
A.6.4 | DB2 UDB, WebSphere, and iBATIS |
This is an article describing using iBATIS with DB2 and WebSphere Application Server | |
www.ibm.com/developerworks/db2/library/techarticle/dm-0502cline/ | |
A.6.5 | Comment lines: Roland Barcia: Tired of hand-coding JDBC? Use iBatis as a data mapping framework instead. |
This is a basic commentary on iBATIS that provides a brief overview of the benefits of using the iBATIS framework instead of the JDBC API. | |
www.ibm.com/developerworks/websphere/techjournal/0510_col_barcia/0510_col_barcia.html |