Возможности JDBC — второй этап

В предыдущей статье мы только познакомились с JDBC и написали простое приложение, которое позволило нам соединиться с СУБД и получить данные с помощью SQL-запроса. Хоть программа и не очень сложная, но на мой взгляд, мы сделали весьма важный шаг — мы смогли сединиться с базой данных и сделать пусть и простой, но запрос. Все, что мы рассмотрим дальше — это уже более удобные и более профессиональные способы использования JDBC.

Запросы на получение данных и запросы на обновление

SQL-запросы можно условно разделить на две группы:

  1. Получение данных — к ним относится оператор SELECT
  2. Изменение данных — к ним относятся операторы INSERT, UPDATE и DELETE

Для первой группы используется уже знакомый нам метод интерфейса StatementexecuteQuery(). В принципе для начала этого метода вполне достаточно. Он покрывает очень большой процент запросов, которые разрабатываются для реальных систем. Позже мы познакомимся с дополнительными возможностями, но на данных момент советую запомнить — если надо получить данные из таблицы, то executeQuery в подавляющем большинстве случаев будет самым правильным выбором.
Для второй группа запросов (опять же в большинстве случаев) может использоваться другой метод интерфейса StatementexecuteUpdate(). Есл посмотреть документацию, то в отличии от executeQuery() (который возвращает ResultSet) этот метод возвращает целое число, которое говорит сколько строк в таблице было изменено при исполнении вашего запроса.
Например, вы можете оператором DELETE FROM JC_CONTACT удалить ВСЕ строки (посему будьте очень аккуратны). В этом случае метод executeUpdate() вернет количество удаленных строк. В некоторых ситуациях знание о количестве измененных строк бывает удобным для построения алгоритмов работы с данными.
В принципе с этим вопросов можно закончить — главное мы уже увидели. Для выборки данных — executeQuery(). Для изменения данных — executeUpdate().

Разновидности Statement

Самый простой интерфейс Statement мы уже видели. И хотя он вполне пригоден для работы, для сложных запросов он подходит не так хорошо. В некоторых источниках высказывается мнение, что использовать Statement вообще не надо — вместо него подходят более сложные и более функционально насыщенные интерфейсы.

  1. PreparedStatement
  2. CallableStatement

Возникает вполне резонный вопрос — а зачем эти интерфейсы нужны ? Давайте разбираться. Для начала мы рассмотрим интерфейс PreparedStatement и другие возможности JDBC. К интерфейсу CallableStatement обратимся позже — его использование во-первых не так часто встречается, и во-вторых — после всего рассмотренного, про него разговор уже можно делать достаточно коротким.

PreparedStatement

Если честно перевести название, то можно получить что-то вроде «подготовленный оператор». Самым важным здесь является слово «подготовленный». В чем же заключается «подготовленность» ?
Прежде чем мы рассмотрим этот вопрос, предлагаю увидеть достаточно важный с точки зрения удобства момент, который возникает очень часто. Итак, в каком-либо приложении нам надо вставить данные о контакте в таблицу JC_CONTACT. Для этого нам надо подготовить запрос наподобие такого:

На первый взгляд кажется все не так уж сложно и страшно. Надо написать код, который будет «собирать» нужную нам строку из параметров — имя, фамилия, адрес и телефон. Надо только не забыть, что все строковые данные надо «окружить» символом ординарная кавычка. Если мы это делаем в отдельной функции, то получается что-то вроде такого

Мы передаем в функцию в виде параметров имя, фамилию, телефон и адрес и из них составляем строку SQL-запроса. Кавычки немного портят картину, но пока не страшно.
Ок, а что делать с числами ? Их не надо “окружать” кавычками. Опаньки, в одном случае надо кавычки, в другом — не надо. Ситуация усложняется.
Теперь добавим еще одну проблему — а если внутри строки есть ординарная кавычка (и даже не одна) ? Надо предварительно искать такие кавычки и обрабатывать их. Мда-а-а. Как-то неуютно начинаем себя ощущать.
Если теперь прибавить обработку дат, то задача становится совсем «скучной» — надо делать огромное количество работы. С датами вообще неприятно — разные SQL-сервера принимают для дат разные форматы.
Итак, что мы видим ? Если нам надо использовать параметры внутри запроса, то в «ручном» режиме построение запроса становится очень неприятным делом. Причем не просто непрятным — я бы даже сказал «занудным». Надо учитывать огромное количество случаев и это ужасно скучная работа. В основном именно для таких случаев и был предложен интерфейс PreparedStatement.
Этот запрос позволяет вам сделать две вещи:

  1. Заранее подготовить запрос с указанием мест, где будут подставляться параметры
  2. Установить параметры определенного типа и выполнить после этого запрос с уже установленными параметрами

