Common Problems with Parameter and Data Value Handling

Spring Framework의 JDBC support에서 제공하는 다양한 접근 방식에는 parameter와 data value와 관련된 일반적인 문제가 존재합니다. 이 섹션에서는 이러한 문제를 해결하는 방법을 다룹니다.

Providing SQL Type Information for Parameters

일반적으로 Spring은 전달된 parameter의 타입을 기반으로 parameter의 SQL type을 결정합니다. parameter value를 설정할 때 사용될 SQL type을 명시적으로 제공하는 것도 가능합니다. 이는 NULL value를 올바르게 설정하기 위해 때때로 필요합니다.

SQL type 정보를 제공하는 방법은 여러 가지가 있습니다:

• JdbcTemplate의 많은 update 및 query method는 int array 형태의 추가 parameter를 받습니다. 이 array는 java.sql.Types class의 상수 값을 사용하여 해당 parameter의 SQL type을 나타내는 데 사용됩니다. 각 parameter마다 하나의 entry를 제공해야 합니다.
• 추가 정보가 필요한 parameter value를 감싸기 위해 SqlParameterValue class를 사용할 수 있습니다. 이를 위해 각 value에 대해 새로운 instance를 생성하고 constructor에 SQL type과 parameter value를 전달합니다. 숫자 value의 경우 선택적인 scale parameter도 제공할 수 있습니다.
• named parameter로 동작하는 method의 경우, SqlParameterSource class인 BeanPropertySqlParameterSource 또는 MapSqlParameterSource를 사용할 수 있습니다. 이 둘은 모두 named parameter value 각각에 대해 SQL type을 등록하기 위한 method를 가지고 있습니다.

Handling BLOB and CLOB objects

database에 image, 기타 binary data, 그리고 큰 크기의 text를 저장할 수 있습니다. 이러한 large object는 binary data의 경우 BLOB(Binary Large OBject), character data의 경우 CLOB(Character Large OBject)라고 불립니다.

Spring에서는 JdbcTemplate을 직접 사용할 때뿐만 아니라 RDBMS Objects와 SimpleJdbc class가 제공하는 상위 abstraction을 사용할 때에도 이러한 large object를 다룰 수 있습니다. 이러한 모든 접근 방식은 LOB(Large OBject) data의 실제 관리를 위해 LobHandler interface의 구현을 사용합니다. LobHandler는 getLobCreator method를 통해 LobCreator class에 대한 access를 제공하며, 이 class는 insert할 새로운 LOB object를 생성하는 데 사용됩니다.

LobCreator와 LobHandler는 LOB input과 output에 대해 다음과 같은 지원을 제공합니다:

• BLOB
  • byte[]: getBlobAsBytes 및 setBlobAsBytes
  • InputStream: getBlobAsBinaryStream 및 setBlobAsBinaryStream
• CLOB
  • String: getClobAsString 및 setClobAsString
  • InputStream: getClobAsAsciiStream 및 setClobAsAsciiStream
  • Reader: getClobAsCharacterStream 및 setClobAsCharacterStream

다음 예제는 BLOB을 생성하고 insert하는 방법을 보여줍니다. 이후에 database에서 이를 다시 읽어오는 방법을 보여줍니다.

이 예제는 JdbcTemplate과 AbstractLobCreatingPreparedStatementCallback의 구현을 사용합니다. 이 구현은 setValues라는 하나의 method를 구현합니다. 이 method는 SQL insert statement의 LOB column에 대한 value를 설정하는 데 사용하는 LobCreator를 제공합니다.

이 예제에서는 lobHandler라는 변수가 이미 DefaultLobHandler instance로 설정되어 있다고 가정합니다. 일반적으로 이 값은 의존성 주입을 통해 설정합니다.

다음 예제는 BLOB을 생성하고 insert하는 방법을 보여줍니다:

1final File blobIn = new File("spring2004.jpg");
2final InputStream blobIs = new FileInputStream(blobIn);
3final File clobIn = new File("large.txt");
4final InputStream clobIs = new FileInputStream(clobIn);
5final InputStreamReader clobReader = new InputStreamReader(clobIs);
6
7jdbcTemplate.execute(
8	"INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
9	new AbstractLobCreatingPreparedStatementCallback(lobHandler) {  (1)
10		protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
11			ps.setLong(1, 1L);
12			lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int)clobIn.length());  (2)
13			lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int)blobIn.length());  (3)
14		}
15	}
16);
17
18blobIs.close();
19clobReader.close();

1	이 예제에서 lobHandler는 일반적인 DefaultLobHandler이므로 이를 전달합니다.
2	setClobAsCharacterStream method를 사용하여 CLOB의 내용을 전달합니다.
3	setBlobAsBinaryStream method를 사용하여 BLOB의 내용을 전달합니다.

