参数和数据值处理的常见问题

Hunyuan 7b 中英对照 Common Problems with Parameter and Data Value Handling

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

为参数提供SQL类型信息

通常，Spring会根据传递进来的参数类型来确定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框架中，你可以通过直接使用JdbcTemplate来处理这些大型对象，也可以利用RDBMS对象提供的更高层次的抽象以及SimpleJdbc类来进行处理。所有这些方法都使用了LobHandler接口的实现来实际管理LOB（大型对象）数据。LobHandler通过getLobCreator方法提供了对LobCreator类的访问，该类用于创建新的LOB对象以便插入数据库中。

LobCreator 和 LobHandler 为 LOB（大型对象）的输入和输出提供以下支持：

• BLOB

  • byte[]: getBlobAsBytes 和 setBlobAsBytes
  • InputStream: getBlobAsBinaryStream 和 setBlobAsBinaryStream

• CLOB

  • String: getClobAsString 和 setClobAsString
  • InputStream: getClobAsAsciiStream 和 setClobAsAsciiStream
  • Reader: getClobAsCharacterStream 和 setClobAsCharacterStream

下一个示例展示了如何创建和插入BLOB。之后我们将展示如何从数据库中读取它。

这个示例使用了JdbcTemplate和AbstractLobCreatingPreparedStatementCallback的实现类。该实现类包含一个方法setValues，该方法提供一个LobCreator，我们通过它来为SQL插入语句中的LOB列设置值。

对于这个例子，我们假设存在一个变量 lobHandler，它已经被设置为 DefaultLobHandler 的一个实例。通常你通过依赖注入来设置这个值。

以下示例展示了如何创建和插入BLOB：

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();

• 传递 lobHandler，在这个例子中它是一个普通的 DefaultLobHandler。

• 使用 setClobAsCharacterStream 方法来传递 CLOB 的内容。

• 使用 setBlobAsBinaryStream 方法来传递 BLOB 的内容。

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, clobIn.length().toInt())  3
        }
    }
)
blobIs.close()
clobReader.close()

• 传递 lobHandler，在这个例子中它是一个普通的 DefaultLobHandler。

• 使用 setClobAsCharacterStream 方法来传递 CLOB 的内容。

• 使用 setBlobAsBinaryStream 方法来传递 BLOB 的内容。

备注

如果您对从 DefaultLobHandler.getLobCreator() 返回的 LobCreator 调用 setBlobAsBinaryStream、setClobAsAsciiStream 或 setClobAsCharacterStream 方法，您可以选择性地为 contentLength 参数指定一个负值。如果指定的内容长度为负数，DefaultLobHandler 将使用 JDBC 4.0 版本中的无长度参数的 set-stream 方法。否则，它会将指定的长度传递给驱动程序。

请查看您所使用的 JDBC 驱动程序的文档，以确认该驱动程序是否支持在不提供内容长度的情况下流式传输 LOB。

现在是时候从数据库中读取LOB数据了。同样，你使用一个JdbcTemplate，该JdbcTemplate具有相同的实例变量lobHandler以及对DefaultLobHandler的引用。以下示例展示了如何进行操作：

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
		 Ergebnis.put("CLOB", clobText);
		(byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");  2
			result.put("BLOB", blobBytes);
			return results;
		}
	});

• 使用方法 getClobAsString 来获取 CLOB 的内容。

• 使用方法 getBlobAsBytes 来获取 BLOB 的内容。

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
omap("CLOB" to clobText, "BLOB" to blobBytes)
}

• 使用方法 getClobAsString 来获取 CLOB 的内容。

• 使用方法 getBlobAsBytes 来获取 BLOB 的内容。

为IN子句传递值列表

SQL标准允许根据包含变量值列表的表达式来选择行。一个典型的例子是select * from T_ACTOR where id in (1, 2, 3)。JDBC标准并不直接支持这种变量列表在预编译语句中的使用。你不能声明可变数量的占位符。你需要准备出与所需占位符数量相匹配的多种版本预编译语句，或者需要在确定所需占位符数量后动态生成SQL字符串。NamedParameterJdbcTemplate提供的命名参数支持采用了后一种方法。你可以将值作为java.util.List（或任何Iterable）的形式传递进去，该列表中的元素即为所需的占位符值，在执行语句时再将这些值插入到实际的SQL语句中。

备注

在传递多个值时请小心。JDBC标准并不保证可以在IN表达式列表中使用超过100个值。虽然某些数据库可以支持更多的值，但它们通常都有明确的最大值限制。例如，Oracle的最大限制是1000个。

