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

В первой части статьи я перечислил основные различия традиционной виртуальной машины Java и JVM, встроенной в СУБД Oracle. А также создал в базе данных несколько Java классов и PL/SQL обертки для вызова их методов, и продемонстрировал передачу и возврат значений из PL/SQL в Java код и обратно. Во второй части будет показан альтернативный способ создания объектов JAVA CLASS, рассмотрено поведение статических переменных класса в течение сеанса Oracle, и реализован Java класс и соответствующая PL/SQL обертка для чтения/записи внешних файлов.

До сих пор мы создавали Java классы в схеме Oracle из исходного кода с помощью команды CREATE JAVA SOURCE. Альтернатива в том, чтобы загрузить в базу данных готовые Java классы с помощью утилиты loadjava.

Вначале создам файл DeepThought.java, содержащий одноименный класс, и откомпилирую его для JVM версии 1.5:

$ cat DeepThought.java

// DeepThought.java

public class DeepThought 
{
    private static int theAnswer = 42;
    
 public static void setTheAnswer(int answer)
 {
     theAnswer = answer;
 }
 
 public static int getTheAnswer()
 {
     return theAnswer;
 }
}

$ javac -target 1.5 DeepThought.java

$ ls
DeepThought.class    DeepThought.java

Успешно. Теперь загружу полученный DeepThought.class в базу данных утилитой loadjava. Опция -verbose позволяет увидеть, что же делает утилита:

$ loadjava -user ay/ay@may -resolve -verbose DeepThought.class
arguments: '-user' 'ay/***@may' '-resolve' '-verbose' 'DeepThought.class'
creating : class DeepThought
loading  : class DeepThought
resolving: class DeepThought
Classes Loaded: 1
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

Утилита loadjava позволяет загружать (и компилировать) java-файлы c исходным кодом, class-файлы (и разрешать внешние зависимости классов) и файлы ресурсов.

Создам обертку для метода DeepThought.getTheAnswer() и получу ответ на главный вопрос о жизни, Вселенной и обо всем:

SQL> CREATE OR REPLACE FUNCTION deep_thought_get_the_answer RETURN NUMBER
  2  AS LANGUAGE JAVA NAME 'DeepThought.getTheAnswer() return int';
  3  /
Function created

SQL> show error
No errors 

SQL> SELECT deep_thought_get_the_answer FROM dual;

DEEP_THOUGHT_GET_THE_ANSWER
---------------------------
                         42

(Сейчас можно взять паузу и помедитировать над числом 42 :-)

В нашем классе DeepThought имеется метод setTheAnswer(int), который позволяет изменить значение статической переменной theAnswer. Воспользуемся этим методом, чтобы продемонстрировать два свойства встроенной JVM Oracle:

• каждый сеанс Oracle имеет дело с собственным экземпляром JVM и собственными экземплярами загруженных классов,
• статические данные классов сохранются между вызовами методов Java на протяжении сеанса Oracle.

Создам обертку для метода DeepThought.setTheAnswer() и изменю с его помощью значение переменной theAnswer:

SQL> CREATE OR REPLACE PROCEDURE deep_thought_set_the_answer(answer NUMBER)
  2  AS LANGUAGE JAVA NAME 'DeepThought.setTheAnswer(int)';
  3  /
Procedure created

SQL> show error
No errors

SQL> exec deep_thought_set_the_answer(43)
PL/SQL procedure successfully completed

SQL> SELECT deep_thought_get_the_answer FROM dual;
DEEP_THOUGHT_GET_THE_ANSWER
---------------------------
                         43

В другом сеансе.

SQL> SELECT deep_thought_get_the_answer FROM dual;
DEEP_THOUGHT_GET_THE_ANSWER
---------------------------
                         42

SQL> exec deep_thought_set_the_answer(41)
PL/SQL procedure successfully completed

SQL> SELECT deep_thought_get_the_answer FROM dual;
DEEP_THOUGHT_GET_THE_ANSWER
---------------------------
                         41

И снова в первом сеансе.

SQL> SELECT deep_thought_get_the_answer FROM dual;
DEEP_THOUGHT_GET_THE_ANSWER
---------------------------
                         43

Как видим, в разных сеансах функция deep_thought_get_the_answer дает разный ответ, и этот ответ остается неизменным от вызова к вызову (если он не изменен процедурой deep_thought_set_the_answer). Последнее доказывает, что время жизни JVM и статических переменных не ограничивается единственным вызовом Java метода. Статические переменные Java, как и переменные пакетов PL/SQL, сохраняют свои значения между обращениями к ним в течение всего сеанса работы с Oracle.

