Simplifying JDBC Operations with the SimpleJdbc Classes

通过使用SimpleJdbcInsert类，可以插入数据、检索自动生成的主键并指定要插入的列。Map或SqlParameterSource接口的实现可以用来为参数提供值。

SimpleJdbcCall类用于调用存储过程或函数，它使用数据库元数据查找in和out参数，但也可以显式声明参数。该类还提供对返回结果集或REF游标的支持。

SimpleJdbcInsert 和 SimpleJdbcCall 类可利用可以通过 JDBC 驱动检索的数据库元数据，从而提供简化的配置。这意味着您不需要预先进行大量配置，尽管您可以覆盖或关闭元数据处理，如果您愿意在代码中提供所有详细信息。

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 that you have less to configure up front, although you can override or turn off the metadata processing if you prefer to provide all the details in your code.

Inserting Data by Using SimpleJdbcInsert

我们首先查看配置选项最少的 SimpleJdbcInsert 类。您应该在数据访问层的初始化方法中实例化 SimpleJdbcInsert。对于此示例，初始化方法是 setDataSource 方法。您无需对 SimpleJdbcInsert 类进行子类化。相反，您可以创建一个新实例，并使用 withTableName 方法设置表名。此类的配置方法遵循返回 SimpleJdbcInsert 实例的“流畅”风格，它可以让您链接所有配置方法。以下示例仅使用一个配置方法（我们稍后会展示多个方法的示例）：

We start by looking at the SimpleJdbcInsert class with the minimal amount of configuration options. You should instantiate the SimpleJdbcInsert in the data access layer’s initialization method. For this example, the initializing method is the setDataSource method. You do not need to subclass the SimpleJdbcInsert class. Instead, you can create a new instance and set the table name by using the withTableName method. Configuration methods for this class follow the fluid style that returns the instance of the SimpleJdbcInsert, which lets you chain all configuration methods. The following example uses only one configuration method (we show examples of multiple methods later):

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcInsert insertActor;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

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

	// ... additional methods
}

此处使用的 execute 方法以一个普通的 java.util.Map 作为其唯一参数。这里需要指出的是，用于 Map 的键必须与表（在数据库中定义）的列名匹配。这是因为我们读取元数据来构建实际的插入语句。

The execute method used here takes a plain java.util.Map as its only parameter. The important thing to note here is that the keys used for the Map must match the column names of the table, as defined in the database. This is because we read the metadata to construct the actual insert statement.

Retrieving Auto-generated Keys by Using SimpleJdbcInsert

下一个示例使用和前一个示例相同的插入，但是，它没有传递 id，而是检索自动生成的键并将其设置在新的 Actor 对象上。当它创建 SimpleJdbcInsert 时，除了指定表名，它还使用 usingGeneratedKeyColumns 方法指定已生成键列的名称。以下清单显示了执行方式：

The next example uses the same insert as the preceding example, but, instead of passing in the id, it retrieves the auto-generated key and sets it on the new Actor object. When it creates the SimpleJdbcInsert, in addition to specifying the table name, it specifies the name of the generated key column with the usingGeneratedKeyColumns method. The following listing shows how it works:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcInsert insertActor;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

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

	// ... additional methods
}

使用此第二种方法运行插入时，主要区别在于，您不向 Map 添加 id，并且您调用 executeAndReturnKey 方法。这会返回一个 java.lang.Number 对象，您可以使用该对象创建一个数字类型实例，该类型用于您的域类。您不能依赖所有数据库在此处返回特定的 Java 类。java.lang.Number 是您可以依赖的基本类。如果您有多个自动生成列，或者已生成的值为非数字，则可以使用从 executeAndReturnKeyHolder 方法返回的 KeyHolder。

The main difference when you run the insert by using this second approach is that you do not add the id to the Map, and you call the executeAndReturnKey method. This returns a java.lang.Number object with which you can create an instance of the numerical type that is used in your domain class. You cannot rely on all databases to return a specific Java class here. java.lang.Number is the base class that you can rely on. If you have multiple auto-generated columns or the generated values are non-numeric, you can use a KeyHolder that is returned from the executeAndReturnKeyHolder method.

Specifying Columns for a SimpleJdbcInsert

您可以通过使用 usingColumns 方法指定一列列名来为插入限定列，如下面的示例所示：

You can limit the columns for an insert by specifying a list of column names with the usingColumns method, as the following example shows:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcInsert insertActor;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

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

	// ... additional methods
}

插入的执行与您依赖元数据来确定要使用哪一列时相同。

The execution of the insert is the same as if you had relied on the metadata to determine which columns to use.

Using SqlParameterSource to Provide Parameter Values

