Section 5.1. Integrating iBATIS | Spring: A Developers Notebook

5.1. Integrating iBATIS

In a fiasco that's become famous, a sample application from Sun was used as a centerpiece for a benchmark in a widely publicized competition between J2EE and .NET. The .NET version soundly beat the EJB-based J2EE version, and the heat was on. Clinton Begin built a simplified version of PetStore around his persistence framework called iBATIS, and it's grown in popularity ever since. Spring provides excellent iBATIS integration, and we'll look at it in this chapter.

Not all problems are well-suited for full-blown persistence frameworks. They're moderately complicated in the best of circumstances. Without the right skills or with an inappropriate problem, they can be disastrous. In a class that we teach together, Ted Neward, the author of Effective Enterprise Java, often compares building or adopting a persistence framework to the United States war in Vietnam, claiming that they're both seductive wars to enter, but both wars are difficult to win, and there's no effective exit strategy in either case. You can still find the blog at http://www.neward.net.

While I won't go quite that far, it's nice to have a framework like iBATIS SqlMaps that gives you a taste of an OR usage model without forcing you to eat a whole elephant with one bite. Specifically, iBATIS lets you:

Map columns and SQL statements to keywords
Use the full power of SQL without the tedium of JDBC
Break your SQL away from your code

Spring with iBATIS gives you these advantages and more. Let's get busy.

5.1.1. How do I do that?