Удалить теперь уже ненужный класс DeepThought можно либо с помощью команды DROP JAVA CLASS "DeepThought";, либо с помощью утилиты dropjava. Воспользуюсь утилитой dropjava:

$ dropjava -user ay/ay@may -verbose DeepThought
dropping: class DeepThought

PL/SQL обертки методов этого класса также больше нам не нужны:

SQL> DROP PROCEDURE deep_thought_set_the_answer;
Procedure dropped

SQL> DROP FUNCTION deep_thought_get_the_answer;
Function dropped

Теперь обратимся к задаче записи и чтения внешних файлов из СУБД Oracle. Обычно эта задача решается при помощи пакета UTL_FILE. (Да вот беда, время от времени с ним случаются необъяснимые сбои! UTL_FILE явно не любит некоторые символы, особенно, если они стоят в конце строки. При интенсивной эксплуатации UTL_FILE сбои возникают регулярно.) Создадим альтернативное решение для работы с внешними файлами - на базе стандартной библиотеки Java.

Класс TextFileWizard, код которого представлен ниже, пишет и читает текстовые файлы в указанной кодировке с помощью java.io.BufferedWriter и java.io.BufferedReader, соответственно. Приведенная реализация позволяет одновременно писать не более одного файла и читать не более одного файла (из-за того, что ссылки на открытые файлы хранятся в статических переменных класса bufferedWriter и bufferedReader). Желающие снять это ограничение могут доработать код самостоятельно.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "TextFileWizard" AS
public class TextFileWizard
{
    private static java.io.BufferedWriter bufferedWriter = null;
    private static java.io.BufferedReader bufferedReader = null;

    
    public static int openOutputFile(String file) throws java.io.IOException
    {
        return openOutputFile(file, "UTF-8");
    }
    
    public static int openOutputFile(String file, String encoding) throws java.io.IOException
    {
        if (bufferedWriter == null) {
            java.io.OutputStreamWriter osw = 
                new java.io.OutputStreamWriter(new java.io.FileOutputStream(file), encoding);
            bufferedWriter = new java.io.BufferedWriter(osw);
            return bufferedWriter.hashCode();
        } else {
            return -1;
        }
    }
    
    public static int closeOutputFile(int fileHandler) throws java.io.IOException 
    {
        if (bufferedWriter != null && bufferedWriter.hashCode() == fileHandler) {
            bufferedWriter.close();
            bufferedWriter = null;
            return 1;
        } else {
            return -1;
        }
    }

    public static int openInputFile(String file) throws 
        java.io.FileNotFoundException, java.io.UnsupportedEncodingException 
    {
        return openInputFile(file, "UTF-8");
    }
    
    public static int openInputFile(String file, String encoding) throws
        java.io.FileNotFoundException, java.io.UnsupportedEncodingException
    {
        if (bufferedReader == null) {
            java.io.InputStreamReader fr = 
                new java.io.InputStreamReader(new java.io.FileInputStream(file), encoding);
            bufferedReader = new java.io.BufferedReader(fr);
            return bufferedReader.hashCode();
        } else {
            return -1;
        }
    }

    public static int closeInputFile(int fileHandler) throws java.io.IOException
    {
        if (bufferedReader != null && bufferedReader.hashCode() == fileHandler) {
            bufferedReader.close();
            bufferedReader = null;
            return 1;
        } else {
            return -1;
        }
    }

    public static void write(int fileHandler, String line) throws java.io.IOException 
    {
        if (bufferedWriter != null && bufferedWriter.hashCode() == fileHandler) {
            bufferedWriter.write(line);
        }
    }

    public static String read(int fileHandler) throws java.io.IOException 
    {
        if (bufferedReader != null && bufferedReader.hashCode() == fileHandler) {
            return bufferedReader.readLine();
        } else {
            return null;
        }
    }
}
/

Для того, чтобы метод Java, выполняемый от имени пользователя Oracle, мог работать с внешними файлами, нужно предоставить необходимые привилегии этому пользователю. Это делается с помощью пакета dbms_java:

SQL> begin
  2      dbms_java.grant_permission(user, 'SYS:java.io.FilePermission', '/home/oracle/folder/-', 'read,write');
  3  end;
  4  /
