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:

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:

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 8 shows the definition of the CreateTable class, which is responsible for intializing the accounts table.

// 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 9 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.

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}
        );
    }

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 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());
    }