Spring Docs

主题

参数和数据值处理的常见问题 (Common Problems with Parameter and Data Value Handling) ​

在 Spring Framework 的 JDBC 支持所提供的不同方法中，参数和数据值的处理存在一些常见问题。本节介绍如何解决这些问题。

为参数提供 SQL 类型信息 ​

通常，Spring 会根据传入参数的 Java 类型来确定其 SQL 类型。但在某些情况下，显式地提供要使用的 SQL 类型是有必要的（例如为了正确地设置 NULL 值）。

你可以通过以下几种方式提供 SQL 类型信息：

  • JdbcTemplate 方法：许多更新和查询方法接受一个额外的 int 数组参数。该数组使用 java.sql.Types 中的常量来指定对应位置参数的 SQL 类型。
  • SqlParameterValue 类：你可以使用该类包装参数值。在构造函数中传入 SQL 类型和实际值。对于数值类型，还可以提供可选的精度（scale）参数。
  • SqlParameterSource 实现：对于使用命名参数的方法，可以使用 BeanPropertySqlParameterSource 或 MapSqlParameterSource。它们都有注册特定命名参数 SQL 类型的方法。

处理 BLOB 和 CLOB 对象 ​

数据库可以存储图像、其他二进制数据和大文本块。这些大型对象对于二进制数据称为 BLOB（Binary Large OBject），对于字符数据称为 CLOB（Character Large OBject）。

Spring 使用 LobHandler 接口和 LobCreator 类来抽象 LOB 数据的管理。

插入 LOB 数据 ​

以下示例展示了如何使用 JdbcTemplate 和 AbstractLobCreatingPreparedStatementCallback 插入 BLOB 和 CLOB。

java
final File blobIn = new File("spring2004.jpg");
final InputStream blobIs = new FileInputStream(blobIn);
final File clobIn = new File("large.txt");
final InputStream clobIs = new FileInputStream(clobIn);
final InputStreamReader clobReader = new InputStreamReader(clobIs);

jdbcTemplate.execute(
	"INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
	new AbstractLobCreatingPreparedStatementCallback(lobHandler) { // (1)
		protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
			ps.setLong(1, 1L);
			lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int)clobIn.length()); // (2)
			lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int)blobIn.length()); // (3)
		}
	}
);

blobIs.close();
clobReader.close();
kotlin
val blobIn = File("spring2004.jpg")
val blobIs = FileInputStream(blobIn)
val clobIn = File("large.txt")
val clobIs = FileInputStream(clobIn)
val clobReader = InputStreamReader(clobIs)

jdbcTemplate.execute(
		"INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
		object: AbstractLobCreatingPreparedStatementCallback(lobHandler) { // (1)
			override fun setValues(ps: PreparedStatement, lobCreator: LobCreator) {
				ps.setLong(1, 1L)
				lobCreator.setClobAsCharacterStream(ps, 2, clobReader, clobIn.length().toInt()) // (2)
				lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, blobIn.length().toInt()) // (3)
			}
		}
)
blobIs.close()
clobReader.close()
  1. 传入 lobHandler（通常是 DefaultLobHandler 的实例）。
  2. 使用 setClobAsCharacterStream 传递 CLOB 内容。
  3. 使用 setBlobAsBinaryStream 传递 BLOB 内容。

读取 LOB 数据 ​

读取 LOB 时，同样使用 LobHandler 提供的便捷方法：

java
List<Map<String, Object>> l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table",
	new RowMapper<Map<String, Object>>() {
		public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException {
			Map<String, Object> results = new HashMap<String, Object>();
			String clobText = lobHandler.getClobAsString(rs, "a_clob"); // (1)
			results.put("CLOB", clobText);
			byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob"); // (2)
			results.put("BLOB", blobBytes);
			return results;
		}
	});
kotlin
val l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table") { rs, _ ->
	val clobText = lobHandler.getClobAsString(rs, "a_clob") // (1)
	val blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob") // (2)
	mapOf("CLOB" to clobText, "BLOB" to blobBytes)
}

为 IN 子句传入值列表 ​

SQL 标准允许根据包含变量列表的表达式来选择行，例如 where id in (1, 2, 3)。JDBC 标准并不直接支持预编译语句中的这种变量列表（你不能声明可变数量的占位符）。

NamedParameterJdbcTemplate 解决了这个问题。你可以将 java.util.List（或任何 Iterable）作为参数传递。Spring 会根据列表的大小动态生成 SQL 字符串（插入正确数量的占位符）。

注意

在传入大量值时要小心。许多数据库对 IN 列表中的值数量有硬性限制（例如 Oracle 的限制是 1000）。

处理存储过程调用的复杂类型 ​

在调用存储过程时，有时需要使用特定于数据库的复杂类型。Spring 提供了 SqlReturnType 处理返回的复杂类型，以及 SqlTypeValue 处理传入的参数类型。

这些接口允许你直接访问底层的 JDBC Connection 或 CallableStatement，从而能够创建或读取特定于数据库的对象，如 java.sql.Struct 或 java.sql.Array。

补充教学 ​

1. NULL 值的烦恼：为什么需要 SQL 类型？ ​

如果你在 JdbcTemplate 中使用普通的 Map 或参数数组，当某个值为 null 时，JDBC 驱动程序可能无法确定该参数的类型，从而抛出异常（例如：Invalid column type）。 通过使用 SqlParameterValue 或在 MapSqlParameterSource 中显式注册类型：

java
parameters.addValue("optional_info", null, Types.VARCHAR);

这能确保驱动程序明确知道即使是 NULL，它也应该被视为 VARCHAR 类型。

2. LOB 处理的最佳实践 ​

  • 内存 vs 流：getBlobAsBytes 和 getClobAsString 会将整个大型对象加载到 JVM 内存中。如果文件非常大（GB 级），这会导致 OutOfMemoryError。在这种情况下，请优先使用流式处理（getBlobAsBinaryStream）。
  • 事务性：许多数据库要求在事务开启的情况下处理 LOB。如果你的 LOB 访问在事务外，可能会遇到流已关闭的错误。

3. 如何突破 IN 子句 1000 个值的限制？ ​

当你的列表超过 1000 时，与其尝试让 IN 子句工作，不如考虑以下方案：

  1. 分批处理：将列表分成多个 500 个一组的小列表，执行多次查询并合并结果。
  2. 临时表：将这一组 ID 先插入一张临时表中，然后使用 JOIN 或 EXISTS 来进行查询。这在处理数十万个 ID 时效率最高。
  3. 内联视图：通过 UNION ALL 构造一个内联表，但这在性能上并不总是最优。

Based on Spring Framework.

Copyright © 2024 Spring 中文社区