3. 使用 JDBC 进行数据访问

预计阅读时间: 134 分钟

The value provided by the Spring Framework JDBC abstraction is perhaps best shown by the sequence of actions outlined in the following table below. The table shows which actions Spring takes care of and which actions are your responsibility.
Spring 框架提供的 JDBC 抽象层价值，或许可以通过下表所示的操作序列最好地体现。该表显示了 Spring 负责哪些操作，以及哪些操作是您的责任。

Table 4. Spring JDBC - who does what?
表 4. Spring JDBC - 谁做什么？

Action 行动

Spring 春天

You 您

Define connection parameters.
定义连接参数。

Open the connection. 打开连接。

Specify the SQL statement.
指定 SQL 语句。

Declare parameters and provide parameter values
声明参数并提供参数值

Prepare and run the statement.
准备并运行该语句。

Set up the loop to iterate through the results (if any).
设置循环以遍历结果（如果有）。

Do the work for each iteration.
为每次迭代做工作。

Process any exception. 处理任何异常。

Handle transactions. 处理交易。

Close the connection, the statement, and the resultset.
关闭连接、语句和结果集。

The Spring Framework takes care of all the low-level details that can make JDBC such a tedious API.
Spring 框架负责处理所有可能使 JDBC 成为一个繁琐 API 的低级细节。

(#jdbc-choose-style)3.1. Choosing an Approach for JDBC Database Access

3.1. 选择 JDBC 数据库访问的方法

You can choose among several approaches to form the basis for your JDBC database access. In addition to three flavors of JdbcTemplate, a new SimpleJdbcInsert and SimpleJdbcCall approach optimizes database metadata, and the RDBMS Object style takes a more object-oriented approach similar to that of JDO Query design. Once you start using one of these approaches, you can still mix and match to include a feature from a different approach.
您可以选择几种方法作为您 JDBC 数据库访问的基础。除了三种 JdbcTemplate 口味外，一种新的 SimpleJdbcInsert 和 SimpleJdbcCall 方法优化了数据库元数据，而 RDBMS 对象风格采用了一种类似于 JDO 查询设计的面向对象的方法。一旦您开始使用这些方法之一，您仍然可以混合搭配，以包含来自不同方法的功能。
All approaches require a JDBC 2.0-compliant driver, and some advanced features require a JDBC 3.0 driver.
所有方法都需要符合 JDBC 2.0 规范的驱动程序，并且一些高级功能需要 JDBC 3.0 驱动程序。

JdbcTemplate is the classic and most popular Spring JDBC approach. This “lowest-level” approach and all others use a JdbcTemplate under the covers.
JdbcTemplate 是经典的、最受欢迎的 Spring JDBC 方法。这种“最低级别”的方法以及所有其他方法都使用 JdbcTemplate 作为底层实现。
NamedParameterJdbcTemplate wraps a JdbcTemplate to provide named parameters instead of the traditional JDBC ? placeholders. This approach provides better documentation and ease of use when you have multiple parameters for an SQL statement.
NamedParameterJdbcTemplate 将 JdbcTemplate 包装起来，以提供命名参数而不是传统的 JDBC ? 占位符。这种方法在您有多个参数用于 SQL 语句时提供了更好的文档和易用性。
SimpleJdbcInsert and SimpleJdbcCall optimize database metadata to limit the amount of necessary configuration. This approach simplifies coding so that you need to provide only the name of the table or procedure and provide a map of parameters matching the column names.
SimpleJdbcInsert 和 SimpleJdbcCall 优化数据库元数据以限制必要的配置量。这种方法简化了编码，您只需提供表或存储过程名称，并提供与列名匹配的参数映射。
This works only if the database provides adequate metadata. If the database does not provide this metadata, you have to provide explicit configuration of the parameters.
这仅在数据库提供足够的元数据时才有效。如果数据库不提供这些元数据，您必须提供参数的显式配置。
RDBMS objects — including MappingSqlQuery, SqlUpdate, and StoredProcedure — require you to create reusable and thread-safe objects during initialization of your data-access layer. This approach is modeled after JDO Query, wherein you define your query string, declare parameters, and compile the query. Once you do that, execute(…), update(…), and findObject(…) methods can be called multiple times with various parameter values.
RDBMS 对象（包括 MappingSqlQuery 、 SqlUpdate 和 StoredProcedure ）要求你在数据访问层初始化时创建可重用和线程安全的对象。这种方法模仿了 JDO 查询，其中你定义查询字符串、声明参数并编译查询。一旦完成这些操作， execute(…) 、 update(…) 和 findObject(…) 方法就可以多次调用，并使用不同的参数值。

(#jdbc-packages)3.2. Package Hierarchy 3.2. 包层次

The Spring Framework’s JDBC abstraction framework consists of four different packages:
Spring 框架的 JDBC 抽象框架包括四个不同的包：

core: The org.springframework.jdbc.core package contains the JdbcTemplate class and its various callback interfaces, plus a variety of related classes. A subpackage named org.springframework.jdbc.core.simple contains the SimpleJdbcInsert and SimpleJdbcCall classes. Another subpackage named org.springframework.jdbc.core.namedparam contains the NamedParameterJdbcTemplate class and the related support classes. See Using the JDBC Core Classes to Control Basic JDBC Processing and Error Handling, JDBC Batch Operations, and Simplifying JDBC Operations with the SimpleJdbc Classes.
core : 该 org.springframework.jdbc.core 包含了 JdbcTemplate 类及其各种回调接口，以及各种相关类。一个名为 org.springframework.jdbc.core.simple 的子包包含了 SimpleJdbcInsert 和 SimpleJdbcCall 类。另一个名为 org.springframework.jdbc.core.namedparam 的子包包含了 NamedParameterJdbcTemplate 类和相关支持类。请参阅《使用 JDBC 核心类控制基本 JDBC 处理和错误处理》、《JDBC 批处理操作》以及《使用 SimpleJdbc 类简化 JDBC 操作》。
datasource: The org.springframework.jdbc.datasource package contains a utility class for easy DataSource access and various simple DataSource implementations that you can use for testing and running unmodified JDBC code outside of a Java EE container. A subpackage named org.springframework.jdbc.datasource.embedded provides support for creating embedded databases by using Java database engines, such as HSQL, H2, and Derby. See Controlling Database Connections and Embedded Database Support.
datasource : 该 org.springframework.jdbc.datasource 包包含一个用于轻松 DataSource 访问的实用类以及各种简单的 DataSource 实现，您可以使用这些实现进行测试和运行未经修改的 JDBC 代码，而不在 Java EE 容器中。一个名为 org.springframework.jdbc.datasource.embedded 的子包提供了使用 Java 数据库引擎（如 HSQL、H2 和 Derby）创建嵌入式数据库的支持。请参阅“控制数据库连接”和“嵌入式数据库支持”。
object: The org.springframework.jdbc.object package contains classes that represent RDBMS queries, updates, and stored procedures as thread-safe, reusable objects. See Modeling JDBC Operations as Java Objects. This approach is modeled by JDO, although objects returned by queries are naturally disconnected from the database. This higher-level of JDBC abstraction depends on the lower-level abstraction in the org.springframework.jdbc.core package.
object : org.springframework.jdbc.object 包含代表 RDBMS 查询、更新和存储过程的线程安全、可重用对象。参见将 JDBC 操作建模为 Java 对象。这种方法由 JDO 模型，尽管查询返回的对象自然与数据库断开连接。这种 JDBC 的高级抽象依赖于 org.springframework.jdbc.core 包中的低级抽象。
support: The org.springframework.jdbc.support package provides SQLException translation functionality and some utility classes. Exceptions thrown during JDBC processing are translated to exceptions defined in the org.springframework.dao package. This means that code using the Spring JDBC abstraction layer does not need to implement JDBC or RDBMS-specific error handling.
support : The org.springframework.jdbc.support package provides SQLException translation functionality and some utility classes. Exceptions thrown during JDBC processing are translated to exceptions defined in the org.springframework.dao package. This means that code using the Spring JDBC abstraction layer does not need to implement JDBC or RDBMS-specific error handling. support : 该 org.springframework.jdbc.support 包提供 SQLException 翻译功能和一些实用类。在 JDBC 处理过程中抛出的异常被转换为 org.springframework.dao 包中定义的异常。这意味着使用 Spring JDBC 抽象层的代码不需要实现 JDBC 或 RDBMS 特定的错误处理。
All translated exceptions are unchecked, which gives you the option of catching the exceptions from which you can recover while letting other exceptions be propagated to the caller. See Using SQLExceptionTranslator.
所有已翻译的异常未被检查，这为您提供了从其中恢复的选项，同时允许其他异常传播给调用者。请参阅使用 SQLExceptionTranslator 。

(#jdbc-core)3.3. Using the JDBC Core Classes to Control Basic JDBC Processing and Error Handling

3.3. 使用 JDBC 核心类控制基本 JDBC 处理和错误处理

This section covers how to use the JDBC core classes to control basic JDBC processing, including error handling. It includes the following topics:
本节介绍如何使用 JDBC 核心类来控制基本的 JDBC 处理，包括错误处理。它包括以下主题：

Using JdbcTemplate 使用 JdbcTemplate
Using NamedParameterJdbcTemplate 使用 NamedParameterJdbcTemplate
Using SQLExceptionTranslator 使用 SQLExceptionTranslator
Running Statements 运行语句
Running Queries 运行查询
Updating the Database 更新数据库
Retrieving Auto-generated Keys
检索自动生成的密钥

(#jdbc-JdbcTemplate)3.3.1. Using`JdbcTemplate` 3.3.1. 使用`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.
JdbcTemplate 是 JDBC 核心包中的中心类。它处理资源的创建和释放，有助于您避免常见的错误，例如忘记关闭连接。
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:
它执行核心 JDBC 工作流程的基本任务（如创建和执行语句），将 SQL 提供和结果提取的任务留给应用程序代码。 JdbcTemplate 类：

Runs SQL queries 运行 SQL 查询
Updates statements and stored procedure calls
Performs iteration over ResultSet instances and extraction of returned parameter values.
执行对 ResultSet 实例的迭代和返回参数值的提取。
Catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package. (See Consistent Exception Hierarchy.)
捕获 JDBC 异常并将其转换为 org.springframework.dao 包中定义的通用、更详细的异常层次结构。（见一致的异常层次结构。）

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.
当您在代码中使用 JdbcTemplate 时，只需实现回调接口，并给出一个明确定义的合约。给定由 JdbcTemplate 类提供的 Connection ， PreparedStatementCreator 回调接口创建一个预处理语句，提供 SQL 和任何必要的参数。对于 CallableStatementCreator 接口，它创建可调用语句也是如此。 RowCallbackHandler 接口从 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.
您可以在 DAO 实现中使用 JdbcTemplate ，通过直接使用 DataSource 引用进行实例化，或者将其配置在 Spring IoC 容器中，并将其作为 bean 引用提供给 DAO。

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.
DataSource 应始终在 Spring IoC 容器中配置为 Bean。在第一种情况下，Bean 直接传递给服务；在第二种情况下，传递给准备好的模板。

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).
所有由此类发出的 SQL 都在对应模板实例完全限定类名的类别下以 DEBUG 级别进行记录（通常是 JdbcTemplate ，但如果您使用了 JdbcTemplate 类的自定义子类，则可能不同）。

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.
以下部分提供了一些 JdbcTemplate 的使用示例。这些示例并不是 JdbcTemplate 所暴露的所有功能的完整列表。有关详细信息，请参阅相关的 javadoc。

(#jdbc-JdbcTemplate-examples-query)Querying (`SELECT`) 查询（`SELECT` ）

The following query gets 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 a String:
以下查询寻找一个 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 a single 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 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:
如果最后两个代码片段实际上存在于同一个应用程序中，那么从两个 RowMapper lambda 表达式中的重复部分中提取出来，并将它们合并成一个单独的字段，以便随后由 DAO 方法按需引用，是有意义的。例如，前面的代码片段可以写成如下所示：

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

(#jdbc-JdbcTemplate-examples-update)Updating (`INSERT`,`UPDATE`, and`DELETE`) with`JdbcTemplate`

更新（ INSERT ， UPDATE ，和 DELETE ）为 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.
您可以使用 update(..) 方法执行插入、更新和删除操作。参数值通常以可变参数或作为对象数组的形式提供。

The following example inserts 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 updates an existing entry:
以下示例更新现有条目：

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:
以下示例删除一个条目：

this.jdbcTemplate.update(
        "delete from t_actor where id = ?",
        Long.valueOf(actorId));

jdbcTemplate.update("delete from t_actor where id = ?", actorId.toLong())

(#jdbc-JdbcTemplate-examples-other)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:
您可以使用 execute(..) 方法运行任何任意的 SQL。因此，该方法常用于 DDL 语句。它包含了许多变体，包括接受回调接口、绑定变量数组等。以下示例创建了一个表：

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:
以下示例调用了一个存储过程：

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.
更高级的存储过程支持将在后面介绍。

(#jdbc-JdbcTemplate-idioms)`JdbcTemplate` Best Practices`JdbcTemplate` 最佳实践

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.
该 JdbcTemplate 类的实例一旦配置即线程安全。这很重要，因为它意味着您可以配置一个 JdbcTemplate 的单个实例，然后将这个共享引用安全地注入到多个 DAO（或存储库）中。 JdbcTemplate 是有状态的，因为它维护了对 DataSource 的引用，但这种状态不是对话状态。

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. This leads to DAOs that resemble the following:
使用 JdbcTemplate 类（及其相关的 NamedParameterJdbcTemplate 类）的常见做法是在 Spring 配置文件中配置一个 DataSource ，然后将这个共享的 DataSource bean 依赖注入到 DAO 类中。 JdbcTemplate 在 DataSource 的 setter 方法中创建。这导致 DAO 类类似于以下结构：

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

Annotate the class with @Repository.

Annotate the DataSource setter method with @Autowired.

Create a new JdbcTemplate with the DataSource.

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 XML configuration:
以下示例显示了相应的 XML 配置：

<?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 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) and annotate the DataSource setter method with @Autowired. The following example shows how to do so:
显式配置的替代方案是使用组件扫描和注解支持来实现依赖注入。在这种情况下，您可以使用 @Repository 注解类（使其成为组件扫描的候选者），并使用 @Autowired 注解 DataSource 设置方法。以下示例展示了如何这样做：

@Repository (1)
public class JdbcCorporateEventDao implements CorporateEventDao {

    private JdbcTemplate jdbcTemplate;

    @Autowired (2)
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource); (3)
    }

    // JDBC-backed implementations of the methods on the CorporateEventDao follow...
}

Annotate the class with @Repository.

Constructor injection of the DataSource.

Create a new JdbcTemplate with the DataSource.

@Repository (1)
class JdbcCorporateEventDao(dataSource: DataSource) : CorporateEventDao { (2)

    private val jdbcTemplate = JdbcTemplate(dataSource) (3)

    // JDBC-backed implementations of the methods on the CorporateEventDao follow...
}

The following example shows the corresponding XML configuration:
以下示例显示了相应的 XML 配置：

<?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">

    <!-- 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 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.
如果您使用 Spring 的 JdbcDaoSupport 类，并且您的各种基于 JDBC 的 DAO 类都继承自它，那么您的子类将从 JdbcDaoSupport 类继承一个 setDataSource(..) 方法。您可以选择是否从该类继承。 JdbcDaoSupport 类仅作为便利提供。

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.
无论选择哪种模板初始化样式（或不选择），在每次运行 SQL 时创建一个新的 JdbcTemplate 类实例通常是不必要的。一旦配置完成， JdbcTemplate 实例是线程安全的。如果您的应用程序访问多个数据库，您可能需要多个 JdbcTemplate 实例，这需要多个 DataSources ，随后是多个配置不同的 JdbcTemplate 实例。

(#jdbc-NamedParameterJdbcTemplate)3.3.2. Using`NamedParameterJdbcTemplate` 3.3.2. 使用

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:
该 NamedParameterJdbcTemplate 类通过使用命名参数支持编程 JDBC 语句，而不是仅使用经典占位符（ '?' ）参数来编程 JDBC 语句。该 NamedParameterJdbcTemplate 类包装了一个 JdbcTemplate ，并将大部分工作委托给包装的 JdbcTemplate 。本节仅描述 NamedParameterJdbcTemplate 类与 JdbcTemplate 本身不同的区域——即，通过使用命名参数编程 JDBC 语句。以下示例展示了如何使用 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, 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).
请注意在分配给 sql 变量的值中使用的命名参数符号以及相应地插入到 namedParameters 变量（类型为 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.
另外，您可以通过使用基于 Map 的风格传递命名参数及其对应的值给 NamedParameterJdbcTemplate 实例。 NamedParameterJdbcOperations 和由 NamedParameterJdbcTemplate 类实现的其余方法遵循类似的模式，此处未涵盖。

The following example shows the use of the Map-based style:
以下示例展示了基于 Map 风格的用法：

// 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.
一个与 NamedParameterJdbcTemplate 相关且存在于同一 Java 包中的不错特性是 SqlParameterSource 接口。您已经在之前的代码片段中看到了这个接口的一个实现示例（ MapSqlParameterSource 类）。 SqlParameterSource 是向 NamedParameterJdbcTemplate 提供命名参数值的来源。 MapSqlParameterSource 类是一个简单的实现，它围绕一个 java.util.Map 进行适配，其中键是参数名称，值是参数值。

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.
另一个 SqlParameterSource 实现是 BeanPropertySqlParameterSource 类。此类封装了一个任意的 JavaBean（即遵循 JavaBean 约定的类的实例）并使用封装的 JavaBean 的属性作为命名参数值的来源。

The following example shows a typical JavaBean:
以下示例展示了一个典型的 JavaBean：

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:
以下示例使用 NamedParameterJdbcTemplate 来返回前一个示例中所示类的成员数量：

// 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.
请记住， NamedParameterJdbcTemplate 类封装了一个经典的 JdbcTemplate 模板。如果您需要访问封装的 JdbcTemplate 实例以访问仅在 JdbcTemplate 类中存在的功能，您可以使用 getJdbcOperations() 方法通过 JdbcOperations 接口访问封装的 JdbcTemplate 。

See also JdbcTemplate Best Practices for guidelines on using the NamedParameterJdbcTemplate class in the context of an application.
参见 JdbcTemplate 在应用程序上下文中使用 NamedParameterJdbcTemplate 类的最佳实践指南。

(#jdbc-SQLExceptionTranslator)3.3.3. Using`SQLExceptionTranslator` 3.3.3. 使用`SQLExceptionTranslator`

SQLExceptionTranslator is an interface to be implemented by classes that can translate between SQLExceptions 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 the common JdbcTemplate and JdbcTransactionManager entry points which do not propagate SQLException but rather DataAccessException.
SQLExceptionTranslator 是一个接口，由能够在不同 SQLException 和 Spring 自身的 org.springframework.dao.DataAccessException 之间进行转换的类实现，它对数据访问策略是中立的。实现可以是通用的（例如，使用 JDBC 的 SQLState 代码）或专有的（例如，使用 Oracle 错误代码），以提高精度。这种异常转换机制用于常见的 JdbcTemplate 和 JdbcTransactionManager 入口点之后，这些入口点不传播 SQLException 而是传递 DataAccessException 。

SQLErrorCodeSQLExceptionTranslator is the implementation of SQLExceptionTranslator that is used by default. This implementation uses specific vendor codes. It is more precise than the SQLState implementation. 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.
SQLErrorCodeSQLExceptionTranslator 是默认使用的 SQLExceptionTranslator 的实现。此实现使用特定的供应商代码。它比 SQLState 实现更精确。错误代码翻译基于名为 SQLErrorCodes 的 JavaBean 类型类中持有的代码。该类由一个 SQLErrorCodesFactory 创建并填充，该 SQLErrorCodesFactory（正如其名所示）是一个基于名为 sql-error-codes.xml 的配置文件内容的 SQLErrorCodes 工厂。此文件用供应商代码填充，并基于从 DatabaseMetaData 中获取的 DatabaseProductName 。使用的是您实际使用的数据库的代码。

The SQLErrorCodeSQLExceptionTranslator applies matching rules in the following sequence:
SQLErrorCodeSQLExceptionTranslator 按照以下顺序应用匹配规则：

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.
任何由子类实现的自定义翻译。通常使用提供的具体 SQLErrorCodeSQLExceptionTranslator ，因此此规则不适用。仅当您实际上提供了子类实现时才适用。
Any custom implementation of the SQLExceptionTranslator interface that is provided as the customSqlExceptionTranslator property of the SQLErrorCodes class.
任何作为 SQLErrorCodes 类的 customSqlExceptionTranslator 属性提供的 SQLExceptionTranslator 接口的自定义实现。
The list of instances of the CustomSQLErrorCodesTranslation class (provided for the customTranslations property of the SQLErrorCodes class) are searched for a match.
该类（为该类 SQLErrorCodes 的 customTranslations 属性提供）的实例列表被搜索以匹配。
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 the SQLStateSQLExceptionTranslator.
使用备用翻译器。 SQLExceptionSubclassTranslator 是默认备用翻译器。如果此翻译不可用，下一个备用翻译器是 SQLStateSQLExceptionTranslator 。

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.
默认使用 SQLErrorCodesFactory 来定义错误代码和自定义异常翻译。它们在名为 sql-error-codes.xml 的文件中进行查找，并根据正在使用的数据库的数据库元数据中的数据库名称定位匹配的 SQLErrorCodes 实例。

You can extend SQLErrorCodeSQLExceptionTranslator, as the following example shows:
您可以根据以下示例扩展 SQLErrorCodeSQLExceptionTranslator ：

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:
在先前的示例中，特定的错误代码（ -12345 ）被翻译，而其他错误则留给默认翻译器实现进行翻译。要使用此自定义翻译器，您必须通过方法 setExceptionTranslator 将 JdbcTemplate 传递给它，并且您必须在此翻译器需要的所有数据访问处理中使用此 JdbcTemplate 。以下示例显示了如何使用此自定义翻译器：

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.
自定义翻译器接收数据源以查找错误代码 sql-error-codes.xml 。

(#jdbc-statements-executing)3.3.4. Running Statements

3.3.4. 运行语句

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:
运行 SQL 语句只需要很少的代码。你需要一个 DataSource 和一个 JdbcTemplate ，包括 JdbcTemplate 提供的便捷方法。以下示例展示了创建一个最小但功能齐全的类所需包含的内容：

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))")
    }
}

(#jdbc-statements-querying)3.3.5. Running Queries 3.3.5. 运行查询

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:
一些查询方法返回单个值。要从一行中检索计数或特定值，请使用 queryForObject(..) 。后者将返回的 JDBC Type 转换为作为参数传递的 Java 类。如果类型转换无效，将抛出 InvalidDataAccessApiUsageException 。以下示例包含两个查询方法，一个用于 int ，另一个用于查询 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);
    }
}

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:
除了单结果查询方法外，还有几种方法返回一个列表，其中包含查询返回的每一行的条目。最通用的方法是 queryForList(..) ，它返回一个 List ，其中每个元素都是一个 Map ，包含每一列的一个条目，使用列名作为键。如果您在前面示例中添加一个方法来检索所有行的列表，可能如下所示：

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

