Съхранени процедури за Java програмисти

В тази статия ще разгледаме как съхранените процедури могат да се използват в системи за управление на бази данни (СУБД). Ще разгледаме не само най-основните и общи функции на запомнените процедури, но и някои допълнителни, например възможността за връщане на ResultSet обекти. Предполага се, че сте доста запознати с принципите на СУБД и JDBC API. Не е необходимо да имате опит с използването на съхранени процедури.

Съхранените процедури са програми, които се намират и изпълняват в сървър на база данни. Такава процедура може лесно да бъде извикана от Java клас, като се използва специален синтаксис. Когато извиквате такава процедура, трябва да посочите нейното име и да дефинирате списък с параметри. Имената и списъкът с параметри се изпращат през JDBC връзка към СУБД, която изпълнява извиканата процедура и връща резултата (ако има такъв) обратно, използвайки същата връзка.

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

Можете да напишете вашите съхранени процедури на Java, Python, Perl или C, но повечето от тях обикновено са написани на специфичен за базата данни език. Oracle използва PL/SQL, PostgreSQL -pl/pgsql и DB2 използва процедурен SQL. Тези езици като цяло са много сходни един с друг. Тяхната взаимозаменяемост всъщност не е по-сложна от това, да речем, Session Bean да бъде взаимозаменяем от една версия на EJB спецификацията на Sun към друга. В допълнение, езиците за писане на съхранени процедури са проектирани по такъв начин, че обикновените SQL изрази могат лесно да бъдат вградени в тях. Поради това те са много по-добри в изразяването на това как работят базите данни, отколкото езици като Java или C, например.

Тъй като запаметените процедури се изпълняват директно от самата СУБД, времето за тяхното изпълнение и обработка е значително намалено. Това е особено забележимо в приложения, които изпълняват не много сложни SQL оператори, които се обработват доста бързо, но броят им не е умерено висок. Вместо да изпълнявате четири или пет SQL оператора във вашия Java код, можете да изпълните само една съхранена процедура, която ще извърши всички необходими операции от страната на сървъра. Намаляването на броя на мрежовите комуникации може да има много драматичен ефект върху производителността на приложението.

Използване на съхранени процедури

JDBC поддържа извикване на съхранени процедури с класа CallableStatement. Този клас е действителен подклас на класа PreparedStatement. Нека си представим, че имаме база данни с поети. Базата данни съдържа съхранена процедура за определяне на възрастта на поета към момента на неговата смърт (т.е. на каква възраст е починал този или онзи поет). Следното е пример за извикване на съхранена процедура за попълване на базата данни с информация за стария алкохолик Дилън Томас:

опитайте int възраст = 39; String poetName = "dylan thomas" ; CallableStatement proc = connection.prepareCall ( "< повикване set_death_age(?, ?) >" ); proc.setString ( 1 , poetName ) ; proc.setInt (2, възраст); cs.execute(); >

Низът, който се предава като параметър на метода pripraveCall(), е спецификацията на извикването на процедурата. Той определя името на процедурата, която ще бъде извикана, и символите '?', които определят необходимите параметри.

Интегрирането на JDBC е огромно предимство за съхранените процедури, защото не е необходимо да променяте класове или да използвате каквито и да е конфигурационни файлове, за да го извикате от вашето приложение. Всичко, което трябва да направите, е да изберете подходящия JDBC драйвер за вашата СУБД.

И така, когато се изпълни горният код, се извиква процедурата на базата данни. В този пример не се опитваме да получим някакъв резултат, тъй като просто няма да има такъв. Можете да разберете дали процедурата е приключила успешно или е възникнала някаква извънредна ситуация, като използвате изключението, хвърлено в този случай. Грешката може да се прояви по два начина: или директно при изпълнение на процедурата (например, когато типът на един от предадените параметри не съвпада с типа, очакван от процедурата), или на ниво приложение (например, хвърля се изключение, което показва, че „Дилън Томас“ не е намерен в базата данни на поети).