除了值列表中的原始值之外，你还可以创建一个java.util.List来存储对象数组。这个列表可以支持为in子句定义多个表达式，例如select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop'))。当然，这要求你的数据库支持这种语法。

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

当你调用存储过程时，有时可以使用数据库特有的复杂类型。为了处理这些类型，Spring 提供了 SqlReturnType 用于在存储过程调用返回这些类型时进行处理，以及 SqlTypeValue 用于在将它们作为参数传递给存储过程时使用。

SqlReturnType接口有一个必须实现的方法（名为getTypeValue）。该接口用于声明SqlOutParameter的一部分。以下示例展示了如何返回用户声明的类型为ITEM_TYPE的java.sql.Struct对象的值：

Java

import java.sql.CallableStatement;
import java.sql.Struct;
import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class TestItemStoredProcedure extends StoredProcedure {

	public TestItemStoredProcedure(DataSource dataSource) {
		super(dataSource, "get_item");
		declareParameter(new SqlOutParameter("item", Types.STRUCT, "ITEM_TYPE",
				(CallableStatement cs, int colIndx, int sqlType, String typeName) -> {
					Struct struct = (Struct) cs.getObject(colIndx);
					Object[] attr = struct.getAttributes();
					TestItem item = new TestItem();
					item.setId(((Number) attr[0]).longValue());
					item.setDescription((String) attr[1]);
					item.setExpirationDate((java.util.Date) attr[2]);
					return item;
				}));
		// ...
	}

}

Kotlin

import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.`object`.StoredProcedure
import java.sql.CallableStatement
import java.sql.Struct
import java.sql.Types
import java.util.Date
import javax.sql.DataSource

class TestItemStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, "get_item") {
	init {
		declareParameter(SqlOutParameter("item",Types.STRUCT,"ITEM_TYPE") {
				cs: CallableStatement, colIndx: Int, _: Int, _: String? ->
				val struct = cs.getObject(colIndx) as Struct
				val attr = struct.attributes
				TestItem(
					(attr[0] as Number).toLong(),
					attr[1] as String,
					attr[2] as Date
				)
			})
		// ...
	}
}

您可以使用 SqlTypeValue 将 Java 对象（例如 TestItem）的值传递给存储过程。SqlTypeValue 接口有一个必须实现的方法（名为 createTypeValue）。该方法会接收活动连接作为参数，您可以使用该连接来创建特定于数据库的对象，如 java.sql.Struct 实例或 java.sql.Array 实例。以下示例创建了一个 java.sql.Struct 实例：

Java

TestItem testItem = new TestItem(123L, "A test item",
		new SimpleDateFormat("yyyy-M-d").parse("2010-12-31"));

SqlTypeValue value = new AbstractSqlTypeValue() {
	protected Object createTypeValue(Connection connection, int sqlType, String typeName) throws SQLException {
		Object[] item = new Object[] { testItem.getId(), testItem.getDescription(),
				new java.sql.Date(testItem.getExpirationDate().getTime()) };
		return connection.createStruct(typeName, item);
	}
};

Kotlin

val testItem = TestItem(123L, "A test item",
	SimpleDateFormat("yyyy-M-d").parse("2010-12-31"))

val value = object : AbstractSqlTypeValue() {
	override fun createTypeValue(connection: Connection, sqlType: Int, typeName: String?): Any {
		val item = arrayOf<Any>(testItem.id, testItem.description,
			Date(testItem.expirationDate.time))
		return connection.createStruct(typeName, item)
	}
}

现在你可以将这个 SqlTypeValue 添加到包含存储过程 execute 调用输入参数的 Map 中。

SqlTypeValue的另一个用途是将一组值传递给Oracle存储过程。Oracle在OracleConnection上提供了一个createOracleArray方法，你可以通过展开它来使用这个方法。你可以使用SqlTypeValue来创建一个数组，并用来自Java java.sql.Array的值填充该数组，如下例所示：

Java

Long[] ids = new Long[] {1L, 2L};

SqlTypeValue value = new AbstractSqlTypeValue() {
	protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
		return conn.unwrap(OracleConnection.class).createOracleArray(typeName, ids);
	}
};

Kotlin

val ids = arrayOf(1L, 2L)
val value: SqlTypeValue = object : AbstractSqlTypeValue() {
	override fun createTypeValue(conn: Connection, sqlType: Int, typeName: String?): Any {
		return conn.unwrap(OracleConnection::class.java).createOracleArray(typeName, ids)
	}
}