JDBC - DmitryGontarenko/usefultricks GitHub Wiki

About

JDBC (Java DataBase Connectivity) - это стандарт взаимодействия Java-приложений с различными СУБД, реализованный в виде пакетов java.sql и javax.sql, входящих в состав Java SE.

Dependency
Для примера будем использовать базу данных H2:

    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>1.4.191</version>
    </dependency>

Connection

Существует два способа установить соединение с БД:

  • Через DriverManager
  • Через DataSource

DriverManager - позволяет подключиться к базе данных по указанному URL.

        Connection connection =  DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", ""); // URL, user, password

Соединение с БД происходит с помощью метода getConnection().
После необходимых операций с БД, соединение нужно закрыть - это можно сделать с помощью метода connection.close(). Закрывать соединение лучше всего в блоке finally или же использовать при работе с БД конструкцию try-with-resources.

Напишем удобный метод для последующего использования:

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "");
    }

Statement

Как только соединение установлено, мы можем взаимодействовать с базой данных. Интерфейсы JDBC Statement и ресширяющие его интерфейсы PreparedStatement и CallableStatement определяют методы и свойства, которые позволяют отправлять SQL команды в БД.

  • Statemen - предназначен для SQL выражений, которые не содержат параметров;
  • PreparedStatement - предназначен для SQL выражений, которые содержат входные параметры;
  • CallableStatement - предназначен для SQL выражений, которые могут получить возвращаемые значение из хранимых процедур.

Все соединения автоматически закроются после закрытия Connection, но для корректного завершения работы, объекты Statement, PreparedStatement и CallableStatement также следует закрывать - close();.

Выполним создание и заполнение таблицы с помощью Statements.
Воспользуемся конструкцией try-with-resources, которая автоматически будет закрывать соединение с БД.

        try(Connection connection = getConnection()) {
            Statement statement = connection.createStatement();
            statement.executeUpdate(
                    "CREATE TABLE Person (id int primary key auto_increment, name varchar(64));");
            statement.executeUpdate(
                    "INSERT INTO Person VALUES (1, 'Sarah');");
        }

Метод executeUpdate() Используется для операций INSERT, UPDATE и DELETE. Он возвращает количество строк, затронутых SQL операцией.

PreparedStatement
С помощью PreparedStatement выполним запрос с параметрами:

            PreparedStatement preparedStatement = connection.prepareStatement(
                    "SELECT * FROM Person WHERE name = ?");
            preparedStatement.setString(1, "Sarah");
            preparedStatement.execute();

JDBC не поддерживает именованные параметры для PreparedStatement, поэтому сами параметры указываются знаками вопроса, а обращаться к ним возможно только по индексу (начиная с 1).
Метод execute() выполняет SQL операцию и возвращает true, если можно получить объект ResultSet.
Что бы получить результат запроса, воспользуемся объектов ResultSet.

CallableStatement
CallableStatement служит для получения хранимый процедуры.
В отличии от объекта PreparedStatement, который использует только IN параметр, CallableStatement может использовать IN, OUT и INOUT параметры.
IN параметр устанавливается с помощью метода setXXX().
OUT и INOUT параметры устанавливаются с помощью метода registerOutParameter(), атрибутом которого будет тип, ожидаемый от процедуры.

Рассмотрим на примере.
Представим, что у нас есть хранимая процедура, которая называется getSum, она принимает на вход два параметра и возвращает результат их сложения.

            CallableStatement callableStatement = connection.prepareCall("{? = call getSum(?, ?)}");
            callableStatement.setInt(2, 5); // устанавливаем 1й параметр
            callableStatement.setInt(3, 5); // устанавливаем 2й параметр
            callableStatement.registerOutParameter(1, Types.INTEGER); // устанавливаем тип возврата int

            System.out.println(callableStatement.getInt(1)); // получаем результат 10

В данном SQL запросе используются escape синтаксис. Для автопроцедуры, которая принимает и возвращает значение используется формат {? = call my_procedure(?)}.

