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

Добавлено : 7 Mar 2009, 07:19

В этой статье мы будем рассматривать, каким образом можно использовать хранимые процедуры в системах управления базами данных (СУБД). Мы рассмотрим не только самые базовые и общие возможности хранимых процедур, но и некоторые дополнительные, например, возможность возвращения объектов ResultSet. Подразумевается, что вы достаточно хорошо знакомы с принципами работы СУБД и 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. Этот класс является фактическим подклассом класса PreparedStatement. Представим, что у нас есть база данных поэтов. В базе данных содержится хранимая процедура для задания возраста поэта во время его смерти (т.е. во сколько лет умер тот или иной поэт). Далее приведен пример вызова хранимой процедуры для внесения в базу данных информации о старом алкоголике Дилане Томасе:

try
{
 
int age = 39;
  String poetName =
"dylan thomas";
  CallableStatement proc =
  connection.prepareCall
("{ call set_death_age(?, ?) }");
  proc.setString
(1, poetName);
  proc.setInt
(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 id 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_death_age не оптимизирована должным образом. Однако это не окажет никакого влияния на Java-код, поскольку он не зависит от схемы реализации базы данных. С помощью Java-кода мы всего-навсего вызываем процедуру. Поэтому мы можем внести необходимые изменения в схему базы данных и в саму процедуру для увеличения производительности позже. При этом Java-код редактировать не придется.

Вот Java-метод, который вызывает нашу хранимую процедуру:

public static void setDeathAge(Poet dyingBard, int age)
throws SQLException
{
 
Connection con = null;
  CallableStatement proc =
null;

 
try
 
{
   
con  = connectionPool.getConnection();
    proc = con.prepareCall
("{ call set_death_age(?, ?) }");
    proc.setString
(1, dyingBard.getName());
    proc.setInt
(2, age);
    proc.execute
();
 
}
 
finally
 
{
   
try
   
{
     
proc.close();
   
}
   
catch (SQLException e) {}
   
con.close();
 
}
}

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

Функции

Хранимые процедуры могут возвращать значения. Это осуществляется с помощью метода getResultSet() класса CallableStatement, который получает возвращенное значение. Когда хранимая процедура возвращает значение, необходимо с помощью метода registerOutParameter() сообщить JDBC-драйверу какого типа данные ожидаются в качестве возвращаемого значения. Мало того, необходимо также изменить реализацию хранимой процедуры и явно указать какой тип данных эта процедура будет возвращать.

Не будем отходить от нашего старого примера и на этот раз напишем процедуру, с помощью которой мы сможем узнать, например, сколько было лет Дилану Томасу, когда он умер. На этот раз хранимая процедура реализована с помощью pl/pgsql (PostgreSQL):

create function snuffed_it_when (VARCHAR) returns integer 
declare
    poet_id NUMBER;
    poet_age NUMBER;
begin
    -- сперва получаем идентификатор, связанный с поэтом.
    SELECT id INTO poet_id FROM poets WHERE name = $1;
    -- получаем и возвращаем его возраст.
    SELECT age INTO poet_age FROM deaths WHERE mort_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
("{ ? = call snuffed_it_when(?) }");
proc.registerOutParameter
(1, Types.INTEGER);
proc.setString
(2, poetName);
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
    open 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 toesUp =
null;

 
try
 
{
   
con = ConnectionPool.getConnection();

   
// для PostgreSQL сначала нужно создать транзакцию (AutoCommit == false)...
   
con.setAutoCommit(false);

   
// Настраиваем вызов.
   
CallableStatement toesUp
    = connection.prepareCall
("{ ? = call list_early_deaths () }");
    toesUp.registerOutParameter
(1, Types.OTHER);
    getResults.execute
();

    ResultSet rs =
(ResultSet) getResults.getObject(1);
   
while (rs.next())
    {
     
String name = rs.getString(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 на поток вывода. Вот пример переработанного метода:

static void sendEarlyDeaths(PrintWriter out)
{
 
Connection con = null;
  CallableStatement toesUp =
null;

 
try
 
{
   
con = ConnectionPool.getConnection();

   
// для PostgreSQL сначала нужно создать транзакцию (AutoCommit == false)...
   
con.setAutoCommit(false);

   
// Настраиваем вызов.
   
CallableStatement toesUp
    = connection.prepareCall
("{ ? = call list_early_deaths () }");
    toesUp.registerOutParameter
(1, Types.OTHER);
    getResults.execute
();

    ResultSet rs =
(ResultSet) getResults.getObject(1);
   
while (rs.next())
    {
     
String name = rs.getString(1);
     
int age = rs.getInt(2);
      out.println
(name + " was " + age + " years old.");
   
}
   
rs.close();
 
}
 
catch (SQLException e)
  {
   
// Мы должны защитить эти вызовы.
   
toesUp.close();
    con.close
();
 
}
}

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

static void sendEarlyDeaths(final PrintWriter out)
{
 
ProcessPoetDeaths myMapper = new ProcessPoetDeaths()
  {
   
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.

Хранимые процедуры наверняка несколько прибавят весу вашему коду, но это не идет ни в какое сравнение с тем, что вам нужно будет предпринять, если использовать серверы приложений. Если ваш код достаточно сложный, чтобы появилась необходимость в использовании СУБД – можете смело применять хранимые процедуры. Не пожалеете.