Конструкция для PreparedStatement для нашего варианта установки параметров будет выглядеть вот так:

Как видите, все достаточно несложно.
Во-первых, при написании SQL-запроса на места, куда надо будет подставлять параметры, записываются знаки вопроса — “?”.
Во-вторых — запрос создается через вызов con.prepareStatement()
В-третьих — установка параметров идет через указание номера и значения. Обратите внимание, что номар параметров начинаются с 1, а не с 0, как вы возможно уже привыкли при работе с массивами и коллекциями. Интерфейс PreparedStatement содержит методы для установки строк — setString, для установки чисел — setInt, setLong, setDouble, для установки дат — setDate. И болеесложных типов — это можно увидеть в документации.
В-четвертых — вызов stmt.executeUpdate() выполняется уже без указания строки запроса.
Крайне настоятельно рекомендую “подружиться” с PreparedStatement — это очень эффективный инструмент.

Сгенерированные поля

Теперь я хочу обратить ваше внимание на еще один интеренсый момент. При вставке контакта через оператор INSERT мы не указываем поле CONTACT_ID (посмотрите внимательно на скрипт в конце раздела Установка СУБД PostgreSQL). База данных генерирует это значение автоматически, используя свои возможности.
Для PostgreSQL это достигается путем использования специального объекта — SEQUENCE (последовательность). Вызов nextval у этого объекта каждый раз генерирует следующее число. Это облегчает нам жизнь — мы не должны придумывать как же достичь уникальности ИД контакта — просто используем этот объект. Если в утилите pgAdmin выделить таблицу JC_CONTACT, то можно увидеть точный SQL-скрипт для ее генерации.

Обратим внимание на объявление поля contact_id.

Слово DEFAULT указывает, какое значение будет вставляться в это поле, если для него не будет указано значение. Это следующеее число в последовательности jc_contact_contact_id_seq. Т.е. когда мы выполняем вставку без указание поля CONTACT_ID, то оно генерируется само. Смотрим еще раз внимательно на наш оператор вставки

Как видим, поля CONTACT_ID нет. Но когда мы выбираем данные — оно имеет значение. Мало того — попытка втавки значение NULL в то поле просто невозможно. И дублирование значений тоже невозможно. Все эти ограничение отслеживаются самой СУБД. В общем все достаточно удобно и хорошо. Генерится автоматически, проверяется на уникальность тоже автоматически. Чего еще можно желать.
Код на Java для вставки записи и последующего чтения теперь может быть такой — здесь я уже использовал интерфейс PreparedStatement:

На первый взгляд может показаться, что все замечательно — мы вставили запись и можем даже ее увидеть в следующем операторе SELECT. Там же мы найдем значение поля CONTACT_ID. Но в данном случае мы просто знаем, что только мы вставляли запись и мы можем определить, какая запись вставилась и какое значение поля CONTACT_ID сгенерировалось. Но если операцию вставки делают одновременно десятки, сотни или даже тысячи потоков, то какая запись наша ? Знание ИД записи достаточно важно — например, нам для этого контакта надо будет добавить данные в какую-то другую таблицу ? Или надо послать эту информацию по почте системному администратору ? Да мало ли зачем еще. В общем, иметь возможность узнать, под каким ИД вставилась запись — важно и очень удобно. Можно сформулировать эту проблему даже шире — узнать значения полей, которые вставлялись автоматически.
При всей очевидности необходимости иметь такой механизм, на многих SQL-серверах это делается совершенно разными способами, что очень затрудняет разработку.

К счастью для Java-разработчиков, при создании JDBC этот важный вопрос был продуман и вы можете использовать стандартное решение. Решение строится на двух моментах.
Во-первых, надо указать список полей, значения которых вы хотите получить после выполнения запроса.
Во-вторых, у объекта Statement есть специальный метод получения сгенерированных полей — getGeneratedKeys()
Теперь давайте посмотрим код, котрый демонстрирует этот механизм

