[Spring Docs] Common Problems with Parameter and Data Value Handling

이번글은 공식 문서에서 소개하는 Common Problems with Parameter and Data Value Handling에 대해 정리했습니다.

 

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.

Spring Framework의 JDBC 지원에서 제공하는 다양한 접근 방식에는 매개변수와 데이터 값과 관련된 공통적인 문제들이 존재합니다.

이 섹션에서는 그러한 문제들을 해결하는 방법을 다룹니다.

 

Providing SQL Type Information for Parameters

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.

보통 Spring은 전달된 매개변수의 타입을 기반으로 SQL 타입을 자동으로 결정합니다.

하지만 매개변수 값을 설정할 때 사용될 SQL 타입을 명시적으로 지정하는 것도 가능합니다.

이는 특히 NULL 값을 정확하게 설정해야 할 때 가끔 필요합니다.

 

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.

SQL 타입 정보를 제공하는 방법에는 여러 가지가 있습니다:

 

JdbcTemplate의 많은 update 및 query 메서드는 int 배열 형태의 추가 매개변수를 받을 수 있습니다.

이 배열은 java.sql.Types 클래스의 상수 값을 사용하여 각 매개변수에 대응하는 SQL 타입을 지정하는 데 사용됩니다.

각 매개변수마다 하나의 항목을 배열에 제공해야 합니다.

 

추가 정보가 필요한 매개변수 값을 감싸기 위해 SqlParameterValue 클래스를 사용할 수 있습니다.

이를 위해 각 값에 대해 새로운 인스턴스를 생성하고, 생성자에 SQL 타입과 매개변수 값을 전달합니다.

숫자 값의 경우에는 선택적으로 scale 파라미터(소수점 자릿수)도 제공할 수 있습니다.

 

이름이 지정된 매개변수(named parameters)를 사용하는 메서드의 경우,

SqlParameterSource 계열 클래스인 BeanPropertySqlParameterSource 또는 MapSqlParameterSource를 사용할 수 있습니다.

이들 클래스는 이름이 지정된 매개변수 값에 대해 SQL 타입을 등록할 수 있는 메서드를 제공합니다.

 

Handling BLOB and CLOB objects

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.

 

이미지, 기타 이진 데이터, 그리고 큰 텍스트 덩어리들을 데이터베이스에 저장할 수 있습니다.

이러한 큰 객체들은 이진 데이터의 경우 BLOB(Binary Large Object), 문자 데이터의 경우 CLOB(Character Large Object)라고 불립니다.

 

Spring에서는 JdbcTemplate을 직접 사용할 때뿐만 아니라, RDBMS 객체들과 SimpleJdbc 클래스들이 제공하는 더 높은 수준의 추상화를 사용할 때도 이러한 대용량 객체들을 처리할 수 있습니다.

 

이러한 모든 접근 방식은 실제로 LOB(Large Object) 데이터를 관리하기 위해 LobHandler 인터페이스의 구현체를 사용합니다.

LobHandler는 getLobCreator 메서드를 통해 LobCreator 클래스를 제공하며,

이 LobCreator는 삽입할 새로운 LOB 객체를 생성할 때 사용됩니다.

 

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

• BLOB(Binary Large Object)
  • byte[]: getBlobAsBytes and setBlobAsBytes
  • InputStream: getBlobAsBinaryStream and setBlobAsBinaryStream
• CLOB(Character Large Obejct)
  • String: getClobAsString and setClobAsString
  • InputStream: getClobAsAsciiStream and setClobAsAsciiStream
  • Reader: getClobAsCharacterStream and setClobAsCharacterStream

 

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

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.

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.

The following example shows how to create and insert a BLOB:

 

다음 예제는 BLOB을 생성하고 삽입하는 방법을 보여줍니다. 이후에는 데이터베이스에서 이를 다시 읽어오는 방법을 설명합니다.

이 예제는 JdbcTemplate과 AbstractLobCreatingPreparedStatementCallback의 구현체를 사용합니다. 이 클래스는 하나의 메서드인 setValues를 구현합니다.

이 메서드는 LobCreator를 제공하며, 이를 사용해 SQL insert문에서 LOB 컬럼의 값을 설정합니다.