使用 Map 提供参数值的效果很好，但这不是最方便的使用类。Spring 提供了 SqlParameterSource 几个实现接口，您可以使用这些实现。第一个是 BeanPropertySqlParameterSource，如果您有一个包含您的值的符合 JavaBean 的类，这是一个非常方便的类。它使用相应的 getter 方法来提取参数值。下面的示例演示了如何使用 BeanPropertySqlParameterSource：

Using a Map to provide parameter values works fine, but it is not the most convenient class to use. Spring provides a couple of implementations of the SqlParameterSource interface that you can use instead. The first one is BeanPropertySqlParameterSource, which is a very convenient class if you have a JavaBean-compliant class that contains your values. It uses the corresponding getter method to extract the parameter values. The following example shows how to use BeanPropertySqlParameterSource:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcInsert insertActor;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

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

	// ... additional methods
}

另一个选项是 MapSqlParameterSource，它类似于 Map，但提供了一个更方便的 addValue 方法，可以进行链接。下面的示例演示了如何使用它：

Another option is the MapSqlParameterSource that resembles a Map but provides a more convenient addValue method that can be chained. The following example shows how to use it:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcInsert insertActor;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

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

	// ... additional methods
}

如您所见，配置是相同的。只有执行代码才必须更改才能使用这些替代输入类。

As you can see, the configuration is the same. Only the executing code has to change to use these alternative input classes.

Calling a Stored Procedure with SimpleJdbcCall

SimpleJdbcCall 类使用数据库中的元数据来查找 in 和 out 参数的名称，以便您不必显式地声明它们。如果您愿意，或者您有无法自动映射到 Java 类的参数（如 ARRAY 或 STRUCT），则可以声明参数。第一个示例显示了一个简单的过程，它仅从 MySQL 数据库中以 VARCHAR 和 DATE 格式返回标量值。示例过程读取指定的 actor 条目，并以 out 参数的形式返回 first_name、last_name 和 birth_date 列。以下列表显示了第一个示例：

The SimpleJdbcCall class uses metadata in the database to look up names of in and out parameters so that you do not have to explicitly declare them. You can declare parameters if you prefer to do that or if you have parameters (such as ARRAY or STRUCT) that do not have an automatic mapping to a Java class. The first example shows a simple procedure that returns only scalar values in VARCHAR and DATE format from a MySQL database. The example procedure reads a specified actor entry and returns first_name, last_name, and birth_date columns in the form of out parameters. The following listing shows the first example:

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;

in_id 参数包含您正在查找的 actor 的 id。out 参数返回从表中读取的数据。

The in_id parameter contains the id of the actor that you are looking up. The out parameters return the data read from the table.

您可以以类似于声明 SimpleJdbcInsert 的方式声明 SimpleJdbcCall。您应该在数据访问层的初始化方法中实例化并配置该类。与 StoredProcedure 类相比，您无需创建子类，也无需声明可以在数据库元数据中查找的参数。以下 SimpleJdbcCall 配置示例使用前面的存储过程（除了 DataSource 外，唯一的配置选项是存储过程的名称）：

You can declare SimpleJdbcCall in a manner similar to declaring SimpleJdbcInsert. You should instantiate and configure the class in the initialization method of your data-access layer. Compared to the StoredProcedure class, you need not create a subclass and you need not to declare parameters that can be looked up in the database metadata. The following example of a SimpleJdbcCall configuration uses the preceding stored procedure (the only configuration option, in addition to the DataSource, is the name of the stored procedure):

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcCall procReadActor;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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


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

		// ... additional methods
}

用于执行调用的代码涉及创建包含 IN 参数的 SqlParameterSource。您必须将提供给输入值的名称与存储过程中声明的参数名称相匹配。大小写不必匹配，因为您使用元数据来确定如何在存储过程中引用数据库对象。存储过程源中指定的并不是存储在数据库中的方式。一些数据库将名称转换为所有大写，而另一些数据库使用小写或使用指定的大小写。

The code you write for the execution of the call involves creating an SqlParameterSource containing the IN parameter. You must match the name provided for the input value with that of the parameter name declared in the stored procedure. 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.

execute 方法获取 IN 参数并返回包含任何 out 参数的 Map，这些参数按存储过程中指定的名称进行键入。在这种情况下，它们是 out_first_name、out_last_name 和 out_birth_date。

The execute method takes the IN parameters and returns a Map that contains any out parameters keyed by the name, as specified in the stored procedure. In this case, they are out_first_name, out_last_name, and out_birth_date.

execute 方法的最后一部分创建一个 Actor 实例，用于返回检索到的数据。同样，重要的是使用 out 参数的名称，因为它们在存储过程中被声明。此外，结果映射中存储的 out 参数名称的大小写与数据库中 out 参数名称的大小写相匹配，这在不同的数据库中可能有所不同。为了使您的代码更具可移植性，您应该执行不区分大小写的查找或指示 Spring 使用 LinkedCaseInsensitiveMap。要执行后者，您可以创建自己的 JdbcTemplate 并将 setResultsMapCaseInsensitive 属性设置为 true。然后，您可以将此自定义的 JdbcTemplate 实例传递给 SimpleJdbcCall 的构造函数。以下示例演示了此配置：