(#jdbc-updates)3.3.6. Updating the Database

3.3.6. 更新数据库

The following example updates a column for a certain primary key:
以下示例更新了某个主键的列：

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.
在前面示例中，SQL 语句为行参数预留了占位符。您可以将参数值作为可变参数传递，或者作为对象数组传递。因此，您应该显式地将原始数据类型包装在原始数据类型包装类中，或者使用自动装箱。

(#jdbc-auto-generated-keys)3.3.7. Retrieving Auto-generated Keys

3.3.7. 获取自动生成的键

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:
一个 update() 便捷方法支持数据库生成的主键检索。这种支持是 JDBC 3.0 标准的一部分。请参阅规范的第 13.6 章以获取详细信息。该方法将其第一个参数作为其第一个参数，这是指定所需插入语句的方式。另一个参数是 KeyHolder ，它包含从更新成功返回的生成键。没有标准单一代码创建适当 PreparedStatement （这也解释了为什么方法签名是这样的）。以下示例在 Oracle 上有效，但在其他平台上可能无效：

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

(#jdbc-connections)3.4. Controlling Database Connections

3.4. 控制数据库连接

This section covers:本节涵盖：

Using DataSource 使用 DataSource
Using DataSourceUtils 使用 DataSourceUtils
Implementing SmartDataSource 实施 SmartDataSource
Extending AbstractDataSource 扩展 AbstractDataSource
Using SingleConnectionDataSource 使用 SingleConnectionDataSource
Using DriverManagerDataSource 使用 DriverManagerDataSource
Using TransactionAwareDataSourceProxy 使用 TransactionAwareDataSourceProxy
Using DataSourceTransactionManager / JdbcTransactionManager 使用 DataSourceTransactionManager / JdbcTransactionManager

(#jdbc-datasource)3.4.1. Using`DataSource` 3.4.1. 使用`DataSource`

Spring obtains a connection to the database through a DataSource. A DataSource is part of the JDBC specification and is a generalized connection factory. It lets a container or a framework hide connection pooling and transaction management issues from the application code. As a developer, you need not know details about how to connect to the database.
Spring 通过一个 DataSource 获取数据库连接。 DataSource 是 JDBC 规范的一部分，是一个通用连接工厂。它允许容器或框架将连接池和事务管理问题从应用程序代码中隐藏起来。作为开发者，你不需要了解如何连接到数据库的细节。
That is the responsibility of the administrator who sets up the datasource. You most likely fill both roles as you develop and test code, but you do not necessarily have to know how the production data source is configured.
这是设置数据源的管理员的职责。在开发和测试代码时，你很可能同时扮演这两个角色，但你不必一定了解生产数据源是如何配置的。

When you use Spring’s JDBC layer, you can obtain a data source from JNDI, or you can configure your own with a connection pool implementation provided by a third party. Traditional choices are Apache Commons DBCP and C3P0 with bean-style DataSource classes; for a modern JDBC connection pool, consider HikariCP with its builder-style API instead.
当您使用 Spring 的 JDBC 层时，您可以从 JNDI 获取数据源，或者您可以使用第三方提供的连接池实现来配置自己的。传统选择是 Apache Commons DBCP 和 C3P0，它们具有 bean 风格的 DataSource 类；对于现代 JDBC 连接池，请考虑使用具有构建器风格 API 的 HikariCP。

You should use the DriverManagerDataSource and SimpleDriverDataSource classes (as included in the Spring distribution) only for testing purposes! Those variants do not provide pooling and perform poorly when multiple requests for a connection are made.
您应仅将 DriverManagerDataSource 和 SimpleDriverDataSource 类（包含在 Spring 发行版中）用于测试目的！这些变体不提供连接池，在多次请求连接时表现不佳。

The following section uses Spring’s DriverManagerDataSource implementation. Several other DataSource variants are covered later.
以下部分使用 Spring 的 DriverManagerDataSource 实现。稍后还将介绍几个其他 DataSource 变体。

To configure a DriverManagerDataSource:
配置一个 DriverManagerDataSource :

Obtain a connection with DriverManagerDataSource as you typically obtain a JDBC connection.
获取与 DriverManagerDataSource 的连接，就像您通常获取 JDBC 连接一样。
Specify the fully qualified classname of the JDBC driver so that the DriverManager can load the driver class.
指定 JDBC 驱动的完全限定类名，以便 DriverManager 可以加载驱动类。
Provide a URL that varies between JDBC drivers. (See the documentation for your driver for the correct value.)
提供不同 JDBC 驱动程序之间的 URL。（请参阅您驱动程序的文档以获取正确值。）
Provide a username and a password to connect to the database.
请提供用户名和密码以连接到数据库。

The following example shows how to configure a DriverManagerDataSource in Java:
以下示例展示了如何在 Java 中配置 DriverManagerDataSource ：

DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");

val dataSource = DriverManagerDataSource().apply {
    setDriverClassName("org.hsqldb.jdbcDriver")
    url = "jdbc:hsqldb:hsql://localhost:"
    username = "sa"
    password = ""
}

The following example shows the corresponding XML configuration:
以下示例显示了相应的 XML 配置：

<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 next two 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 和 C3P0 的基本连接性和配置。要了解更多有助于控制池化功能的选项，请参阅相应连接池实现的产品文档。

The following example shows DBCP configuration:
以下示例显示了 DBCP 配置：

<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"/>

The following example shows C3P0 configuration:
以下示例显示了 C3P0 配置：

<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"/>

(#jdbc-DataSourceUtils)3.4.2. Using`DataSourceUtils` 3.4.2. 使用`DataSourceUtils`

The DataSourceUtils class is a convenient and powerful helper class that provides static methods to obtain connections from JNDI and close connections if necessary. It supports a thread-bound JDBC Connection with DataSourceTransactionManager but also with JtaTransactionManager and JpaTransactionManager.
DataSourceUtils 类是一个方便且强大的辅助类，它提供了 static 方法来从 JNDI 获取连接并在必要时关闭连接。它支持线程绑定的 JDBC Connection ，同时支持 DataSourceTransactionManager 以及 JtaTransactionManager 和 JpaTransactionManager 。

Note that JdbcTemplate implies DataSourceUtils connection access, using it behind every JDBC operation, implicitly participating in an ongoing transaction.
请注意， JdbcTemplate 表示 DataSourceUtils 连接访问，在每次 JDBC 操作中使用它，隐式地参与当前事务。

(#jdbc-SmartDataSource)3.4.3. Implementing`SmartDataSource` 3.4.3. 实现编号`SmartDataSource`

The SmartDataSource interface should be implemented by classes that can provide a connection to a relational database. It extends the DataSource interface to let classes that use it query whether the connection should be closed after a given operation. This usage is efficient when you know that you need to reuse a connection.
该 SmartDataSource 接口应由能够提供与关系型数据库连接的类实现。它扩展了 DataSource 接口，以便使用它的类可以查询在给定操作后是否应该关闭连接。当你知道需要重用连接时，这种用法是高效的。

(#jdbc-AbstractDataSource)3.4.4. Extending`AbstractDataSource` 3.4.4. 扩展`AbstractDataSource`

AbstractDataSource is an abstract base class for Spring’s DataSource implementations. It implements code that is common to all DataSource implementations. You should extend the AbstractDataSource class if you write your own DataSource implementation.
AbstractDataSource 是 Spring 的 abstract 实现的基类。它实现了所有 DataSource 实现共有的代码。如果您编写自己的 DataSource 实现，应扩展 AbstractDataSource 类。

(#jdbc-SingleConnectionDataSource)3.4.5. Using`SingleConnectionDataSource` 3.4.5. 使用

SingleConnectionDataSource

The SingleConnectionDataSource class is an implementation of the SmartDataSource interface that wraps a single Connection that is not closed after each use. This is not multi-threading capable.
SingleConnectionDataSource 类是实现 SmartDataSource 接口的一个封装，每次使用后不会关闭单个 Connection 。这不具备多线程能力。

If any client code calls close on the assumption of a pooled connection (as when using persistence tools), you should set the suppressClose property to true. This setting returns a close-suppressing proxy that wraps the physical connection. Note that you can no longer cast this to a native Oracle Connection or a similar object.
如果任何客户端代码在假设使用连接池的情况下调用 close （例如在使用持久化工具时），您应将 suppressClose 属性设置为 true 。此设置返回一个包装物理连接的抑制关闭代理。请注意，您不能再将其转换为原生 Oracle Connection 或类似对象。

SingleConnectionDataSource is primarily a test class. It typically enables easy testing of code outside an application server, in conjunction with a simple JNDI environment. In contrast to DriverManagerDataSource, it reuses the same connection all the time, avoiding excessive creation of physical connections.
SingleConnectionDataSource 主要是一个测试类。它通常允许在应用程序服务器外轻松测试代码，并与简单的 JNDI 环境结合使用。与 DriverManagerDataSource 相比，它始终重用相同的连接，避免过度创建物理连接。

(#jdbc-DriverManagerDataSource)3.4.6. Using`DriverManagerDataSource` 3.4.6. 使用`DriverManagerDataSource`

The DriverManagerDataSource class is an implementation of the standard DataSource interface that configures a plain JDBC driver through bean properties and returns a new Connection every time.
DriverManagerDataSource 类是实现标准 DataSource 接口的类，通过 bean 属性配置一个普通的 JDBC 驱动程序，每次都返回一个新的 Connection 。

This implementation is useful for test and stand-alone environments outside of a Java EE container, either as a DataSource bean in a Spring IoC container or in conjunction with a simple JNDI environment. Pool-assuming Connection.close() calls close the connection, so any DataSource-aware persistence code should work. However, using JavaBean-style connection pools (such as commons-dbcp) is so easy, even in a test environment, that it is almost always preferable to use such a connection pool over DriverManagerDataSource.
此实现适用于 Java EE 容器之外的测试和独立环境，无论是作为 Spring IoC 容器中的 DataSource bean，还是与简单的 JNDI 环境结合使用。池假设 Connection.close() 调用关闭连接，因此任何 DataSource -感知的持久化代码都应该工作。然而，使用 JavaBean 风格的连接池（如 commons-dbcp ）如此简单，即使在测试环境中，也几乎总是更倾向于使用这样的连接池而不是 DriverManagerDataSource 。

(#jdbc-TransactionAwareDataSourceProxy)3.4.7. Using`TransactionAwareDataSourceProxy` 3.4.7. 使用

TransactionAwareDataSourceProxy

TransactionAwareDataSourceProxy is a proxy for a target DataSource. The proxy wraps that target DataSource to add awareness of Spring-managed transactions. In this respect, it is similar to a transactional JNDI DataSource, as provided by a Java EE server.
TransactionAwareDataSourceProxy 是目标 DataSource 的代理。代理封装该目标 DataSource 以增加对 Spring 管理的事务的意识。在这方面，它与 Java EE 服务器提供的交易性 JNDI DataSource 类似。

It is rarely desirable to use this class, except when already existing code must be called and passed a standard JDBC DataSource interface implementation. In this case, you can still have this code be usable and, at the same time, have this code participating in Spring managed transactions.
很少希望使用此类，除非必须调用现有代码并传递标准 JDBC DataSource 接口实现。在这种情况下，您仍然可以使此代码可用，同时让此代码参与 Spring 管理的事务。
It is generally preferable to write your own new code by using the higher level abstractions for resource management, such as JdbcTemplate or DataSourceUtils.
通常最好通过使用资源管理的高级抽象，如 JdbcTemplate 或 DataSourceUtils ，来编写自己的新代码。

(#jdbc-DataSourceTransactionManager)3.4.8. Using`DataSourceTransactionManager` /`JdbcTransactionManager`

3.4.8. 使用 DataSourceTransactionManager / JdbcTransactionManager

The DataSourceTransactionManager class is a PlatformTransactionManager implementation for a single JDBC DataSource. It binds a JDBC Connection from the specified DataSource to the currently executing thread, potentially allowing for one thread-bound Connection per DataSource.
DataSourceTransactionManager 类是一个针对单个 JDBC DataSource 的 PlatformTransactionManager 实现。它将指定的 DataSource 中的 JDBC Connection 绑定到当前正在执行的线程，可能允许每个 DataSource 有一个线程绑定的 Connection 。

Application code is required to retrieve the JDBC Connection through DataSourceUtils.getConnection(DataSource) instead of Java EE’s standard DataSource.getConnection. It throws unchecked org.springframework.dao exceptions instead of checked SQLExceptions. All framework classes (such as JdbcTemplate) use this strategy implicitly. If not used with a transaction manager, the lookup strategy behaves exactly like DataSource.getConnection and can therefore be used in any case.
应用程序代码需要通过 Connection 到 DataSourceUtils.getConnection(DataSource) 来检索 JDBC，而不是使用 Java EE 的标准 DataSource.getConnection 。它抛出未检查的 org.springframework.dao 异常，而不是检查的 SQLExceptions 异常。所有框架类（如 JdbcTemplate ）都隐式地使用这种策略。如果不与事务管理器一起使用，查找策略的行为与 DataSource.getConnection 完全相同，因此可以在任何情况下使用。

The DataSourceTransactionManager class supports savepoints (PROPAGATION_NESTED), custom isolation levels, and timeouts that get applied as appropriate JDBC statement query timeouts. To support the latter, application code must either use JdbcTemplate or call the DataSourceUtils.applyTransactionTimeout(..) method for each created statement.
该 DataSourceTransactionManager 类支持保存点（ PROPAGATION_NESTED ）、自定义隔离级别以及适用于适当 JDBC 语句查询的超时时间。为了支持后者，应用程序代码必须使用 JdbcTemplate 或为每个创建的语句调用 DataSourceUtils.applyTransactionTimeout(..) 方法。

You can use DataSourceTransactionManager instead of JtaTransactionManager in the single-resource case, as it does not require the container to support a JTA transaction coordinator. Switching between these transaction managers is just a matter of configuration, provided you stick to the required connection lookup pattern.
您可以在单资源情况下使用 DataSourceTransactionManager 代替 JtaTransactionManager ，因为它不需要容器支持 JTA 事务协调器。只要您坚持使用所需的连接查找模式，在这些事务管理器之间切换只是配置问题。
Note that JTA does not support savepoints or custom isolation levels and has a different timeout mechanism but otherwise exposes similar behavior in terms of JDBC resources and JDBC commit/rollback management.
请注意，JTA 不支持保存点或自定义隔离级别，并且具有不同的超时机制，但在 JDBC 资源和 JDBC 提交/回滚管理方面表现出类似的行为。

As of 5.3, Spring provides an extended JdbcTransactionManager variant which adds exception translation capabilities on commit/rollback (aligned with JdbcTemplate). Where DataSourceTransactionManager will only ever throw TransactionSystemException (analogous to JTA), JdbcTransactionManager translates database locking failures etc to corresponding DataAccessException subclasses. Note that application code needs to be prepared for such exceptions, not exclusively expecting TransactionSystemException. In scenarios where that is the case, JdbcTransactionManager is the recommended choice.
截至 5.3 版本，Spring 提供了一个扩展的 JdbcTransactionManager 变体，该变体在提交/回滚时增加了异常翻译功能（与 JdbcTemplate 对齐）。其中 DataSourceTransactionManager 将始终抛出 TransactionSystemException （类似于 JTA）， JdbcTransactionManager 将数据库锁定失败等转换为相应的 DataAccessException 子类。请注意，应用程序代码需要准备处理此类异常，而不仅仅是期望 TransactionSystemException 。在那种情况下， JdbcTransactionManager 是推荐的选择。

In terms of exception behavior, JdbcTransactionManager is roughly equivalent to JpaTransactionManager and also to R2dbcTransactionManager, serving as an immediate companion/replacement for each other. DataSourceTransactionManager on the other hand is equivalent to JtaTransactionManager and can serve as a direct replacement there.
在异常行为方面， JdbcTransactionManager 大约等同于 JpaTransactionManager ，也等同于 R2dbcTransactionManager ，彼此可以作为即时伴侣/替代品。另一方面， DataSourceTransactionManager 等同于 JtaTransactionManager ，可以在此处直接替代。

(#jdbc-advanced-jdbc)3.5. JDBC Batch Operations

3.5. JDBC 批量操作

Most JDBC drivers provide improved performance if you batch multiple calls to the same prepared statement. By grouping updates into batches, you limit the number of round trips to the database.
大多数 JDBC 驱动程序在将多个调用批处理到相同的预编译语句时提供改进的性能。通过将更新分组到批次中，您可以限制往返数据库的次数。

(#jdbc-batch-classic)3.5.1. Basic Batch Operations with`JdbcTemplate`

3.5.1. 使用 JdbcTemplate 的基本批量操作

You accomplish JdbcTemplate batch processing by implementing two methods of a special interface, BatchPreparedStatementSetter, and passing that implementation in as the second parameter in your batchUpdate method call. You can use the getBatchSize method to provide the size of the current batch. You can use the setValues method to set the values for the parameters of the prepared statement. This method is called the number of times that you specified in the getBatchSize call. The following example updates the t_actor table based on entries in a list, and the entire list is used as the batch:
您通过实现特殊接口的两种方法 JdbcTemplate 来完成 batchUpdate 方法调用中的批处理，并将该实现作为第二个参数传入。您可以使用 getBatchSize 方法提供当前批次的尺寸。您可以使用 setValues 方法设置预处理语句的参数值。此方法将根据您在 getBatchSize 调用中指定的次数被调用。以下示例根据列表中的条目更新 t_actor 表，整个列表用作批次：

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int batchUpdate(final List<Actor> actors) {
        return this.jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        Actor actor = actors.get(i);
                        ps.setString(1, actor.getFirstName());
                        ps.setString(2, actor.getLastName());
                        ps.setLong(3, actor.getId().longValue());
                    }
                    public int getBatchSize() {
                        return actors.size();
                    }
                });
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val jdbcTemplate = JdbcTemplate(dataSource)

    fun batchUpdate(actors: List<Actor>): IntArray {
        return jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                object: BatchPreparedStatementSetter {
                    override fun setValues(ps: PreparedStatement, i: Int) {
                        ps.setString(1, actors[i].firstName)
                        ps.setString(2, actors[i].lastName)
                        ps.setLong(3, actors[i].id)
                    }

                    override fun getBatchSize() = actors.size
                })
    }

    // ... additional methods
}

If you process a stream of updates or reading from a file, you might have a preferred batch size, but the last batch might not have that number of entries. In this case, you can use the InterruptibleBatchPreparedStatementSetter interface, which lets you interrupt a batch once the input source is exhausted. The isBatchExhausted method lets you signal the end of the batch.
如果您处理一系列更新或从文件中读取，您可能有一个首选的批次大小，但最后一个批次可能没有那么多条目。在这种情况下，您可以使用 InterruptibleBatchPreparedStatementSetter 接口，它允许在输入源耗尽后中断一个批次。 isBatchExhausted 方法允许您发出批次结束的信号。

(#jdbc-batch-list)3.5.2. Batch Operations with a List of Objects

3.5.2. 使用对象列表进行批量操作

Both the JdbcTemplate and the NamedParameterJdbcTemplate provides an alternate way of providing the batch update. Instead of implementing a special batch interface, you provide all parameter values in the call as a list. The framework loops over these values and uses an internal prepared statement setter.
两者（ JdbcTemplate 和 NamedParameterJdbcTemplate ）都提供了一种提供批量更新的替代方法。而不是实现一个特殊的批量接口，你可以在调用中将所有参数值作为一个列表提供。框架会遍历这些值，并使用内部准备好的语句设置器。
The API varies, depending on whether you use named parameters. For the named parameters, you provide an array of SqlParameterSource, one entry for each member of the batch. You can use the SqlParameterSourceUtils.createBatch convenience methods to create this array, passing in an array of bean-style objects (with getter methods corresponding to parameters), String-keyed Map instances (containing the corresponding parameters as values), or a mix of both.
API 根据是否使用命名参数而有所不同。对于命名参数，您提供一个包含 SqlParameterSource 的数组，每个批次成员一个条目。您可以使用 SqlParameterSourceUtils.createBatch 便捷方法创建此数组，传入一个包含与参数对应的 getter 方法的 bean 样式对象数组， String -键的 Map 实例（包含相应的参数值），或者两者的混合。

The following example shows a batch update using named parameters:
以下示例展示了使用命名参数的批量更新：

public class JdbcActorDao implements ActorDao {

    private NamedParameterTemplate namedParameterJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public int batchUpdate(List<Actor> actors) {
        return this.namedParameterJdbcTemplate.batchUpdate(
                "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
                SqlParameterSourceUtils.createBatch(actors));
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)

    fun batchUpdate(actors: List<Actor>): IntArray {
        return this.namedParameterJdbcTemplate.batchUpdate(
                "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
                SqlParameterSourceUtils.createBatch(actors));
    }

        // ... additional methods
}

For an SQL statement that uses the classic ? placeholders, you pass in a list containing an object array with the update values. This object array must have one entry for each placeholder in the SQL statement, and they must be in the same order as they are defined in the SQL statement.
对于使用经典 ? 占位符的 SQL 语句，您传递一个包含对象数组的列表，该对象数组包含更新值。此对象数组必须为 SQL 语句中的每个占位符提供一个条目，并且它们的顺序必须与在 SQL 语句中定义的顺序相同。

The following example is the same as the preceding example, except that it uses classic JDBC ? placeholders:
以下示例与前面的示例相同，只是它使用了经典的 JDBC ? 占位符：

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int batchUpdate(final List<Actor> actors) {
        List<Object> batch = new ArrayList<Object>();
        for (Actor actor : actors) {
            Object values = new Object {
                    actor.getFirstName(), actor.getLastName(), actor.getId()};
            batch.add(values);
        }
        return this.jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                batch);
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val jdbcTemplate = JdbcTemplate(dataSource)

    fun batchUpdate(actors: List<Actor>): IntArray {
        val batch = mutableListOf<Array<Any>>()
        for (actor in actors) {
            batch.add(arrayOf(actor.firstName, actor.lastName, actor.id))
        }
        return jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?", batch)
    }

    // ... additional methods
}

All of the batch update methods that we described earlier return an int array containing the number of affected rows for each batch entry. This count is reported by the JDBC driver. If the count is not available, the JDBC driver returns a value of -2.
所有我们之前描述的批量更新方法都返回一个包含每个批次条目影响的行数的 int 数组。这个计数由 JDBC 驱动程序报告。如果计数不可用，JDBC 驱动程序返回一个值 -2 。

In such a scenario, with automatic setting of values on an underlying PreparedStatement, the corresponding JDBC type for each value needs to be derived from the given Java type. While this usually works well, there is a potential for issues (for example, with Map-contained null values). Spring, by default, calls ParameterMetaData.getParameterType in such a case, which can be expensive with your JDBC driver. You should use a recent driver version and consider setting the spring.jdbc.getParameterType.ignore property to true (as a JVM system property or via the SpringProperties mechanism) if you encounter a performance issue (as reported on Oracle 12c, JBoss, and PostgreSQL).
在这种情况下，通过在底层自动设置值 PreparedStatement ，每个值的对应 JDBC 类型需要从给定的 Java 类型中推导出来。虽然这通常工作良好，但存在潜在问题（例如，与包含 null 值的 Map 相关）。默认情况下，Spring 会调用 ParameterMetaData.getParameterType ，这可能会对你的 JDBC 驱动程序造成开销。如果你遇到性能问题（如 Oracle 12c、JBoss 和 PostgreSQL 上报告的），应使用最新版本的驱动程序，并考虑将 spring.jdbc.getParameterType.ignore 属性设置为 true （作为 JVM 系统属性或通过 SpringProperties 机制）。

Alternatively, you might consider specifying the corresponding JDBC types explicitly, either through a BatchPreparedStatementSetter (as shown earlier), through an explicit type array given to a List<Object> based call, through registerSqlType calls on a custom MapSqlParameterSource instance, or through a BeanPropertySqlParameterSource that derives the SQL type from the Java-declared property type even for a null value.
或者，您可以考虑明确指定相应的 JDBC 类型，无论是通过 BatchPreparedStatementSetter （如前所述），通过提供给基于 List<Object> 的调用的显式类型数组，通过在自定义的 registerSqlType 实例上调用 MapSqlParameterSource ，还是通过 BeanPropertySqlParameterSource ，即使对于空值，也能从 Java 声明的属性类型推导出 SQL 类型。

(#jdbc-batch-multi)3.5.3. Batch Operations with Multiple Batches

3.5.3. 多批次批量操作

The preceding example of a batch update deals with batches that are so large that you want to break them up into several smaller batches. You can do this with the methods mentioned earlier by making multiple calls to the batchUpdate method, but there is now a more convenient method. This method takes, in addition to the SQL statement, a Collection of objects that contain the parameters, the number of updates to make for each batch, and a ParameterizedPreparedStatementSetter to set the values for the parameters of the prepared statement. The framework loops over the provided values and breaks the update calls into batches of the size specified.
前一个批量更新的例子处理的是如此庞大的批次，以至于您希望将它们拆分成几个更小的批次。您可以通过多次调用 batchUpdate 方法来实现这一点，但现在有一个更方便的方法。此方法除了 SQL 语句外，还接受一个包含参数的对象 Collection 、每个批次要进行的更新次数以及一个用于设置预处理语句参数值的 ParameterizedPreparedStatementSetter 。框架遍历提供的值，并将更新调用拆分成指定大小的批次。

The following example shows a batch update that uses a batch size of 100:
以下示例展示了使用 100 个批次的批量更新：

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int batchUpdate(final Collection<Actor> actors) {
        int updateCounts = jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                actors,
                100,
                (PreparedStatement ps, Actor actor) -> {
                    ps.setString(1, actor.getFirstName());
                    ps.setString(2, actor.getLastName());
                    ps.setLong(3, actor.getId().longValue());
                });
        return updateCounts;
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val jdbcTemplate = JdbcTemplate(dataSource)

    fun batchUpdate(actors: List<Actor>): Array<IntArray> {
        return jdbcTemplate.batchUpdate(
                    "update t_actor set first_name = ?, last_name = ? where id = ?",
                    actors, 100) { ps, argument ->
            ps.setString(1, argument.firstName)
            ps.setString(2, argument.lastName)
            ps.setLong(3, argument.id)
        }
    }

    // ... additional methods
}

The batch update method for this call returns an array of int arrays that contains an array entry for each batch with an array of the number of affected rows for each update. The top-level array’s length indicates the number of batches run, and the second level array’s length indicates the number of updates in that batch.
该调用批处理更新方法返回一个包含每个批次的数组条目的数组，每个条目都是一个包含每个更新影响的行数的数组。顶层数组的长度表示运行的批次数量，第二级数组的长度表示该批次中的更新数量。
The number of updates in each batch should be the batch size provided for all batches (except that the last one that might be less), depending on the total number of update objects provided. The update count for each update statement is the one reported by the JDBC driver.
每个批次中更新的数量应该是为所有批次提供的批次大小（除了最后一个可能更少的情况），取决于提供的总更新对象数。每个更新语句的更新计数是由 JDBC 驱动程序报告的。
If the count is not available, the JDBC driver returns a value of -2.
如果计数不可用，JDBC 驱动程序返回值为 -2 。

(#jdbc-simple-jdbc)3.6. Simplifying JDBC Operations with the`SimpleJdbc` Classes

3.6. 使用 SimpleJdbc 类简化 JDBC 操作

The SimpleJdbcInsert and SimpleJdbcCall classes provide a simplified configuration by taking advantage of database metadata that can be retrieved through the JDBC driver.
SimpleJdbcInsert 和 SimpleJdbcCall 类通过利用通过 JDBC 驱动程序检索到的数据库元数据，提供了一种简化的配置。
This means that you have less to configure up front, although you can override or turn off the metadata processing if you prefer to provide all the details in your code.
这意味着您在最初配置时需要设置的内容更少，尽管如果您愿意在代码中提供所有详细信息，您可以覆盖或关闭元数据处理。

(#jdbc-simple-jdbc-insert-1)3.6.1. Inserting Data by Using`SimpleJdbcInsert`

3.6.1. 使用 SimpleJdbcInsert 插入数据

We start by looking at the SimpleJdbcInsert class with the minimal amount of configuration options. You should instantiate the SimpleJdbcInsert in the data access layer’s initialization method. For this example, the initializing method is the setDataSource method. You do not need to subclass the SimpleJdbcInsert class. Instead, you can create a new instance and set the table name by using the withTableName method. Configuration methods for this class follow the fluid style that returns the instance of the SimpleJdbcInsert, which lets you chain all configuration methods. The following example uses only one configuration method (we show examples of multiple methods later):
我们首先查看具有最少配置选项的 SimpleJdbcInsert 类。您应该在数据访问层的初始化方法中实例化 SimpleJdbcInsert 。对于这个示例，初始化方法是 setDataSource 方法。您不需要继承 SimpleJdbcInsert 类。相反，您可以创建一个新的实例，并使用 withTableName 方法设置表名。此类的配置方法遵循 fluid 风格，该方法返回 SimpleJdbcInsert 的实例，这使得您可以链式调用所有配置方法。以下示例仅使用一个配置方法（我们稍后会展示多个方法的示例）：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("t_actor");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(3);
        parameters.put("id", actor.getId());
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        insertActor.execute(parameters);
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val insertActor = SimpleJdbcInsert(dataSource).withTableName("t_actor")

    fun add(actor: Actor) {
        val parameters = mutableMapOf<String, Any>()
        parameters["id"] = actor.id
        parameters["first_name"] = actor.firstName
        parameters["last_name"] = actor.lastName
        insertActor.execute(parameters)
    }

    // ... additional methods
}

The execute method used here takes a plain java.util.Map as its only parameter. The important thing to note here is that the keys used for the Map must match the column names of the table, as defined in the database. This is because we read the metadata to construct the actual insert statement.
此处使用的 execute 方法仅接受一个普通 java.util.Map 作为其唯一参数。需要注意的是，用于 Map 的键必须与数据库中定义的表列名相匹配。这是因为我们读取元数据来构建实际的插入语句。

(#jdbc-simple-jdbc-insert-2)3.6.2. Retrieving Auto-generated Keys by Using`SimpleJdbcInsert`

3.6.2. 使用 SimpleJdbcInsert 检索自动生成的键

The next example uses the same insert as the preceding example, but, instead of passing in the id, it retrieves the auto-generated key and sets it on the new Actor object. When it creates the SimpleJdbcInsert, in addition to specifying the table name, it specifies the name of the generated key column with the usingGeneratedKeyColumns method. The following listing shows how it works:
下一个示例使用与上一个示例相同的插入操作，但它不是传递 id ，而是检索自动生成的键并将其设置在新的 Actor 对象上。当它创建 SimpleJdbcInsert 时，除了指定表名外，还使用 usingGeneratedKeyColumns 方法指定生成键的列名。以下列表显示了它是如何工作的：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor").usingGeneratedKeyColumns("id")

    fun add(actor: Actor): Actor {
        val parameters = mapOf(
                "first_name" to actor.firstName,
                "last_name" to actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters);
        return actor.copy(id = newId.toLong())
    }

    // ... additional methods
}

The main difference when you run the insert by using this second approach is that you do not add the id to the Map, and you call the executeAndReturnKey method. This returns a java.lang.Number object with which you can create an instance of the numerical type that is used in your domain class. You cannot rely on all databases to return a specific Java class here. java.lang.Number is the base class that you can rely on. If you have multiple auto-generated columns or the generated values are non-numeric, you can use a KeyHolder that is returned from the executeAndReturnKeyHolder method.
当你使用第二种方法运行插入操作时，主要区别在于你不需要将 id 添加到 Map 中，而是调用 executeAndReturnKey 方法。这将返回一个 java.lang.Number 对象，你可以使用它来创建你领域类中使用的数值类型的实例。你不能依赖所有数据库都返回特定的 Java 类。 java.lang.Number 是你可以依赖的基本类。如果你有多个自动生成的列或生成的值是非数值的，你可以使用从 executeAndReturnKeyHolder 方法返回的 KeyHolder 。

(#jdbc-simple-jdbc-insert-3)3.6.3. Specifying Columns for a`SimpleJdbcInsert`

3.6.3. 指定 SimpleJdbcInsert 的列

You can limit the columns for an insert by specifying a list of column names with the usingColumns method, as the following example shows:
您可以通过指定列名列表使用 usingColumns 方法来限制插入的列，如下例所示：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingColumns("first_name", "last_name")
                .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingColumns("first_name", "last_name")
            .usingGeneratedKeyColumns("id")

    fun add(actor: Actor): Actor {
        val parameters = mapOf(
                "first_name" to actor.firstName,
                "last_name" to actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters);
        return actor.copy(id = newId.toLong())
    }

    // ... additional methods
}

The execution of the insert is the same as if you had relied on the metadata to determine which columns to use.
执行插入操作与您依赖元数据来确定使用哪些列相同。

(#jdbc-simple-jdbc-parameters)3.6.4. Using`SqlParameterSource` to Provide Parameter Values

3.6.4. 使用 SqlParameterSource 提供参数值

Using a Map to provide parameter values works fine, but it is not the most convenient class to use. Spring provides a couple of implementations of the SqlParameterSource interface that you can use instead. The first one is BeanPropertySqlParameterSource, which is a very convenient class if you have a JavaBean-compliant class that contains your values. It uses the corresponding getter method to extract the parameter values. The following example shows how to use BeanPropertySqlParameterSource:
使用 Map 提供参数值效果不错，但并不是最方便的类。Spring 提供了一些 SqlParameterSource 接口的实现，您可以替换使用。第一个是 BeanPropertySqlParameterSource ，如果您有一个包含您值的 JavaBean 兼容类，它是一个非常方便的类。它使用相应的 getter 方法来提取参数值。以下示例展示了如何使用 BeanPropertySqlParameterSource ：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(actor);
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingGeneratedKeyColumns("id")

    fun add(actor: Actor): Actor {
        val parameters = BeanPropertySqlParameterSource(actor)
        val newId = insertActor.executeAndReturnKey(parameters)
        return actor.copy(id = newId.toLong())
    }

    // ... additional methods
}

Another option is the MapSqlParameterSource that resembles a Map but provides a more convenient addValue method that can be chained. The following example shows how to use it:
另一个选项是类似于 Map 的 MapSqlParameterSource ，但提供了更方便的 addValue 方法，可以链式调用。以下示例展示了如何使用它：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcInsert insertActor;

    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }

    public void add(Actor actor) {
        SqlParameterSource parameters = new MapSqlParameterSource()
                .addValue("first_name", actor.getFirstName())
                .addValue("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingGeneratedKeyColumns("id")

    fun add(actor: Actor): Actor {
        val parameters = MapSqlParameterSource()
                    .addValue("first_name", actor.firstName)
                    .addValue("last_name", actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters)
        return actor.copy(id = newId.toLong())
    }

    // ... additional methods
}

As you can see, the configuration is the same. Only the executing code has to change to use these alternative input classes.
如您所见，配置是相同的。只需更改执行代码，才能使用这些替代输入类。

(#jdbc-simple-jdbc-call-1)3.6.5. Calling a Stored Procedure with`SimpleJdbcCall`

3.6.5. 调用存储过程使用 SimpleJdbcCall

The SimpleJdbcCall class uses metadata in the database to look up names of in and out parameters so that you do not have to explicitly declare them. You can declare parameters if you prefer to do that or if you have parameters (such as ARRAY or STRUCT) that do not have an automatic mapping to a Java class. The first example shows a simple procedure that returns only scalar values in VARCHAR and DATE format from a MySQL database. The example procedure reads a specified actor entry and returns first_name, last_name, and birth_date columns in the form of out parameters. The following listing shows the first example:
该 SimpleJdbcCall 类使用数据库中的元数据来查找 in 和 out 参数的名称，以便您不必显式声明它们。如果您愿意，可以声明参数，或者如果您有（如 ARRAY 或 STRUCT ）没有自动映射到 Java 类的参数。第一个示例显示了一个简单的过程，该过程从 MySQL 数据库中以 VARCHAR 和 DATE 格式返回仅标量值。示例过程读取指定的演员条目，并以 out 参数的形式返回 first_name 、 last_name 和 birth_date 列。以下列表显示了第一个示例：

CREATE PROCEDURE read_actor (
    IN in_id INTEGER,
    OUT out_first_name VARCHAR(100),
    OUT out_last_name VARCHAR(100),
    OUT out_birth_date DATE)
BEGIN
    SELECT first_name, last_name, birth_date
    INTO out_first_name, out_last_name, out_birth_date
    FROM t_actor where id = in_id;
END;

The in_id parameter contains the id of the actor that you are looking up. The out parameters return the data read from the table.
in_id 参数包含您正在查找的演员的 id 。 out 参数返回从表中读取的数据。

You can declare SimpleJdbcCall in a manner similar to declaring SimpleJdbcInsert. You should instantiate and configure the class in the initialization method of your data-access layer. Compared to the StoredProcedure class, you need not create a subclass and you need not to declare parameters that can be looked up in the database metadata. The following example of a SimpleJdbcCall configuration uses the preceding stored procedure (the only configuration option, in addition to the DataSource, is the name of the stored procedure):
您可以使用类似于声明 SimpleJdbcInsert 的方式声明 SimpleJdbcCall 。您应该在数据访问层的初始化方法中实例化和配置该类。与 StoredProcedure 类相比，您不需要创建子类，也不需要声明可以在数据库元数据中查找的参数。以下 SimpleJdbcCall 配置的示例使用了前面的存储过程（除了 DataSource 之外，唯一的配置选项是存储过程的名称）：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcCall procReadActor;

    public void setDataSource(DataSource dataSource) {
        this.procReadActor = new SimpleJdbcCall(dataSource)
                .withProcedureName("read_actor");
    }

    public Actor readActor(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        Map out = procReadActor.execute(in);
        Actor actor = new Actor();
        actor.setId(id);
        actor.setFirstName((String) out.get("out_first_name"));
        actor.setLastName((String) out.get("out_last_name"));
        actor.setBirthDate((Date) out.get("out_birth_date"));
        return actor;
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val procReadActor = SimpleJdbcCall(dataSource)
            .withProcedureName("read_actor")


    fun readActor(id: Long): Actor {
        val source = MapSqlParameterSource().addValue("in_id", id)
        val output = procReadActor.execute(source)
        return Actor(
                id,
                output["out_first_name"] as String,
                output["out_last_name"] as String,
                output["out_birth_date"] as Date)
    }

        // ... additional methods
}

The code you write for the execution of the call involves creating an SqlParameterSource containing the IN parameter. You must match the name provided for the input value with that of the parameter name declared in the stored procedure.
您编写的用于执行调用的代码涉及创建一个包含 IN 参数的 SqlParameterSource 。您必须将提供的输入值名称与存储过程中声明的参数名称相匹配。
The case does not have to match because you use metadata to determine how database objects should be referred to in a stored procedure. What is specified in the source for the stored procedure is not necessarily the way it is stored in the database.
案例不必匹配，因为您使用元数据来确定在存储过程中如何引用数据库对象。存储过程源中指定的内容不一定就是它在数据库中存储的方式。
Some databases transform names to all upper case, while others use lower case or use the case as specified.
一些数据库将名称转换为大写，而另一些则使用小写或按照指定的大小写使用。

The execute method takes the IN parameters and returns a Map that contains any out parameters keyed by the name, as specified in the stored procedure. In this case, they are out_first_name, out_last_name, and out_birth_date.
该方法接收 IN 参数并返回一个包含任何按名称键控的参数的 Map ，如存储过程中指定的。在这种情况下，它们是 out_first_name 、 out_last_name 和 out_birth_date 。

The last part of the execute method creates an Actor instance to use to return the data retrieved. Again, it is important to use the names of the out parameters as they are declared in the stored procedure. Also, the case in the names of the out parameters stored in the results map matches that of the out parameter names in the database, which could vary between databases. To make your code more portable, you should do a case-insensitive lookup or instruct Spring to use a LinkedCaseInsensitiveMap. To do the latter, you can create your own JdbcTemplate and set the setResultsMapCaseInsensitive property to true. Then you can pass this customized JdbcTemplate instance into the constructor of your SimpleJdbcCall. The following example shows this configuration:
最后一部分创建一个 Actor 实例以用于返回检索到的数据。再次强调，使用存储过程中声明的 out 参数名称非常重要。此外，存储在结果映射中的 out 参数名称的大小写与数据库中的 out 参数名称的大小写相匹配，这可能在不同的数据库之间有所不同。为了使您的代码更具可移植性，您应该进行不区分大小写的查找或指示 Spring 使用 LinkedCaseInsensitiveMap 。为此，您可以创建自己的 JdbcTemplate 并设置 setResultsMapCaseInsensitive 属性为 true 。然后，您可以将此自定义的 JdbcTemplate 实例传递到您的 SimpleJdbcCall 的构造函数中。以下示例显示了这种配置：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcCall procReadActor;

    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_actor");
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private var procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
        isResultsMapCaseInsensitive = true
    }).withProcedureName("read_actor")

    // ... additional methods
}

By taking this action, you avoid conflicts in the case used for the names of your returned out parameters.
通过采取此行动，您避免了在返回的 out 参数名称中使用的案例冲突。

(#jdbc-simple-jdbc-call-2)3.6.6. Explicitly Declaring Parameters to Use for a`SimpleJdbcCall`

3.6.6. 明确声明用于 SimpleJdbcCall 的参数

Earlier in this chapter, we described how parameters are deduced from metadata, but you can declare them explicitly if you wish. You can do so by creating and configuring SimpleJdbcCall with the declareParameters method, which takes a variable number of SqlParameter objects as input. See the next section for details on how to define an SqlParameter.
本章早期，我们描述了如何从元数据中推导参数，但如果你愿意，也可以显式声明它们。你可以通过创建和配置 SimpleJdbcCall ，使用 declareParameters 方法，该方法接受可变数量的 SqlParameter 对象作为输入来实现。有关如何定义 SqlParameter 的详细信息，请参阅下一节。

Explicit declarations are necessary if the database you use is not a Spring-supported database. Currently, Spring supports metadata lookup of stored procedure calls for the following databases: Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase.
显式声明是必要的，如果您使用的数据库不是 Spring 支持的数据库。目前，Spring 支持以下数据库的存储过程调用元数据查找：Apache Derby、DB2、MySQL、Microsoft SQL Server、Oracle 和 Sybase。
We also support metadata lookup of stored functions for MySQL, Microsoft SQL Server, and Oracle.
我们同样支持 MySQL、Microsoft SQL Server 和 Oracle 存储函数的元数据查找。

You can opt to explicitly declare one, some, or all of the parameters. The parameter metadata is still used where you do not explicitly declare parameters.
您可以选择显式声明一个、一些或所有参数。即使您没有显式声明参数，仍然会使用参数元数据。
To bypass all processing of metadata lookups for potential parameters and use only the declared parameters, you can call the method withoutProcedureColumnMetaDataAccess as part of the declaration. Suppose that you have two or more different call signatures declared for a database function. In this case, you call useInParameterNames to specify the list of IN parameter names to include for a given signature.
绕过所有潜在参数的元数据查找处理，仅使用声明的参数，您可以在声明部分调用 withoutProcedureColumnMetaDataAccess 方法。假设您为数据库函数声明了两个或更多不同的调用签名。在这种情况下，您调用 useInParameterNames 来指定给定签名应包含的 IN 参数名称列表。

The following example shows a fully declared procedure call and uses the information from the preceding example:
以下示例展示了完全声明的过程调用，并使用了前面示例中的信息：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcCall procReadActor;

    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_actor")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("in_id")
                .declareParameters(
                        new SqlParameter("in_id", Types.NUMERIC),
                        new SqlOutParameter("out_first_name", Types.VARCHAR),
                        new SqlOutParameter("out_last_name", Types.VARCHAR),
                        new SqlOutParameter("out_birth_date", Types.DATE)
                );
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

        private val procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
            isResultsMapCaseInsensitive = true
        }).withProcedureName("read_actor")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("in_id")
                .declareParameters(
                        SqlParameter("in_id", Types.NUMERIC),
                        SqlOutParameter("out_first_name", Types.VARCHAR),
                        SqlOutParameter("out_last_name", Types.VARCHAR),
                        SqlOutParameter("out_birth_date", Types.DATE)
    )

        // ... additional methods
}

The execution and end results of the two examples are the same. The second example specifies all details explicitly rather than relying on metadata.
两个示例的执行和最终结果相同。第二个示例明确指定所有细节，而不是依赖于元数据。

(#jdbc-params)3.6.7. How to Define`SqlParameters`

3.6.7. 如何定义 SqlParameters

To define a parameter for the SimpleJdbc classes and also for the RDBMS operations classes (covered in Modeling JDBC Operations as Java Objects) you can use SqlParameter or one of its subclasses. To do so, you typically specify the parameter name and SQL type in the constructor. The SQL type is specified by using the java.sql.Types constants. Earlier in this chapter, we saw declarations similar to the following:
为了为 SimpleJdbc 类以及 RDBMS 操作类（在《将 JDBC 操作建模为 Java 对象》中介绍）定义一个参数，您可以使用 SqlParameter 或其子类之一。为此，您通常在构造函数中指定参数名称和 SQL 类型。SQL 类型是通过使用 java.sql.Types 常量来指定的。在本章早期，我们看到了类似以下声明：

new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),

SqlParameter("in_id", Types.NUMERIC),
SqlOutParameter("out_first_name", Types.VARCHAR),

The first line with the SqlParameter declares an IN parameter. You can use IN parameters for both stored procedure calls and for queries by using the SqlQuery and its subclasses (covered in Understanding SqlQuery).
第一行使用 SqlParameter 声明一个 IN 参数。您可以使用 IN 参数进行存储过程调用以及查询，通过使用 SqlQuery 及其子类（在理解 SqlQuery 中介绍）。

The second line (with the SqlOutParameter) declares an out parameter to be used in a stored procedure call. There is also an SqlInOutParameter for InOut parameters (parameters that provide an IN value to the procedure and that also return a value).
第二行（带有 SqlOutParameter ）声明了一个 out 参数，用于存储过程调用。还有一个 SqlInOutParameter 用于 InOut 参数（提供 IN 值给过程并返回值的参数）。

Only parameters declared as SqlParameter and SqlInOutParameter are used to provide input values. This is different from the StoredProcedure class, which (for backwards compatibility reasons) lets input values be provided for parameters declared as SqlOutParameter.
仅使用声明为 SqlParameter 和 SqlInOutParameter 的参数来提供输入值。这与 StoredProcedure 类不同，该类（出于向后兼容的原因）允许为声明为 SqlOutParameter 的参数提供输入值。

For IN parameters, in addition to the name and the SQL type, you can specify a scale for numeric data or a type name for custom database types. For out parameters, you can provide a RowMapper to handle mapping of rows returned from a REF cursor. Another option is to specify an SqlReturnType that provides an opportunity to define customized handling of the return values.
对于 IN 参数，除了名称和 SQL 类型外，您还可以指定数值数据的刻度或自定义数据库类型的类型名称。对于 out 参数，您可以提供 RowMapper 来处理从 REF 游标返回的行映射。另一种选择是指定 SqlReturnType ，这提供了定义自定义处理返回值的机会。

(#jdbc-simple-jdbc-call-3)3.6.8. Calling a Stored Function by Using`SimpleJdbcCall`

3.6.8. 通过 SimpleJdbcCall 调用存储函数

You can call a stored function in almost the same way as you call a stored procedure, except that you provide a function name rather than a procedure name. You use the withFunctionName method as part of the configuration to indicate that you want to make a call to a function, and the corresponding string for a function call is generated. A specialized call (executeFunction) is used to run the function, and it returns the function return value as an object of a specified type, which means you do not have to retrieve the return value from the results map. A similar convenience method (named executeObject) is also available for stored procedures that have only one out parameter. The following example (for MySQL) is based on a stored function named get_actor_name that returns an actor’s full name:
您几乎可以用与调用存储过程相同的方式调用存储函数，只是您提供的是函数名而不是过程名。您使用 withFunctionName 方法作为配置的一部分来指示您想要调用一个函数，并生成一个函数调用的对应字符串。一个专门的调用（ executeFunction ）用于运行函数，并返回一个指定类型的对象作为函数的返回值，这意味着您不需要从结果映射中检索返回值。对于只有一个 out 参数的存储过程，也提供了一个类似的便利方法（命名为 executeObject ）。以下示例（针对 MySQL）基于一个名为 get_actor_name 的存储函数，该函数返回一个演员的全名：

CREATE FUNCTION get_actor_name (in_id INTEGER)
RETURNS VARCHAR(200) READS SQL DATA
BEGIN
    DECLARE out_name VARCHAR(200);
    SELECT concat(first_name, ' ', last_name)
        INTO out_name
        FROM t_actor where id = in_id;
    RETURN out_name;
END;

To call this function, we again create a SimpleJdbcCall in the initialization method, as the following example shows:
调用此函数时，我们再次在初始化方法中创建一个 SimpleJdbcCall ，如下例所示：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcCall funcGetActorName;

    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.funcGetActorName = new SimpleJdbcCall(jdbcTemplate)
                .withFunctionName("get_actor_name");
    }

    public String getActorName(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        String name = funcGetActorName.executeFunction(String.class, in);
        return name;
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

    private val jdbcTemplate = JdbcTemplate(dataSource).apply {
        isResultsMapCaseInsensitive = true
    }
    private val funcGetActorName = SimpleJdbcCall(jdbcTemplate)
            .withFunctionName("get_actor_name")

    fun getActorName(id: Long): String {
        val source = MapSqlParameterSource().addValue("in_id", id)
        return funcGetActorName.executeFunction(String::class.java, source)
    }

    // ... additional methods
}

The executeFunction method used returns a String that contains the return value from the function call.
该方法返回一个包含函数调用返回值的 String 。

(#jdbc-simple-jdbc-call-4)3.6.9. Returning a`ResultSet` or REF Cursor from a`SimpleJdbcCall`

3.6.9. 从 SimpleJdbcCall 返回 ResultSet 或 REF 光标

Calling a stored procedure or function that returns a result set is a bit tricky. Some databases return result sets during the JDBC results processing, while others require an explicitly registered out parameter of a specific type. Both approaches need additional processing to loop over the result set and process the returned rows. With the SimpleJdbcCall, you can use the returningResultSet method and declare a RowMapper implementation to be used for a specific parameter. If the result set is returned during the results processing, there are no names defined, so the returned results must match the order in which you declare the RowMapper implementations. The name specified is still used to store the processed list of results in the results map that is returned from the execute statement.
调用返回结果集的存储过程或函数有点棘手。一些数据库在 JDBC 结果处理期间返回结果集，而其他数据库则需要显式注册特定类型的 out 参数。两种方法都需要额外的处理来遍历结果集并处理返回的行。使用 SimpleJdbcCall ，您可以使用 returningResultSet 方法并声明一个 RowMapper 实现来用于特定参数。如果在结果处理期间返回结果集，则没有定义名称，因此返回的结果必须与您声明 RowMapper 实现的顺序相匹配。指定的名称仍然用于在从 execute 语句返回的结果映射中存储处理后的结果列表。

The next example (for MySQL) uses a stored procedure that takes no IN parameters and returns all rows from the t_actor table:
下一个示例（针对 MySQL）使用了一个不接受任何 IN 参数的存储过程，并返回 t_actor 表的所有行：

CREATE PROCEDURE read_all_actors()
BEGIN
 SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
END;

To call this procedure, you can declare the RowMapper. Because the class to which you want to map follows the JavaBean rules, you can use a BeanPropertyRowMapper that is created by passing in the required class to map to in the newInstance method. The following example shows how to do so:
调用此过程，您可以声明 RowMapper 。因为您想要映射到的类遵循 JavaBean 规则，您可以使用在 newInstance 方法中传递所需映射类创建的 BeanPropertyRowMapper 。以下示例展示了如何操作：

public class JdbcActorDao implements ActorDao {

    private SimpleJdbcCall procReadAllActors;

    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadAllActors = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_all_actors")
                .returningResultSet("actors",
                BeanPropertyRowMapper.newInstance(Actor.class));
    }

    public List getActorsList() {
        Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
        return (List) m.get("actors");
    }

    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {

        private val procReadAllActors = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
            isResultsMapCaseInsensitive = true
        }).withProcedureName("read_all_actors")
                .returningResultSet("actors",
                        BeanPropertyRowMapper.newInstance(Actor::class.java))

    fun getActorsList(): List<Actor> {
        val m = procReadAllActors.execute(mapOf<String, Any>())
        return m["actors"] as List<Actor>
    }

    // ... additional methods
}

The execute call passes in an empty Map, because this call does not take any parameters. The list of actors is then retrieved from the results map and returned to the caller.
该 execute 调用传递一个空的 Map ，因为这个调用不接受任何参数。然后从结果映射中检索演员列表，并将其返回给调用者。

(#jdbc-object)3.7. Modeling JDBC Operations as Java Objects

3.7. 将 JDBC 操作建模为 Java 对象

The org.springframework.jdbc.object package contains classes that let you access the database in a more object-oriented manner. As an example, you can run queries and get the results back as a list that contains business objects with the relational column data mapped to the properties of the business object.
该 org.springframework.jdbc.object 包含了让你以面向对象的方式访问数据库的类。例如，你可以运行查询并将结果作为包含将关系列数据映射到业务对象属性的商务对象的列表返回。
You can also run stored procedures and run update, delete, and insert statements.
您还可以运行存储过程，并执行更新、删除和插入语句。

Many Spring developers believe that the various RDBMS operation classes described below (with the exception of the StoredProcedure class) can often be replaced with straight JdbcTemplate calls. Often, it is simpler to write a DAO method that calls a method on a JdbcTemplate directly (as opposed to encapsulating a query as a full-blown class).
许多 Spring 开发者认为，以下描述的各种 RDBMS 操作类（除 StoredProcedure 类外）通常可以用直接的 JdbcTemplate 调用替代。通常，编写一个直接在 JdbcTemplate 上调用方法的 DAO 方法（而不是将查询封装为完整的类）会更简单。

However, if you are getting measurable value from using the RDBMS operation classes, you should continue to use these classes.
然而，如果您在使用 RDBMS 操作类中获得可衡量的价值，您应该继续使用这些类。

(#jdbc-SqlQuery)3.7.1. Understanding`SqlQuery` 3.7.1. 理解`SqlQuery`

SqlQuery is a reusable, thread-safe class that encapsulates an SQL query. Subclasses must implement the newRowMapper(..) method to provide a RowMapper instance that can create one object per row obtained from iterating over the ResultSet that is created during the execution of the query. The SqlQuery class is rarely used directly, because the MappingSqlQuery subclass provides a much more convenient implementation for mapping rows to Java classes. Other implementations that extend SqlQuery are MappingSqlQueryWithParameters and UpdatableSqlQuery.
SqlQuery 是一个可重用的、线程安全的类，它封装了一个 SQL 查询。子类必须实现 newRowMapper(..) 方法，以提供一个 RowMapper 实例，该实例可以创建一个对象，每次迭代查询执行期间创建的 ResultSet 中的一个对象。 SqlQuery 类很少直接使用，因为 MappingSqlQuery 子类提供了一个将行映射到 Java 类的更方便的实现。其他扩展 SqlQuery 的实现包括 MappingSqlQueryWithParameters 和 UpdatableSqlQuery 。

(#jdbc-MappingSqlQuery)3.7.2. Using`MappingSqlQuery` 3.7.2. 使用`MappingSqlQuery`

MappingSqlQuery is a reusable query in which concrete subclasses must implement the abstract mapRow(..) method to convert each row of the supplied ResultSet into an object of the type specified. The following example shows a custom query that maps the data from the t_actor relation to an instance of the Actor class:
MappingSqlQuery 是一个可重用的查询，其中具体的子类必须实现抽象的 mapRow(..) 方法，以将提供的 ResultSet 的每一行转换为指定类型的对象。以下示例展示了将 t_actor 关系中的数据映射到 Actor 类实例的自定义查询：

public class ActorMappingQuery extends MappingSqlQuery<Actor> {

    public ActorMappingQuery(DataSource ds) {
        super(ds, "select id, first_name, last_name from t_actor where id = ?");
        declareParameter(new SqlParameter("id", Types.INTEGER));
        compile();
    }

    @Override
    protected Actor mapRow(ResultSet rs, int rowNumber) throws SQLException {
        Actor actor = new Actor();
        actor.setId(rs.getLong("id"));
        actor.setFirstName(rs.getString("first_name"));
        actor.setLastName(rs.getString("last_name"));
        return actor;
    }
}

class ActorMappingQuery(ds: DataSource) : MappingSqlQuery<Actor>(ds, "select id, first_name, last_name from t_actor where id = ?") {

    init {
        declareParameter(SqlParameter("id", Types.INTEGER))
        compile()
    }

    override fun mapRow(rs: ResultSet, rowNumber: Int) = Actor(
            rs.getLong("id"),
            rs.getString("first_name"),
            rs.getString("last_name")
    )
}

The class extends MappingSqlQuery parameterized with the Actor type. The constructor for this customer query takes a DataSource as the only parameter. In this constructor, you can call the constructor on the superclass with the DataSource and the SQL that should be run to retrieve the rows for this query. This SQL is used to create a PreparedStatement, so it may contain placeholders for any parameters to be passed in during execution. You must declare each parameter by using the declareParameter method passing in an SqlParameter. The SqlParameter takes a name, and the JDBC type as defined in java.sql.Types. After you define all parameters, you can call the compile() method so that the statement can be prepared and later run. This class is thread-safe after it is compiled, so, as long as these instances are created when the DAO is initialized, they can be kept as instance variables and be reused.
该类扩展了由 Actor 类型参数化的 MappingSqlQuery 。这个客户查询的构造函数只接受一个 DataSource 作为参数。在这个构造函数中，你可以使用 DataSource 调用超类的构造函数，以及执行此查询时应运行的 SQL。此 SQL 用于创建一个 PreparedStatement ，因此它可能包含在执行期间传递的任何参数的占位符。你必须通过使用传递一个 SqlParameter 的 declareParameter 方法来声明每个参数。 SqlParameter 接受一个名称和定义在 java.sql.Types 中的 JDBC 类型。在定义所有参数后，你可以调用 compile() 方法，以便可以准备并稍后运行该语句。该类在编译后是线程安全的，因此，只要这些实例在 DAO 初始化时创建，它们就可以作为实例变量保留并重用。
The following example shows how to define such a class:
以下示例展示了如何定义此类：

private ActorMappingQuery actorMappingQuery;

@Autowired
public void setDataSource(DataSource dataSource) {
    this.actorMappingQuery = new ActorMappingQuery(dataSource);
}

public Customer getCustomer(Long id) {
    return actorMappingQuery.findObject(id);
}

private val actorMappingQuery = ActorMappingQuery(dataSource)

fun getCustomer(id: Long) = actorMappingQuery.findObject(id)

The method in the preceding example retrieves the customer with the id that is passed in as the only parameter. Since we want only one object to be returned, we call the findObject convenience method with the id as the parameter. If we had instead a query that returned a list of objects and took additional parameters, we would use one of the execute methods that takes an array of parameter values passed in as varargs. The following example shows such a method:
前一个示例中的方法通过传入的唯一参数检索具有 id 的客户。由于我们只想返回一个对象，所以我们使用 id 作为参数调用 findObject 便捷方法。如果我们有一个返回对象列表并接受额外参数的查询，我们会使用接受作为 varargs 传入的参数值数组的 execute 方法之一。以下示例显示了这样一个方法：

public List<Actor> searchForActors(int age, String namePattern) {
    List<Actor> actors = actorSearchMappingQuery.execute(age, namePattern);
    return actors;
}

fun searchForActors(age: Int, namePattern: String) =
            actorSearchMappingQuery.execute(age, namePattern)

(#jdbc-SqlUpdate)3.7.3. Using`SqlUpdate` 3.7.3. 使用`SqlUpdate`

The SqlUpdate class encapsulates an SQL update. As with a query, an update object is reusable, and, as with all RdbmsOperation classes, an update can have parameters and is defined in SQL. This class provides a number of update(..) methods analogous to the execute(..) methods of query objects. The SqlUpdate class is concrete. It can be subclassed — for example, to add a custom update method. However, you do not have to subclass the SqlUpdate class, since it can easily be parameterized by setting SQL and declaring parameters. The following example creates a custom update method named execute:
该 SqlUpdate 类封装了一个 SQL 更新。与查询一样，更新对象是可重用的，并且，与所有 RdbmsOperation 类一样，更新可以有参数并在 SQL 中定义。此类提供了一些类似于查询对象 execute(..) 方法的 update(..) 方法。 SqlUpdate 类是具体的。它可以被继承——例如，添加自定义更新方法。然而，您不必继承 SqlUpdate 类，因为它可以通过设置 SQL 和声明参数轻松地进行参数化。以下示例创建了一个名为 execute 的自定义更新方法：

import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

public class UpdateCreditRating extends SqlUpdate {

    public UpdateCreditRating(DataSource ds) {
        setDataSource(ds);
        setSql("update customer set credit_rating = ? where id = ?");
        declareParameter(new SqlParameter("creditRating", Types.NUMERIC));
        declareParameter(new SqlParameter("id", Types.NUMERIC));
        compile();
    }

    /**
     * @param id for the Customer to be updated
     * @param rating the new value for credit rating
     * @return number of rows updated
     */
    public int execute(int id, int rating) {
        return update(rating, id);
    }
}

import java.sql.Types
import javax.sql.DataSource
import org.springframework.jdbc.core.SqlParameter
import org.springframework.jdbc.object.SqlUpdate

class UpdateCreditRating(ds: DataSource) : SqlUpdate() {

    init {
        setDataSource(ds)
        sql = "update customer set credit_rating = ? where id = ?"
        declareParameter(SqlParameter("creditRating", Types.NUMERIC))
        declareParameter(SqlParameter("id", Types.NUMERIC))
        compile()
    }

    /**
     * @param id for the Customer to be updated
     * @param rating the new value for credit rating
     * @return number of rows updated
     */
    fun execute(id: Int, rating: Int): Int {
        return update(rating, id)
    }
}

(#jdbc-StoredProcedure)3.7.4. Using`StoredProcedure` 3.7.4. 使用`StoredProcedure`

The StoredProcedure class is an abstract superclass for object abstractions of RDBMS stored procedures.
StoredProcedure 类是 RDBMS 存储过程对象抽象的超类。

The inherited sql property is the name of the stored procedure in the RDBMS.
继承的 sql 属性是 RDBMS 中存储过程的名称。

To define a parameter for the StoredProcedure class, you can use an SqlParameter or one of its subclasses. You must specify the parameter name and SQL type in the constructor, as the following code snippet shows:
为了定义 StoredProcedure 类的参数，您可以使用 SqlParameter 或其子类之一。您必须在构造函数中指定参数名称和 SQL 类型，如下代码片段所示：

new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),

SqlParameter("in_id", Types.NUMERIC),
SqlOutParameter("out_first_name", Types.VARCHAR),

The SQL type is specified using the java.sql.Types constants.
SQL 类型使用 java.sql.Types 常量指定。

The first line (with the SqlParameter) declares an IN parameter. You can use IN parameters both for stored procedure calls and for queries using the SqlQuery and its subclasses (covered in Understanding SqlQuery).
第一行（带有 SqlParameter ）声明了一个 IN 参数。您可以在存储过程调用和查询中使用 IN 参数，包括 SqlQuery 及其子类（在《理解 SqlQuery 》中介绍）。

The second line (with the SqlOutParameter) declares an out parameter to be used in the stored procedure call. There is also an SqlInOutParameter for InOut parameters (parameters that provide an in value to the procedure and that also return a value).
第二行（带有 SqlOutParameter ）声明了一个 out 参数，用于在存储过程调用中使用。还有一个 SqlInOutParameter 用于 InOut 参数（提供 in 值给过程并返回值的参数）。

For in parameters, in addition to the name and the SQL type, you can specify a scale for numeric data or a type name for custom database types. For out parameters, you can provide a RowMapper to handle mapping of rows returned from a REF cursor. Another option is to specify an SqlReturnType that lets you define customized handling of the return values.
对于 in 参数，除了名称和 SQL 类型外，您还可以指定数值数据的刻度或自定义数据库类型的类型名称。对于 out 参数，您可以提供一个 RowMapper 来处理从 REF 游标返回的行映射。另一种选择是指定一个 SqlReturnType ，让您能够定义自定义的返回值处理方式。

The next example of a simple DAO uses a StoredProcedure to call a function (sysdate()), which comes with any Oracle database. To use the stored procedure functionality, you have to create a class that extends StoredProcedure. In this example, the StoredProcedure class is an inner class. However, if you need to reuse the StoredProcedure, you can declare it as a top-level class. This example has no input parameters, but an output parameter is declared as a date type by using the SqlOutParameter class. The execute() method runs the procedure and extracts the returned date from the results Map. The results Map has an entry for each declared output parameter (in this case, only one) by using the parameter name as the key. The following listing shows our custom StoredProcedure class:
下一个简单的 DAO 示例使用 StoredProcedure 调用一个函数（ sysdate() ），该函数是任何 Oracle 数据库都有的。要使用存储过程功能，你必须创建一个扩展 StoredProcedure 的类。在这个例子中， StoredProcedure 类是一个内部类。但是，如果你需要重用 StoredProcedure ，你可以将其声明为一个顶级类。这个例子没有输入参数，但通过使用 SqlOutParameter 类声明了一个日期类型的输出参数。 execute() 方法运行该过程并从结果 Map 中提取返回的日期。结果 Map 为每个声明的输出参数（在这种情况下，只有一个）提供了一个条目，使用参数名作为键。以下列表显示了我们的自定义 StoredProcedure 类：

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class StoredProcedureDao {

    private GetSysdateProcedure getSysdate;

    @Autowired
    public void init(DataSource dataSource) {
        this.getSysdate = new GetSysdateProcedure(dataSource);
    }

    public Date getSysdate() {
        return getSysdate.execute();
    }

    private class GetSysdateProcedure extends StoredProcedure {

        private static final String SQL = "sysdate";

        public GetSysdateProcedure(DataSource dataSource) {
            setDataSource(dataSource);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("date", Types.DATE));
            compile();
        }

        public Date execute() {
            // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
            Map<String, Object> results = execute(new HashMap<String, Object>());
            Date sysdate = (Date) results.get("date");
            return sysdate;
        }
    }

}

import java.sql.Types
import java.util.Date
import java.util.Map
import javax.sql.DataSource
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.object.StoredProcedure

class StoredProcedureDao(dataSource: DataSource) {

    private val SQL = "sysdate"

    private val getSysdate = GetSysdateProcedure(dataSource)

    val sysdate: Date
        get() = getSysdate.execute()

    private inner class GetSysdateProcedure(dataSource: DataSource) : StoredProcedure() {

        init {
            setDataSource(dataSource)
            isFunction = true
            sql = SQL
            declareParameter(SqlOutParameter("date", Types.DATE))
            compile()
        }

        fun execute(): Date {
            // the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
            val results = execute(mutableMapOf<String, Any>())
            return results["date"] as Date
        }
    }
}

The following example of a StoredProcedure has two output parameters (in this case, Oracle REF cursors):
以下 StoredProcedure 的示例有两个输出参数（在这种情况下，Oracle REF cursors）：

import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class TitlesAndGenresStoredProcedure extends StoredProcedure {

    private static final String SPROC_NAME = "AllTitlesAndGenres";

    public TitlesAndGenresStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        declareParameter(new SqlOutParameter("genres", OracleTypes.CURSOR, new GenreMapper()));
        compile();
    }

    public Map<String, Object> execute() {
        // again, this sproc has no input parameters, so an empty Map is supplied
        return super.execute(new HashMap<String, Object>());
    }
}

import java.util.HashMap
import javax.sql.DataSource
import oracle.jdbc.OracleTypes
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.object.StoredProcedure

class TitlesAndGenresStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, SPROC_NAME) {

    companion object {
        private const val SPROC_NAME = "AllTitlesAndGenres"
    }

    init {
        declareParameter(SqlOutParameter("titles", OracleTypes.CURSOR, TitleMapper()))
        declareParameter(SqlOutParameter("genres", OracleTypes.CURSOR, GenreMapper()))
        compile()
    }

    fun execute(): Map<String, Any> {
        // again, this sproc has no input parameters, so an empty Map is supplied
        return super.execute(HashMap<String, Any>())
    }
}

Notice how the overloaded variants of the declareParameter(..) method that have been used in the TitlesAndGenresStoredProcedure constructor are passed RowMapper implementation instances. This is a very convenient and powerful way to reuse existing functionality. The next two examples provide code for the two RowMapper implementations.
methods of the declareParameter(..) method that have been used in the TitlesAndGenresStoredProcedure constructor are passed RowMapper implementation instances. 这是一种非常方便且强大的重用现有功能的方式。接下来的两个示例提供了两个 RowMapper 实现的代码。

The TitleMapper class maps a ResultSet to a Title domain object for each row in the supplied ResultSet, as follows:
该 TitleMapper 类将提供的 ResultSet 中的每一行映射到 Title 域对象，如下所示：

import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Title;
import org.springframework.jdbc.core.RowMapper;

public final class TitleMapper implements RowMapper<Title> {

    public Title mapRow(ResultSet rs, int rowNum) throws SQLException {
        Title title = new Title();
        title.setId(rs.getLong("id"));
        title.setName(rs.getString("name"));
        return title;
    }
}

import java.sql.ResultSet
import com.foo.domain.Title
import org.springframework.jdbc.core.RowMapper

class TitleMapper : RowMapper<Title> {

    override fun mapRow(rs: ResultSet, rowNum: Int) =
            Title(rs.getLong("id"), rs.getString("name"))
}

The GenreMapper class maps a ResultSet to a Genre domain object for each row in the supplied ResultSet, as follows:
该 GenreMapper 类将提供的 ResultSet 中的每一行映射到 Genre 域对象，如下所示：

import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Genre;
import org.springframework.jdbc.core.RowMapper;

public final class GenreMapper implements RowMapper<Genre> {

    public Genre mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Genre(rs.getString("name"));
    }
}

import java.sql.ResultSet
import com.foo.domain.Genre
import org.springframework.jdbc.core.RowMapper

class GenreMapper : RowMapper<Genre> {

    override fun mapRow(rs: ResultSet, rowNum: Int): Genre {
        return Genre(rs.getString("name"))
    }
}

To pass parameters to a stored procedure that has one or more input parameters in its definition in the RDBMS, you can code a strongly typed execute(..) method that would delegate to the untyped execute(Map) method in the superclass, as the following example shows:
要将参数传递给在 RDBMS 定义中包含一个或多个输入参数的存储过程，您可以编码一个强类型 execute(..) 方法，该方法将委派给超类中的无类型 execute(Map) 方法，如下例所示：

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class TitlesAfterDateStoredProcedure extends StoredProcedure {

    private static final String SPROC_NAME = "TitlesAfterDate";
    private static final String CUTOFF_DATE_PARAM = "cutoffDate";

    public TitlesAfterDateStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlParameter(CUTOFF_DATE_PARAM, Types.DATE);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        compile();
    }

    public Map<String, Object> execute(Date cutoffDate) {
        Map<String, Object> inputs = new HashMap<String, Object>();
        inputs.put(CUTOFF_DATE_PARAM, cutoffDate);
        return super.execute(inputs);
    }
}

import java.sql.Types
import java.util.Date
import javax.sql.DataSource
import oracle.jdbc.OracleTypes
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.core.SqlParameter
import org.springframework.jdbc.object.StoredProcedure

class TitlesAfterDateStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, SPROC_NAME) {

    companion object {
        private const val SPROC_NAME = "TitlesAfterDate"
        private const val CUTOFF_DATE_PARAM = "cutoffDate"
    }

    init {
        declareParameter(SqlParameter(CUTOFF_DATE_PARAM, Types.DATE))
        declareParameter(SqlOutParameter("titles", OracleTypes.CURSOR, TitleMapper()))
        compile()
    }

    fun execute(cutoffDate: Date) = super.execute(
            mapOf<String, Any>(CUTOFF_DATE_PARAM to cutoffDate))
}

(#jdbc-parameter-handling)3.8. Common Problems with Parameter and Data Value Handling

3.8. 参数和数据值处理中的常见问题

Common problems with parameters and data values exist in the different approaches provided by Spring Framework’s JDBC support. This section covers how to address them.
Spring 框架的 JDBC 支持提供的不同方法中存在参数和数据值方面的常见问题。本节将介绍如何解决这些问题。

(#jdbc-type-information)3.8.1. Providing SQL Type Information for Parameters

3.8.1. 为参数提供 SQL 类型信息

Usually, Spring determines the SQL type of the parameters based on the type of parameter passed in. It is possible to explicitly provide the SQL type to be used when setting parameter values. This is sometimes necessary to correctly set NULL values.
通常，Spring 根据传入参数的类型确定 SQL 参数类型。在设置参数值时，可以显式提供要使用的 SQL 类型。这有时是正确设置 NULL 值所必需的。

You can provide SQL type information in several ways:
您可以通过多种方式提供 SQL 类型信息：

Many update and query methods of the JdbcTemplate take an additional parameter in the form of an int array. This array is used to indicate the SQL type of the corresponding parameter by using constant values from the java.sql.Types class. Provide one entry for each parameter.
许多 JdbcTemplate 的更新和查询方法接受一个额外的参数，该参数以 int 数组的形式存在。此数组通过使用 java.sql.Types 类中的常量值来指示相应参数的 SQL 类型。为每个参数提供一个条目。
You can use the SqlParameterValue class to wrap the parameter value that needs this additional information. To do so, create a new instance for each value and pass in the SQL type and the parameter value in the constructor. You can also provide an optional scale parameter for numeric values.
您可以使用 SqlParameterValue 类来包装需要此附加信息的参数值。为此，为每个值创建一个新实例，并在构造函数中传入 SQL 类型和参数值。您还可以为数值提供可选的缩放参数。
For methods that work with named parameters, you can use the SqlParameterSource classes, BeanPropertySqlParameterSource or MapSqlParameterSource. They both have methods for registering the SQL type for any of the named parameter values.
对于使用命名参数的方法，您可以使用 SqlParameterSource 类、 BeanPropertySqlParameterSource 或 MapSqlParameterSource 。它们都为任何命名参数值注册 SQL 类型的方法。

(#jdbc-lob)3.8.2. Handling BLOB and CLOB objects

3.8.2. 处理 BLOB 和 CLOB 对象

You can store images, other binary data, and large chunks of text in the database. These large objects are called BLOBs (Binary Large OBject) for binary data and CLOBs (Character Large OBject) for character data. In Spring, you can handle these large objects by using the JdbcTemplate directly and also when using the higher abstractions provided by RDBMS Objects and the SimpleJdbc classes. All of these approaches use an implementation of the LobHandler interface for the actual management of the LOB (Large OBject) data. LobHandler provides access to a LobCreator class, through the getLobCreator method, that is used for creating new LOB objects to be inserted.
您可以在数据库中存储图像、其他二进制数据和大量文本。这些大型对象被称为 BLOB（二进制大对象）和 CLOB（字符大对象）。在 Spring 中，您可以通过直接使用 JdbcTemplate 以及使用 RDBMS 对象和 SimpleJdbc 类提供的更高抽象来处理这些大型对象。所有这些方法都使用 LobHandler 接口的实际实现来管理 LOB（大型对象）数据。 LobHandler 通过 getLobCreator 方法提供对 LobCreator 类的访问，用于创建要插入的新 LOB 对象。

LobCreator and LobHandler provide the following support for LOB input and output:
LobCreator 和 LobHandler 为 LOB 输入和输出提供以下支持：

BLOB
- byte: getBlobAsBytes and setBlobAsBytes
  byte : getBlobAsBytes 和 setBlobAsBytes
- InputStream: getBlobAsBinaryStream and setBlobAsBinaryStream
  InputStream : getBlobAsBinaryStream 和 setBlobAsBinaryStream
CLOB
- String: getClobAsString and setClobAsString
  String : getClobAsString 和 setClobAsString
- InputStream: getClobAsAsciiStream and setClobAsAsciiStream
  InputStream : getClobAsAsciiStream 和 setClobAsAsciiStream
- Reader: getClobAsCharacterStream and setClobAsCharacterStream
  Reader : getClobAsCharacterStream 和 setClobAsCharacterStream

The next example shows how to create and insert a BLOB. Later we show how to read it back from the database.
下一个示例展示了如何创建和插入一个 BLOB。稍后我们将展示如何从数据库中读取它。

This example uses a JdbcTemplate and an implementation of the AbstractLobCreatingPreparedStatementCallback. It implements one method, setValues. This method provides a LobCreator that we use to set the values for the LOB columns in your SQL insert statement.
此示例使用了一个 JdbcTemplate 和一个 AbstractLobCreatingPreparedStatementCallback 的实现。它实现了一个 setValues 方法。此方法提供了一个 LobCreator ，我们用它来设置 SQL 插入语句中 LOB 列的值。

For this example, we assume that there is a variable, lobHandler, that is already set to an instance of a DefaultLobHandler. You typically set this value through dependency injection.
在这个例子中，我们假设有一个变量 lobHandler ，它已经被设置为 DefaultLobHandler 的一个实例。你通常通过依赖注入来设置这个值。

The following example shows how to create and insert a BLOB:
以下示例展示了如何创建和插入一个 BLOB：

final File blobIn = new File("spring2004.jpg");
final InputStream blobIs = new FileInputStream(blobIn);
final File clobIn = new File("large.txt");
final InputStream clobIs = new FileInputStream(clobIn);
final InputStreamReader clobReader = new InputStreamReader(clobIs);

jdbcTemplate.execute(
    "INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {  (1)
        protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
            ps.setLong(1, 1L);
            lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int)clobIn.length());  (2)
            lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int)blobIn.length());  (3)
        }
    }
);