ResultSet

Когда мы выполняем SELECT запросы, они возвращают табличные данные. Объектом хранения таких данных является ResultSet - он содержит методы для получения и манипуляции результатами выполненных запросов, получаемых из метода executeQuery() или интерфейса Statement.
Он работает как итератор, что позволит перемещаться по его данным.
Объект ResultSet имеет курсор/указатель, который указывает на текущую строку. Первоначально этот курсор располагается перед первой строкой.
resultset_cursor

Рассмотрим на примере:

            // получение результатов запроса от экземпляра объекта Statement
            ResultSet resultSet = preparedStatement.getResultSet();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name")); // Sarah
            }

            // получение результатов запроса с помощью метода executeQuery()
            ResultSet resultSet1 = statement.executeQuery("SELECT * FROM Person");
            while (resultSet1.next()) {
                System.out.println(resultSet1.getString("name")); // Sarah
            }

Метод executeQuery() предназначен для получения результатов операции SELECT.

Доступ к значениям ResultSet
В примере мы получили строковое значение Sarah по имени столбца name.
Помимо getString() существует еще множество get-методов для получения различных типов - boolean, byte, short, int, long, float, double, BigDecimal, Date, Time, Timestamp, Clob, Blob и т.д.

Существует две версии для каждого из возможных get-методов, рассмотрим на примере типа int:

  • int getInt(String columnName) - возвращает значение по имени столбца;
  • int getInt(int columnIndex) - возвращает индекс столбца (начиная с 1) текущей строки.

Попробуем с помощью этих двух методов получить данные:

            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
            while (resultSet.next()) {
                System.out.println(resultSet.getString(2)); // Sarah
                System.out.println(resultSet.getString("name")); // Sarah
            }

Такой код вернул нам одинаковый результат, т.к. таблица Person имеет два столбца - id и name, следовательно, у столбца name индекс равен 2.
Далее мы уже конкретно указали имя столбца - name.

Обновление ResultSet
Интерфейс ResultSet содержит набор методов обновления для обновления данных.
Как и в случае с get-методами, для каждого типа данных есть два метода обновления, рассмотрим их на примере типа String:

  • updateString(String columnLabel, String x) - изменяет значение в указанном столбце на x;
  • updateString(int columnIndex, String x) изменяет значение в указанном по индексу столбце на x.

Помимо updateString() существует еще множество методов обновления различных типов - boolean, byte, short, int, long, float, double, BigDecimal, Date, Time, Timestamp, Clob, Blob и т.д.

Важно учесть, что обновление строки изменяет столбцы текущей строки только в объекте ResultSet, но не в самой БД. Чтобы обновить изменения строки в базе данных, нужно вызвать один из следующих методов:

  • void updateRow() - обновляет текущую строку в базе данных текущим значением;
  • void deleteRow() - удаляет текущую строку из базы данных;
  • void refreshRow() - обновляет данные в ResultSet, чтобы отразить любые недавние изменения в базе данных;
  • void insertRow() - вставляет новую строку в базу данных. Этот метод перемещает курсор на специальную строку вставки, это некий буфер, в котором новая строка может быть "построена" для обновления с помощью методов обновления.

