какой класс или интерфейс пакета java sql используется при необходимости

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

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

Строка, которая подается в качестве параметра методу 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:

Необычная реализация, не правда ли? Готов поспорить, что многие ожидали увидеть обновление таблицы 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 ;

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

Функции

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

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

Далее идет код на Java, вызывающий эту процедуру:

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

Комплексные возвращаемые значения

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

Когда вы выполняете любой SQL-запрос, СУБД создает объект базы данных, который принято называть курсором (cursor). Он используется для итеративного изъятия записей (рядов таблицы), возвращаемых этим запросом. Объект класса ResultSet – это представление такого курсора на момент времени. Благодаря этому вы можете, без участия базы данных, проходить по содержимому ResultSet.

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

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

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

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

try
<
con = ConnectionPool.getConnection () ;

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

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 ) ;

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:

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

Резюме

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

Не все базы данных поддерживают хранимые процедуры. Однако существует большое количество добротных реализаций, как бесплатных open-source, так и не совсем бесплатных. Так что переносимость – это не проблема. Oracle, PostgreSQL и DB2 имеют очень схожие языки для написания хранимых процедур, которые хорошо поддерживаются многими on-line сообществами.

Существует много широко распространенных инструментов для работы с хранимыми процедурами. Среди них: редакторы, отладчики, среды разработки (IDE), как, например, TOAD или TORA, которые представляют собой великолепную среду для написания и сопровождения PL/SQL и pl/pgsql.

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

Источник

Хранимые процедуры Java в Java DB

1 хранимая процедура Java

Этот пост о хранимых процедурах Java в Java DB.

Java DB — это система управления реляционными базами данных, основанная на языке программирования Java и SQL. Это релиз Oracle проекта Apby Software Foundation с открытым исходным кодом Derby. Java DB включена в Java SE 7 SDK.

Код Java, вызываемый в базе данных, является хранимой процедурой (или процедурой). Хранимые процедуры Java являются подпрограммами JDBC (Java Database Connectivity) на стороне базы данных.

Код процедуры определяется в методе класса Java и сохраняется в базе данных. Это выполняется с использованием SQL. Код процедуры может быть с или без какого-либо кода, связанного с базой данных.

Другие программы на стороне базы данных (или на стороне сервера) являются триггерами и табличными функциями.

1.1 Типы процедур Java

Существует два типа хранимых процедур, основанных на транзакции, в которой они вызываются: вложенные соединения и не вложенные соединения.

Вложенные соединения

Обратите внимание, что атрибуты URL-адреса соединения не поддерживаются для этого типа.

Не вложенные соединения

Этот тип процедуры использует новое соединение с базой данных. Процедура выполняется в транзакции, отличной от транзакции вызывающего SQL.

Код хранимой процедуры также может подключаться к другой базе данных.

1.2 Исключения SQL в процедурах

Исключения SQL в процедурах могут быть перехвачены и обработаны в коде процедуры или распространены (и перехвачены) в вызывающей программе.

2 Создайте и используйте хранимую процедуру Java

Процедура вызывается (или вызывается) с помощью команды SQL или из программы Java, использующей JDBC API.

2.1 Создание

Создайте метод Java, скомпилируйте его и сохраните процедуру в базе данных.

2.1.1 Создание метода Java

Ниже приведен пример метода.

2.1.2 Создание процедуры в базе данных

Процедура создается в базе данных с помощью оператора CREATE PROCEDURE. Эта команда запускается в интерактивном режиме с использованием ij или из программы Java с использованием интерфейса java.sql.Statement API JDBC.

Синтаксис и детали команды следующие:

ProcedureParameters : указывает режим параметров (IN, INOUT или OUT), необязательное имя и тип данных. Тип данных относится к типу данных базы данных. Java DB не поддерживает длинные типы столбцов (например, Long Varchar, BLOB,…) в процедурах. Параметры не являются обязательными.

ProcedureElements: элементы ProcedureElements: должны содержать следующие три элемента и могут иметь дополнительные необязательные элементы.

2.1.2.1 Интерактивное создание процедуры в базе данных с использованием ij

ij — инструмент командной строки, включенный в Java DB. ij — это инструмент JDBC, используемый для выполнения интерактивных запросов к базе данных Java DB.

Чтобы вывести список процедур в базе данных, используйте команду SHOW PROCEDURES.

2.2.2 Изменить или удалить процедуру

Чтобы изменить процедуру, удалите процедуру из базы данных и создайте заново.

Пример удаления процедуры с использованием ij :

2.2 Использование (вызов)

Процедура запускается в интерактивном режиме с помощью команды SQL CALL или из клиентской программы с использованием JDBC API.

Команда CALL SQL поддерживает только параметры IN. Интерфейс CallableStatement API JDBC используется для вызова процедуры с параметрами IN, INOUT или OUT.

2.2.1 Вызов SQL-оператора

Оператор CALL используется для вызова процедуры. Это не возвращает значение. При вызове с использованием CALL поддерживаются только процедуры с параметрами IN.

