This version is still in development and is not considered stable yet. For the latest stable version, please use Spring Framework 6.1.12! |
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.
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.
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 anint
array. This array is used to indicate the SQL type of the corresponding parameter by using constant values from thejava.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
orMapSqlParameterSource
. They both have methods for registering the SQL type for any of the named parameter values.
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.
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 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:
-
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 | Pass in the lobHandler that (in this example) is a plain DefaultLobHandler . |
2 | Using the method setClobAsCharacterStream to pass in the contents of the CLOB. |
3 | Using the method setBlobAsBinaryStream to pass in the contents of the 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 | Pass in the lobHandler that (in this example) is a plain DefaultLobHandler . |
2 | Using the method setClobAsCharacterStream to pass in the contents of the CLOB. |
3 | Using the method setBlobAsBinaryStream to pass in the contents of the BLOB. |
If you invoke the See the documentation for the JDBC driver you use to verify that it supports streaming a LOB without providing the content length. |
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:
-
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 | Using the method getClobAsString to retrieve the contents of the CLOB. |
2 | Using the method getBlobAsBytes to retrieve the contents of the 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 | Using the method getClobAsString to retrieve the contents of the CLOB. |
2 | Using the method getBlobAsBytes to retrieve the contents of the 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.
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 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.
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
:
-
Java
-
Kotlin
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
})
// ...
}
}
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
) 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:
-
Java
-
Kotlin
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)
}
}
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:
-
Java
-
Kotlin
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)
}
}