JdbcTemplate
is the central class in the core JDBC package. It handles resource creation and release,
which helps avoid common mistakes such as forgetting to close a connection. It performs the basic tasks of the main
JDBC workflow (such as creating and executing statements), leaving the application code to provide SQL and retrieve
results. Class JdbcTemplate
:
Executes SQL queries
Updates statements and stored procedure calls
Iterates through
ResultSet
instances and extracts the return parameter values.Catches JDBC exceptions and converts them to a typed , a more descriptive exception hierarchy defined in the
org.springframework.dao
package. (See Consistent Hierarchy of Exceptions).
If you use the
JdbcTemplate
template for your code, you only need to implement the callback interfaces by providing
them with a well-defined contract. Given a
Connection
provided by a JdbcTemplate
class, the
PreparedStatementCreator
callback interface creates a compiled statement, providing the SQL and all
necessary parameters. The same is true for the
CallableStatementCreator
interface, which creates callable statements. The
RowCallbackHandler
interface retrieves the values from each row of the ResultSet
.
You
can use the JdbcTemplate
inside a DAO implementation by directly instantiating it with a reference to
DataSource
, or you can configure it in the Spring IoC container and pass it to the DAO as a bean
reference.
DataSource
should always be configured as a bean in the Spring
IoC container. In the first case, the bean is passed directly to the service; in the second case, it is passed to
the prepared template.
All SQL produced by this class is logged at the DEBUG
level in the category corresponding to the
fully qualified class name of the template instance (usually JdbcTemplate
, but it may be different if
you are using a custom subclass of JdbcTemplate
)
The following sections provide some examples of
using JdbcTemplate
. These examples are not an exhaustive list of all functionality provided by JdbcTemplate
.
See companion javadoc.
Building queries (SELECT
)
The following query allows you to get the number of rows in a relation:
int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);
val rowCount = jdbcTemplate.queryForObject<Int>("select count(*) from t_actor")!!
The following query uses a bind variable:
int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
"select count(*) from t_actor where first_name = ?", Integer.class, "Joe");
val countOfActorsNamedJoe = jdbcTemplate.queryForObject<Int>(
"select count(*) from t_actor where first_name = ?", arrayOf("Joe"))!!
The following query looks for String
:
String lastName = this.jdbcTemplate.queryForObject(
"select last_name from t_actor where id = ?",
String.class, 1212L);
val lastName = this.jdbcTemplate.queryForObject<String>(
"select last_name from t_actor where id = ?",
arrayOf(1212L))!!
The following query finds and populates one domain object:
Actor actor = jdbcTemplate.queryForObject(
"select first_name, last_name from t_actor where id = ?",
(resultSet, rowNum) -> {
Actor newActor = new Actor();
newActor.setFirstName(resultSet.getString("first_name"));
newActor.setLastName(resultSet.getString("last_name"));
return newActor;
},
1212L);
val actor = jdbcTemplate.queryForObject(
"select first_name, last_name from t_actor where id = ?",
arrayOf(1212L)) { rs, _ ->
Actor(rs.getString("first_name"), rs.getString("last_name"))
}
The following query finds and populates a list of domain objects:
List<Actor> actors = this.jdbcTemplate.query(
"select first_name, last_name from t_actor",
(resultSet, rowNum) -> {
Actor actor = new Actor();
actor.setFirstName(resultSet.getString("first_name"));
actor.setLastName(resultSet.getString("last_name"));
return actor;
});
val actors = jdbcTemplate.query("select first_name, last_name from t_actor") { rs, _ ->
Actor(rs.getString("first_name"), rs.getString("last_name"))
If the last two code snippets actually existed in the same application , then it would make sense to remove the
duplication present in the two RowMapper
lambda expressions and extract them into a single field,
which could then be referenced by DAO methods as needed. For example, it is better to write the previous code
fragment as follows:
private final RowMapper<Actor> actorRowMapper = (resultSet, rowNum) -> {
Actor actor = new Actor();
actor.setFirstName(resultSet.getString("first_name"));
actor.setLastName(resultSet.getString("last_name"));
return actor;
};
public List<Actor> findAllActors() {
return this.jdbcTemplate.query("select first_name, last_name from t_actor", actorRowMapper);
}
val actorMapper = RowMapper<Actor> { rs: ResultSet, rowNum: Int ->
Actor(rs.getString("first_name"), rs.getString("last_name"))
}
fun findAllActors(): List<Actor> {
return jdbcTemplate.query("select first_name, last_name from t_actor", actorMapper)
}
Update (INSERT
, UPDATE
and DELETE
) using JdbcTemplate
You can use the update(..)
method to perform insert, update, and delete operations. Parameter values
are typically provided as variable arguments or, alternatively, as an array of objects.
The following example shows how to insert a new entry:
this.jdbcTemplate.update(
"insert into t_actor (first_name, last_name) values (?, ?)",
"Leonor", "Watling");
jdbcTemplate.update(
"insert into t_actor (first_name, last_name) values (?, ?)",
"Leonor", "Watling")
The following example shows updating an existing record:
this.jdbcTemplate.update(
"update t_actor set last_name = ? where id = ?",
"Banjo", 5276L);
jdbcTemplate.update(
"update t_actor set last_name = ? where id = ?",
"Banjo", 5276L)
The following example shows how to delete an entry:
this.jdbcTemplate.update(
"delete from t_actor where id = ?",
Long.valueOf(actorId));
jdbcTemplate.update("delete from t_actor where id = ?, actorId.toLong())
Other operations JdbcTemplate
You can use the execute(..)
method to execute arbitrary SQL. Consequently, this method is often used
for statements in Data Definition Language (DDL). It is heavily loaded with options that accept callback
interfaces, bind arrays of variables, and so on. The following example creates a table:
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
jdbcTemplate.execute("create table mytable (id integer, name varchar(100))")
The following example calls the stored procedure:
this.jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
Long.valueOf(unionId));
jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
unionId.toLong())
Improved support for stored procedures described further.
Best practices for working with JdbcTemplate
After
configuration is complete, instances of the JdbcTemplate
class are thread safe. This is important
because it means that you can configure one JdbcTemplate
instance and then safely inject that shared
reference into multiple DAOs (or repositories). JdbcTemplate
is stateful because it stores a reference
to the DataSource
, but this state is not dialog state.
Best practice when using the JdbcTemplate
(and the associated class NamedParameterJdbcTemplate
) is the configuration of DataSource
in the Spring configuration file, and then injecting the
dependency of that common DataSource
bean into the DAO classes. JdbcTemplate
is created in
the setter for DataSource
. This results in a DAO that looks something like this:
public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// Implementations of CorporateEventDao methods with JDBC support follow...
}
class JdbcCorporateEventDao(dataSource: DataSource) : CorporateEventDao {
private val jdbcTemplate = JdbcTemplate(dataSource)
// JDBC-enabled implementations of CorporateEventDao methods follow...
}
The following example shows the corresponding XML configuration:
<?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
https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
https://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 bean scanning and support annotations for dependency
injection. In this case, you can mark the class with the @Repository
annotation (making it a candidate
for component scanning) and mark the DataSource
setter with the @Autowired
annotation. The
following example shows how to do this:
@Repository
public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-enabled implementations of CorporateEventDao methods follow...
}
- Annotate the class with
@Repository
. - Annotate the
DataSource
setter with@Autowired
. - Create a new
JdbcTemplate
usingDataSource
.
@Repository
class JdbcCorporateEventDao(dataSource: DataSource) : CorporateEventDao {
private val jdbcTemplate = JdbcTemplate(dataSource)
// Implementations of CorporateEventDao methods with support JDBC follows...
}
- Annotate the class with
@Repository
. - Constructor injection of the
DataSource
. - Create a new
JdbcTemplate
usingDataSource
.
The following example shows the corresponding XML configuration:
<?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
https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
https://www.springframework.org/schema/context/spring-context.xsd">
<!-- Search the base application package for classes with the @Component annotation 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 the JdbcDaoSupport
class from Spring and have various JDBC-enabled DAO classes
attached to it, then your subclass inherits the setDataSource(..)
method from the
JdbcDaoSupport
class. You can determine whether to inherit from this class. The
JdbcDaoSupport
class is provided for convenience only.
Regardless of which of the template
initialization styles described above you decide to use (or not to use), there is rarely a need to create a new
instance of the JdbcTemplate
whenever you need to execute SQL. Once configuration is complete,
the JdbcTemplate
instance is thread safe. If your application accesses multiple databases, then you may
need multiple JdbcTemplate
instances, which requires multiple DataSources
and,
accordingly, several differently configured instances of JdbcTemplate
.