The last part of the execute method creates an Actor instance to use to return the data retrieved. Again, it is important to use the names of the out parameters as they are declared in the stored procedure. Also, the case in the names of the out parameters stored in the results map matches that of the out parameter names in the database, which could vary between databases. To make your code more portable, you should do a case-insensitive lookup or instruct Spring to use a LinkedCaseInsensitiveMap. To do the latter, you can create your own JdbcTemplate and set the setResultsMapCaseInsensitive property to true. Then you can pass this customized JdbcTemplate instance into the constructor of your SimpleJdbcCall. The following example shows this configuration:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcCall procReadActor;

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

	// ... additional methods
}

通过执行此操作，您可以避免为返回的 out 参数名称使用的大小写出现冲突。

By taking this action, you avoid conflicts in the case used for the names of your returned out parameters.

Explicitly Declaring Parameters to Use for a SimpleJdbcCall

在本章前面，我们描述了如何从元数据中推演参数，但如果你愿意，可以显式声明它们。你可以通过创建和配置带有 declareParameters 方法的 SqlParameter，它的输入是一个数量可变的 SimpleJdbcCall 对象来实现这一点。请参阅 SqlParameter 以了解如何定义 SqlParameter。

Earlier in this chapter, we described how parameters are deduced from metadata, but you can declare them explicitly if you wish. You can do so by creating and configuring SimpleJdbcCall with the declareParameters method, which takes a variable number of SqlParameter objects as input. See the next section for details on how to define an SqlParameter.

如果所使用的数据库不是 Spring 支持的数据库，则必须有明确的声明。目前，Spring 支持对以下数据库的存储过程调用的元数据查找：Apache Derby、DB2、MySQL、Microsoft SQL Server、Oracle 和 Sybase。我们还支持对 MySQL、Microsoft SQL Server 和 Oracle 的存储函数的元数据查找。

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.

您可以选择显式声明一个、一些或所有参数。在您没有显式声明参数的情况下，仍然会使用参数元数据。要绕过对潜在参数的所有元数据查找处理并仅使用声明的参数，您可以调用 withoutProcedureColumnMetaDataAccess 方法作为声明的一部分。假设您为数据库函数声明了两个或更多不同的调用签名。在这种情况下，您可以调用 useInParameterNames 来指定要为给定签名包括的 IN 参数名称列表。

You can opt to explicitly declare one, some, or all of the parameters. The parameter metadata is still used where you do not explicitly declare parameters. To bypass all processing of metadata lookups for potential parameters and use only the declared parameters, you can call the method withoutProcedureColumnMetaDataAccess as part of the declaration. Suppose that you have two or more different call signatures declared for a database function. In this case, you call useInParameterNames to specify the list of IN parameter names to include for a given signature.

以下示例演示了一个完全声明的过程调用，并使用了前面示例中的信息：

The following example shows a fully declared procedure call and uses the information from the preceding example:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcCall procReadActor;

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

		// ... additional methods
}

这两个示例的执行和最终结果是相同的。第二个示例显式指定了所有详细信息，而不是依赖元数据。

The execution and end results of the two examples are the same. The second example specifies all details explicitly rather than relying on metadata.

How to Define SqlParameters

要为 SimpleJdbc 类和 RDBMS 操作类（在 Modeling JDBC Operations as Java Objects 中介绍）定义一个参数，您可以使用 SqlParameter 或其子类之一。为此，您通常需要在构造函数中指定参数名和 SQL 类型。通过使用 java.sql.Types 常量指定 SQL 类型。在本章前面，我们看到了类似于以下内容的声明：

To define a parameter for the SimpleJdbc classes and also for the RDBMS operations classes (covered in Modeling JDBC Operations as Java Objects) you can use SqlParameter or one of its subclasses. To do so, you typically specify the parameter name and SQL type in the constructor. The SQL type is specified by using the java.sql.Types constants. Earlier in this chapter, we saw declarations similar to the following:

  • Java

  • Kotlin

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

带有`SqlParameter`的第一行声明了一个IN参数。你可以将IN参数用于存储过程调用和查询，方法是使用`SqlQuery`及其子类（xref:data-access/jdbc/object.adoc#jdbc-SqlQuery[Understanding `SqlQuery`中已介绍）。

The first line with the SqlParameter declares an IN parameter. You can use IN parameters for both stored procedure calls and for queries by using the SqlQuery and its subclasses (covered in Understanding SqlQuery).

第二行（带有 SqlOutParameter）声明在存储过程调用中要使用的 out 参数。对于 InOut 参数（向过程提供 IN 值并也返回值的的参数），还有一个 SqlInOutParameter。

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