Рассмотрим на примере.
Создадим таблицу и заполним ее:

        try (Connection connection = getConnection()) {
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            statement.executeUpdate(
                    "CREATE TABLE Person (id int primary key auto_increment, name varchar(64));");
            statement.executeUpdate(
                    "INSERT INTO Person VALUES (1, 'Sarah'), (2, 'Peter');");

Обновим строку с помощью метода updateRow():

            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
            while (resultSet.next()) {
                System.out.println(resultSet.getString(2)); // получаем первичный результат - Sarah, Peter
                String newName = "new_" + resultSet.getString("name"); // получаем имя с приставкой new_
                resultSet.updateString("name", newName); // обновляем значение столбца name новым именем
                resultSet.updateRow(); // применяем обновление для строки
                System.out.println(resultSet.getString(2)); // получаем результат после обновления - new_Sarah, new_Peter
            }

Вставим новую строку с помощью метода insertRow():

            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
            resultSet.moveToInsertRow(); // перемещаем курсор на строку вставки 
            resultSet.updateInt("id", 3); // заполняем идентификатор
            resultSet.updateString("name", "Kate"); // заполняем имя
            resultSet.insertRow(); // вставляем новую строку
            resultSet.moveToCurrentRow(); // перемещаем курсор на его обычную (запомненную) позицию

            // Строка должна быть успешно вставлена, но т.к. мы вставили новую строку,
            // а не обновили существующую, для просмотра обновленных данных таблицы
            // нам необходимо сделать новый запрос к БД. 
            resultSet.close();
            ResultSet newResultSet = statement.executeQuery("SELECT * FROM Person");
            while (newResultSet.next()) {
                System.out.println(newResultSet.getString(2)); // Sarah, Peter, Kate
            }

Удаляем строку с помощью метода deleteRow():

            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person"); // имеем 2 строки
            resultSet.last(); // перемещаем курсор на последнюю строку
            resultSet.deleteRow(); // удаляем строку
            resultSet.beforeFirst(); // перемещаем курсор в начало

            // Аналогично предыдущему примеру, для просмотра результата
            // открываем новое соединение
            resultSet.close();
            ResultSet newResultSet = statement.executeQuery("SELECT * FROM Person");
            while (newResultSet.next()) {
                System.out.println(newResultSet.getString(2)); // Sarah
            }

Свойства ResultSet
Объект ResultSet можно получить из результатов запросов типа Statement, PreparedStatement или CallableStatement.
При создании SQL запросов из перечисленных типов, можно задать характеристики для ResultSet, например при создании объекта Statement:

  • Statement createStatement(int resultSetType, int resultSetConcurrency);
  • Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability).

Первый атрибут resultSetType может иметь следующие значения:
ResultSet.TYPE_FORWARD_ONLY - курсор может двигаться только вперед. Установлен по умолчанию.
ResultSet.TYPE_SCROLL_INSENSITIVE - курсор может двигать вперед и назад, и набор результатов не чувствителен к изменениям, внесенным другими в базу данных, которые происходят после создания объекта ResultSet.
ResultSet.TYPE_SCROLL_SENSITIVE - курсор может двигать вперед и назад, и набор результатов чувствителен к изменениям, внесенным другими в базу данных, которые происходят после создания объекта ResultSet.

Второй атрибут resultSetConcurrency может иметь следующие значения:
ResultSet.CONCUR_READ_ONLY - создает объект ResultSet только для чтения. Установлен по умолчанию.
ResultSet.CONCUR_UPDATABLE - создает объект ResultSet с возможностью изменения.

Третий атрибут resultSetHoldability может иметь следующие значения:
ResultSet.CLOSE_CURSORS_AT_COMMIT - все экземпляры ResultSet закрываются при вызове метода connection.commit() для соединения, создавшего ResultSet.
ResultSet.HOLD_CURSORS_OVER_COMMIT - все экземпляры ResultSet остаются открытыми при вызове метода connection.commit() для соединения, создавшего ResultSet.

Рассмотрим на примере:

            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ...
            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
            resultSet.last(); // двигаем курсор
            resultSet.deleteRow(); // удаляем строку

Установив в качестве атрибутов параметры TYPE_SCROLL_INSENSITIVE и CONCUR_UPDATABLE мы получили возможность двигать курсор в наборе результатов, а также вносить изменения в изначальный набор результатов.

Навигация по ResultSet
Интерфейс ResultSet имеет ряд методов для перемещения курсора:
void beforeFirst() - перемещает курсор прямо перед первой строкой;
void afterLast() - перемещает курсор сразу после первой строкой;
void first() - перемещает курсор на первую строку;
void last() - перемещает курсор на последнюю строку;
boolean next() - перемещает курсор на одну строку вперед;
boolean previous() - перемещает курсор на одну строку назад;
boolean absolute(int row) - перемещает курсор к указанному номеру строки;
boolean relative(int row) - перемещает курсор к указанному номеру строки относительно положению курсора;

