SupportConsoleDevelopersStart a trialContact

4.2. Spring JDBC Template

Overview

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.

JdbcTemplate class

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

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.

Updating

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

Other SQL operations

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

Example application

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.

Format of money transfer orders

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.

CreateTable class

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, “The CreateTable Class” 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.

AccountService class

Example 4.2, “The AccountService class” 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>

AccountService.credit() method

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, 1
            @XPath("/transaction/transfer/amount/text()") String amount
            )
    {
        log.info("credit() called with args name = " + name + " and amount = " + amount);
        int origAmount = jdbc.queryForInt( 2
                "select amount from accounts where name = ?",
                new Object[]{name}
        );
        int newAmount = origAmount + Integer.parseInt(amount);
        
        jdbc.update( 3
                "update accounts set amount = ? where name = ?",
                new Object[] {newAmount, name}
        );
    }
1
For methods invoked using the beanRef() (or bean()) DSL command, Apache Camel provides a powerful set of annotations for binding the exchange to the method parameters. In this example, the parameters are annotated using the @XPath annotation, so that the result of the XPath expression is injected into the corresponding parameter.
For example, the first XPath expression, /transaction/transfer/receiver/text(), selects the contents of the receiver XML element from the body of the exchange's In message and injects them into the name parameter. Likewise, the contents of the amount element are injected into the amount parameter.
2
The JdbcTemplate.queryForInt() method returns the current balance of the name account. For details about using JdbcTemplate to make database queries, see the section called “Querying”.
3
The JdbcTemplate.update() method updates the balance of the name account, adding the specified amount of money. For details about using JdbcTemplate to make database updates, see the section called “Updating”.

AccountService.debit() method

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, 1
            @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) { 2
            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) { 3
            throw new IllegalArgumentException("Not enough in account");
        }
        
        jdbc.update(
                "update accounts set amount = ? where name = ?",
                new Object[] {newAmount, name}
        );
    }
1
The parameters of the debit() method are also bound to the exchange using annotations. In this case, however, the name of the account is bound to the sender XML element in the In message.
2
There is a fixed debit limit of 100. Amounts greater than this will trigger an IllegalArgument exception. This feature is useful, if you want to trigger a rollback to test a transaction example.
3
If the balance of the account would go below zero after debiting, abort the transaction by calling the IllegalArgumentException exception.

AccountService.dumpTable() method

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

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

We help Red Hat users innovate and achieve their goals with our products and services with content they can trust.

Making open source more inclusive

Red Hat is committed to replacing problematic language in our code, documentation, and web properties. For more details, see the Red Hat Blog.

About Red Hat

We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

© 2024 Red Hat, Inc.