Поширені проблеми з обробкою параметрів та значень даних

Встановлення типу 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 для отримання вмісту CLOB.

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