JDBC

Ключевые интерфейсы:

Driver;
Connection;
Statement;
PreparedStatement;
CallableStatement;
ResultSet;
DriverManager - OCP получает соединение (Connection) через него.

В случае запуска JVM в модульном режиме, JDBC требует подключения в module-info как:

1	requires java.sql;

url для подключения к БД выглядит следующим образом:

jdbc	:	${dbtype}	:	//${host}:${port}/${database}
Протокол всегда JDBC		Протокол соединяет, фактически, имя вендора или продукта БД		Параметры подключения

Получение соединения

Промышленный стандарт рекомендует использовать класс DataSource. Вместо этого, OCP использует класс DriverManager или фабрику для подключения. Ниже рассматривается именно он:

1	Connection conn = DriverManager.getConnection("jdbc:...");

Некоторые вендоры требуют логина и пароля для соединения:

1	Connection conn = DriverManager.getConnection("jdbc:...", "myDbUser", "myDbPass");

PreparedStatement

Призван для:

Производительности. Строит план запроса на уровне БД;
Безопасность. Защищает от SQL-инъекций;
Читабельность. Запрос составляется отдельно; параметры передаются отдельно;
Отложенное использование. Достаточно хранить ссылку на объект с запросом и передать параметры в другом участке кода.

1	try(PreparedStatement pstmt = conn.prepareStatement("...")) { ... }

Тело запроса обязательно, за его указанием необходимо следить.

executeUpdate - выполняет только INSERT, UPDATE, DELETE и подобные типы запросов. Возвращает int - количество фактических изменений (некоторые вендоры могут возвращать собственные коды);
executeQuery - возвращает ResultSet, как результат выполнения SELECT запроса;
execute - выполняет запрос и возвращает boolean флаг, который определяет, является ли результат выполнения ResultSet. Затем в зависимости от boolean можно определить логику:

boolean hasResultSet = pstmt.execute();
if(hasResultSet) {
    try(ResultSet rs = pstmt.getResultSet()) { ... }
} else {
    int amount = pstmt.getUpdateCount();
}

Параметры

Передаются как символ “?”. В случае отсутствия параметра, или указания лишнего - будет выведена ошибка. Основные setter-методы для параметров соответствуют типам. Если указать setObject, то переданному параметру будет передан эквивалент. Для примитива будет найден wrapper-класс, который и будет использоваться:

1	pstmt.setInt((int) 123);

Аналогично:

1	pstmt.setObject((int) 123);

Batch запросы

JDBC API позволяет выполнять batch запросы:

var sql = "...";
try(var pstmt = conn.preparedStatement(sql)) {
    pstmt.setInt(1);
    for(int x=0; i<10; i++) {
        pstmt.setDouble(Double.valueOf(x));
        pstmt.addBatch();
    }
    int[] updates = pstmt.executeBatch();
}

Результатом будет количество измененных строк в каждом запросе, а те, в свою очередь, будут указывать количество выполненных изменений. Количество элементов массива будет эквивалентно количеству выполненных addBatch запросов.

Получение результатов SELECT

Использование ResultSet (тоже является AutoCloseable):

try(ResultSet rs = pstmt.executeQuery("...")) {
    while(rs.next()) {
        Int x = rs.getInt("x");
        String s = rs.getString("s");
        Double d = rs.getDouble(3);
    }
}

Проход по ResultSet осуществляется с помощью курсора. В случае обращения к результату по несуществующей колонке (с не корректным именем или индексом), последует ошибка исполнения.

Как и на вход, ResultSet имеет метод getObject, который возвращает объект произвольного типа, зависящий от типа колонки. Впоследствии этот тип можно проверить стандартными средствами Java (например, instanceof).

CallableStatement

Выполняет хранимую процедуру на стороне БД

1
2
3

try(CallableStatement cstmt = conn.prepareCall("{call my_procedure()}")) {
    try(ResultSet rs = cstmt.executeQuery()) { ... }
}

Передача параметров в процедуру допускает как используя имя, так и по индексу:

1 2	cstmt.setString(1, "val"); cstmt.setString("proc_arg", "val");

При этом, аргумент для запроса так же надо указать:

try(CallableStatement cstmt = conn.prepareCall("{call my_proc_with_args(my_arg=>?)}")) {
    cstmt.setString("my_arg", "value");
    try(ResultSet rs = cstmt.executeQuery()) {
        ...
    }
}

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