PL/SQL procedure successfully completed

Теперь текущий пользователь может читать и писать файлы в директории /home/oracle/folder и ее поддиректориях.

PL/SQL пакет tfw предоставляет методы-обертки для методов класса TextFileWizard, а также содержит процедуру testme для тестирования его основной функциональности:

CREATE OR REPLACE PACKAGE tfw IS

    FILEPATH CONSTANT VARCHAR2(255) := '/home/oracle/folder';
    CHARSET CONSTANT VARCHAR2(50) := 'CP1251';
    
    CR CONSTANT CHAR(1) := chr(13);
    LF CONSTANT CHAR(1) := chr(10);
    CRLF CONSTANT CHAR(2) := CR||LF;
    NL CONSTANT VARCHAR2(2) := CR||LF;

    -- open file p_filename for write
    -- in p_dir
    -- using charset p_charset
    FUNCTION open_output_file(
        p_filename VARCHAR2,
        p_dir IN VARCHAR2 DEFAULT tfw.FILEPATH,
        p_charset IN VARCHAR2 DEFAULT tfw.CHARSET)
    RETURN BINARY_INTEGER;
    
    PROCEDURE write_line(
        p_filehandler BINARY_INTEGER,
        p_line VARCHAR2,
        p_nl VARCHAR2 DEFAULT tfw.NL);
    
    FUNCTION close_output_file(p_filehandler BINARY_INTEGER) RETURN BINARY_INTEGER;

    -- open file p_filename for read
    -- in p_dir
    -- using charset p_charset
    FUNCTION open_input_file(
        p_filename VARCHAR2,
        p_dir IN VARCHAR2 DEFAULT tfw.FILEPATH,
        p_charset IN VARCHAR2 DEFAULT tfw.CHARSET)
    RETURN BINARY_INTEGER;

    FUNCTION read_line(p_filehandler BINARY_INTEGER) RETURN VARCHAR2;

    FUNCTION close_input_file(p_filehandler BINARY_INTEGER) RETURN BINARY_INTEGER;

    PROCEDURE testme;
