Oracle SQL und PL/SQL ...: Von Java nach SQL: Java-Objekte und die Datenbank-JVM

In der Vergangenheit hatte ich ja schon einige Blog-Postings zum Thema "Java in der Datenbank"; das letzte Posting zum Thema Twitter-Postings mit der Datenbank, die diversen Postings zum Thema Dateisystem-Zugriffe haben alle eines gemein: Sie verwenden die in der Datenbank enthaltene Java-Engine. Wenn es aber darum geht, Java-Funktionen aus PL/SQL oder SQL heraus zu nutzen, ist immer auch eine Parameter-Mapping gefragt. Und genau dem möchte ich mich heute widmen. Dabei geht es mir aber nicht um das Abbilden eines VARCHAR2, NUMBER oder DATE auf Java-Datentypen - das ist sehr einfach, wie man hier sehen kann ...

create or replace and compile java source named simple_test as

public class SimpleTest {
  public static String getString() {
    return "Hallo Welt";
  }
  public static java.sql.Timestamp getDate() {
    return new java.sql.Timestamp(new java.util.Date().getTime());
  }
  public static int getNumber() {
    return 4711;
  }
} 
/
sho err

create or replace package simple_test_plsql is
  function get_string return varchar2;
  function get_date return date;
  function get_number return number;
end simple_test_plsql;
/
sho err

create or replace package body simple_test_plsql is
  function get_string return varchar2 as 
  language java name 'SimpleTest.getString() return java.lang.String';
  function get_date return date as
  language java name 'SimpleTest.getDate() return java.sql.Timestamp';
  function get_number return number as
  language java name 'SimpleTest.getNumber() return int';
end simple_test_plsql;
/
sho err

select 
  simple_test_plsql.get_string,
  simple_test_plsql.get_date,
  simple_test_plsql.get_number
from dual
/

GET_STRING           GET_DATE            GET_NUMBER
-------------------- ------------------- ----------
Hallo Welt           21.09.2011 09:19:40       4711

Den Experten ist sicher schon aufgefallen, dass die Java-Methoden alle "static" sind; das ist auch logisch so, denn PL/SQL ist ja keine objektorientierte Sprache. Mit "statischen" Java-Methoden wird Java wie eine prozedurale Sprache genutzt - es entspricht eher dem Konzept von PL/SQL, daher können nur statische Java-Methoden auf PL/SQL-Pakete, Prozeduren oder Funktionen abgebildet werden.

Das einzige, wo man ein wenig aufpassen muss, sind DATE-Mappings - diese können auf die Klassen java.sql.Date und java.sql.Timestamp abgebildet werden. In Java bedeutet das aber etwas anderes als in SQL. Arbeitet man beim Mapping mit java.sql.Date, dann werden nur Tag, Monat und Jahr an die SQL-Ebene zurückgegeben - also das Datum - möchte man die Uhrzeit haben, sollte man mit java.sql.Timestamp arbeiten. Aber abgesehen davon ist das Mapping solcher Datentypen ja wirklich einfach - und wenn man Java-Bibliotheken in der Datenbank verwendet, sollte man sich am besten stets eine eigene "Schicht" mit Java-Methoden schreiben, die einfach auf SQL und PL/SQL abzubilden sind - wo die Methodensignaturen also am besten nur solche einfachen Datentypen nutzen und keine komplexen Objekte.

Aber jetzt geht's ans Eingemachte: Angenommen, wir haben eine Java-Bibliothek (und als Beispiel nehmen wir mal java.io.File), die ein komplexes Objekt repräsentiert. Und wir möchten nun eben nicht für jedes Attribut einen eigenen Call bauen, sondern alle relevanten Attribute mit einem einzigen Call abholen. Uns prinzipiell gibt es in der Datenbank ja auch Objekttypen bzw. User Defined Types, mit denen man sowas wie ein "File" modellieren kann. Also fangen wir mal damit an.

create type file_t as object(
  file_path      varchar2(4000),
  file_name      varchar2(4000),
  file_size      number,
  last_modified  date,
  is_dir         char(1),
  is_writeable   char(1),
  is_readable    char(1),
  file_exists    char(1)
)
/

Allerdings kann man nun keine direkte Verbindung zwischen dem SQL-Typen FILE_T und der Java-Klasse java.io.File herstellen - beide haben ja überhaupt nichts miteinander zu tun - ein Mapping ist aber mit Hilfe von einer "Java-Brückenklasse" möglich: Mit oracle.sql.STRUCT kann man Objekttypen aus Java heraus erstellen und an SQL bzw. PL/SQL zurückgeben. Was wir also brauchen, ist eine (statische) Java-Methode, die mit Hilfe von java.io.File die Information zu einer Datei holt, damit ein STRUCT für den FILE_T erzeugt und diese kann dann in die SQL-Ebene zurückgibt.

