/привет/мир/etc: Java в СУБД Oracle, часть III

В этой статье я рассмотрю, как Java код, выполняющийся внутри СУБД Oracle, работает с данными в БД. Попутно продемонстрирую работу резолвера (resolver) встроенной Oracle JVM и экспериментально установлю, с какими правами выполняется Java код внутри СУБД - правами владельца кода (definer) или вызывающего пользователя (invoker). В заключение, я продемонстрирую использование SQLJ для включения команд SQL в исходный код JAVA.

Для экспериментов создам таблицу books и вставлю в нее пару строк:

SQL> CREATE TABLE books (
         book_id NUMBER(9) PRIMARY KEY,
         name VARCHAR2(50) NOT NULL
     );
Table created

SQL> INSERT INTO books VALUES (1, 'Книга рекордов Гиннеса');
1 row inserted

SQL> INSERT INTO books VALUES (2, 'Слово о полку Игореве');
1 row inserted

SQL> COMMIT;
Commit complete

Класс Connector будет предоставлять соединение с БД по умолчанию. Для этого не нужно задавать параметры соединения - это то же соединение, что используется текущим сеансом, в котором выполняется хранимый код Java. Соединение по умолчанию предоставляется серверным внутренним (server-side intrernal) JDBC драйвером.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "my/demo/Connector" AS
package my.demo;

import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.driver.*;

public class Connector {
 
 public static Connection getConnection() throws SQLException {
  return new OracleDriver().defaultConnection();
 }

}

Операции с таблицей books будет выполнять класс BooksDAO. На самом деле, это очень скромный DAO. Вместо типичного набора операций CRUD мой DAO поддерживает только чтение всех строк (BooksDAO.listAll()) и изменение указанной строки таблицы (BooksDAO.update()). Для краткости я не стал вводить класс Book, объекты которого представляли бы строку таблицы. Моя задача - продемонстрировать работу с БД из хранимого Java кода, а для этого вполне достаточно реализовать пару операций с таблицей.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "my/demo/BooksDAO" AS
package my.demo;

import java.sql.*;

public class BooksDAO
{
 public static void update(int bookId, String name) throws SQLException {
  String update = "UPDATE books SET name = ? WHERE book_id = ?";
  Connection conn = Connector.getConnection();
  PreparedStatement stm = conn.prepareStatement(update);
  stm.setString(1, name);
  stm.setInt(2, bookId);
  stm.executeUpdate();
 }

 public static void listAll() throws SQLException {
  Connection conn = Connector.getConnection();
  Statement stm = conn.createStatement(); 
  ResultSet rs = stm.executeQuery("SELECT book_id, name FROM books");
  while (rs.next()) {
   System.out.println(String.format("%5d : %s", rs.getInt(1), rs.getString(2)));
  }
 }

}

Ошибки, если таковые возникают при выполнении команды CREATE AND RESOLVE JAVA SOURCE, можно посмотреть в системном вью user_errors. Для только что созданного объекта запрос будет таким:

SELECT * FROM user_errors WHERE NAME = 'my/demo/BooksDAO';

Теперь создам PL/SQL обертку для методов класса BooksDAO, пакет demo_books_dao:

SQL> CREATE OR REPLACE PACKAGE demo_books_dao IS
  2  
  3      PROCEDURE update_book(book_id BINARY_INTEGER, name VARCHAR2)
  4      AS LANGUAGE JAVA NAME 'my.demo.BooksDAO.update(int, java.lang.String)';
  5  
  6      PROCEDURE list_all_books AS LANGUAGE JAVA NAME 'my.demo.BooksDAO.listAll()';
  7  
  8  END demo_books_dao;
  9  /
Package created

SQL> show error
No errors

Вызову методы класса BooksDAO:

SQL> set serveroutput on
SQL> call dbms_java.set_output(1000000);
Method called

SQL> begin demo_books_dao.list_all_books; end;
  2  /
    1 : Книга рекордов Гиннеса
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

SQL> begin demo_books_dao.update_book(1, 'Атлас мира'); end;
  2  /
PL/SQL procedure successfully completed

SQL> begin demo_books_dao.list_all_books; end;
  2  /
    1 : Атлас мира
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

