Using JdbcTemplate

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.

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...
}

1. Annotate the class with @Repository.
2. Annotate the DataSource setter with @Autowired.
3. Create a new JdbcTemplate using DataSource.

@Repository 
class JdbcCorporateEventDao(dataSource: DataSource) : CorporateEventDao { 
    private val jdbcTemplate = JdbcTemplate(dataSource) 
    // Implementations of CorporateEventDao methods with support JDBC follows...
}

1. Annotate the class with @Repository .
2. Constructor injection of the DataSource.
3. Create a new JdbcTemplate using DataSource.

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.