使用 SimpleJdbc 類別簡化 JDBC 操作

SimpleJdbcInsert 和 SimpleJdbcCall 類別透過利用可透過 JDBC 驅動程式檢索的資料庫中繼資料，提供簡化的組態。這表示您需要預先組態的內容較少，但如果您偏好在程式碼中提供所有詳細資訊，則可以覆寫或關閉中繼資料處理。

使用 SimpleJdbcInsert 插入資料

我們先從最少組態選項的 SimpleJdbcInsert 類別開始。您應該在資料存取層的初始化方法中實例化 SimpleJdbcInsert。在本範例中，初始化方法是 setDataSource 方法。您不需要子類別化 SimpleJdbcInsert 類別。相反地，您可以建立新的實例，並使用 withTableName 方法設定資料表名稱。此類別的組態方法遵循 fluid 樣式，該樣式會傳回 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，該 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 參數。您可以將 IN 參數用於預存程序呼叫和查詢，方法是使用 SqlQuery 及其子類別（在了解 SqlQuery中涵蓋）。

第二行（具有 SqlOutParameter）宣告要在預存程序呼叫中使用的 out 參數。還有一個 SqlInOutParameter 用於 InOut 參數（提供程序的 IN 值並傳回值的參數）。

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

對於 IN 參數，除了名稱和 SQL 類型之外，您還可以為數字資料指定小數位數，或為自訂資料庫類型指定類型名稱。對於 out 參數，您可以提供 RowMapper 來處理從 REF 游標傳回的資料列的對應。另一個選項是指定 SqlReturnType，它提供了定義自訂處理傳回值的機會。

使用 SimpleJdbcCall 呼叫預存函數

您可以以幾乎與呼叫預存程序相同的方式呼叫預存函數，不同之處在於您提供函數名稱而不是程序名稱。您可以使用 withFunctionName 方法作為組態的一部分，以指示您想要呼叫函數，並產生函數呼叫的對應字串。專用呼叫 (executeFunction) 用於執行函數，它會將函數傳回值作為指定類型的物件傳回，這表示您不必從結果 Map 中檢索傳回值。類似的便利方法（名為 executeObject）也適用於只有一個 out 參數的預存程序。以下範例（針對 MySQL）是基於名為 get_actor_name 的預存函數，該函數傳回 actor 的完整名稱

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 陳述式傳回的結果 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 規則，因此您可以使用 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，因為此呼叫不採用任何參數。然後從結果 Map 中檢索 actor 清單並傳回給呼叫者。