blobIs.close();
clobReader.close();

Pass in the lobHandler that (in this example) is a plain DefaultLobHandler.
传入 lobHandler （在这个例子中）是一个普通的 DefaultLobHandler 。

Using the method setClobAsCharacterStream to pass in the contents of the CLOB.
使用方法 setClobAsCharacterStream 传入 CLOB 的内容。

Using the method setBlobAsBinaryStream to pass in the contents of the BLOB.
使用方法 setBlobAsBinaryStream 传入 BLOB 的内容。

val blobIn = File("spring2004.jpg")
val blobIs = FileInputStream(blobIn)
val clobIn = File("large.txt")
val clobIs = FileInputStream(clobIn)
val clobReader = InputStreamReader(clobIs)

jdbcTemplate.execute(
        "INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
        object: AbstractLobCreatingPreparedStatementCallback(lobHandler) {  (1)
            override fun setValues(ps: PreparedStatement, lobCreator: LobCreator) {
                ps.setLong(1, 1L)
                lobCreator.setClobAsCharacterStream(ps, 2, clobReader, clobIn.length().toInt())  (2)
                lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, blobIn.length().toInt())  (3)
            }
        }
)
blobIs.close()
clobReader.close()

Pass in the lobHandler that (in this example) is a plain DefaultLobHandler.

