Хранимые процедуры для Java-программистов

Хранимые процедуры для Java-программистов

В этой статье мы будем рассматривать, каким образом можно использовать хранимые процедуры в системах управления базами данных (СУБД). Мы рассмотрим не только самые базовые и общие возможности хранимых процедур, но и некоторые дополнительные — например, возможность возвращения объектов Result-Set. Подразумевается, что вы достаточно хорошо знакомы с принципами работы СУБД и JDBC API. При этом от вас не требуется никакого опыта использования хранимых процедур.

Хранимые процедуры — это программы, которые располагаются и выполняются в пределах сервера баз данных. Такую процедуру можно легко вызвать из Java-класса с помощью специального синтаксиса. При вызове такой процедуры необходимо указать ее имя и определить список параметров. Имя и список параметров посылаются по JDBC-соединению в СУБД, которая выполняет вызываемую процедуру и возвращает результат (если таковой имеется) обратно, используя это же соединение.

Использование хранимых процедур имеет ряд значительных преимуществ перед использованием серверов на основе EJB-компонентов и/или CORBA. Разница в том, что возможность использования хранимых процедур обычно уже предусмотрена разработчиками конкретной СУБД, и поэтому за данную возможность вам не нужно платить дополнительно, тогда как большинство серверов приложений стоят сегодня больших денег. Однако вы выигрываете не только в стоимости лицензии. Время, которое занимает администрирование и написание кода для серверов приложений, и нарастающая сложность клиентских приложений, которые зависят от них, может с лихвой окупиться возможностями вашей СУБД.

Вы можете писать свои хранимые процедуры на Java, Python, Perl или C, но большинство из них обычно пишутся на языке, специфичном для конкретной СУБД. Oracle использует PL/SQL, PostgreSQL — pl/pgsql, а DB2 использует Procedural SQL. Эти языки, в общем, очень похожи друг на друга. Их взаимозаменяемость на самом деле ничуть не сложнее, чем заменяемость, скажем, сессионного бина (Session Bean) одной версии спецификации EJB от Sun на другую. Вдобавок языки для написания хранимых процедур спроектированы таким образом, что в них можно легко встраивать обычные SQL-выражения. Благодаря этому они куда лучше выражают механизмы работы баз данных, чем, к примеру, Java или C.

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

Использование хранимых процедур
JDBC поддерживает вызов хранимых процедур с помощью класса CallableStatement. Этот класс фактически является подклассом класса PreparedSta-tement. Представим, что у нас есть база данных поэтов. В базе данных содержится хранимая процедура для задания возраста поэта во время его смерти (т.е. во сколько лет умер тот или иной поэт). Далее приведен пример вызова хранимой процедуры для внесения в базу данных информации о старом алкоголике Дилане Томасе:

