Введение в JDBC

1. Обзор

В этой статье мы рассмотрим JDBC (Java Database Connectivity), который представляет собой API для подключения и выполнения запросов к базе данных.

JDBC может работать с любой базой данных при наличии соответствующих драйверов.

2. Драйверы JDBC

Драйвер JDBC — это реализация JDBC API, используемая для подключения к определенному типу базы данных. Существует несколько типов драйверов JDBC:

• Тип 1 — содержит сопоставление с другим API доступа к данным; примером этого является драйвер JDBC-ODBC.
• Тип 2 — это реализация, использующая клиентские библиотеки целевой базы данных; также называется драйвером собственного API
• Тип 3 — использует промежуточное программное обеспечение для преобразования вызовов JDBC в вызовы, специфичные для базы данных; также известный как драйвер сетевого протокола
• Тип 4 — подключение напрямую к базе данных путем преобразования вызовов JDBC в вызовы, специфичные для базы данных; известные как драйверы протокола базы данных или тонкие драйверы,

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

3. Подключение к базе данных

Чтобы подключиться к базе данных, нам просто нужно инициализировать драйвер и открыть соединение с базой данных.

3.1. Регистрация драйвера

В нашем примере мы будем использовать драйвер протокола базы данных типа 4.

Поскольку мы используем базу данных MySQL, нам нужна зависимость mysql-connector-java :

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

Далее зарегистрируем драйвер с помощью метода Class.forName() , который динамически загружает класс драйвера:

Class.forName("com.mysql.cj.jdbc.Driver");

В старых версиях JDBC перед получением соединения нам сначала нужно было инициализировать драйвер JDBC, вызвав метод Class.forName . Начиная с JDBC 4.0 , все драйверы, найденные в пути к классам, загружаются автоматически . Поэтому нам не понадобится эта часть Class.forName в современных средах.

3.2. Создание соединения

Чтобы открыть соединение, мы можем использовать метод getConnection() класса DriverManager . Для этого метода требуется параметр строки URL-адреса подключения :

try (Connection con = DriverManager
  .getConnection("jdbc:mysql://localhost:3306/myDb", "user1", "pass")) {
    // use con here
}

Поскольку Connection является ресурсом AutoCloseable , мы должны использовать его внутри блока try-with-resources .

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

jdbc:mysql://localhost:3306/myDb?user=user1&password=pass

jdbc:postgresql://localhost/myDb

jdbc:hsqldb:mem:myDb

Чтобы подключиться к указанной базе данных myDb , нам нужно будет создать базу данных и пользователя, а также добавить предоставление необходимого доступа:

CREATE DATABASE myDb;
CREATE USER 'user1' IDENTIFIED BY 'pass';
GRANT ALL on myDb.* TO 'user1';

4. Выполнение операторов SQL

Для отправки SQL-инструкций в базу данных мы можем использовать экземпляры типа Statement , PreparedStatement или CallableStatement, которые мы можем получить с помощью объекта Connection .

4.1. Заявление

Интерфейс оператора содержит основные функции для выполнения команд SQL.

Во-первых, давайте создадим объект Statement :

try (Statement stmt = con.createStatement()) {
    // use stmt here
}

Опять же, мы должны работать с операторами внутри блока try-with-resources для автоматического управления ресурсами.

В любом случае, выполнение инструкций SQL может быть выполнено тремя способами:

• executeQuery() для инструкций SELECT
• executeUpdate() для обновления данных или структуры базы данных
• execute() можно использовать в обоих случаях выше, когда результат неизвестен.

Давайте используем метод execute() для добавления таблицы студентов в нашу базу данных:

String tableSql = "CREATE TABLE IF NOT EXISTS employees" 
  + "(emp_id int PRIMARY KEY AUTO_INCREMENT, name varchar(30),"
  + "position varchar(30), salary double)";
stmt.execute(tableSql);

При использовании метода execute() для обновления данных метод stmt.getUpdateCount() возвращает количество затронутых строк.

Если результат равен 0, то либо строки не были затронуты, либо это была команда обновления структуры базы данных.

Если значение равно -1, то команда была запросом SELECT; затем мы можем получить результат, используя stmt.getResultSet() .

Далее добавим запись в нашу таблицу с помощью метода executeUpdate() :

String insertSql = "INSERT INTO employees(name, position, salary)"
  + " VALUES('john', 'developer', 2000)";
stmt.executeUpdate(insertSql);

Метод возвращает количество затронутых строк для команды, которая обновляет строки, или 0 для команды, которая обновляет структуру базы данных.

Мы можем получить записи из таблицы, используя метод executeQuery() , который возвращает объект типа ResultSet :

String selectSql = "SELECT * FROM employees"; 
try (ResultSet resultSet = stmt.executeQuery(selectSql)) {
    // use resultSet here
}

