Specifying SQL type information for parameters
Typically, Spring determines the SQL type of parameters based on the type of the parameter passed. You can explicitly specify the SQL type to be used when setting parameter values. Sometimes this is necessary to set NULL
values correctly.
You can specify SQL type information in several ways:
Many update and query methods
JdbcTemplate
templates accept an additional parameter in the form of an arrayint
. This array is used to specify the SQL type of the corresponding parameter using constant values from thejava.sql.Types
class. Specify one entry for each parameter.You can use the
SqlParameterValue
class to wrap the value of a parameter that requires this additional information. To do this, create a new instance for each value and pass the SQL type and parameter value in the constructor. You can also specify an optional scaling parameter for numeric values.For methods that work with named parameters, you can use the classes
SqlParameterSource
,BeanPropertySqlParameterSource
orMapSqlParameterSource
. They both have methods for registering the SQL type for any of the named parameter values.
Handling BLOB and CLOB objects
The database can store images, other binary data and large chunks of text. In the case of binary data, these large objects are called binary large objects, or abbreviated BLOB (Binary Large OBject), and character large objects, or CLOB (Character Large OBject), in the case of character data. In Spring, you can work with such large objects by using JdbcTemplate
directly, as well as by using higher-level abstractions provided by RDBMS objects and SimpleJdbc
classes. All of these approaches use an implementation of the LobHandler
interface to actually manage the LOB (Large OBject) data. LobHandler
provides access to the LobCreator
class through the getLobCreator
method, which is used to create new LOB objects for insertion.
LobCreator
and LobHandler
provide the following support for LOB input and output:
BLOB
byte[]
:getBlobAsBytes
andsetBlobAsBytes
InputStream
:getBlobAsBinaryStream
andsetBlobAsBinaryStream
CLOB
String
:getClobAsString
andsetClobAsString
InputStream
:getClobAsAsciiStream
andsetClobAsAsciiStream
Reader
:getClobAsCharacterStream
andsetClobAsCharacterStream
The following example shows how to create and insert a BLOB. We will demonstrate later how to read it back from the database.
This example uses JdbcTemplate
and the AbstractLobCreatingPreparedStatementCallback
implementation. It implements one method, setValues
. This method provides a LobCreator
that is used to set values for LOB columns in the SQL insert statement.
In this example, we assume that the variable lobHandler
exists , which is already set for the DefaultLobHandler
instance. Typically this value is set via dependency injection.
The following example shows how to create and insert a 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();
- We pass
lobHandler
, which (in this example) is a normalDefaultLobHandler
. - Using the
setClobAsCharacterStream
method to pass the contents of the CLOB. - Using the
setBlobAsBinaryStream
method to pass the contents of a 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) {
override fun setValues(ps: PreparedStatement, lobCreator: LobCreator) {
ps.setLong(1, 1L)
lobCreator.setClobAsCharacterStream(ps, 2, clobReader, clobIn.length().toInt())
lobCreator.setBlobAsBinaryStream(ps, 3, blobIs, blobIn.length().toInt())
}
}
)
blobIs.close()
clobReader.close()
- We pass
lobHandler
, which (in this example) is the usualDefaultLobHandler
. - Using the
setClobAsCharacterStream
method to pass the contents of a CLOB. - Using the
setBlobAsBinaryStream
method to pass the contents of a BLOB.
If you call the method setBlobAsBinaryStream
, setClobAsAsciiStream
or setClobAsCharacterStream
For the LobCreator
returned from DefaultLobHandler.getLobCreator()
, you can optionally set the contentLength
argument to a negative value. If the specified content length is negative, DefaultLobHandler
uses variants of JDBC 4.0 thread configuration methods without a length parameter. Otherwise, it passes the specified length to the driver.
Refer to the documentation for the JDBC driver you are using to ensure that it supports LOB streaming without specifying the content length.
Now it's time to read the LOB data from the database. Again, the JdbcTemplate
is used with the same lobHandler
instance variable and a reference to the DefaultLobHandler
. The following example shows how to do this:
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;
}
});
- Usage
getClobAsString
method to get the contents of a CLOB. - Use the
getBlobAsBytes
method to get the contents of a BLOB.
val l = jdbcTemplate.query("select id, a_clob, a_blob from lob_table") { rs, _ ->
val clobText = lobHandler.getClobAsString(rs, "a_clob")
val blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob")
mapOf("CLOB" to clobText, "BLOB" to blobBytes)
}
- Using the
getClobAsString
method to get the contents of a CLOB. - Using the
getBlobAsBytes
method to get the contents of a BLOB .
Passing lists of values for the "IN" expression
The SQL standard allows you to select rows based on an expression that includes a list of values for a variable. A typical example is select * from T_ACTOR where id in (1, 2, 3
). This list of variables is not directly supported by the JDBC standard for prepared statements. You can declare a variable number of placeholders. You need to prepare several options with the required number of placeholders, or you need to generate the SQL string dynamically once you know how many placeholders are required. The named parameter support provided in NamedParameterJdbcTemplate
and JdbcTemplate
takes the latter approach. You can pass values as a list of primitive objects java.util.List
. This list is used to insert the required placeholders and pass values during statement execution.
in
expressions. Various databases allow you to exceed this figure, but they usually have a strict limit on the number of valid values. For example, Oracle's limit is 1000.
In addition to primitive values in a list of values, you can create java.util.List
from arrays of objects. This list can support multiple expressions defined for the in
expression, for example 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
When calling stored procedures, you can sometimes use database-specific complex types. To work with these types, Spring provides SqlReturnType
to handle them if they are returned from a stored procedure call, and SqlTypeValue
if they are passed 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 SqlOutParameter
parameter declaration. The following example shows the return of the value of an Oracle object called STRUCT
of type ITEM_TYPE
declared by the user:
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)
})
// ...
}
}
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
) to implement.The active connection is passed and you can use it to create database-specific objects such as StructDescriptor
instances or ArrayDescriptor
instances code>. The following example instantiates a StructDescriptor
:
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)))
}
}
You can now add this SqlTypeValue
value to the Map
containing the input parameters for calling the execute
stored procedure.
Another use of SqlTypeValue
is passing an array of values to an Oracle stored procedure. Oracle has its own internal class ARRAY
which should be used in this case and you can use SqlTypeValue
to instantiate ARRAY
from Orcale and populate it with values from ARRAY
from Java, as shown in the following example:
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)
}
}
}
}
GO TO FULL VERSION