Обратите внимание, что метод BooksDAO.update() не выполняет COMMIT. Внутренний JDBC драйвер также не выполняет (и не поддерживает!) автокоммит, в результате можно откатить сделанные изменения:

SQL> ROLLBACK;
Rollback complete

SQL> begin demo_books_dao.list_all_books; end;
  2  /
    1 : Книга рекордов Гиннеса
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

До сих пор мои эксперименты ограничиывались работой с Java под одним и тем же пользователем. Это был пользователь ay.

Теперь попробуем вызывать методы класса BooksDAO под пользователем ay2, который не является владельцем класса. Есть два способа сделать это:

предоставить пользователю ay2 права на выполнение PL/SQL пакета demo_books_dao, тогда пользователь ay2 сможет вызвать методы класса BooksDAO через процедуры-обертки, так же, как это делает пользователь ay;
предоставить пользователю ay2 права на выполнение Java класса my/demo/BooksDAO, тогда пользователь ay2 сможет определить свой Java класс, вызывающий его методы, и создать PL/SQL обертку для методов собственного Java класса.

Первый способ:

-- ay
GRANT EXECUTE ON demo_books_dao TO ay2;

-- ay2
SQL> set serveroutput on
SQL> call dbms_java.set_output(1000000);
Method called

SQL> begin ay.demo_books_dao.list_all_books; end;
  2  /
    1 : Книга рекордов Гиннеса
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

Как известно, PL/SQL код выполняется с правами владельца этого кода (если явно не указать, что код должен выполняться с правами вызывающего пользователя). То есть, вызванная нами процедура ay.demo_books_dao.list_all_books выполнилась с правами пользователя ay, который одновременно является владельцем хранимого Java класса BooksDAO и таблицы books.

Теперь попробуем второй способ.

Вызовем методы класса BooksDAO непосредственно под пользователем ay2. PL/SQL процедура-обертка и оборачиваемый ею Java метод должны находиться в одной и той же схеме. Поэтому, под пользователем ay2 создадим Java класс-обертку для класса BooksDAO, принадлежащего пользователю ay. Для того, чтобы Java класс пользователя ay2 мог использовать класс BooksDAO в другой схеме, необходимо

выдать пользователю ay2 права на выполнение класса BooksDAO;
при создании Java класса в схеме ay2 указать спецификацию резолвера, которая позволит отыскать класс в схеме ay.

-- ay
SQL> grant execute on "my/demo/BooksDAO" to ay2;
Grant succeeded

-- ay2
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "my/demo/BooksDAOWrapper" 
RESOLVER ((* AY) (* PUBLIC))
AS
package my.demo;

import java.sql.*;

public class BooksDAOWrapper
{
    public static void update(int bookId, String name) throws SQLException {
        BooksDAO.update(bookId, name);
    }

    public static void listAll() throws SQLException {
        BooksDAO.listAll();
    }

}

В приведенном коде строка RESOLVER ((* AY) (* PUBLIC)) говорит о том, что классы, от которых зависит данный класс, нужно искать в схемах AY и PUBLIC. Попробуйте убрать спецификацию резолвера - и получите ошибки "cannot find symbol":

ORA-29535: source requires recompilation
my/demo/BooksDAOWrapper:8: cannot find symbol
symbol  : variable BooksDAO
location: class my.demo.BooksDAOWrapper
        BooksDAO.update(bookId, name);
        ^
my/demo/BooksDAOWrapper:12: cannot find symbol
symbol  : variable BooksDAO
location: class my.demo.BooksDAOWrapper
        BooksDAO.listAll();
        ^
2 errors

В случае, когда спецификация резолвера не указана, резолвер ищет зависимости в текущей схеме и в схеме PUBLIC - такова спецификация резолвера по умолчанию.

Успешно создав валидный Java класс BooksDAOWrapper в схеме ay2, создадим для него пакет-обертку в этой же схеме:

SQL> CREATE OR REPLACE PACKAGE demo_books_dao_wrapper IS
  2  
  3      PROCEDURE update_book(book_id BINARY_INTEGER, name VARCHAR2)
  4      AS LANGUAGE JAVA NAME 'my.demo.BooksDAOWrapper.update(int, java.lang.String)';
  5  
  6      PROCEDURE list_all_books AS LANGUAGE JAVA NAME 'my.demo.BooksDAOWrapper.listAll()';
  7  
  8  END demo_books_dao_wrapper;
  9  /
