Common problems with parameter and data value handling - Java-Springs

Common problems with parameters and data values exist in the different approaches provided by the 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.

You can provide SQL type information in several ways:

  • Many update and query methods of the Jdbc Template take an additional parameter in the form of an int array. This array is used to indicate the SQL type of the coresponding parameter using constant values from the java.sql.Types class. Provide one entry for each parameter.
  • You can use the Sql Parameter Value class to wrap the parameter value that needs this additional information. Create a new instance for each value and pass in the SQL type and parameter value in the constructor.You can also provide an optional scale parameter for numeric values.
  • For methods working with named parameters, use the Sql Parameter Source classes

Bean Property Sql Parameter Source or Map Sql Parameter Source. 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 objects, and large chunks of text.These large object are called BLOB for binary data and CLOB for character data.In Spring you can handle these large objects by using the Jdbc Template 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 data. The LobHandler provides access to a LobCreator class, through the get Lob Creator method, used for creating new LOB objects to be inserted.

The LobCreator/LobHandler provides the following support for LOB input and output:

  • BLOB
  • byte[] – get Blob As Bytes and set Blob As Bytes
  • InputStream – get Blob As Binary Stream and set Blob As Binary Stream
  • CLOB
  • String – get Clob As String and set Clob As String
  • InputStream – get Clob As Ascii Stream and set Clob As Ascii Stream
  • Reader – get Clob As Character Stream and set Clob As Character Stream

The next example shows how to create and insert a BLOB. Later you will see how to read it back from the database.

This example uses a JdbcTemplate and an implementation of the Abstract Lob Creating Prepared Statement Call back. It implements one method, set Values. This method provides a LobCreator that you 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 already is set to an instance of a Default Lob Handler. You typically set this value through dependency injection.

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

Pass in the lob Handler that in this example is a plain Default Lob Handler Using the method set Clob As Character Stream, pass in the contents of the CLOB. ¸ Using the method set Blob As Binary Stream, pass in the contents of the BLOB.Now it's time to read the LOB data from the database. Again, you use a Jdbc Template with the same instance variable lob Handler and a reference to a Default Lob Handler.

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;

· Using the method getClobAsString, retrieve the contents of the CLOB. Using the method get Blob As Bytes, 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 place holders. You need a number of variations with the desired number of place holders 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 Named Parameter Jdbc Template and Simple Jdbc Template takes the latter approach. Pass in the values as a java.util.List of primitive objects. This list will be used to insert the required place holders and pass in the values during the statement execution.

This list would support multiple expressions 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.

Here is an example of returning the value of an Oracle STRUCT object of the user declared type ITEM_TYPE. The Sql Return Type interface has a single method named getTypeValue that must be implemented. This interface is used as part of the declaration of an Sql Out Parameter.

final TestItem - new TestItem(123L, "A test item",
new SimpleDateFormat("yyyy-M-d").parse("2010-12-31"););
declareParameter(new SqlOutParameter ("item", OracleTypes.STRUCT, "ITEM_TYPE",
new SqlReturnType() {
public Object getTypeValue (CallableStatement cs, int colIndx, int sqlType, String typeName)
throws SQLException {
STRUCT struct = (STRUCT)cs.getObject(colIndx);
Object[] attr = struct.getAttributes();
TestItem item = new TestItem();
item.setId(((Number) attr[0]).longValue());
return item;

You use the Sql Type Value to pass in the value of a Java object like TestItem into a stored procedure.The Sql Type Value interface has a single method named create Type Value that you must implement. The active connection is passed in, and you can use it to create data base-specific objects such as Struct Descriptors, as shown in the following example, or ArrayDescriptors.

final 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[] {
new java.sql.Date(testItem.getExpirationDate().getTime())
return item;

This Sql Type Value can now be added to the Map containing the input parameters for the execute call of the stored procedure.

Another use for the Sql Type Value is passing in an array of values to an Oracle stored procedure. Oracle has its own internal ARRAY class that must be used in this case,and you can use the Sql Type Value to create an instance of the Oracle ARRAY and populate it with values from the Java ARRAY.

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;

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Java-Springs Topics