JDBC mit Oracle und PostgreSQL
Werbung
JDBC mit Oracle und PostgreSQL Holger Jakobs – [email protected], [email protected] 2012-03-13 Inhaltsverzeichnis 1 Warum Java in Verbindung mit Datenbanken? 1 2 Vorstellung von JDBC 2.1 ODBC – zum Vergleich . 2.2 JDBC – was ist anders? 2.3 Portabilität von JDBC . 2.4 Die JDBC-Treibertypen . . . . 2 2 2 3 4 . . . . . . . . . . . . . . . . . . . . . . . 5 5 5 5 6 7 7 7 8 8 9 10 10 11 11 12 12 12 12 13 13 13 14 14 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Programmierung mit JDBC 3.1 Bestandteile eines JDBC-Programms . . . . . . . 3.1.1 Exceptions . . . . . . . . . . . . . . . . . . 3.1.2 Datenbanktreiber laden . . . . . . . . . . 3.1.3 Datenbankverbindung herstellen . . . . . . 3.1.4 Statement herstellen . . . . . . . . . . . . 3.1.5 Anfrage ausführen . . . . . . . . . . . . . 3.1.6 Ergebnistupel lesen . . . . . . . . . . . . . 3.1.7 Ergebnismenge und Statement freigeben . 3.2 Ein komplettes JDBC-Programm . . . . . . . . . 3.3 Automatische Datenbank-Verbindung . . . . . . . 3.4 Metadaten einer Ergebnismenge . . . . . . . . . 3.4.1 Ermitteln der Metadaten . . . . . . . . . . 3.4.2 Verarbeitung einer Ergebnismenge zu einer 3.4.3 Erweiterungen der dynamischen Abfrage . 3.5 Metadaten einer Datenbankverbindung . . . . . . 3.6 SQL-Escapes . . . . . . . . . . . . . . . . . . . . 3.7 Transaktionen . . . . . . . . . . . . . . . . . . . . 3.7.1 Beginnen und Beenden von Transaktionen 3.7.2 Isolationslevel . . . . . . . . . . . . . . . . 3.8 Fehler und Warnungen . . . . . . . . . . . . . . . 3.8.1 Fehler . . . . . . . . . . . . . . . . . . . . 3.8.2 Warnungen . . . . . . . . . . . . . . . . . 3.9 Prepared Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . HTML-Tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 VORSTELLUNG VON JDBC 1 Warum Java in Verbindung mit Datenbanken? Eigentlich erscheint es auf den ersten Blick nicht so sinnvoll zu sein, die objektorientierte Sprache Java mit den tabellenorientierten relationalen Datenbanken zu verbinden. Trotzdem gibt es mehrere Anbindungsmöglichkeiten, eben weil sich der objektorientierte Ansatz bei der Programmierung vorwiegend durchgesetzt hat, im Bereich der Datenbanken aber nach wie vor die relationalen Datenbanken die führende Rolle spielen. Es gibt zwar auch ernst zu nehmende Ansätze im Bereich objektorientierter Datenbanken, aber da meistens auch auf bereits bestehende und nicht nur neu zu erstellende Datenbestände zugegriffen werden muss, ist da der Übergang nicht so schnell zu schaffen. Die objektorienteren Datenbanken führen noch immer eher ein Nischendasein (Stand Mitte 2008). Die relationalen Datenbanken haben aber bereits begonnen, objektorientierte Dinge hinzuzufügen, so dass man von objektrelationalen Datenbanken spricht. Oracle enthält seit Version 8 objektorientierte Ansätze, PostgreSQL seit Version 7. Zu diesen Ansätzen gehört z. B. die Abkehr vom Grundsatz, dass ein Objekt durch seine Attribute identifiziert wird. Während im rein relationalen Ansatz niemals zwei Tupel von ihren Werten her identisch sein können – das wäre ein Widerspruch zur Relation als Tupelmenge –, ist das bei Objekt-Datenbanken durchaus möglich, denn die Identität eines Objekts ergibt sich aus dem Object-Identifier (bei Oracle wäre das die rowid, bei PostgreSQL die oid) und ist von den Attributwerten völlig losgelöst. Darüber hinaus sind komplexe Datenelemente in Tupeln erlaubt, z. B. Mengen (set of), Tupel (tupel of), Listen (list of) – so in Oracle 8. Es kann auch Vererbungen geben, so dass eine Tabelle Attribute und ggf. auch Tupel einer anderen Tabelle (oder sogar von mehreren) erbt – so in PostgreSQL 7. Eine objektorientierte Datenbank kann auch Methoden enthalten, d. h. neben den reinen Daten auch Ausführbares. Die Methoden beschreiben dann, wie die Datenobjekte manipuliert werden dürfen. Es scheint sich also eher ein evolutionärer Weg von den relationalen Datenbanksystemen über die objektrelationalen abzuzeichnen als ein revolutionärer Bruch und Übergang zu rein objektorientierten Systemen. Schließlich haben nicht einmal die objektorientierten Programmiersprachen die prozeduralen vollständig verdrängt – und das ist nicht ohne Grund so. 2 Vorstellung von JDBC Wenn ODBC für Open Data Base Connectivity steht, bedeutet dann JDBC nicht Java Data Base Connectivity? Laut Sun, dem Erfinder von JDBC ist das nicht so – eine andere Deutung der Buchstabenkombination liefert Sun allerdings auch nicht. 2 VORSTELLUNG VON JDBC 2.1 ODBC – zum Vergleich 2.1 ODBC – zum Vergleich ODBC wurde von Microsoft entwickelt, um insbesondere den Office-Produkten einen direkten Zugriff auf Datenbanken beliebigen Typs zu geben. Für jede Datenbank auf Serverseite und jedes Betriebssystem auf Clientseite benötigt man hierfür einen Treiber. Die Datenbank muss also ODBC-fähig sein, und es muss auf Clientseite für genau diese Datenbank einen passenden ODBC-Treiber geben. Meistens handelt es sich hierbei um compilierten C-Code, d. h. er ist in der übersetzten Form sowohl von der Hardware als auch dem Betriebssystem abhängig. Da es aber nur wenige Windows-Versionen und diese jeweils nur auf einer einzigen Hardware-Plattform gibt, bleibt die Anzahl der Treiber in Grenzen. Bei 5 Plattformen und 10 Datenbanken wären das ansonsten auch schon 50 bereitzustellende Treiber. ODBC definiert übrigens keine völlig abstrakte Schnittstelle, sondern es werden einfach SQL-Anfragen als Zeichenketten an die Datenbank geschickt, die diese dann interpretiert – so wie das bei den gängigen Datenbank-Frontends auch der Fall ist. Das bedeutet aber, dass eine SQL-Anfrage nach SQL92, z. B. SELECT * FROM A NATURAL JOIN B von vielen Datenbanken nicht verstanden wird. Der jeweilige SQL-Dialekt muss also nach wie vor berücksichtigt werden, was beim Wechsel des Datenbanksystems zu Problemen führen kann, wenn man viele Anfragen umschreiben muss. Für die MS-eigenen Datenspeicher (von einfachen Textdateien über Excel-Tabellen, den Jet-Engine-Dateien mit der Endung .mdb) sowie für einige klassische Datenbank-Dateien wie dBase und Foxpro (.dbf) werden die ODBC-Treiber bei Windows bereits mitgeliefert, für andere Datenbanken gibt es sie von den jeweiligen Herstellern. Alle werden in der Systemsteuerung unter ODBC-Datenquellen verwaltet. 2.2 JDBC – was ist anders? Java verfolgt konsequent einen plattformübergreifenden Ansatz und möchte auf möglichst vielen Geräten lauffähig sein. Die Java VM (virtuelle Maschine) mit dem Bytecode-Interpreter wird daher fleißig auf immer neue Hardware portiert. Daher kann der compilierte Bytecode quasi überall ausgeführt werden. Allerdings muss auch der JDBC-Treiber für die jeweilige Datenbank passen, weil die Datenbanksysteme alle ein eigenes Protokoll für die Kommunikation zwischen Client und Server verwenden. Als JDBC vorgestellt wurde, gab es nur wenige Treiber, die von Sun entwickelt worden waren. Um es trotzdem möglichst schnell einsetzbar zu machen, fügte Sun die JDBCODBC-Bridge hinzu, so dass auf diese Weise jede ODBC-fähige Datenbank auch gleichzeitig JDBC-fähig wurde. Allerdings muss betont werden, dass dies nur dem Anstoßen der Entwicklung dienen sollte und heute keineswegs mehr der richtige Ansatz für den Einsatz von JDBC ist. Es gibt heute ausreichend „native“ Treiber, die eine wesentlich bessere Performance und auch einen größeren Funktionsumfang bieten. Außerdem bindet man das Java-Programm an eine Schnittstelle, die plattformabhängig ist, konterkariert also den Java-Ansatz! In Java-Applets ist die Verwendung von ODBC übrigens völlig unmöglich, weil Applets 2.3 Portabilität von JDBC 2 VORSTELLUNG VON JDBC ausschließlich aus Java-Bytecode bestehen müssen und nur Java-Klassen nachladen können, aber keinen Maschinencode. Ein JDBC-Treiber besteht aus einer Java-Klasse – meist einer .jar-Datei. Diese ist, da in Java geschrieben, unmittelbar auf allen Plattformen mit Java-Interpreter lauffähig. Man benötigt also für jedes Datenbanksystem nur einen einzigen JDBC-Treiber. Dieser JDBCTreiber verhält sich gegenüber dem Datenbank-Server genau wie ein in C geschriebener Client, d. h. es wird das datenbankspezifische Protokoll verwendet. Gegenüber dem Java-Programm (Application oder Applet) verhalten sich alle JDBCTreiber gleich, d. h. für den Java-Programmierer ist es völlig gleichgültig, welcher JDBCTreiber später einmal mit dem Programm verwendet werden soll. Beim Umstieg von einem Datenbanksystem auf ein anderes ist also keine Änderung am Programmcode notwendig – außer den Namen des JDBC-Treibers und der Datenbank natürlich. Ein Java-Programm kann sogar mehrere JDBC-Treiber laden und gleichzeitig auf mehrere Datenbanken zugreifen. 2.3 Portabilität von JDBC Man fragt sich, warum das überhaupt ein Thema ist. Schließlich wurde oben erläutert, dass die Java-Programme plattformunabhängig sind, weshalb die Portierung auf eine andere Plattform eigentlich nur aus einem Kopiervorgang des Java-Programms bestehen sollte. Das ist soweit auch richtig. Allerdings geht es nicht nur um die Clientseite, sondern auch um die Datenbank. Leider kommuniziert auch JDBC mit der Datenbank über die Sprache SQL mit all ihren Dialekten. Das bedeutet, dass die SQL-Queries auf die jeweilige Zieldatenbank abgestimmt sein müssen. Das bereits in Abschnitt 2.1 auf der vorherigen Seite erläuterte Problem der Unterschiedlichkeit bei den Datenbanksystemen besteht weiterhin. JDBC ist eine „Low-Level“ Schnittstelle zum SQL-Interface diverser Datenbanken, aber ebnet diese Unterschiede keineswegs ein (genau das beabsichtigt beispielsweise das ADO-Konzept von PHP). Immerhin verlangt Sun von JDBC-Treibern die Einhaltung des „Entry Levels“ von SQL92. Nur dann darf sich der Treiber „JDBC compliant“ nennen. Für einige typische Probleme, beispielsweise das Datumsformat, gibt es die Lösung von SQL-Escapes (siehe Abschnitt 3.6 auf Seite 12). Man darf in SQL-Kommandos ein Datum als „{d ’2003-12-31’}“ schreiben, auch wenn das Datenbanksystem das Datum evtl. in einem nicht ISO-8601-gemäßen Format verlangt. Über diverse Metadaten-Abfragen kann zur Laufzeit abgefragt werden, welche Eigenschaften das gerade verwendete Datenbanksystem hat. Beispiele sind in Abschnitt 3.4 auf Seite 10 zu finden. Falls man zum Entwicklungszeitpunkt nicht absolut sicher ist, welche Datenbank-Backends später einmal verwendet werden, sollte man sich bemühen, nur sehr kompatible Abfragen zu verwenden. Sogar so einfache Dinge wie NATURAL JOIN werden schließlich nicht von allen Systemen verstanden. 2 VORSTELLUNG VON JDBC 2.4 Die JDBC-Treibertypen Abbildung 1: Die 4 Treibertypen von JDBC Java JDBC−ODBC− Bridge Treiber für DBMS B C ODBC− Treiber (C) Treiber für DBMS B Middleware− JDBC−Treiber Treiber für DBMS D Client Java−Programm mit JDBC−Treibermanager Middleware A Treibertyp: Typ 1 B C D Typ 2 Typ 3 Typ 4 2.4 Die JDBC-Treibertypen Die JDBC-Treiber lassen sich in vier Typen untergliedern, siehe auch Abbildung 1: 1. Typ 1 Das ist die oben erwähnte JDBC-ODBC-Bridge, eine reine Übergangslösung und heute nicht mehr zeitgemäß. Bitte nicht verwenden! 2. Typ 2 Das ist ähnlich wie Typ 1, aber es wird nicht auf einen ODBC-Treiber, sondern auf einen für die Zieldatenbank speziellen, z. B. in C geschriebenen Treiber zurückgegriffen. Auch dies ist eine plattformabhängige Übergangslösung und heute nicht mehr zeitgemäß. Bitte nicht verwenden! 3. Typ 3 Hier wird ein einheitlicher Java-Treiber für alle Datenbanken verwendet. Er greift nicht direkt auf die Zieldatenbank zu, sondern auf eine Middleware, die nicht unbedingt in Java geschrieben sein muss. Diese befindet sich nicht im plattformunabhängigen Client, sondern entweder auf dem Datenbankserver oder einem dritten Rechner. Die Middleware setzt die Anfragen vom JDBC-Treiber für die Zieldatenbank passend um. Dies ist clientseitig eine 100 %-Java-Lösung und daher plattformunabhängig. Ein 3 PROGRAMMIERUNG MIT JDBC Beispiel für die Middleware ist OpenLink1 . Man spricht hier von einem DreischichtModell (3-Tier-Model). 4. Typ 4 Diese Treiber sind wie die von Typ 3 zu 100 % in Java geschrieben und daher plattformunabhängig. Sie benötigen allerdings keine Middleware, sondern greifen direkt auf die Zieldatenbank zu. Das bedeutet, dass die gesamte Funktionalität der Middleware hier im Treiber auf Clientseite integriert ist. Das macht den Treiber zwar etwas größer, aber man benötigt keine dritte Schicht, weshalb man hier von einem Zweischicht-Modell spricht (2-Tier-Model). Die Treiber stammen meist direkt von den Datenbank-Herstellern. 3 Programmierung mit JDBC Hinweis: Komplette Beispielquelltexte stehen im Datenbank-Portal unter „Quelltexte“2 zur Verfügung. 3.1 Bestandteile eines JDBC-Programms Ein JDBC-Programm ist natürlich in erster Linie ein gewöhnliches Java-Programm – sei es ein Applet oder eine Application. Hier sollen nur die Besonderheiten erläutert werden, die JDBC betreffen. Als Beispiele dienen hier kleine, kommandozeilenorientierte Applications, so dass der Code übersichtlich bleibt und den Blick auf das für JDBC Wesentliche nicht versperrt wird. 3.1.1 Exceptions Die Klasse, die JDBC verwendet, kann folgende Exceptions werfen: ClassNotFoundException, IOException, SQLException. Diese sind daher bei der Deklaration zusätzlich zu den übrigen evtl. Notwendigen anzugeben. 3.1.2 Datenbanktreiber laden Der Datenbanktreiber muss geladen werden. Der Name des Treibers hängt von der Datenbank (Typ 4) bzw. von der Middleware (Typ 3) ab. Bei PostgreSQL heißt er org.postgresql.Driver und muss daher bei Oracle mit der Anweisung Class.forName("oracle. jdbc.driver.OracleDriver") und bei PostgreSQL mit Class.forName("org.postgresql.Driver") geladen werden. Damit das klappt, muss sichergestellt sein, dass die Java-Archive mit dem Treiber (nicht das Verzeichnis, in dem es liegt!), bei Oracle classes12. und nls_charset12.jar, bei PostgreSQL postgresql.jar, im CLASSPATH enthalten sind. 1) http://www.openlinksw.com 2) http://www.bg.bib.de/portale/dab/Quelltexte 3 PROGRAMMIERUNG MIT JDBC 3.1 Bestandteile eines JDBC-Programms Eigentlich liefert die Methode nur die Klasse zu einem Namen, aber bei bislang nicht geladenen Klassen versucht der Classloader, diese Klasse im CLASSPATH zu finden und sie zu laden. Dieser Nebeneffekt wird hier ausgenutzt, während das eigentliche Ergebnis der (statischen) Methode gar nicht verwendet wird. Der dynamische Ladevorgang wird erst zur Laufzeit ausgeführt, d. h. man kann den zu ladenden Namen auch vom Anwender erfragen oder aus einer Datei oder anderswo her laden, so dass man zum Zeitpunkt der Programmerstellung noch gar nicht wissen muss, wie der Name lautet. Auf diese Weise können auch nachträglich bislang unbekannte Datenbanken verwendet werden. 3.1.3 Datenbankverbindung herstellen Wie bei den interaktiven Tools (psql, sqlplus, TOra, pgAdminIII) und den Programmen mit Embedded SQL in C auch muss (mindestens) eine Verbindung zur Datenbank hergestellt werden. Die Angabe der Datenbank geschieht bei JDBC mit einem URL (Uniform Resource Locator), der außer Benutzername und Passwort alle notwendigen Angaben enthält. Letztere werden in weiteren Parametern übergeben. Der URL besteht aus • jdbc: • subprotocol, d. h. Name des Datenbanktyps, z. B. oracle:thin oder postgresql, gefolgt von einem weiteren Doppelpunkt • weiteres ist der sogenannte subname, bei Oracle bestehend aus einem @-Zeichen, dem Namen des Datenbankservers, der Portnummer und dem Namen der Datenbank, bei uns also @dbserver2:1521:ora10; bei PostgreSQL sind es zwei Slashes, der Name des Datenbankservers und der Name der Datenbank bei uns also //dbserver2/datenbankname. Achtung: Auch die Trennzeichen unterscheiden sich. Für die Verbindung wird ein Objekt vom Typ Connection benötigt. Bei Oracle sieht die Anweisung zur Herstellung der Datenbankverbindung also so aus: Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbserver2:1521:ora10", "username", "password"); Wenn das Passwort hier angegeben wird, dann ist es in der .class-Datei vorhanden, zwar vielleicht nicht direkt im Klartext, aber auch nicht ausreichend geschützt. Bei PostgreSQL sieht die Anweisung zur Herstellung der Datenbankverbindung also so aus: Connection conn = DriverManager.getConnection ("jdbc:postgresql://dbserver2/dbname", "username", "password"); Das Passwort kann leer bleiben, falls die Datenbank den Benutzer aufgrund seines bereits erfolgen Logins beim Betriebssystem erkennt (sogenanntes Single-Sign-On). 3.1 Bestandteile eines JDBC-Programms 3 PROGRAMMIERUNG MIT JDBC 3.1.4 Statement herstellen Die Datenbankverbindung kann während der gesamten Laufzeit aufrecht erhalten bleiben. Lediglich wenn ein Programm nur gelegentlich auf die Datenbank zugreift und nur eine begrenzte Anzahl von Verbindungen zur Verfügung steht, könnte man sie zwischendurch beenden und wieder aufbauen. Für jede einzelne Abfrage benötigt man aber noch ein Objekt vom Typ Statement. Das Erzeugen eines Statements braucht keine Parameter. Anschließend ist alles bereit, um eine Anfrage auszuführen. Statement stmt = conn.createStatement(); 3.1.5 Anfrage ausführen Eine Anfrage liegt in Form eines String-Objekts vor und kann komplett vom Benutzer erfragt oder auch aus festen und variablen Anteilen zusammengebaut werden. Das Ergebnis der Methode executeQuery ist ein Objekt vom Typ ResultSet. Diese ist bei Selects anzuwenden – bei allen anderen Anfragen (Insert, Update, Delete oder auch Datendefinitionskommandos) ist executeUpdate zu benutzen, das statt einer Ergebnismenge die Anzahl der vom Kommando betroffenen Tupel liefert. String query = "select name, ort from kunden where knr=7"; ResultSet result = stmt.executeQuery (query); Mit dieser Ergebnismenge kann weiter gearbeitet werden, z. B. Größe abgefragen, Tupel des Ergebnisses lesen usw. 3.1.6 Ergebnistupel lesen Die häufigste Anwendung ist wohl das Lesen der Ergebnistupel, weshalb genau das hier gezeigt wird. Hierzu bietet das Ergebnismengenobjekt entsprechende Methoden an. Die Methode next() setzt den Lesezeiger auf das nächste Tupel und liefert true, wenn es dieses nächste Tupel gibt – d. h. am Ende der Daten liefert sie false. Dies eignet sich zur Konstruktion einer while-Schleife. Die Methode getString(int) verlangt einen int-Parameter, der die Nummer des Attributs angibt (gezählt wird ab 1). Es wird ein String-Objekt geliefert mit dem Inhalt des entsprechenden Attributs aus dem aktuellen Tupel. Entsprechende Methoden gibt es für die anderen Datentypen (getInt(), getFloat(), . . .). Mit diesen Informationen können wir die Ausgabe der gefundenen Daten programmieren: while (result.next()) { String name = result.getString(1); String ort = result.getString(2); System.out.println (name + ", " + ort); 3 PROGRAMMIERUNG MIT JDBC 3.2 Ein komplettes JDBC-Programm } // while System.out.println(">> Ende der Daten <<"); Alternativ zum numerischen Parameter kann man auch den Namen des Attributs übergeben. Für diese Form ist die Methode overloadet. Da die Verwendung des numerischen Index performanter ist, kann man den numerischen Index eines Attributs herausfinden über die Methode findColumn(attributname). Bei Attributen, die auch den Nullwert beinhalten können, kann man mittels der parameterlosen Methode wasNull() herausfinden, ob das letzte get. . .() einen Wert Null geliefert hat. Die get. . .()-Methoden liefern zwar sowieso auch in Java ein null, aber wenn man das Ergebnis in einfachen Variablen speichert, kann man es von einer numerischen 0 nicht unterscheiden. 3.1.7 Ergebnismenge und Statement freigeben Die Ergebnismenge und das Statement bleiben auch nach dem Auslesen erhalten. Zur Ressourcenschonung und auch zur evtl. Aufhebung von Sperren auf die Datenbank ist es notwendig, sie freizugeben. Ansonsten geschieht das erst am Ende des Programms. result.close(); stmt.close(); Verbindung beenden Am Ende des Programms – ggf. früher – kann man die Verbindung zur Datenbank schließen. conn.close(); 3.2 Ein komplettes JDBC-Programm In diesem kleinen Beispielprogramm ExampleJDBC_pg.java bzw. in der Oracle-Variante ExampleJDBC_ora.java sind die sonst notwendigen bzw. sinnvollen Fehlerprüfungen nicht enthalten, so dass Exceptions zum Programmabbruch führen. import java.io.*; import java.sql.*; public class ExampleJDBC_pg { public ExampleJDBC_pg() throws ClassNotFoundException, FileNotFoundException, IOException, SQLException { Class.forName("org.postgresql.Driver"); 3.3 Automatische Datenbank-Verbindung 3 PROGRAMMIERUNG MIT JDBC Connection conn = DriverManager.getConnection ("jdbc:postgresql://dbserver2/kunden", "hugo", "geheim"); Statement stmt = conn.createStatement(); System.out.print ("Ort: "); System.out.flush(); BufferedReader r = new BufferedReader (new InputStreamReader (System.in)); String ort = r.readLine(); String query = "select name, ort from kunden where ort = '" + ort + "'"; System.out.println (query); ResultSet res = stmt.executeQuery (query); while (res.next()) { String name = res.getString(1); ort = res.getString(2); System.out.println (name + ", " + ort); } // while System.out.println(">> Ende der Daten <<"); res.close(); stmt.close(); conn.close(); } public static void main (String args[]) { try { new ExampleJDBC_pg(); } catch (Exception exc) { System.err.println ("Exception caught.\n" + exc); exc.printStackTrace(); } } // main } // class 3.3 Automatische Datenbank-Verbindung Es gibt bei uns eine vordefinierte Klasse namens DabVerbindung – die Quelltextdatei finden Sie ebenfalls im Portal – mit den notwendigen Mechanismen, um eine Verbindung wahlweise mit Oracle oder mit PostgreSQL herzustellen. Hierzu werden die Zugangsdaten 3 PROGRAMMIERUNG MIT JDBC 3.4 Metadaten einer Ergebnismenge aus den Dateien $HOME/dabpw_oracle.sql bzw. $HOME/dabpw_postgresql.sql gelesen. Das bedeutet, dass Sie ein manuell geändertes Datenbank-Kennwort dort eintragen müssen, damit es funktioniert. Vorteil ist, dass Sie in Ihre Quelltexte kein Kennwort eintragen müssen, denn auch aus dem compilierten Programm könnte man es ganz leicht extrahieren. Außerdem können Sie compilierte Programme weitergeben, so dass andere Anwender sie verwenden können, dabei aber auf ihrer jeweils eigene Datenbank zugreifen. Beachten Sie hierzu auch die Dokumentation in Form von JavaDoc. Das obige Programm funktioniert unabhängig von dieser Klasse. Bauen Sie aber in alle Ihre eigenen Programm die Klasse DabVerbindung ein und vollziehen Sie die dort verwendeten Mechanismen nach. 3.4 Metadaten einer Ergebnismenge 3.4.1 Ermitteln der Metadaten Natürlich ist es schöner, wenn man flexiblere Abfragen zulassen kann als im ersten Beispiel gezeigt, wo Tabellen- und Attributnamen fest im Programm verankert sind. Bei Abfragen mit SELECT * weiß man zum Zeitpunkt der Programmierung noch nicht, wie viele Attribute die Ergebnistabelle haben wird und wie diese heißen. Die Anzahl und auch die Namen der Attribute können erst zur Laufzeit ermittelt werden. HTML-Tabellen kann man daraus besonders leicht erstellen, weshalb die Verwendung in CGI-Programmen und Servlets besonders beliebt ist. Wenn die Abfrage der Ergebnisse nun allgemein formuliert werden soll, d. h. unabhängig vom Datentyp der Ergebnisspalte, dann fragt man sich, ob man mit der im ersten Beispiel verwendeten Methode getString() weit kommt. Hier kommt dem Programmierer die Eigenschaft von Objekten entgegen, eine toString()-Methode zu haben. Man kann also getrost getObject() verwenden und sich darauf verlassen, dass bei der Stringverkettung oder beim print() das Objekt sinnvoll in eine Zeichenkette umgewandelt wird. Tabelle 1: Ergebnis-Metadaten-Methoden Methode getMetaData() Beschreibung liefert ein Objekt vom Typ ResultSetMetaData, das bei allen folgenden Methodenaufrufen verwendet werden muss getColumnCount() liefert die Anzahl Attribute (Spalten) der Abfrage getColumnName(i) liefert den Namen des Attributs Nr. i, wobei die Attribute (Spalten) ab 1 gezählt werden. 3.4 Metadaten einer Ergebnismenge 3 PROGRAMMIERUNG MIT JDBC 3.4.2 Verarbeitung einer Ergebnismenge zu einer HTML-Tabelle Um ein in seiner Struktur zum Programmierzeitpunkt unbekanntes Abfrageergebnis in einer HTML-Tabelle aufzubereiten, geht man wie folgt vor: • Metadaten holen (getMetaData()) • Anzahl der Spalten holen (getColumnCount()) • Schleife über die Spalten, um die Spaltennamen zu holen (getColumnName(i)) • Erzeugung der Kopfzeile für die HTML-Tabelle • Abrufen der Ergebnistupel und Schreiben der Datenzeilen für die HTML-Tabelle ResultSet res = stmt.executeQuery (query); ResultSetMetaData rsmd = res.getMetaData(); int anzSpalten = rsmd.getColumnCount(); System.out.print ("<table border><tr>"); for (int i=1; i <= anzSpalten; i++) { System.out.print ("<th>" + rsmd.getColumnName (i) + "</th>"); } // for System.out.println ("</tr>"); while (res.next()) { System.out.print ("<tr>"); for (int i=1; i <= anzSpalten; i++) { System.out.print ("<td>" + res.getObject(i) + "</td>"); } // for System.out.println ("</tr>"); } // while System.out.println ("</table>"); res.close(); stmt.close(); Dies kann man gut in ein CGI-Programm oder in ein Servlet einbauen. Es ist allerdings auch noch ausbaufähig, daher im Folgenden ein paar Anregungen dazu. 3.4.3 Erweiterungen der dynamischen Abfrage 1. Es könnte z. B. es sinnvoll sein, numerische Spalten rechtsbündig auszurichten. Dazu müsste man herausfinden, von welcher Klasse das gerade geholte Objekt ist (Methode getClass()) und darauf basierend eine Fallunterscheidung durchführen. Alternativ 3 PROGRAMMIERUNG MIT JDBC 3.5 Metadaten einer Datenbankverbindung kann man auch mit einer der Methoden getColumnType() oder getColumnTypeName() den SQL-Datentyp herausbekommen. Allerdings ist der Typname datenbankabhängig. Verwenden Sie die Java-API-Doku, um mehr herauszubekommen. 2. Obiger Programmausschnitt ist nicht in der Lage, mit NULL-Werten umzugehen, sondern bricht mit einer NullPointerException ab. Verhindern Sie dies, indem Sie das gelieferte Objekt in Java mit null vergleichen oder indem Sie die Methode wasNull() des ResultSets verwenden. Letztere können Sie ebenfalls in der Java-API-Doku finden. 3. Um Datumswerte hervorzuheben, stellen Sie diese in der HTML-Tabelle kursiv dar. Wie finden Sie heraus, ob eine Spalte Datumswerte enthält? 3.5 Metadaten einer Datenbankverbindung Auch über eine Datenbankverbindung und das verwendete Datenbanksystem gibt es Metadaten. Hierzu holt man sich zunächst ein Objekt der Klasse DatabaseMetaData zu einer bestehenden Datenbankverbindung mit conn.getMetaData() und verwendet dies bei den in Tabelle 2 auf Seite 16 aufgeführten Methoden. 3.6 SQL-Escapes SQL-Escapes dienen zur Egalisierung von Unterschieden zwischen Datenbanksystemen, um Programme portabler zu machen, auch wenn die SQL-Dialekte etwas voneinander abweichen. Sie dienen u. a. zur Darstellung von Werten, die nicht bei allen Datenbanksystemen gleich dargestellt werden, nämlich Zeit- und Datumsstrings, siehe Tabelle 3 auf Seite 16. Außerdem kann man mit ihnen Funktionen und Prozeduren aufrufen (wird hier nicht erläutert) und Outer Joins datenbankneutral formulieren. Mit der Methode nativeSQL(query) kann man eine Datenbankanfrage, die mit SQLEscapes formuliert ist, in den datenbankspezifischen SQL-Dialekt übersetzen lassen, beispielsweise in die Oracle-spezifische Darstellung von Outer Joins. Die einzugebende Schreibweise orientiert sich am ANSI-Standard. 3.7 Transaktionen Ohne weitere Festlegung wird bei JDBC immer Autocommit verwendet, so dass jede einzelne Anweisung in ihrer eigenen Transaktion ausgeführt wird – sofern die verwendete Datenbank überhaupt Transaktionen unterstützt (was man auch herausfinden kann, siehe Abschnitt 3.5). 3.7.1 Beginnen und Beenden von Transaktionen Die für Transaktions-Management notwendigen Methoden werden mit dem VerbindungsObjekt (hier: conn) aufgerufen. Um Transaktionen zu benutzen, muss man zunächst Auto- 3.8 Fehler und Warnungen 3 PROGRAMMIERUNG MIT JDBC commit ausschalten: conn.setAutoCommit(false). Den aktuellen Status kann man mit conn.getAutoCommit() abfragen. Bei jeder Anweisung wird eine neue Transaktion begonnen, wenn nicht bereits eine solche begonnen wurde. Sie kann dann mit conn.commit() abgeschlossen oder mit conn.rollback() zurückgefahren werden. 3.7.2 Isolationslevel Den aktuell vewendeten Isolationslevel kann man abfragen mit conn.getTransactionIsolation();, dessen Ergebnis man mit den Konstanten TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_ READ und TRANSACTION_SERIALIZABLE vergleichen muss, um den Level festzustellen. Der Wert TRANSACTION_NONE wird bei „Datenbanken“ geliefert, die keine Transaktionen unterstützen (und daher eigentlich gar keine Datenbanken sind). Entsprechend kann man den Isolationslevel auch setzten mit conn.getTransactionIsolation(level);. Beim Absetzen von Datendefinitionskommandos innerhalb von Transaktionen reagieren die einzelnen Datenbanksysteme völlig unterschiedlich. Daher kann man das genaue Verhalten mit diversen Methoden abfragen, die alle nicht mit dem Verbindungsobjekt, sondern mit dem Metadaten-Objekt (siehe Abschnitt 3.5 auf der vorherigen Seite) aufgerufen werden und einen boolean-Wert liefern: dataDefinitionIgnoredInTransactions(), dataDefinitionCausesTransactionCommit(), supportsDataDefinitionAndDataManipulationTransactions(), supportsDataManipulationTransactionsOnly(). 3.8 Fehler und Warnungen 3.8.1 Fehler Bei Fehlern werden Java-Exceptions ausgelöst, so dass keine ständige manuelle Prüfung notwendig ist. Der Mechanismus ist vergleichbar mit den WHENEVER-Konstruktionen bei Embedded SQL in C. Die ausgelöste Exception enthält nähere Information über den Fehler. Abfragen kann man eine Fehlermeldung mittels toString(). Den SQLSTATE gemäß ANSI kann man mit der Methode getSQLState() des Exception-Objekts ermitteln. Der SQLSTATE ist eine fünf Zeichen lange Zeichenkette. Die ersten beiden Zeichen geben die Fehlerklasse an, die letzten drei Zeichen den genauen Fehler. In nebenstehender Tabelle sind die wichtigsten Fehlerklassen von PostgreSQL aufgeführt (nähere Information ist im Kapitel „Error Handling and Diagnostics“ des „Programmer’s Guide to the Oracle Precompilers“ bzw. im Appendix A der PostgreSQL-Dokumention zu finden). Damit sind die Fehlerinformationen aus SQLSTATE wesentlich umfangreicher und ausführlicher als die aus dem SQLCODE. Den hier so genannten Error Code bekommt man mit 3 PROGRAMMIERUNG MIT JDBC der Methode getErrorCode(). Der Error Code ist – jeweiligen Datenbanksystem abhängig. SQLSTATE ist siert. Zu einem Zeitpunkt können – je nach Datenbanksystem – nicht nur eine, sondern auch mehrere Fehlermeldungen erzeugt worden sein. Zunächst wird immer der schwerwiegendste Fehler gemeldet, aber zur Bestimmung der Ursache kann es hilfreich sein, sich auch die weiteren Meldungen anzuschauen, wozu man die Methode getNextException() verwendet. 3.8.2 Warnungen 3.9 Prepared Statements im Gegensatz zum SQLSTATE – vom dagegen in weiten Teilen standardiCode 00 01 02 03 08 09 0B 22 23 24 25 26 40 53 SQLSTATE-Klasse kein Fehler Warnung keine Daten Kommando unvollständig Verbindungsproblem Fehler bei Triggeraktion ungültiger Transaktionsbegin Datenfehler Integritätsverletzung ungültiger Cursorzustand ungültiger Transaktionszusta ungültiges SQL-Kommando Transaktions-Rollback unzureichende Ressourcen Über Warnungen informiert Java nicht automatisch, sondern sie müssen abgefragt werden. Mit Hilfe der Methode getWarnings(), die auf das Result Set angewendet werden, ermittelt man die erste Warnung. Gibt es keine, so wird null geliefert. Die jeweils nächste Warnung bekommt man, wenn man die Methode getNextWarning() auf das Warnungsobjekt anwendet. Die Klasse SQLWarning ist übrigens eine Unterklasse von SQLException, so dass man die im vorigen Abschnitt erläuterten Methoden zur Abfrage von Error Code und SQLSTATE verwenden kann, toString() gibt es natürlich auch. 3.9 Prepared Statements Prepared Statements (vorbereitete Kommandos) sollen Vorteile bei der Ausführungsgeschwindigkeit bieten, weil sie beim Vorbereiten einmal besonders gründlich optimiert werden und bei den nachfolgenden Ausführungen auf die bereits erfolgte Optimierung zurückgegriffen werden kann. Bei voll dynamischen Kommandos müssen Analyse und Optimierung bei jeder einzelnen Ausführung erneut durchgeführt werden. Man benötigt ein Statement-Objekt, d. h. ein Objekt der Klasse PreparedStatement. Diesem weist man den Rückgabewert der Methode prepareStatement (Abfrage) zu, die auf das Verbindungsobjekt angewendet wird. Die genannte Abfrage kann Platzhalter in Form von Fragezeichen enthalten. Diese Platzhalter sind von 1 an durchnumeriert und müssen vor der Ausführung mit einer der Methoden setInt (nr, intWert, setDouble (nr, doubleWert, setString (nr, StringWert usw. gefüllt werden. nr gibt dabei die laufende Nummer des Platzhalters an. Mittels setNull (nr kann für einen Platzhalter auch ein Nullwert eingetragen werden. Die vorbereitete Abfrage kann anschließend mit der Methode executeQuery() ausführen. Hier ein kleines Codebeispiel, das auf die bereits bestehende Verbindung conn zugreift. PreparedStatement pst; 3.9 Prepared Statements 3 PROGRAMMIERUNG MIT JDBC pst = conn.prepareStatement ("SELECT * FROM kunden where ort = ?"); pst.setString (1, "Bonn"); ResultSet result = pst.executeQuery(); Die Ergebnismenge result kann genauso wie in Abschnitt 3.1.6 auf Seite 7 beschrieben durchgeführt werden. Die Ergebnismenge muss natürlich auch freigegeben werden mittels result.close(), ebenso das vorbereitete Kommando genauso wie ein gewöhnliches Kommando mittels pst.close(). Es ist nicht garantiert, dass eine Datenbank von den möglichen Vorteilen der vorbereiteten Kommandos Gebrauch macht, d. h. es kann sein, dass es zwar die beschriebenen Methoden gibt, aber vielleicht doch keine Speicherung des Optimierungsergebnisses erfolgt. $Id: jdbc.tex,v efcb5b401798 2009/03/23 14:15:07 bibjah $ 3 PROGRAMMIERUNG MIT JDBC 3.9 Prepared Statements Tabelle 2: kleine Auswahl aus den Datenbank-Metadaten-Methoden getDatabaseProductName(); liefert einen String mit dem Namen des Datenbanksystems getTables(catalog, schemamuster, tabellenmuster, tabellentyp); liefert eine Ergebnismenge (genau wie executeQuery()) mit 5 Attributen: Katalogname, Schemaname, Tabellenname, Tabellentyp, Bemerkungen. Als Parameter werden übergeben: Katalogname (sofern vom Datenbanksystem unterstützt, sonst null für alle); Muster (mit % und _ als Platzhalter wie bei SQL allgemein üblich) für den Schemanamen, ggf. nur %; Muster für den Tabellennamen, ggf. nur %; Tabellentyp (mögliche Werte abfragbar mit getTableTypes() oder null für alle. supportsANSIEntryLevelSQL(); supportsANSIIntermediateLevelSQL(); supportsANSIFullLevelSQL(); liefert einen boolean-Wert, der angibt, ob der jeweilige ANSI-Level unterstützt wird oder nicht getTypeInfo(); liefert eine Ergebnismenge (genau wie executeQuery()) mit 18 Attributen, von denen hier nur die ersten 2 erläutert werden: SQL-Datentyp (diese stehen bei CREATE TABLE zur Verfügung), zugehöriger Java-Datentyp. Dieser wird in Form einer numerischen Konstanten angezeigt, wobei es folgende Entsprechungen gibt (Auswahl): bigint boolean double other time −5 16 8 1111 92 binary −2 char 1 float 6 real 7 timestamp 93 bit date integer smallint varbinary −7 91 4 5 −3 blob 2004 decimal 3 numeric 2 struct 2002 varchar 12 Tabelle 3: SQL-Escapes Escape-Notation {d ’yyyy-mm-dd’} {t ’hh:mm:ss’} {ts ’yyyy-mm-dd hh:mm:ss.ffffff ’} {oj tabelle1 NATURAL LEFT JOIN tabelle2} Darstellung von: Datum Zeit Zeitstempel (timestamp) Natural Left Outer Join