try { int age = 39; String poetName = "dylan thomas"; CallableStatement proc = conne
ction.prepareCall(" { call set_death_age(?, ?) }"); proc.setString(1, poetName); proc.setI
nt(2, age); cs.execute(); } catch (SQLException e) { // .... }

Строка, которая подается в качестве параметра методу prepareCall(), — это спецификация вызова процедуры. Она определяет имя вызываемой процедуры и символы "?", которые определяют необходимые параметры. Интеграция с JDBC — это огромное достоинство для хранимых процедур, поскольку для того, чтобы вызывать ее из вашего приложения, не нужно изменять классы или использовать какие-либо конфигурационные файлы. Все, что нужно, — это выбрать подходящий JDBC-драйвер для вашей СУБД.
Итак, при выполнении приведенного выше кода вызывается процедура базы данных. В этом примере мы не пытаемся получить какой бы то ни было результат, поскольку его просто не будет. Узнать, была ли процедура выполнена успешно, либо возникла какая-то внештатная ситуация, можно с помощью выбрасываемого в этом случае исключения. Ошибка может проявиться в двух ипостасях: либо непосредственно при выполнении процедуры (например, когда тип одного из переданных параметров не соответствует типу, ожидаемому процедурой), либо на уровне приложения (например, выбрасываемое исключение, сообщающее о том, что "Dylan Thomas" не найден в базе данных поэтов).

Совмещаем SQL-выражения и процедуры
Отображать Java-объекты в записи SQL-таблиц достаточно просто, но для этого обычно необходимо выполнить несколько SQL-выражений. Например, нужно выполнить SELECT, чтобы найти идентификатор (ID) нужного ряда таблицы (записи), после чего выполнить INSERT, чтобы внести данные в ряд таблицы с определенным ID. Однако в схеме с более высокой нормализацией необходимо выполнить обновление (UPDATE) множества таблиц базы данных. Это значит, что нужно выполнить намного больше SQL-выражений. И поэтому Java-код может сильно разрастись, а нагрузка на сеть будет расти при добавлении каждого нового такого выражения.
Вынесение же всех этих SQL-выражений в одну хранимую процедуру очень сильно облегчит вам жизнь. При этом вы делаете только один сетевой запрос на вызов процедуры, а не на вызов каждого из выражений. Все эти действующие SQL-операторы будут иметь место в базе данных. Более того, языки для написания хранимых процедур — например, PL/SQL — позволяют писать более натуральный SQL-код, чем это можно сделать с помощью Java. Ниже следует вариант хранимой процедуры, о которой говорилось ранее, записанной с помощью языка Oracle PL/SQL:

create procedure set_death_age (poet VARCHAR2, poet_age NUMBER) poet_id NUMBER; begin SELECT i
d INTO poet_id FROM poets WHERE name = poet; INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet
_age); end set_death_age;

Необычная реализация, не правда ли? Готов поспорить, что многие ожидали увидеть обновление таблицы poets с помощью UPDATE-выражения. Это наглядный пример того, насколько проще можно реализовать некоторые вещи с помощью хранимых процедур. Понятно, что текущая реализация процедуры set_de-ath_age не оптимизирована должным образом. Однако это не окажет никакого влияния на Java-код, поскольку он не зависит от схемы реализации базы данных. С помощью Java-кода мы всего-навсего вызываем процедуру. Поэтому мы можем внести необходимые изменения в схему базы данных и в саму процедуру для увеличения производительности позже. При этом Java-код редактировать не придется.
Вот Java-метод, который вызывает нашу хранимую процедуру:

public static void setDeath Age(Poet dyingBard, int age) throws SQLException { Connection con 
= null; CallableStatement proc = null; try { con = connectionPool. getConnection(); proc = con.prepa
reCall("{ call set_death_age(?, ?) }"); proc.setString(1, dying Bard.getName()); proc.setI
nt(2, age); proc.execute(); } finally { try { proc.close(); } catch (SQLException e) {} con.close();
 } }

Использование статических методов, как здесь, — очень хороший способ обеспечить надежность дальнейшей эксплуатации. Плюс к этому, такой подход делает код для вызова хранимых процедур очень простым и шаблонным. Если, к примеру, вам нужно вызывать много хранимых процедур, вы можете просто копировать код приведенного выше метода и путем внесения незначительных изменений получать метод для вызова очередной процедуры. А поскольку этот код очень стереотипный, вы вообще можете написать собственный скрипт специально для вызова хранимых процедур.

Функции
Хранимые процедуры могут возвращать значения. Это осуществляется с помощью метода getResultSet() класса Callable-Statement, который получает возвращенное значение. Когда хранимая процедура возвращает значение, необходимо с помощью метода registerOutPara-meter() сообщить JDBC-драйверу, какого типа данные ожидаются в качестве возвращаемого значения. Мало того, необходимо также изменить реализацию хранимой процедуры и явно указать, какой тип данных эта процедура будет возвращать.
Не будем отходить от нашего старого примера и на этот раз напишем процедуру, с помощью которой мы сможем узнать, например, сколько было лет Дилану Томасу, когда он умер. На этот раз хранимая процедура реализована с помощью pl/pgsql (PostgreSQL):