Package created

Вызовем процедуру list_all_books:

SQL> begin demo_books_dao_wrapper.list_all_books; end;
  2  /
Exception in thread "Root Thread" oracle.jdbc.driver.OracleSQLException: ORA-00942: table or view does not exist

...

 at my.demo.BooksDAO.listAll(BooksDAO:19)
 at my.demo.BooksDAOWrapper.listAll(BooksDAOWrapper:12)
begin demo_books_dao_wrapper.list_all_books; end;
ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-00942: table or view does not exist
ORA-06512: at "INTSTOCK.DEMO_BOOKS_DAO_WRAPPER", line 6
ORA-06512: at line 1

Мы получили ошибку "ORA-00942: table or view does not exis". Дело в том, что хранимый Java код, в отличие от хранимого PL/SQL кода, по умолчанию выполняется с правами вызывающего пользователя(!), а не с правами владельца. Метод BooksDAOWrapper.listAll() не видит таблицы books потому, что ее не видит пользователь ay2 - владелец класса BooksDAOWrapper.

Ситуацию можно исправить, предоставив пользователю ay2 права на чтение и изменение (для метода BooksDAOWrapper.update()) таблицы ay.books и создав соответствующий синоним:

-- ay
SQL> grant select, update on books to ay2;
Grant succeeded

-- ay2
SQL> CREATE SYNONYM books FOR ay.books;
Synonym created

Теперь:

SQL> begin demo_books_dao_wrapper.list_all_books; end;
  2  /
    1 : Книга рекордов Гиннеса
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

SQL> begin demo_books_dao_wrapper.update_book(1, 'Атлас мира'); end;
  2  /
PL/SQL procedure successfully completed

SQL> begin demo_books_dao_wrapper.list_all_books; end;
  2  /
    1 : Атлас мира
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

Итак, эксперименты с вызовами Java методов пользователем Oracle, который не является владельцем Java кода, показали

как использовать спецификацию резолвера в предложении CREATE AND RESOLVE JAVA SOURCE,
что Java код выполняется с правами вызывающего пользователя, а не с правами владельца кода.

Спецификация резолвера есть список пар (маска схема), согласно которому Oracle Java резолвер ищет классы-зависимости для данного класса. Спецификация резолвера для встроенной JVM Oracle выполняет функцию CLASSPATH, но определяется для каждого класса, в отличие от глобального списка CLASSPATH обычной JVM.

Найдем спецификацию резолвера для определенного нами класса в системном словаре Oracle:

-- ay2
SQL> SELECT * FROM All_Java_Resolvers WHERE owner=USER;

OWNER           NAME                           TERM_INDEX PATTERN      SCHEMA
--------------- ------------------------------ ---------- ------------ ---------------
AY2             my/demo/BooksDAOWrapper                 0 *            AY
AY2             my/demo/BooksDAOWrapper                 1 *            PUBLIC

Спецификации резолвера используются в предложениях CREATE JAVA, ALTER JAVA, и с утилитой loadjava. Несколько примеров спецификаций резолвера из документации Oracle:

-- искать в схеме SCOTT, затем в пакете my/gui в схеме OTHER, затем в схеме PUBLIC
((* SCOTT) ("my/gui/*" OTHER) (* PUBLIC))

-- искать в схеме SCOTT, в схеме PUBLIC, игнорировать ненайденные классы в пакете my/gui
((* SCOTT) (* PUBLIC) ("my/gui/*" -))

-- искать в схеме SCOTT, в схеме PUBLIC, игнорировать все ненайденные
((* SCOTT) (* PUBLIC) (* -))

Полезно запомнить, что

объект JAVA SOURCE приобретает статус VALID, если успешно откомпилирован;
объект JAVA CLASS приобретает статус VALID, если зависимости успешно разрешены;
объект JAVA RESOURCE всегда имеет статус VALID.

Для рассмотрения следующей темы вернусь к работе в схеме ay. Я собираюсь реализовать функциональность BooksDAO при помощи SQLJ.

SQLJ - это стандарт, разработанный в конце 1990-х при участии компаний IMB, Oracle, Sun и некоторых других. Реализация этого стандарта позволяет включать в исходный код на Jаva команды языка SQL. Все предложения SQLJ начинаются с префикса #sql. Препроцессор SQLJ производит чистый код Java, использующий JDBC.