create or replace and compile java source named java_file as
import java.math.*;
import java.util.*;
import java.io.*;

import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class JavaFile {
  public static STRUCT getFile(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    Object[]         o      = new Object[8];

    File f = new File(pFile);
    if (f.exists()) {
     o[0] = f.getPath();
     o[1] = f.getName();
     o[2] = new BigDecimal(f.length());
     o[3] = new java.sql.Timestamp(f.lastModified());
     o[4] = (f.isDirectory()?"Y":"N");
     o[5] = (f.canWrite()?"Y":"N");
     o[6] = (f.canRead()?"Y":"N");
     o[7] = "Y";
     return new STRUCT(sDescr, con, o);
    } else {
     return null;
    } 
  }
}
/
sho err

create or replace function get_file_atts (p_file in varchar2) return file_t
is language java name 'JavaFile.getFile(java.lang.String) return oracle.sql.STRUCT';
/
sho err

Hier muss der Java-Code mit der SQL-Ebene zusammenspielen - deshalb wird zuerst eine JDBC-"Verbindung" aufgebaut - das ist aber nichts weiter als eine Art "Pointer" auf die Datenbanksitzung, denn das Java läuft ja bereits in der Datenbank. Es wird ein StructDescriptor-Objekt erzeugt, welches auf den vorher erzeugten Typen FILE_T zeigt. Alle Attribute des FILE_T werden auf Java-Seite in einem Array der Klasse Object[] abgelegt. Hier muss man als Entwickler aufpassen, dass die verwendeten Java-Typen auf die Datentypen des SQL-Typen passen (siehe einfache Mappings oben). Mit diesem Array, dem StructDescriptor-Objekt und dem Connection-Objekt wird dann ganz zum Schluß ein STRUCT-Objekt generiert, welches genau auf den Typen FILE_T passt. Natürlich kann man auch komplexere Dinge bauen und ein "STRUCT in ein STRUCT" schachteln, man muss nur aufpassen, dass alles zur Definition der Objekttypen in SQL passt.

Zum Abschluß kommt wieder die PL/SQL-Definition der Funktion - in PL/SQL wird als IN Parameter ein VARCHAR2 und als Rückgabewert ein FILE_T deklariert. Folgerichtig passt das auf ein java.lang.String als Eingabe- und ein oracle.sql.STRUCT als Rückgabewert. Alle Objekttypen werden auf Java-Seite als oracle.sql.STRUCT abgebildet - die Verknüpfung mit dem konkreten Objekttypen erledigt der StructDescriptor ...

Alles klar? Dann können wir testen ...

SQL> select get_file_atts('/') from dual;
select get_file_atts('/') from dual
                               *
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.security.AccessControlException: the Permission (java.io.FilePermission /
read) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '/', 'read'
)

Ach ja: Zum Dateisystemzugriff braucht es Privilegien - die muss der DBA einräumen. Wenn Ihr vor diesen Meldungen Ruhe haben wollt, gebt eurem Datenbankschema das JAVASYSPRIV-Privileg, dann har er alle Rechte, die man haben kann (für Produktion nicht zu empfehlen). Alternativ könnt Ihr einfach den in der Fehlermeldung dargestellten Aufruf ausführen - der räumt genau das fehlende Privileg ein. Wenn Ihr das Privileg habt, probiert es nochmal ...

SQL> select get_file_atts('/tmp') from dual;

GET_FILE_ATTS('/TMP')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DIR, IS
--------------------------------------------------------------------------------
FILE_T('/tmp', 'tmp', 126976, '21.09.2011 10:25:22', 'Y', 'Y', 'Y', 'Y')

1 Zeile wurde ausgewählt.

So weit so gut. Wir haben es also geschafft, ein strukturiertes Objekt von Java nach PL/SQL zu übertragen. Analog dazu kann man nun für alle Objekte vorgehen:

SQL-Objekttypen erzeugen
Java Methode erzeugen, die das eigentliche Java-Objekt auf eine STRUCT-Instanz abbildet, dabei mit dem StructDescriptor und dem Object[]-Array arbeiten
STRUCT-Instanz aus Java zurückgeben und in SQL übernehmen
PL/SQL Wrapper für die neue Java Stored Procedure erstellen