1val blobIn = File("spring2004.jpg")
2val blobIs = FileInputStream(blobIn)
3val clobIn = File("large.txt")
4val clobIs = FileInputStream(clobIn)
5val clobReader = InputStreamReader(clobIs)
6
7jdbcTemplate.execute(
8	"INSERT INTO lob_table (id, a_clob, a_blob) VALUES (?, ?, ?)",
9	object : AbstractLobCreatingPreparedStatementCallback(lobHandler) {  (1)
10		override fun setValues(ps: PreparedStatement, lobCreator: LobCreator) {
11			ps.setLong(1, 1L)
12			lobCreator.setClobAsCharacterStream(ps, 2, clobReader, clobIn.length().toInt())  (2)
13			lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, blobIn.length().toInt())  (3)
14		}
15	}
16)
17blobIs.close()
18clobReader.close()

1	이 예제에서 lobHandler는 일반적인 DefaultLobHandler이므로 이를 전달합니다.
2	setClobAsCharacterStream method를 사용하여 CLOB의 내용을 전달합니다.
3	setBlobAsBinaryStream method를 사용하여 BLOB의 내용을 전달합니다.

DefaultLobHandler.getLobCreator()에서 반환된 LobCreator에 대해 setBlobAsBinaryStream, setClobAsAsciiStream, 또는 setClobAsCharacterStream method를 호출할 때 contentLength argument에 음수 값을 지정할 수 있습니다. 지정된 content length가 음수인 경우, DefaultLobHandler는 length parameter가 없는 JDBC 4.0 set-stream method variant를 사용합니다. 그렇지 않으면 지정된 length를 driver에 전달합니다. 사용 중인 JDBC driver의 documentation을 참조하여 content length를 제공하지 않고 LOB을 streaming하는 것을 지원하는지 확인하십시오.

이제 database에서 LOB data를 읽어올 차례입니다. 다시 JdbcTemplate과 동일한 instance variable lobHandler 및 DefaultLobHandler reference를 사용합니다. 다음 예제는 이를 수행하는 방법을 보여줍니다:

1List<Map<String, Object>> l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table",
2	new RowMapper<Map<String, Object>>() {
3		public Map<String, Object> mapRow(ResultSet rs, int i) throws SQLException {
4			Map<String, Object> results = new HashMap<String, Object>();
5			String clobText = lobHandler.getClobAsString(rs, "a_clob");  (1)
6			results.put("CLOB", clobText);
7			byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");  (2)
8			results.put("BLOB", blobBytes);
9			return results;
10		}
11	});

1	getClobAsString method를 사용하여 CLOB의 내용을 가져옵니다.
2	getBlobAsBytes method를 사용하여 BLOB의 내용을 가져옵니다.

1val l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table") { rs, _ ->
2	val clobText = lobHandler.getClobAsString(rs, "a_clob")  (1)
3	val blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob")  (2)
4	mapOf("CLOB" to clobText, "BLOB" to blobBytes)
5}

1	getClobAsString method를 사용하여 CLOB의 내용을 가져옵니다.
2	getBlobAsBytes method를 사용하여 BLOB의 내용을 가져옵니다.

Passing in Lists of Values for IN Clause

SQL standard는 variable value list를 포함하는 expression을 기반으로 row를 선택하는 것을 허용합니다. 전형적인 예는 select * from T_ACTOR where id in (1, 2, 3)입니다. 이 variable list는 JDBC standard에서 prepared statement에 대해 직접적으로 지원되지 않습니다.

variable 개수의 placeholder를 선언할 수 없습니다. 원하는 개수의 placeholder가 미리 준비된 여러 변형을 사용하거나, 필요한 placeholder 개수를 알게 되었을 때 SQL string을 동적으로 생성해야 합니다. NamedParameterJdbcTemplate에서 제공하는 named parameter support는 후자의 접근 방식을 사용합니다. java.util.List(또는 임의의 Iterable) 형태의 simple value들을 value로 전달할 수 있습니다. 이 list는 실제 SQL statement에 필요한 placeholder를 삽입하고 statement 실행 중에 value를 전달하는 데 사용됩니다.

많은 value를 전달할 때는 주의해야 합니다. JDBC standard는 IN expression list에 100개를 초과하는 value를 사용할 수 있다고 보장하지 않습니다. 다양한 database가 이 숫자를 초과할 수 있지만, 일반적으로 허용되는 value 개수에 대한 hard limit가 존재합니다. 예를 들어, Oracle의 limit는 1000입니다.

value list에 있는 primitive value 외에도 object array의 java.util.List를 생성할 수 있습니다. 이 list는 select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop'))와 같이 in 절에 대해 여러 expression이 정의되는 것을 지원할 수 있습니다. 물론 이를 위해서는 사용 중인 database가 이 syntax를 지원해야 합니다.

Handling Complex Types for Stored Procedure Calls