В следующем примере показана команда CALL, запускаемая из ij для вызова процедуры MyProc. MyProc MyProc. MyProc — это имя процедуры, определенной в базе данных с помощью CREATE PROCEDURE.

2.2.2 Процедура вызова из Java-программы

В этом примере кода вызывается процедура (PROC_NAME), созданная ранее (2.1 Создание).

В коде используется интерфейс CallableStatement API JDBC (подробнее см. 2.2.3 Примечания по CallableStatement ). Входные параметры для процедуры устанавливаются, и значение параметра out печатается в конце этого примера метода. Обратите внимание, что этот класс Java отличается от класса, в котором создается процедура.

Источник

Какой класс или интерфейс пакета java sql используется при необходимости

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

В чем заключаются преимущества использования JDBC?

Преимуществами JDBC считают:

Что из себя представляет JDBC URL?

JDBC URL состоит из:

Пример JDBC URL для подключения к MySQL базе данных «Test» расположенной по адресу localhost и ожидающей соединений по порту 3306: jdbc:mysql://localhost:3306/Test

Из каких частей стоит JDBC?

JDBC состоит из двух частей:

JDBC превращает вызовы уровня API в «родные» команды того или иного сервера баз данных.

Перечислите основные классы и интерфейсы JDBC.

Перечислите основные типы данных используемые в JDBC. Как они связаны с типами Java?

Опишите основные этапы работы с базой данных при использовании JDBC.

Как зарегистрировать драйвер JDBC?

Регистрацию драйвера можно осуществить несколькими способами:

Class.forName(«полное имя класса драйвера») ;

Как установить соединение с базой данных?

В качестве параметра может передаваться:

Какие уровни изоляции транзакций поддерживаются в JDBC?

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

Во время использования транзакций, для обеспечения целостности данных, СУБД использует блокировки, чтобы заблокировать доступ других обращений к данным, участвующим в транзакции. Такие блокировки необходимы, чтобы предотвратить:

«грязное» чтение (dirty read) — чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится);

неповторяющееся чтение (non-repeatable read) — при повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными;

фантомное чтение (phantom reads) — ситуация, когда при повторном чтении в рамках одной транзакции одна и та же выборка дает разные множества строк.

Уровни изоляции транзакций определены в виде констант интерфейса java.sql.Connection :

TRANSACTION_NONE – драйвер не поддерживает транзакции;

TRANSACTION_READ_UNCOMMITTED – позволяет транзакциям видеть несохраненные изменения данных: разрешает грязное, непроверяющееся и фантомное чтения;

TRANSACTION_READ_COMMITTED – любое изменение, сделанное в транзакции, не видно вне неё, пока она не сохранена: предотвращает грязное чтение, но разрешает непроверяющееся и фантомное;

TRANSACTION_REPEATABLE_READ – запрещает грязное и непроверяющееся, фантомное чтение разрешено;

TRANSACTION_SERIALIZABLE – грязное, непроверяющееся и фантомное чтения запрещены.

NB! Сервер базы данных может не поддерживать все уровни изоляции. Интерфейс java.sql.DatabaseMetaData предоставляет информацию об уровнях изолированности транзакций, которые поддерживаются данной СУБД.

При помощи чего формируются запросы к базе данных?

Для выполнения запросов к базе данных в Java используются три интерфейса:

Объекты-носители интерфейсов создаются при помощи методов объекта java.sql.Connection :

Чем отличается Statement от PreparedStatement?

Перед выполнением СУБД разбирает каждый запрос, оптимизирует его и создает «план» (query plan) его выполнения. Если один и тот же запрос выполняется несколько раз, то СУБД в состоянии кэшировать план его выполнения и не производить этапов разборки и оптимизации повторно. Благодаря этому запрос выполняется быстрее.

Суммируя: PreparedStatement выгодно отличается от Statement тем, что при повторном использовании с одним или несколькими наборами параметров позволяет получить преимущества заранее прекомпилированного и кэшированного запроса, помогая при этом избежать SQL Injection.

Как осуществляется запрос к базе данных и обработка результатов?

Выполнение запросов осуществляется при помощи вызова методов объекта, реализующего интерфейс java.sql.Statement :

Как вызвать хранимую процедуру?

Выбор объекта зависит от характеристик хранимой процедуры:

Если неизвестно, как была определена хранимая процедура, для получения информации о хранимой процедуре (например, имен и типов параметров) можно использовать методы java.sql.DatabaseMetaData позволяющие получить информацию о структуре источника данных.

Пример вызова хранимой процедуры с входными и выходными параметрами:

Как закрыть соединение с базой данных?

Соединение с базой данной закрывается вызовом метода close() у соответствующего объекта java.sql.Connection или посредством использования механизма try-with-resources при создании такого объекта, появившегося в Java 7.

NB! Предварительно необходимо закрыть все запросы созданные этим соединением.

Источник