Modeling JDBC Operations as Java Objects

org.springframework.jdbc.object 包含允许你以更面向对象的方式访问数据库的类。作为示例，你可以运行查询并将结果作为列表获取，该列表包含关系列数据映射到业务对象属性的业务对象。你还可以运行存储过程并运行更新、删除和插入语句。

The org.springframework.jdbc.object package contains classes that let you access the database in a more object-oriented manner. As an example, you can run queries and get the results back as a list that contains business objects with the relational column data mapped to the properties of the business object. You can also run stored procedures and run update, delete, and insert statements.

许多Spring开发者认为，下面描述的各种RDBMS操作类（xref:data-access/jdbc/object.adoc#jdbc-StoredProcedure[`StoredProcedure`类除外）通常可以用简单的`JdbcTemplate`调用来替代。通常，编写一个直接调用`JdbcTemplate`中的方法的DAO方法会更简单（而不是将查询封装成一个成熟的类）。

Many Spring developers believe that the various RDBMS operation classes described below (with the exception of the StoredProcedure class) can often be replaced with straight JdbcTemplate calls. Often, it is simpler to write a DAO method that calls a method on a JdbcTemplate directly (as opposed to encapsulating a query as a full-blown class).

但是，如果你通过使用 RDBMS 操作类获得可衡量价值，你应继续使用这些类。

However, if you are getting measurable value from using the RDBMS operation classes, you should continue to use these classes.

Understanding `SqlQuery`

SqlQuery 是一个可重用、线程安全的类，可封装 SQL 查询。子类必须实现 newRowMapper(..) 方法以提供一个 RowMapper 实例，该实例可以在查询执行期间通过迭代创建的 ResultSet 所获取的每行创建一个对象。SqlQuery 类很少直接使用，因为 MappingSqlQuery 子类为映射行到 Java 类提供了更方便的实现。扩展 SqlQuery 的其他实现是 MappingSqlQueryWithParameters 和 UpdatableSqlQuery。

SqlQuery is a reusable, thread-safe class that encapsulates an SQL query. Subclasses must implement the newRowMapper(..) method to provide a RowMapper instance that can create one object per row obtained from iterating over the ResultSet that is created during the execution of the query. The SqlQuery class is rarely used directly, because the MappingSqlQuery subclass provides a much more convenient implementation for mapping rows to Java classes. Other implementations that extend SqlQuery are MappingSqlQueryWithParameters and UpdatableSqlQuery.

Using `MappingSqlQuery`

MappingSqlQuery 是一个可重用的查询，其具体子类必须实现抽象 mapRow(..) 方法，以将所提供 ResultSet 的每一行转换为指定类型的对象。以下示例展示了一个自定义查询，该查询将 t_actor 关系的数据映射到 Actor 类的实例：

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:

Java
Kotlin