Комбиниране на SQL оператори и процедури

Съпоставянето на Java обекти към записи в SQL таблици е сравнително лесно, но обикновено изисква изпълнението на няколко SQL оператора; например трябва да извършите SELECT, за да намерите идентификатора (ID) на желания ред от таблицата (запис) и след това да изпълните INSERT, за да вмъкнете данни в реда на таблицата с конкретен ID. Въпреки това, в схема с по-висока нормализация, трябва да извършите АКТУАЛИЗАЦИЯ на много таблици на база данни. Следователно трябва да се изпълнят много повече SQL изрази. И така кодът на Java може да нарасне много и натоварването на мрежата ще бъдерастат с добавянето на всеки нов такъв израз.

Поставянето на всички тези SQL изрази в една съхранена процедура ще направи живота ви много по-лесен. В този случай правите само една мрежова заявка за извикване на процедура, а не за всеки от изразите. Всички тези валидни SQL изрази ще имат място в базата данни. Освен това езиците за писане на съхранени процедури, като PL/SQL, ви позволяват да пишете повече собствен SQL, отколкото можете с Java. Следното е вариант на запомнената процедура, обсъдена по-рано, написана с помощта на езика Oracle PL/SQL:

Необичайно изпълнение, нали? Обзалагам се, че много хора са очаквали да видят таблицата на поетите актуализирана с израз UPDATE. Това е ясен пример колко по-лесно е да внедриш някои неща с помощта на запомнени процедури. Ясно е, че текущото изпълнение на процедурата set_death_age не е правилно оптимизирано. Това обаче няма да има ефект върху кода на Java, тъй като не зависи от схемата за изпълнение на базата данни. С кода на Java ние просто извикваме процедура. Следователно можем да направим необходимите промени в схемата на базата данни и в самата процедура, за да увеличим производителността по-късно. В този случай кодът на Java не трябва да се редактира.

Ето метода на Java, който извиква нашата съхранена процедура:

public static void setDeathAge ( Poet dyingBard, int age ) хвърля SQLException Connection con = null; CallableStatement proc = null;

опитайте con = connectionPool.getConnection(); proc = con.prepareCall ( "< повикване set_death_age(?, ?) >" ); proc.setString (1, dyingBard.getName ()); proc.setInt (2, възраст); proc.execute(); > накрая опитайте proc.close() ; > catch ( SQLException e ) <> con.close(); > >

Използването на статични методи като този е много добър начин за осигуряване на надеждност при бъдеща експлоатация. Освен това този подход прави кода за извикване на съхранени процедури много прост и шаблонен. Ако например трябва да извикате много запаметени процедури, можете просто да копирате горния код на метода и с малки промени да получите метод за извикване на следващата процедура. И тъй като този код е много общ, всъщност можете да напишете свой собствен скрипт специално за извикване на съхранени процедури.

Съхранените процедури могат да връщат стойности. Това се прави с помощта на метода getResultSet() на класа CallableStatement, който получава върнатата стойност. Когато съхранена процедура върне стойност, трябва да използвате метода registerOutParameter(), за да кажете на JDBC драйвера какъв тип данни се очаква като върната стойност. Освен това е необходимо също така да се промени изпълнението на съхранената процедура и изрично да се посочи какъв тип данни тази процедура ще върне.

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

Следва кодът на Java, който извиква тази процедура:

Какво се случва, ако зададете типа на връщане неправилно? В този случай ще бъде хвърлено RuntimeException, точно както при използване на метод на клас ResultSet за получаване на стойност от грешен тип.

Комплексни връщани стойности

За много хора познаването на съхранените процедури се крие в това, което обсъдихме по-горе. И това е. Ако това беше всичко, което можехме да направимсъхранени процедури, те не биха могли да заменят други механизми за дистанционно изпълнение. Всъщност съхранените процедури са много по-мощни, отколкото може да изглежда на пръв поглед.