Мы должны обязательно закрыть экземпляры ResultSet после использования. В противном случае мы можем держать базовый курсор открытым гораздо дольше, чем ожидалось. Для этого рекомендуется использовать блок try-with-resources , как в нашем примере выше.

4.2. Подготовленное заявление

Объекты PreparedStatement содержат предварительно скомпилированные последовательности SQL. Они могут иметь один или несколько параметров, обозначенных знаком вопроса.

Давайте создадим PreparedStatement , который обновляет записи в таблице сотрудников на основе заданных параметров:

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
try (PreparedStatement pstmt = con.prepareStatement(updatePositionSql)) {
    // use pstmt here
}

Чтобы добавить параметры в PreparedStatement , мы можем использовать простые сеттеры — setX() — где X — тип параметра, а аргументы метода — порядок и значение параметра:

pstmt.setString(1, "lead developer");
pstmt.setInt(2, 1);

Оператор выполняется одним из трех описанных выше методов: executeQuery(), executeUpdate(), execute() без параметра SQL String :

int rowsAffected = pstmt.executeUpdate();

4.3. CallableStatement

Интерфейс CallableStatement позволяет вызывать хранимые процедуры.

Чтобы создать объект CallableStatement , мы можем использовать метод prepareCall () Connection :

String preparedSql = "{call insertEmployee(?,?,?,?)}";
try (CallableStatement cstmt = con.prepareCall(preparedSql)) {
    // use cstmt here
}

Установка значений входных параметров для хранимой процедуры осуществляется аналогично интерфейсу PreparedStatement с использованием методов setX() :

cstmt.setString(2, "ana");
cstmt.setString(3, "tester");
cstmt.setDouble(4, 2000);

Если у хранимой процедуры есть выходные параметры, их нужно добавить с помощью метода registerOutParameter() :

cstmt.registerOutParameter(1, Types.INTEGER);

Затем давайте выполним оператор и получим возвращаемое значение с помощью соответствующего метода getX() :

cstmt.execute();
int new_id = cstmt.getInt(1);

Например, для работы нам нужно создать хранимую процедуру в нашей базе данных MySql:

delimiter //
CREATE PROCEDURE insertEmployee(OUT emp_id int, 
  IN emp_name varchar(30), IN position varchar(30), IN salary double) 
BEGIN
INSERT INTO employees(name, position,salary) VALUES (emp_name,position,salary);
SET emp_id = LAST_INSERT_ID();
END //
delimiter ;

Приведенная выше процедура insertEmployee вставит новую запись в таблицу сотрудников , используя заданные параметры, и вернет идентификатор новой записи в выходном параметре emp_id .

Чтобы запустить хранимую процедуру из Java, пользователь подключения должен иметь доступ к метаданным хранимой процедуры. Этого можно добиться, предоставив пользователю права на все хранимые процедуры во всех базах данных:

GRANT ALL ON mysql.proc TO 'user1';

Кроме того, мы можем открыть соединение со свойством noAccessToProcedureBodies , установленным в true :

con = DriverManager.getConnection(
  "jdbc:mysql://localhost:3306/myDb?noAccessToProcedureBodies=true", 
  "user1", "pass");

Это сообщит JDBC API, что у пользователя нет прав на чтение метаданных процедуры, поэтому он создаст все параметры как параметры INOUT String .

5. Анализ результатов запроса

После выполнения запроса результат представляется объектом ResultSet , который имеет структуру, аналогичную таблице, со строками и столбцами.

5.1. Интерфейс набора результатов

ResultSet использует метод next () для перехода к следующей строке.

Давайте сначала создадим класс Employee для хранения полученных нами записей:

public class Employee {
    private int id;
    private String name;
    private String position;
    private double salary;
 
    // standard constructor, getters, setters
}

Далее пройдемся по ResultSet и создадим объект Employee для каждой записи:

String selectSql = "SELECT * FROM employees"; 
try (ResultSet resultSet = stmt.executeQuery(selectSql)) {
    List<Employee> employees = new ArrayList<>(); 
    while (resultSet.next()) { 
        Employee emp = new Employee(); 
        emp.setId(resultSet.getInt("emp_id")); 
        emp.setName(resultSet.getString("name")); 
        emp.setPosition(resultSet.getString("position")); 
        emp.setSalary(resultSet.getDouble("salary")); 
        employees.add(emp); 
    }
}

Получить значение для каждой ячейки таблицы можно с помощью методов типа getX( ), где X представляет тип данных ячейки.

Методы getX() можно использовать с параметром int , представляющим порядок ячеек, или параметром String , представляющим имя столбца. Последний вариант предпочтительнее, если мы меняем порядок столбцов в запросе.

5.2. Обновляемый набор результатов

Неявно объект ResultSet может быть пройден только вперед и не может быть изменен.

Если мы хотим использовать ResultSet для обновления данных и обхода их в обоих направлениях, нам нужно создать объект Statement с дополнительными параметрами:

