This version is still in development and is not considered stable yet. For the latest stable version, please use Spring Framework 6.1.12! |
Using the JDBC Core Classes to Control Basic JDBC Processing and Error Handling
This section covers how to use the JDBC core classes to control basic JDBC processing, including error handling. It includes the following topics:
Using JdbcTemplate
JdbcTemplate
is the central class in the JDBC core package. It handles the
creation and release of resources, which helps you avoid common errors, such as
forgetting to close the connection. It performs the basic tasks of the core JDBC
workflow (such as statement creation and execution), leaving application code to provide
SQL and extract results. The JdbcTemplate
class:
-
Runs SQL queries
-
Updates statements and stored procedure calls
-
Performs iteration over
ResultSet
instances and extraction of returned parameter values. -
Catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the
org.springframework.dao
package. (See Consistent Exception Hierarchy.)
When you use the JdbcTemplate
for your code, you need only to implement callback
interfaces, giving them a clearly defined contract. Given a Connection
provided by the
JdbcTemplate
class, the PreparedStatementCreator
callback interface creates a prepared
statement, providing SQL and any necessary parameters. The same is true for the
CallableStatementCreator
interface, which creates callable statements. The
RowCallbackHandler
interface extracts values from each row of a ResultSet
.
You can use JdbcTemplate
within a DAO implementation through direct instantiation
with a DataSource
reference, or you can configure it in a Spring IoC container and give it to
DAOs as a bean reference.
The DataSource should always be configured as a bean in the Spring IoC container. In
the first case the bean is given to the service directly; in the second case it is given
to the prepared template.
|
All SQL issued by this class is logged at the DEBUG
level under the category
corresponding to the fully qualified class name of the template instance (typically
JdbcTemplate
, but it may be different if you use a custom subclass of the
JdbcTemplate
class).
The following sections provide some examples of JdbcTemplate
usage. These examples
are not an exhaustive list of all of the functionality exposed by the JdbcTemplate
.
See the attendant javadoc for that.
Querying (SELECT
)
The following query gets the number of rows in a relation:
-
Java
-
Kotlin
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:
-
Java
-
Kotlin
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 a String
:
-
Java
-
Kotlin
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 a single domain object:
-
Java
-
Kotlin
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:
-
Java
-
Kotlin
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 snippets of code actually existed in the same application, it would make
sense to remove the duplication present in the two RowMapper
lambda expressions and
extract them out into a single field that could then be referenced by DAO methods as needed.
For example, it may be better to write the preceding code snippet as follows:
-
Java
-
Kotlin
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)
}
Updating (INSERT
, UPDATE
, and DELETE
) with JdbcTemplate
You can use the update(..)
method to perform insert, update, and delete operations.
Parameter values are usually provided as variable arguments or, alternatively, as an object array.
The following example inserts a new entry:
-
Java
-
Kotlin
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 updates an existing entry:
-
Java
-
Kotlin
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 deletes an entry:
-
Java
-
Kotlin
this.jdbcTemplate.update(
"delete from t_actor where id = ?",
Long.valueOf(actorId));
jdbcTemplate.update("delete from t_actor where id = ?", actorId.toLong())
Other JdbcTemplate
Operations
You can use the execute(..)
method to run any arbitrary SQL. Consequently, the
method is often used for DDL statements. It is heavily overloaded with variants that take
callback interfaces, binding variable arrays, and so on. The following example creates a
table:
-
Java
-
Kotlin
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
jdbcTemplate.execute("create table mytable (id integer, name varchar(100))")
The following example invokes a stored procedure:
-
Java
-
Kotlin
this.jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
Long.valueOf(unionId));
jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
unionId.toLong())
More sophisticated stored procedure support is covered later.
JdbcTemplate
Best Practices
Instances of the JdbcTemplate
class are thread-safe, 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 is not conversational state.
A common practice when using the JdbcTemplate
class (and the associated
NamedParameterJdbcTemplate
class) 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
or in the constructor. This leads to DAOs that resemble the following:
-
Java
-
Kotlin
public class JdbcCorporateEventDao implements CorporateEventDao {
private final JdbcTemplate jdbcTemplate;
public JdbcCorporateEventDao(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-backed implementations of the methods on the CorporateEventDao follow...
}
class JdbcCorporateEventDao(dataSource: DataSource): CorporateEventDao {
private val jdbcTemplate = JdbcTemplate(dataSource)
// JDBC-backed implementations of the methods on the CorporateEventDao follow...
}
The following example shows the corresponding configuration:
-
Java
-
Kotlin
-
Xml
@Bean
JdbcCorporateEventDao corporateEventDao(DataSource dataSource) {
return new JdbcCorporateEventDao(dataSource);
}
@Bean(destroyMethod = "close")
BasicDataSource dataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");
return dataSource;
}
@Bean
fun corporateEventDao(dataSource: DataSource) = JdbcCorporateEventDao(dataSource)
@Bean(destroyMethod = "close")
fun dataSource() = BasicDataSource().apply {
driverClassName = "org.hsqldb.jdbcDriver"
url = "jdbc:hsqldb:hsql://localhost:"
username = "sa"
password = ""
}
<bean id="corporateEventDao" class="org.example.jdbc.JdbcCorporateEventDao">
<constructor-arg ref="dataSource"/>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp2.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"/>
An alternative to explicit configuration is to use component-scanning and annotation
support for dependency injection. In this case, you can annotate the class with @Repository
(which makes it a candidate for component-scanning). The following example shows how to do so:
@Repository
public class JdbcCorporateEventRepository implements CorporateEventRepository {
private JdbcTemplate jdbcTemplate;
// Implicitly autowire the DataSource constructor parameter
public JdbcCorporateEventRepository(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-backed implementations of the methods on the CorporateEventRepository follow...
}
The following example shows the corresponding configuration:
-
Java
-
Kotlin
-
Xml
@Configuration
@ComponentScan("org.example.jdbc")
public class JdbcCorporateEventRepositoryConfiguration {
@Bean(destroyMethod = "close")
BasicDataSource dataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");
return dataSource;
}
}
@Configuration
@ComponentScan("org.example.jdbc")
class JdbcCorporateEventRepositoryConfiguration {
@Bean(destroyMethod = "close")
fun dataSource() = BasicDataSource().apply {
driverClassName = "org.hsqldb.jdbcDriver"
url = "jdbc:hsqldb:hsql://localhost:"
username = "sa"
password = ""
}
}
<!-- Scans within the base package of the application for @Component classes to configure as beans -->
<context:component-scan base-package="org.example.jdbc" />
<bean id="dataSource" class="org.apache.commons.dbcp2.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"/>
If you use Spring’s JdbcDaoSupport
class and your various JDBC-backed DAO classes
extend from it, 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 run SQL. Once configured, a JdbcTemplate
instance is thread-safe.
If your application accesses multiple databases, you may want multiple JdbcTemplate
instances, which requires multiple DataSources
and, subsequently, multiple differently
configured JdbcTemplate
instances.
Using NamedParameterJdbcTemplate
The NamedParameterJdbcTemplate
class adds support for programming JDBC statements
by using named parameters, as opposed to programming JDBC statements using only classic
placeholder ( '?'
) arguments. The NamedParameterJdbcTemplate
class wraps a
JdbcTemplate
and delegates to the wrapped JdbcTemplate
to do much of its work. This
section describes only those areas of the NamedParameterJdbcTemplate
class that differ
from the JdbcTemplate
itself — namely, programming JDBC statements by using named
parameters. The following example shows how to use NamedParameterJdbcTemplate
:
-
Java
-
Kotlin
// 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, namedParameters, Integer.class);
}
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
fun countOfActorsByFirstName(firstName: String): Int {
val sql = "select count(*) from t_actor where first_name = :first_name"
val namedParameters = MapSqlParameterSource("first_name", firstName)
return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}
Notice the use of the named parameter notation in the value assigned to the sql
variable and the corresponding value that is plugged into the namedParameters
variable (of type MapSqlParameterSource
).
Alternatively, you can pass along named parameters and their corresponding values to a
NamedParameterJdbcTemplate
instance by using the Map
-based style. The remaining
methods exposed by the NamedParameterJdbcOperations
and implemented by the
NamedParameterJdbcTemplate
class follow a similar pattern and are not covered here.
The following example shows the use of the Map
-based style:
-
Java
-
Kotlin
// 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, namedParameters, Integer.class);
}
// some JDBC-backed DAO class...
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
fun countOfActorsByFirstName(firstName: String): Int {
val sql = "select count(*) from t_actor where first_name = :first_name"
val namedParameters = mapOf("first_name" to firstName)
return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}
One nice feature related to the NamedParameterJdbcTemplate
(and existing in the same
Java package) is the SqlParameterSource
interface. You have already seen an example of
an implementation of this interface in one of the previous code snippets (the
MapSqlParameterSource
class). An SqlParameterSource
is a source of named parameter
values to a NamedParameterJdbcTemplate
. The MapSqlParameterSource
class is a
simple implementation that is an adapter around a java.util.Map
, where the keys
are the parameter names and the values are the parameter values.
Another SqlParameterSource
implementation is the BeanPropertySqlParameterSource
class. This class wraps an arbitrary JavaBean (that is, an instance of a class that
adheres to the
JavaBean conventions) and uses the properties of the wrapped JavaBean as the source
of named parameter values.
The following example shows a typical JavaBean:
-
Java
-
Kotlin
public class Actor {
private Long id;
private String firstName;
private String lastName;
public String getFirstName() {
return this.firstName;
}
public String getLastName() {
return this.lastName;
}
public Long getId() {
return this.id;
}
// setters omitted...
}
data class Actor(val id: Long, val firstName: String, val lastName: String)
The following example uses a NamedParameterJdbcTemplate
to return the count of the
members of the class shown in the preceding example:
-
Java
-
Kotlin
// 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, namedParameters, Integer.class);
}
// some JDBC-backed DAO class...
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
fun countOfActors(exampleActor: Actor): Int {
// notice how the named parameters match the properties of the above 'Actor' class
val sql = "select count(*) from t_actor where first_name = :firstName and last_name = :lastName"
val namedParameters = BeanPropertySqlParameterSource(exampleActor)
return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}
Remember that the NamedParameterJdbcTemplate
class wraps a classic JdbcTemplate
template. If you need access to the wrapped JdbcTemplate
instance to access
functionality that is present only in the JdbcTemplate
class, you can use the
getJdbcOperations()
method to access the wrapped JdbcTemplate
through the
JdbcOperations
interface.
See also JdbcTemplate
Best Practices
for guidelines on using the NamedParameterJdbcTemplate
class in the context of an application.
Unified JDBC Query/Update Operations: JdbcClient
As of 6.1, the named parameter statements of NamedParameterJdbcTemplate
and the positional
parameter statements of a regular JdbcTemplate
are available through a unified client API
with a fluent interaction model.
For example, with positional parameters:
private JdbcClient jdbcClient = JdbcClient.create(dataSource);
public int countOfActorsByFirstName(String firstName) {
return this.jdbcClient.sql("select count(*) from t_actor where first_name = ?")
.param(firstName)
.query(Integer.class).single();
}
For example, with named parameters:
private JdbcClient jdbcClient = JdbcClient.create(dataSource);
public int countOfActorsByFirstName(String firstName) {
return this.jdbcClient.sql("select count(*) from t_actor where first_name = :firstName")
.param("firstName", firstName)
.query(Integer.class).single();
}
RowMapper
capabilities are available as well, with flexible result resolution:
List<Actor> actors = this.jdbcClient.sql("select first_name, last_name from t_actor")
.query((rs, rowNum) -> new Actor(rs.getString("first_name"), rs.getString("last_name")))
.list();
Instead of a custom RowMapper
, you may also specify a class to map to.
For example, assuming that Actor
has firstName
and lastName
properties
as a record class, a custom constructor, bean properties, or plain fields:
List<Actor> actors = this.jdbcClient.sql("select first_name, last_name from t_actor")
.query(Actor.class)
.list();
With a required single object result:
Actor actor = this.jdbcClient.sql("select first_name, last_name from t_actor where id = ?")
.param(1212L)
.query(Actor.class)
.single();
With a java.util.Optional
result:
Optional<Actor> actor = this.jdbcClient.sql("select first_name, last_name from t_actor where id = ?")
.param(1212L)
.query(Actor.class)
.optional();
And for an update statement:
this.jdbcClient.sql("insert into t_actor (first_name, last_name) values (?, ?)")
.param("Leonor").param("Watling")
.update();
Or an update statement with named parameters:
this.jdbcClient.sql("insert into t_actor (first_name, last_name) values (:firstName, :lastName)")
.param("firstName", "Leonor").param("lastName", "Watling")
.update();
Instead of individual named parameters, you may also specify a parameter source object –
for example, a record class, a class with bean properties, or a plain field holder which
provides firstName
and lastName
properties, such as the Actor
class from above:
this.jdbcClient.sql("insert into t_actor (first_name, last_name) values (:firstName, :lastName)")
.paramSource(new Actor("Leonor", "Watling")
.update();
The automatic Actor
class mapping for parameters as well as the query results above is
provided through implicit SimplePropertySqlParameterSource
and SimplePropertyRowMapper
strategies which are also available for direct use. They can serve as a common replacement
for BeanPropertySqlParameterSource
and BeanPropertyRowMapper
/DataClassRowMapper
,
also with JdbcTemplate
and NamedParameterJdbcTemplate
themselves.
JdbcClient is a flexible but simplified facade for JDBC query/update statements.
Advanced capabilities such as batch inserts and stored procedure calls typically require
extra customization: consider Spring’s SimpleJdbcInsert and SimpleJdbcCall classes or
plain direct JdbcTemplate usage for any such capabilities not available in JdbcClient .
|
Using SQLExceptionTranslator
SQLExceptionTranslator
is an interface to be implemented by classes that can translate
between SQLException
s and Spring’s own org.springframework.dao.DataAccessException
,
which is agnostic in regard to data access strategy. Implementations can be generic (for
example, using SQLState codes for JDBC) or proprietary (for example, using Oracle error
codes) for greater precision. This exception translation mechanism is used behind the
common JdbcTemplate
and JdbcTransactionManager
entry points which do not
propagate SQLException
but rather DataAccessException
.
As of 6.0, the default exception translator is SQLExceptionSubclassTranslator ,
detecting JDBC 4 SQLException subclasses with a few extra checks, and with a fallback
to SQLState introspection through SQLStateSQLExceptionTranslator . This is usually
sufficient for common database access and does not require vendor-specific detection.
For backwards compatibility, consider using SQLErrorCodeSQLExceptionTranslator as
described below, potentially with custom error code mappings.
|
SQLErrorCodeSQLExceptionTranslator
is the implementation of SQLExceptionTranslator
that is used by default when a file named sql-error-codes.xml
is present in the root
of the classpath. This implementation uses specific vendor codes. It is more precise than
SQLState
or SQLException
subclass translation. The error code translations are based
on codes held in a JavaBean type class called SQLErrorCodes
. This class is created and
populated by an SQLErrorCodesFactory
, which (as the name suggests) is a factory for
creating SQLErrorCodes
based on the contents of a configuration file named
sql-error-codes.xml
. This file is populated with vendor codes and based on the
DatabaseProductName
taken from DatabaseMetaData
. The codes for the actual
database you are using are used.
The SQLErrorCodeSQLExceptionTranslator
applies matching rules in the following sequence:
-
Any custom translation implemented by a subclass. Normally, the provided concrete
SQLErrorCodeSQLExceptionTranslator
is used, so this rule does not apply. It applies only if you have actually provided a subclass implementation. -
Any custom implementation of the
SQLExceptionTranslator
interface that is provided as thecustomSqlExceptionTranslator
property of theSQLErrorCodes
class. -
The list of instances of the
CustomSQLErrorCodesTranslation
class (provided for thecustomTranslations
property of theSQLErrorCodes
class) are searched for a match. -
Error code matching is applied.
-
Use the fallback translator.
SQLExceptionSubclassTranslator
is the default fallback translator. If this translation is not available, the next fallback translator is theSQLStateSQLExceptionTranslator
.
The SQLErrorCodesFactory is used by default to define error codes and custom
exception translations. They are looked up in a file named sql-error-codes.xml from the
classpath, and the matching SQLErrorCodes instance is located based on the database
name from the database metadata of the database in use.
|
You can extend SQLErrorCodeSQLExceptionTranslator
, as the following example shows:
-
Java
-
Kotlin
public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {
protected DataAccessException customTranslate(String task, String sql, SQLException sqlEx) {
if (sqlEx.getErrorCode() == -12345) {
return new DeadlockLoserDataAccessException(task, sqlEx);
}
return null;
}
}
class CustomSQLErrorCodesTranslator : SQLErrorCodeSQLExceptionTranslator() {
override fun customTranslate(task: String, sql: String?, sqlEx: SQLException): DataAccessException? {
if (sqlEx.errorCode == -12345) {
return DeadlockLoserDataAccessException(task, sqlEx)
}
return null
}
}
In the preceding example, the specific error code (-12345
) is translated while
other errors are left to be translated by the default translator implementation.
To use this custom translator, you must pass it to the JdbcTemplate
through the
method setExceptionTranslator
, and you must use this JdbcTemplate
for all of the
data access processing where this translator is needed. The following example shows
how you can use this custom translator:
-
Java
-
Kotlin
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);
}
// create a JdbcTemplate and set data source
private val jdbcTemplate = JdbcTemplate(dataSource).apply {
// create a custom translator and set the DataSource for the default translation lookup
exceptionTranslator = CustomSQLErrorCodesTranslator().apply {
this.dataSource = dataSource
}
}
fun updateShippingCharge(orderId: Long, pct: Long) {
// use the prepared JdbcTemplate for this update
this.jdbcTemplate!!.update("update orders" +
" set shipping_charge = shipping_charge * ? / 100" +
" where id = ?", pct, orderId)
}
The custom translator is passed a data source in order to look up the error codes in
sql-error-codes.xml
.
Running Statements
Running an SQL statement requires very little code. You need a DataSource
and a
JdbcTemplate
, including the convenience methods that are provided with the
JdbcTemplate
. The following example shows what you need to include for a minimal but
fully functional class that creates a new table:
-
Java
-
Kotlin
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))");
}
}
import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class ExecuteAStatement(dataSource: DataSource) {
private val jdbcTemplate = JdbcTemplate(dataSource)
fun doExecute() {
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, an
InvalidDataAccessApiUsageException
is thrown. The following example contains two
query methods, one for an int
and one that queries for a String
:
-
Java
-
Kotlin
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);
}
}
import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class RunAQuery(dataSource: DataSource) {
private val jdbcTemplate = JdbcTemplate(dataSource)
val count: Int
get() = jdbcTemplate.queryForObject("select count(*) from mytable")!!
val name: String?
get() = jdbcTemplate.queryForObject("select name from mytable")
}
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 is queryForList(..)
,
which returns a List
where each element is a Map
containing one entry for each column,
using the column name as the key. If you add a method to the preceding example to retrieve a
list of all the rows, it might be as follows:
-
Java
-
Kotlin
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");
}
private val jdbcTemplate = JdbcTemplate(dataSource)
fun getList(): List<Map<String, Any>> {
return jdbcTemplate.queryForList("select * from mytable")
}
The returned list would resemble the following:
[{name=Bob, id=1}, {name=Mary, id=2}]
Updating the Database
The following example updates a column for a certain primary key:
-
Java
-
Kotlin
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);
}
}
import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class ExecuteAnUpdate(dataSource: DataSource) {
private val jdbcTemplate = JdbcTemplate(dataSource)
fun setName(id: Int, name: String) {
jdbcTemplate.update("update mytable set name = ? where id = ?", name, id)
}
}
In the preceding example, an SQL statement has placeholders for row parameters. You can pass the parameter values in as varargs or, alternatively, as an array of objects. Thus, you should explicitly wrap primitives in the primitive wrapper classes, or you should use auto-boxing.
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 no standard single way to create an appropriate PreparedStatement
(which explains why the method signature is the way it is). The following example works
on Oracle but may not work on other platforms:
-
Java
-
Kotlin
final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "id" });
ps.setString(1, name);
return ps;
}, keyHolder);
// keyHolder.getKey() now contains the generated key
val INSERT_SQL = "insert into my_test (name) values(?)"
val name = "Rob"
val keyHolder = GeneratedKeyHolder()
jdbcTemplate.update({
it.prepareStatement (INSERT_SQL, arrayOf("id")).apply { setString(1, name) }
}, keyHolder)
// keyHolder.getKey() now contains the generated key