Многие РСУБД начали реализовывать поддержку для некоторых типов типов CURSOR, REF CURSOR или ARRAY / TABLE. Эти типы имеют примерно ту же семантику , как JDBC «S java.sql.Array и java.sql.ResultSet . В принципе, такие типы могут появляться в любом месте SQL, даже если некоторые СУБД ограничивают поддержку определенным подмножеством функций. Что именно эти типы?
Типы ARRAY
Типы ARRAY легче всего понять. Массив обычно реализуется как типизированный набор значений. Их можно использовать как для столбцов таблицы, так и для хранимых процедур. Большинство разработчиков схем базы данных согласны с тем, что использование типов ARRAY в таблицах не обязательно является хорошей идеей, поскольку это приведет к схеме, которая нормализуется в « не первой нормальной форме ». С другой стороны, типы ARRAY могут быть очень мощными при использовании в качестве параметров хранимых процедур и особенно в качестве результатов хранимых функций. В обоих сценариях (столбцы таблицы, результаты функций) массивы могут быть извлечены с помощью таких операторов, как TABLE (…) или UNNEST (…). Эти операторы сделают содержимое массива доступным для любого предложения SQL, которое принимает таблицы в качестве аргументов. Пример (в HSQLDB):
-- unnest an ad-hoc anonymously typed array SELECT element FROM unnest(array[1, 2, 3]) AS unnested(element);
Аналогичный пример (в Oracle, который не поддерживает анонимные типы ARRAY):
-- create a typed array of numbers CREATE TYPE number_array AS VARRAY(10) OF NUMBER(7); -- unnest an ad-hoc array instance. Oracle will name -- the column of such an unnested array "COLUMN_VALUE" SELECT column_value FROM table(number_array(1, 2, 3));
Обе конструкции приведут к простой таблице, содержащей один столбец и три записи: 1, 2, 3. Как упоминалось ранее, мощь таких типов ARRAY становится очевидной при использовании их в качестве результатов в хранимых функциях. В Oracle, например, вы можете определить функцию следующим образом:
-- a simple example function CREATE FUNCTION get_array RETURN number_array IS BEGIN RETURN number_array(1, 2, 3); END; -- unnest results from a function returning an array SELECT column_value FROM TABLE(get_array);
С помощью этих синтаксических контуров вы можете определять очень сложные функции, возвращающие четко определенные типы, которые затем могут быть снова вставлены в таблицы SQL. Мощь этой функциональности знает небольшие ограничения, когда вы комбинируете типы ARRAY с типами OBJECT, в тех RDBMS, которые ее поддерживают. Опять же, с Oracle вы можете написать:
-- A simple, reusable person type CREATE TYPE person AS OBJECT( id NUMBER(7), name VARCHAR2(100) ); -- An array of such users CREATE TYPE person_array AS VARRAY(10) OF person; -- An unnested array of such users used in SQL: SELECT * FROM TABLE(person_array( person(1, 'Jim'), person(2, 'Joe') ));
Приведенный выше оператор SELECT приведет к тому, что таблица с двумя столбцами из двух столбцов будет выглядеть следующим образом:
| МНЕ БЫ | НАЗВАНИЕ |
|---|---|
| 1 | Джим |
| 2 | Джо |
Опять же, это можно использовать в сочетании с хранимыми функциями, которые вначале выполняют намного более сложную обработку, чтобы вычислить вышеуказанный результат, включая Джима и Джо.
ТАБЛИЦА типов
Некоторые СУБД (например, Oracle) различают типы ARRAY в памяти (например, тип VARRAY, который мы видели ранее) и типы TABLE в памяти. Основным отличием этой статьи является тот факт, что типы VARRAY имеют максимальный размер, тогда как типы TABLE могут быть расширены до любой произвольной длины. Кроме того, API Oracle для манипулирования вложенными таблицами непосредственно в SQL более богат, чем у типов VARRAY, если вы хотите добавить запись в таблицу, например, вложенную в другую таблицу. Тщательное сравнение было бы вне области, однако.
REF CURSORS
Курсоры, и в частности REF CURSORS, обрабатываются по-разному таким образом, что они не содержат непосредственно все свои данные, но могут быть повторены (или «зациклены»). Кроме того, REF CURSOR является слабо типизированным объектом, что означает, что число и типы столбцов REF CURSOR не могут быть известны во время компиляции SQL (или «parse-time»), но только при выполнении оператора SQL. Это затрудняет использование REF CURSOR непосредственно в SQL. Например, функция Oracle TABLE (…) не поддерживает типы REF CURSOR в качестве параметра. Смотрите также обзор того, что возможно, а что нет в этом вопросе переполнения стека:
http://stackoverflow.com/questions/6410452/fetch-oracle-table-type-from-stored-procedure-using-jdbc
Тем не менее, REF CURSOR может быть возвращен из хранимой процедуры или из хранимой функции и извлечен в JDBC, как и любой другой java.sql.ResultSet .
Поддержка jOOQ для типов ARRAY / CURSOR
Одна из основных целей jOOQ — позволить пользователям легко интегрировать передовые концепции RDBMS в Java, что является нетривиальным действием при использовании JDBC. Эти понятия еще не были (насколько мне известно) стандартизированы в SQL: 2008. Каждая СУБД, поддерживающая эти концепции, использует свой собственный синтаксис. Самый любопытный (но также и самый мощный) синтаксис, с которым я столкнулся, — это синтаксис базы данных H2 , где можно выполнить анализ массивов следующим образом:
-- unnest an ad-hoc anonymously typed array
SELECT *
FROM TABLE(
ID INT=(1, 2),
NAME VARCHAR=('Hello', 'World'));
Приведенный выше пример можно найти здесь:
http://www.h2database.com/html/functions.html#table
Помимо сокрытия этих многих фактов синтаксиса SQL от пользователя, jOOQ также стремится скрыть подготовку оператора JDBC и отображение типов от пользователя. Передача массивов в подготовленный оператор не является тривиальной с Oracle, выборка ResultSets из хранимых функций также не является. И ни одна из сегодняшних основных платформ, включая Hibernate / JPA, Spring, myBatis и т. Д., Не поддерживает простой способ автоматической интеграции таких типов данных и хранимых процедур в Java — хотя Spring позволяет писать один пользовательский преобразователь для каждой процедуры.
С jOOQ это станет возможным. В то время как типы ARRAY уже давно поддерживаются jOOQ, их удаление является частью работающего в настоящее время проекта CURSOR. API расширяется для обработки примеров, приведенных выше в SQL. Пример такого использования API:
// Create the usual jOOQ factory
Factory create = new OracleFactory(connection);
// Loop over the values returned from thegenerated getArray function
for (Record record : create.select()
.from(create.table(getArray()))
.fetch()) {
// This will print 1, 2, 3
System.out.println(record.getValue(0));
}
Расширенные типы данных как будущие инвестиции
Сила этих типов данных давно известна (и любима) администраторами баз данных и программистами, использующими PL / SQL и другие языки баз данных. Разработчики Java избегали их в основном из-за неловкости (или даже отсутствия) поддержки JDBC и / или JPA. Для неопытного разработчика JDBC трудно правильно связать массивы объектов с подготовленным оператором Oracle или извлечь REF CURSOR из CallableStatement.
Изображенный функционал уже доступен в jOOQ 1.6.3. Многие другие функции, связанные с типами CURSOR и ARRAY, будут реализованы в ближайшем будущем.
… так что начните использовать полный набор функций вашей базы данных!
связи
Эта статья и будущие статьи о SQL / Java с jOOQ будут опубликованы в моем блоге:
http://lukaseder.wordpress.com/2011/07/24/the-power-of-ref-cursor-types/
Для получения дополнительной информации о jOOQ, обратитесь к