12.7 Common issues with parameter and data value handling

There are some issues involving parameters and data values that are common across all the different approaches provided by the Spring JDBC Framework.

12.7.1 Providing SQL type information for parameters

Most of the time Spring will assume 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.

There are a few different ways this can be accomplished:

  • Many of the update and query methods of the JdbcTemplate take an additional parameter in the form of an int array. This array should contain the SQL type using constant values from the java.sql.Types class. There must be one entry for each parameter.

  • You can wrap the parameter value that needs this additional information using the SqlParameterValue class. 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, 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.

12.7.2 Handling BLOB and CLOB objects

You can store images and other binary objects as well and large chunks of text. These large object are called BLOB for binary data and CLOB for character data. Spring lets you handle these large objects 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 data. The LobHandler provides access to a LobCreator, via the getLobCreator method, for creating new LOB objects to be inserted.

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

  • BLOB

    • byte[] – getBlobAsBytes and setBlobAsBytes

    • InputStream – getBlobAsBinaryStream and setBlobAsBinaryStream

  • CLOB

    • String – getClobAsString and setClobAsString

    • InputStream – getClobAsAsciiStream and setClobAsAsciiStream

    • Reader – getClobAsCharacterStream and setClobAsCharacterStream

We will now show an example of how to create and insert a BLOB. We will later see how to read it back from the database.

This example uses a JdbcTemplate and an implementation of the AbstractLobCreatingPreparedStatementCallback. There is one method that must be implemented and it is "setValues". In this method you will be provided with a LobCreator that can be used to set the values for the LOB columns in your SQL insert statement.

We are assuming that we have a variable named 'lobHandler' that already is set to an instance of a DefaultLobHandler. This is typically done using 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);
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

Here we use the lobHandler that in this example is a plain DefaultLobHandler

2

Using the method setClobAsCharacterStream we pass in the contents of the CLOB

3

Using the method setBlobAsBinartStream we pass in the contents of the BLOB

Now it's time to read the LOB data from the database. Again, we use a JdbcTempate and we have the same instance variable 'lobHandler' with a reference to a 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");                                                (1)
            results.put("CLOB", clobText);
            byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");                                                (2)
            results.put("BLOB", blobBytes);
            return results;
          }
        });

2

Using the method getClobAsString we retrieve the contents of the CLOB

3

Using the method getBlobAsBytes we retrieve the contents of the BLOB

12.7.3 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 - there is no way of declaring a variable number of place holders. You would have to either have a number of variations with the desired number of place holders prepared or you would have to dynamically generate the SQL string once you know how many place holders are required. The named parameter support provided in the NamedParameterJdbcTemplate and SimpleJdbcTemplate takes the latter approach. When you pass in the values you should pass them in 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.

[Note]Note

You need to be careful when passing in a large number of values. The JDBC standard doesn't 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. Oracle's limit for instance is 1000.

In addition to the primitive values in the value list, you can create a java.util.List of object arrays. This would support a case where there are multiple expressions defined for the IN clause like "select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop'))". This of course requires that your database supports this syntax.

12.7.4 Handling complex types for stored procedure calls

When calling stored procedures it's sometimes possible to 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 SqlReturnType interface has a single method named "getTypeValue" that must be implemented. This interface is used as part of the declaration of an SqlOutParameter.

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());
        item.setDescription((String)attr[1]);
        item.setExpirationDate((java.util.Date)attr[2]);
        return item;
      }
    }));

Going from Java to the database and passing in the value of a TestItem into a stored procedure is done using the SqlTypeValue. The SqlTypeValue interface has a single method named "createTypeValue" that must be implemented. The active connection is passed in and can be used to create database specific objects like StructDescriptors 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[] {
            testItem.getId(),
            testItem.getDescription(),
            new java.sql.Date(testItem.getExpirationDate().getTime())
        });
    return item;
  }
};

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

Another use for the SqlTypeValue is for 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 we can use the SqlTypeValue to create an instance of the Oracle ARRAY and populate it with values from our 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;
  }
};