19. Data access with JDBC
使用JDBC的数据访问
19.1 Introduction to Spring Framework JDBC
介绍spring框架的JDBC
The value-add provided by the Spring Framework JDBC abstraction is perhaps best shown by the sequence of actions outlined in the table below. The table shows what actions Spring will take care of and which actions are the responsibility of you, the application developer.
通过spring框架的JDBC抽象提供的价值增值通常展示通过一系列的行为如下面的表。这个表格展示了spring将会处理的行为和你需要处理的行为，应用开发者。
Table 19.1. Spring JDBC - who does what?
spring JDBC - 谁做什么？
| Action 行为 | Spring | You |
| Define connection parameters. 定义连接参数 |
| X |
| Open the connection. 打开连接 | X |
|
| Specify the SQL statement. 定义SQL语句 |
| X |
| Declare parameters and provide parameter values 定义参数和提供参数值 |
| X |
| Prepare and execute the statement. 准备和执行语句 | X |
|
| Set up the loop to iterate through the results (if any). 设置结果集的迭代 | X |
|
| Do the work for each iteration. 对于每次迭代进行处理 |
| X |
| Process any exception. 处理任何异常 | X |
|
| Handle transactions. 处理事务 | X |
|
| Close the connection, statement and resultset. 关闭连接、语句和结果集 | X |
|
The Spring Framework takes care of all the low-level details that can make JDBC such a tedious API to develop with.
spring处理低级的细节并且保证JDBC的开发不会复杂。
19.1.1 Choosing an approach for JDBC database access
选择JDBC数据库方法的方式
You can choose among several approaches to form the basis for your JDBC database access. In addition to three flavors of the 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. All approaches require a JDBC 2.0-compliant driver, and some advanced features require a JDBC 3.0 driver.
你可以选择不同的方式对于你的JDBC数据库访问。此外有三种形式的JdbcTemplate，一个新的SimpleJdbcInsert和SimplejdbcCall方式优化数据库元数据，并且RDBMS Object风格可以考虑面向对象的方法形式类似于JDO查询设计。一旦你使用了三个中其中的一个方式，你可以混合并且匹配包括不同方法的特性。所有的方法要求2.0的编译驱动，并且一些高级特性需要JDBC3.0的驱动。
JdbcTemplate is the classic Spring JDBC approach and the most popular. This "lowest level" approach and all others use a JdbcTemplate under the covers.
JdbcTemplate是一个经典的springJDBC方式并且是最流行的。这个低级别的方式和其他使用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 only need to provide the name of the table or procedure and provide a map of parameters matching the column names. This only works if the database provides adequate metadata. If the database doesn’t provide this metadata, you will have to provide explicit configuration of the parameters.
SimpleJdbcInsert和SimpleJdbcCall优化了数据元数据限制需要配置的数量。这个方式简化了代码量因此你只需要提供表的名字或程序并且提供参数匹配的map对于列的名字。只有当数据支持元数据时这个方法才有效。如果数据库不提供元数据，你将不得不提供明确的参数配置。
RDBMS Objects including MappingSqlQuery, SqlUpdate and StoredProcedure requires 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 methods can be called multiple times with various parameter values passed in.
RDBMS Object包括MappingSqlQuery、SqlUpdate和StoredProcedure要求你创建可以重用和线程安全的object在初始化你的数据访问层中。这个方式是模仿JDO的查询当你定义你的查询字符串、声明参数和编译查询。一旦你这么做，执行方法可以调用多次通过传入不同的参数。
19.1.2 Package hierarchy
包结构
The Spring Framework’s JDBC abstraction framework consists of four different packages, namely core, datasource, object, and support.
spring框架的JDBC抽象框架包括四个不同的包，名字为core、datasource、object和support。
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 Section 19.2, “Using the JDBC core classes to control basic JDBC processing and error handling”, Section 19.4, “JDBC batch operations”, and Section 19.5, “Simplifying JDBC operations with the SimpleJdbc classes”.
org.springframework.jdbc.core包中包含JdbcTemplate类和他的不同回调接口，加上一些相关的类。一个子包名字为org.springframework.jdbc.core.simple包含SimpleJdbcInsert和SimpleJdbcCall类。另一个包名字为org.springframework.jdbc.core.namedparam包含NamedParameterJdbcTemplate类和相关的支持类。见19.2节“使用JDBC core类来控制基本的JDBC处理和错误处理”，19.4节“JDBC的批量操作”和19.5节“使用SimpleJdbc类实现简单的JDBC操作”。
The org.springframework.jdbc.datasource package contains a utility class for easy DataSource access, and various simple DataSource implementations that can be used for testing and running unmodified JDBC code outside of a Java EE container. A subpackage named org.springfamework.jdbc.datasource.embedded provides support for creating embedded databases using Java database engines such as HSQL, H2, and Derby. See Section 19.3, “Controlling database connections” and Section 19.8, “Embedded database support”.
org.springframework.jdbc.datasource包中包含一个工具类用于简单的数据源访问，并且有多个不同的简单数据眼实现可以用于测试和运行没有修改的JDBC代码在JavaEE容器之外。一个子包名字为org.springfamework.jdbc.datasource.embedded提供了创建内嵌数据库的支持通过使用Java数据库引擎例如HSQL、H2和Derby。见章节19.3，“控制数据库连接”和章节19.8“内嵌数据库支持”
The org.springframework.jdbc.object package contains classes that represent RDBMS queries, updates, and stored procedures as thread-safe, reusable objects. See Section 19.6, “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.
org.springframework.jdbc.object包中包含了RDBMS查询、更新和存储过程是线程安全可重用的object。将章节19.6“作为Java的object来模型化JDBC操作”。这种方式通过JDO模型化，尽管查询返回object是不连接数据库的。这样高级别的JDBC抽象在低级别存在于org.springframework.jdbc.core包中。
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. All translated exceptions are unchecked, which gives you the option of catching the exceptions from which you can recover while allowing other exceptions to be propagated to the caller. See Section 19.2.3, “SQLExceptionTranslator”.
org.springfamework.jdbc.support包提供了SQLException的翻译和一些工具类。异常在JDBC运行时可能会被抛出并转换为定义在org.springframework.dao包中的异常。这意味着使用JDBC抽象层不需要实现JDBC或特定的RDBMS错误处理。所有转换后的异常是非检查的，给了你选择捕获异常并恢复当允许把这些异常传递给调用者时。见章节19.2.3““SQLExceptionTranslator”。
19.2 Using the JDBC core classes to control basic JDBC processing and error handling
使用JDBC核心类来控制基本的JDBC操作和错误处理
19.2.1 JdbcTemplate
The JdbcTemplate class is the central class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors such as forgetting to close the connection. It performs the basic tasks of the core JDBC workflow such as statement creation and execution, leaving application code to provide SQL and extract results. The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.
JdbcTemplate类是一个中心类在JDBC的core包中。他处理创建和发布资源，帮助你避免一般的错误例如忘记关闭连接。他提供基本的任务对于核心的JDBC工作流例如声明的创建和执行、让应用代码来提供SQL和相关的结果。JdbcTemplate类执行SQL查询、更新和存储过程调用、对结果集迭代和返回参数值的提取。他也捕获JDBC异常并将他们转换为通用的、更加有意义的定义在org.springframework.dao包中的异常结构。
When you use the JdbcTemplate for your code, you only need to implement callback interfaces, giving them a clearly defined contract. The PreparedStatementCreator callback interface creates a prepared statement given a Connection provided by this class, 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来处理代码时，你只需要实现回调接口，给予他们一个明确的规范。PreparedStatementCreator回调接口通过connection创建一个prepared statement，提供SQL和任何需要的参数。对于CallableStatementCreator接口来说也是一样的，创建callable statements。RowCallbackHandler接口从每一行的结果集中提取值。
The JdbcTemplate can be used within a DAO implementation through direct instantiation with a DataSource reference, or be configured in a Spring IoC container and given to DAOs as a bean reference.
JdbcTemplate可以用于DAO实现通过直接的实例化数据源引用或在spring的IOC容器中进行配置并给予DAO一个bean的引用。
[Note]
注意
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.
数据源配置为一个bean在spring的IOC容器中。在第一个例子中bean直接传递给了service；在第二个例子中传递给了prepared template。
All SQL issued by this class is logged at the DEBUG level under the category corresponding to the fully qualified class name of the template instance (typically JdbcTemplate, but it may be different if you are using a custom subclass of the JdbcTemplate class).
所有这个讨论的SQL都以调试的级别记录了根据全限定模板实例的名字（通常是JdbcTemplate，但是也可能不同如果你使用一个自定义类继承了JdbcTemplate类）。
Examples of JdbcTemplate class usage
JdbcTemplate类的使用案例
This section provides some examples of JdbcTemplate class usage. These examples are not an exhaustive list of all of the functionality exposed by the JdbcTemplate; see the attendant javadocs for that.
这一节提供了一些使用JdbcTemplate的案例。这些例子并没有完全包括JdbcTemplate中所有的功能，见相关的javadocs。
Querying (SELECT)
Here is a simple query for getting the number of rows in a relation:
这是一个简单的查询来获得给定关系的数目：
int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);
A simple query using a bind variable:
一个简单的查询使用一个绑定的变量：
int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
"select count(*) from t_actor where first_name = ?", Integer.class, "Joe");
Querying for a String:
查询一个字符串：
String lastName = this.jdbcTemplate.queryForObject(
"select last_name from t_actor where id = ?",
new Object[]{1212L}, String.class);
Querying and populating a single domain object:
查询和获取一个单独的域object：
Actor actor = this.jdbcTemplate.queryForObject(
"select first_name, last_name from t_actor where id = ?",
new Object[]{1212L},
new RowMapper<Actor>() {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
});
Querying and populating a number of domain objects:
查询和获取一系列域object：
List<Actor> actors = this.jdbcTemplate.query(
"select first_name, last_name from t_actor",
new RowMapper<Actor>() {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
});
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 anonymous inner classes, and extract them out into a single class (typically a static nested class) that can then be referenced by DAO methods as needed. For example, it may be better to write the last code snippet as follows:
如果最后两个代码片段存在于同一个应用中，最好去掉重复的在两个RowMapper中的匿名类，并且抽取他们到外部称为一个单独的类（通常是一个静态内部类）可以根据需要被DAO方法引用。例如，最好书写如下：
public List<Actor> findAllActors() {
return this.jdbcTemplate.query( "select first_name, last_name from t_actor", new ActorMapper());
}
private static final class ActorMapper implements RowMapper<Actor> {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
}
Updating (INSERT/UPDATE/DELETE) with jdbcTemplate
You use the update(..) method to perform insert, update and delete operations. Parameter values are usually provided as var args or alternatively as an object array.
你可以使用update方法来执行插入、更新和删除操作。参数值被通常提供作为var参数或替代作为一个object数组。
this.jdbcTemplate.update(
"insert into t_actor (first_name, last_name) values (?, ?)",
"Leonor", "Watling");
this.jdbcTemplate.update(
"update t_actor set last_name = ? where id = ?",
"Banjo", 5276L);
this.jdbcTemplate.update(
"delete from actor where id = ?",
Long.valueOf(actorId));
Other jdbcTemplate operations
其他jdbcTemplate操作
You can use the execute(..) method to execute any arbitrary SQL, and as such the method is often used for DDL statements. It is heavily overloaded with variants taking callback interfaces, binding variable arrays, and so on.
你可以使用execute方法来执行任何SQL并且这样的方法通过用于DDL语句。他可以覆盖回调接口的变量、绑定变量数组等等。
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
The following example invokes a simple stored procedure. More sophisticated stored procedure support is covered later.
下面的例子调用了一个存储过程。大部分复杂的存储过程支持在后面说明。
this.jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
Long.valueOf(unionId));
JdbcTemplate best practices
JdbcTemplate最好的练习
Instances of the JdbcTemplate class are threadsafe 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（或Repository中）。JdbcTemplate是状态的并且包含一个数据源的引用，但是这个状态不是会话状态。
A common practice when using the JdbcTemplate class (and the associated NamedParameterJdbcTemplate classes) 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 look in part like the following:
当使用JdbcTemplate的一个通用练习（并且连接着NamedParameterJdbcTemplate类）来配置一个数据源在你的spring配置文件中，并且依赖注入共享数据源的bean到你的DAO类中，JdbcTemplate被创建通过数据源的set方法。他引导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...
}
The corresponding configuration might look like this.
相关的配置如下：
<?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
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://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 annotate the class with @Repository (which makes it a candidate for component-scanning) and annotate the DataSource setter method with @Autowired.
从明确的配置中二选一是使用组件扫描和注解支持对于依赖注入。这个例子中你使用@Repository来注解类（需要配合组件扫描来使用）和注解数据源的set方法使用@Autowired。
@Repository
public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-backed implementations of the methods on the CorporateEventDao follow...
}
The corresponding XML configuration file would look like the following:
相关的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
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://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 are using Spring’s JdbcDaoSupport class, and your various JDBC-backed DAO classes extend from it, then 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 execute SQL. Once configured, a JdbcTemplate instance is threadsafe. You may want multiple JdbcTemplate instances if your application accesses multiple databases, which requires multiple DataSources, and subsequently multiple differently configured JdbcTemplates.
不管上面的模板初始化风格你选择哪种，他都需要每次创建一个JdbcTemplate实例当你希望执行SQL时。一旦配置，JdbcTemplate实例是线程安全的。你希望多个JdbcTemplate实例如果你的应用访问多个数据库，需要多个数据源并且随后不同的配置JdbcTemplate。
19.2.2 NamedParameterJdbcTemplate
The NamedParameterJdbcTemplate class adds support for programming JDBC statements 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 using named parameters.
NamedParameterJdbcTemplate类添加的对编程JDBC声明使用命名参数的支持，相反的编程JDBC语句只使用典型的占位符（？）参数。NamedParameterJdbcTemplate包裹了JdbcTemplate并且委托给JdbcTemplate来执行大部分工作。这节只描述NamedParameterJdbcTemplate和JdbcTemplate不同的部分，编程JDBC声明使用命名参数。
// 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);
}
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.
此外，你可以传递一个命名参数和他们相应的值到NamedParameterJdbcTemplate实例通过使用基于Map的风格。剩余的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);
}
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 snippet (the MapSqlParameterSource class). An SqlParameterSource is a source of named parameter values to a NamedParameterJdbcTemplate. The MapSqlParameterSource class is a very simple implementation that is simply 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，其中key是参数并且值是参数值。
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的属性作为命名参数值的源。
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...
}
// 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);
}
Remember that the NamedParameterJdbcTemplate class wraps a classic JdbcTemplate template; if you need access to the wrapped JdbcTemplate instance to access functionality only present in the JdbcTemplate class, you can use the getJdbcOperations() method to access the wrapped JdbcTemplate through the JdbcOperations interface.
记住NamedParameterJdbcTemplate类包裹了一个经典的JdbcTemplate模板，如果你访问包裹的JdbcTemplate实例来访问在JdbcTemplate类中的功能，你可以使用getJdbcOperations来访问包裹的JdbcTemplate通过JdbcOperations接口。
See also the section called “JdbcTemplate best practices” for guidelines on using the NamedParameterJdbcTemplate class in the context of an application.
见章节名为““JdbcTemplate最好的练习”来查找指引使用NamedParameterJdbcTemplate类在应用的上下文中。
19.2.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.
SQLExceptionTranslator是一个接口用于类的实现可以将转换SQLExceptions和spring自己的org.springframework.dao.DataAccessException，忽略不同的数据访问策略。实现可以是通用的（例如，使用SQLState代码的JDBC）或私有的（例如，使用Oracle的错误代码）为了更加的准确。
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 the DatabaseMetaData. The codes for the actual database you are using are used.
SQLErrorCodeSQLExceptionTranslator是SQLExceptionTranslator的默认实现被使用。这个实现使用特定的供应商代码。他比SQLState实现更加精确。错误代码翻译基于JavaBean类型类的代码名为SQLErrorCodes。这个类被创建通过SQLErrorCodesFactory作为工程用于创建SQLErrorCodes基于配置的上下文文件啊名字为sql-error-codes.xml。这个文件有供应商代码并且基于DatabaseProductName来自DatabaseMetaData。这些对应于你实际使用的数据库。
The SQLErrorCodeSQLExceptionTranslator applies matching rules in the following sequence:
SQLErrorCodeSQLExceptionTranslator应用规则如下：
[Note]
注意
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来自classpath并且匹配SQLErrorCodes实例位于数据库来自使用的数据库的元数据。
Any custom translation implemented by a subclass. Normally the provided concrete SQLErrorCodeSQLExceptionTranslator is used so this rule does not apply. It only applies 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.
任何自定义的SQLExceptionTranslator接口实现提供作为SQLErrorCodes类的customSqlExceptionTranslator属性中。
The list of instances of the CustomSQLErrorCodesTranslation class, provided for the customTranslations property of the SQLErrorCodes class, are searched for a match.
CustomSQLErrorCodesTranslation类的实例列表，通过SQLErrorCodes类的customTranslations属性被选为匹配。
Error code matching is applied.
错误代码匹配被应用。
Use the fallback translator. SQLExceptionSubclassTranslator is the default fallback translator. If this translation is not available then the next fallback translator is the SQLStateSQLExceptionTranslator.
使用后备的翻译器。SQLExceptionSubclassTranslator是默认的后备翻译。如果这个翻译不可用则下一个后备翻译是SQLStateSQLExceptionTranslator。
You can extend SQLErrorCodeSQLExceptionTranslator:
你可以继承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;
}
}
In this example, the specific error code -12345 is translated and other errors are left to be translated by the default translator implementation. To use this custom translator, it is necessary to pass it to the JdbcTemplate through the method setExceptionTranslator and to use this JdbcTemplate for all of the data access processing where this translator is needed. Here is an example of how this custom translator can be used:
在这个例子中，特定的错误代码12345被翻译并且其他的作为留给了默认翻译来实现。为了使用这个自定义翻译器，需要将其传递给JdbcTemplate通过setExceptionTranslator方法并且使用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);
}
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中。
19.2.4 Executing statements
执行声明
Executing 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))");
}
}
19.2.5 Running queries
运行查询
Some query methods return a single value. To retrieve a count or a specific value from one row, use queryForObject(..). The latter converts the returned JDBC Type to the Java class that is passed in as an argument. If the type conversion is invalid, then an InvalidDataAccessApiUsageException is thrown. Here is an example that contains two query methods, one for an int and one that queries for a String.
一些查询方法返回单一值。为了获取一个计数或一个特定的值来自单行可以使用queryForObject。可以将返回的JDBC类型转换为Java类并且作为一个参数来传递。如果类型转换是不合法的则会抛出一个InvalidDataAccessApiUsageException异常。这是一个例子包含两个查询方法，一个用于整型而另一个用于字符串。
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);
}
}
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 entry is a Map with each entry in the map representing the column value for that row. If you add a method to the above example to retrieve a list of all the rows, it would look like this:
此外对于单一的结果查询方法，一些方法返回一个列表包含每个行的查询返回的入口。通用的方法是queryForList返回一个列表其中每个入口是一个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");
}
The list returned would look something like this:
这个列表的返回可能是这样的：
[{name=Bob, id=1}, {name=Mary, id=2}]
19.2.6 Updating the database
更新数据库
The following example shows a column updated for a certain primary key. In this example, an SQL statement has placeholders for row parameters. The parameter values can be passed in as varargs or alternatively as an array of objects. Thus primitives should be wrapped in the primitive wrapper classes explicitly or using auto-boxing.
下面的例子展示了一个列通过主键进行更行操作。在这个例子中，一个SQL语句使用的行参数的占位符。这个参数值可以传递作为可变参数或一个object的数组。基本类型应当被包裹明确使用基本类型包装器或使用自动装箱。
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);
}
}
19.2.7 Retrieving auto-generated keys
接收自增的键
An update() convenience method supports the retrieval of primary keys generated by the database. This support is part of the JDBC 3.0 standard; see Chapter 13.6 of the specification for details. The method takes a PreparedStatementCreator as its first argument, and this is the way the required insert statement is specified. The other argument is a KeyHolder, which contains the generated key on successful return from the update. There is not a 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方法支持返回数据库生成的键。这个支持时JDBC3.0标准的一部分，见章节13.6中的详细定义。这个方法将PreparedStatementCreator作为第一个参数，并且这个方式需要指定插入语句。其他的参数是KeyHolder，包含来自更新操作的返回的键值。这并不是一个标准单一的方式创建适当的PreparedStatement（解释为了方法签名是这样的）。下面的例子在Oracle上可以运行但是不一定在其他平台可行。
final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setString(1, name);
return ps;
}
},
keyHolder);
// keyHolder.getKey() now contains the generated key
19.3 Controlling database connections
控制数据库连接
19.3.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 allows a container or a framework to 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; that is the responsibility of the administrator that 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.
spring获得一个数据库连接通过数据源。一个数据源是JDBC定义的一部分并且是一个通用的连接工厂。他允许框架的容器来隐藏连接池和翻译管理问题对于应用代码来说。作为一个开发者，你不需要了解如何连接数据库的细节。这是管理设置数据库时需要知道的。你只需要完成开发和测试代码就可以，但是你不需要知道如何配置生产数据源。
When using Spring’s JDBC layer, you obtain a data source from JNDI or you configure your own with a connection pool implementation provided by a third party. Popular implementations are Apache Jakarta Commons DBCP and C3P0. Implementations in the Spring distribution are meant only for testing purposes and do not provide pooling.
当你使用spring的JDBC层，你获得一个来自JNDI的数据源或你配置你自己的数据源包含一个连接池通过第三方的实现。最流行的实现是Apache Jakarta Commons DBCP和C3P0。实现在spring中意味着是为了测试的目的并且不需要提供池。
This section uses Spring’s DriverManagerDataSource implementation, and several additional implementations are covered later.
这一节使用spring的DriverManagerDataSource实现，和一些额外的实现包含在后面。
[Note]
注意
Only use the DriverManagerDataSource class should only be used for testing purposes since it does not provide pooling and will perform poorly when multiple requests for a connection are made.
只使用DriverManagerDataSource类应当只用于测试的目的因为他不需要提供池化并且表现一般当一个连接发送多个请求的时候。
You obtain a connection with DriverManagerDataSource as you typically obtain a JDBC connection. Specify the fully qualified classname of the JDBC driver so that the DriverManager can load the driver class. Next, provide a URL that varies between JDBC drivers. (Consult the documentation for your driver for the correct value.) Then provide a username and a password to connect to the database. Here is an example of how to configure a DriverManagerDataSource in Java code:
你使用DriverManagerDataSource获得一个连接作为你通常的JDBC连接。指定全限定名的JDBC驱动名因此DriverManager可以加载驱动器类。然后提供一个URL给JDBC驱动。（参考你驱动对于参数的文档。）然后提供一个用户名和密码给连接的数据库。这是一个使用Java代码配置DriverManagerDataSource的例子。
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
dataSource.setUrl("jdbc:hsqldb:hsql://localhost:");
dataSource.setUsername("sa");
dataSource.setPassword("");
Here is 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 following 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。为了了解更多的选项来控制池特性，见相应连接实现的参考文档。
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"/>
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"/>
19.3.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 thread-bound connections with, for example, DataSourceTransactionManager.
DataSourceUtils类是一个方便的功能强大的助手类提供了静态方法来获得来自JNDI的连接并且如果需要的话会关闭连接。他支持线程绑定连接，例如，DataSourceTransactionManager。
19.3.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 allow classes using it to query whether the connection should be closed after a given operation. This usage is efficient when you know that you will reuse a connection.
SmartDataSource接口应当被类实现可以提供一个对于关系型数据库的连接。他继承了DataSource接口允许类使用查询来了解连接是否应当被关闭在一个给定的操作之后。这个用法是有效的当你知道你将会重用一个连接。
19.3.4 AbstractDataSource
AbstractDataSource is an abstract base class for Spring’s DataSource implementations that implements code that is common to all DataSource implementations. You extend the AbstractDataSource class if you are writing your own DataSource implementation.
AbstractDataSource是一个抽象的基类对于spring的DataSource实现并且实现代码通常是对于全DataSource实现。你可以继承AbstractDataSource类如果你需要书写你自己的DataSource实现时。
19.3.5 SingleConnectionDataSource
The SingleConnectionDataSource class is an implementation of the SmartDataSource interface that wraps a single Connection that is not closed after each use. Obviously, this is not multi-threading capable.
SingleConnectionDataSource类是SmartDataSource接口的实现不会在每次使用后关闭连接。很明显，他不适用于多线程。
If any client code calls close in the assumption of a pooled connection, as when using persistence tools, set the suppressClose property to true. This setting returns a close-suppressing proxy wrapping the physical connection. Be aware that you will not be able to cast this to a native Oracle Connection or the like anymore.
如果任何客户端代码调用假设是连接池，当我们使用了持久化工具，并且设置suppressClose属性为true。这个设置返回一个close-suppressing代理包裹物理的连接。意识到你将不可能转换这个到一个本地的Oracle连接或其他类似的。
This is primarily a test class. For example, it 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.
这主要是一个测试类。例如，他允许简单的测试代码在应用服务器外，配合简单的JNDI环境。和DriverManagerDataSource相比，他重用了相同的连接，避免了过度创建物理连接。
19.3.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接口的标准实现用于配置普通的JDBC驱动通过bean的属性并且每次都返回一个新的连接。
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 will simply 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.
这个实现是有用的对于测试和独立的JavaEE容器外的环境，不管DataSource的bean在spring的ioc容器中或绑定在简单的JNDI环境中。Connection.close()方法调用将简单关闭连接，因此任何意识到DataSource的持久化代码将会工作。然而，使用JavaBean风格的连接例如commons-dbcp是很简单的在测试环境，并且最好通过DriverManagerDataSource来使用这样的连接池。
19.3.7 TransactionAwareDataSourceProxy
TransactionAwareDataSourceProxy is a proxy for a target DataSource, which 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管理的事务。在这方面，他相似于传统的JNDI的DataSource通过JavaEE服务器来提供。
[Note]
注意
It is rarely desirable to use this class, except when already existing code that must be called and passed a standard JDBC DataSource interface implementation. In this case, it’s possible to still have this code be usable, and at the same time have this code participating in Spring managed transactions. It is generally preferable to write your own new code using the higher level abstractions for resource management, such as JdbcTemplate or DataSourceUtils.
对于使用这个类经常不会令人满意，除非已经存在的代码必须被调用或传递一个标准的JDBC的DataSource接口实现。在这个例子中，这段代码依然可以被使用，并且在同时这段代码参与了spring管理的事务。最好写你自己的代码使用更高级别的资源管理抽象例如JdbcTemplate或DataSourceUtils。
(See the TransactionAwareDataSourceProxy javadocs for more details.)
见TransactionAwareDataSourceProxy的javadocs来了解更多。
19.3.8 DataSourceTransactionManager
The DataSourceTransactionManager class is a PlatformTransactionManager implementation for single JDBC datasources. It binds a JDBC connection from the specified data source to the currently executing thread, potentially allowing for one thread connection per data source.
DataSourceTransactionManager类是PlatformTransactionManager的实现用于单个JDBC的数据源。他绑定一个JDBC连接来自特定的数据源对于当前执行的线程，允许每个线程处理一个连接。
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 like JdbcTemplate use this strategy implicitly. If not used with this transaction manager, the lookup strategy behaves exactly like the common one - it can thus be used in any case.
应用代码要求获得JDBC连接通过DataSourceUtils.getConnection(DataSource)而不是JavaEE的标准的DataSource.getConnection。他抛出非检查异常org.springframework.dao exceptions而不是检查异常SQLExceptions。所有的框架类像JdbcTemplate是用这样内部的策略。如果不使用事务管理，查找策略行为会很一致————可以在任何情况下使用。
The DataSourceTransactionManager class supports 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支持自定义隔离级别和超时可以使用适当的JDBC语句来查询超时。为了后续的支持，应用代码必须使用JdbcTemplate或调用DataSourceUtils.applyTransactionTimeout方法对于每次创建的语句。
This implementation can be used instead of JtaTransactionManager in the single resource case, as it does not require the container to support JTA. Switching between both is just a matter of configuration, if you stick to the required connection lookup pattern. JTA does not support custom isolation levels!
这个实现可以被用于替代JtaTransactionManager在单一的资源情况，并且他不需要容器支持JTA。切换也只是配置的问题，如果你绑定需要的连接查询模式。JTA不支持自定义的隔离级别。
19.3.9 NativeJdbcExtractor
Sometimes you need to access vendor specific JDBC methods that differ from the standard JDBC API. This can be problematic if you are running in an application server or with a DataSource that wraps the Connection, Statement and ResultSet objects with its own wrapper objects. To gain access to the native objects you can configure your JdbcTemplate or OracleLobHandler with a NativeJdbcExtractor.
有时你需要访问特定的JDBC方法不同于标准的JDBC的API，这可能是一个问题如果你运行在一个应用服务器上或者一个DataSource包裹了Connection、Statement和ResultSet。为了访问本地的object你可以配置你的JdbcTemplate或OracleLobHandler使用NativeJdbcExtractor。
The NativeJdbcExtractor comes in a variety of flavors to match your execution environment:
NativeJdbcExtractor有一些来匹配你的执行环境。
SimpleNativeJdbcExtractor
C3P0NativeJdbcExtractor
CommonsDbcpNativeJdbcExtractor
JBossNativeJdbcExtractor
WebLogicNativeJdbcExtractor
WebSphereNativeJdbcExtractor
XAPoolNativeJdbcExtractor
Usually the SimpleNativeJdbcExtractor is sufficient for unwrapping a Connection object in most environments. See the javadocs for more details.
通常SimpleNativeJdbcExtractor是足够的对于包裹一个Connection的object在大部分环境。详见javadocs。
19.4 JDBC batch operations
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驱动提供提高性能如果你批量调用多个方法对于相同的prepared statement。通过分组更新到批量减少了往返于数据库和应用之间。
19.4.1 Basic batch operations with the JdbcTemplate
使用JdbcTemplate进行基本的批量操作
You accomplish JdbcTemplate batch processing by implementing two methods of a special interface, BatchPreparedStatementSetter, and passing that in as the second parameter in your batchUpdate method call. Use the getBatchSize method to provide the size of the current batch. Use the setValues method to set the values for the parameters of the prepared statement. This method will be called the number of times that you specified in the getBatchSize call. The following example updates the actor table based on entries in a list. The entire list is used as the batch in this example:
你完成JdbcTemplate批量操作通过实现特定接口中的两个方法，BatchPreparedStatementSetter然后将其传递给batchUpdate方法的第二个参数中。使用getBatchSize方法来提供当前批量的执行数量。使用setValues方法来设置prepared statement的参数值。这个方法将被调用一定的次数根据你指定的getBatchSize调用。下面的例子更新了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) {
int[] updateCounts = jdbcTemplate.batchUpdate("update t_actor set first_name = ?, " +
"last_name = ? where id = ?",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, actors.get(i).getFirstName());
ps.setString(2, actors.get(i).getLastName());
ps.setLong(3, actors.get(i).getId().longValue());
}
public int getBatchSize() {
return actors.size();
}
});
return updateCounts;
}
// ... additional methods
}
If you are processing a stream of updates or reading from a file, then 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 allows you to interrupt a batch once the input source is exhausted. The isBatchExhausted method allows you to signal the end of the batch.
如果你在执行一个更新或读文件的操作，你可以有一个更好的批量大小，但是最后的批量不会有那些数量的实体。在这个例子中你可以使用InterruptibleBatchPreparedStatementSetter接口，允许你打断批量当输入源耗尽的时候。isBatchExhausted方法允许你标记批量的结束。
19.4.2 Batch operations with a List of objects
批量操作一个object列表
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. 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 SqlParameterSource.createBatch method to create this array, passing in either an array of JavaBeans or an array of Maps containing the parameter values.
JdbcTemplate和NamedParameterJdbcTemplate提供了方法用于执行批量更新。作为代替实现一个特定的批量接口，你提供所有的参数值在你的调用中作为一个列表。框架查找这些值并且使用内部的prepared statement来进行设置。API不同依赖于你是否使用的命名参数。对于命名参数你提供一个SqlParameterSource数组，对于批量的每个实体。你可以使用SqlParameterSource.createBatch方法来创建这个数组，传递JavaBeans数组或Maps数组包含参数值。
This 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(final List<Actor> actors) {
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(
"update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
batch);
return updateCounts;
}
// ... additional methods
}
For an SQL statement using 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语句使用典型的问号占位符，你可以传递一个列表包含一个object数组，内容是更新的值。这个object数组必须对于每个占位符都有相应的参数值并且他必须以相同的顺序和他们在语句中定义的顺序相同。
The same example using classic JDBC "?" placeholders:
相同的例子使用了经典的问号占位符：
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);
}
int[] updateCounts = jdbcTemplate.batchUpdate(
"update t_actor set first_name = ?, last_name = ? where id = ?",
batch);
return updateCounts;
}
// ... additional methods
}
All of the above batch update methods 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 -2 value.
上面所有的批量更新方法返回一个int数组包含每条批量语句更新的行的数量。这个数量是来自JDBC驱动的。如果无法返回计数，JDBC驱动将返回-2作为代替。
19.4.3 Batch operations with multiple batches
多个批量的批量操作
The last 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 of course do this with the methods mentioned above 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 containing 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语句，包含参数的object的集合，更新的数量对于每个批量和ParameterizedPreparedStatementSetter用于设置参数的值对于prepared statement。框架查找提供的值并且将其分成更新调用根据指定的批量大小。
This example shows a batch update using 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,
new ParameterizedPreparedStatementSetter<Actor>() {
public void setValues(PreparedStatement ps, Actor argument) throws SQLException {
ps.setString(1, argument.getFirstName());
ps.setString(2, argument.getLastName());
ps.setLong(3, argument.getId().longValue());
}
});
return updateCounts;
}
// ... additional methods
}
The batch update methods for this call returns an array of int arrays containing 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 executed 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 for 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. If the count is not available, the JDBC driver returns a -2 value.
批量更行方法对于这个调用返回一个int数组包含每个批量影响到的行数。顶级数组的长度指示了批量执行的数量并且二级数组长度指示了某个批量的影响的数量。每次更新的苏亮应当是批量大小对于每个批量指定的值并且只能少不能多，依赖于总共的更新object的提供。更新计数对于每个更新语句是来自JDBC驱动。如果计数无法获取，JDBC驱动将返回-2作为返回值。
扫一扫
212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