이 예제에서는 lobHandler라는 변수가 이미 DefaultLobHandler의 인스턴스로 설정되어 있다고 가정합니다.

보통 이 값은 의존성 주입을 통해 설정됩니다.

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

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) {  
		protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
			ps.setLong(1, 1L);
			lobCreator.setClobAsCharacterStream(ps, 2, clobReader, (int)clobIn.length());  
			lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, (int)blobIn.length());  
		}
	}
);

blobIs.close();
clobReader.close();

 

(1) Pass in the lobHandler that (in this example) is a plain DefaultHandler.

(2) Using the method setClobAsCharacterStream to pass in the contents of the CLOB.

(3) Using the method set BlobAsBinaryStream to pass in the contents of the BLOB.

 

(1) 이 예제에서는 단순한 DefaultLobHandler를 사용하여 lobHandler를 전달합니다.

(2) setClobAsCharacterStream 메서드를 사용하여 CLOB의 내용을 전달합니다.

(3) setBlobAsBinaryStream 메서드를 사용하여 BLOB의 내용을 전달합니다.

 

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.

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

 

DefaultLobHandler.getLobCreator()로부터 반환된 LobCreator에서 setBlobAsBinaryStream, setClobAsAsciiStream, 또는 setClobAsCharacterStream 메서드를 호출할 때, contentLength 인자에 음수 값을 지정할 수 있습니다.

만약 지정된 contentLength가 음수이면, DefaultLobHandler는 길이 파라미터가 없는 JDBC 4.0 버전의 스트림 설정 메서드를 사용합니다.

그렇지 않으면, 지정된 길이 값을 JDBC 드라이버로 전달합니다.

사용 중인 JDBC 드라이버가 길이 값을 제공하지 않고 LOB을 스트리밍하는 것을 지원하는지 확인하려면, 해당 드라이버의 문서를 참고하십시오.

 

 

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:

 

이제 데이터베이스에서 LOB 데이터를 읽어올 차례입니다.

여기서도 마찬가지로 JdbcTemplate을 사용하며, lobHandler라는 동일한 인스턴스 변수와 DefaultLobHandler에 대한 참조를 사용합니다.

다음 예제는 이를 수행하는 방법을 보여줍니다.

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

 

(1) getClobAsString 메서드를 사용하여 CLOB의 내용을 가져옵니다.

(2) getBlobAsBytes 메서드를 사용하여 BLOB의 내용을 가져옵니다.

 

 

Passing in Lists of Values for IN Clause

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.

 

SQL 표준은 값들의 가변 목록을 포함하는 표현식을 기반으로 행을 선택하는 것을 허용합니다.

전형적인 예는 다음과 같습니다:

select * from T_ACTOR where id in (1, 2, 3)

 

이러한 가변 목록은 JDBC 표준의 prepared statement에서는 직접적으로 지원되지 않습니다.

즉, 가변 개수의 플레이스홀더를 선언할 수 없습니다.

 

이를 해결하려면, 원하는 개수만큼의 플레이스홀더를 미리 준비한 여러 가지 SQL 변형을 사용하거나, 필요한 플레이스홀더 개수를 알고 난 뒤, SQL 문자열을 동적으로 생성해야 합니다.

 

NamedParameterJdbcTemplate이 제공하는 이름이 지정된 매개변수 지원(named parameter support)은후자의 방식을 사용합니다.단순 값들의 java.util.List 또는 어떤 Iterable 객체든지 값을 전달할 수 있습니다. 이 리스트는 실제 SQL 문에 필요한 플레이스홀더들을 삽입하는 데 사용되며, SQL 실행 시에 해당 값들이 전달됩니다.

 

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.

 

많은 값을 IN 표현식 목록에 전달할 때는 주의해야 합니다. JDBC 표준은 IN 절에 100개 이상의 값을 사용할 수 있다고 보장하지 않습니다.

여러 데이터베이스는 이 수를 초과할 수 있지만, 보통은 허용되는 값의 개수에 대한 명확한 제한이 존재합니다.

예를 들어, Oracle의 경우 이 제한은 1000개입니다.

 

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.

 

