Parameter 與資料值處理的常見問題

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

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

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

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

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

}

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
				val item = TestItem()
				item.id = (attr[0] as Number).toLong()
				item.description = attr[1] as String
				item.expirationDate = attr[2] as Date
				item
			})
		// ...
	}
}

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

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

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

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