void moveToInsertRow() - перемещает курсор на специальную строку вставки. Текущее положение курсора запоминается;
void moveToCurrentRow() - перемещает курсор на на его обычную (запомненную) позицию;

Проверки:
boolean isBeforeFirst - находится ли курсор перед первой строкой;
boolean isAfterLast - находится ли курсор за последней строкой;
boolean isFirst - находится ли курсор на первой строке;
boolean isLast - находится ли курсор на последней строке;
int getRow() - получить номер строки, где находится курсор.

Рассмотрим на примере:

        try (Connection connection = getConnection()) {
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            statement.executeUpdate(
                    "CREATE TABLE Person (id int primary key auto_increment, name varchar(64));");
            statement.executeUpdate(
                    "INSERT INTO Person VALUES (1, 'Sarah'), (2, 'Peter'), (3, 'Kate');");

            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
            System.out.println(resultSet.isBeforeFirst()); // true
            resultSet.next(); // перемещаем курсор на одну строку вперед
            resultSet.relative(2); // перемещаем курсор еще на две строки
            System.out.println(resultSet.getRow()); // 3
            System.out.println(resultSet.isLast()); // true
            System.out.println(resultSet.getString("name")); // Kate
        }

Transaction

По умолчанию JDBC-соединение находится в режиме автоматической транзакции (фиксации изменений).
Автоматическая транзакция означает, что каждая отдельная SQL операция рассматривается как транзакция и автоматически фиксируется сразу после ее выполнения.
Транзакции позволяют контролировать, когда изменения применяются к базе данных. Транзакция может обрабатывать одну SQL операцию или группу SQL операций как одну логическую единицу, и в случае сбоя любой операции происходит откат всей транзакции.

Чтобы разрешить возможность ручной транзакции, нужно отключить режим автоматической фиксации - connection.setAutoCommit(false).
Зафиксировать внесенные изменения можно с помощью метода connection.commit().
Откатить изменения можно с помощью метода connection.rollback().

Рассмотрим на примере:

        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();

            connection.setAutoCommit(false); // отключаем авто-транзакцию
            statement = connection.createStatement();
            statement.executeUpdate(
                    "CREATE TABLE Person (id int primary key auto_increment, name varchar(64));");
            statement.executeUpdate(
                    "INSERT INTO Person VALUES (1, 'Sarah');");
            connection.commit(); // применяем транзакцию

            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
            while(resultSet.next()) {
                System.out.println(resultSet.getString("name")); // Sarah
            }

            // закрываем соединения
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException se) {
            try { if (connection != null) { connection.rollback(); } // откатываем изменения
            } catch (SQLException seRollback) { se.printStackTrace(); }
        } finally {
            try { if (statement != null) { statement.close(); }
            } catch (SQLException seStatement) { }
            try { if (connection != null) { connection.close(); }
            } catch (SQLException eConnection) { }
        }
    }

Важно помнить, что connection.close(), в зависимости от БД, может откатить или зафиксировать транзакцию автоматически. Поэтому настоятельно рекомендуется, что бы приложение явно зафиксировало или откатило активную транзакцию до закрытия соединения.

Точки сохранения
Интерфейс Savepoint может дать дополнительный транзакционный контроль.
При установке точки сохранения, определяется логическая точка отката в транзакции. Если после точки сохранения возникает ошибка, можно использовать метод отката транзакций и откатить все изменения, сделанные после точки сохранения.
Объект Connection имеет несколько методов для работы с объектом Savepoint:

  • Savepoint setSavepoint() - создание точки сохранения;
  • void releaseSavepoint(Savepoint savepoint) - удаление точки сохранения;
  • void rollback(Savepoint savepoint) - откатывает изменения до точки сохранения.