Bleibt die nächste (und im Datenbankumfeld spannende) Aufgabe: Ich möchte ein Directory-Listing abbilden, also eine ganze Reihe von strukturierten Objekten zurückgeben. Im reinen PL/SQL geht haben wir hierfür die Table Functions - und ein ähnliches Konzept nutzen wir auch in Java. Zunächst brauchen wir, wie bei der PL/SQL Table Function, einen Objekttypen, der die Dateiliste repräsentiert - das ist einfach:

create type file_ct as table of file_t
/

Und wieder gilt es, aus Java heraus eine Instanz dieses Typs FILE_CT zu erzeugen. Für Varray- oder Table Types gibt es jedoch eine andere Java-"Brückenklasse": oracle.sql.ARRAY. Der Umgang damit ist aber ganz ähnlich wie bei der Klasse oracle.sql.STRUCT. Es wird ein ArrayDescriptor-Objekt benötigt, der die Verbindung zum konkreten SQL-Typen herstellt, und mit diesem, dem Connection-Objekt und einem Standard-Java-Array wird die Instanz vom Typ oracle.sql.ARRAY erzeugt, die genau auf den FILE_CT passt. Das ganze als Code ...

public class JavaFile {
  public static ARRAY getFileList(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    ArrayDescriptor  aDescr = ArrayDescriptor.createDescriptor("FILE_CT", con);
    Object[]         o      = new Object[8];

    /* Array containing java File objects */
    File[]   f = new File(pFile).listFiles();

    /* Array containing SQL STRUCT objects */
    STRUCT[] a = new STRUCT[f.length];

    /* now loop through the File array and create a STRUCT instance for each file */
    for (int i=0;i<f.length;i++) {
     o[0] = f[i].getPath();
     o[1] = f[i].getName();
     o[2] = new BigDecimal(f[i].length());
     o[3] = new java.sql.Timestamp(f[i].lastModified());
     o[4] = (f[i].isDirectory()?"Y":"N");
     o[5] = (f[i].canWrite()?"Y":"N");
     o[6] = (f[i].canRead()?"Y":"N");
     o[7] = "Y"
     a[i] = new STRUCT(sDescr, con, o);
    } 
    /* Create and return the ARRAY object which maps to the SQL type */
    return new ARRAY(aDescr, con, a);
  }
}
/
sho err

create or replace function get_file_list (p_file in varchar2) return file_ct
is language java name 'JavaFile.getFileList(java.lang.String) return oracle.sql.ARRAY';
/
sho err

Und das ist im Grunde genommen nur eine Erweiterung obigen Codes. Durch die Liste der File-Objekte, die von java.io.File.listFiles() zurückgegeben wird, laufen wir in einer Schleife durch, erzeugen für jedes File-Objekt eine STRUCT-Instanz und packen auch diese in ein Array. Das wird dann mit dem ArrayDescriptor auf den SQL-Typen FILE_CT abgebildet und als oracle.sql.ARRAY-Objekt zurückgegeben. Fertig - Test.

SQL> select file_name, last_modified, file_size from table(get_file_list('/'))

FILE_NAME                 LAST_MODIFIED        FILE_SIZE
------------------------- ------------------- ----------
wget-log                  27.04.2011 16:24:02        496
boot                      02.02.2011 13:22:58       1024
misc                      25.07.2011 09:29:09          0
stage                     04.04.2011 11:27:57          7
lib                       15.07.2011 11:06:27       4096
etc                       25.07.2011 09:29:07       4096
root                      15.07.2011 11:14:01       4096
bin                       02.02.2011 13:28:45       4096
:                         :                            :

Eigentlich ganz einfach, oder? Mit dem hier vorgestellten lässt sich nun jede beliebige Java-Bibliothek in der Datenbank nutzen - die einzige Voraussetzung ist, dass sie keinen Native-Code verwendet - nur 100%-Java-Bibliotheken laufen in der Datenbank. Zum Ansprechen aus SQL und PL/SQL überlegt man sich dann eine vernünftige Schnittstelle, erzeugt die passenden Java-Klassen, die entweder einfache Datentypen (String, Date, numerische) oder komplexe Datentypen als STRUCT oder ARRAY zurückgeben. Diese werden dann auf SQL-Ebene durch PL/SQL-Packages und Objekttypen repräsentiert. Die Code-Packages zum Dateisystem-Zugriff, zum Umgang mit einem POP3- oder IMAP-Mailserver oder zum Ein- und Auspacken von ZIP-Archiven arbeiten alle genau so - und wie gesagt: Jede andere Java-Bibliothek lässt sich genauso einbinden. Damit gibt es keine Grenzen in der Oracle-Datenbank.

