Common Problems with Parameter and Data Value Handling

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

Common problems with parameters and data values exist in the different approaches provided by Spring Framework’s JDBC support. This section covers how to address them.

Providing SQL Type Information for Parameters

通常，Spring 根据传入的参数类型确定参数的 SQL 类型。在设置参数值时可以显式地提供要使用的 SQL 类型。有时需要这样做才能正确设置 NULL 值。

Usually, Spring determines the SQL type of the parameters based on the type of parameter passed in. It is possible to explicitly provide the SQL type to be used when setting parameter values. This is sometimes necessary to correctly set NULL values.

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

You can provide SQL type information in several ways:

  • Many update and query methods of the JdbcTemplate take an additional parameter in the form of an int array. This array is used to indicate the SQL type of the corresponding parameter by using constant values from the java.sql.Types class. Provide one entry for each parameter.

  • You can use the SqlParameterValue class to wrap the parameter value that needs this additional information. To do so, create a new instance for each value and pass in the SQL type and the parameter value in the constructor. You can also provide an optional scale parameter for numeric values.

  • For methods that work with named parameters, you can use the SqlParameterSource classes, BeanPropertySqlParameterSource or MapSqlParameterSource. They both have methods for registering the SQL type for any of the named parameter values.

Handling BLOB and CLOB objects

你可以在数据库中存储图像、其他二进制数据和大量文本。这些大对象对于二进制数据称为 BLOB（二进制大对象），对于字符数据称为 CLOB（字符大对象）。在 Spring 中，你可以通过直接使用 JdbcTemplate，以及使用 RDBMSObjects 和 SimpleJdbc 类提供的更高级别的抽象来处理这些大对象。所有这些方法都使用 LobHandler 接口的实现来实际管理 LOB（大对象）数据。LobHandler 通过 getLobCreator 方法提供对 LobCreator 类的访问，该类用于创建要插入的新 LOB 对象。

You can store images, other binary data, and large chunks of text in the database. These large objects are called BLOBs (Binary Large OBject) for binary data and CLOBs (Character Large OBject) for character data. In Spring, you can handle these large objects by using the JdbcTemplate directly and also when using the higher abstractions provided by RDBMS Objects and the SimpleJdbc classes. All of these approaches use an implementation of the LobHandler interface for the actual management of the LOB (Large OBject) data. LobHandler provides access to a LobCreator class, through the getLobCreator method, that is used for creating new LOB objects to be inserted.

LobCreator 和 LobHandler 为 LOB 输入和输出提供以下支持：

LobCreator and LobHandler provide the following support for LOB input and output:

  • BLOB

    • byte[]: getBlobAsBytes and setBlobAsBytes

    • InputStream: getBlobAsBinaryStream and setBlobAsBinaryStream

  • CLOB

    • String: getClobAsString and setClobAsString

    • InputStream: getClobAsAsciiStream and setClobAsAsciiStream

    • Reader: getClobAsCharacterStream and setClobAsCharacterStream

下一个示例演示如何创建和插入 BLOB。稍后我们将演示如何从数据库中回读它。

The next example shows how to create and insert a BLOB. Later we show how to read it back from the database.

此示例使用 JdbcTemplate 和 AbstractLobCreatingPreparedStatementCallback 的实现。它实现了一个方法 setValues。此方法提供一个 LobCreator，我们使用它为你 SQL insert 语句中的 LOB 列设置值。

This example uses a JdbcTemplate and an implementation of the AbstractLobCreatingPreparedStatementCallback. It implements one method, setValues. This method provides a LobCreator that we use to set the values for the LOB columns in your SQL insert statement.

对于此示例，我们假设存在一个变量 lobHandler，该变量已预先设置为 DefaultLobHandler 的实例。你通常通过依赖注入来设置此值。

For this example, we assume that there is a variable, lobHandler, that is already set to an instance of a DefaultLobHandler. You typically set this value through dependency injection.

下面的示例演示如何创建和插入 BLOB：

The following example shows how to create and insert a 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();
1 Pass in the lobHandler that (in this example) is a plain DefaultLobHandler.
2 Using the method setClobAsCharacterStream to pass in the contents of the CLOB.
3 Using the method setBlobAsBinaryStream to pass in the contents of the 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, blobIn.length().toInt())  (3)
			}
		}
)
blobIs.close()
clobReader.close()
4 Pass in the lobHandler that (in this example) is a plain DefaultLobHandler.
5 Using the method setClobAsCharacterStream to pass in the contents of the CLOB.
6 Using the method setBlobAsBinaryStream to pass in the contents of the BLOB.

