Artikel JavaMagazin - reeCon IT

Artikel JavaMagzin 10/2010
Kommunikation zwischen ORACLE und Non-ORACLE
Datenbanken
oder
Mit JEE eine ORACLE-Brücke bauen
Der Datenaustausch zwischen ORACLE Datenbanken ist so einfach
wie eine simple SQL-Abfrage. Auch Non-ORACLE Datenbanken
bieten gute direkte Zugriffsmöglichkeiten auf die ORACLE RDBMS
(wie SQL Server, MySQL oder auch MS Access, z.B. via ODBC).
Doch wie sieht es beim Datenbankgiganten selbst aus? Welche
Möglichkeiten bieten sich hier, um an „artfremde“ Informationen zu
kommen?
Andreas Reese
In heterogenen IT-Landschaften, wie sie oft bei großen Unternehmen zu finden sind, gibt
es in der Regel auch Datenbanken von unterschiedlichen Anbietern. Während man im
Systemumfeld zwischen SAP und NON-SAP unterscheidet, bietet sich dieser Blickwinkel im
Datenbanksektor in Bezug auf ORACLE. Große SAP-Systeme oder entsprechend aufgestellte
Individualentwicklungen basieren häufig auf einer ORACLE Datenbank. Daneben existieren
aber auch branchenspezifische Produkte, die beispielsweise einen SQL Server aus dem Hause
Microsoft im Gepäck haben.
Von besonderem Interesse für diesen Artikel sind die ORACLE-basierten
Individualentwicklungen, die bei den Unternehmen etwa aufgrund von fehlenden oder nicht
ausreichenden Standardprodukten entstehen und nicht zuletzt wichtige Informationen aus
verschiedenen Systemen zusammenbringen sollen. Da die Anwendung als Schnittstelle zum
Benutzer in der Regel nichts vom Datenaustausch mitbekommen soll, findet dieser sehr oft
auch auf Ebene der Datenbank statt. Wie können nun ORACLE- und NON-ORACLE
Datenbanken miteinander kommunizieren? Indirekt geschieht das in der Regel über
Schnittstellen, die jedoch nicht Thema dieses Artikels sein sollen. Aber kann man auch direkt
aus einer ORACLE-Session heraus in einer Access-Datenbank oder gar einem SQL Server
Informationen lesen oder gar Daten manipulieren?
Für ein besseres Verständnis zunächst ein kurzer Blick auf die möglichen
Konstellationen:
ORACLE <-> ORACLE
Hier bietet die Datenbank das bequeme Feature des Datenbanklinks. Nach Anlage eines
entsprechenden Links kann direkt per SQL-Abfrage auf die Objekte (wie Tabellen) der
entfernten Datenbank zugegriffen werden.
NON-ORACLE
ORACLE
Auch diese Konstellation ist eher unproblematisch, da viele Datenbanken bereits eine
einfache Möglichkeit mitbringen, um auf ORACLE zuzugreifen (wie z.B. per ODBC von MS
Access aus).
ORACLE
NON-ORACLE
Wie zu erwarten ist, bietet natürlich auch ORACLE einen Service an, mit dem eine
ODBC-Verbindung möglich ist. Das Produkt nennt sich Heterogeneous Services und stellt
nach erfolgreicher Installation und Konfiguration eine überaus bequeme Möglichkeit der
Datenabfrage und –manipulation auf der Datenbank des Drittanbieters zur Verfügung. Dies
geschieht dann ähnlich komfortabel per SQL-Abfrage und Datenbanklink.
Abb.1: ORACLE Heterogeneous Services
Dazu wird zunächst eine ODBC-Verbindung auf dem ORACLE Datenbankserver
eingerichtet, sowie ein entsprechender ODBC-Agent auf dem Server des Drittanbieters
installiert (siehe Abb. 1). Nun müssen noch der ORACLE TNS-Listener sowie die eine oder
andere Konfigurationsdatei angepasst werden und schon kann der Zugriff über einen neu
anzulegenden Datenbanklink erfolgen. Die genaue Vorgehensweise kann in diversen
Anleitungen sowie in der entsprechenden ORACLE-Dokumentation nachgelesen werden.
Dieses Produkt funktioniert in den meisten Fällen. Doch was tun, wenn Betriebssystem,
Serverprozessor und der Heterogeneous Service nicht miteinander harmonieren? Unnötig zu
erwähnen, dass das oben beschriebene Vorgehen unter UNIX (insbesondere das Einrichten
einer ODBC-Quelle) nichts für Anfänger ist.
Eine geschickte Kombination der folgenden Tools und Datenbankobjekte liefert eine
Lösung, die immer funktioniert:
•
•
•
•
•
JDBC-Treiber des jeweiligen Drittanbieters
ORACLE JavaStoredProcedures
ORACLE PL/SQL
ORACLE Collections und Objekte
ORACLE Views und Trigger
Das Zusammenspiel dieser Komponenten soll anhand eines lesenden Zugriffs auf einen
SQL Server verdeutlicht werden (siehe Abb. 2).
Lesender Zugriff auf einen SQL Server
Abb. 2: Lesender Zugriff auf einen SQL Server aus der ORACLE RDBMS heraus
Die relevanten Tabellen des SQL Servers werden zunächst 1:1 als ORACLE Objekte in
der RDBMS erzeugt. Nehmen wir dazu eine Tabelle tab1 mit mehreren Spalten
unterschiedlicher Datentypen. Dann kann das adäquate ORACLE-Objekt wie folgt erzeugt
werden:
CREATE OR REPLACE TYPE sqls_tab_obj AS OBJECT
(
COL1
VARCHAR2(50),
COL2
NUMBER(10),
COL3
DATE
);
Da in der Praxis stets mehrere Zeilen benötigt werden, muss dem Objekt eine weitere
Dimension in Form einer Collection hinzugefügt werden:
CREATE OR REPLACE TYPE sqls_tab IS TABLE OF sqls_tab_obj;
Die spätere SQL-Abfrage wird dann direkt auf dieser Collection sqls_tab ausgeführt,
deren zugrundeliegende Struktur genau der Zieltabelle auf dem SQL Server entsprechen
muss. Das Hauptaugenmerk liegt jedoch auf einer oder auch mehrerer Java Klassen, welche
die eigentliche Arbeit erledigen:
• Herstellen einer Verbindung zum SQL Server
• Abfrage der entsprechenden Tabelle
• Umleitung der Daten in die ORACLE Collection
Damit dies innerhalb der Datenbank funktioniert, hat ORACLE mit der Version 8i die
JavaStoredProcedure eingeführt. Dabei handelt es sich um Java Klassen, die als Alternative
zur Datenbanksprache PL/SQL innerhalb der Datenbank benutzt werden können.
Die Java Klasse muss zunächst per JDBC die Verbindungen zum SQL Server und auch
zur ORACLE-Datenbank herstellen. Die Verbindung zum SQL Server erfolgt traditionell
über JDBC:
import
import
import
import
com.microsoft.sqlserver.jdbc.SQLServerDriver;
java.sql.Connection;
java.sql.DriverManager;
java.util.HashMap;
--->
// get configurations (e.g. from config.properties)
HashMap settings = new HashMap();
ResourceBundle bundle = ResourceBundle.getBundle("config");
settings.put("SERVER",bundle.getString("db.server");
settings.put("PORT",bundle.getString("db.port");
settings.put("DBNAME",bundle.getString("db.dbname");
settings.put("USER",bundle.getString("db.user");
settings.put("PASSWORD",bundle.getString("db.password");
// register driver to SQL Server
DriverManager.registerDriver(new SQLServerDriver());
// establish connection
Connection connSQL = DriverManager.getConnection("jdbc:sqlserver://"
+ settings.get("SERVER").toString() + ":"
+ settings.get("PORT").toString()
+ ";databaseName="
+ settings.get("DBNAME").toString()
, settings.get("USER").toString(), settings.get("PASSWORD").toString()));
Auch eine Verbindung zur ORACLE-Datenbank ist zwingend erforderlich. Da sich die
Klasse jedoch in der Datenbank befindet und auch dort ausgeführt wird, lässt diese sich
einfach die Standardverbindung geben:
import oracle.jdbc.OracleDriver;
--->
Connection connORA = new OracleDriver().defaultConnection();
Der Aufruf einer Java Klasse in der Rolle als JavaStoredProcedure kann über eine
beliebige statische Methode erfolgen. Der Start über die traditionelle Methode main ist hier
jedoch nicht zulässig:
import
import
import
import
java.sql.Connection;
java.sql.PreparedStatement;
java.sql.ResultSet;
java.sql.SQLException;
import java.util.Vector;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
--->
public static ARRAY readFromSQLServer(ARRAY in)
{
// initialize SQL array
ARRAY[] out = new ARRAY[1];
// fill SQL array
try {
readFromDatabase(in,out); }
catch(SQLException e)
{ System.err.println(e.getMessage()); }
// return SQL array
return out[0];
}
Die Methode readFromSQLServer erhält einen Parameter vom ORACLE Datentyp
ARRAY und gibt einen ebensolchen auch wieder zurück. Der übergebene Parameter liefert die
Metadaten der in der Datenbank angelegten ORACLE Collection, jedoch keinerlei Daten. Der
zurückgegebene Parameter enthält die aus dem SQL Server gelesenen Daten und schreibt
diese automatisch in die ORACLE Collection zurück. Man beachte, dass der erzeugte
Parameter die Dimension 1 hat. Der Datentyp ARRAY bietet zahlreiche Möglichkeiten, um
aufgrund der Zuordnung der Collection zum Parameter dessen Struktur auszulesen. Mit
Methoden wie getSQLTypeName() oder getBaseTypeName() können die Namen der
Collection sowie des zugrundeliegenden Objektes ermittelt werden. Nun ist es relativ einfach
per JDBC das ORACLE Repository auszulesen, um die Spaltennamen des Objektes zu
erfahren. Damit kann dann ein SQL-Statement gebaut werden, das die Zieltabelle auf dem
SQL Server ausliest:
private static void readFromDatabase(ARRAY in, ARRAY[] out) throws SQLException
{
// read from SQLServer
PreparedStatement stmt = connSQL.prepareStatement(
"SELECT col1, col2, col3 FROM tab1");
ResultSet rset = stmt.executeQuery();
// fill column vectors
int idx = 0;
Vector[] a = new Vector[3];
for(int i=0; i<3; i++)
a[i] = new Vector();
while(rset.next())
{
for(int i=0; i<3; i++)
a[i].add(idx,rset.getString(i+1));
idx++;
}
rset.close();
stmt.close();
// copy column vectors to multi-dimensional array
String[][] arr = new String[a[0].size()][3];
for(int i=0; i<a[0].size(); i++)
for(int j=0; j<3; j++)
arr[i][j] = (String) a[j].get(i);
// copy mult-dimensional array to SQL array
ArrayDescriptor ads = ArrayDescriptor.createDescriptor(ds.getSQLTypeName(),connORA);
out[0] = new ARRAY(ads,connInt,arr);
}
Nachdem die Daten aus dem ResultSet in ein 2-dimensionales Array kopiert wurden,
erfolgt der eigentliche Kunstgriff. Nun werden die gelesenen Daten dem Parameter vom Typ
ARRAY zugeordnet und landen damit in der ORACLE Collection innerhalb der Datenbank.
Die fertige(n) Java Klasse(n) können nun mit dem ORACLE loadjava-Befehl in die
Datenbank transportiert werden:
loadjava -u scott/tiger -v -resolve SQLServerReader.class
Die hier benutzten Schalter und ihre Bedeutungen:
• -u
• -v
• -resolve
User/Password
verbose (Ausgabe Logging)
Auflösung von Abhängigkeiten
Neben der selbst erstellten Klasse sollte keinesfalls der JDBC-Treiber des Herstellers (hier
Microsoft) vergessen werden. Die entsprechenden Bibliotheken müssen ebenfalls in die
Datenbank geladen werden. Ein Upload von jar-Archiven ist selbstverständlich möglich und
verringert den Aufwand ungemein.
Nun befindet sich die Java Klasse innerhalb der Datenbank. Um diese benutzen zu
können, ist jedoch ein PL/SQL-Wrapper erforderlich, der die Verbindung zu der jeweiligen
statischen Methode einer Klasse herstellt und die Typkonvertierungen zwischen denen der
Datenbank und Java vornimmt:
CREATE OR REPLACE FUNCTION get_sqls_data
(
p_in
IN sqls_tab
)
RETURN sqls_tab
AS
LANGUAGE JAVA NAME 'SQLServerReader.readFromSQLServer(oracle.sql.ARRAY)
return oracle.sql.ARRAY';
Zu guter Letzt muss noch die Verbindung zum SQL Server freigegeben werden:
dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission'
,'sqls.your_domain.de:1433'
,'connect,resolve');
Jetzt sind alle Voraussetzungen erfüllt, um jede beliebige Tabelle des freigegebenen SQL
Servers abzufragen. Dazu müssen dann lediglich die Tabellenpendants in Form von ORACLE
Objects und Collections angelegt sowie ein neuer PL/SQL-Wrapper in Form einer kurzen
Funktion erzeugt werden. Eine SQL-Abfrage sieht dann wie folgt aus:
SELECT *
FROM TABLE(get_sqls_data(sqls_tab()));
Die SQL-Abfragen können beliebig komplex sein und auch mit anderen Statements
kombiniert werden (JOINS). Die SQL Server-Tabellen verhalten sich nun wie herkömmliche
ORACLE-Tabellen und werden (fast) genauso angesprochen. Wie der Leser sicherlich ahnt,
ist die Performance, bedingt durch die zusätzliche Abfrage gegen die fremde Datenbank,
geringfügig schlechter. Die Java Klasse sollte daher in dem Sinne eine Erweiterung erfahren,
dass z.B. einschränkende WHERE-Bedingungen mitgegeben und direkt gegen den fremden
Server ausgeführt werden können. Dadurch lässt sich die zu übertragende Datenmenge
deutlich reduzieren.
Die Welt ist jedoch keine Einbahnstraße, d.h. auch die umgekehrte Richtung ist möglich.
Es können aus einer ORACLE-Datenbank heraus Daten in einen SQL Server transportiert
oder vorhandene Daten manipuliert werden.
Schreibender Zugriff auf einen SQL Server
Abb. 2: Schreibender Zugriff auf einen SQL Server aus der ORACLE RDBMS heraus
Die umgekehrte Richtung, also Daten in einen SQL Server zu schreiben oder dort zu
manipulieren, funktioniert ähnlich (siehe Abb. 3). Soll dieser Zugriff jedoch direkt per SQL
erfolgen, ist ein klein wenig mehr Aufwand zu betreiben, um die Daten in die Java Klasse zu
leiten. Da die ORACLE Collection nicht analog zum Auslesen mit einem SELECT-Statement
auch wieder gefüllt werden kann, wird zusätzlich eine ORACLE-View erzeugt. Diese kann
zwar auch keine Daten aufnehmen, dafür aber mithilfe eines sogenannten Instead-Of Triggers
die empfangenen Daten weiter- bzw. umleiten. Der Trigger füllt die ORACLE Collection und
gibt diese an die Java Klasse innerhalb der Datenbank weiter. An dieser Stelle sei lediglich
der schreibende Zugriff (INSERT) erläutert (Manipulationen per UPDATE oder DELETE sind
ähnlich zu realisieren):
public int writeToSQLServer(ARRAY in)
{
try
{ writeToDatabase(in); }
catch(SQLException e)
{ System.err.println(e.getMessage());
return 1;
}
return 0;
}
Die Methode erhält wieder einen Parameter vom ORACLE Datentyp ARRAY, der dieses
Mal jedoch die zu schreibenden Daten enthält.
import oracle.sql.STRUCT;
--->
private void writeToDatabase(ARRAY in) throws SQLException
{
// prepare statement for SQL server
PreparedStatement stmt = connSQL.prepareStatement(
"INSERT INTO tab1 (col1,col2,col3) VALUES (?,?,?)");
// loop SQL array and bind parameters to statement
Object[] objs = (Object[])in.getArray();
for(int i=0; i<objs.length; i++)
{
STRUCT strct = (STRUCT)objs[i];
for(int j=0; j< strct.getAttributes().length; j++)
stmt.setObject(j+1,strct.getAttributes()[j]);
stmt.executeUpdate();
}
stmt.close();
}
Auch hier kann für einen generischen Ansatz die Struktur der Zieltabelle aus den
Metadaten des übergebenen Parameters vom Typ ARRAY und somit dem ORACLE
Repository entnommen werden. Die Daten werden dem Parameter in in Form eines Arrays
entnommen und anschließend für jede Zeile an das JDBC-Statement gebunden und
ausgeführt.
Fazit
Die hier vorgestellte Methode zeichnet einen gangbaren Weg, wenn auf die Produkte der
Hersteller aus technischen oder sonstigen nachvollziehbaren Gründen nicht zurückgegriffen
werden kann. Die Erfahrungen in der Praxis zeigen eine gute Performance und einen
vertretbaren Konfigurationsaufwand bei der Einbindung von weiteren Tabellen. Um letzteren
zu minimieren, kann ein generischer Ansatz bei der Konzipierung und Realsierung der Java
Klasse gewählt werden. Die ORACLE-Datentypen sowie das Datenbank-Repository liefern
alle notwendigen Metainformationen.
Zur Person des Autors:
Andreas Reese ist studierter Diplom-Mathematiker und seit 1998 als IT-Consultant im
ORACLE- und JEE-Umfeld (Konzeption und Entwicklung von client-, server- oder
webbasierten JEE-Applikationen) tätig. Zunächst als angestellter Berater, machte er sich
2001 selbstständig und gründete eine IT-Firma, für die er bis Ende 2007 erfolgreich als
geschäftsführender Gesellschafter tätig war. Seitdem steht er seinen Kunden als
freiberuflicher IT-Berater zur Verfügung.
Kontakt Autor:
Andreas Reese
[email protected]
www.reecon-it.de