Using the method setClobAsCharacterStream to pass in the contents of the CLOB.

Using the method setBlobAsBinaryStream to pass in the contents of the BLOB.

If you invoke the setBlobAsBinaryStream, setClobAsAsciiStream, or setClobAsCharacterStream method on the LobCreator returned from DefaultLobHandler.getLobCreator(), you can optionally specify a negative value for the contentLength argument. If the specified content length is negative, the DefaultLobHandler uses the JDBC 4.0 variants of the set-stream methods without a length parameter. Otherwise, it passes the specified length on to the driver.
如果您在 DefaultLobHandler.getLobCreator() 返回的 LobCreator 上调用 setBlobAsBinaryStream 、 setClobAsAsciiStream 或 setClobAsCharacterStream 方法，您可以可选地为 contentLength 参数指定一个负值。如果指定的内容长度为负， DefaultLobHandler 将使用不带长度参数的 JDBC 4.0 版本的 set-stream 方法。否则，它将把指定的长度传递给驱动程序。

See the documentation for the JDBC driver you use to verify that it supports streaming a LOB without providing the content length.
查看您使用的 JDBC 驱动程序的文档，以验证它是否支持在不提供内容长度的情况下流式传输 LOB。

Now it is time to read the LOB data from the database. Again, you use a JdbcTemplate with the same instance variable lobHandler and a reference to a DefaultLobHandler. The following example shows how to do so:
现在是从数据库读取 LOB 数据的时候了。再次，你使用相同的实例变量 lobHandler 和一个对 DefaultLobHandler 的引用。以下示例展示了如何操作：

