对于最新的稳定版本，请使用 Spring Framework 6.2.4！spring-doc.cadn.net.cn

将 JDBC作建模为 Java 对象

这org.springframework.jdbc.objectpackage 包含允许您访问数据库。例如，您可以运行查询并将结果作为列表返回，其中包含具有关系映射到业务对象属性的列数据。您也可以运行 stored 过程并运行 Update、Delete 和 Insert 语句。spring-doc.cadn.net.cn

许多 Spring 开发人员认为，下面描述的各种 RDBMS作类（除了StoredProcedure类）可以经常替换为 StraightJdbcTemplate调用。通常，编写 DAO 更简单方法，该方法在JdbcTemplate直接（而不是将查询封装为完整的类）。spring-doc.cadn.net.cn

但是，如果您通过使用 RDBMS作类获得可衡量的值，您应该继续使用这些类。spring-doc.cadn.net.cn

理解`SqlQuery`

SqlQuery是封装 SQL 查询的可重用、线程安全的类。子必须实现newRowMapper(..)方法提供RowMapper实例，该实例可以通过迭代ResultSet即创建在执行查询期间。这SqlQuery类很少直接使用，因为这MappingSqlQuerysubclass 为将行映射到 Java 类。扩展的其他实现SqlQuery是MappingSqlQueryWithParameters和UpdatableSqlQuery.spring-doc.cadn.net.cn

用`MappingSqlQuery`

MappingSqlQuery是一个可重用的查询，其中具体子类必须实现抽象mapRow(..)方法将提供的每一行ResultSet转换为 object 指定类型。以下示例显示了一个自定义查询，该查询将数据来自t_actor与Actor类：spring-doc.cadn.net.cn

Javaspring-doc.cadn.net.cn
Kotlinspring-doc.cadn.net.cn

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

该类扩展了MappingSqlQuery参数化为Actor类型。构造函数对于此 Customer 查询，需要一个DataSource作为唯一的参数。在这个构造函数，您可以使用DataSource和 SQL 应该运行该 API 以检索此查询的行。此 SQL 用于创建一个PreparedStatement，因此它可以包含任何参数的占位符在执行期间传入。您必须使用declareParameter方法传入SqlParameter.这SqlParameter采用 name，JDBC 类型如java.sql.Types.定义所有参数后，您可以调用compile()方法，以便可以准备语句并在以后运行。这个类是线程安全的，因此，只要这些实例是在 DAO 初始化后，它们可以保留为实例变量并可重用。以下内容 example 演示如何定义此类：spring-doc.cadn.net.cn

Javaspring-doc.cadn.net.cn
Kotlinspring-doc.cadn.net.cn

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作为 only 参数。由于我们只想返回一个对象，因此我们将findObject方便方法与id作为参数。如果我们有一个返回 list 并获取额外的参数，我们将使用execute方法，该方法接受作为 vararg 传入的参数值数组。以下内容 example 显示了这样的方法：spring-doc.cadn.net.cn

Javaspring-doc.cadn.net.cn
Kotlinspring-doc.cadn.net.cn

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

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

用`SqlUpdate`

这SqlUpdateclass 封装 SQL 更新。与查询一样，更新对象是可重复使用，并且与所有RdbmsOperation类，则 Update 可以有参数，并且是在 SQL 中定义。此类提供了许多update(..)方法类似于execute(..)查询对象的方法。这SqlUpdateclass 是具体的。可以是 subclassed — 例如，添加自定义更新方法。但是，您不必将SqlUpdate类，因为它可以通过设置 SQL 和声明参数来轻松参数化。以下示例创建一个名为execute:spring-doc.cadn.net.cn

Javaspring-doc.cadn.net.cn
Kotlinspring-doc.cadn.net.cn

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


The StoredProcedure class is an abstract superclass for object abstractions of RDBMS
stored procedures.spring-doc.cadn.net.cn


The inherited sql property is the name of the stored procedure in the RDBMS.spring-doc.cadn.net.cn


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:spring-doc.cadn.net.cn






Javaspring-doc.cadn.net.cn


Kotlinspring-doc.cadn.net.cn






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






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






The SQL type is specified using the java.sql.Types constants.spring-doc.cadn.net.cn


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).spring-doc.cadn.net.cn


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).spring-doc.cadn.net.cn


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.spring-doc.cadn.net.cn


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:spring-doc.cadn.net.cn






Javaspring-doc.cadn.net.cn


Kotlinspring-doc.cadn.net.cn






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

public class StoredProcedureDao {

	private GetSysdateProcedure getSysdate;

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

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

	private class GetSysdateProcedure extends StoredProcedure {

		private static final String SQL = "sysdate";

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

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

}







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

class StoredProcedureDao(dataSource: DataSource) {

	private val SQL = "sysdate"

	private val getSysdate = GetSysdateProcedure(dataSource)

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

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

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

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







The following example of a StoredProcedure has two output parameters (in this case,
Oracle REF cursors):spring-doc.cadn.net.cn






Javaspring-doc.cadn.net.cn


Kotlinspring-doc.cadn.net.cn






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

public class TitlesAndGenresStoredProcedure extends StoredProcedure {

	private static final String SPROC_NAME = "AllTitlesAndGenres";

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

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







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

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

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

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

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







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


The TitleMapper class maps a ResultSet to a Title domain object for each row in
the supplied ResultSet, as follows:spring-doc.cadn.net.cn






Javaspring-doc.cadn.net.cn


Kotlinspring-doc.cadn.net.cn






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

public final class TitleMapper implements RowMapper<Title> {

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







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

class TitleMapper : RowMapper<Title> {

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







The GenreMapper class maps a ResultSet to a Genre domain object for each row in
the supplied ResultSet, as follows:spring-doc.cadn.net.cn






Javaspring-doc.cadn.net.cn


Kotlinspring-doc.cadn.net.cn






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

public final class GenreMapper implements RowMapper<Genre> {

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







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

class GenreMapper : RowMapper<Genre> {

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







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






Javaspring-doc.cadn.net.cn


Kotlinspring-doc.cadn.net.cn






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

public class TitlesAfterDateStoredProcedure extends StoredProcedure {

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

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

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







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

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

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

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

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

将 JDBC作建模为 Java 对象

理解SqlQuery

用MappingSqlQuery

用SqlUpdate

Using StoredProcedure

理解`SqlQuery`

用`MappingSqlQuery`

用`SqlUpdate`

Using `StoredProcedure`