First, you need to install iBATIS. You don't have to configure it right away, because the configuration will go into your Spring application context. You can find it at http://www.ibatis.com/. Download it and set it up. We're using Version 1.3.1 for this book. Place the iBATIS-provided jars (ibatis-sqlmap.jar, ibatis-dao.jar, and ibatis-common.jar) as well as the Spring-provided jdom.jar (in Spring's /lib folder) in the /war/WEB-INF/lib folder of your project directory).

You've already got the interface for the façade and the model, so you need an implementation of the façade and the SQL statements. First, you can implement the façade for the application, as in Example 5-1.

Example 5-1. IBatisRentABike.java

public class IBatisRentABike extends SqlMapDaoSupport    implements RentABike {         private String storeName = "";    public void setStoreName(String name) {this.storeName = name;}    public String getStoreName( ) {return this.storeName;}    public List getBikes( ) {       return getSqlMapTemplate( ).executeQueryForList("getBikes", null);    }    public Bike getBike(String serialNo) {       return (Bike) getSqlMapTemplate( ).          executeQueryForObject("getBikeBySerialNo", serialNo);    }    public Bike getBike(int bikeId) {       return (Bike) getSqlMapTemplate( ).          executeQueryForObject("getBikeByID", new Integer(bikeId));    }    public void saveBike(Bike bike) {       getSqlMapTemplate( ).executeUpdate("saveBike", bike);    }    public void deleteBike(Bike bike) {       getSqlMapTemplate( ).executeUpdate("deleteBike", bike);    }    public List getCustomers( ) {       return getSqlMapTemplate( ).          executeQueryForList("getCustomers", null);    }    public Customer getCustomer(int custId) {       return (Customer) getSqlMapTemplate( ).          executeQueryForObject("getCustomer", new Integer(custId));    }    public List getReservations( ) {       return getSqlMapTemplate( ).          executeQueryForList("getReservations", null);    }    public List getReservations(Customer customer) {       return getSqlMapTemplate( ).          executeQueryForList("getReservationsForCustomer", customer);    }    public List getReservations(Bike bike) {       return getSqlMapTemplate( ).          executeQueryForList("getReservationsForBike", bike);    }    public List getReservations(Date date) {       return getSqlMapTemplate( ).          executeQueryForList("getReservationsForDate", date);    }        public Reservation getReservation(int resId) {       return (Reservation) getSqlMapTemplate( ).          executeQueryForObject("getReservation", new Integer(resId));    } }

The SqlMapTemplate is provided by Spring's SqlMapDaoSupport class which our RentABike implementation must extend. It handles the establishment and management of connections to the underlying data store as well as interpretation of the map files you provide. You can look at the template as a default implementation for the things you'd like to do with an iBATIS named query.

You'll also need to create the SQL statements. You can give each SQL statement a name. Then, you'll map the results to a Java bean. You have two choices here. You can either reference every bean property in an SQL AS alias, or build an explicit map between the query and the bean, as in Example 5-2.

Example 5-2. Bike.xml (an IBatis SQL map file)

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sql-map     PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN"     "http://www.ibatis.com/dtd/sql-map.dtd"> <sql-map name="Bike">   <result-map name="result" >     <property name="bikeId" column="bikeId" columnIndex="1"/>     <property name="manufacturer" column="manufacturer" columnIndex="2"/>     <property name="model" column="model" columnIndex="3"/>     <property name="frame" column="frame" columnIndex="4"/>     <property name="serialNo" column="serialNo" columnIndex="5"/>     <property name="weight" column="weight" columnIndex="6"/>     <property name="status" column="status" columnIndex="7"/>   </result-map>   <mapped-statement name="getBikes" result-map="result">     select bikeId, manufacturer, model, frame, serialNo, weight, status     from bikes   </mapped-statement>   <mapped-statement name="getBikeBySerialNo" result-map="result">     select bikeId, manufacturer, model, frame, serialNo, weight, status     from bikes     where serialNo=#value#   </mapped-statement>   <mapped-statement name="getBikeByID" result-map="result">     select bikeId, manufacturer, model, frame, serialNo, weight, status     from bikes     where bikeId=#value#   </mapped-statement>   <mapped-statement name="saveBike">     insert into bikes       (bikeId, manufacturer, model, frame, serialNo, weight, status)       values (#bikeId#, #manufacturer#, #model#, #frame#, #serialNo#,        #weight#, #status#)   </mapped-statement>   <mapped-statement name="deleteBike">       delete from bikes       where bikeId = #bikeId#   </mapped-statement> </sql-map>

The <result-map> portion provides an explicit map between columns in the database and properties of a persistent class. The <mapped-statements> can then simply define the SQL queries necessary to execute the needed functionality, and the map handles creation of the resultant Java object. In addition to the Bike version above, your application currently also requires a map for Customer and Reservation.

You're going to have to do a little work that an OR framework would normally do for us, like create identifiers. In this case, you're going to use MySQL sequences (which are just AUTO_INCREMENT columns on a table). You simply define our table with the bikeId column marked as AUTO_INCREMENT and, when inserting a new row, ignore the bikeId column. The SaveBike statement from our map now becomes Example 5-3.

Example 5-3. Bike.xml

<mapped-statement name="saveBike">    insert into bikes    (manufacturer, model, frame, serialNo, weight, status)    values (#manufacturer#, #model#, #frame#, #serialNo#, #weight#,      #status#) </mapped-statement>

If you are using Oracle, Spring and iBatis support Oracle sequences as well.

Next, you can update the application context. You'll need to specify our new façade, which requires the SQL maps. Introduce the SQL maps in a properties file, like we do the JDBC parameters. You'll also need to configure a transaction strategy (Example 5-4).

Example 5-4. RentABikeApp-servlet.xml

<beans>    <bean         >       <property name="driverClassName">          <value>com.mysql.jdbc.Driver</value>       </property>       <property name="url">          <value>jdbc:mysql://localhost/bikestore</value>       </property>       <property name="username">          <value>bikestore</value>       </property>    </bean>    <bean  >       <property name="storeName"><value>Bruce's Bikes</value></property>       <property name="dataSource"><ref local="dataSource"/></property>       <property name="sqlMap"><ref local="sqlMap"/></property>    </bean>    <bean         >       <property name="configLocation">          <value>/WEB-INF/ibatis.config</value>       </property>    </bean>    <bean        >       <property name="dataSource"><ref local="dataSource"/></property>    </bean>

Example 5-5. ibatis.config

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sql-map-config     PUBLIC "-//iBATIS.com//DTD SQL Map Config 1.0//EN"     "http://www.ibatis.com/dtd/sql-map-config.dtd"> <sql-map-config>     <sql-map resource="Bike.xml" />     <sql-map resource="Customer.xml" />     <sql-map resource="Reservation.xml" /> </sql-map-config>

We'll talk about the transaction strategy more later.

Note: This transaction strategy is in the context. You won't have to manage commits, because Spring will do those for you. You'll see a more complete treatment of transactions in Chapter 6.

5.1.2. What just happened?

You're not seeing OR mapping. An OR framework will typically tie a database table to a class, or classes. In this case, iBATIS attaches the results from a query to a class. That means that iBATIS makes no attempt to hide SQL from you at all. In fact, it embraces SQL.

Spring is going to simplify iBATIS usage through templates. The iBATIS template will give you a similar usage model to the JDBC template. You'll specify a datasource and an iBATIS mapped SQL statement.

When you execute the statement, Spring works with iBATIS to manage the resources for you, creating and closing connections as required. Spring will pass the mapped statement to iBATIS, which executes the mapped statement, and if necessary, puts the result set into the bean that you specified when you mapped the SQL statement. If you've got any parameters, you place those in a hash map and pass those to the template with the mapped statement.

While the internals might not look like an OR framework, the usage model definitely has an OR flavor to it. Build data access objects with methods to manipulate the data store. You don't see SQL in your code; it remains in configuration files. Work with collections of objects instead of result sets. In short, it's an elegant compromise between full OR and JDBC.

5.1.3. What about...

...using iBATIS for everything? Since this usage model is so similar to an OR model and since many applications need more control over the SQL that they might generate, you may be tempted to use iBATIS pervasively. Still, there's room for OR frameworks like JDO and Hibernate. OR frameworks give you more flexibility and power:

Some advanced object models lead to very complex SQL, which is best delivered through ORM. For example, inheritance often complicates native JDBC.
Some advanced performance features, like lazy loading and fetch groups, require a more formal model for effective automation. While perfectly tuned JDBC will always be at least as fast as ORM, the performance tuning options afforded by ORM make it easier to get better performance for some types of problems than native JDBC.
ORM makes some problems less tedious. It's simply easier to manipulate an object than to create a set of SQL queries. Applications with simple finders and create, update, read-by-primary-key, and delete may be easier with ORM.

If you have a rapidly changing data model and object model, iBATIS can come up short. If you've got a CRUD style application, then iBATIS may be a little tedious for you. If, instead, you're looking for good access to SQL and an effective compromise between ORM and native JDBC, then iBATIS can probably give you everything you need.