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
Существует два типа хранимых процедур, основанных на транзакции, в которой они вызываются: вложенные соединения и не вложенные соединения.
Вложенные соединения
Процедура этого типа использует ту же транзакцию, что и оператор SQL, который ее вызвал. Код процедуры использует то же соединение, что и родительский SQL, используя синтаксис URL соединения jdbc:default:connection
. Ниже приведен пример кода для получения соединения:
1
|
Connection c = DriverManager.getConnection( "jdbc:default:connection" ); |
Обратите внимание, что атрибуты URL-адреса соединения не поддерживаются для этого типа.
Пример кода по адресу: 2.1 Создание .
Не вложенные соединения
Этот тип процедуры использует новое соединение с базой данных. Процедура выполняется в транзакции, отличной от транзакции вызывающего SQL.
Код хранимой процедуры также может подключаться к другой базе данных.
Пример кода по адресу: 3.1 Использование не вложенного соединения .
1.2 Исключения SQL в процедурах
Исключения SQL в процедурах могут быть перехвачены и обработаны в коде процедуры или распространены (и перехвачены) в вызывающей программе.
2 Создайте и используйте хранимую процедуру Java
Здесь описывается создание хранимой процедуры Java в базе данных Java DB и ее интерактивное использование в SQL, а также в коде Java. Код хранимой процедуры создается с использованием языка программирования Java. Процедура представляет собой Java-код в методе с подписью public static void procedureMethod
. Хранимая процедура создается и сохраняется в базе данных Java DB как объект базы данных.
Процедура вызывается (или вызывается) с помощью команды SQL или из программы Java, использующей JDBC API.
2.1 Создание
Создайте метод Java, скомпилируйте его и сохраните процедуру в базе данных.
2.1.1 Создание метода Java
Ниже приведен пример метода.
01
02
03
04
05
06
07
08
09
10
11
|
public static void testProc( int iParam1, String iParam2, int [] oParam) throws SQLException { String connectionURL = "jdbc:default:connection" ; Connection conn = DriverManager.getConnection(connectionURL); String DML = "UPDATE TEST_TABLE SET NAME = ? WHERE ID = ?" ; PreparedStatement pstmnt = conn.prepareStatement(DML); pstmnt.setString( 1 , iParam2); pstmnt.setInt( 2 , iParam1); int updateRowcount = pstmnt.executeUpdate(); oParam [ 0 ] = updateRowcount; } // testProc() |
Код создается в классе Java, например JavaStoredProcs.java
, и компилируется. В классе может быть создано любое количество методов процедур.
В примере кода:
- Метод процедуры имеет три параметра. Первые два (iParam1 и iParam2) имеют вход IN, а третий — режимы параметров OUT соответственно. Обратите внимание, что параметр OUT указывается в виде массива; каждый параметр OUT и INOUT должен быть указан в методе процедуры в виде массива, и только первый элемент массива используется (то есть отображается) в качестве переменной параметра процедуры.
- Процедура использует вложенное соединение.
- Любое выброшенное исключение SQL может быть обработано в вызывающей программе или в методе процедуры; в этом случае исключение обрабатывается в вызывающем коде.
2.1.2 Создание процедуры в базе данных
Процедура создается в базе данных с помощью оператора CREATE PROCEDURE. Эта команда запускается в интерактивном режиме с использованием ij
или из программы Java с использованием интерфейса java.sql.Statement
API JDBC.
Синтаксис и детали команды следующие:
1
|
CREATE PROCEDURE procedure - Name (ProcedureParameters)ProcedureElements |
procedure-Name
-процедуры: имя процедуры, хранящееся в базе данных; создается в схеме по умолчанию, если не указано.
ProcedureParameters
: указывает режим параметров (IN, INOUT или OUT), необязательное имя и тип данных. Тип данных относится к типу данных базы данных. Java DB не поддерживает длинные типы столбцов (например, Long Varchar, BLOB,…) в процедурах. Параметры не являются обязательными.
ProcedureElements:
элементы ProcedureElements:
должны содержать следующие три элемента и могут иметь дополнительные необязательные элементы.
- ЯЗЫК ЯВА. Это единственная ценность.
- ПАРАМЕТР СТИЛЬ JAVA. Это единственная ценность.
- ВНЕШНЕЕ ИМЯ. Это указывает метод Java, который будет вызван при выполнении процедуры, и принимает форму
ClassName.methodName Optional, procedure elements
: - ДИНАМИЧНЫЕ РЕЗУЛЬТАТЫ НАБОРЫ
- DeterministicCharacteristic
- ВНЕШНЯЯ БЕЗОПАСНОСТЬ
- ИЗМЕНЕНИЯ ДАННЫХ SQL (по умолчанию), СОДЕРЖИТ SQL, ЧИТАЕТ ДАННЫЕ SQL, НЕТ SQL (процедура без какого-либо кода, связанного с базой данных)
2.1.2.1 Интерактивное создание процедуры в базе данных с использованием ij
ij
— инструмент командной строки, включенный в Java DB. ij
— это инструмент JDBC, используемый для выполнения интерактивных запросов к базе данных Java DB.
1
2
|
ij> CONNECT 'jdbc:derby:testDB' ; ij> CREATE PROCEDURE PROC_NAME(IN id1 INTEGER, IN name2 VARCHAR(50), OUT count3 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'JavaStoredProcs.testProc' PARAMETER STYLE JAVA; |
В этом примере процедура PROC_NAME
создается в testDB
данных testDB
. Метод Java, созданный ранее ( 2.1.1 Создание метода Java ), указывается как ВНЕШНЕЕ ИМЯ.
Чтобы вывести список процедур в базе данных, используйте команду SHOW PROCEDURES.
2.2.2 Изменить или удалить процедуру
Чтобы изменить процедуру, удалите процедуру из базы данных и создайте заново.
Пример удаления процедуры с использованием ij
:
1
|
ij> DROP PROCEDURE procedureName; |
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.
1
|
ij> CALL MyProc(); |
2.2.2 Процедура вызова из Java-программы
В этом примере кода вызывается процедура (PROC_NAME), созданная ранее (2.1 Создание).
В коде используется интерфейс CallableStatement
API JDBC (подробнее см. 2.2.3 Примечания по CallableStatement ). Входные параметры для процедуры устанавливаются, и значение параметра out печатается в конце этого примера метода. Обратите внимание, что этот класс Java отличается от класса, в котором создается процедура.
01
02
03
04
05
06
07
08
09
10
11
12
13
|
private static void runStoredProc(Connection conn) throws SQLException { int iParam1 = 1 ; String iParam2 = "updated name data" ; String proc = "{call PROC_NAME(?, ?, ?)}" ; CallableStatement cs = conn.prepareCall(proc); cs.setInt( 1 , iParam1); cs.setString( 2 , iParam2); cs.registerOutParameter( 3 , java.sql.Types.INTEGER); cs.executeUpdate(); String oParam = cs.getInt( 3 ); System.out.println( "Updated row count from the proc: " + oParam); } // runStoredProc() |
2.2.3 Примечания по CallableStatement
Интерфейс CallableStatement
API Java JDBC расширяет PreparedStatement
и определяется в пакете java.sql
. Это используется для выполнения хранимых процедур SQL.
API предоставляет синтаксис экранирования SQL хранимых процедур, который позволяет стандартным образом вызывать процедуры для всех РСУБД. Синтаксис имеет одну форму, которая включает в себя параметр результата, а другую — нет. Если используется, параметр результата должен быть зарегистрирован как параметр OUT. Другие параметры (arg1, arg2,…) могут использоваться для ввода, вывода или обоих.
Ниже приведен синтаксис (с возвращаемым значением и без него соответственно):
1
2
|
{? = call <procedure-name> [(arg1, arg2, ...)]} {call <procedure-name> [(arg1, arg2, ...)]} |
Значения параметра IN задаются с помощью методов установки, унаследованных от PreparedStatement
. Тип всех параметров OUT должен быть зарегистрирован до выполнения хранимой процедуры с помощью registerOutParameter (); их значения извлекаются после выполнения через методы getXxx (int parameterIndex / StringparameterName) (getBoolean (), getArray (),…).
Режимы параметров
Атрибуты параметров IN (по умолчанию), OUT и INOUT являются режимами параметров.
Вызов хранимой процедуры
1
2
3
|
String procName = "{call STORED_PRODURE_NAME(}" ; CallableStatement cs = conn.prepareCall(procName); ResultSet rs = cs.executeQuery(); |
Чтобы вызвать хранимую процедуру, используйте методы execute (), executeQuery () или executeUpdate () в зависимости от того, сколько объектов ResultSet
возвращает процедура. Если вы не уверены, сколько объектов ResultSet
возвращает процедура, используйте метод execute ().
1
2
3
4
5
6
|
cs = conn.prepareCall( "{call INCR_PRICE(?, ?)}" ); cs.setString( 1 , itemNameArg); // (1) cs.setFloat( 2 , newPriceArg); // (2a) cs.registerOutParameter( 2 , Types.NUMERIC); // (2b) cs.execute(); float newPrice = cs.getFloat( 2 ); // (2c) |
Первый параметр 1 является параметром IN.
Второй параметр имеет параметр режима INOUT. Его значение IN указывается путем вызова метода установки 2a и регистрации типа OUT с помощью метода registerOutParameter () 2b . Выходное значение извлекается методом получения 2c .
3 примера
Есть два примера: первый показывает код для создания и использования процедуры не вложенного типа, а следующий — пример использования предопределенной процедуры Java DB.
3.1 Использование не вложенного соединения
В этом примере процедура Java обращается к другой базе данных и соединению, чем та, которая используется в вызывающей программе. Процедура возвращает целочисленное значение параметра OUT.
- Создайте и скомпилируйте код процедуры.
01020304050607080910111213
public
static
void
testProc4(
int
[] retval)
throws
SQLException {
String connectionURL =
"jdbc:derby:testDB2"
;
Connection conn = DriverManager.getConnection(connectionURL);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT * FROM ID_TABLE"
);
int
nextid =
0
;
while
(rs.next()) {
nextid = rs.getInt(
"ID"
);
}
retval[
0
] = nextid;
conn.close();
// alternative: shutdown the database.
}
// testProc4()
- Создайте процедуру в базе данных.
12345
CREATE
PROCEDURE
PROC_NAME_4(
OUT
paramname
INTEGER
)
LANGUAGE JAVA
EXTERNAL
NAME
'JavaStoredProcs.testProc4'
PARAMETER STYLE JAVA
READS SQL DATA;
Элемент процедуры READS SQL DATA указывает, что метод SQL в процедуре может использовать только операторы SELECT.
- Вызовите процедуру в клиентской программе.
12345678
private
static
void
runStoredProc4(Connection conn)
throws
SQLException {
String proc =
"{call PROC_NAME_4(?)}"
;
CallableStatement cs = conn.prepareCall(proc);
cs.registerOutParameter(
1
, java.sql.Types.INTEGER);
cs.execute();
int
oParamData = cs.getInt(
1
);
// proc output value
}
// runStoredProc4()
3.2 Процедура встроенной системы Java DB
SYSCS_UTIL.SYSCS_BACKUP_DATABASE — это предопределенная и специфичная для Java DB системная процедура. Это создает резервную копию базы данных в указанном каталоге. Синтаксис: SYSCS_BACKUP_DATABASE (IN backupDirPath VARCHAR). Процедура не возвращает значение.
В следующем примере команда SQL вызывает процедуру:
1
|
CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE( 'c:/backupdir' ); |
Примечание. Подробный пример кода Java можно найти в записи блога под названием « Резервное копирование базы данных Java DB» по адресу http://www.javaquizplayer.com/blogposts/blogpost4.html.
4 ПРИМЕЧАНИЯ
4.1. Некоторые преимущества процедур перед кодом клиента
- Эта подпрограмма позволяет сохранить код один раз на сервере базы данных и получить доступ к нему из нескольких приложений. Кроме того, код может быть сложным по сравнению с SQL.
- Код выполняется на сервере, поэтому в приложении aclient-server уменьшается сетевой трафик. Это улучшает производительность приложения.
4.2 Другие РСУБД
Oracle 10g и HyperSQL DataBase (HSQLDB) являются одними из других баз данных, которые поддерживают хранимые процедуры Java.