List<Map<String, Object>> l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table",
    new RowMapper<Map<String, Object>>() {
        public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException {
            Map<String, Object> results = new HashMap<String, Object>();
            String clobText = lobHandler.getClobAsString(rs, "a_clob");  (1)
            results.put("CLOB", clobText);
            byte blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");  (2)
            results.put("BLOB", blobBytes);
            return results;
        }
    });

Using the method getClobAsString to retrieve the contents of the CLOB.
使用方法 getClobAsString 获取 CLOB 的内容。

Using the method getBlobAsBytes to retrieve the contents of the BLOB.
使用方法 getBlobAsBytes 获取 BLOB 的内容。

val l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table") { rs, _ ->
    val clobText = lobHandler.getClobAsString(rs, "a_clob")  (1)
    val blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob")  (2)
    mapOf("CLOB" to clobText, "BLOB" to blobBytes)
}

Using the method getClobAsString to retrieve the contents of the CLOB.

Using the method getBlobAsBytes to retrieve the contents of the BLOB.

(#jdbc-in-clause)3.8.3. Passing in Lists of Values for IN Clause

3.8.3. 为 IN 子句传递值列表

The SQL standard allows for selecting rows based on an expression that includes a variable list of values. A typical example would be select * from t_actor where id in (1, 2, 3). This variable list is not directly supported for prepared statements by the JDBC standard. You cannot declare a variable number of placeholders.
SQL 标准允许根据包含变量值列表的表达式选择行。一个典型的例子是 select * from t_actor where id in (1, 2, 3) 。此变量列表不是由 JDBC 标准直接支持的。您不能声明可变数量的占位符。
You need a number of variations with the desired number of placeholders prepared, or you need to generate the SQL string dynamically once you know how many placeholders are required. The named parameter support provided in the NamedParameterJdbcTemplate takes the latter approach. You can pass in the values as a java.util.List (or any Iterable) of simple values. This list is used to insert the required placeholders into the actual SQL statement and pass in the values during statement execution.
您需要准备一定数量的具有所需占位符数量的变体，或者一旦知道需要多少个占位符，就需要动态生成 SQL 字符串。 NamedParameterJdbcTemplate 提供的命名参数支持采用后一种方法。您可以将值作为 java.util.List （或任何 Iterable ）的简单值传入。此列表用于将所需的占位符插入到实际的 SQL 语句中，并在执行语句时传入值。

Be careful when passing in many values. The JDBC standard does not guarantee that you can use more than 100 values for an IN expression list. Various databases exceed this number, but they usually have a hard limit for how many values are allowed. For example, Oracle’s limit is 1000.
请注意在传递多个值时。JDBC 标准不保证您可以使用超过 100 个值用于 IN 表达式列表。各种数据库都超过了这个数字，但它们通常对允许的值的数量有一个硬性限制。例如，Oracle 的限制是 1000。

In addition to the primitive values in the value list, you can create a java.util.List of object arrays. This list can support multiple expressions being defined for the in clause, such as select * from t_actor where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop')). This, of course, requires that your database supports this syntax.
除了值列表中的原始值之外，您还可以创建一个对象数组 java.util.List 。此列表可以支持为 in 子句定义多个表达式，例如 select * from t_actor where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop')) 。当然，这要求您的数据库支持此语法。

(#jdbc-complex-types)3.8.4. Handling Complex Types for Stored Procedure Calls

3.8.4. 处理存储过程调用中的复杂类型

When you call stored procedures, you can sometimes use complex types specific to the database. To accommodate these types, Spring provides a SqlReturnType for handling them when they are returned from the stored procedure call and SqlTypeValue when they are passed in as a parameter to the stored procedure.
调用存储过程时，有时可以使用特定于数据库的复杂类型。为了适应这些类型，Spring 提供了 SqlReturnType 用于处理从存储过程调用返回的类型，以及 SqlTypeValue 用于将它们作为参数传递给存储过程。

The SqlReturnType interface has a single method (named getTypeValue) that must be implemented. This interface is used as part of the declaration of an SqlOutParameter. The following example shows returning the value of an Oracle STRUCT object of the user declared type ITEM_TYPE:
SqlReturnType 接口有一个必须实现的方法（命名为 getTypeValue ）。该接口用作声明 SqlOutParameter 的一部分。以下示例显示了返回用户声明的类型 ITEM_TYPE 的 Oracle STRUCT 对象的值：

public class TestItemStoredProcedure extends StoredProcedure {

    public TestItemStoredProcedure(DataSource dataSource) {
        // ...
        declareParameter(new SqlOutParameter("item", OracleTypes.STRUCT, "ITEM_TYPE",
            (CallableStatement cs, int colIndx, int sqlType, String typeName) -> {
                STRUCT struct = (STRUCT) cs.getObject(colIndx);
                Object attr = struct.getAttributes();
                TestItem item = new TestItem();
                item.setId(((Number) attr[0]).longValue());
                item.setDescription((String) attr[1]);
                item.setExpirationDate((java.util.Date) attr[2]);
                return item;
            }));
        // ...
    }

class TestItemStoredProcedure(dataSource: DataSource) : StoredProcedure() {

    init {
        // ...
        declareParameter(SqlOutParameter("item", OracleTypes.STRUCT, "ITEM_TYPE") { cs, colIndx, sqlType, typeName ->
            val struct = cs.getObject(colIndx) as STRUCT
            val attr = struct.getAttributes()
            TestItem((attr[0] as Long, attr[1] as String, attr[2] as Date)
        })
        // ...
    }
}

You can use SqlTypeValue to pass the value of a Java object (such as TestItem) to a stored procedure. The SqlTypeValue interface has a single method (named createTypeValue) that you must implement. The active connection is passed in, and you can use it to create database-specific objects, such as StructDescriptor instances or ArrayDescriptor instances. The following example creates a StructDescriptor instance:
您可以使用 SqlTypeValue 来传递 Java 对象（如 TestItem ）的值到存储过程。 SqlTypeValue 接口有一个必须实现的方法（命名为 createTypeValue ）。活动连接被传递进来，您可以使用它来创建数据库特定的对象，例如 StructDescriptor 实例或 ArrayDescriptor 实例。以下示例创建了一个 StructDescriptor 实例：

final TestItem testItem = new TestItem(123L, "A test item",
        new SimpleDateFormat("yyyy-M-d").parse("2010-12-31"));

SqlTypeValue value = new AbstractSqlTypeValue() {
    protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
        StructDescriptor itemDescriptor = new StructDescriptor(typeName, conn);
        Struct item = new STRUCT(itemDescriptor, conn,
        new Object {
            testItem.getId(),
            testItem.getDescription(),
            new java.sql.Date(testItem.getExpirationDate().getTime())
        });
        return item;
    }
};

val (id, description, expirationDate) = TestItem(123L, "A test item",
        SimpleDateFormat("yyyy-M-d").parse("2010-12-31"))

val value = object : AbstractSqlTypeValue() {
    override fun createTypeValue(conn: Connection, sqlType: Int, typeName: String?): Any {
        val itemDescriptor = StructDescriptor(typeName, conn)
        return STRUCT(itemDescriptor, conn,
                arrayOf(id, description, java.sql.Date(expirationDate.time)))
    }
}

You can now add this SqlTypeValue to the Map that contains the input parameters for the execute call of the stored procedure.
您现在可以将此 SqlTypeValue 添加到包含存储过程 execute 调用输入参数的 Map 中。

Another use for the SqlTypeValue is passing in an array of values to an Oracle stored procedure. Oracle has its own internal ARRAY class that must be used in this case, and you can use the SqlTypeValue to create an instance of the Oracle ARRAY and populate it with values from the Java ARRAY, as the following example shows:
另一个使用 SqlTypeValue 的用途是将值数组传递给 Oracle 存储过程。Oracle 有一个内部使用的 ARRAY 类，在这种情况下必须使用它，您可以使用 SqlTypeValue 创建 Oracle ARRAY 的实例，并用 Java ARRAY 中的值填充它，如下例所示：

final Long ids = new Long {1L, 2L};

SqlTypeValue value = new AbstractSqlTypeValue() {
    protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
        ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, conn);
        ARRAY idArray = new ARRAY(arrayDescriptor, conn, ids);
        return idArray;
    }
};

class TestItemStoredProcedure(dataSource: DataSource) : StoredProcedure() {

    init {
        val ids = arrayOf(1L, 2L)
        val value = object : AbstractSqlTypeValue() {
            override fun createTypeValue(conn: Connection, sqlType: Int, typeName: String?): Any {
                val arrayDescriptor = ArrayDescriptor(typeName, conn)
                return ARRAY(arrayDescriptor, conn, ids)
            }
        }
    }
}

(#jdbc-embedded-database-support)3.9. Embedded Database Support

3.9. 嵌入式数据库支持

The org.springframework.jdbc.datasource.embedded package provides support for embedded Java database engines. Support for HSQL, H2, and Derby is provided natively. You can also use an extensible API to plug in new embedded database types and DataSource implementations.
The org.springframework.jdbc.datasource.embedded package provides support for embedded Java database engines. Support for HSQL, H2, and Derby is provided natively. You can also use an extensible API to plug in new embedded database types and DataSource implementations. 该 org.springframework.jdbc.datasource.embedded 包提供对嵌入式 Java 数据库引擎的支持。原生支持 HSQL、H2 和 Derby。您还可以使用可扩展的 API 插入新的嵌入式数据库类型和 DataSource 实现。

(#jdbc-why-embedded-database)3.9.1. Why Use an Embedded Database?

3.9.1. 为什么使用嵌入式数据库？

An embedded database can be useful during the development phase of a project because of its lightweight nature. Benefits include ease of configuration, quick startup time, testability, and the ability to rapidly evolve your SQL during development.
嵌入式数据库在项目开发阶段可能很有用，因为它轻量级。其优点包括配置简单、启动速度快、可测试性和在开发过程中快速演变 SQL 的能力。

(#jdbc-embedded-database-xml)3.9.2. Creating an Embedded Database by Using Spring XML

3.9.2. 使用 Spring XML 创建嵌入式数据库

If you want to expose an embedded database instance as a bean in a Spring ApplicationContext, you can use the embedded-database tag in the spring-jdbc namespace:
如果您想将嵌入式数据库实例作为 bean 暴露在 Spring ApplicationContext 中，您可以使用 spring-jdbc 命名空间中的 embedded-database 标签：

<jdbc:embedded-database id="dataSource" generate-name="true">
    <jdbc:script location="classpath:schema.sql"/>
    <jdbc:script location="classpath:test-data.sql"/>
</jdbc:embedded-database>

The preceding configuration creates an embedded HSQL database that is populated with SQL from the schema.sql and test-data.sql resources in the root of the classpath. In addition, as a best practice, the embedded database is assigned a uniquely generated name. The embedded database is made available to the Spring container as a bean of type javax.sql.DataSource that can then be injected into data access objects as needed.
前一个配置创建了一个嵌入式的 HSQL 数据库，该数据库使用类路径根目录中的 schema.sql 和 test-data.sql 资源中的 SQL 进行填充。此外，作为最佳实践，嵌入的数据库被分配了一个唯一生成的名称。嵌入的数据库作为类型为 javax.sql.DataSource 的 bean 提供给 Spring 容器，然后可以根据需要将其注入到数据访问对象中。

(#jdbc-embedded-database-java)3.9.3. Creating an Embedded Database Programmatically

3.9.3. 以编程方式创建嵌入式数据库

The EmbeddedDatabaseBuilder class provides a fluent API for constructing an embedded database programmatically. You can use this when you need to create an embedded database in a stand-alone environment or in a stand-alone integration test, as in the following example:
EmbeddedDatabaseBuilder 类提供了一个流畅的 API 来程序化地构建嵌入式数据库。您可以在需要创建独立环境或独立集成测试中的嵌入式数据库时使用此 API，如下例所示：

EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
        .generateUniqueName(true)
        .setType(H2)
        .setScriptEncoding("UTF-8")
        .ignoreFailedDrops(true)
        .addScript("schema.sql")
        .addScripts("user_data.sql", "country_data.sql")
        .build();

// perform actions against the db (EmbeddedDatabase extends javax.sql.DataSource)

db.shutdown()

val db = EmbeddedDatabaseBuilder()
        .generateUniqueName(true)
        .setType(H2)
        .setScriptEncoding("UTF-8")
        .ignoreFailedDrops(true)
        .addScript("schema.sql")
        .addScripts("user_data.sql", "country_data.sql")
        .build()

// perform actions against the db (EmbeddedDatabase extends javax.sql.DataSource)

db.shutdown()

See the javadoc for EmbeddedDatabaseBuilder for further details on all supported options.
查看 EmbeddedDatabaseBuilder 的 javadoc 以获取所有支持选项的详细信息。

You can also use the EmbeddedDatabaseBuilder to create an embedded database by using Java configuration, as the following example shows:
您也可以使用 EmbeddedDatabaseBuilder 通过 Java 配置创建嵌入式数据库，如下例所示：

@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .setType(H2)
                .setScriptEncoding("UTF-8")
                .ignoreFailedDrops(true)
                .addScript("schema.sql")
                .addScripts("user_data.sql", "country_data.sql")
                .build();
    }
}

@Configuration
class DataSourceConfig {

    @Bean
    fun dataSource(): DataSource {
        return EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .setType(H2)
                .setScriptEncoding("UTF-8")
                .ignoreFailedDrops(true)
                .addScript("schema.sql")
                .addScripts("user_data.sql", "country_data.sql")
                .build()
    }
}

(#jdbc-embedded-database-types)3.9.4. Selecting the Embedded Database Type

3.9.4. 选择嵌入式数据库类型

This section covers how to select one of the three embedded databases that Spring supports. It includes the following topics:
本节介绍如何选择 Spring 支持的三个嵌入式数据库之一。包括以下主题：

Using HSQL 使用 HSQL
Using H2 使用 H2
Using Derby 使用 Derby

(#jdbc-embedded-database-using-HSQL)Using HSQL 使用 HSQL

Spring supports HSQL 1.8.0 and above. HSQL is the default embedded database if no type is explicitly specified. To specify HSQL explicitly, set the type attribute of the embedded-database tag to HSQL. If you use the builder API, call the setType(EmbeddedDatabaseType) method with EmbeddedDatabaseType.HSQL.
Spring 支持 HSQL 1.8.0 及以上版本。如果没有明确指定类型，HSQL 是默认的嵌入式数据库。要显式指定 HSQL，将 embedded-database 标签的 type 属性设置为 HSQL 。如果您使用构建器 API，请使用 EmbeddedDatabaseType.HSQL 调用 setType(EmbeddedDatabaseType) 方法。

(#jdbc-embedded-database-using-H2)Using H2 使用 H2

Spring supports the H2 database. To enable H2, set the type attribute of the embedded-database tag to H2. If you use the builder API, call the setType(EmbeddedDatabaseType) method with EmbeddedDatabaseType.H2.
Spring 支持 H2 数据库。要启用 H2，将 embedded-database 标签的 type 属性设置为 H2 。如果您使用构建器 API，请调用 setType(EmbeddedDatabaseType) 方法并传递 EmbeddedDatabaseType.H2 。

(#jdbc-embedded-database-using-Derby)Using Derby 使用 Derby

Spring supports Apache Derby 10.5 and above. To enable Derby, set the type attribute of the embedded-database tag to DERBY. If you use the builder API, call the setType(EmbeddedDatabaseType) method with EmbeddedDatabaseType.DERBY.
Spring 支持 Apache Derby 10.5 及以上版本。要启用 Derby，将 embedded-database 标签的 type 属性设置为 DERBY 。如果您使用构建器 API，请调用 setType(EmbeddedDatabaseType) 方法并传递 EmbeddedDatabaseType.DERBY 。

(#jdbc-embedded-database-dao-testing)3.9.5. Testing Data Access Logic with an Embedded Database

3.9.5. 使用嵌入式数据库测试数据访问逻辑

Embedded databases provide a lightweight way to test data access code. The next example is a data access integration test template that uses an embedded database.
嵌入式数据库提供了一种轻量级的方式来测试数据访问代码。下一个示例是一个使用嵌入式数据库的数据访问集成测试模板。
Using such a template can be useful for one-offs when the embedded database does not need to be reused across test classes. However, if you wish to create an embedded database that is shared within a test suite, consider using the Spring TestContext Framework and configuring the embedded database as a bean in the Spring ApplicationContext as described in Creating an Embedded Database by Using Spring XML and Creating an Embedded Database Programmatically. The following listing shows the test template:
使用此类模板在嵌入式数据库不需要在测试类之间重用时很有用。然而，如果您希望创建一个在测试套件中共享的嵌入式数据库，请考虑使用 Spring TestContext 框架，并将嵌入式数据库配置为 Spring ApplicationContext 中的 bean，如《使用 Spring XML 创建嵌入式数据库》和《通过编程方式创建嵌入式数据库》中所述。以下列表显示了测试模板：

public class DataAccessIntegrationTestTemplate {

    private EmbeddedDatabase db;

    @BeforeEach
    public void setUp() {
        // creates an HSQL in-memory database populated from default scripts
        // classpath:schema.sql and classpath:data.sql
        db = new EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .addDefaultScripts()
                .build();
    }

    @Test
    public void testDataAccess() {
        JdbcTemplate template = new JdbcTemplate(db);
        template.query( /* ... */ );
    }

    @AfterEach
    public void tearDown() {
        db.shutdown();
    }

}

class DataAccessIntegrationTestTemplate {

    private lateinit var db: EmbeddedDatabase

    @BeforeEach
    fun setUp() {
        // creates an HSQL in-memory database populated from default scripts
        // classpath:schema.sql and classpath:data.sql
        db = EmbeddedDatabaseBuilder()
                .generateUniqueName(true)
                .addDefaultScripts()
                .build()
    }

    @Test
    fun testDataAccess() {
        val template = JdbcTemplate(db)
        template.query( /* ... */)
    }

    @AfterEach
    fun tearDown() {
        db.shutdown()
    }
}

(#jdbc-embedded-database-unique-names)3.9.6. Generating Unique Names for Embedded Databases

3.9.6. 为嵌入式数据库生成唯一名称

Development teams often encounter errors with embedded databases if their test suite inadvertently attempts to recreate additional instances of the same database. This can happen quite easily if an XML configuration file or @Configuration class is responsible for creating an embedded database and the corresponding configuration is then reused across multiple testing scenarios within the same test suite (that is, within the same JVM process)— for example, integration tests against embedded databases whose ApplicationContext configuration differs only with regard to which bean definition profiles are active.
开发团队常常会遇到嵌入式数据库的错误，如果他们的测试套件无意中尝试重新创建相同数据库的额外实例。如果 XML 配置文件或 @Configuration 类负责创建嵌入式数据库，并且相应的配置随后在同一个测试套件（即在同一个 JVM 进程内）的多个测试场景中被重复使用——例如，针对嵌入式数据库的集成测试，其 ApplicationContext 配置仅与哪些 Bean 定义配置文件处于活动状态有关——这种情况很容易发生。

The root cause of such errors is the fact that Spring’s EmbeddedDatabaseFactory (used internally by both the <jdbc:embedded-database> XML namespace element and the EmbeddedDatabaseBuilder for Java configuration) sets the name of the embedded database to testdb if not otherwise specified. For the case of <jdbc:embedded-database>, the embedded database is typically assigned a name equal to the bean’s id (often, something like dataSource). Thus, subsequent attempts to create an embedded database do not result in a new database. Instead, the same JDBC connection URL is reused, and attempts to create a new embedded database actually point to an existing embedded database created from the same configuration.
此类错误的根本原因是 Spring 的 EmbeddedDatabaseFactory （由 <jdbc:embedded-database> XML 命名空间元素和 EmbeddedDatabaseBuilder Java 配置内部使用）在未指定的情况下将嵌入式数据库的名称设置为 testdb 。对于 <jdbc:embedded-database> 的情况，嵌入式数据库通常被分配一个与 bean 的 id （通常是类似 dataSource 的东西）相等的名称。因此，随后的尝试创建嵌入式数据库不会导致创建新的数据库。相反，会重复使用相同的 JDBC 连接 URL，并且尝试创建新的嵌入式数据库实际上指向由相同配置创建的现有嵌入式数据库。

To address this common issue, Spring Framework 4.2 provides support for generating unique names for embedded databases. To enable the use of generated names, use one of the following options.
为了解决这个常见问题，Spring Framework 4.2 提供了对生成嵌入式数据库唯一名称的支持。要启用使用生成的名称，请使用以下选项之一。

EmbeddedDatabaseFactory.setGenerateUniqueDatabaseName()
EmbeddedDatabaseBuilder.generateUniqueName()
<jdbc:embedded-database generate-name="true" … >

(#jdbc-embedded-database-extension)3.9.7. Extending the Embedded Database Support

3.9.7. 扩展嵌入式数据库支持

You can extend Spring JDBC embedded database support in two ways:
您可以通过两种方式扩展 Spring JDBC 嵌入式数据库支持：

Implement EmbeddedDatabaseConfigurer to support a new embedded database type.
实现 EmbeddedDatabaseConfigurer 以支持新的嵌入式数据库类型。
Implement DataSourceFactory to support a new DataSource implementation, such as a connection pool to manage embedded database connections.
实现 DataSourceFactory 以支持新的 DataSource 实现，例如用于管理嵌入式数据库连接的连接池。

We encourage you to contribute extensions to the Spring community at GitHub Issues.
我们鼓励您在 GitHub Issues 上为 Spring 社区贡献扩展。

(#jdbc-initializing-datasource)3.10. Initializing a`DataSource` 3.10. 初始化`DataSource`

The org.springframework.jdbc.datasource.init package provides support for initializing an existing DataSource. The embedded database support provides one option for creating and initializing a DataSource for an application. However, you may sometimes need to initialize an instance that runs on a server somewhere.
The org.springframework.jdbc.datasource.init package provides support for initializing an existing DataSource . The embedded database support provides one option for creating and initializing a DataSource for an application. However, you may sometimes need to initialize an instance that runs on a server somewhere.

(#jdbc-initializing-datasource-xml)3.10.1. Initializing a Database by Using Spring XML

3.10.1. 使用 Spring XML 初始化数据库

If you want to initialize a database and you can provide a reference to a DataSource bean, you can use the initialize-database tag in the spring-jdbc namespace:
如果您想初始化一个数据库，并且您能提供一个对 DataSource 实例的引用，您可以使用 initialize-database 命名空间中的 spring-jdbc 标签：

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath:com/foo/sql/db-schema.sql"/>
    <jdbc:script location="classpath:com/foo/sql/db-test-data.sql"/>
</jdbc:initialize-database>

The preceding example runs the two specified scripts against the database. The first script creates a schema, and the second populates tables with a test data set.
该示例在数据库上运行了两个指定的脚本。第一个脚本创建一个模式，第二个脚本用测试数据集填充表。
The script locations can also be patterns with wildcards in the usual Ant style used for resources in Spring (for example, classpath*:/com/foo/**/sql/*-data.sql). If you use a pattern, the scripts are run in the lexical order of their URL or filename.
脚本位置也可以是使用 Spring 中资源通用的 Ant 风格通配符模式（例如， classpath*:/com/foo/**/sql/*-data.sql ）。如果您使用模式，脚本将按照其 URL 或文件名的字典顺序执行。

The default behavior of the database initializer is to unconditionally run the provided scripts. This may not always be what you want — for instance, if you run the scripts against a database that already has test data in it.
数据库初始化器的默认行为是无条件运行提供的脚本。这不一定总是你想要的——例如，如果你在已经包含测试数据的数据库上运行这些脚本。
The likelihood of accidentally deleting data is reduced by following the common pattern (shown earlier) of creating the tables first and then inserting the data. The first step fails if the tables already exist.
删除数据的意外可能性通过遵循先创建表然后插入数据的常见模式（之前已展示）而降低。如果表已存在，第一步将失败。

However, to gain more control over the creation and deletion of existing data, the XML namespace provides a few additional options. The first is a flag to switch the initialization on and off.
然而，为了获得更多对现有数据创建和删除的控制，XML 命名空间提供了一些额外的选项。第一个选项是开关初始化的标志。
You can set this according to the environment (such as pulling a boolean value from system properties or from an environment bean). The following example gets a value from a system property:
您可以根据环境设置此选项（例如从系统属性或环境 bean 中获取布尔值）。以下示例从系统属性获取一个值：

<jdbc:initialize-database data-source="dataSource"
    enabled="#{systemProperties.INITIALIZE_DATABASE}"> (1)
    <jdbc:script location="..."/>
</jdbc:initialize-database>

Get the value for enabled from a system property called INITIALIZE_DATABASE.
获取名为 INITIALIZE_DATABASE 的系统属性中的 enabled 值。

The second option to control what happens with existing data is to be more tolerant of failures. To this end, you can control the ability of the initializer to ignore certain errors in the SQL it runs from the scripts, as the following example shows:
第二种控制现有数据如何处理的方法是更加宽容失败。为此，您可以控制初始化器在从脚本中运行的 SQL 中忽略某些错误的能力，如下例所示：

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
    <jdbc:script location="..."/>
</jdbc:initialize-database>

In the preceding example, we are saying that we expect that, sometimes, the scripts are run against an empty database, and there are some DROP statements in the scripts that would, therefore, fail. So failed SQL DROP statements will be ignored, but other failures will cause an exception. This is useful if your SQL dialect doesn’t support DROP … IF EXISTS (or similar) but you want to unconditionally remove all test data before re-creating it. In that case the first script is usually a set of DROP statements, followed by a set of CREATE statements.
在先前的例子中，我们表示有时我们期望脚本在空数据库上运行，脚本中存在一些因此会失败的 DROP 语句。因此，失败的 SQL DROP 语句将被忽略，但其他失败将引发异常。如果你的 SQL 方言不支持 DROP … IF EXISTS （或类似），但你想无条件地删除所有测试数据然后再重新创建，这很有用。在这种情况下，第一个脚本通常是一组 DROP 语句，随后是一组 CREATE 语句。

The ignore-failures option can be set to NONE (the default), DROPS (ignore failed drops), or ALL (ignore all failures).
ignore-failures 选项可以设置为 NONE （默认）、 DROPS （忽略失败的删除）或 ALL （忽略所有失败）。

Each statement should be separated by ; or a new line if the ; character is not present at all in the script. You can control that globally or script by script, as the following example shows:
每条语句应通过 ; 或新行分隔，如果脚本中根本不存在 ; 字符。您可以通过全局或按脚本控制这一点，如下例所示：

<jdbc:initialize-database data-source="dataSource" separator="@@"> (1)
    <jdbc:script location="classpath:com/myapp/sql/db-schema.sql" separator=";"/> (2)
    <jdbc:script location="classpath:com/myapp/sql/db-test-data-1.sql"/>
    <jdbc:script location="classpath:com/myapp/sql/db-test-data-2.sql"/>
</jdbc:initialize-database>

Set the separator scripts to @@.
设置分隔符脚本为 @@ 。

Set the separator for db-schema.sql to ;.
设置 db-schema.sql 的分隔符为 ; 。

In this example, the two test-data scripts use @@ as statement separator and only the db-schema.sql uses ;. This configuration specifies that the default separator is @@ and overrides that default for the db-schema script.
在这个例子中，两个 test-data 脚本使用 @@ 作为语句分隔符，而只有 db-schema.sql 使用 ; 。此配置指定默认分隔符为 @@ ，并覆盖了 db-schema 脚本的默认设置。

If you need more control than you get from the XML namespace, you can use the DataSourceInitializer directly and define it as a component in your application.
如果您需要比 XML 命名空间提供的更多控制，您可以直接使用 DataSourceInitializer 并将其定义为应用程序中的组件。

(#jdbc-client-component-initialization)Initialization of Other Components that Depend on the Database

数据库依赖组件的初始化

A large class of applications (those that do not use the database until after the Spring context has started) can use the database initializer with no further complications. If your application is not one of those, you might need to read the rest of this section.
一类大量应用（那些在 Spring 上下文启动后才使用数据库的应用）可以使用数据库初始化器而不会出现任何问题。如果你的应用不属于这类，你可能需要阅读本节剩余内容。

The database initializer depends on a DataSource instance and runs the scripts provided in its initialization callback (analogous to an init-method in an XML bean definition, a @PostConstruct method in a component, or the afterPropertiesSet() method in a component that implements InitializingBean). If other beans depend on the same data source and use the data source in an initialization callback, there might be a problem because the data has not yet been initialized.
数据库初始化器依赖于一个 DataSource 实例，并运行其初始化回调中提供的脚本（类似于 XML bean 定义中的 init-method ，组件中的 @PostConstruct 方法，或实现 InitializingBean 的组件中的 afterPropertiesSet() 方法）。如果其他 bean 依赖于相同的数据源并在初始化回调中使用数据源，可能会出现问题，因为数据尚未初始化。
A common example of this is a cache that initializes eagerly and loads data from the database on application startup.
这是一个常见的例子，即缓存会在应用程序启动时积极初始化并从数据库加载数据。

To get around this issue, you have two options: change your cache initialization strategy to a later phase or ensure that the database initializer is initialized first.
为了解决这个问题，你有两个选择：将你的缓存初始化策略改为更晚的阶段，或者确保数据库初始化器首先被初始化。

Changing your cache initialization strategy might be easy if the application is in your control and not otherwise. Some suggestions for how to implement this include:
更改您的缓存初始化策略可能很容易，如果应用程序在您的控制之下，而不是其他情况。以下是一些如何实施此策略的建议：

Make the cache initialize lazily on first usage, which improves application startup time.
使缓存在首次使用时懒加载，从而提高应用程序启动时间。
Have your cache or a separate component that initializes the cache implement Lifecycle or SmartLifecycle. When the application context starts, you can automatically start a SmartLifecycle by setting its autoStartup flag, and you can manually start a Lifecycle by calling ConfigurableApplicationContext.start() on the enclosing context.
拥有缓存或单独的初始化缓存组件实现 Lifecycle 或 SmartLifecycle 。当应用程序上下文启动时，您可以通过设置其 autoStartup 标志自动启动 SmartLifecycle ，并且可以通过在封装上下文中调用 ConfigurableApplicationContext.start() 来手动启动 Lifecycle 。
Use a Spring ApplicationEvent or similar custom observer mechanism to trigger the cache initialization. ContextRefreshedEvent is always published by the context when it is ready for use (after all beans have been initialized), so that is often a useful hook (this is how the SmartLifecycle works by default).
使用 Spring ApplicationEvent 或类似的自定义观察者机制来触发缓存初始化。 ContextRefreshedEvent 在上下文准备好使用时（所有 bean 初始化完成后）总是被发布，因此这通常是一个有用的钩子（这就是 SmartLifecycle 默认工作的方式）。

Ensuring that the database initializer is initialized first can also be easy. Some suggestions on how to implement this include:
确保数据库初始化器首先初始化也可以很简单。以下是一些实现建议：

Rely on the default behavior of the Spring BeanFactory, which is that beans are initialized in registration order. You can easily arrange that by adopting the common practice of a set of <import/> elements in XML configuration that order your application modules and ensuring that the database and database initialization are listed first.
依赖 Spring BeanFactory 的默认行为，即按照注册顺序初始化 bean。您可以通过采用在 XML 配置中设置一组 <import/> 元素的常用做法来轻松安排这一点，该做法可以按顺序排列您的应用程序模块，并确保数据库和数据库初始化被列在第一位。
Separate the DataSource and the business components that use it and control their startup order by putting them in separate ApplicationContext instances (for example, the parent context contains the DataSource, and the child context contains the business components). This structure is common in Spring web applications but can be more generally applied.
将 DataSource 与使用它的业务组件分开，并通过将它们放入单独的 ApplicationContext 实例中来控制它们的启动顺序（例如，父上下文包含 DataSource ，子上下文包含业务组件）。这种结构在 Spring Web 应用程序中很常见，但可以更广泛地应用。

ON THIS PAGE

#3. 使用 JDBC 进行数据访问

#(#jdbc-choose-style)3.1. Choosing an Approach for JDBC Database Access

#(#jdbc-packages)3.2. Package Hierarchy 3.2. 包层次

#(#jdbc-core)3.3. Using the JDBC Core Classes to Control Basic JDBC Processing and Error Handling

#(#jdbc-JdbcTemplate)3.3.1. UsingJdbcTemplate 3.3.1. 使用JdbcTemplate

#(#jdbc-JdbcTemplate-examples-query)Querying (SELECT) 查询（SELECT ）

#(#jdbc-JdbcTemplate-examples-update)Updating (INSERT,UPDATE, andDELETE) withJdbcTemplate

#(#jdbc-JdbcTemplate-examples-other)OtherJdbcTemplate Operations 其他操作

#(#jdbc-JdbcTemplate-idioms)JdbcTemplate Best PracticesJdbcTemplate 最佳实践

#(#jdbc-NamedParameterJdbcTemplate)3.3.2. UsingNamedParameterJdbcTemplate 3.3.2. 使用

#(#jdbc-SQLExceptionTranslator)3.3.3. UsingSQLExceptionTranslator 3.3.3. 使用SQLExceptionTranslator

#(#jdbc-statements-executing)3.3.4. Running Statements

#(#jdbc-statements-querying)3.3.5. Running Queries 3.3.5. 运行查询

#(#jdbc-updates)3.3.6. Updating the Database

#(#jdbc-auto-generated-keys)3.3.7. Retrieving Auto-generated Keys

#(#jdbc-connections)3.4. Controlling Database Connections

#(#jdbc-datasource)3.4.1. UsingDataSource 3.4.1. 使用DataSource

#(#jdbc-DataSourceUtils)3.4.2. UsingDataSourceUtils 3.4.2. 使用DataSourceUtils

#(#jdbc-SmartDataSource)3.4.3. ImplementingSmartDataSource 3.4.3. 实现编号SmartDataSource

#(#jdbc-AbstractDataSource)3.4.4. ExtendingAbstractDataSource 3.4.4. 扩展AbstractDataSource

#(#jdbc-SingleConnectionDataSource)3.4.5. UsingSingleConnectionDataSource 3.4.5. 使用

#(#jdbc-DriverManagerDataSource)3.4.6. UsingDriverManagerDataSource 3.4.6. 使用DriverManagerDataSource

#(#jdbc-TransactionAwareDataSourceProxy)3.4.7. UsingTransactionAwareDataSourceProxy 3.4.7. 使用

#(#jdbc-DataSourceTransactionManager)3.4.8. UsingDataSourceTransactionManager /JdbcTransactionManager

#(#jdbc-advanced-jdbc)3.5. JDBC Batch Operations

#(#jdbc-batch-classic)3.5.1. Basic Batch Operations withJdbcTemplate

#(#jdbc-batch-list)3.5.2. Batch Operations with a List of Objects

#(#jdbc-batch-multi)3.5.3. Batch Operations with Multiple Batches

#(#jdbc-simple-jdbc)3.6. Simplifying JDBC Operations with theSimpleJdbc Classes

#(#jdbc-simple-jdbc-insert-1)3.6.1. Inserting Data by UsingSimpleJdbcInsert

#(#jdbc-simple-jdbc-insert-2)3.6.2. Retrieving Auto-generated Keys by UsingSimpleJdbcInsert

#(#jdbc-simple-jdbc-insert-3)3.6.3. Specifying Columns for aSimpleJdbcInsert

#(#jdbc-simple-jdbc-parameters)3.6.4. UsingSqlParameterSource to Provide Parameter Values

#(#jdbc-simple-jdbc-call-1)3.6.5. Calling a Stored Procedure withSimpleJdbcCall

#(#jdbc-simple-jdbc-call-2)3.6.6. Explicitly Declaring Parameters to Use for aSimpleJdbcCall

#(#jdbc-params)3.6.7. How to DefineSqlParameters

#(#jdbc-simple-jdbc-call-3)3.6.8. Calling a Stored Function by UsingSimpleJdbcCall

#(#jdbc-simple-jdbc-call-4)3.6.9. Returning aResultSet or REF Cursor from aSimpleJdbcCall

#(#jdbc-object)3.7. Modeling JDBC Operations as Java Objects

#(#jdbc-SqlQuery)3.7.1. UnderstandingSqlQuery 3.7.1. 理解SqlQuery

#(#jdbc-MappingSqlQuery)3.7.2. UsingMappingSqlQuery 3.7.2. 使用MappingSqlQuery

#(#jdbc-SqlUpdate)3.7.3. UsingSqlUpdate 3.7.3. 使用SqlUpdate

#(#jdbc-StoredProcedure)3.7.4. UsingStoredProcedure 3.7.4. 使用StoredProcedure

#(#jdbc-parameter-handling)3.8. Common Problems with Parameter and Data Value Handling

#(#jdbc-type-information)3.8.1. Providing SQL Type Information for Parameters

#(#jdbc-lob)3.8.2. Handling BLOB and CLOB objects

#(#jdbc-in-clause)3.8.3. Passing in Lists of Values for IN Clause

#(#jdbc-complex-types)3.8.4. Handling Complex Types for Stored Procedure Calls

#(#jdbc-embedded-database-support)3.9. Embedded Database Support

#(#jdbc-why-embedded-database)3.9.1. Why Use an Embedded Database?

#(#jdbc-embedded-database-xml)3.9.2. Creating an Embedded Database by Using Spring XML

#(#jdbc-embedded-database-java)3.9.3. Creating an Embedded Database Programmatically

#(#jdbc-embedded-database-types)3.9.4. Selecting the Embedded Database Type

#(#jdbc-embedded-database-using-HSQL)Using HSQL 使用 HSQL

#(#jdbc-embedded-database-using-H2)Using H2 使用 H2

#(#jdbc-embedded-database-using-Derby)Using Derby 使用 Derby

#(#jdbc-embedded-database-dao-testing)3.9.5. Testing Data Access Logic with an Embedded Database

#(#jdbc-embedded-database-unique-names)3.9.6. Generating Unique Names for Embedded Databases

#(#jdbc-embedded-database-extension)3.9.7. Extending the Embedded Database Support

#(#jdbc-initializing-datasource)3.10. Initializing aDataSource 3.10. 初始化DataSource

#(#jdbc-initializing-datasource-xml)3.10.1. Initializing a Database by Using Spring XML

#(#jdbc-client-component-initialization)Initialization of Other Components that Depend on the Database

3. 使用 JDBC 进行数据访问

(#jdbc-choose-style)3.1. Choosing an Approach for JDBC Database Access

(#jdbc-packages)3.2. Package Hierarchy 3.2. 包层次

(#jdbc-core)3.3. Using the JDBC Core Classes to Control Basic JDBC Processing and Error Handling

(#jdbc-JdbcTemplate)3.3.1. Using`JdbcTemplate` 3.3.1. 使用`JdbcTemplate`

(#jdbc-JdbcTemplate-examples-query)Querying (`SELECT`) 查询（`SELECT` ）

(#jdbc-JdbcTemplate-examples-update)Updating (`INSERT`,`UPDATE`, and`DELETE`) with`JdbcTemplate`

(#jdbc-JdbcTemplate-examples-other)Other`JdbcTemplate` Operations 其他操作

(#jdbc-JdbcTemplate-idioms)`JdbcTemplate` Best Practices`JdbcTemplate` 最佳实践

(#jdbc-NamedParameterJdbcTemplate)3.3.2. Using`NamedParameterJdbcTemplate` 3.3.2. 使用

(#jdbc-SQLExceptionTranslator)3.3.3. Using`SQLExceptionTranslator` 3.3.3. 使用`SQLExceptionTranslator`

(#jdbc-statements-executing)3.3.4. Running Statements

(#jdbc-statements-querying)3.3.5. Running Queries 3.3.5. 运行查询

(#jdbc-updates)3.3.6. Updating the Database

(#jdbc-auto-generated-keys)3.3.7. Retrieving Auto-generated Keys

(#jdbc-connections)3.4. Controlling Database Connections

(#jdbc-datasource)3.4.1. Using`DataSource` 3.4.1. 使用`DataSource`

(#jdbc-DataSourceUtils)3.4.2. Using`DataSourceUtils` 3.4.2. 使用`DataSourceUtils`

(#jdbc-SmartDataSource)3.4.3. Implementing`SmartDataSource` 3.4.3. 实现编号`SmartDataSource`

(#jdbc-AbstractDataSource)3.4.4. Extending`AbstractDataSource` 3.4.4. 扩展`AbstractDataSource`

(#jdbc-SingleConnectionDataSource)3.4.5. Using`SingleConnectionDataSource` 3.4.5. 使用

(#jdbc-DriverManagerDataSource)3.4.6. Using`DriverManagerDataSource` 3.4.6. 使用`DriverManagerDataSource`

(#jdbc-TransactionAwareDataSourceProxy)3.4.7. Using`TransactionAwareDataSourceProxy` 3.4.7. 使用

(#jdbc-DataSourceTransactionManager)3.4.8. Using`DataSourceTransactionManager` /`JdbcTransactionManager`

(#jdbc-advanced-jdbc)3.5. JDBC Batch Operations

(#jdbc-batch-classic)3.5.1. Basic Batch Operations with`JdbcTemplate`

(#jdbc-batch-list)3.5.2. Batch Operations with a List of Objects

(#jdbc-batch-multi)3.5.3. Batch Operations with Multiple Batches

(#jdbc-simple-jdbc)3.6. Simplifying JDBC Operations with the`SimpleJdbc` Classes

(#jdbc-simple-jdbc-insert-1)3.6.1. Inserting Data by Using`SimpleJdbcInsert`

(#jdbc-simple-jdbc-insert-2)3.6.2. Retrieving Auto-generated Keys by Using`SimpleJdbcInsert`

(#jdbc-simple-jdbc-insert-3)3.6.3. Specifying Columns for a`SimpleJdbcInsert`

(#jdbc-simple-jdbc-parameters)3.6.4. Using`SqlParameterSource` to Provide Parameter Values

(#jdbc-simple-jdbc-call-1)3.6.5. Calling a Stored Procedure with`SimpleJdbcCall`

(#jdbc-simple-jdbc-call-2)3.6.6. Explicitly Declaring Parameters to Use for a`SimpleJdbcCall`

(#jdbc-params)3.6.7. How to Define`SqlParameters`

(#jdbc-simple-jdbc-call-3)3.6.8. Calling a Stored Function by Using`SimpleJdbcCall`

(#jdbc-simple-jdbc-call-4)3.6.9. Returning a`ResultSet` or REF Cursor from a`SimpleJdbcCall`

(#jdbc-object)3.7. Modeling JDBC Operations as Java Objects

(#jdbc-SqlQuery)3.7.1. Understanding`SqlQuery` 3.7.1. 理解`SqlQuery`

(#jdbc-MappingSqlQuery)3.7.2. Using`MappingSqlQuery` 3.7.2. 使用`MappingSqlQuery`

(#jdbc-SqlUpdate)3.7.3. Using`SqlUpdate` 3.7.3. 使用`SqlUpdate`

(#jdbc-StoredProcedure)3.7.4. Using`StoredProcedure` 3.7.4. 使用`StoredProcedure`

(#jdbc-parameter-handling)3.8. Common Problems with Parameter and Data Value Handling

(#jdbc-type-information)3.8.1. Providing SQL Type Information for Parameters

(#jdbc-lob)3.8.2. Handling BLOB and CLOB objects

(#jdbc-in-clause)3.8.3. Passing in Lists of Values for IN Clause

(#jdbc-complex-types)3.8.4. Handling Complex Types for Stored Procedure Calls

(#jdbc-embedded-database-support)3.9. Embedded Database Support

(#jdbc-why-embedded-database)3.9.1. Why Use an Embedded Database?

(#jdbc-embedded-database-xml)3.9.2. Creating an Embedded Database by Using Spring XML

(#jdbc-embedded-database-java)3.9.3. Creating an Embedded Database Programmatically

(#jdbc-embedded-database-types)3.9.4. Selecting the Embedded Database Type

(#jdbc-embedded-database-using-HSQL)Using HSQL 使用 HSQL

(#jdbc-embedded-database-using-H2)Using H2 使用 H2

(#jdbc-embedded-database-using-Derby)Using Derby 使用 Derby

(#jdbc-embedded-database-dao-testing)3.9.5. Testing Data Access Logic with an Embedded Database

(#jdbc-embedded-database-unique-names)3.9.6. Generating Unique Names for Embedded Databases

(#jdbc-embedded-database-extension)3.9.7. Extending the Embedded Database Support

(#jdbc-initializing-datasource)3.10. Initializing a`DataSource` 3.10. 初始化`DataSource`

(#jdbc-initializing-datasource-xml)3.10.1. Initializing a Database by Using Spring XML

(#jdbc-client-component-initialization)Initialization of Other Components that Depend on the Database