END tfw;
/
CREATE OR REPLACE PACKAGE BODY tfw IS

    FUNCTION j_open_output_file(p_filename VARCHAR2, p_encoding VARCHAR2) RETURN BINARY_INTEGER
    AS LANGUAGE JAVA
    NAME 'TextFileWizard.openOutputFile(java.lang.String, java.lang.String) return int';

    FUNCTION open_output_file(
        p_filename VARCHAR2,
        p_dir IN VARCHAR2 DEFAULT tfw.FILEPATH,
        p_charset IN VARCHAR2 DEFAULT tfw.CHARSET)
    RETURN BINARY_INTEGER
    IS
        l_filename VARCHAR2(1000) :=
            CASE
            WHEN p_dir IS NOT NULL THEN
                p_dir || '/' || p_filename
            ELSE
                p_filename
            END;
    BEGIN
        RETURN j_open_output_file(l_filename, p_charset);
    END open_output_file;

    PROCEDURE j_write_to_file(p_filehandler BINARY_INTEGER, p_line VARCHAR2)
    AS LANGUAGE JAVA
    NAME 'TextFileWizard.write(int, java.lang.String)';
    
    PROCEDURE write_line(
        p_filehandler BINARY_INTEGER,
        p_line VARCHAR2,
        p_nl VARCHAR2 DEFAULT tfw.NL)
    IS
    BEGIN
        j_write_to_file(p_filehandler, p_line || p_nl);
    END write_line;

    FUNCTION close_output_file(p_filehandler BINARY_INTEGER) RETURN BINARY_INTEGER
    AS LANGUAGE JAVA
    NAME 'TextFileWizard.closeOutputFile(int) return int';

    
    FUNCTION j_open_input_file(p_filename VARCHAR2, p_encoding VARCHAR2) RETURN BINARY_INTEGER
    AS LANGUAGE JAVA
    NAME 'TextFileWizard.openInputFile(java.lang.String, java.lang.String) return int';

    FUNCTION open_input_file(
        p_filename VARCHAR2,
        p_dir IN VARCHAR2 DEFAULT tfw.FILEPATH,
        p_charset IN VARCHAR2 DEFAULT tfw.CHARSET)
    RETURN BINARY_INTEGER
    IS
        l_filename VARCHAR2(1000) :=
            CASE
            WHEN p_dir IS NOT NULL THEN
                p_dir || '/' || p_filename
            ELSE
                p_filename
            END;
    BEGIN
        RETURN j_open_input_file(l_filename, p_charset);
    END open_input_file;

    FUNCTION read_line(p_filehandler BINARY_INTEGER) RETURN VARCHAR2
    AS LANGUAGE JAVA
    NAME 'TextFileWizard.read(int) return java.lang.String';

    FUNCTION close_input_file(p_filehandler BINARY_INTEGER) RETURN BINARY_INTEGER
    AS LANGUAGE JAVA
    NAME 'TextFileWizard.closeInputFile(int) return int';
        

    PROCEDURE p(message VARCHAR2) IS BEGIN dbms_output.put_line(message); END;

    PROCEDURE testme IS
        fh BINARY_INTEGER := NULL;
        line VARCHAR2(10000);
        FILENAME1251 VARCHAR2(100) := 'testme_1251.txt';
        FILENAMEUTF8 VARCHAR2(100) := 'testme_utf8.txt';
    BEGIN
        -- writing to file in default encoding
        fh := tfw.open_output_file(FILENAME1251);
        p('out : ' || fh);
        FOR r IN (SELECT username FROM all_users WHERE rownum < 6) LOOP
            tfw.write_line(fh, 'Привет, ' || r.username);
        END LOOP;
        fh := tfw.close_output_file(fh);
        p('out : ' || fh);

        -- writing to file in UTF-8 encoding
        fh := tfw.open_output_file(FILENAMEUTF8, tfw.FILEPATH, 'UTF-8');
        p('out : ' || fh);
        FOR r IN (SELECT username FROM all_users WHERE rownum < 6) LOOP
            tfw.write_line(fh, 'Привет, ' || r.username);
        END LOOP;
        fh := tfw.close_output_file(fh);
        p('out : ' || fh);

        -- reading from file in default encoding
        fh := tfw.open_input_file(FILENAME1251);
        p(' in : ' || fh);
        LOOP
            line := tfw.read_line(fh);
            EXIT WHEN line IS NULL;
            p(line);
        END LOOP;
        fh := tfw.close_input_file(fh);
        p(' in : ' || fh);

        -- opening non-existent file for read
        BEGIN
            fh := tfw.open_input_file('non-existant');
        EXCEPTION
        WHEN OTHERS THEN
            p(SQLERRM(SQLCODE));
        END;
    END;

END tfw;
/

Протестирую запись-чтение файлов:

SQL> set serveroutput on

SQL> exec tfw.testme
out : -1403543160
out : 1
out : -1762102103
out : 1
 in : 462025015
Привет, SYS
Привет, SYSTEM
Привет, OUTLN
Привет, DIP
Привет, ORACLE_OCM
 in : 1
ORA-29532: Java call terminated by uncaught Java exception: java.io.FileNotFoundException: No such file or directory

PL/SQL procedure successfully completed

Итак, благодаря встроенной JVM прорублено окошко из СУБД Oracle в файловую систему, альтернативное UTL_FILE.

Документация Oracle сообщает интересную подробность, относящуюся к работе с внешними файлами:

Files, threads, and sockets persist across calls when you use a dedicated mode server. In shared server mode, files, threads, and sockets terminate when the call ends.

Это значит, что если сервер Oracle сконфигурирован для работы в режиме shared server mode (когда пользовательские запросы выполняются серверным процессом, произвольно выбираемым из пула предварительно запущенных процессов-серверов), то файл, открытый в Java методе, будет принудительно закрыт, как только запрос пользователя будет обработан процессом-сервером. Отсюда вывод: в режиме shared server mode открывайте, пишите/читайте и закрывайте файл в пределах одного запроса к серверу, например, в пределах вызывемой PL/SQL процедуры. И не рассчитывайте, что файл, открытый в одном запросе(вызове), все еще открыт при следующем запросе(вызове) в том же самом сеансе.

В следующий я раз рассмотрю работу хранимого Java кода с данными и объектами БД при помощи серверного JDBC драйвера. Исходный код будет использовать стандарт SQLJ и будет обработан SQLJ препроцессором. Попутно познакомимся с работой резолвера (resolver) и разберемся, с какими правами выполняется Java код внутри СУБД. Будет интересно запустить один и тот же код внутри СУБД и на традиционной JVM, с традиционным JDBC драйвером.