Fuse ESB Enterprise - EIP Transaction Guide

Overview

If you need to access a database, the JDBC data source provides a convenient, general-purpose mechanism for connecting to a database and making SQL based queries and updates. To group multiple updates into a single transaction, you can instantiate a Spring DataSourceTransactionManager and create a transaction scope using the transacted() DSL command.

Sample JDBC configuration

Example 2 shows how to instantiate a JDBC transaction manager, of DataSourceTransactionManager type, which is required if you want to integrate a JDBC connection with Spring transactions. The JDBC transaction manager requires a reference to data source bean (created here with the ID, dataSource).

Example 2. Data Source Transaction Manager Configuration

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
       http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd">
    ...
    <!-- spring transaction manager -->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- datasource to the database -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <property name="driverClass" value="org.hsqldb.jdbcDriver"/>
        <property name="url" value="jdbc:hsqldb:mem:camel"/>
        <property name="username" value="sa"/>
        <property name="password" value=""/>
    </bean>

</beans>

JDBC data source transaction manager bean

In Example 2, the txManager bean is a local JDBC transaction manager instance, of DataSourceTransactionManager type. There is just one property you need to provide to the JDBC transaction manager: a reference to a JDBC data source.

JDBC data source bean

In Example 2, the dataSource bean is an instance of a JDBC data source, of javax.sql.DataSource type. The JDBC data source is a standard feature of the Java DataBase Connectivity (JDBC) specification and it represents a single JDBC connection, which encapsulating the information required to connect to a specific database.

In Spring, the recommended way to create a data source is to instantiate a SimpleDriverDataSource bean (which implements the javax.sql.DataSource interface). The simple driver data source bean creates a new data source using a JDBC driver class (which is effectively a data source factory). The properties that you supply to the driver manager data source bean are specific to the database you want to connect to. In general, you need to supply the following properties:

driverClass

An instance of java.sql.Driver, which is the JDBC driver implemented by the database you want to connect to. Consult the third-party database documentation for the name of this driver class (some examples are given in Table 6).

url

The JDBC URL that is used to open a connection to the database. Consult the third-party database documentation for details of the URL format (some examples are given in Table 6).

For example, the URL provided to the dataSource bean in Example 2 is in a format prescribed by the HSQLDB database. The URL, jdbc:hsqldb:mem:camel, can be parsed as follows:

The prefix, jdbc:hsqldb:, is common to all HSQLDB JDBC connection URLs;
The prefix, mem:, signifies an in-memory (non-persistent) database;
The final identifier, camel, is an arbitrary name that identifies the in-memory database instance.

username

The username that is used to log on to the database.

For example, when a new HSQLDB database instance is created, the sa user is created by default (with administrator privileges).

password

The password that matches the specified username.

Standalone data sources

Spring provides a variety of data source implementations, which are suitable for standalone mode (that is, the application is not deployed inside an OSGi container). These data sources are described in Table 3.

Table 3. Standalone Data Source Classes

Data Source Class	Description
`SimpleDriverDataSource`	This data source should always be used in standalone mode. You configure this data source by providing it with details of a third-party JDBC driver class. This implementation has the following features: Caches credentials for opening connections. Supports multi-threading. Compatible with the Spring transaction API. Compatible with OSGi.
`DriverManagerDataSource`	(Deprecated) Incompatible with OSGi containers. This class is superseded by the `SimpleDriverDataSource`.
`SingleConnectionDataSource`	A data source that opens only one database connection (that is, every call to `getConnection()` returns a reference to the same connection instance). It follows that this data source is incompatible with multi-threading and is therefore not recommended for general use.

J2EE data source adapters