SQLJ позволяет программисту писать меньше кода и делает программу более читабельной. Однако, SQLJ пригоден только для статических команд SQL. Если необходимо динамически конструировать и выполнять команды SQL, то нужно напрямую работать с JDBC API.

СУБД Oracle предоставляет

серверный внутренний препроцессор SQLJ, который преобразует предложения SQLJ в код Java, использующий серверный внутренний JDBC драйвер и соединение с БД по умолчанию;
утилиту командной строки sqlj, которая генерирует код Java, использующий обычный JDBC драйвер и параметризуемое соединение с БД.

Таким образом, исходный код SQLJ может быть без изменений откомпилирован на сервере и на клиенте, в результате чего в обоих случаях получится работающий байт-код.

В СУБД имеется также обычный тонкий (thin) драйвер, с помощью которого можно открывать соединения под другими пользователями и с другими серверами. Работа с тонким JDBC драйвером на сервере ничем не отличается от работы с клиентским тонким JDBC драйвером.

Итак, создадим класс BooksSQLJ, функционально аналогичный классу BooksDAO:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "my/demo/BooksSQLJ" AS
package my.demo;

public class BooksSQLJ
{
    #sql static iterator BooksIter (int bookId, String name);

    public static void update(int bookId, String name) throws java.sql.SQLException {
        #sql { UPDATE books SET name = :name WHERE book_id = :bookId };
    }

    public static void listAll() throws java.sql.SQLException {
        BooksIter results;
        #sql results = { SELECT book_id "bookId", name FROM books };
        while (results.next()) {
            System.out.println(String.format("%5d : %s", results.bookId(), results.name()));
        }
        results.close();
    }

}

Создам обертку для методов класса BooksSQLJ:

CREATE OR REPLACE PACKAGE demo_books_sqlj IS

    PROCEDURE update_book(book_id BINARY_INTEGER, name VARCHAR2)
    AS LANGUAGE JAVA NAME 'my.demo.BooksSQLJ.update(int, java.lang.String)';
  
    PROCEDURE list_all_books AS LANGUAGE JAVA NAME 'my.demo.BooksSQLJ.listAll()';

END demo_books_sqlj;
/

Протестирую работу класса BooksSQLJ так же, как тестировал работу класса BooksDAO:

SQL> set serveroutput on
SQL> call dbms_java.set_output(1000000);
Method called

SQL> begin demo_books_sqlj.list_all_books; end;
  2  /
    1 : Книга рекордов Гиннеса
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

SQL> begin demo_books_sqlj.update_book(1, 'Атлас мира'); end;
  2  /
PL/SQL procedure successfully completed

SQL> begin demo_books_sqlj.list_all_books; end;
  2  /
    1 : Атлас мира
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

Как видим, результат аналогичный.

SQL> ROLLBACK;
Rollback complete

SQL> begin demo_books_sqlj.list_all_books; end;
  2  /
    1 : Книга рекордов Гиннеса
    2 : Слово о полку Игореве
PL/SQL procedure successfully completed

Для того, чтобы выполнить явный COMMIT в методе BooksSQLJ.update() достаточно добавить в его конец строку

#SQL { COMMIT };

В заключение, удалю из БД все объекты, использовавшиеся в экспериментах.

-- ay2
SQL> DROP PACKAGE demo_books_dao_wrapper;
Package dropped

SQL> DROP JAVA SOURCE "my/demo/BooksDAOWrapper";
Java dropped

SQL> DROP SYNONYM books;
Synonym dropped

-- ay
SQL> DROP PACKAGE demo_books_dao;
Package dropped

SQL> DROP JAVA SOURCE "my/demo/BooksDAO";
Java dropped

SQL> DROP PACKAGE demo_books_sqlj;
Package dropped

SQL> DROP JAVA SOURCE "my/demo/BooksSQLJ";
Java dropped

SQL> DROP TABLE books;
Table dropped

/привет/мир/etc

четверг, 31 июля 2014 г.

Java в СУБД Oracle, часть III

Комментариев нет:

Отправить комментарий

четверг, 31 июля 2014 г.

Java в СУБД Oracle, часть III

Комментариев нет:

Отправить комментарий

четверг, 31 июля 2014 г.