如果你调用 DefaultLobHandler.getLobCreator() 返回的 LobCreator 上的 setBlobAsBinaryStream、setClobAsAsciiStream 或 setClobAsCharacterStream 方法，还可以为 contentLength 参数指定一个负值。如果指定的内容长度为负值，则 DefaultLobHandler 使用没有长度参数的 set-stream 方法的 JDBC 4.0 变体。否则，它会将指定的长度传递给驱动程序。

If you invoke the setBlobAsBinaryStream, setClobAsAsciiStream, or setClobAsCharacterStream method on the LobCreator returned from DefaultLobHandler.getLobCreator(), you can optionally specify a negative value for the contentLength argument. If the specified content length is negative, the DefaultLobHandler uses the JDBC 4.0 variants of the set-stream methods without a length parameter. Otherwise, it passes the specified length on to the driver.

请参阅你使用的 JDBC 驱动程序的文档，以验证其是否支持在不提供内容长度的情况下流式传输 LOB。

See the documentation for the JDBC driver you use to verify that it supports streaming a LOB without providing the content length.

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

Now it is time to read the LOB data from the database. Again, you use a JdbcTemplate with the same instance variable lobHandler and a reference to a DefaultLobHandler. The following example shows how to do so:

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;
		}
	});
1 Using the method getClobAsString to retrieve the contents of the CLOB.
2 Using the method getBlobAsBytes to retrieve the contents of the 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)
	mapOf("CLOB" to clobText, "BLOB" to blobBytes)
}
3 Using the method getClobAsString to retrieve the contents of the CLOB.
4 Using the method getBlobAsBytes to retrieve the contents of the BLOB.

Passing in Lists of Values for IN Clause

SQL 标准允许根据包含可变值列表的表达式来选择行。一个典型的示例是 select * from T_ACTOR where id in(1, 2, 3)。这个可变列表不受 JDBC 标准的 prepared 语句直接支持。你无法声明可变数量的占位符。你需要准备好具有所需数量的占位符的许多变体，或者在知道需要多少个占位符后动态生成 SQL 字符串。NamedParameterJdbcTemplate 中提供的命名参数支持采用后一种方法。你可以将值作为简单值的 java.util.List（或任何 Iterable）传递。此列表用于将所需的占位符插入到实际 SQL 语句中并在执行语句期间传递值。

The SQL standard allows for selecting rows based on an expression that includes a variable list of values. A typical example would be select * from T_ACTOR where id in (1, 2, 3). This variable list is not directly supported for prepared statements by the JDBC standard. You cannot declare a variable number of placeholders. You need a number of variations with the desired number of placeholders prepared, or you need to generate the SQL string dynamically once you know how many placeholders are required. The named parameter support provided in the NamedParameterJdbcTemplate takes the latter approach. You can pass in the values as a java.util.List (or any Iterable) of simple values. This list is used to insert the required placeholders into the actual SQL statement and pass in the values during statement execution.

在传递多个值时应小心。JDBC 标准未保证你可以对 IN 表达式列表使用超过 100 个值。虽然许多数据库超过了此数量，但它们通常对允许的值数量有严格限制。例如，Oracle 的限制为 1000。

Be careful when passing in many values. The JDBC standard does not guarantee that you can use more than 100 values for an IN expression list. Various databases exceed this number, but they usually have a hard limit for how many values are allowed. For example, Oracle’s limit is 1000.

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