If your application is deployed into a J2EE container, it does not make sense to create a data source directly. Instead, you should let the J2EE container take care of creating data sources and you can then access those data sources by doing a JNDI lookup. For example, the following code fragment shows how you can obtain a data source from the JNDI reference, java:comp/env/jdbc/myds, and then wrap the data source with a UserCredentialsDataSourceAdapter.

 <bean id="myTargetDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
   <property name="jndiName" value="java:comp/env/jdbc/myds"/>
 </bean>

 <bean id="myDataSource" class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
   <property name="targetDataSource" ref="myTargetDataSource"/>
   <property name="username" value="myusername"/>
   <property name="password" value="mypassword"/>
 </bean>

The JndiObjectFactoryBean exploits the Spring bean factory pattern to look up an object in JNDI. When this bean's ID, myTargetDataSource, is referenced elsewhere in Spring using the ref attribute, instead of getting a reference to the JndiObjectFactoryBean bean, you actually get a reference to the bean that was looked up in JNDI (a javax.sql.DataSource instance).

The standard javax.sql.DataSource interface exposes two methods for creating connections: getConnection() and getConnection(String username, String password). If (as is normally the case) the referenced database requires credentials in order to open a connection, the UserCredentialsDataSourceAdapter class provides a convenient way of ensuring that these user credentials are available. You can use this adapter class for wrapping JNDI-provided data sources that do not have their own credentials cache.

In addition to UserCredentialsDataSourceAdapter, there are a number of other adapter classes that you can use to wrap data sources obtained from JNDI lookups. These J2EE data source adapters are summarized in Table 4.

Table 4. J2EE Data Source Adapters

Data Source Adapter Description

Data Source Adapter	Description
`UserCredentialsDataSourceAdapter`	Data source wrapper class that caches username/password credentials, for cases where the wrapped data source does not have its own credentials cache. This class can be used to wrap a data source obtained by JNDI lookup (typically, in a J2EE container). The username/password credentials are bound to a specific thread. Hence, you can store different connection credentials for different threads.
`IsolationLevelDataSourceAdapter`	Subclass of `UserCredentialsDataSourceAdapter` which, in addition to caching user credentials, also applies the current Spring transaction's level of isolation to all of the connections it creates.
`WebSphereDataSourceAdapter`	Same functionality as `IsolationLevelDataSourceAdapter`, except that the implementation is customized to work with IBM-specific APIs.

UserCredentialsDataSourceAdapter

Data source wrapper class that caches username/password credentials, for cases where the wrapped data source does not have its own credentials cache. This class can be used to wrap a data source obtained by JNDI lookup (typically, in a J2EE container).

The username/password credentials are bound to a specific thread. Hence, you can store different connection credentials for different threads.

IsolationLevelDataSourceAdapter Subclass of UserCredentialsDataSourceAdapter which, in addition to caching user credentials, also applies the current Spring transaction's level of isolation to all of the connections it creates.

WebSphereDataSourceAdapter Same functionality as IsolationLevelDataSourceAdapter, except that the implementation is customized to work with IBM-specific APIs.

Data source proxies for special features

You can wrap a data source with a data source proxy in order to add special functionality to a data source. The data source proxies can be applied either to a standalone data source or a data source provided by the container. They are summarized in Table 5.

Table 5. Data Source Proxies

Data Source Proxy Description

Data Source Proxy	Description
`LazyConnectionDataSourceProxy`	This proxy uses lazy semantics to avoid unnecessary database operations. That is, a connection will not actually be opened until the application code attempts to write (or read) to the database. For example, if some application code opens a connection, begins a transaction, and then commits a transaction, but never actually accesses the database, the lazy connection proxy would optimize these database operations away.
`TransactionAwareDataSourceProxy`	Provides support for legacy database code that is not implemented using the Spring persistence API. Do not use this proxy for normal transaction support. The other Spring data sources are already compatible with the Spring persistence and transaction APIs. For example, if your application code uses Spring's `JdbcTemplate` class to access JDBC resources, do not use this proxy class.

LazyConnectionDataSourceProxy

This proxy uses lazy semantics to avoid unnecessary database operations. That is, a connection will not actually be opened until the application code attempts to write (or read) to the database.