public class ActorMappingQuery extends MappingSqlQuery<Actor> {

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

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

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

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

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

该类扩展了以 Actor 类型参数化的 MappingSqlQuery。此自定义查询的构造函数以 DataSource 作为一个唯一参数。在此构造函数中，你可以使用 DataSource 和在此查询中检索行要运行的 SQL，调用超类上的构造函数。此 SQL 用于创建一个 PreparedStatement，因此，它可包含在执行期间传递任何参数的占位符。你必须使用 declareParameter 方法传递 SqlParameter 来声明每个参数。SqlParameter 占有一个名称和 java.sql.Types 中定义的 JDBC 类型。在定义所有参数之后，你可以调用 compile() 方法，以便可以准备并稍后运行该语句。此类在编译之后是线程安全的，因此，只要在 DAO 初始化时创建这些实例，就可以将它们保留为实例变量并重复使用。以下示例展示如何定义此类：

The class extends MappingSqlQuery parameterized with the Actor type. The constructor for this customer query takes a DataSource as the only parameter. In this constructor, you can call the constructor on the superclass with the DataSource and the SQL that should be run to retrieve the rows for this query. This SQL is used to create a PreparedStatement, so it may contain placeholders for any parameters to be passed in during execution. You must declare each parameter by using the declareParameter method passing in an SqlParameter. The SqlParameter takes a name, and the JDBC type as defined in java.sql.Types. After you define all parameters, you can call the compile() method so that the statement can be prepared and later run. This class is thread-safe after it is compiled, so, as long as these instances are created when the DAO is initialized, they can be kept as instance variables and be reused. The following example shows how to define such a class:

Java
Kotlin

private ActorMappingQuery actorMappingQuery;

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

public Actor getActor(Long id) {
	return actorMappingQuery.findObject(id);
}

private val actorMappingQuery = ActorMappingQuery(dataSource)

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

前一个示例中的方法检索具有作为唯一参数传递的 id 的演员。由于我们只希望返回一个对象，因此，我们将 id 作为参数调用 findObject 方便方法。如果我们有一个返回对象列表并采用其他参数的查询，我们将使用一个数组形式传递的参数值 execute 方法。以下示例展示此类方法：

The method in the preceding example retrieves the actor with the id that is passed in as the only parameter. Since we want only one object to be returned, we call the findObject convenience method with the id as the parameter. If we had instead a query that returned a list of objects and took additional parameters, we would use one of the execute methods that takes an array of parameter values passed in as varargs. The following example shows such a method:

Java
Kotlin

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

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

Using `SqlUpdate`

SqlUpdate 类封装一个 SQL 更新。与查询一样，一个更新对象是可重用的，并且，与所有 RdbmsOperation 类一样，一个更新可以具有参数且在 SQL 中定义。此类提供了许多与查询对象的 execute(..) 方法类似的 update(..) 方法。SqlUpdate 类是具体的。它可以被子类化，例如，用于添加自定义更新方法。但是，你不需要子类化 SqlUpdate 类，因为可以通过设置 SQL 和声明参数轻松对其进行参数化。以下示例创建名为 execute 的自定义更新方法：

The SqlUpdate class encapsulates an SQL update. As with a query, an update object is reusable, and, as with all RdbmsOperation classes, an update can have parameters and is defined in SQL. This class provides a number of update(..) methods analogous to the execute(..) methods of query objects. The SqlUpdate class is concrete. It can be subclassed — for example, to add a custom update method. However, you do not have to subclass the SqlUpdate class, since it can easily be parameterized by setting SQL and declaring parameters. The following example creates a custom update method named execute:

Java
Kotlin

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

public class UpdateCreditRating extends SqlUpdate {

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

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

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

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

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

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

Using `StoredProcedure`

StoredProcedure 类是 RDBMS 存储过程对象抽象的一个 abstract 超类。

The StoredProcedure class is an abstract superclass for object abstractions of RDBMS stored procedures.

继承的 sql 属性是 RDBMS 中存储过程的名称。

The inherited sql property is the name of the stored procedure in the RDBMS.

要为 StoredProcedure 类定义一个参数，你可以使用一个 SqlParameter 或其子类之一。你必须在构造函数中指定参数名称和 SQL 类型，如下面的代码片段所示：

To define a parameter for the StoredProcedure class, you can use an SqlParameter or one of its subclasses. You must specify the parameter name and SQL type in the constructor, as the following code snippet shows:

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

SQL 类型使用 java.sql.Types 常量进行指定。

The SQL type is specified using the java.sql.Types constants.

第一行（带`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 both for stored procedure calls and for queries using the SqlQuery and its subclasses (covered in Understanding SqlQuery).

第二行（带 SqlOutParameter）声明一个用于存储过程调用的 out 参数。对于 InOut 参数（为过程提供一个 in 值并且还返回一个值的 parameters），还有一个 SqlInOutParameter。

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

对于 in 参数，除了名称和 SQL 类型之外，你还可以为数字数据指定一个范围或为自定义数据库类型指定一个类型名称。对于 out 参数，你可以提供一个 RowMapper 来处理从 REF 游标返回的行。另一种选择是指定一个 SqlReturnType，该 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 lets you define customized handling of the return values.

下面一个简单 DAO 的示例使用 StoredProcedure 调用一个函数（sysdate()），该函数随任何 Oracle 数据库提供。要使用存储过程功能，你必须创建一个扩展 StoredProcedure 的类。在此示例中，StoredProcedure 类是一个内部类。但是，如果你需要重复使用 StoredProcedure，你可以将其声明为一个顶级类。此示例没有输入参数，但输出参数使用 SqlOutParameter 类被声明为日期类型。execute() 方法运行该过程并从结果 Map 中提取返回的日期。结果 Map 使用参数名称作为键，为每个声明的输出参数（在本例中，只有一个）具有一个条目。以下清单展示了我们自定义的 StoredProcedure 类：

The next example of a simple DAO uses a StoredProcedure to call a function (sysdate()), which comes with any Oracle database. To use the stored procedure functionality, you have to create a class that extends StoredProcedure. In this example, the StoredProcedure class is an inner class. However, if you need to reuse the StoredProcedure, you can declare it as a top-level class. This example has no input parameters, but an output parameter is declared as a date type by using the SqlOutParameter class. The execute() method runs the procedure and extracts the returned date from the results Map. The results Map has an entry for each declared output parameter (in this case, only one) by using the parameter name as the key. The following listing shows our custom StoredProcedure class:

Java
Kotlin

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

public class StoredProcedureDao {

	private GetSysdateProcedure getSysdate;

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

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

	private class GetSysdateProcedure extends StoredProcedure {

		private static final String SQL = "sysdate";

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

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

}

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

class StoredProcedureDao(dataSource: DataSource) {

	private val SQL = "sysdate"

	private val getSysdate = GetSysdateProcedure(dataSource)

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

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

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

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

以下 StoredProcedure 的示例有两个输出参数（在这种情况下，Oracle REF 游标）：

The following example of a StoredProcedure has two output parameters (in this case, Oracle REF cursors):

Java
Kotlin

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

public class TitlesAndGenresStoredProcedure extends StoredProcedure {

	private static final String SPROC_NAME = "AllTitlesAndGenres";

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

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

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

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

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

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

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

注意在 TitlesAndGenresStoredProcedure 构造函数中使用的 declareParameter(..) 方法的重载变体是如何传递 RowMapper 实现实例的。这是重用现有功能的一种非常方便而强大的方法。接下来的两个示例提供了两个 RowMapper 实现的代码。

Notice how the overloaded variants of the declareParameter(..) method that have been used in the TitlesAndGenresStoredProcedure constructor are passed RowMapper implementation instances. This is a very convenient and powerful way to reuse existing functionality. The next two examples provide code for the two RowMapper implementations.

TitleMapper 类将 ResultSet 映射到 ResultSet 中每一行的 Title 域对象，如下所示：

The TitleMapper class maps a ResultSet to a Title domain object for each row in the supplied ResultSet, as follows:

Java
Kotlin

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

public final class TitleMapper implements RowMapper<Title> {

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

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

class TitleMapper : RowMapper<Title> {

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

GenreMapper 类将 ResultSet 映射到 ResultSet 中每一行的 Genre 域对象，如下所示：

The GenreMapper class maps a ResultSet to a Genre domain object for each row in the supplied ResultSet, as follows:

Java
Kotlin

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

public final class GenreMapper implements RowMapper<Genre> {

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

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

class GenreMapper : RowMapper<Genre> {

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

要在 RDBMS 中其定义中具有一个或多个输入参数的存储过程中传递参数，可以编码一个强类型化 execute(..) 方法，该方法将委派给超类中的非类型化 execute(Map) 方法，如下面的示例所示：

To pass parameters to a stored procedure that has one or more input parameters in its definition in the RDBMS, you can code a strongly typed execute(..) method that would delegate to the untyped execute(Map) method in the superclass, as the following example shows:

Java
Kotlin

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

public class TitlesAfterDateStoredProcedure extends StoredProcedure {

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

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

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

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

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

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

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

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

Modeling JDBC Operations as Java Objects

Understanding SqlQuery

Using MappingSqlQuery

Using SqlUpdate

Using StoredProcedure

Understanding `SqlQuery`

Using `MappingSqlQuery`

Using `SqlUpdate`

Using `StoredProcedure`