使用 SimpleJdbc 類簡化 JDBC 操作

SimpleJdbcInsert 和 SimpleJdbcCall 類利用可透過 JDBC 驅動程式檢索的資料庫元資料提供簡化的配置。這意味著您需要預先配置的內容較少，儘管如果您更喜歡在程式碼中提供所有詳細資訊，可以覆蓋或關閉元資料處理。

使用 SimpleJdbcInsert 插入資料

我們首先從最少配置選項開始瞭解 SimpleJdbcInsert 類。您應該在資料訪問層的初始化方法中例項化 SimpleJdbcInsert。對於此示例，初始化方法是 setDataSource 方法。您不需要對 SimpleJdbcInsert 類進行子類化。相反，您可以建立一個新例項並使用 withTableName 方法設定表名。此類的配置方法遵循返回 SimpleJdbcInsert 例項的 fluid 風格，這允許您連結所有配置方法。以下示例僅使用一個配置方法（稍後我們將展示多個方法的示例）

  • 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 的鍵必須與資料庫中定義的表列名匹配。這是因為我們讀取元資料來構建實際的 insert 語句。

使用 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 格式的標量值。該示例過程讀取指定的演員條目，並以 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 引數包含您正在查詢的演員的 id。out 引數返回從表中讀取的資料。

您可以以類似於宣告 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 引數並返回一個 Map，該 Map 包含以儲存過程中指定的名稱為鍵的任何 out 引數。在本例中，它們是 out_first_name、out_last_name 和 out_birth_date。

execute 方法的最後一部分建立了一個 Actor 例項，用於返回檢索到的資料。同樣重要的是使用儲存過程中宣告的 out 引數名稱。此外，結果 Map 中儲存的 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 引數。還有一個 SqlInOutParameter 用於 InOut 引數（向過程提供 IN 值並且也返回值的引數）。

只有宣告為 SqlParameter 和 SqlInOutParameter 的引數才用於提供輸入值。這與 StoredProcedure 類不同，後者（出於向後相容的原因）允許為宣告為 SqlOutParameter 的引數提供輸入值。

對於 IN 引數，除了名稱和 SQL 型別之外，您還可以指定數字資料的 scale 或自定義資料庫型別的型別名稱。對於 out 引數，您可以提供一個 RowMapper 來處理從 REF cursor 返回的行的對映。另一種選擇是指定一個 SqlReturnType，它提供了一個定義返回值的自定義處理的機會。

使用 SimpleJdbcCall 呼叫儲存函式

呼叫儲存函式的方式與呼叫儲存過程的方式幾乎相同，只是您提供的是函式名稱而不是過程名稱。您使用 withFunctionName 方法作為配置的一部分，以表明您要呼叫一個函式，並生成相應的函式呼叫字串。一個專門的呼叫（executeFunction）用於執行函式，並以指定型別的物件形式返回函式返回值，這意味著您不必從結果 Map 中檢索返回值。對於只有一個 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。

從 SimpleJdbcCall 返回 ResultSet 或 REF Cursor

呼叫返回結果集的儲存過程或函式有點棘手。有些資料庫在 JDBC 結果處理期間返回結果集，而另一些資料庫則需要顯式註冊特定型別的 out 引數。這兩種方法都需要額外的處理來迴圈遍歷結果集並處理返回的行。使用 SimpleJdbcCall，您可以使用 returningResultSet 方法並宣告一個 RowMapper 實現用於特定的引數。如果在結果處理期間返回結果集，則沒有定義名稱，因此返回的結果必須與您宣告 RowMapper 實現的順序匹配。指定的名稱仍用於將處理過的結果列表儲存在從 execute 語句返回的結果 Map 中。

下一個示例（針對 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 規則，因此您可以使用透過在 newInstance 方法中傳入所需的類來建立的 BeanPropertyRowMapper。以下示例展示瞭如何進行操作

  • 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，因為此呼叫不接受任何引數。然後從結果 Map 中檢索演員列表並返回給呼叫者。