For example, if some application code opens a connection, begins a transaction, and then commits a transaction, but never actually accesses the database, the lazy connection proxy would optimize these database operations away.

TransactionAwareDataSourceProxy

Provides support for legacy database code that is not implemented using the Spring persistence API.

Do not use this proxy for normal transaction support. The other Spring data sources are already compatible with the Spring persistence and transaction APIs. For example, if your application code uses Spring's JdbcTemplate class to access JDBC resources, do not use this proxy class.

Third-party JDBC driver managers

Table 6 shows the JDBC connection details for a variety of different database products.

Table 6. Connection Details for Various Databases

Database	JDBC Driver Manager Properties
HSQLDB	The JDBC driver class for HSQLDB is as follows: org.hsqldb.jdbcDriver To connect to a HSQLDB database, you can use one of the following JDBC URL formats: jdbc:hsqldb:hsql[s]://host[:port][/`DBName`][`KeyValuePairs`] jdbc:hsqldb:http[s]://host[:port][/`DBName`][`KeyValuePairs`] jdbc:hsqldb:mem:`DBName`[`KeyValuePairs`] Where the `hsqls` and `https` protocols use TLS security and the `mem` protocol references an in-process, transient database instance (useful for testing). For more details, see http://www.hsqldb.org/doc/src/org/hsqldb/jdbc/jdbcConnection.html.
MySQL	The JDBC driver class for MySQL is as follows: com.mysql.jdbc.Driver To connect to a MySQL database, use the following JDBC URL format: jdbc:mysql://[host][,failoverhost...][:port]/[`DBName`][`Options`] Where the `Options` coincidentally have the same format as Camel component options—for example, `?Option1=Value1&Option2=Value2`. For more details, see http://dev.mysql.com/doc/refman/6.0/en/connector-j-reference-configuration-properties.html.
Oracle	Depending on which version of Oracle you are using choose one of the following JDBC driver classes: oracle.jdbc.OracleDriver (Oracle 9i, 10) oracle.jdbc.driver.OracleDriver (Oracle 8i) To connect to an Oracle database, use the following JDBC URL format: jdbc:oracle:thin:[user/password]@[host][:port]:`SID` Where the Oracle System ID (`SID`) identifies an Oracle database instance. For more details, see http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/basic.htm.
DB2	The JDBC driver class for DB2 is as follows: com.ibm.db2.jcc.DB2Driver To connect to a DB2 database, use the following JDBC URL format: jdbc:db2://host[:port]/`DBName`
SQL Server	The JDBC driver class for SQL Server is as follows: com.microsoft.jdbc.sqlserver.SQLServerDriver To connect to a SQL Server database, use the following JDBC URL format: jdbc:microsoft:sqlserver://host[:port];DatabaseName=`DBName`
Sybase	The JDBC driver class for Sybase is as follows: com.sybase.jdbc3.jdbc.SybDriver To connect to a Sybase database, use the following JDBC URL format: jdbc:sybase:Tds:host:port/`DBName`
Informix	The JDBC driver class for Informix is as follows: com.informix.jdbc.IfxDriver To connect to an Informix database, use the following JDBC URL format: jdbc:informix-sqli://host:port/`DBName`:informixserver=`DBServerName`
PostgreSQL	The JDBC driver class for PostgreSQL is as follows: org.postgresql.Driver To connect to a PostgreSQL database, use the following JDBC URL format: jdbc:postgresql://host[:port]/`DBName`
MaxDB	The JDBC driver class for the SAP database is as follows: com.sap.dbtech.jdbc.DriverSapDB To connect to a MaxDB database, use the following JDBC URL format: jdbc:sapdb://host[:port]/`DBName`
FrontBase	The JDBC driver class for FrontBase is as follows: com.frontbase.jdbc.FBJDriver To connect to a FrontBase database, use the following JDBC URL format: jdbc:FrontBase://host[:port]/`DBName`

JDBC Data Source