Когато изпълнявате произволна SQL заявка, СУБД създава обект на база данни, наречен курсор. Използва се за итеративно извличане на записите (редовете на таблицата), върнати от тази заявка. Обект от класа ResultSet е представяне на такъв курсор в определен момент. Това ви позволява да преминавате през съдържанието на ResultSet без необходимост от база данни.

Някои СУБД ви позволяват да върнете препратки към курсори, върнати от извиквания към съхранени процедури. Спецификацията на JDBC не поддържа това, но драйверите на Oracle, PostgreSQL и DB2 JDBC имат вградената възможност да преобразуват указател към курсор в обект ResultSet.

За следващия пример, възможността да изберете списък с поети, които никога не са достигнали пенсионна възраст (60 години). Процедурата за извършване на следващия избор е дадена по-долу. Ще върне курсор. Използваме същия език (PostgreSQL pl/pgsql):

Следното е метод на Java, който извиква тази процедура, получава резултата и отпечатва получените записи в обект на PrintWriter:

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

опитайте 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 ()) Име на низ = rs.getString ( 1 ); int възраст = rs.getInt ( 2 ); out.println ( име + " беше " + възраст + " години." ); > rs.close(); > catch ( SQLException e ) // Трябва да защитим тези повиквания. toesUp.close(); con.close(); > >

Тъй като връщането на курсори не е изрично предвидено в JDBC спецификацията, ние използваме Types.OTHER, за да декларираме името на типа, върнат от процедурата, и след това да получим резултата от извикването, използвайки метода getObject().

Метод на Java, който извиква процедура, е много добър пример за картографиране. Картографирането е начин за абстрахиране на операции върху набор от записи (например). Вместо да връщаме самия набор от тази процедура, можем просто да предадем операция, която ще направи нещо с този набор. В този случай нашата операция е да отпечатаме съдържанието на ResultSet в изходния поток. Ето пример за преработен метод:

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

опитайте con = ConnectionPool.getConnection();

// за PostgreSQL първо трябва да създадете транзакция (AutoCommit == false). con.setAutoCommit (false) ;

ResultSet rs = (ResultSet) getResults.getObject (1); while ( rs.next ()) Име на низ = rs.getString ( 1 ); int възраст = rs.getInt ( 2 ); out.println ( име + " беше " + възраст + " години." ); > rs.close(); > улов(SQLException e ) // Трябва да защитим тези повиквания. toesUp.close(); con.close(); > >

Това ви позволява да извършвате произволни операции върху данните на обект ResultSet, без да се налага да променяте или дублирате кода на метода, който получава този обект ResultSet. И това е много важно. Ако искаме, можем да пренапишем метода sendEarlyDeaths:

Този метод извиква mapEarlyDeaths, който се предава на анонимен екземпляр на класа ProcessPoetDeaths. Този екземпляр на класа имплементира метода sendDeath, който отпечатва информация за поети в изходния поток, както в предишния пример. Разбира се, тази техника не е част от спецификацията на запомнената процедура, но понякога е много необходимо да се комбинира възможността за връщане на ResultSet обекти и запаметени процедури. В резултат на това имаме невероятно мощен инструмент.

Съхранените процедури помагат за постигане на логическо разделяне на вашия код, което почти винаги е изключително необходимо и полезно. Сред предимствата на това разделение:

Не всички бази данни поддържат съхранени процедури. Въпреки това има голям брой добри реализации, както безплатни с отворен код, така и не съвсем безплатни. Така че преносимостта не е проблем. Oracle, PostgreSQL и DB2 имат много сходни езици за писане на съхранени процедури, които се поддържат добре от много онлайн общности.

Съхранените процедури със сигурност ще добавят известна тежест към вашия код, но това е нищо в сравнение с това, което трябва да направите, ако използвате сървъри за приложения. Ако вашият код е достатъчно сложен, че е необходимо да използвате СУБД, можете безопасно да използвате запомнени процедури. Няма да съжаляваш.