仅将声明为 SqlParameter 和 SqlInOutParameter 的参数用于提供输入值。这与 StoredProcedure 类不同，后者（出于向后兼容性的原因）允许为声明为 SqlOutParameter 的参数提供输入值。

Only parameters declared as SqlParameter and SqlInOutParameter are used to provide input values. This is different from the StoredProcedure class, which (for backwards compatibility reasons) lets input values be provided for parameters declared as SqlOutParameter.

对于 IN 参数，除了名称和 SQL 类型外，你可以为 numeric 数据指定标度或为自定义数据库类型指定类型名称。对于 out 参数，你可以提供一个 RowMapper 来处理从 REF 光标返回的行映射。另一种选择是指定一个 SqlReturnType，它提供了定义自定义处理返回值的机会。

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.

Calling a Stored Function by Using SimpleJdbcCall

你可以用几乎和你调用存储过程一样的方式调用存储函数，但你要提供一个函数名称而不是过程名称。你使用 withFunctionName 方法作为配置的一部分来表示你要调用某个函数，并生成用于函数调用的相应字符串。一个专门的调用（executeFunction）用于运行函数，它将函数返回值作为指定类型的对象进行返回，这意味着你无需从结果映射中检索返回值。对于只有一个 out 参数的存储过程，也有一个类似的方便方法（名为 executeObject）。以下示例（适用于 MySQL）基于名为 get_actor_name 的存储函数，该函数返回演员的姓名：

You can call a stored function in almost the same way as you call a stored procedure, except that you provide a function name rather than a procedure name. You use the withFunctionName method as part of the configuration to indicate that you want to make a call to a function, and the corresponding string for a function call is generated. A specialized call (executeFunction) is used to run the function, and it returns the function return value as an object of a specified type, which means you do not have to retrieve the return value from the results map. A similar convenience method (named executeObject) is also available for stored procedures that have only one out parameter. The following example (for MySQL) is based on a stored function named get_actor_name that returns an actor’s full name:

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;

要调用这个函数，我们再次在初始化方法中创建一个 SimpleJdbcCall，如下例所示：

To call this function, we again create a SimpleJdbcCall in the initialization method, as the following example shows:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcCall funcGetActorName;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

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

	// ... additional methods
}

使用的 executeFunction 方法返回一个包含来自函数调用的返回值的 String。

The executeFunction method used returns a String that contains the return value from the function call.

Returning a ResultSet or REF Cursor from a SimpleJdbcCall

调用返回结果集的存储过程或函数有点棘手。一些数据库在 JDBC 结果处理过程中返回结果集，而另一些则需要明确注册特定类型的 out 参数。两种方法都需要额外的处理来循环遍历结果集并处理返回的行。使用 SimpleJdbcCall，你可以使用 returningResultSet 方法并声明一个 RowMapper 实现，用于特定参数。如果结果集在结果处理期间返回，则没有定义名称，因此返回的结果必须与你声明 RowMapper 实现的顺序相匹配。指定的名称仍然用于将处理过的结果列表存储在从 execute 语句返回的结果映射中。

Calling a stored procedure or function that returns a result set is a bit tricky. Some databases return result sets during the JDBC results processing, while others require an explicitly registered out parameter of a specific type. Both approaches need additional processing to loop over the result set and process the returned rows. With the SimpleJdbcCall, you can use the returningResultSet method and declare a RowMapper implementation to be used for a specific parameter. If the result set is returned during the results processing, there are no names defined, so the returned results must match the order in which you declare the RowMapper implementations. The name specified is still used to store the processed list of results in the results map that is returned from the execute statement.

下一个示例（适用于 MySQL）使用一个没有任何 IN 参数且从 t_actor 表返回所有行的存储过程：

The next example (for MySQL) uses a stored procedure that takes no IN parameters and returns all rows from the t_actor table:

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

要调用此过程，你可以声明 RowMapper。由于你要映射到的类遵循 JavaBean 规则，因此你可以使用一个 BeanPropertyRowMapper，该映射器是通过在 newInstance 方法中传入要映射到的必需类创建的。以下示例演示如何执行此操作：

To call this procedure, you can declare the RowMapper. Because the class to which you want to map follows the JavaBean rules, you can use a BeanPropertyRowMapper that is created by passing in the required class to map to in the newInstance method. The following example shows how to do so:

  • Java

  • Kotlin

public class JdbcActorDao implements ActorDao {

	private SimpleJdbcCall procReadAllActors;

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

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

	// ... additional methods
}
class JdbcActorDao(dataSource: DataSource) : ActorDao {

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

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

	// ... additional methods
}

execute 调用传递了一个空 Map，因为此调用不接受任何参数。然后从结果映射中检索演员列表并返回给调用方。

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.