Common problems with processing parameters and data values

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 array int. This array is used to specify the SQL type of the corresponding parameter using constant values from the java.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 or MapSqlParameterSource. 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 and setBlobAsBytes

  • InputStream: getBlobAsBinaryStream and setBlobAsBinaryStream

• CLOB

  • String: getClobAsString and setClobAsString

  • InputStream: getClobAsAsciiStream and setClobAsAsciiStream

  • Reader: getClobAsCharacterStream and setClobAsCharacterStream

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

1. We pass lobHandler, which (in this example) is a normal DefaultLobHandler.
2. Using the setClobAsCharacterStream method to pass the contents of the CLOB.
3. 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()

1. We pass lobHandler, which (in this example) is the usual DefaultLobHandler .
2. Using the setClobAsCharacterStream method to pass the contents of a CLOB.
3. Using the setBlobAsBinaryStream method to pass the contents of a BLOB.

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

1. Usage getClobAsString method to get the contents of a CLOB.
2. 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)
}

1. Using the getClobAsString method to get the contents of a CLOB.
2. 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 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)
            }
        }
    }
}