25. Внешние хранимые процедуры, функции и триггеры, написанные на языке Java
В РЕД Базе Данных реализована возможность создания внешних процедур, функций и триггеров с использованием языка программирования Java, что существенно расширяет возможности языка PSQL по обработке данных. Например, с помощью PSQL невозможно работать с объектами файловой системы, а язык Java позволяет это.
О настройках РЕД Базы Данных для работы с внешними подпрограммами и о взаимодействиях с Java методами из базы данных описано в отдельном руководстве "Внешние хранимые процедуры, функции и триггеры, написанные на языке Java". В этом же разделе рассмотрен SQL синтаксис операторов создания, изменения и пересоздания внешних хранимых подпрограмм, написанных на Java.
25.1. Объявление/изменение/пересоздание внешних процедур
Синтаксис операторов создания, изменения и пересоздания внешней хранимой процедуры, написанной на Java, имеет одинаковую структуру и приведен в листинге:
Листинг 25.1 Синтаксис операторов создания, изменения и пересоздания внешней хранимой процедуры
{CREATE [ OR ALTER ] | RECREATE | ALTER} PROCEDURE <имя хранимой процедуры>
[AUTHID {OWNER | CALLER}]
[(<входной параметр> [, <входной параметр> ...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
[SQL SECURITY {DEFINER | INVOKER}]
EXTERNAL NAME '<полное имя класса>.<имя static метода>!(
[<Java тип> [, <Java тип>...]])'
[!<определяемая пользователем информация>]
ENGINE JAVA
Внешние процедуры либо не имеют выходных параметров, либо возвращают набор данных
ExternalResultSet (или класс, реализующий этот интерфейс), в зависимости от того, является
ли процедура селективной или нет. Выходные параметры при вызове функций должны быть массивами.
JavaEngine передает каждый параметр как массив с длиной 1, таким образом процедуры могут
менять их нулевой элемент.
Пример объявления в базе данных внешней выполнимой процедуры, осуществляющей вставку записи в таблицу:
CREATE PROCEDURE testInsert (n integer, s varchar(10))
EXTERNAL NAME 'example.ExampleClass.insert(int, String)'
ENGINE JAVA;
В следующем листинге приведено описание внешней процедуры, написанной на Java, иллюстрирующей пример вставки записи в таблицу:
public static void insert(int n, String s) throws SQLException {
Connection con = DriverManager.getConnection("jdbc:default:connection:");
try {
PreparedStatement stmt = con.prepareStatement ("insert into test_table (n, s)
values (?, ?)");
try { stmt.setInt(1, n);
stmt.setString(2, s);
stmt.execute(); }
finally { stmt.close(); }
}
finally { con.close(); }
}
Пример объявления в базе данных внешней селективной процедуры, генерирующей строки:
CREATE PROCEDURE testGenRows (numRows integer) RETURNS (n integer)
EXTERNAL NAME 'example.ExampleClass.genRows(int, int[])'
ENGINE JAVA;
Пример тела внешней процедуры, осуществляющей генерирование значений строк, приведен ниже:
public static ExternalResultSet genRows(final int numRows, final int[] n) {
return new ExternalResultSet() {
private int i = 1;
public void close() {}
public boolean fetch() throws Exception {
if (i > numRows) {
return false;
}
n[0] = i++;
return true;
}
};
}
25.2. Объявление/изменение/пересоздание внешних функций
Синтаксис операторов создания, изменения и пересоздания внешней функции, написанной на Java, имеет одинаковую семантику и приведен в листинге:
Листинг 25.2 Синтаксис операторов создания, изменения и пересоздания внешней функции
{CREATE [ OR ALTER ] | RECREATE | ALTER} FUNCTION <имя функции>
[AUTHID {OWNER | CALLER}]
[(<входной параметр> [, <входной параметр> ...])]
RETURNS (<тип данных>)
[SQL SECURITY {DEFINER | INVOKER}]
EXTERNAL NAME '<полное имя класса>.<имя static метода>!(
[<Java тип> [, <Java тип>...]])'
[!<определяемая пользователем информация>]
ENGINE JAVA
В отличие от внешних процедур, внешние функции всегда возвращают одно значение какого-либо из типов описанных в разделе "Соответствие типов данных SQL и Java" руководства разработчика.
Пример объявления новой или изменения существующей внешней функции, возвращающей системное свойство по указанному ключу:
CREATE OR ALTER FUNCTION get_system_property (name varchar(80))
RETURNS varchar(80)
EXTERNAL NAME 'java.lang.System.getProperty(String)'
ENGINE JAVA;
Описанная в примере внешняя функция производит суммирование входных параметров функции, объявленной в базе данных:
public static int sum() throws SQLException {
FunctionContext context = FunctionContext.get();
ValuesMetadata valuesmetadata = context.getInputMetadata();
Values values = context.getInputValues();
int ret = 0;
for (int i = 1; i <= valuesmetadata.getParameterCount(); ++i) {
ret += ((BigDecimal) values.getObject(i)).intValue();
}
return ret;
}
Пример регистрации в базе данных этой внешней функции:
CREATE FUNCTION funcSum2 (n1 integer, n2 integer)
RETURNS integer
EXTERNAL NAME 'example.ExampleClass.sum()'
ENGINE JAVA;
25.3. Объявление/изменение/пересоздание внешних триггеров
Синтаксис операторов создания, изменения и пересоздания внешнего триггера, написанного на Java, имеет одинаковую семантику и приведен в листинге:
Листинг 25.3 Синтаксис операторов создания, изменения и пересоздания внешнего триггера
{CREATE [ OR ALTER ] | RECREATE | ALTER} TRIGGER <имя триггера>
[AUTHID {OWNER | CALLER}]
{
<объявление табличного триггера>
| <объявление табличного триггера в стандарте SQL-2003>
| <объявление триггера базы данных>
| <объявление DDL триггера> }
[SQL SECURITY {DEFINER | INVOKER}]
EXTERNAL NAME '<полное имя класса>.<имя static метода>!(
[<Java тип> [, <Java тип>...]])'
[!<определяемая пользователем информация>]
ENGINE JAVA
Спецификация вызовов триггеров всегда без параметров, и Java метод должен возвращать void.
Детали вызова и значения полей OLD и NEW можно получить и установить из контекста вызова.
Тело внешнего триггера, написанного на Java, выполняющего логгирование:
public static void info() throws SQLException {
Logger log = LoggerFactory.getLogger(ExampleClass.class);
String NEWLINE = System.getProperty("line.separator");
TriggerContext context = TriggerContext.get();
String msg = "Table: "+ context.getTableName() +
"; Type: "+ context.getType() +
"; Action: "+ context.getAction() +
valuesToStr(context.getFieldsMetadata(), context.getOldValues(),
NEWLINE + "OLD:"+ NEWLINE) +
valuesToStr(context.getFieldsMetadata(), context.getNewValues(),
NEWLINE + "NEW:"+ NEWLINE);
log.info(msg);
}
private static String valuesToStr(ValuesMetadata metadata, Values values, String
label) throws SQLException {
if (values == null)
return "";
StringBuilder sb = new StringBuilder(label);
String NEWLINE = System.getProperty("line.separator");
for (int i = 1, count = metadata.getParameterCount(); i <= count; ++i)
sb.append(metadata.getName(i) + ": "+ values.getObject(i) + NEWLINE);
return sb.toString();
}
Пример объявления нового или изменения существующего внешнего триггера в базе данных:
CREATE OR ALTER TRIGGER trig_Employee_Log
BEFORE DELETE OR INSERT OR UPDATE on employee
EXTERNAL NAME 'example.ExampleClass.info()'
ENGINE JAVA;
25.4. Удаление внешних процедур, функций и триггеров
Удаление внешней процедуры осуществляется оператором DROP PROCEDURE.
Листинг 25.4 Синтаксис оператора удаления процедуры
DROP PROCEDURE <имя_процедуры>;
Предупреждение
В отличие от обычных хранимых процедур (функций), сервер не может проконтролировать наличие вызовов удаляемой процедуры (функции) из других внешних процедур, функций или триггеров. Поэтому удаление такой процедуры (функции) пройдет без ошибок, однако при последующем вызове внешней процедуры, функции или триггера будет сгенерировано исключение.
Удаление внешней функции осуществляется оператором DROP FUNCTION. Его синтаксис:
Листинг 25.5 Синтаксис оператора удаления функции
DROP FUNCTION <имя_функции>;
Оператор DROP TRIGGER удаляет существующий триггер:
Листинг 25.6 Синтаксис оператора удаления триггера
DROP TRIGGER <имя_триггера>;
25.5. Вызов внешних процедур и функций
Вызов внешних процедур и функций, написанных на Java, аналогичен вызову обычных хранимых
процедур и функций. Например, вызов внешней процедуры можно осуществить оператором EXECUTE PROCEDURE.
При этом внешняя процедура всегда будет выполняться с правами вызывающего её пользователя.