Все самое главное находится в методе insert. Мы объявили переменную для нового ИД. После создаем PreparedStatement и в качестве уже двух параметров передаем строку с запросом (как и раньше) и массив имен столбцов, данные которых нам нужны. У нас только одно поле — contact_id — поэтому массив состоит из одного элемента.
ВАЖНО !!!Обращаю ваше внимание на то, что JDBC PostgreSQL различает большие и маленькие буквы в этом месте. Это его особенность. В остальных местах регистр не важен. Почему так — не знаю, не разбирался.

В конце используем код для получения значений для поля contact_id

Полный пример можно оскачать здесь: InsertDb.

Транзакция

В общем виде транзакция — это механизм, который позволяет выполнить несколько запросов на изменение данных как одно целое. Т.е. либо все запросы пройдут, либо ни один. Например, вы вставляете данные о пользоователе в разные таблицы. В этом случае будет плохо, если вы половину таблиц заполните, а вторая половина останется пустой из-за того, что при исполнении какого-то запроса произодет ошибка. Тут уж елибо во все таблицы надо вставить данные, либо совсем никаких изменений быть не должно.
На мой взгляд достаточно очевидное желание. Часто приводят в пример вариант пересылки денег с одного счета на счет — с одного счета денежку сняли, на другой счет зачислили. И несамотря на то, что это две разные операции, они должны пройти либо обе, либо ни одна из них.
Если немного поразмышлять, то все становится еще загадочнее в случае, когда у вас все это происходит не в одном банке в рамках одной базы данных, а в разных банках — само собой там и базы данных тоже разные. Это уже приближается к понятию “распределенная транзакция”, но об этом мы в этом разделе говорить не будем — слишком сложно и глубоко погружаться придется. Хотя такие задачи существуют и программисты имеют механизмы для их решения.
Мы рассмотрим несложный пример использования транзакции в виде вставки несколькоих записей в одну и ту же таблицу, но даже в этом случае мы сможем показать, что либо все несколько записей добавяться, либо ни одна. Давайте смотреть — я специально использовал практически тот же пример, что и в предыдущем разделе — просто добавил цикл для вставки сразу 5 записей и сделал это в рамках одной транзакции, так что все изменения надо смотреть в методе main:

Как я уже говорил всек самое интересное находится в самом начале программы — в методе main

Первый шаг — это выставление свойства соединения autoCommit в false. Это свойство говорит о том, надо ли автоматически помещать исполнение каждого запроса (Statement) как-бы в рамки одной транзакции. Т.е. Получается такая маленькая компактная одноразовая транзакция — сделал запрос и транзакция в рамках этого запроса и началась и закончилась. Если запрос выполнился успешно — транзакция завершилась подтверждением — commit. Выполнился запрос неудачно — транзакция откатывается — rollback. Так вот по умолчанию это свойство стоит в true. Для того, чтобы самим начать управлять транзакциями надо это свойство поставить под свое управление — запретить думать за вас. С этого момента все ваши запросы вам надо будет либо подтвердить — что сделано сразу после цикла путем вызова con.commit();, либо вам надо отменять все ваши запросы — вариант вызова чуть ниже (закомментирован) — con.rollback();.
Я рекомендую сначала запустить прирме как есть — и в конце за счет кода

можно посмотреть, что записи вставились (конечно, если пример успешно завершился).
После этого закомментировать вызов con.commit(); и раскомментировать вызов con.rollback();. Теперь записи не будут вставлены, хотя запросы на вставку будут выполнены.
Вы даже это сможете увидеть — буду выводится строки с CONTACT_ID. А вот новых записей не будет.
Подводя итог раговора о транзакциях. Во-первых, конечно же я очень упрощенно рассказал об этом весьма любопытном, сложном и важном механизме. Во-вторых, логика чередования вызовов commit и rollback ложится на плечи программиста. Использование более сложных пакетов и технологий в большинстве случаев повзоялет сделать эту работу удобнее и легче, но тем не менее алгоритм, логика — все на ваше усмотрение.
Достаточно часто используется конструкция подобная такой:

Эта конструкция достаточно удобная и покрывает большое количество ситуаций, но не факт, что она выручит вас в каком-то сложном случае.

Полный пример можно оскачать здесь: InsertTransactionDb.