In addition to the primitive values in the value list, you can create a java.util.List of object arrays. This list can support multiple expressions being defined for the in clause, such as select * from T_ACTOR where (id, last_name) in 1, 'Johnson'), (2, 'Harrop'. This, of course, requires that your database supports this syntax.

Handling Complex Types for Stored Procedure Calls

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

When you call stored procedures, you can sometimes use complex types specific to the database. To accommodate these types, Spring provides a SqlReturnType for handling them when they are returned from the stored procedure call and SqlTypeValue when they are passed in as a parameter to the stored procedure.

SqlReturnType 接口有一个必须实现的单一方法（名为 getTypeValue）。此接口用作 SqlOutParameter 声明的一部分。以下示例显示如何返回用户声明类型 ITEM_TYPE 的 Oracle STRUCT 对象的值：

The SqlReturnType interface has a single method (named getTypeValue) that must be implemented. This interface is used as part of the declaration of an SqlOutParameter. The following example shows returning the value of an Oracle STRUCT object of the user declared type ITEM_TYPE:

  • Java

  • Kotlin

public class TestItemStoredProcedure extends StoredProcedure {

	public TestItemStoredProcedure(DataSource dataSource) {
		// ...
		declareParameter(new SqlOutParameter("item", OracleTypes.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;
			}));
		// ...
	}
class TestItemStoredProcedure(dataSource: DataSource) : StoredProcedure() {

	init {
		// ...
		declareParameter(SqlOutParameter("item", OracleTypes.STRUCT, "ITEM_TYPE") { cs, colIndx, sqlType, typeName ->
			val struct = cs.getObject(colIndx) as STRUCT
			val attr = struct.getAttributes()
			TestItem((attr[0] as Long, attr[1] as String, attr[2] as Date)
		})
		// ...
	}
}

你可以使用 SqlTypeValue 将 Java 对象（例如 TestItem）的值传递给存储过程。SqlTypeValue 接口有一个必须实现的单一方法（名为 createTypeValue）。活动连接将传入，你可以使用它来创建数据库特定对象，例如 StructDescriptor 实例或 ArrayDescriptor 实例。以下示例创建一个 StructDescriptor 实例：

You can use SqlTypeValue to pass the value of a Java object (such as TestItem) to a stored procedure. The SqlTypeValue interface has a single method (named createTypeValue) that you must implement. The active connection is passed in, and you can use it to create database-specific objects, such as StructDescriptor instances or ArrayDescriptor instances. The following example creates a StructDescriptor instance:

  • Java

  • Kotlin

final 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 conn, int sqlType, String typeName) throws SQLException {
		StructDescriptor itemDescriptor = new StructDescriptor(typeName, conn);
		Struct item = new STRUCT(itemDescriptor, conn,
		new Object[] {
			testItem.getId(),
			testItem.getDescription(),
			new java.sql.Date(testItem.getExpirationDate().getTime())
		});
		return item;
	}
};
val (id, description, expirationDate) = TestItem(123L, "A test item",
		SimpleDateFormat("yyyy-M-d").parse("2010-12-31"))

val value = object : AbstractSqlTypeValue() {
	override fun createTypeValue(conn: Connection, sqlType: Int, typeName: String?): Any {
		val itemDescriptor = StructDescriptor(typeName, conn)
		return STRUCT(itemDescriptor, conn,
				arrayOf(id, description, java.sql.Date(expirationDate.time)))
	}
}

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

You can now add this SqlTypeValue to the Map that contains the input parameters for the execute call of the stored procedure.

SqlTypeValue 的另一个用法是将值数组传递给 Oracle 存储过程。Oracle 具有自己的内部 ARRAY 类，在这种情况下必须使用它，你可以使用 SqlTypeValue 创建 Oracle ARRAY 的一个实例，并使用来自 Java ARRAY 的值填充它，如下例所示：

Another use for the SqlTypeValue is passing in an array of values to an Oracle stored procedure. Oracle has its own internal ARRAY class that must be used in this case, and you can use the SqlTypeValue to create an instance of the Oracle ARRAY and populate it with values from the Java ARRAY, as the following example shows:

  • Java

  • Kotlin

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

SqlTypeValue value = new AbstractSqlTypeValue() {
	protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
		ArrayDescriptor arrayDescriptor = new ArrayDescriptor(typeName, conn);
		ARRAY idArray = new ARRAY(arrayDescriptor, conn, ids);
		return idArray;
	}
};
class TestItemStoredProcedure(dataSource: DataSource) : StoredProcedure() {

	init {
		val ids = arrayOf(1L, 2L)
		val value = object : AbstractSqlTypeValue() {
			override fun createTypeValue(conn: Connection, sqlType: Int, typeName: String?): Any {
				val arrayDescriptor = ArrayDescriptor(typeName, conn)
				return ARRAY(arrayDescriptor, conn, ids)
			}
		}
	}
}