In the past I had quite a number of postinhs in which I made use of the Java engine within the Oracle Database. Examples are the previous posting about automated tweets from PL/SQL (which was about using "twitter4j" in the database) or the postings about File- and operating system access. Today I'd like to elaborate a bit about a fundamental thing which one has to accomplish when using Java in the database: The parameter mapping. This posting is about how to map input or return paramaters in a java method to SQL and PL/SQL types in a package. And the focus will not be on the simple mapping of VARCHAR2, NUMBER or DATE datatypes ... these are rather simple, as we can see with this code example ...

create or replace and compile java source named simple_test as

public class SimpleTest {
  public static String getString() {
    return "Hello World";
  }
  public static java.sql.Timestamp getDate() {
    return new java.sql.Timestamp(new java.util.Date().getTime());
  }
  public static int getNumber() {
    return 4711;
  }
} 
/
sho err

create or replace package simple_test_plsql is
  function get_string return varchar2;
  function get_date return date;
  function get_number return number;
end simple_test_plsql;
/
sho err

create or replace package body simple_test_plsql is
  function get_string return varchar2 as 
  language java name 'SimpleTest.getString() return java.lang.String';
  function get_date return date as
  language java name 'SimpleTest.getDate() return java.sql.Timestamp';
  function get_number return number as
  language java name 'SimpleTest.getNumber() return int';
end simple_test_plsql;
/
sho err

select 
  simple_test_plsql.get_string,
  simple_test_plsql.get_date,
  simple_test_plsql.get_number
from dual
/

GET_STRING           GET_DATE            GET_NUMBER
-------------------- ------------------- ----------
Hello World          21.09.2011 09:19:40       4711

And now we'll handle these: Let's assume we have a Java library doing some stuff and for this example we use the java.io.File class (which is part of standard Java). We'd like to access file attributes with SQL functions and we don't want to have a single call for each attribute. We need a SQL function which collects all attributes in one call - so we need a datatype containing all these attributes at the SQL side. We have Object types (or User-Defined-Types) for that purpose so the first thing we want to do is to create an object type representing a file.

create type file_t as object(
  file_path      varchar2(4000),
  file_name      varchar2(4000),
  file_size      number,
  last_modified  date,
  is_dir         char(1),
  is_writeable   char(1),
  is_readable    char(1),
  file_exists    char(1)
)
/

So far we have a Java representation for a file: java.io.File - and we have a SQL representation for a file: Our new FILE_T. But there is absolutly no connection between those. Of course, we cannot map the java.io.File class to our FILE_T type - the database has no clue how to map the attributes. We need to build a "bridge" between java.io.File and FILE_T. And this bridge is a special java class: oracle.sql.STRUCT. So we now need to implement a static Java method (we can only use static methods) which uses java.io.File to collect file attributes and builds a oracle.sql.STRUCT instance which can be mapped to FILE_T. This code goes here ...

create or replace and compile java source named java_file as
import java.math.*;
import java.util.*;
import java.io.*;

import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class JavaFile {
  public static STRUCT getFile(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    Object[]         o      = new Object[8];

    File f = new File(pFile);
    if (f.exists()) {
     o[0] = f.getPath();
     o[1] = f.getName();
     o[2] = new BigDecimal(f.length());
     o[3] = new java.sql.Timestamp(f.lastModified());
     o[4] = (f.isDirectory()?"Y":"N");
     o[5] = (f.canWrite()?"Y":"N");
     o[6] = (f.canRead()?"Y":"N");
     o[7] = "Y";
     return new STRUCT(sDescr, con, o);
    } else {
     return null;
    } 
  }
}
/
sho err

create or replace function get_file_atts (p_file in varchar2) return file_t
is language java name 'JavaFile.getFile(java.lang.String) return oracle.sql.STRUCT';
/
sho err

Got it ...? Then try it!

SQL> select get_file_atts('/') from dual;
select get_file_atts('/') from dual
                               *
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.security.AccessControlException: the Permission (java.io.FilePermission /
read) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '/', 'read'
)

Ah, yes: You need Java privileges in order to access the file system with a Java stored porcedure. In the developing stage you might grant yourself the JAVASYSPRIV privilege - you will be a "Java superuser" then - on production systems this is not recommended you might grant individual privileges there. The practical bit is that Oracle not only throws the error message - it also gives the complete PL/SQL call to grant the required permission. So when you have the privilege, try again ...

