Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher Java-Datenbankzugriff mit JDBC Vorlesungsmaterial zu JDBC von Prof. Dr. Stefan Böttcher Inhaltsverzeichnis: 1. Praktische Hinweise zum Arbeiten mit JDBC, ODBC und dem Datenbanksystem 2. Überblick über JDBC 3. Datenbankinitialisierung : Programmlisting dbinit.java 4. Datenselektion : Programmlisting dbselect.java 5. Metadaten der Datenbank abfragen: Programmlisting dbinf.java 6. Metadaten einer Tabelle abfragen: Programmlisting dbtab.java 7. Java-Datenbankzugriff mit JDBC unter Unix 8.-10. Übungsaufgaben 1 bis 3 11.-13. Applets und eine Übungsaufgabe dazu 14. Anschluß von Excel-Tabellen 1. Praktische Hinweise zum Arbeiten mit JDBC, ODBC und dem Datenbanksystem 1.1. Systemvoraussetzungen prüfen (für zuhause, nur 1x) Folgende Voraussetzungen benötigen Sie, um die Programme auf Ihrem Rechner zuhause zu starten (für Ihren Rechner an der Uni müssen Sie ggf. die Pfade entsprechend abändern): 1. Sie brauchen ein Datenbanksystem, z.B. Access97 unter Windows95/98/NT/2000/Me (wenn entsprechende Pfade gesetzt sind, kann man auch relationale DBMS unter Unix verwenden, z.B. Sybase etc. siehe Extra-Anleitung im letzten Abschnitt) 2. Das Java Development Kit von Sun, z.B. für Windows95/NT muß installiert sein, z.B. unter C:\jdk118 (sie können sich Java 1.1.8 kostenlos von http://www.javasoft.com/product/jdk/1.1/download-jdk-windows.html herunterladen). 3. Die Umgebungsvariable path muß das Java-Verzeichnis enthalten, also z.B. C:\jdk118 4. Die Umgebungsvariable classpath muß das aktuelle Verzeichnis und das Java-Verzeichnis enthalten, also z.B. set classpath=.;C:\jdk118 5. ODBC muß installiert sein. Seite 1 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 1.2. So wurden die Access-Datenbank erzeugt (nur 1x – ist jetzt nicht mehr nötig) Für die Übungen genügt eine leere Access-Datenbank. Unter Access97 starten Sie Access und wählen Sie "Erstellen einer neuen Datenbank unter Verwendung von leerer Datenbank" Wählen Sie "erstellen", speichern Sie Ihre Datenbank (z.B. unter C:\db2.mdb), und verlassen Sie Access.1 Die mitgelieferte Datenbank db2000.mdb wurde unter Access 2000 durch Konvertierung aus db2.mdb erstellt. 1.3. Datenbank unter ODBC anmelden (nur 1x , aber an der Uni und zuhause) Wählen Sie unter Windows 95/98/NT Arbeitsplatz | Systemsteuerung | ODBC und die Karte Benutzer-DSN bzw. unter Windows2000 Arbeitsplatz | Systemsteuerung | Verwaltung | ODBC und die Karte BenutzerDSN. Falls der Name "odbc2access" noch nicht eingetragen ist, tun Sie folgendes: clicken Sie auf "Hinzufügen" , und wählen Sie dann aus der Liste als Treiber den Namen "Microsoft Access-Treiber (*.mdb)". Clicken Sie auf "Fertigstellen". Wählen Sie als Datenquellenname "odbc2access". Im Panel Datenbank clicken Sie dann auf "Auswählen" und wählen Sie die erstellte Access-Datenbank aus, und zwar die mitgelieferte Datenbank db2.mdb für Acsess 97 und db2000.mdb für Access 2000. Wählen Sie mehrfach OK, um den ODBC-Datenquellen-Administrator zu verlassen. 1.4. Java-Pfade setzen am PC-Pool in F2 Am einfachsten ist es, wenn Sie Java 1.1.8 benutzen, weil es dann keine Probleme mit JavaSecurity bzw. mit dem Signieren von Applets geben sollte. (Alle Teile außer Aufgabe 4 sollten mit JDK1.3. identisch gehen.) Unter Netscape und unter dem Appletviewer aus JDK118 sollten alle Applets mit Datenbankzugriff laufen. 2 Um Java 1.1.8. am PC-Pool der Uni zu benutzen, setzen Sie die Umgebungsvariablen path und classpath in jeder DOS-Box mit einem Aufruf der Batch-Datei j118pool. Anschließend können Sie Java-Programme, z.B. dbinit.java mit javac dbinit.java übersetzen und das übersetzte Programm danach mit java dbinit starten. Arbeitsverzeichnis > j118pool Arbeitsverzeichnis > javac dbinit.java Arbeitsverzeichnis > java dbinit Das Programm dbinit.java erzeugt Ihnen unsere Beispieldatenbank. Es zeigt Ihnen, wie man Tabellen anlegt und löscht und wie man Datensätze im Java-Programm zusammenbaut und in die Datenbank einträgt. Ein anderes Programm dbselect.java zeigt Ihnen, wie man mit JDBC Anfragen an die Datenbank stellt und die Ergebnisse im Java-Programm weiter verarbeitet. dbinf.java und dbtab.java zeigen den Zugriff auf Meta-Information für die Datenbank bzw. für einzelne Tabellen. 1 Unter Access XP existiert dieses Fenster nicht mehr. Statt dessen gibt es am rechten Rand eine Leiste, in der man „Neu…/Leere Datenbank“ wählen muss. 2 Wenn Sie Java 1.2. oder 1.3. benutzen und vom Applet auf die Datenbank zugreifen wollen, braucht der Appletviewer ein entsprechendes Recht, was durch ein policy-File gesetzt werden kann. Folgende Möglichkeit wurde im Verzeichnis ..\dabaw99\jdbckurs (JDBC-Kursunterlagen vom WS99) getestet: appletviewer –J–Djava.security.policy=all.policy aufgabe5.html Seite 2 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 2. Überblick über JDBC Was ist und was bietet JDBC Paket von Java-Klassen zum DB-Zugriff mit SQL vom Ziel-DBMS unabhängige API Standard seit Java 1.1 Java-Programm JDBC: API Treibermanager Treiber Zieldatenbanksystem Java-Interfaces von JDBC SQLDriver: Treiber für ein Ziel-DBMS oder ODBC SQLDriverManager : registriert Treiber Connection : Für Verbindungen Statement : für Statement-Objekt, z.B. Query ResultSet : für Ergebnismenge Die Klassen zu diesen Interfaces werden von DB-Herstellern implementiert JDBC-ODBC-Brücke wird von SUN mitgeliefert Seite 3 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher Datenbankzugriffe mit JDBC Treiber laden Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Verbindung herstellen con = DriverManager.getConnection("jdbc:odbc:odbc2access"); Statement-Objekte definieren Statement stmt = con.createStatement() ; Datenbank zugreifen, z.B. einfügen stmt.executeUpdate( “insert into Liefert values(‘IBM‘, ‘pc500‘, 2500,6) “); Statement und Verbindung zum DBMS schließen stmt.close( ) ; con.close( ) ; Datenbank-Anfragen mit JDBC Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:odbc2access"); Statement stmt = con.createStatement() ; Datenbankanfrage stellen ResultSet rsLiefert = stmt.executeQuery( “ select * from Liefert where Teil = ‘pc500‘ “); while ( rsLiefert.next( ) ) // hole nächstes Tupel aus Result-Set { ausgabe += rsLiefert.getString( "Lieferant" ) ; // ggf. weitere Spalten ausgeben } stmt.close( ) ; con.close( ) ; Seite 4 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 3. Datenbankinitialisierung : Programmlisting dbinit.java import java.sql.*; public class dbinit { public static void main( String[] args ) { String ergebnis = "" ; try { Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Treiber für ODBC Connection con = DriverManager.getConnection("jdbc:odbc:odbc2access"); // : :DB-Name unter ODBC ergebnis = makeDB( con ) ; System.out.println( ergebnis ) ; con.close() ; // darf nicht von einer Exception aus makeDB übersprungen werden! } catch (Exception e) { System.out.println( e ) ; } } public static String makeDB( Connection con ) { String ausgabe="" ; // String zum Sammeln der Ausgabe try { Statement stmt = con.createStatement() ; try { stmt.executeUpdate( "drop table Liefert" ); } catch (Exception e) { } stmt.executeUpdate( "create table Liefert( Lieferant char(10), Teil char(10), " + " Preis int, Lieferzeit int ) " ); stmt.executeUpdate( "Insert into Liefert stmt.executeUpdate( "Insert into Liefert stmt.executeUpdate( "Insert into Liefert stmt.executeUpdate( "Insert into Liefert stmt.executeUpdate( "Insert into Liefert stmt.executeUpdate( "Insert into Liefert Seite 5 von 13 values('Vobis ','pc400',1700,3)" ); values('Dell ','pc500',2000,6)" ); values('IBM ','pc500',2500,6)" ); values('Vobis ','pc500',2000,3)" ); values('IBM ','pc600',3500,4)" ); values('Vobis ','pc600',2500,3)" ); Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher try { stmt.executeUpdate( "drop table Auftrag" ); } catch (Exception e) { } stmt.executeUpdate( "create table Auftrag( Kunde char(10), PC char(10) ) " ); stmt.executeUpdate( "Insert into Auftrag values('Arm ','pc400') " ); stmt.executeUpdate( "Insert into Auftrag values('Meier','pc500') " ); stmt.executeUpdate( "Insert into Auftrag values('Reich','pc600') " ); stmt.close(); // Statement schließen ausgabe += "\nDatenbank initialisiert.\n" ; } catch (Exception e) { ausgabe += "\n" + "Fehler: " + e ; } return ausgabe ; } // makeDB zuende } // class zuende Seite 6 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 4. Datenselektion : Programmlisting dbselect.java import java.sql.*; public class dbselect { public static void main( String[] args ) { String ergebnis = "" ; try { Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Treiber für ODBC Connection con = DriverManager.getConnection("jdbc:odbc:odbc2access"); // : :DB-Name unter ODBC ergebnis = selectTab( con , "2200" ) ; System.out.println( ergebnis ) ; con.close() ; } catch (Exception e) { System.out.println( e ) ; } } public static String selectTab( Connection con, String limit ) { String ausgabe="" ; // String zum Sammeln der Ausgabe try { Statement stmt = con.createStatement() ; ResultSet rsLiefert = stmt.executeQuery( "SELECT * FROM Liefert WHERE Preis < " + limit ) ; // // Strings in SQL müßten zusätzlich in einfache Hochkommas: "SELECT * FROM Liefert WHERE Teil = '" + limit + "'" ) ; ausgabe += "\n\nLiefert:\n( Lieferant Teil" + " Preis Lieferzeit )" ; while (rsLiefert.next()) // hole nächstes Tupel aus Result-Set { ausgabe += "\n" + rsLiefert.getString("Lieferant") + " " + rsLiefert.getString("Teil") + " " + rsLiefert.getInt("Preis") + " " + rsLiefert.getInt("Lieferzeit") ; } rsLiefert.close() ; stmt.close() ; // Result-Set schließen // Statement schließen } catch (Exception e) { ausgabe += "\nFehler bei Anfrage an die Datenbank:\n" + e ; } return ausgabe ; } // selectTab zuende } // class zuende Seite 7 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 5. Metadaten der Datenbank abfragen : Programmlisting dbinf.java import java.sql.*; public class dbinf { public static void main( String[] args ) { String ergebnis = "" ; try { Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Treiber für ODBC Connection con = DriverManager.getConnection("jdbc:odbc:odbc2access"); // : :DB-Name unter ODBC ergebnis = accessDB( con ) ; System.out.println( ergebnis ) ; con.close() ; } catch (Exception e) { System.out.println( e ) ; } } public static String accessDB( Connection con ) { String ausgabe="" ; // String zum Sammeln der Ausgabe try { DatabaseMetaData md = con.getMetaData(); // Metadaten holen // Hilfsvariable final String[] tabellen = {"TABLE"}; // Hole Tabellennamen ResultSet tablesNames =md.getTables(null,null,null,tabellen); while (tablesNames.next()) { // Hole Tabellenname String tablename = new String(tablesNames.getString(3)); ausgabe += tablename + "\n" ; } } catch (Exception e) { ausgabe += e ; } return ausgabe; } // accessDB } // dbinf Seite 8 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 6. Metadaten einer Tabelle abfragen : Programmlisting dbtab.java import java.sql.*; public class dbtab { public static void main( String[] args ) { String ergebnis = "" ; try { Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Treiber für ODBC Connection con = DriverManager.getConnection("jdbc:odbc:odbc2access"); // : :DB-Name unter ODBC ergebnis = accessTab( con , "Auftrag" ) ; System.out.println( ergebnis ) ; con.close() ; } catch (Exception e) { System.out.println( e ) ; } } public static String accessTab( Connection con , String tabelle ) { int spalte; String ausgabe="" ; // String zum Sammeln der Ausgabe try { Statement stmt = con.createStatement(); // Statement anlegen ResultSet rs = stmt.executeQuery("select * from " + tabelle); // Hole Meta-Daten für dieses Result Set ResultSetMetaData rsmd= rs.getMetaData(); int spaltenAnzahl = rsmd.getColumnCount(); for( spalte=1 ; spalte <= spaltenAnzahl ; spalte++ ) { ausgabe += rsmd.getColumnLabel( spalte ) + "\t\t" ; } ausgabe += "\n-------------------------------------\n" ; while (rs.next()) { for( spalte=1 ; spalte <= spaltenAnzahl ; spalte++ ) { ausgabe += rs.getString(spalte) + "\t" ; } ausgabe += "\n" ; } } catch (Exception e) { ausgabe += e ; } return ausgabe; } // accessTab } // dbtab Seite 9 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 7. Übungsaufgabe 1: Datenbestände aufbauen und abfragen A1.1. Übersetzen und starten Sie erst dbinit.java und dann dbselect.java , um zu sehen, wie die Datenbank initialisiert wird. A1.2. Ergänzen Sie zwei stmt.executeUpdate – Befehle in dbinit.java , nämlich für folgende Informationen: "Dell liefert auch noch einen pc400 für 1800 in 2 Tagen" und "Reich gibt auch noch einen pc500 in Auftrag" . Übersetzen und starten Sie dbinit.java erneut, und starten Sie dann dbselect.class , um sich die Änderungen anzusehen. A1.3. Programmieren Sie in der Datei dbselect.java die Queries: „Wer liefert (mindestens) ein Teil, das Reich bestellt hat“ „Wer liefert alle Teile, die Reich bestellt hat in maximal 4 Tagen“ und lassen Sie das Ergebnis via JDBC von Java ausgeben. Ändern Sie ggf. Ihren Datenbestand, um die Korrektheit Ihrer Programme zu testen. 8. Übungsaufgabe 2: Meta-Datenbestände aufbauen und abfragen A2: Schreiben Sie ein Programm, das den kompletten Datenbestand aller Tabellen der Datenbank ausgibt. (Tipp: Eine Kombination aus dbinf und dbtab) 9. Java-Datenbankzugriff mit JDBC unter Unix Zuerst auf einem Unix-Rechner einloggen, vom PC aus z.B. mit telnet (oder ssh): Start | Ausführen | telnet Im telnet-Fenster: open waterway.upb.de (oder ein anderer Unix-Rechner) + einloggen In den classpath zusätzlich eintragen: .:/homes/beethoven/sybase/jConnect/classes:$classpath Dateien dbinit.java etc. auf diesen Unix-Rechner übertragen und folgendes anpassen: Treiber laden Class c = Class.forName(“com.sybase.jdbc.SybDriver“) ; Verbindung herstellen con = DriverManager.getConnection( "jdbc:sybase:Tds:beethoven.uni-paderborn.de:4100/datenbanken01" , "userid", "password" ); Dabei sind die Strings userid und password durch Ihre Datenbank-Userid und Ihr Datenbank-Paswort zu erstetzen. Alles weitere wie für Access: Statement-Objekt definieren, Datenbank zugreifen, Statement und Verbindung zum DBMS schließen Java-Programm compilieren und starten (jedoch mit jConnect im classpath). 10. Übungsaufgabe 3: Sybase statt Access zugreifen A3: Ändern Sie Ihre Programme so, daß sie auf eine Sybase-Relation zugreifen. Seite 10 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 11. Programmlisting applet1.java ( ein Applet mit TextField und TextArea ) import java.awt.*; import java.sql.*; // erst für später nötig public class applet1 extends java.applet.Applet { static String ausgabe ; Label l1 = new Label("Ihre Bestellung") ; TextField tfIO = new TextField("pc500",15) ; TextArea taOutput = new TextArea("",20,70); public void init() // wird 1x beim starten gerufen { this.add("North", l1) ; this.add("North", tfIO) ; this.add("Center", taOutput) ; } public void paint (Graphics g) // bei jedem Neuzeichnen { ausgabe = "" ; ausgabe += "\nIhre Bestellung: " ; ausgabe += "\n" + tfIO.getText() ; taOutput.setText(ausgabe); // in die TextArea schreiben } } 12. Programmlisting applet1.html ( der HTML-Rahmen für das Applet ) <html> <head> <title>Main</title> </head> <body> <hr> Ein Applet, das Werte des Textfeldes (oben) beim Neuzeichen (paint) in die TextArea &uuml;bernimmt <p> <applet code="applet1.class" width="550" height="400"> </applet> </p> <hr> </body> </html> Seite 11 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher 13. Übungsaufgabe 4 (geht voraussichtlich nur mit JDK118 und Netscape oder Appletviewer) : Applets zum DB-Zugriff Hinweis: Hierbei wird der Sicherheitsmechanismus von Applets (zu Demonstrationszwecken) umgangen. Eigentlich müsste das Applet anders mit der Datenbank verbunden werden. A4.1. Integrieren Sie das Programm dbselect.java in das Beispiel-Applet. Modifizieren Sie die Query so, daß in der textArea des Applets das Ergebnis der Datenbankanfrage „Wer liefert einen pc500“ zu sehen ist. Dazu müssen Sie (mindestens) folgendes tun: 1. Funktionsdefinition ersetzen: static void main ( String [ ] args ) ersetzen durch void mymain() 2. mymain() aufrufen in paint() ; 3. Ausgabe umleiten, d.h. System.out.println(“XYZ“) ; ersetzen durch ausgabe = ausgabe + “XYZ“ ; Übersetzen Sie das neu erzeugte Programm. Starten Sie das Applet, indem Sie das HTML-File in den Browser laden. A4.2. Erweitern Sie das Programm so, daß das TextField des Applets in der Funktion mymain() gelesen wird, und daß nur nach Lieferungen von dem Teil gesucht wird, das in diesem TextField angegeben ist. Übersetzen Sie das geänderte Programm und starten das Applet. Testen Sie, ob sich die angezeigten Teile ändern, wenn Sie pc600 statt pc500 in das TextField eingeben und die Fenstergröße verändern. A4.3. Eleganter ist es, die Datenbank-Verbindung nur einmal (in der init()-Methode) aufzurufen. Ändern Sie das Applet entsprechend ab (falls das noch nicht geschehen ist). 14. Übungsaufgabe 5: Excel anschließen A5. Ändern Sie die Programme so, dass Sie einen ODBC-Treiber für Excel anstatt einem ODBC-Treiber für odbc2access benutzten, z.B. ein Programm exinit.java statt dbinit.java . Beim Einrichten des ODBC-Treibers ist zu beachten (getestet unter Windows 2000 mit Office 2000 bzw. Excel2000): Excel-Treiber hinzufügen Datenquellennamen ausdenken (könnte z.B. odbc2excel heißen) und an eine existierende Excel-Datei anschließen. (Die Excel-Datei muß bereits existieren, sie kann aber leer sein.) Unbedingt den Schreibschutz für diese Excel-Datei aufheben! im ODBC-Fenster (wo auch Name und Excel-Tabelle verknüpft werden) Optionen wählen und Schreibschutz aufheben. Seite 12 von 13 Datenbanken und Informationssysteme 1 WS 2001 Java-Datenbankzugriff mit JDBC Prof. Dr. Böttcher Evtl. muß zusätzlich im Windows-Explorer der Schreibschutz aufgehoben werden mit: Excel-Datei anklicken , dann rechte Maustaste | Eigenschaften | Karte Allgemein wählen und ggf den Haken bei Schreibschutz entfernen Im Programm (z.B. exinit.java) muß der Excel-Datenquellenname angesprochen werden, zB. odbc2excel. Ändern Sie in Excel-Tabelle das Datenblatt zu Auftrag von Hand, und schließen Sie die Excel unbedingt, bevor Sie dann mit einer für Excel geschriebenen Variante des Programms dbtab.java darauf zugreifen. . Wenn die Excel-Tabelle schreibgeschützt ist und „hängt“, d.h. nicht mehr zugreifbar ist, (passierte mir nach dem Zugriff auf eine gesperrte Tabelle) empfehle ich, über ODBC eine andere Excel-Tabelle anzubinden. Excel unter Office XP Bei Office XP (an der Uni) muss zusätzlich in der Excel-Tabelle, die man beschreiben möchte, der beschreibbare Tabellenbereich auf die richtige Größe eingestellt werden. Nach dem die Excel-Tabelle durch JDBC initiiert wurde (mit exinit.java) muss man dafür folgende Schritte ausführen: 1. Die Excel-Mappe öffnen 2. Menüpunkt Einfügen -> Namen -> Definieren wählen 3. Im Auswahlfeld die zu erweiternde Tabelle auswählen 4. Unten im Fenster die maximale Tabellengröße vorwählen, z.B. „ =Auftrag!$A$1:$B10“ für eine Auftragstabelle mit 2 Spalten (A und B) und 10 Zeilen (1 bis 10) 5. Mit OK bestätigen. Wenn man dieses für alle Tabellen der "Excel-Datenbank" durchführt, können diese Tabellen beliebig mit Excel oder über die JDBC erweitert werden. Seite 13 von 13