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.
Example 2.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.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>
In Example 2.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.
In Example 2.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 2.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 2.6).
For example, the URL provided to the
dataSource
bean in Example 2.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.
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 2.3.
Table 2.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:
|
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. |
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 2.4.
Table 2.4. J2EE Data Source Adapters
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. |
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 2.5.
Table 2.5. Data Source Proxies
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 |
Table 2.6 shows the JDBC connection details for a variety of different database products.
Table 2.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][/ Where the |
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]/[ Where the |
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]:
Where the Oracle System ID
( |
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]/
|
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=
|
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/
|
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/ |
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]/
|
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]/
|
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]/
|