var sql = "{?=call procedure_name(?)}";
try(var stmt = conn.prepareCall("")) {
    stmt.registerOutParameter(1, java.sql.Types.INTEGER);
    if(stmt.execute()) {
        int result = stmt.getInt("result_var_name");
    }
}

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

var sql = "{call procedure_name(?)}";
try(var stmt = connection.prepareCall()) {
    stmt.setInt(1, 10);
    stmt.registerOutParameter(1, Types.INTEGER);
    if(pstmt.execute()) {
        int result = stmt.getInt("result_var_name");
    }
}

Если процедура типа OUT, то она может выполнять registerOutParameter; может включать (может и НЕ включать) символы “?=”.
Если процедура INOUT, то она может выполнять registerOutParameter; может включать (может и НЕ включать) символы “?=”; может принимать параметры на вход.

ResultSet

Перед выполнением запроса можно передать параметры модифицирующие ResultSet и то как он будет считываться:

1	connection.prepareStatement(sql, ResultSet.${param}, ResultSet.${param});

Ниже представлены параметры, доступные в ResultSet:

TYPE_FORWARD_ONLY - ResultSet может двигаться в чтении только вперед, вызов ResultSet.previous() будет выдавать ошибку;
TYPE_SCROLL_INSENSITIVE - во время прохода по результату запроса, изменения, сделанные в таблицах в момент считывания, будут проигнорированы;
TYPE_SCROLL_SENSITIVE - обратное к TYPE_SCROLL_INSENSITIVE;
CONCUR_READ_ONLY - изменения в ResultSet не будут применимы;
CONCUR_UPDATABLE - изменения в ResultSet допустимы.

Чтение в обратную сторону

ResultSet позволяет выполнять чтение в обратном направлении, но для этого должны быть выполнены некоторые условия:

драйвер должен позволять это делать;
необходимо получить достаточный объем данных используя setFetchSize;
сдвинуть курсор чтения в конец методом afterLast;
выполнять previous для сдвига курсора в обратном направлении.

Для TYPE_SCROLL_INSENSITIVE, де-факто, выполняется кэширование всех полученных результатов (для этого и выполняется setFetchSize) и ResultSet оперирует этими данными, а не обращается к БД для прочтения каждой следующей строки. Не помогает, в том числе, и работа в изолированной транзакции, т.к. чтение фактически выполняется в небольшой промежуток времени для загрузки в кэш.

Для TYPE_SCROLL_SENSITIVE так же поддерживается возможность чтения в 2 направлениях, но чтение каждой новой строки (в любом направлении) производится на уровне БД. Это накладывает дополнительную нагрузку на БД!

Изменения через ResultSet

Некоторые драйвера подключения к БД поддерживают изменение полученных данных напрямую в ResultSet с последующим подтверждением изменений в БД. За это отвечает ResultSet.CONCUR_UPDATABLE. Если драйвер не поддерживает подобную функцию, будет выброшена ошибка, которую можно избежать предварительно проверив наличие такой функции:

1	boolean supported = DatabaseMetaData.supportsResultSetConcurrency(ResultSet.CONCUR_UPDATABLE);

Пример чтения и изменения:

boolean supportsBackward = connection.getMetaData()
            .supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
boolean supportsInstantUpdate = connection.getMetaData()
    .supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
try (PreparedStatement pstmt = connection.prepareStatement
      ("", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
    pstmt.setInt(1, 1);
    try (ResultSet rs = pstmt.executeQuery()) {
        rs.setFetchSize(100);
        rs.afterLast();
        while (rs.previous()) {
            if (supportsInstantUpdate) {
                int index = rs.getInt(1);
                if(index > 100) {
                    rs.moveToInsertRow();
                    rs.updateString(1, "");
                    rs.updateDouble(2, 1);
                    rs.updateRow();
                    rs.moveToCurrentRow();
                } else {
                    rs.updateString(1, "");
                    rs.updateDouble(2, 1);
                    rs.updateRow();
                }
            } else {
                ...
            }
        }
    }
}

Закрытие соединения

Большое количество классов, работающих с БД реализуют AutoCloseable, а значит их можно вставлять в try-with-resources блоки. Если в рамках 1-го открытого Statement выполняется насколько ResultSet, то все кроме последнего будут закрыты автоматически - т.е. при открытии нового ResultSet, уже открытый будет принудительно закрыт.