iBATIS SQL Maps

The basic idea of SQL Maps is simple: Statements are defined in an XML file, specifying an SQL string with parameter placeholders (optionally specifying SQL types and other details for the parameters). On execution, the placeholders are resolved to given parameter values, either from a parameter map, a JavaBean with bean properties, or a simple parameter object. In case of an SQL query, the definition maps result columns to a result object, supporting the same kinds of values as for parameters.

Beyond plain statement mapping, SQL Maps provides support for caching query results, with pluggable cache strategies. Various out-of-the-box implementations are provided, including a default memory cache and a strategy for OpenSymphony's OSCache. Cache models are applied per mapped query statement; it's possible to specify insert/update/delete statements that should cause flushing of the particular cache (for example to flush the accountCache cache if an updateAccount statement is executed). There is no support for evicting specific object instances from the cache; if a single Account gets updated, all Account instances will be evicted.

For detailed information on SQL Maps capabilities and configuration, please refer to Clinton Begin's excellent iBATIS SQL Maps Developer Guide (available from www.ibatis.com). We will concentrate on Spring integration in this section, showing examples from the Spring JPetStore — which builds on the original iBATIS JPetStore, for example reusing its SQL Maps definition files. We will refer to the Spring1.2 version of JPetStore here, which uses iBATIS SQL Maps 2.0, rather than SQL Maps 1.3, as used by the original JPetStore.

iBATIS SQL Maps is available from www.ibatis.com, under the Apache license 2.0. SQL Maps 2.0 final was released in June 2004, providing the new (non–backward-compatible) SqlMapClient API as successor of the original SqlMap API, and featuring a revised mapping file format. The iBATIS project applied for Apache membership in late 2004, so future releases are likely to be offered under the Apache umbrella.

Mapping File

An SQL Maps mapping file contains mapped statements, including parameter and result mapping. For example, JPetStore's Account domain object is mapped as follows (omitting a couple of further statements from the actual JPetStore Account.xml file for simplicity's sake):

<sqlMap namespace="Account">       <typeAlias alias="account"       type="org.springframework.samples.jpetstore.domain.Account"/>       <resultMap  >     <result property="username" column="USERID"/>     <result property="email" column="EMAIL"/>     <result property="firstName" column="FIRSTNAME"/>     <result property="lastName" column="LASTNAME"/>     <result property="status" column="STATUS"/>     <result property="address1" column="ADDR1"/>     <result property="address2" column="ADDR2"/>     <result property="city" column="CITY"/>     <result property="state" column="STATE"/>     <result property="zip" column="ZIP"/>     <result property="country" column="COUNTRY"/>     <result property="phone" column="PHONE"/>     <result property="languagePreference" column="LANGPREF"/>     <result property="favouriteCategoryId" column="FAVCATEGORY" />     <result property="listOption" column="MYLISTOPT" />     <result property="bannerOption" column="BANNEROPT" />     <result property="bannerName" column="BANNERNAME" />   </resultMap>       <select  resultMap="accountResult"       parameter>     select           SIGNON.USERNAME as USERID,           ACCOUNT.EMAIL,           ACCOUNT.FIRSTNAME,           ACCOUNT.LASTNAME,           ACCOUNT.STATUS,           ...           PROFILE.LANGPREF,           PROFILE.FAVCATEGORY,           PROFILE.MYLISTOPT,           PROFILE.BANNEROPT,           BANNERDATA.BANNERNAME     from ACCOUNT, PROFILE, SIGNON, BANNERDATA     where ACCOUNT.USERID = #value#       and SIGNON.USERNAME = ACCOUNT.USERID       and PROFILE.USERID = ACCOUNT.USERID       and PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY   </select>       <insert  parameter>     insert into ACCOUNT (EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2,     CITY, STATE, ZIP, COUNTRY, PHONE, USERID)     values (#email#, #firstName#, #lastName#, #status#, #address1#,     #address2:VARCHAR#, #city#, #state#, #zip#, #country#, #phone#, #username#)   </insert>       <insert  parameter>     insert into PROFILE (LANGPREF, FAVCATEGORY, MYLISTOPT, BANNEROPT, USERID)     values (#languagePreference#, #favouriteCategoryId#, #listOption#,     #bannerOption#, #username#)   </insert>       <insert  parameter>     insert into SIGNON (PASSWORD,USERNAME) values (#password#, #username#)   </insert>       ...     </sqlMap> 

