MySQL Connector/J 5.1 Developer Guide
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.
<bean id="dao" class="code.Ex2JdbcDao"> <property name="dataSource" ref="dataSource"/> </bean>
At this point, we can just grab a reference to the DAO from
Spring and call
getRandomCityByCountryCode()
.
// Create the application context ApplicationContext ctx = new ClassPathXmlApplicationContext("ex2appContext.xml"); // Obtain a reference to our DAO Ex2JdbcDao dao = (Ex2JdbcDao) ctx.getBean("dao"); String countryCode = "USA"; // Find a few random cities in the US for(int i = 0; i < 4; ++i) System.out.printf("A random city in %s is %s%n", countryCode, dao.getRandomCityByCountryCode(countryCode));
This example shows how to use Spring's JDBC classes to
completely abstract away the use of traditional JDBC classes
including Connection
and
PreparedStatement
.