Simplifying JDBC Operations with the SimpleJdbc Classes

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

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

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

Inserting Data by Using SimpleJdbcInsert

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

  • 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 的键必须与表(在数据库中定义)的列名匹配。这是因为我们读取元数据来构建实际的插入语句。

Retrieving Auto-generated Keys by Using SimpleJdbcInsert

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

  • 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

Specifying Columns for a SimpleJdbcInsert

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

  • 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
}

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

Using SqlParameterSource to Provide Parameter Values

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

  • 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
}

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

Calling a Stored Procedure with SimpleJdbcCall

SimpleJdbcCall 类使用数据库中的元数据来查找 inout 参数的名称,以便您不必显式地声明它们。如果您愿意,或者您有无法自动映射到 Java 类的参数(如 ARRAYSTRUCT),则可以声明参数。第一个示例显示了一个简单的过程,它仅从 MySQL 数据库中以 VARCHARDATE 格式返回标量值。示例过程读取指定的 actor 条目,并以 out 参数的形式返回 first_namelast_namebirth_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;

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

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

  • 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。您必须将提供给输入值的名称与存储过程中声明的参数名称相匹配。大小写不必匹配,因为您使用元数据来确定如何在存储过程中引用数据库对象。存储过程源中指定的并不是存储在数据库中的方式。一些数据库将名称转换为所有大写,而另一些数据库使用小写或使用指定的大小写。

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

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

  • 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 参数名称使用的大小写出现冲突。

Explicitly Declaring Parameters to Use for a SimpleJdbcCall

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

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

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

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

  • 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
}

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

How to Define SqlParameters

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

  • 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`中已介绍)。

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

仅将声明为 SqlParameterSqlInOutParameter 的参数用于提供输入值。这与 StoredProcedure 类不同,后者(出于向后兼容性的原因)允许为声明为 SqlOutParameter 的参数提供输入值。

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

Calling a Stored Function by Using SimpleJdbcCall

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

  • 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

Returning a ResultSet or REF Cursor from a SimpleJdbcCall

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

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

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 方法中传入要映射到的必需类创建的。以下示例演示如何执行此操作:

  • 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,因为此调用不接受任何参数。然后从结果映射中检索演员列表并返回给调用方。