The most important parts of an SQL Maps mapping file are:

• Result maps: Mapping result set columns to JavaBean properties of the Account domain class. A result map can be reused for multiple query statements. Alternatively, bean property names can also be specified inline in the statement, as column aliases (not shown in the preceding sample).

• Query statements: Defining SQL select statements, referring to a result map for mapping the result columns onto an Account domain object. The result of executing such a statement via the SQL Maps API will be one or more instances of the Account domain class.

• Update statements: Defining SQL insert/update/delete statements, using an Account domain object as parameter. Each # ... # placeholder refers to a bean property of the Account class, using its value as column value in the database table.

This is already a typical, fully functional mapping file for iBATIS SQL Maps. Of course, there are further options, such as specifying a cache model. However, the basic principle is illustrated in the preceding sample: mapped SQL statements with result maps and parameter maps. For further details, please refer to the iBATIS SQL Maps Developer Guide.

Note that such mapped statements can leverage any specific database SQL constructs, for example on Oracle; you are in full control over the SQL issued. If you need multiple versions of a statement to support multiple databases, simply define different statement IDs for them and let your DAO choose a specific statement to execute. Whether the DAO detects the database and chooses an appropriate statement or has special subclasses for specific databases is up to you; iBATIS does not provide any prebuilt database abstraction.

Usage of iBATIS SQL Maps in Spring does not impose any special requirements. The mapping files are usually completely independent from the actual usage style; they can be seamlessly used with plain iBATIS API or with Spring's iBATIS support.

Important 

iBATIS SQL Maps works at the SQL statement level. The application developer defines mapped SQL statements with results and parameters. In contrast to such a statement-based approach, full-blown O/R mapping tools such as Hibernate map persistent classes to database tables and columns, generating the SQL statements under the hood.

DAO Implementation

Spring provides prebuilt convenience classes for accessing iBATIS SQL Maps according to Spring conventions, that is, in DAOs that throw Spring's generic DataAccessException and are usually set up as Spring beans in a Spring application context.

The template class for data access operations is org.springframework.orm.ibatis.SqlMapClientTemplate, working with iBATIS 2.0's SqlMapClient API underneath. It is typically used through the base class org.springframework.orm.ibatis.support.SqlMapClientDaoSupport, which takes an SqlMapClient instance as bean property and provides an SqlMapClientTemplate instance for it. (This is not a requirement, however: SqlMapClientTemplate can also be instantiated directly, just like JdbcTemplate.)

For example, the DAO for account domain objects in JPetStore looks as follows, when leveraging SqlMapClientDaoSupport:

 public class SqlMapAccountDao extends SqlMapClientDaoSupport     implements AccountDao {       public Account getAccount(String username) throws DataAccessException {     return (Account) getSqlMapClientTemplate().queryForObject(         "getAccountByUsername", username);   }       public void insertAccount(Account account) throws DataAccessException {     getSqlMapClientTemplate().update("insertAccount", account);     getSqlMapClientTemplate().update("insertProfile", account);     getSqlMapClientTemplate().update("insertSignon", account);   }       ... }

Without the provided convenience base class, the implementation could implement its own setSqlMapClient method, preferably creating a shared SqlMapClientTemplate instance for the DAO:

 public class SqlMapAccountDao implements AccountDao {       private SqlMapClientTemplate sqlMapClientTemplate;       public void setSqlMapClient(SqlMapClient sqlMapClient) {     this.sqlMapClientTemplate  = new SqlMapClientTemplate(sqlMapClient);   }       public Account getAccount(String username) throws DataAccessException {     return (Account) this.sqlMapClientTemplate.queryForObject(         "getAccountByUsername", username);   }       public void insertAccount(Account account) throws DataAccessException {     this.sqlMapClientTemplate.update("insertAccount", account);     this.sqlMapClientTemplate.update("insertProfile", account);     this.sqlMapClientTemplate.update("insertSignon", account);   }       ... }

