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

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

为参数提供SQL类型信息

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

您可以通过多种方式提供SQL类型信息：

  • JdbcTemplate的许多更新和查询方法接受一个额外的参数，形式为int数组。通过使用java.sql.Types类的常量值来指示相应参数的SQL类型。为每个参数提供一个条目。

  • 您可以使用SqlParameterValue类来包装需要此额外信息的参数值。为此，为每个值创建一个新实例，并在构造函数中传入SQL类型和参数值。您还可以为数值提供可选的比例参数。

  • 对于使用命名参数的方法，可以使用SqlParameterSource类、BeanPropertySqlParameterSource或MapSqlParameterSource。它们都有用于为任何命名参数值注册SQL类型的方法。

处理BLOB和CLOB对象

您可以在数据库中存储图像、其他二进制数据和大块文本。这些大对象称为BLOB（二进制大对象）用于二进制数据和CLOB（字符大对象）用于字符数据。在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

  • Kotlin

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 传入（在此示例中）为普通DefaultLobHandler的lobHandler。
2 使用setClobAsCharacterStream方法传入CLOB的内容。
3 使用setBlobAsBinaryStream方法传入BLOB的内容。 
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 传入（在此示例中）为普通DefaultLobHandler的lobHandler。
2 使用setClobAsCharacterStream方法传入CLOB的内容。
3 使用setBlobAsBinaryStream方法传入BLOB的内容。

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

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

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

  • Java

  • Kotlin

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 使用getClobAsString方法检索CLOB的内容。
2 使用getBlobAsBytes方法检索BLOB的内容。 
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)
}
1 使用getClobAsString方法检索CLOB的内容。
2 使用getBlobAsBytes方法检索BLOB的内容。

传递IN子句的值列表

SQL标准允许根据包含变量值列表的表达式选择行。典型示例是select * from T_ACTOR where id in (1, 2, 3)。JDBC标准不直接支持此变量列表用于预编译语句。您无法声明可变数量的占位符。您需要准备所需数量占位符的多个变体，或者在知道需要多少占位符时动态生成SQL字符串。Spring提供的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的Oracle STRUCT对象的值：

  • 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实例：

  • 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添加到包含存储过程调用的输入参数的Map中。

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

  • 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)
			}
		}
	}
}