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
.
GO TO FULL VERSION