19.5 Simplifying JDBC operations with the SimpleJdbc classes
简化JDBC操作通过使用SimpleJdbc类
The SimpleJdbcInsert and SimpleJdbcCall classes provide a simplified configuration by taking advantage of database metadata that can be retrieved through the JDBC driver. This means there is 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.
SimpleJdbcInsert和SimpleJdbcCall类提供了简化的配置对于数据库的元数据可以通过JDBC驱动获取。这意味着不需要预先配置尽管你可以覆盖并关闭元数据处理如果你可以提供你代码的所有细节。
19.5.1 Inserting data using SimpleJdbcInsert
使用SimpleJdbcInsert来实现数据的插入
Let’s 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; simply create a new instance and set the table name using the withTableName method. Configuration methods for this class follow the "fluid" style that returns the instance of the SimpleJdbcInsert, which allows you to chain all configuration methods. This example uses only one configuration method; you will see examples of multiple ones later.
让我们开始查看如何使用SimpleJdbcInsert类通过最小化的配置。你应当实例化SimpleJdbcInsert在数据访问层的初始化方法中。对于这个例子，初始化方法是setDataSource方法。你不需要继承SimpleJdbcInsert类，简单的创建一个新的实例并且设置表名通过使用withTableName方法。对于这个类的配置方法遵循链式的风格并且返回SimpleJdbcInsert的实例使得你可以通过链式的方式调用所有的方法。这个例子只使用了一个配置方法，你将看到多个例子在后面。
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
The execute method used here takes a plain java.utils.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 in order to construct the actual insert statement.
执行方法在这里需要一个java.util.Map作为参数。这是重要的对于Map的key是匹配表中的列名定义在数据库中。这是因为我们读取元数据根据顺序来构建插入语句。
19.5.2 Retrieving auto-generated keys using SimpleJdbcInsert
使用SimpleJdbcInsert获得自动生成的key
This example uses the same insert as the preceding, but instead of passing in the id it retrieves the auto-generated key and sets it on the new Actor object. When you create the SimpleJdbcInsert, in addition to specifying the table name, you specify the name of the generated key column with the usingGeneratedKeyColumns method.
这个例子使用了相同插入作为前置，但是替代了传递id并且获得自动生成的key设置在新的Acotr的object中。当你创建SimpleJdbcInsert，并指定了表名，你指定了生成键的列通过使用usingGeneratedKeyColumns方法。
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
The main difference when executing the insert by 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 our 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, then you can use a KeyHolder that is returned from the executeAndReturnKeyHolder method.
主要的区别当执行插入语句通过第二种方法时你不需要添加id到Map中并且你可以调用executeAndReturnKey方法。这个方法返回一个java.lang.Number的object用于创建一个数据类型的实例使用在我们的实体类中。你在这里不能依赖所有的数据库来返回特定的Java类，java.lang.Number是一个你可以依靠的基类。如果你有多个自动生成的列或者需要生成的不是数值类型，你可以使用KeyHolder，他可以从executeAndReturnKeyHolder方法中返回。
19.5.3 Specifying columns for a SimpleJdbcInsert
使用SimpleJdbcInsert指定列
You can limit the columns for an insert by specifying a list of column names with the usingColumns method:
你可以限制插入的列通过定义一个包含列名的列表使用usingColumns方法：
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
The execution of the insert is the same as if you had relied on the metadata to determine which columns to use.
插入的执行是相同的如果你依赖于列使用的元数据。
19.5.4 Using SqlParameterSource to provide parameter values
使用SqlParameterSource来提供参数值
Using a Map to provide parameter values works fine, but it’s not the most convenient class to use. Spring provides a couple of implementations of the SqlParameterSource interface that can be used instead.The first one is BeanPropertySqlParameterSource, which is a very convenient class if you have a JavaBean-compliant class that contains your values. It will use the corresponding getter method to extract the parameter values. Here is an example:
使用一个Map来提供参数值是很好的，但是他不是最方便的调用方式。spring提供了一个SqlParameterSource接口实现可以作为替代使用。首先BeanPropertySqlParameterSource是一个非常方便的类如果你有一个符合JavaBean规范的类并且包含你的值。他将使用相应的get方法来提取参数值。下面是一个例子：
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
Another option is the MapSqlParameterSource that resembles a Map but provides a more convenient addValue method that can be chained.
另一个选择是MapSqlParameterSource类似一个Map但是提供了更方便的addValue方法可以被使用。
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
As you can see, the configuration is the same; only the executing code has to change to use these alternative input classes.
就如同你看到的，配置是相同的，只是执行代码有所改变且输入类是不同的。
19.5.5 Calling a stored procedure with SimpleJdbcCall
使用SimpleJdbcCall来调用存储过程
The SimpleJdbcCall class leverages metadata in the database to look up names of in and out parameters, so that you do not have to declare them explicitly. 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.
SimpleJdbcCall类利用数据库中的元数据来查找输入和输出参数。因此你不需要明确定义他们。你可以定义参数如果你倾向于这样做的话或如果你有类似ARRAY或STRUCT这样的参数不能直接匹配成Java类。第一个例子展示了一个简单的存储过程返回以VARCHAR和DATE类型的范围来自MySQL数据库。这个例子读取指定的actor实体并返回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 you are looking up. The out parameters return the data read from the table.
in_id参数包含了你用来查找actor的id。输出参数将返回来自表中的数据。
The SimpleJdbcCall is declared in a similar manner to the SimpleJdbcInsert. You should instantiate and configure the class in the initialization method of your data access layer. Compared to the StoredProcedure class, you don’t have to create a subclass and you don’t have to declare parameters that can be looked up in the database metadata. Following is an example of a SimpleJdbcCall configuration using the above stored procedure. The only configuration option, in addition to the DataSource, is the name of the stored procedure.
SimpleJdbcCall是定义了相同的方式对于SimpleJdbcInsert。你应当在你的数据访问层实例化和配置类在初始化的方法中。和StoredProcedure类相比较，你不要创建一个子类并且你不需要定义可以在数据库元数据中可以查找到的参数。下面的例子是一个SimpleJdbcCall配置并使用上面的存储过程。对于数据源唯一的配置选项是存储过程的名字。
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall procReadActor;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
The code you write for the execution of the call involves creating an SqlParameterSource containing the IN parameter. It’s important to match the name provided for the input value with that of the parameter name declared in the stored procedure. 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.
这段代码用于执行调用创建SqlParameterSource包含输入的参数。重要的是匹配给定的输入值的名字和定义在存储过程中的参数名。这个例子不需要匹配因为你使用元数据来决定数据库object应当依赖于存储过程。定义在存储过程中的内容是不必要的因为他存储在数据库中。一些数据库将name全部设置为答谢当其他使用小写时需要指定。
The execute method takes the IN parameters and returns a Map containing 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参数定义在存储过程的名字中。在这个例子中他们是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 create your own JdbcTemplate and set the setResultsMapCaseInsensitive property to true. Then you pass this customized JdbcTemplate instance into the constructor of your SimpleJdbcCall. Here is an example of 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
}
By taking this action, you avoid conflicts in the case used for the names of your returned out parameters.
通过采取这样的行为，你避免了冲突对于使用你out返回参数的名字。
19.5.6 Explicitly declaring parameters to use for a SimpleJdbcCall
明确定义参数用于SimpleJdbcCall
You have seen how the parameters are deduced based on metadata, but you can declare then explicitly if you wish. You do this 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来定义。
[Note]
注意
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. We also support metadata lookup of stored functions for MySQL, Microsoft SQL Server, and Oracle.
明确的定义是必要的如果你使用的数据库不是一个支持spring的数据库。当前spring支持元数据查找对于存储过程调用对于以下的数据库：Apache Derby，DB2、MySQL、Microsoft SQL Server、Oracle和Sybase。我们已经支持元数据查找存储函数的是MySQL、Microsoft SQL Server和Oracle。
You can opt to declare one, some, or all the parameters explicitly. The parameter metadata is still used where you do not declare parameters explicitly. To bypass all processing of metadata lookups for potential parameters and only use the declared parameters, you 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 the 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, using 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
}
The execution and end results of the two examples are the same; this one specifies all details explicitly rather than relying on metadata.
两个例子的执行和结果是相同的，这一个定义了所有的细节相对于元数据来说。
19.5.7 How to define SqlParameters
如何定义SqlParameters
To define a parameter for the SimpleJdbc classes and also for the RDBMS operations classes, covered in Section 19.6, “Modeling JDBC operations as Java objects”, you use an SqlParameter or one of its subclasses. You typically specify the parameter name and SQL type in the constructor. The SQL type is specified using the java.sql.Types constants. We have already seen declarations like:
为了定义一个SimpleJdbc类的参数并且用于RDBMS操作类，包含在章节19.6中，“JDBC模型操作作为Java对象”，你可以使用SimpleJdbc或其中一个子类。你通常定义参数名和SQL类型在构造器中。SQL类型定义使用java.sql.Types常量。我们已经看到了如下的定义：
new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),
The first line with the SqlParameter declares an IN parameter. IN parameters can be used for both stored procedure calls and for queries using the SqlQuery and its subclasses covered in the following section.
第一行使用了SqlParameter定义了一个in参数。in参数可以被用于存储过程调用并且对于查询使用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值并且也可以返回数据。
[Note]
注意
Only parameters declared as SqlParameter and SqlInOutParameter will be used to provide input values. This is different from the StoredProcedure class, which for backwards compatibility reasons allows input values to 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提供定义自定义处理返回值的功能。
19.5.8 Calling a stored function using SimpleJdbcCall
使用SimpleJdbcCall调用一个存储函数
You 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 we want to make a call to a function, and the corresponding string for a function call is generated. A specialized execute call, executeFunction, is used to execute 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 only have one out parameter. The following example is based on a stored function named get_actor_name that returns an actor’s full name. Here is the MySQL source for this function:
你可以调用一个存储函数和调用存储过程的方法近乎类似，需要你提供函数名而不是存储过程名。你可以使用withFunctionName方法作为配置的一部分用于指定我们希望调用的函数和相应的字符串对于函数调用的生成。一个特定的调用、执行被用于执行函数并返回函数返回值作为特定类型的一个object，意味着你不需要获得来自结果集映射的返回值。一个相似的方便的方法名字为executeObject也可以对存储过程有效并且只有一个out参数。下面的例子基于存储函数get_actor_name并返回一个actor的全名。这是用于MySQL的函数的原型：
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.
为了调用这个函数我们需要在初始化方法中创建SimpleJdbcCall。
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall funcGetActorName;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
The execute method used returns a String containing the return value from the function call.
为了执行方法并返回一个字符串包含函数调用的返回值。
19.5.9 Returning ResultSet/REF Cursor from a SimpleJdbcCall
返回来自SimpleJdbcCall的结果集和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 use the returningResultSet method and declare a RowMapper implementation to be used for a specific parameter. In the case where the result set is returned during the results processing, there are no names defined, so the returned results will have to 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实现用于特定的参数。在例子中当结果集返回在结果处理过程中，没有name被定义因此返回结果将匹配你定义RowMapper实现的顺序。指定的name被用于存储处理结果集列表并从执行语句中返回。
The next example uses a stored procedure that takes no IN parameters and returns all rows from the t_actor table. Here is the MySQL source for this procedure:
下一个例子使用了一个存储过程但是没有in参数并且返回所有t_actor表中的行。这是MySQL的源码：
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 declare the RowMapper. Because the class you want to map to 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.
为了调用这个存储过程你需要定义RowMapper。因为你希望匹配的类满足JavaBean的规则，你可以使用BeanPropertyRowMapper通过传入匹配类来创建通过newInstance方法。
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
}
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.
执行传入一个空的Map因为这个过程没有任何参数。Actors的列表收到结果集并返回给调用者。
19.6 Modeling JDBC operations as Java objects
作为Java的object模型化JDBC操作
The org.springframework.jdbc.object package contains classes that allow you to access the database in a more object-oriented manner. As an example, you can execute queries and get the results back as a list containing business objects with the relational column data mapped to the properties of the business object. You can also execute stored procedures and run update, delete, and insert statements.
org.springframework.jdbc.object包含类允许你访问数据库以一种面向对象的方式。举个例子，你可以执行查询并获得结果作为一个列表包含业务object关于相关的列数据匹配业务object的属性。你也可以执行存储过程并执行更新、删除和插入操作。
[Note]
注意
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 simply calls a method on a JdbcTemplate directly (as opposed to encapsulating a query as a full-blown class).
许多spring的开发者详细不同RDBMS操作类描述如下（除了StoredProcedure类）可以被替换使用直接的JdbcTemplate调用。这个直接写DAO方法类似并简单的调用一个方法直接在JdbcTemplate上（和全部的封装有些相反）。
However, if you are getting measurable value from using the RDBMS operation classes, continue using these classes.
然而，如果你使用RDBMS操作类获得可测值，继续使用这些类。
19.6.1 SqlQuery
SqlQuery is a reusable, threadsafe 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实例可以用于对每一行创建一个object根据结果集的迭代，并且在执行中创建。SqlQuery很少被直接使用因为MappingSqlQuery子类提供了更多方便的实现对于结果集和Java类的匹配。其他实现扩展了SqlQuery是MappingSqlQueryWithParameters和UpdatableSqlQuery。
19.6.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方法用于转换每行结果集提供的内容到一个指定的object类型。下面的例子展示了一个自定义的查询匹配来自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 = ?");
super.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;
}
}
The class extends MappingSqlQuery parameterized with the Actor type. The constructor for this customer query takes the DataSource as the only parameter. In this constructor you call the constructor on the superclass with the DataSource and the SQL that should be executed to retrieve the rows for this query. This SQL will be used to create a PreparedStatement so it may contain place holders for any parameters to be passed in during execution.You must declare each parameter 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 call the compile() method so the statement can be prepared and later executed. 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.
类继承了MappingSqlQuery使用了Actor类型的参数。这个消费者查询的构造器使用了DataSource作为唯一的参数。在这个构造器中你调用父类的构造器使用DataSource应当被执行的sql语句。这个sql将被使用用于创建PreparedStatement因此他可以包含对于每个执行中传入的参数的占位符。你必须指定每个参数使用declareParameter方法传递SqlParameter。SqlParameter使用一个name和JDBC类型定义在java.sql.Types中。在你指定所有参数后，你调用compile方法因此声明可以被准备并且在后面执行。这个类是线程安全的当他编译之后，因此这些实例被创建当DAO被初始化可以保存实力变量并且被重用。
private ActorMappingQuery actorMappingQuery;
@Autowired
public void setDataSource(DataSource dataSource) {
this.actorMappingQuery = new ActorMappingQuery(dataSource);
}
public Customer getCustomer(Long id) {
return actorMappingQuery.findObject(id);
}
The method in this example retrieves the customer with the id that is passed in as the only parameter. Since we only want one object returned we simply call the convenience method findObject with the id as parameter. If we had instead a query that returned a list of objects and took additional parameters then we would use one of the execute methods that takes an array of parameter values passed in as varargs.
在这个例子中的方法获得有id的cuntomer作为唯一的参数传递。我们执行需要返回简单调用方便的findObject方法使用id参数。如果我们可以替代一个查询并返回一个列表并使用额外的参数当我们可以使用其中一个执行方法并携带参数数组值作为varargs来传递。
public List<Actor> searchForActors(int age, String namePattern) {
List<Actor> actors = actorSearchMappingQuery.execute(age, namePattern);
return actors;
}
19.6.3 SqlUpdate
The SqlUpdate class encapsulates an SQL update. Like a query, an update object is reusable, and like 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, as in the following snippet where it’s simply called execute. However, you don’t have to subclass the SqlUpdate class since it can easily be parameterized by setting SQL and declaring parameters.
SqlUpdate类可以执行sql更新。就像查询一样，更新object是可以重用的，并且类似所有的RdbmsOperation类，一个更新可以有参数并被定义在sql中。这个类提供了一系列的update方法类似于execute方法。SQLUpdate类是concrete的。他可以被继承，例如，添加一个自定义的更新方法，在下面的片段中可以简单的调用执行。然而，你不需要继承SqlUpdate因为他可以被简单的参数通过设置SQL并且定义参数。
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);
}
}
19.6.4 StoredProcedure
The StoredProcedure class is a superclass for object abstractions of RDBMS stored procedures. This class is abstract, and its various execute(..) methods have protected access, preventing use other than through a subclass that offers tighter typing.
StoredProcedure类是一个超类对于抽象的RDBMS存储过程。这个类是抽象的并且的他的不同execute方法是保护的，防止使用其他的子类提供了一个tighter typing。
The inherited sql property will be the name of the stored procedure in the RDBMS.
继承的sql属性是在RDBMS中存储过程的名字。
To define a parameter for the StoredProcedure class, you use an SqlParameter or one of its subclasses. You must specify the parameter name and SQL type in the constructor like in the following code snippet. The SQL type is specified using the java.sql.Types constants.
为了定义一个参数对于StoredProcedure类，你使用一个SqlParameter或他的子类。你必须定义参数名和sql类型在构造器中就像下面的代码片段。sql类型被指定使用java.sql.Types常量。
new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),
The first line with the SqlParameter declares an IN parameter. IN parameters can be used for both stored procedure calls and for queries using the SqlQuery and its subclasses covered in the following section.
第一行使用了SqlParameter定义了in参数。in参数可以被使用对于存储过程调用和查询使用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参数，参数提供在存储过程中并且返回一个值。
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 enables you to define customized handling of the return values.
对于in参数，需要name和sql类型，你可以指定一定范围的数据或一个类型名对于自定义数据库类型。对于out参数你可以提供一个RowMapper来处理行的返回值来自REF游标。另一个选项是指定一个SqlReturnType允许你定义一个自定义来处理返回值。
Here is an example of a simple DAO that 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, but if you need to reuse the StoredProcedure you declare it as a top-level class. This example has no input parameters, but an output parameter is declared as a date type using the class SqlOutParameter. The execute() method executes 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, using the parameter name as the key.
这是一个例子对于简单DAO使用一个存储过程调用一个函数，sysdate，来自任意的Oracle数据库。为了使用存储过程你必须创建一个类继承StoredProcedure。在这个例子中，StoredProcedure类是一个内部类，但是如果你需要重用StoredProcedure你可以定义为一个顶层类。这个例子中没有输入参数，但是一个输出参数被定义作为一个数据类型使用SqlOutParameter类。execute方法指定存储过程并提取返回的data来自结果集。结果集有一个entry对于每个out参数，在这个例子中只有一个，使用参数名作为key。
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;
}
}
}
The following example of a StoredProcedure has two output parameters (in this case, Oracle REF cursors).
下面的StoredProcedure例子有两个out参数（在这个例子中，Oracle的REF游标）
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
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>());
}
}
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 code for the two RowMapper implementations is provided below.
注意如果覆盖declareParameter方法中的变量被使用在TitlesAndGenresStoredProcedure构造器中并传递RowMapper实现实例，这是一个方便有力的方式来重用已有的功能。对于两个RowMapper实现的代码提供如下：
The TitleMapper class maps a ResultSet to a Title domain object for each row in the supplied ResultSet:
TitleMapper类匹配一个结果集对于Title实体object对于每一行在提供了结果集中：
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Title;
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;
}
}
The GenreMapper class maps a ResultSet to a Genre domain object for each row in the supplied ResultSet.
GenreMapper类匹配结果集对于Genre实体object对于每一个结果集提供的行。
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Genre;
public final class GenreMapper implements RowMapper<Genre> {
public Genre mapRow(ResultSet rs, int rowNum) throws SQLException {
return new 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 superclass' untyped execute(Map parameters) method (which has protected access); for example:
为了传递参数给存储过程需要有一个或多个输入参数在RDBMS的定义中，你可以编码一个强类型的execute方法可以委托给超类的非强类型execute（Map参数）方法（是保护的访问权限），例如：
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
import javax.sql.DataSource;
import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
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);
}
}
19.7 Common problems with parameter and data value handling
通用的参数问题和数据值处理
Common problems with parameters and data values exist in the different approaches provided by the Spring Framework JDBC.
通用的参数问题和数据值存在不同的方法中通过spring的框架JDBC来提供。
19.7.1 Providing SQL type information for parameters
提供参数的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 using constant values from the java.sql.Types class. Provide one entry for each parameter.
许多JdbcTemplate的更新和查询方法可以接收一个数组形式的额外参数。这个数组将被用于指定sql类型对于相应的参数通过使用java.sql.Types类中的常量。对于每个参数提供一个entry。
You can use the SqlParameterValue class to wrap the parameter value that needs this additional information.Create a new instance for each value and pass in the SQL type and parameter value in the constructor. You can also provide an optional scale parameter for numeric values.
你可以使用SqlParameterValue类来包裹参数值需要额外的信息。创建一个新的实例对于每个值并且传递以sql类型和构造器中的参数值。你也可以提供一个可选范围的参数值。
For methods working with named parameters, 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类型。
19.7.2 Handling BLOB and CLOB objects
处理BLOB和CLOB的object
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. The LobHandler provides access to a LobCreator class, through the getLobCreator method, used for creating new LOB objects to be inserted.
你可以存储图像、其他二进制数据和大型文本到数据库中。这些可以被称为BLOBs（大型二进制object）对于二进制数据和CLOBs（大型字符object）对于字符数据。在spring中你可以处理这些大型object通过直接使用JdbcTemplate也可以使用更高的抽象通过RDBMS的object来提供和SimpleJdbc类。所有这些方法定义在LobHandler接口中对于实际的LOB（大型object）数据。LobHandler提供了对于LobCreator类的访问，通过getLobCreator方法，用于创建新的LOB的object用于插入操作。
The LobCreator/LobHandler provides the following support for LOB input and output:
LobCreator/LobHandler提供了下面的支持对于LOB的输入和输出：
BLOB
byte[] — getBlobAsBytes and setBlobAsBytes
InputStream — getBlobAsBinaryStream and setBlobAsBinaryStream
CLOB
String — getClobAsString and setClobAsString
InputStream — getClobAsAsciiStream and setClobAsAsciiStream
Reader — getClobAsCharacterStream and setClobAsCharacterStream
The next example shows how to create and insert a BLOB. Later you will see 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 you use to set the values for the LOB columns in your SQL insert statement.
这个例子使用了JdbcTemplate和AbstractLobCreatingPreparedStatementCallback的实现类。他实现了一个方法，setValues。这个方法提供了一个LobCreator，使得你可以用于设置clob列的值在你的sql插入语句中。
For this example we assume that there is a variable, lobHandler, that already is set to an instance of a DefaultLobHandler. You typically set this value through dependency injection.
对于这个例子，我们假设这是一个变量，lobHandler，已经设置为DefaultLobHandler的实例。你通常会直接使用依赖注入。
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();
1 Pass in the lobHandler that in this example is a plain DefaultLobHandler.
传递lobHandler，在这个例子中是一个普通的DefaultLobHandler
2 Using the method setClobAsCharacterStream, pass in the contents of the CLOB.
使用setClobAsCharacterStream方法，传递clob的内容
3 Using the method setBlobAsBinaryStream, pass in the contents of the BLOB.
使用setBlobAsBinaryStream方法，传递blob内容
[Note]
注意
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 will use the JDBC 4.0 variants of the set-stream methods without a length parameter; otherwise, it will pass the specified length on to the driver.
如果你调用LobCreator中的setBlobAsBinaryStream、setClobAsAsciiStream或者setClobAsCharacterStream方法返回DefaultLobHandler.getLobCreator()，你可以选择指定一个negative值对于contentLength参数。如果指定content的长度为negative，DefaultLobHandler将会使用JDBC4.0变量的set-stream方法而不是一个长度的参数，此外，他将传递指定的长度到驱动中。
Consult the documentation for the JDBC driver in use to verify support for streaming a LOB without providing the content length.
查阅JDBC驱动的文档通过使用验证支持对于LOB流而不提供内容的长度。
Now it’s 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.
现在我们来读取数据库中LOB数据。再次，我们使用JdbcTemplate对于相同的实例变量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; } });
1 Using the method getClobAsString, retrieve the contents of the CLOB.
使用getClobAsString方法来获得clob的内容
2 Using the method getBlobAsBytes, retrieve the contents of the BLOB.
使用getBlobAsBytes方法来获得blob的内容
19.7.3 Passing in lists of values for IN clause
给in参数传递list值
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. 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 and JdbcTemplate takes the latter approach. Pass in the values as a java.util.List of primitive objects. This list will be used to insert the required placeholders and pass in the values during the statement execution.
sql标准允许一个表达式来选择行包括一个列表值。一个通常的例子是select * from T_ACTOR where id in (1, 2, 3)。这个变量列表不是直接支持prepared statements通过JDBC的标准，你不能定义一个占位符。你需要一些占位符来实现或者你需要动态生成sql字符串根据你需要的参数个数。命名参数支持通过NamedParameterJdbcTemplate和JdbcTemplate需要后续的方法。传递一个基本类型的java.util.List。这个列表将被用于插入需要的占位符在语句执行的过程中。
[Note]
注意
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. Oracle’s limit is 1000.
注意当传递许多值时。JDBC标准不需要保证你可以使用多余100个参数在表达式列表中。不同的数据库可能会超过这个值，但是通常很难限制参数的多少。Oracle的限制是1000。
In addition to the primitive values in the value list, you can create a java.util.List of object arrays. This list would support multiple expressions 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.
此外对于基本类型的列表，你可以创建一个object数组的列表。这个列表将支持多个表达式定义在子句中例如select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop'\))。当然需要数据库支持这样的语法。
19.7.4 Handling complex types for stored procedure calls
处理复杂类型的存储过程调用
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可以传递给存储过程。
Here is an example of returning the value of an Oracle STRUCT object of the user declared type ITEM_TYPE. 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.
这里有一个例子关于返回一个Oracle STRUCT的object有关用户定义类型ITEM_TYPE。SqlReturnType接口有一个方法名字为getTypeValue必须被实现。这个接口被作为SqlOutParameter定义的一部分。
final TestItem = new TestItem(123L, "A test item",
new SimpleDateFormat("yyyy-M-d").parse("2010-12-31"));
declareParameter(new SqlOutParameter("item", OracleTypes.STRUCT, "ITEM_TYPE",
new SqlReturnType() {
public Object getTypeValue(CallableStatement cs, int colIndx, int sqlType, String typeName) throws SQLException {
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;
}
}));
You use the SqlTypeValue to pass in the value of a Java object like TestItem into 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 StructDescriptors, as shown in the following example, or ArrayDescriptors.
你使用SqlTypeValue传递一个Java的object就像TestItem到存储过程中。SqlTypeValue接口有一个方法名字为createTypeValue你必须要实现。激活连接被传入并且你可以使用它来创建一个特定数据库object例如StructDescriptors，展示如下或ArrayDescriptors。
final 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;
}
};
This SqlTypeValue can now be added to the Map containing the input parameters for the execute call of the stored procedure.
这个SqlTypeValue现在可以被天骄到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.
另一个使用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;
}
};
19.8 Embedded database support
嵌入数据库的支持
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用于嵌入数据库类型和数据源实现。
19.8.1 Why use an embedded database?
为什么使用嵌入式数据库？
An embedded database is 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 SQL during development.
一个嵌入式数据库是有用的在一个项目的开发阶段因为他的轻便性。好处包括简单的配置、快速的启动时间、可测试和在开发中快速执行sql。
19.8.2 Creating an embedded database using Spring XML
使用spring的xml来创建嵌入数据库
If you want to expose an embedded database instance as a bean in a Spring ApplicationContext, use the embedded-database tag in the spring-jdbc namespace:
如果你希望扩展一个嵌入数据库实例作为一个spring的ApplicationContext的一个bean，使用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 populated with SQL from schema.sql and test-data.sql resources in the root of the classpath. In addition, as a best practice, the embedded database will be assigned a uniquely generated name. The embedded database is made available to the Spring container as a bean of type javax.sql.DataSource which can then be injected into data access objects as needed.
前面的配置创建了一个嵌入的HSQL数据库使用了scheam.sql和test-data.sql资源作为classpath的根。此外，最好的实践，嵌入式数据库可以生成唯一的名字。嵌入数据库使得在spring容器中可用作为一个javax.sql.DataSource类型的bean可以被注入到需要数据访问的object。
19.8.3 Creating an embedded database programmatically
变成创建嵌入数据库
The EmbeddedDatabaseBuilder class provides a fluent API for constructing an embedded database programmatically. Use this when you need to create an embedded database in a standalone environment or in a standalone integration test like in the following example.
EmbeddedDatabaseBuilder类提供了丰富的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()
Consult the Javadoc for EmbeddedDatabaseBuilder for further details on all supported options.
参考EmbeddedDatabaseBuilder的javadocs来获得更多支持选项的细节。
The EmbeddedDatabaseBuilder can also be used to create an embedded database using Java Config like in the following example.
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();
}
}
19.8.4 Selecting the embedded database type
选择嵌入数据库的类型
Using HSQL
Spring supports HSQL 1.8.0 and above. HSQL is the default embedded database if no type is specified explicitly. To specify HSQL explicitly, set the type attribute of the embedded-database tag to HSQL. If you are using the builder API, call the setType(EmbeddedDatabaseType) method with EmbeddedDatabaseType.HSQL.
spring支持HSQL1.8.0以及以上的版本。HSQL作为默认的嵌入数据库如果没有指定类型的时候。为了明确指定HSQL，设置embedded-database标签的type属性对于HSQL。如果你使用builder的API，调用setType(EmbeddedDatabaseType)方法使用EmbeddedDatabaseType.HSQL参数。
Using H2
Spring supports the H2 database as well. To enable H2, set the type attribute of the embedded-database tag to H2. If you are using the builder API, call the setType(EmbeddedDatabaseType) method with EmbeddedDatabaseType.H2.
spring也支持H2数据库。为了启用H2，设置embedded-database标签的type属性为H2.如果你使用了builder的API，调用setType(EmbeddedDatabaseType)方法使用EmbeddedDatabaseType.H2参数。
Using Derby
Spring also supports Apache Derby 10.5 and above. To enable Derby, set the type attribute of the embedded-database tag to DERBY. If you are using the builder API, call the setType(EmbeddedDatabaseType) method with EmbeddedDatabaseType.DERBY.
spring也支持Apache的Derby 10.5以及以上的版本。为了启用Derby，设置embedded-database标签的type属性为DERBY。如果你使用builder的API，调用setType(EmbeddedDatabaseType)方法使用EmbeddedDatabaseType.DERBY参数。
19.8.5 Testing data access logic with an embedded database
使用嵌入数据库测试数据访问的逻辑
Embedded databases provide a lightweight way to test data access code. The following is a data access integration test template that uses an embedded database. Using a template like this 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 Section 19.8.2, “Creating an embedded database using Spring XML” and Section 19.8.3, “Creating an embedded database programmatically”.
嵌入数据提供了轻量级的方法来测试数据的访问。下面的数据访问集成测试模板使用了一个嵌入数据库。使用一个模板类似于这个可能单次有用对于当嵌入数据库不需要跨测试类重用的时候。然而，如果你希望创建一个内嵌数据库在测试中可以共享，考虑使用spring的TestContext框架并且配置一个内嵌数据库作为一个在spring ApplicationContext中的bean，描述在19.8.2节，“使用spring的xml创建一个嵌入数据库”和19.8.3节，“编程创建嵌入式数据库”
public class DataAccessIntegrationTestTemplate {
private EmbeddedDatabase db;
@Before
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( /* ... */ );
}
@After
public void tearDown() {
db.shutdown();
}
}
19.8.6 Generating unique names for embedded databases
为嵌入数据库生成唯一的名字
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 (i.e., within the same JVM process) –- for example, integration tests against embedded databases whose ApplicationContext configuration only differs 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 Config) will set 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 (i.e., often something like "dataSource"). Thus, subsequent attempts to create an embedded database will not result in a new database. Instead, the same JDBC connection URL will be reused, and attempts to create a new embedded database will 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框架4.2提供了支持用于为嵌入数据库生成唯一的名字。为了支持使用生成的名字，使用下面几个选项之一。
EmbeddedDatabaseFactory.setGenerateUniqueDatabaseName()
EmbeddedDatabaseBuilder.generateUniqueName()
<jdbc:embedded-database generate-name="true" … >
19.8.7 Extending the embedded database support
扩展嵌入数据库支持
Spring JDBC embedded database support can be extended 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实现，例如一个连接处来管理嵌入数据库连接。
You are encouraged to contribute back extensions to the Spring community at jira.spring.io.
欢迎你来扩展spring的社区在jira.spring.io。
19.9 Initializing a 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, but sometimes you need to initialize an instance running on a server somewhere.
org.springframework.jdbc.datasource.init包提供了支持对于初始化已有的DataSource。嵌入数据库支持一个选项用于创建和初始化一个DataSource为一个应用，但是有时你有时需要来初始化一个实例在服务器上运行。
19.9.1 Initializing a database using Spring XML
使用spring的xml来初始化数据库
If you want to initialize a database and you can provide a reference to a DataSource bean, use the initialize-database tag in the spring-jdbc namespace:
如果你希望实例化一个数据库，你可以提供一个引用对于DataSource的bean，使用spring-jdbc命名空间中的initialize-database标签。
<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 example above executes the two scripts specified 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 (e.g. classpath*:/com/foo/**/sql/*-data.sql). If a pattern is used, the scripts are executed in lexical order of their URL or filename.
上面两个脚本执行的例如对于数据库：第一个脚本创建一个schema，并且第二个表中有测试数据集合。脚本的位置也可以使用通配符和平常一样并且可以使用spring的资源风格（例如，classpath*:/com/foo/**/sql/*-data.sql）。如果一个模式被使用，脚本被执行以他们URL或文件名的字符顺序。
The default behavior of the database initializer is to unconditionally execute the scripts provided. This will not always be what you want, for instance, if you are executing 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 (as shown above) of creating the tables first and then inserting the data — the first step will fail 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. This can be set according to the environment (e.g. to pull a boolean value from system properties or an environment bean), for example:
然而，对于获得更多的控制来创建和删除已有的数据，xml的命名空间提供了额外的选项。第一个是一个标志来切换是否初始化。这可以被设置对于环境（例如，获得来自系统属性的值或一个环境bean的值），例如：
<jdbc:initialize-database data-source="dataSource"
enabled="#{systemProperties.INITIALIZE_DATABASE}">
<jdbc:script location="..."/>
</jdbc:initialize-database>
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 executes from the scripts, for example:
第二个选项来控制就是对于已有的数据可以运行失败。对于这种你可以控制初始化的能力来忽略相应的SQL中的错误在执行脚本中，例如：
<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
<jdbc:script location="..."/>
</jdbc:initialize-database>
In this example we are saying we expect that sometimes the scripts will be executed against an empty database, and there are some DROP statements in the scripts which 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.
在这个例子中我们说我们期望有时脚本将被执行在一个空数据库中并且他们有时是删除声明在脚本中并且可能会失败。因此失败的SQL删除声明将会被忽略，但是其他的错误将导致异常。如果sql方言不支持如果存在才删除（或类似的）的话这是有用的但是你希望无条件的删除数据在重新创建之前。在这个例子中第一个脚本通常是删除声明的集合，然后是一些创建声明。
The ignore-failures option can be set to NONE (the default), DROPS (ignore failed drops), or ALL (ignore all failures).
在ignore-failures可以被设置为NONE（默认）、DEOPS（忽略失败删除）或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, for example:
每个声明使用分号分隔，或使用新的一行如果分行不能表达表达所有的脚本内容。你可以控制全局或使用脚本来控制脚本，例如：
<jdbc:initialize-database data-source="dataSource" separator="@@">
<jdbc:script location="classpath:com/foo/sql/db-schema.sql" separator=";"/>
<jdbc:script location="classpath:com/foo/sql/db-test-data-1.sql"/>
<jdbc:script location="classpath:com/foo/sql/db-test-data-2.sql"/>
</jdbc:initialize-database>
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 override that default for the db-schema script.
在这个例子中，两个测试数据脚本使用了@@作为声明分隔符并且只有db-schema.sql使用了分号。指定默认配置是@@但是对于db-schema脚本来说是可以覆盖的。
If you need more control than you get from the XML namespace, you can simply use the DataSourceInitializer directly and define it as a component in your application.
如果你希望更多的控制来自xml的命名空间，你可以直接使用DataSourceInitializer来定义你应用中的组件。
Initialization of other components that depend on the database
初始化其他依赖数据库的组件
A large class of applications can just use the database initializer with no further complications: those that do not use the database until after the Spring context has started. If your application is not one of those then you might need to read the rest of this section.
一个庞大的应用类可以使用数据库初始化而不需要复杂的并非，并且不需要使用数据库在spring上下文被创建之后。如果你的应用不是他们其中之一你可能需要阅读这一节的内容。
The database initializer depends on a DataSource instance and executes 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 also use the data source in an initialization callback, then there might be a problem because the data has not yet been initialized. A common example of this is a cache that initializes eagerly and loads data from the database on application startup.
数据库初始化依赖于DataSource实例并且执行他在初始化回调的脚本（类似于init-method在xml的bean的定义中，组件中的@PostConstruct方法或实现InitializingBean的组件的afterPropertiesSet方法）。如果其他的bean依赖相同的数据源并且使用数据库在初始化回调中，他们可能会有问题如果他们没有初始化。一个普通的例子就是缓存提前初始化并且加载了应用启动中数据库的内容。
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.
为了处理这个问题你有两种方法：改变你的缓存的初始化测量改为延迟或保证数据库先被初始化。
The first option 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 up a SmartLifecycle can be automatically started if its autoStartup flag is set, and a Lifecycle can be started manually by calling ConfigurableApplicationContext.start() on the enclosing context.
使得你的缓存和组件分离在初始化的时候并且缓存实现Lifecycle或SmartLifecycle。当应用上下文启动SmartLifecycle可以被自动开始如果他的autoStartup标志被设置，并且Lifecycle可以手动启动通过调用ConfigurableApplicationContext.start()在封闭的上下文中。
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工作的）。
The second option 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 ensure that the database and database initialization are listed first.
回应默认的spring的BeanFactory的行为，bean以注册的顺序被初始化。你可以简单的排列他们通过使用<import/>元素来设定一个集合在xml配置中用于处理应用模块的顺序并且保证数据库初始化在列表的前面。
Separate the DataSource and the business components that use it, and control their startup order by putting them in separate ApplicationContext instances (e.g. the parent context contains the DataSource, and 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应用中被经常使用。
7913
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