As you have come to expect, Spring takes care of all resource management "plumbing" and cleanup issues — you just need to specify the required persistence operations.

Alternatively, a DAO implementation could also use the provided SqlMapClient instance directly, without Spring's SqlMapClientTemplate. However, this would involve manual exception translation and checks for existing Spring transactions; therefore, it is usually advisable to stick with SqlMapClientTemplate.

Essentially, each SqlMapClient operation refers to a statement name as defined in the mapping file. The input parameters must correspond to the defined input parameters in the statement mappings (String, Account object). The result values, if any, can be cast to the defined result type (an Account object in the query case).

All convenience operations defined on SqlMapClientTemplate correspond one-to-one to operations on com.ibatis.sqlmap.client.SqlMapExecutor, but they throw Spring's DataAccessException rather than JDBC's checked SQLException, and participate in Spring transactions (if any). Please refer to the iBATIS docs for details on the semantics of those operations.

As an alternative to invoking such operations on SqlMapClientTemplate itself, there is the option to implement an org.springframework.orm.ibatis.SqlMapClientCallback that works directly on a given SqlMapExecutor resource, via SqlMapClientTemplate's generic execute method. However, this is rarely necessary for typical one-line operations. One use case for such a callback is working with batches, for example.

All such a DAO needs to be able to work is an iBATIS 2.0 SqlMapClient instance via the setSqlMapClient method. In the next section, you will see how to set up such an instance in a Spring context.

Setup in a Spring Context

In a Spring application context, an iBATIS SQL Maps instance is usually set up via Spring's SqlMapClientFactoryBean, referring to an SQL Maps configuration file and the JDBC DataSource to use:

<bean      >   <property name="configLocation">     <value>classpath:sql-map-config.xml</value>   </property>   <property name="dataSource">     <ref bean="dataSource"/>   </property> </bean>

The sql-map-config.xml file enumerates the mapping files to load for this SqlMapClient instance:

<sqlMapConfig>       <sqlMap resource="org/springframework/samples/jpetstore/dao/ibatis/Account.xml"/>   <sqlMap resource="org/springframework/samples/jpetstore/dao/ibatis/Product.xml"/>   ...     </sqlMapConfig>

The SQL Maps configuration file may also contain further configuration such as a JDBC connection pool and transaction configuration; however, this is not common when used within Spring. Instead, the SqlMapClientFactoryBean refers to a Spring-configured DataSource bean, and typically this DataSource is backed by a connection pool. Note that iBATIS lazy loading will still work properly, even if the JDBC configuration is managed by Spring.

The account DAO can now receive a reference to the SqlMapClient instance via a Spring bean reference, just as a JDBC-based DAO receives a reference to a JDBC DataSource:

<bean      >   <property name="sqlMapClient">     <ref bean="sqlMapClient"/>   </property> </bean>

Whether the sqlMapClient property, corresponding to the setSqlMapClient method, is defined by the provided SqlMapClientDaoSupport base class or implemented by the DAO class itself is not relevant here. The same configuration would work in both scenarios.

Transaction Management

While iBATIS does provide its own API for transaction management, this API is not normally used in a Spring environment. Instead, transaction management is delegated to Spring's generic transaction facilities, with the generic TransactionTemplate or TransactionProxyFactoryBean used for demarcation. See Chapter 6 for details on Spring transaction management.

There is no special transaction backend implementation for iBATIS, as SQL Maps do not involve special resource handling. Rather, any transaction strategy suitable for JDBC will work with iBATIS too; DAOs based on SqlMapClientTemplate will simply participate in such JDBC-based transactions. In particular, the following choices are available:

• org.springframework.jdbc.datasource.DataSourceTransactionManager: Allows the execution of transactions on a single JDBC DataSource — that is, a single target database — using the local transaction methods on the JDBC Connection interface. Both JDBC-based and iBATIS-based DAOs can seamlessly participate in such transactions. This strategy is usually sufficient as long as there is no need for transactions that span multiple transactional resources.

• org.springframework.transaction.jta.JtaTransactionManager: Delegates transaction execution to a JTA implementation — usually a J2EE server's JTA subsystem. SqlMapClientTemplate-based DAOs will automatically participate in such transactions, as long as the JDBC DataSource is JTA-aware — that is, defined as XA DataSource in the J2EE server. This strategy allows for performing transactions across multiple transactional resources, for example across multiple database systems. See Chapter 6 for details on when to choose JTA.

Note that no special attention is required for iBATIS lazy loading. If loading within a transaction, it will participate in it; if loading outside a transaction, it will fetch a non-transactional JDBC connection for that particular operation. This is in sharp contrast to Hibernate and JDO, which have special lifecycle requirements for lazy loading.

For example, a transactional proxy for the account DAO defined previously could look as follows. Of course, it is usually preferable to define transactional proxies at the service facade level rather than at the DAO level; we will just use this example for simplicity's sake. The actual Spring JPetStore uses a proxy at the PetStoreFacade level rather than the DAO level.

<bean       >   <property name="dataSource">     <ref bean="dataSource"/>   </property> </bean>     <bean       >   <property name="sqlMapClient">     <ref bean="sqlMapClient"/>   </property> </bean>     <bean     >   <property name="transactionManager">     <ref bean="transactionManager"/>   </property>   <property name="target">     <ref bean="accountDaoTarget"/>   </property>   <property name="transactionAttributes">     <props>       <prop key="insert*">PROPAGATION_REQUIRED</prop>       <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>     </props>   </property> </bean>

Please refer to Chapter 6 for details on transaction proxy definitions. This configuration example uses Spring's standard JDBC DataSourceTransactionManager, which is, as explained in its description, perfectly suitable for iBATIS-based DAOs.

iBATIS Summary

iBATIS SQL Maps is a straightforward data access tool, working at the SQL level like JDBC code, but externalizing SQL statements and their result and parameter mappings into an XML file. It does not aim to abstract specific databases but rather gives full control over the SQL issued, allowing the developer to leverage the full power of the target database, including proprietary SQL syntax.

The SQL Maps approach is particularly suitable for read-only reference data, where database tables or views are read into domain object representations, but not necessarily with a direct relationship between table and domain class. Such query statements can share the same result map; property to column mappings are defined only once. As such, reference data won't be changed from within that kind of application; a tool that does not track changes in the first place is a good match.

However, a complex read-write domain model is not a good fit for SQL Maps, as this would require many definitions for insert and update statements, with repeated property to column mappings. Furthermore, it is usually desirable to store at the level of primary persistent objects in such a scenario, rather than call store for each fine-grained persistent object in a graph. For such an application with a complex domain model, a full-blown O/R mapping tool such as Hibernate is recommended.

Usage of iBATIS in Spring is straightforward. Essentially, all you need is an SqlMapClient instance, populated with mapping files, to be accessed by your DAOs. Transaction management is exactly the same as in a JDBC scenario, using Spring's DataSourceTransactionManager or JtaTransactionManager.

Mixing Spring's JDBC support and iBATIS SQL Maps is seamless. Some DAOs could work with the former, some with the latter, sharing the same JDBC DataSource and the same JDBC-based transactions. While externalizing SQL statements is usually a good idea, Spring's JDBC support is particularly appropriate for applying complex parameters, analyzing complex results, and handling BLOB/CLOB access. So an application could use iBATIS SQL Maps for most of its data access, but still leverage Spring's JDBC support for special operations: for example, streaming binary content from a BLOB.

Important 

The beauty of iBATIS SQL Maps is its simplicity. Externalizing SQL statements and their result and parameter mappings into an XML file is a simple but nevertheless powerful concept. In contrast to Hibernate or JDO, there is no special query language to learn and no automatic change detection working in the background, which means that there is less complexity to deal with.