Examples of JdbcTemplate class usage
This section provides some examples of JdbcTemplate
class usage. These examples are not an exhaustive list of all of the functionality exposed by the JdbcTemplate
; see the attendant javadocs for that.
Here is a simple query for getting the number of rows in a relation:
int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);
A simple query using a bind variable:
int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject( "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");
Querying for a String
:
String lastName = this.jdbcTemplate.queryForObject( "select last_name from t_actor where id = ?", new Object[]{1212L}, String.class);
Querying and populating a single domain object:
Actor actor = this.jdbcTemplate.queryForObject( "select first_name, last_name from t_actor where id = ?", new Object[]{1212L}, new RowMapper<Actor>() { public Actor mapRow(ResultSet rs, int rowNum) throws SQLException { Actor actor = new Actor(); actor.setFirstName(rs.getString("first_name")); actor.setLastName(rs.getString("last_name")); return actor; } });
Querying and populating a number of domain objects:
List<Actor> actors = this.jdbcTemplate.query( "select first_name, last_name from t_actor", new RowMapper<Actor>() { public Actor mapRow(ResultSet rs, int rowNum) throws SQLException { Actor actor = new Actor(); actor.setFirstName(rs.getString("first_name")); actor.setLastName(rs.getString("last_name")); return actor; } });
If the last two snippets of code actually existed in the same application, it would make sense to remove the duplication present in the two RowMapper
anonymous inner classes, and extract them out into a single class (typically a static
inner class) that can then be referenced by DAO methods as needed. For example, it may be better to write the last code snippet as follows:
public List<Actor> findAllActors() { return this.jdbcTemplate.query( "select first_name, last_name from t_actor", new ActorMapper()); } private static final class ActorMapper implements RowMapper<Actor> { public Actor mapRow(ResultSet rs, int rowNum) throws SQLException { Actor actor = new Actor(); actor.setFirstName(rs.getString("first_name")); actor.setLastName(rs.getString("last_name")); return actor; } }
You use the update(..)
method to perform insert, update and delete operations. Parameter values are usually provided as var args or alternatively as an object array.
this.jdbcTemplate.update(
"insert into t_actor (first_name, last_name) values (?, ?)",
"Leonor", "Watling");
this.jdbcTemplate.update(
"update t_actor set last_name = ? where id = ?",
"Banjo", 5276L);
this.jdbcTemplate.update(
"delete from actor where id = ?",
Long.valueOf(actorId));
You can use the execute(..)
method to execute any arbitrary SQL, and as such the method is often used for DDL statements. It is heavily overloaded with variants taking callback interfaces, binding variable arrays, and so on.
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
The following example invokes a simple stored procedure. More sophisticated stored procedure support is covered later.
this.jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
Long.valueOf(unionId));
Instances of the JdbcTemplate
class are threadsafe once configured. This is important because it means that you can configure a single instance of a JdbcTemplate
and then safely inject this shared reference into multiple DAOs (or repositories). The JdbcTemplate
is stateful, in that it maintains a reference to a DataSource
, but this state isnot conversational state.
A common practice when using the JdbcTemplate
class (and the associated NamedParameterJdbcTemplate
classes) is to configure a DataSource
in your Spring configuration file, and then dependency-inject that shared DataSource
bean into your DAO classes; the JdbcTemplate
is created in the setter for the DataSource
. This leads to DAOs that look in part like the following:
public class JdbcCorporateEventDao implements CorporateEventDao { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } // JDBC-backed implementations of the methods on the CorporateEventDao follow... }
The corresponding configuration might look like this.
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <context:property-placeholder location="jdbc.properties"/> </beans>
An alternative to explicit configuration is to use component-scanning and annotation support for dependency injection. In this case you annotate the class with @Repository
(which makes it a candidate for component-scanning) and annotate the DataSource
setter method with @Autowired
.
@Repository public class JdbcCorporateEventDao implements CorporateEventDao { private JdbcTemplate jdbcTemplate; @Autowired public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } // JDBC-backed implementations of the methods on the CorporateEventDao follow... }
The corresponding XML configuration file would look like the following:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- Scans within the base package of the application for @Component classes to configure as beans --> <context:component-scan base-package="org.springframework.docs.test" /> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <context:property-placeholder location="jdbc.properties"/> </beans>
If you are using Spring’s JdbcDaoSupport
class, and your various JDBC-backed DAO classes extend from it, then your sub-class inherits a setDataSource(..)
method from the JdbcDaoSupport
class. You can choose whether to inherit from this class. The JdbcDaoSupport
class is provided as a convenience only.
Regardless of which of the above template initialization styles you choose to use (or not), it is seldom necessary to create a new instance of a JdbcTemplate
class each time you want to execute SQL. Once configured, a JdbcTemplate
instance is threadsafe. You may want multiple JdbcTemplate
instances if your application accesses multiple databases, which requires multiple DataSources
, and subsequently multiple differently configured JdbcTemplates
.
NamedParameterJdbcTemplate
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(*) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
return this.namedParameterJdbcTemplate.queryForObject(sql, Integer.class, namedParameters);
}
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(*) from T_ACTOR where first_name = :first_name";
Map<String, String> namedParameters = Collections.singletonMap("first_name", firstName);
return this.namedParameterJdbcTemplate.queryForObject(sql, Integer.class, namedParameters);
}
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActors(Actor exampleActor) {
// notice how the named parameters match the properties of the above Actor class
String sql = "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
return this.namedParameterJdbcTemplate.queryForObject(sql, Integer.class, namedParameters);
}
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
// create a JdbcTemplate and set data source
this.jdbcTemplate = new JdbcTemplate();
this.jdbcTemplate.setDataSource(dataSource);
// create a custom translator and set the DataSource for the default translation lookup
CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator();
tr.setDataSource(dataSource);
this.jdbcTemplate.setExceptionTranslator(tr);
}
public void updateShippingCharge(long orderId, long pct) {
// use the prepared JdbcTemplate for this update
this.jdbcTemplate.update("update orders" +
" set shipping_charge = shipping_charge * ? / 100" +
" where id = ?", pct, orderId);
}
Executing statements
import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; public class ExecuteAStatement { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public void doExecute() { this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))"); } }
Running queries
Some query methods return a single value. To retrieve a count or a specific value from one row, use queryForObject(..)
. The latter converts the returned JDBC Type
to the Java class that is passed in as an argument. If the type conversion is invalid, then an InvalidDataAccessApiUsageException
is thrown. Here is an example that contains two query methods, one for an int
and one that queries for a String
.
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class RunAQuery {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int getCount() {
return this.jdbcTemplate.queryForObject("select count(*) from mytable", Integer.class);
}
public String getName() {
return this.jdbcTemplate.queryForObject("select name from mytable", String.class);
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}
In addition to the single result query methods, several methods return a list with an entry for each row that the query returned. The most generic method isqueryForList(..)
which returns a List
where each entry is a Map
with each entry in the map representing the column value for that row. If you add a method to the above example to retrieve a list of all the rows, it would look like this:
private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public List<Map<String, Object>> getList() { return this.jdbcTemplate.queryForList("select * from mytable"); }
The list returned would look something like this:
[{name=Bob, id=1}, {name=Mary, id=2}]
Updating the database
The following example shows a column updated for a certain primary key. In this example, an SQL statement has placeholders for row parameters. The parameter values can be passed in as varargs or alternatively as an array of objects. Thus primitives should be wrapped in the primitive wrapper classes explicitly or using auto-boxing.
import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; public class ExecuteAnUpdate { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public void setName(int id, String name) { this.jdbcTemplate.update("update mytable set name = ? where id = ?", name, id); } }
Retrieving auto-generated keys
An update()
convenience method supports the retrieval of primary keys generated by the database. This support is part of the JDBC 3.0 standard; see Chapter 13.6 of the specification for details. The method takes a PreparedStatementCreator
as its first argument, and this is the way the required insert statement is specified. The other argument is a KeyHolder
, which contains the generated key on successful return from the update. There is not a standard single way to create an appropriatePreparedStatement
(which explains why the method signature is the way it is). The following example works on Oracle but may not work on other platforms:
final String INSERT_SQL = "insert into my_test (name) values(?)"; final String name = "Rob"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update( new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] {"id"}); ps.setString(1, name); return ps; } }, keyHolder); // keyHolder.getKey() now contains the generated key
Controlling database connections
DataSource
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");
Here is the corresponding XML configuration:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<context:property-placeholder location="jdbc.properties"/>
The following examples show the basic connectivity and configuration for DBCP and C3P0. To learn about more options that help control the pooling features, see the product documentation for the respective connection pooling implementations.
DBCP configuration:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<context:property-placeholder location="jdbc.properties"/>
C3P0 configuration:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<context:property-placeholder location="jdbc.properties"/>