stored procedure를 호출할 때 때때로 database에 특화된 complex type을 사용할 수 있습니다. 이러한 type을 수용하기 위해 Spring은 stored procedure 호출에서 반환될 때 이를 처리하기 위한 SqlReturnType과 stored procedure에 parameter로 전달될 때 이를 처리하기 위한 SqlTypeValue를 제공합니다.

SqlReturnType interface에는 구현해야 하는 getTypeValue라는 단일 method가 있습니다. 이 interface는 SqlOutParameter 선언의 일부로 사용됩니다. 다음 예제는 사용자 정의 type ITEM_TYPE의 java.sql.Struct object의 value를 반환하는 방법을 보여줍니다:

1import java.sql.CallableStatement;
2import java.sql.Struct;
3import java.sql.Types;
4
5import javax.sql.DataSource;
6
7import org.springframework.jdbc.core.SqlOutParameter;
8import org.springframework.jdbc.object.StoredProcedure;
9
10public class TestItemStoredProcedure extends StoredProcedure {
11
12	public TestItemStoredProcedure(DataSource dataSource) {
13		super(dataSource, "get_item");
14		declareParameter(new SqlOutParameter("item", Types.STRUCT, "ITEM_TYPE",
15				(CallableStatement cs, int colIndx, int sqlType, String typeName) -> {
16					Struct struct = (Struct) cs.getObject(colIndx);
17					Object[] attr = struct.getAttributes();
18					TestItem item = new TestItem();
19					item.setId(((Number) attr[0]).longValue());
20					item.setDescription((String) attr[1]);
21					item.setExpirationDate((java.util.Date) attr[2]);
22					return item;
23				}));
24		// ...
25	}
26
27}

1import org.springframework.jdbc.core.SqlOutParameter
2import org.springframework.jdbc.`object`.StoredProcedure
3import java.sql.CallableStatement
4import java.sql.Struct
5import java.sql.Types
6import java.util.Date
7import javax.sql.DataSource
8
9class TestItemStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, "get_item") {
10	init {
11		declareParameter(
12			SqlOutParameter("item", Types.STRUCT, "ITEM_TYPE") {
13					cs: CallableStatement, colIndx: Int, _: Int, _: String? ->
14				val struct = cs.getObject(colIndx) as Struct
15				val attr = struct.attributes
16				TestItem(
17					(attr[0] as Number).toLong(),
18					attr[1] as String,
19					attr[2] as Date
20				)
21			}
22		)
23		// ...
24	}
25}

SqlTypeValue를 사용하여 Java object(TestItem과 같은)의 value를 stored procedure에 전달할 수 있습니다. SqlTypeValue interface에는 구현해야 하는 createTypeValue라는 단일 method가 있습니다. active connection이 전달되며, 이를 사용하여 java.sql.Struct instance나 java.sql.Array instance와 같은 database-specific object를 생성할 수 있습니다. 다음 예제는 java.sql.Struct instance를 생성합니다:

1TestItem testItem = new TestItem(123L, "A test item",
2		new SimpleDateFormat("yyyy-M-d").parse("2010-12-31"));
3
4SqlTypeValue value = new AbstractSqlTypeValue() {
5	protected Object createTypeValue(Connection connection, int sqlType, String typeName) throws SQLException {
6		Object[] item = new Object[] { testItem.getId(), testItem.getDescription(),
7				new java.sql.Date(testItem.getExpirationDate().getTime()) };
8		return connection.createStruct(typeName, item);
9	}
10};

1val testItem = TestItem(
2	123L,
3	"A test item",
4	SimpleDateFormat("yyyy-M-d").parse("2010-12-31")
5)
6
7val value = object : AbstractSqlTypeValue() {
8	override fun createTypeValue(connection: Connection, sqlType: Int, typeName: String?): Any {
9		val item = arrayOf<Any>(
10			testItem.id,
11			testItem.description,
12			Date(testItem.expirationDate.time)
13		)
14		return connection.createStruct(typeName, item)
15	}
16}

이제 이 SqlTypeValue를 stored procedure의 execute 호출을 위한 input parameter를 담고 있는 Map에 추가할 수 있습니다.

SqlTypeValue의 또 다른 사용 예는 Oracle stored procedure에 value array를 전달하는 것입니다. Oracle에는 OracleConnection에 있는 createOracleArray method가 있으며, 이를 unwrap을 통해 access할 수 있습니다. 다음 예제에서 보듯이, SqlTypeValue를 사용하여 array를 생성하고 Java java.sql.Array의 value로 이를 채울 수 있습니다:

1Long[] ids = new Long[] {1L, 2L};
2
3SqlTypeValue value = new AbstractSqlTypeValue() {
4	protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
5		return conn.unwrap(OracleConnection.class).createOracleArray(typeName, ids);
6	}
7};

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