使用SimpleJdbc类简化 JDBC 操作

SimpleJdbcInsert和SimpleJdbcCall类通过利用可通过JDBC驱动程序检索的数据库元数据，提供了简化的配置。这意味着您需要预先配置的内容更少，尽管如果您更愿意在代码中提供所有细节，可以覆盖或关闭元数据处理。

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

使用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
}

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

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

插入的执行与您依赖元数据确定要使用哪些列的情况相同。

使用SqlParameterSource提供参数值

使用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
}

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

使用SimpleJdbcCall调用存储过程

SimpleJdbcCall类使用数据库中的元数据来查找in和out参数的名称，因此您不必显式声明它们。如果您愿意这样做，或者如果您有不能自动映射到Java类的参数，则可以声明参数。第一个示例显示了一个简单的过程，它只从MySQL数据库返回VARCHAR和DATE格式的标量值。示例过程读取指定的actor条目并以out参数的形式返回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;

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

您可以声明SimpleJdbcCall的方式类似于声明SimpleJdbcInsert。您应该在数据访问层的初始化方法中实例化和配置该类。与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 参数并返回一个Map，其中包含任何out 参数，这些参数以存储过程中指定的名称为键。在本例中，它们是out_first_name、out_last_name和out_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参数名称的大小写冲突。

显式声明用于SimpleJdbcCall的参数

在本章前面，我们描述了如何从元数据推断参数，但如果需要，您可以显式声明它们。您可以通过使用declareParameters方法创建和配置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
}

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

如何定义SqlParameters

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

  • 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 参数。您可以使用SqlQuery及其子类（在理解SqlQuery中介绍）通过使用 IN 参数来进行存储过程调用和查询。

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

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

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

使用SimpleJdbcCall调用存储函数

您可以几乎以与调用存储过程相同的方式调用存储函数，只是您提供函数名称而不是过程名称。您使用withFunctionName方法作为配置的一部分来指示您要进行函数调用，并且会生成相应的函数调用字符串。一个专门的调用（executeFunction）用于运行函数，它将函数返回值作为指定类型的对象返回，这意味着您不必从结果映射中检索返回值。一个类似的便捷方法（名为executeObject）也适用于只有一个out参数的存储过程。以下示例（针对 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。

从SimpleJdbcCall返回ResultSet或 REF 游标

调用返回结果集的存储过程或函数有点棘手。一些数据库在 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，因为此调用不接受任何参数。然后从结果映射中检索演员列表并返回给调用者。