Рассмотрим пример:

            Savepoint savepoint = connection.setSavepoint(); // создаем точку сохранения
            // выполняем какие-либо SQL операции
            connection.rollback(savepoint); // откатываем транзакцию то точки сохранения

Batch Processing

Пакетная обработка позволяет группировать связанные SQL операторы в пакет и отправлять их одним вызовом в базу данных.
Отправляя несколько SQL операторов в базу данных одновременно, снижается объем обращений к БД и тем самым повышается производительность.

Рассмотрим методы для работы с пакетами:

  • Драйверы JDBC может не поддержимать функцию пакетной вставки. Что бы проверить это, необходимо использовать метод supportsBatchUpdates() объекта DatabaseMetaData.
  • void addBatch(String sql) - метод предназначен для добавления SQL операторов в пакет. Он может быть вызван для объектов Statement, PreparedStatement и CallableStatement;
  • int[] executeBatch() - метод выполняет передачу всех существующих операторов в базу данных. Возвращает массив целых чисел, который содержит счетчик обновления для каждого из элементов пакета;
  • void clearBatch() - удаляет все ранее добавленные пакеты.

Рассмотрим на примере:

        try {
            connection = getConnection();
            connection.setAutoCommit(false); // отключаем авто-транзакции
            statement = connection.createStatement();

            // проверяем, поддерживает ли драйвер БД пакетное обновление
            DatabaseMetaData metaData = connection.getMetaData();
            boolean isBatchUpdatable = metaData.supportsBatchUpdates();
            if (isBatchUpdatable) {
                statement.addBatch("CREATE TABLE Person (id int primary key auto_increment, name varchar(64));");
                statement.addBatch("INSERT INTO Person (id, name) VALUES (1, 'Sarah');");
                statement.executeBatch();
            }
            connection.commit();

            ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
            while(resultSet.next()) {
                System.out.println(resultSet.getString("name")); // Sarah
            }
        }

В данном примере мы не производим очистку пакетов statement.clearBatch(), т.к. в документации написано, что вызов statement.executeBatch() автоматически очищает набор пакетов у объектов Statement.
Calling the method executeBatch closes the calling Statement object’s current result set if one is open. The statement’s batch is reset to empty once executeBatch returns.

Так же можно заметить, что в примере выключена автоматическая фиксация изменений connection.setAutoCommit(false). Так как согласно спецификации JDBC точное поведение пакетного выполнения с включенной автоматической фиксацией зависит от базы данных и ее драйвера, и лучше не делать предположений о точном поведении и отключать авто-фиксацию при использовании пакетной обработки.
The commit behavior of executeBatch is always implementation-defined when an error occurs and auto-commit is true.

Пакетная обработка с объектом PreparedStatement
Пакетная обработка с объектом PreparedStatement немного отличается от Statement.
Рассмотрим на примере:

        try {
            connection = getConnection();
            connection.setAutoCommit(false); // отключаем авто-транзакции
            statement = connection.createStatement();

            // проверяем, поддерживает ли драйвера H2 пакетное обновление
            DatabaseMetaData metaData = connection.getMetaData();
            boolean isBatchUpdatable = metaData.supportsBatchUpdates();
            if (isBatchUpdatable) {
                statement.executeUpdate("CREATE TABLE Person (id int primary key auto_increment, name varchar(64));");

                PreparedStatement preparedStatement = connection.prepareStatement(
                        "INSERT INTO Person (id, name) VALUES (?, ?)");
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, "Sarah");
                preparedStatement.addBatch();

                preparedStatement.setInt(1, 2);
                preparedStatement.setString(2, "Peter");
                preparedStatement.addBatch();

                preparedStatement.executeBatch();

                ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
                while(resultSet.next()) {
                    System.out.println(resultSet.getString("name")); // Sarah, Peter
                }
            }
            connection.commit();
        }

Resources

Tutorialspoint. Learn JDBC

⚠️ **GitHub.com Fallback** ⚠️