This section describes how to access a database through the Spring
JdbcTemplate
class and provides a code example that shows how to
use the JdbcTemplate
class in practice.
The org.springframework.jdbc.core.JdbcTemplate class is the key class for
accessing databases through JDBC in Spring. It provides a complete API for executing
SQL statements on the database at run time. The following kinds of SQL operations
are supported by JdbcTemplate
:
Querying (
SELECT
operations).Updating (
INSERT
,UPDATE
, andDELETE
operations).Other SQL operations (all other SQL operations).
The JdbcTemplate query methods are used to send SELECT
queries
to the database. A variety of different query methods are supported, depending on
how complicated the return values are.
The simplest case is where you expect the query to return a single value from a
single row. In this case, you can use a type-specific query method to retrieve the
single value. For example, if you want to retrieve the balance of a particular
customer's account from the accounts
table, you could use the following
code:
// Java int origAmount = jdbc.queryForInt( "select amount from accounts where name = ?", new Object[]{name} );
The arguments to the SQL query are provided as a static array of objects,
Object[]{name}
. In this example, the name
string is
bound to the question mark, ?
, in the SQL query string. If there are
multiple arguments to the query string (where each argument in the SQL string is
represented by a question mark, ?
), you would provide an object array
with multiple arguments—for example,
Object[]{arg1,arg2,arg3,...}
.
The next most complicated case is where you expect the query to return multiple
values from a single row. In this case, you can use one of the
queryForMap()
methods to retrieve the contents of a single row. For
example, to retrieve the complete account details from a single customer:
// Java Map<String,Object> rowMap = jdbc.queryForMap( "select * from accounts where name = ?", new Object[]{name} );
Where the returned map object, rowMap
, contains one entry for each
column, using the column name as the key.
The most general case is where you expect the query to return multiple values from
multiple rows. In this case, you can use one of the queryForList()
methods to return the contents of multiple rows. For example, to return all of the
rows from the accounts
table:
// Java List<Map<String,Object> > rows = jdbc.queryForList( "select * from accounts" );
In some cases, a more convenient way of returning the table rows is to provide a
RowMapper
, which automatically converts each row to a Java object.
The return value of a query call would then be a list of Java objects. For example,
the contents of the accounts
table could be returned as follows:
// Java List<Account> accountList = jdbc.query( "select * from accounts", new Object[]{}, new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { Account acc = new Account(); acc.setName(rs.getString("name")); acc.setAmount(rs.getLong("amount")); return acc; } } );
Where each Account
object in the returned list encapsulates the
contents of a single row.
The JdbcTemplate update methods are used to perform INSERT
,
UPDATE
, or DELETE
operations on the database. The
update methods modify the database contents, but do not return any data from the
database (apart from an integer return value, which counts the number of rows
affected by the operation).
For example, the following update operation shows how to set the
amount
field in a customer's account:
// Java jdbc.update( "update accounts set amount = ? where name = ?", new Object[] {newAmount, name} );
For all other SQL operations, there is a general purpose execute()
method. For example, you would use this method to execute a create
table
statement, as follows:
// Java jdbc.execute("create table accounts (name varchar(50), amount int)");
To illustrate the database operations you can perform through the
JdbcTemplate
class, consider the account
service, which provides access to bank account data stored in a
database. It is assumed that the database is accessible through a JDBC data source
and the account service is implemented by an AccountService
class that
exposes the following methods:
credit()
—add a specific amount of money to a named account.debit()
—subtract a specific amount of money from a named account.
By combining credit and debit operations, it is possible to model money transfers, which can also be used to demonstrate key properties of transaction processing.
For the account service example, the money transfer orders have a simple XML format, as follows:
<transaction> <transfer> <sender>Major Clanger</sender> <receiver>Tiny Clanger</receiver> <amount>90</amount> </transfer> </transaction>
When this money transfer order is executed, the amount of money specified in the
amount
element is debited from the sender
account and
credited to the receiver
account.
Before we can start performing any queries on the database, the first thing we
need to do is to create an accounts
table and populate it with some
initial values. Example 4.1 shows the definition of the
CreateTable
class, which is responsible for intializing
the accounts
table.
Example 4.1. The CreateTable Class
// Java
package com.fusesource.demo.tx.jdbc.java;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
public class CreateTable {
private static Logger log = Logger.getLogger(CreateTable.class);
protected DataSource dataSource;
protected JdbcTemplate jdbc;
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public CreateTable(DataSource ds) {
log.info("CreateTable constructor called");
setDataSource(ds);
setUpTable();
}
public void setUpTable() {
log.info("About to set up table...");
jdbc = new JdbcTemplate(dataSource);
jdbc.execute("create table accounts (name varchar(50), amount int)");
jdbc.update("insert into accounts (name,amount) values (?,?)",
new Object[] {"Major Clanger", 2000}
);
jdbc.update("insert into accounts (name,amount) values (?,?)",
new Object[] {"Tiny Clanger", 100}
);
log.info("Table created");
}
}
Where the accounts
table consists of two columns: name
,
a string value that records the account holder's name, and amount
, a
long integer that records the amount of money in the account. Because this example
uses an ephemeral database, which exists only temporarily in memory, it is necessary
to re-initialize the database every time the example runs. A convenient way to
initialize the table is by instantiating a CreateTable
bean in the
Spring XML configuration, as follows:
<beans ...> <!-- 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> <!-- Bean to initialize table in the DB --> <bean id="createTable" class="com.fusesource.demo.tx.jdbc.java.CreateTable"> <constructor-arg ref="dataSource" /> </bean> ... </beans>
As soon as the createTable
bean is instantiated, the
accounts
table is ready for use. Note that a reference to the JDBC
data source, dataSource
, is passed to the CreateTable()
constructor, because the data source is needed to create a JdbcTemplate
instance.
Example 4.2 shows an outline of the
AccountService
class, not including
the service methods that access the database. The class expects to receive a data
source reference through dependency injection, which it then uses to create a
JdbcTemplate
instance.
Example 4.2. The AccountService class
package com.fusesource.demo.tx.jdbc.java;
import java.util.List;
import javax.sql.DataSource;
import org.apache.camel.Exchange;
import org.apache.camel.language.XPath;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
public class AccountService {
private static Logger log = Logger.getLogger(AccountService.class);
private JdbcTemplate jdbc;
public AccountService() {
}
public void setDataSource(DataSource ds) {
jdbc = new JdbcTemplate(ds);
}
...
// Service methods (see below)
...
}
You can conveniently instantiate an AccountService
bean in Spring
XML, using dependency injection to pass the data source reference, as
follows:
<beans ...> <!-- Bean for account service --> <bean id="accountService" class="com.fusesource.demo.tx.jdbc.java.AccountService"> <property name="dataSource" ref="dataSource"/> </bean> ... </beans>
The credit()
method adds the specified amount of money,
amount
, to the specified account, name
in the
accounts
database table, as follows:
public void credit( @XPath("/transaction/transfer/receiver/text()") String name, @XPath("/transaction/transfer/amount/text()") String amount ) { log.info("credit() called with args name = " + name + " and amount = " + amount); int origAmount = jdbc.queryForInt( "select amount from accounts where name = ?", new Object[]{name} ); int newAmount = origAmount + Integer.parseInt(amount); jdbc.update( "update accounts set amount = ? where name = ?", new Object[] {newAmount, name} ); }
For methods invoked using the For example, the first XPath expression,
| |
The | |
The |
The debit()
method subtracts the specified amount of money,
amount
, from the specified account, name
in the
accounts
database table, as follows:
public void debit( @XPath("/transaction/transfer/sender/text()") String name, @XPath("/transaction/transfer/amount/text()") String amount ) { log.info("debit() called with args name = " + name + " and amount = " + amount); int iamount = Integer.parseInt(amount); if (iamount > 100) { throw new IllegalArgumentException("Debit limit is 100"); } int origAmount = jdbc.queryForInt( "select amount from accounts where name = ?", new Object[]{name} ); int newAmount = origAmount - Integer.parseInt(amount); if (newAmount < 0) { throw new IllegalArgumentException("Not enough in account"); } jdbc.update( "update accounts set amount = ? where name = ?", new Object[] {newAmount, name} ); }
The parameters of the | |
There is a fixed debit limit of 100. Amounts greater than this will
trigger an | |
If the balance of the account would go below zero after debiting, abort
the transaction by calling the |
The dumpTable()
method is convenient for testing. It simply returns
the entire contents of the accounts
table as a string. It is
implemented as follows:
public void dumpTable(Exchange ex) { log.info("dump() called"); List<?> dump = jdbc.queryForList("select * from accounts"); ex.getIn().setBody(dump.toString()); }