값 목록에 원시 값(primitive value)들뿐만 아니라, java.util.List 형태의 객체 배열(object arrays)도 생성할 수 있습니다.

이러한 리스트는 IN 절에서 여러 표현식을 정의하는 것을 지원할 수 있습니다.

예를 들면 다음과 같은 쿼리가 가능합니다:

select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop'))

물론, 이와 같은 문법을 사용하는 데이터베이스가 해당 문법을 지원해야만 사용할 수 있습니다.

Handling Complex Types for Stored Procedure Calls

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.

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 a java.sql.Struct object of the user declared type ITEM_TYPE:

 

저장 프로시저를 호출할 때, 경우에 따라 데이터베이스에 특화된 복합 타입(complex types)을 사용할 수 있습니다.

이러한 타입들을 처리할 수 있도록, Spring은 다음과 같은 기능을 제공합니다:

• 저장 프로시저에서 반환되는 복합 타입을 처리하기 위해 SqlReturnType을 제공합니다.
• 저장 프로시저에 매개변수로 전달할 때는 SqlTypeValue을 제공합니다.

SqlReturnType 인터페이스는 하나의 메서드(getTypeValue)만 구현하면 됩니다.

이 인터페이스는 SqlOutParameter를 선언할 때 함께 사용됩니다.

 

다음 예제는 사용자 정의 타입 ITEM_TYPE의 java.sql.Struct 객체 값을 반환하는 방법을 보여줍니다:

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;
				}));
		// ...
	}

}

 

You can use SqlTypeValue to pass the value of 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 java.sql.Struct instances or java.sql.Array instances. The following example creates a java.sql.Struct instance.

 

SqlTypeValue를 사용하여 Java 객체(예: TestItem)의 값을 저장 프로시저에 전달할 수 있습니다.

SqlTypeValue 인터페이스는 하나의 메서드인 createTypeValue만 구현하면 됩니다.

이 메서드에는 활성화된 데이터베이스 연결(Connection)이 전달되며,이를 사용하여 java.sql.Struct 인스턴스나 java.sql.Array 인스턴 스 같은 데이터베이스 특화 객체를 생성할 수 있습니다.

다음 예제는 java.sql.Struct 인스턴스를 생성하는 방법을 보여줍니다.

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

 

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

Another use for the SqlTypeValue is passing in an array of values to an Oracle stored procedure. Oracle has an createOracleArray method on OracleConnection that you can access by unwrapping it. You can use the SqlTypeValue to create an array and populate it with values from the Java java.sql.Array, as the following example shows:

 

이제 이 SqlTypeValue를 저장 프로시저의 execute 호출에 사용되는 입력 매개변수 맵(Map)에 추가할 수 있습니다.

 

SqlTypeValue의 또 다른 용도는, 값들의 배열을 Oracle 저장 프로시저에 전달하는 것입니다. Oracle에서는 OracleConnection에 있는 createOracleArray 메서드를 사용하여 배열을 생성할 수 있으며, 이 메서드에 접근하려면 연결을 unwrap해야 합니다.

 

아래 예제에서 보듯이, SqlTypeValue를 사용하여 배열을 생성하고, Java의 배열 값을 사용해 java.sql.Array 객체에 데이터를 채울 수 있습니다.

 

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

 

정리

• Spring JDBC에서는 단순한 값 외에도 LOB, 리스트, 복합 타입 등 다양한 데이터를 처리할 수 있습니다.
• NULL이나 복잡한 타입을 다룰 때는 SqlParameterValue를 사용해 SQL 타입을 명시해야 합니다.
• BLOB과 CLOB 같은 대용량 객체는 LobHandler와 LobCreator를 통해 스트림이나 배열로 읽고 쓸 수 있습니다.
• IN 절에 리스트를 전달할 때는 NamedParameterJdbcTemplate을 사용해 SQL을 동적으로 생성해야 하며, DB마다 값 개수 제한이 있으므로 주의해야 합니다.
• 저장 프로시저에서 복합 타입을 주고받을 때는 SqlTypeValue와 SqlReturnType을 통해 Struct나 Array와 같은 DB 특화 객체를 사용할 수 있습니다.