stmt = con.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE, 
  ResultSet.CONCUR_UPDATABLE
);

Для навигации по этому типу ResultSet мы можем использовать один из методов:

• first(), last(), beforeFirst(), beforeLast() — для перехода к первой или последней строке ResultSet или к строке перед ними.
• next(), previous() — для перехода вперед и назад в ResultSet
• getRow() — для получения текущего номера строки
• moveToInsertRow(), moveToCurrentRow() — для перехода к новой пустой строке для вставки и обратно к текущей, если в новой строке
• absolute(int row) – перейти к указанной строке
• относительный (int nrRows) — для перемещения курсора на заданное количество строк

Обновление ResultSet можно выполнить с помощью методов формата updateX() , где X — тип данных ячейки. Эти методы обновляют только объект ResultSet , но не таблицы базы данных.

Чтобы сохранить изменения ResultSet в базе данных, мы должны дополнительно использовать один из методов:

• updateRow() — сохранить изменения текущей строки в базе данных.
• insertRow(), deleteRow() — добавить новую строку или удалить текущую из базы данных
• RefreshRow() — для обновления ResultSet при любых изменениях в базе данных.
• cancelRowUpdates() — для отмены изменений, внесенных в текущую строку.

Давайте рассмотрим пример использования некоторых из этих методов путем обновления данных в таблице сотрудников :

try (Statement updatableStmt = con.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
    try (ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql)) {
        updatableResultSet.moveToInsertRow();
        updatableResultSet.updateString("name", "mark");
        updatableResultSet.updateString("position", "analyst");
        updatableResultSet.updateDouble("salary", 2000);
        updatableResultSet.insertRow();
    }
}

6. Анализ метаданных

JDBC API позволяет искать информацию о базе данных, называемую метаданными.

6.1. Метаданные базы данных

Интерфейс DatabaseMetadata можно использовать для получения общей информации о базе данных, такой как таблицы, хранимые процедуры или диалект SQL.

Давайте кратко рассмотрим, как мы можем получить информацию о таблицах базы данных:

DatabaseMetaData dbmd = con.getMetaData();
ResultSet tablesResultSet = dbmd.getTables(null, null, "%", null);
while (tablesResultSet.next()) {
    LOG.info(tablesResultSet.getString("TABLE_NAME"));
}

6.2. ResultSetMetadata

Этот интерфейс можно использовать для поиска информации об определенном наборе результатов , такой как количество и имя его столбцов:

ResultSetMetaData rsmd = rs.getMetaData();
int nrColumns = rsmd.getColumnCount();

IntStream.range(1, nrColumns).forEach(i -> {
    try {
        LOG.info(rsmd.getColumnName(i));
    } catch (SQLException e) {
        e.printStackTrace();
    }
});

7. Обработка транзакций

По умолчанию каждый оператор SQL фиксируется сразу после его завершения. Однако также возможно управлять транзакциями программно .

Это может быть необходимо в тех случаях, когда мы хотим сохранить согласованность данных, например, когда мы хотим зафиксировать транзакцию только в том случае, если предыдущая транзакция завершилась успешно.

Во-первых, нам нужно установить для свойства autoCommit Connection значение false , а затем использовать методы commit() и rollback() для управления транзакцией.

Давайте добавим второй оператор обновления для столбца зарплаты после обновления столбца должности сотрудника и завернем их оба в транзакцию. Таким образом, зарплата будет обновлена только в том случае, если позиция была успешно обновлена:

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
PreparedStatement pstmt = con.prepareStatement(updatePositionSql);
pstmt.setString(1, "lead developer");
pstmt.setInt(2, 1);

String updateSalarySql = "UPDATE employees SET salary=? WHERE emp_id=?";
PreparedStatement pstmt2 = con.prepareStatement(updateSalarySql);
pstmt.setDouble(1, 3000);
pstmt.setInt(2, 1);

boolean autoCommit = con.getAutoCommit();
try {
    con.setAutoCommit(false);
    pstmt.executeUpdate();
    pstmt2.executeUpdate();
    con.commit();
} catch (SQLException exc) {
    con.rollback();
} finally {
    con.setAutoCommit(autoCommit);
}

Для краткости мы опускаем здесь блоки try-with-resources .

8. Закрытие ресурсов

Когда мы больше не используем его, нам нужно закрыть соединение, чтобы освободить ресурсы базы данных .

Мы можем сделать это с помощью API close() :

con.close();

Однако, если мы используем ресурс в блоке try-with-resources , нам не нужно явно вызывать метод close() , так как блок try-with-resources делает это за нас автоматически.

То же самое относится и к операторам Statement s, PreparedStatement s, CallableStatement s и ResultSet s.

9. Заключение

В этом уроке мы рассмотрели основы работы с JDBC API.

Как всегда, полный исходный код примеров можно найти на GitHub .