SQL> select get_file_atts('/tmp') from dual;

GET_FILE_ATTS('/TMP')(FILE_PATH, FILE_NAME, FILE_SIZE, LAST_MODIFIED, IS_DIR, IS
--------------------------------------------------------------------------------
FILE_T('/tmp', 'tmp', 126976, '21.09.2011 10:25:22', 'Y', 'Y', 'Y', 'Y')

1 row selected.

So far - so good. We managed to create a structured object in java and pass it to PL/SQL. You can use this approach for any kind of structured object - and yes: You can nest one oracle.sql.STRUCT within another oracle.sql.STRUCT - just as you can nest object types in each other. The basic steps are:

Create the SQL object types
Create the static Java method which creates the STRUCT instance matching the object type using the Object[] array and the StructDescriptor class.
Return the STRUCT instance from java to SQL and PL/SQL
Create the PL/SQL wrapper for your Java stored procedure

Then we'll move on to the next (and more interesting) level: We want to pass not only a structured object but a list of structured objects from Java to PL/SQL. In the PL/SQL world we have Table Functions for that purpose - and as with these we now first create another object type representing our list of files ...

create type file_ct as table of file_t
/

And again: We now need to create an object within Java which maps to this FILE_CT type. But for Varray or table types there is another "Bridging class": oracle.sql.ARRAY. It's used the same way as oracle.sql.STRUCT. We first create an ArrayDescriptor instance using the JDBC connection and the type name. This helper object, the JDBC connection and a plain Java array are being used to create the actual instance of oracle.sql.ARRAY. And the code goes here ...

public class JavaFile {
  public static ARRAY getFileList(String pFile) throws Exception {
    Connection       con    = DriverManager.getConnection("jdbc:default:connection:");
    StructDescriptor sDescr = StructDescriptor.createDescriptor("FILE_T", con);
    ArrayDescriptor  aDescr = ArrayDescriptor.createDescriptor("FILE_CT", con);
    Object[]         o      = new Object[8];

    /* Array containing java File objects */
    File[]   f = new File(pFile).listFiles();

    /* Array containing SQL STRUCT objects */
    STRUCT[] a = new STRUCT[f.length];

    /* now loop through the File array and create a STRUCT instance for each file */
    for (int i=0;i<f.length;i++) {
     o[0] = f[i].getPath();
     o[1] = f[i].getName();
     o[2] = new BigDecimal(f[i].length());
     o[3] = new java.sql.Timestamp(f[i].lastModified());
     o[4] = (f[i].isDirectory()?"Y":"N");
     o[5] = (f[i].canWrite()?"Y":"N");
     o[6] = (f[i].canRead()?"Y":"N");
     o[7] = "Y"
     a[i] = new STRUCT(sDescr, con, o);
    } 
    /* Create and return the ARRAY object which maps to the SQL type */
    return new ARRAY(aDescr, con, a);
  }
}
/
sho err

create or replace function get_file_list (p_file in varchar2) return file_ct
is language java name 'JavaFile.getFileList(java.lang.String) return oracle.sql.ARRAY';
/
sho err

As you see: This code is basically just a small extension of the code above. We loop through the File[] array returned by java.io.File.listFiles(), create an oracle.sql.STRUCT instance for each java.io.File object and place it into a Java array (STRUCT[]). This java array is then being used to create the oracle.sql.ARRAY instance, which is finally passed back to PL/SQL. On the PL/SQL side we create the wrapper function and that's it. Try ...

SQL> select file_name, last_modified, file_size from table(get_file_list('/'))

FILE_NAME                 LAST_MODIFIED        FILE_SIZE
------------------------- ------------------- ----------
wget-log                  27.04.2011 16:24:02        496
boot                      02.02.2011 13:22:58       1024
misc                      25.07.2011 09:29:09          0
stage                     04.04.2011 11:27:57          7
lib                       15.07.2011 11:06:27       4096
etc                       25.07.2011 09:29:07       4096
root                      15.07.2011 11:14:01       4096
bin                       02.02.2011 13:28:45       4096
:                         :                            :

4. Oktober 2011

Von Java nach SQL: Java-Objekte und die Datenbank-JVM

Keine Kommentare:

Beliebte Postings

Links ...

Blogroll ....

Deutschsprachige APEX und PL/SQL Community

Google+ Followers

Carsten Czarski

Disclaimer

Label Cloud

Blog-Archiv