13.1 Using JdbcTemplate

Spring makes extensive use of the Template method design pattern
(see
Template
Method Pattern). Our immediate focus will be on the
JdbcTemplate and related classes,
specifically NamedParameterJdbcTemplate. The
template classes handle obtaining and releasing a connection for
data access when one is needed.

The next example shows how to use
NamedParameterJdbcTemplate inside of a DAO
(Data Access Object) class to retrieve a random city given a
country code.

public class Ex2JdbcDao {
/**
* Data source reference which will be provided by Spring.
*/
private DataSource dataSource;
/**
* Our query to find a random city given a country code. Notice
* the ":country" parameter toward the end. This is called a
* named parameter.
*/
private String queryString = "select Name from City " +
"where CountryCode = :country order by rand() limit 1";
/**
* Retrieve a random city using Spring JDBC access classes.
*/
public String getRandomCityByCountryCode(String cntryCode) {
// A template that permits using queries with named parameters
NamedParameterJdbcTemplate template =
new NamedParameterJdbcTemplate(dataSource);
// A java.util.Map is used to provide values for the parameters
Map params = new HashMap();
params.put("country", cntryCode);
// We query for an Object and specify what class we are expecting
return (String)template.queryForObject(queryString, params, String.class);
}
/**
* A JavaBean setter-style method to allow Spring to inject the data source.
* @param dataSource
*/
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}

The focus in the above code is on the
getRandomCityByCountryCode() method. We
pass a country code and use the
NamedParameterJdbcTemplate to query for a
city. The country code is placed in a Map with the key
"country", which is the parameter is named in the SQL query.

To access this code, you need to configure it with Spring by
providing a reference to the data source.