create function snuffed_it_when (VARCHAR) returns integer ' declare poet_id NUMBER; poet_age N
UMBER; begin -- сперва получаем идентификатор, связанный с поэтом. SELECT id INTO poet_id FROM poets
 WHERE name = $1; -- получаем и возвращаем его возраст. SELECT age INTO poet_age FROM deaths WHERE m
ort_id = poet_id; return age; end; ' language 'pl/pgsql';

Между делом заметьте, что в pl/pgsql для передачи параметров используется синтаксис $n, который используется как в Unix-, так и в DOS-скриптах. Также обратите внимание на возможность встраивать комментарии; это еще одно преимущество над Java. Написание подобных комментариев, конечно, возможно и в Java, однако для этого понадобится комментарии заключать в объектах класса String, а это сделает код нечитабельным и бессвязным.
Далее идет код на Java, вызывающий эту процедуру:

connection.setAutoCommit(false); CallableStatement proc = connection.prepareCall("{ ? = c
all snuffed_it_when(?) }"); proc.registerOutParameter(1, Types.INTEGER); proc.setString(2, poet
Name); cs.execute(); int age = proc.getInt(2);

Что же случится, если неправильно задать тип возвращаемого значения? В этом случае будет выброшено исключение RuntimeException, как и в случае использования метода класса ResultSet для получения значения не того типа.

Комплексные возвращаемые значения
У многих людей знания по хранимым процедурам заключаются в том, что мы успели обсудить выше. И все. Если бы это было все, на что способны хранимые процедуры, то они не смогли бы заменить другие механизмы удаленного выполнения каких-либо действий. На самом деле хранимые процедуры куда более мощные, чем это может показаться на первый взгляд. Когда вы выполняете любой SQL-запрос, СУБД создает объект базы данных, который принято называть курсором (cursor). Он используется для итеративного изъятия записей (рядов таблицы), возвращаемых этим запросом. Объект класса ResultSet — это представление такого курсора на момент времени. Благодаря этому вы можете без участия базы данных проходить по содержимому ResultSet.
Некоторые СУБД позволяют возвращать ссылки на курсоры, возвращаемые при вызове хранимых процедур. Спецификация JDBC этого не поддерживает, но JDBC-драйверы от Oracle, PostgreSQL и DB2 имеют встроенную возможность преобразования указателя на курсор в объект класса ResultSet.
В качестве следующего примера — возможность выбора списка поэтов, которые так и не достигли пенсионного возраста (60 лет). Процедура для выполнения следующей выборки приведена ниже. Она будет возвращать курсор. Язык используем все тот же (PostgreSQL pl/pgsql):

create procedure list_early_ deaths () return refcursor as ' declare toesup refcursor; begin o
pen toesup for SELECT poets.name, deaths.age FROM poets, deaths WHERE poets.id = deaths. mort_id AND
 deaths.age < 60; return toesup; end; ' language 'plpgsql';

Далее следует Java-метод, который вызывает эту процедуру, получает результат и выводит полученные записи в объект PrintWriter:

