Распространенные проблемы с обработкой параметров и значений данных

Указание сведений о типе SQL для параметров

Обычно Spring определяет SQL-тип параметров на основе типа переданного параметра. Можно явно указать тип SQL, который будет использоваться при настройке значений параметров. Иногда это необходимо для правильной установки значений NULL.

Можно указать информацию о типе SQL несколькими способами:

• Многие методы обновления и запроса шаблона JdbcTemplate принимают дополнительный параметр в виде массива int. Этот массив используется для указания SQL-типа соответствующего параметра с помощью константных значений из класса java.sql.Types. Указывайте одну запись для каждого параметра.

• Можно использовать класс SqlParameterValue, чтобы обернуть значение параметра, которому требуется эта дополнительная информация. Для этого создайте новый экземпляр для каждого значения и передайте в конструкторе тип SQL и значение параметра. Также можно указать необязательный масштабирующий параметр для числовых значений.

• Для методов, работающих с именованными параметрами, можно использовать классы SqlParameterSource, BeanPropertySqlParameterSource или MapSqlParameterSource. Они оба имеют методы для регистрации типа SQL для любого из значений именованных параметров.

Обработка объектов BLOB и CLOB

В базе данных можно хранить изображения, другие двоичные данные и большие фрагменты текста. В случае двоичных данных эти большие объекты называются двоичные большие объекты, или сокращенно BLOB (Binary Large OBject), и символьные большие объекты, или сокращенно CLOB (Character Large OBject), в случае символьных данных. В Spring можно работать с такими большими объектами, используя JdbcTemplate напрямую, а также используя абстракции более высокого уровня, предоставляемые объектами РСУБД и классами SimpleJdbc. Все эти подходы используют реализацию интерфейса LobHandler для фактического управления данными LOB (Large OBject). LobHandler предоставляет доступ к классу LobCreator через метод getLobCreator, который используется для создания новых LOB-объектов для вставки.

LobCreator и LobHandler обеспечивают следующую поддержку ввода и вывода LOB:

• BLOB

  • byte[]: getBlobAsBytes и setBlobAsBytes

  • InputStream: getBlobAsBinaryStream и setBlobAsBinaryStream

• CLOB

  • String: getClobAsString и setClobAsString

  • InputStream: getClobAsAsciiStream и setClobAsAsciiStream

  • Reader: getClobAsCharacterStream и setClobAsCharacterStream

В следующем примере показано, как создать и вставить BLOB. Позже будет продемонстрировано, как считать его обратно из базы данных.

В этом примере используется JdbcTemplate и реализация AbstractLobCreatingPreparedStatementCallback. Она реализует один метод, setValues. Этот метод предоставляет LobCreator, который используется для установки значений для столбцов LOB в SQL-стейтмента вставки.

В данном примере мы предполагаем, что существует переменная lobHandler, которая уже установлена для экземпляра DefaultLobHandler. Обычно это значение устанавливается через внедрение зависимостей.

В следующем примере показано, как создать и вставить 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. Передаем lobHandler, который (в данном примере) является обычным DefaultLobHandler.
2. Использование метода setClobAsCharacterStream для передачи содержимого CLOB.
3. Использование метода setBlobAsBinaryStream для передачи содержимого 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. Передаем lobHandler, который (в данном примере) является обычным DefaultLobHandler.
2. Использование метода setClobAsCharacterStream для передачи содержимого CLOB.
3. Использование метода setBlobAsBinaryStream для передачи содержимого BLOB.

Теперь пришло время прочитать данные LOB из базы данных. Опять же, используется JdbcTemplate с той же переменной экземпляра lobHandler и ссылкой на 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");  
            results.put("CLOB", clobText);
            byte[] blobBytes = lobHandler.getBlobAsBytes(rs, "a_blob");  
            results.put("BLOB", blobBytes);
            return results;
        }
    });

1. Использование метода getClobAsString для получения содержимого CLOB.
2. Использование метода getBlobAsBytes для получения содержимого 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. Использование метода getClobAsString для получения содержимого CLOB.
2. Использование метода getBlobAsBytes для получения содержимого BLOB.

Передача списков значений для выражения "IN"

Стандарт SQL позволяет делать выборку строк на основе выражения, включающего список значений переменной. В качестве типового примера можно привести select * from T_ACTOR where id in (1, 2, 3). Этот список переменных не поддерживается непосредственно стандартом JDBC для подготовленных стейтментов. Можно объявить переменное количество плейсхолдеров. Необходимо подготовить несколько вариантов с нужным количеством плейсхолдеров, или же нужно сгенерировать строку SQL динамически, как только будет известно, сколько плейсхолдеров требуется. Средства поддержки именованных параметров, предоставляемые в NamedParameterJdbcTemplate и JdbcTemplate, используют последний подход. Можно передать значения в виде списка примитивных объектов java.util.List. Этот список используется для вставки необходимых плейсхолдеров и передачи значений во время выполнения инструкции.

В дополнение к примитивным значениям в списке значений, можно создать java.util.List из массивов объектов. Этот список может поддерживать несколько выражений, определенных для in выражения, например, select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop')). Для этого, конечно, необходимо, чтобы ваша база данных поддерживала данный синтаксис.

Обработка сложных типов для вызовов хранимых процедур

При вызове хранимых процедур иногда можно использовать сложные типы, специфичные для базы данных. В целях работы с этими типами Spring предлагает SqlReturnType для их обработки, если они возвращаются из вызова хранимой процедуры, и SqlTypeValue, если они передаются в качестве параметра в хранимую процедуру.

Интерфейс SqlReturnType имеет единственный метод (с именем getTypeValue), который должен быть реализован. Этот интерфейс используется как часть объявления параметра SqlOutParameter. В следующем примере показано возвращение значения объекта Oracle под названием STRUCT типа ITEM_TYPE, объявленного пользователем:

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)
        })
        // ...
    }
}

Можно использовать SqlTypeValue для передачи значения объекта Java (например, TestItem) в хранимую процедуру. Интерфейс SqlTypeValue имеет единственный метод (с именем createTypeValue), который нужно реализовать. Активное соединение передается, и вы можете использовать его для создания специфических для базы данных объектов, таких как экземпляры StructDescriptor или экземпляры ArrayDescriptor. В следующем примере создается экземпляр 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)))
    }
}

Теперь можно добавить это значение SqlTypeValue в Map, содержащий входные параметры для вызова хранимой процедуры execute.

Еще одно применение SqlTypeValue – передача массива значений в хранимую процедуру Oracle. Oracle имеет свой собственный внутренний класс ARRAY, который должен использоваться в этом случае, и вы можете использовать SqlTypeValue для создания экземпляра ARRAY из Orcale и заполнения его значениями из ARRAY из Java, как показано в следующем примере:

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