static void sendEarlyDeaths (PrintWriter out) { Connection con = null; CallableStatement toesU
p = null; try { con = ConnectionPool.get Connection(); // для PostgreSQL сначала нужно создать транз
акцию (AutoCommit == false)... con.setAutoCommit(false); // Настраиваем вызов. CallableStatement toe
sUp = connection.prepareCall(" { ? = call list_early_deaths () }"); toesUp.registerOutPara
-meter(1, Types.OTHER); getResults.execute(); ResultSet rs = (Result Set) getResults.getObject(1); w
hile (rs.next()) { String name = rs.get String(1); int age = rs.getInt(2); out.println(name + "
 was " + age + " years old."); } rs.close(); } catch (SQLException e) { // Мы должны 
защитить эти вызовы. toesUp.close(); con.close(); } }

Поскольку возвращение курсоров напрямую не предусмотрено в спецификации JDBC, мы используем Types. OTHER для того, чтобы объявить имя возвращаемого процедурой типа и уже после получить результат из вызова с помощью метода getObject().
Java-метод, вызывающий процедуру, является очень хорошим примером маппинга (mapping). Маппинг — это способ абстрагирования операций на набор записей (к примеру). Вместо того чтобы возвращать сам набор из этой процедуры, мы можем просто передать операцию, которая с этим набором будет что-то делать. В этом случае наша операция состоит в печати содержимого ResultSet на поток вывода. Вот пример переработанного метода:

public class ProcessPoet Deaths { public abstract void send Death(String name, int age); } sta
tic void mapEarlyDeaths (ProcessPoetDeaths mapper) { Connection con = null; CallableStatement toesUp
 = null; try { con = ConnectionPool. getConnection(); con.setAutoCommit(false); CallableStatement to
esUp = connection.prepareCall(" {? = call list_early_deaths () }"); toesUp.registerOutPara
-meter(1, Types.OTHER); getResults.execute(); ResultSet rs = (Result Set) getResults.getObject(1); w
hile (rs.next()) { String name = rs.get String(1); int age = rs.getInt(2); mapper.sendDeath(name, ag
e); } rs.close(); } catch (SQLException e) { toesUp.close(); con.close(); } }

Это позволяет выполнять произвольные операции над данными объекта ResultSet без необходимости изменять или дублировать код метода, который получает этот объект ResultSet. И это очень важно. Если мы захотим, то можем переписать метод sendEarlyDeaths:

static void sendEarly Deaths (final PrintWriter out) { ProcessPoetDeaths myMapper = new Proces
sPoetDeaths() { public void sendDeath (String name, int age) { out.println(name + " was " 
+ age + " years old."); } }; mapEarlyDeaths(myMapper); }

Этот метод вызывает mapEarlyDeaths, которому передает анонимный экземпляр класса ProcessPoetDeaths. Этот экземпляр класса реализует метод sendDeath, который выводит информацию о поэтах в поток вывода, как и в предыдущем примере. Конечно, этот прием не является частью спецификации хранимых процедур, но иногда очень нужно совместить возможность возвращать объекты ResultSet и хранимые процедуры. В итоге мы имеем удивительно мощный инструмент.

Резюме
Хранимые процедуры помогают достигнуть логического разделения вашего кода, что почти всегда крайне необходимо и полезно. Достоинства такого разделения в т.ч. следующие:
• Приложения могут строиться быстро, с использованием схемы, которая растет и улучшается вместе с приложением.
• Схема может меняться в любое время, и эти изменения не окажут никакого влияния на объекты Java; кроме того, закончив разрабатывать приложение, мы уже на этом этапе можем спроектировать хорошую схему.
• Процедуры дают возможность упрощать сложные SQL-запросы.
• Для написания хранимых процедур имеются даже лучшие средства и инструментарий, чем для написания SQL в Java (большинство редакторов поддерживают подсветку синтаксиса).
• Хранимые процедуры можно тестировать из любой командной строки SQL, что делает процесс отладки простым и понятным.
Не все базы данных поддерживают хранимые процедуры. Однако существует большое количество добротных реализаций как бесплатных open-source, так и не совсем бесплатных. Так что переносимость — это не проблема. Oracle, PostgreSQL и DB2 имеют очень схожие языки для написания хранимых процедур, которые хорошо поддерживаются многими on-line-сообществами.
Существует много широко распространенных инструментов для работы с хранимыми процедурами. Среди них — редакторы, отладчики, среды разработки (IDE), как, например, TOAD или TORA, которые представляют собой великолепную среду для написания и сопровождения PL/SQL и pl/pgsql.
Хранимые процедуры наверняка несколько прибавят весу вашему коду, но это не идет ни в какое сравнение с тем, что вам нужно будет предпринять в случае использования серверов приложений. Если ваш код достаточно сложный, чтобы появилась необходимость в использовании СУБД — можете смело применять хранимые процедуры. Не пожалеете.

Ресурсы
Спецификация JDBC — http://jcp.org/en/jsr/detail?id=221
PostgreSQL — http://www.postgresql.org/
Oracle Database Server — http://www.oracle.com/database/
IBM DB2 — http://www.ibm.com/db2/

По материалам Nic Ferrier
Алексей Литвинюк, litvinuke@tut.by