Java-Datenbankzugriff mit JDBC

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. Übungsaufgaben
2.1. Datenbestände aufbauen und abfragen in JDBC (mit Access)
2.2. Meta-Datenbestände aufbauen und abfragen in JDBC (mit Access)
2.3. Anbindung von Excel-Tabellen
2.5. Nutzung einer entfernten Sybase-Datenbank (Sybase-Anmeldung erforderlich !!)
3. Überblick über JDBC (Auswahl der Folien)
4. Beispielprogramme
4.1. Datenbankinitialisierung : Programmlisting dbinit.java
4.2. Datenselektion : Programmlisting dbselect.java
4.3. Metadaten der Datenbank abfragen: Programmlisting dbinf.java
4.4. Metadaten einer Tabelle abfragen: Programmlisting dbtab.java
1. Praktische Hinweise zum Arbeiten mit JDBC, ODBC und dem Datenbanksystem
1.1. Systemvoraussetzungen prüfen (nur 1x – nur zuhause – nicht im PC Pool in F2)
Folgende Voraussetzungen benötigen Sie, um die Programme auf Ihrem Rechner zu starten (für Ihren
Rechner zuhause müssen Sie ggf. die Pfade entsprechend abändern) – alle Voraussetzungen sollten auf
Ihrem Rechner im PC-Pool in F2 an der Uni bereits gegeben sein:
1. Sie brauchen ein Datenbanksystem, z.B. Access 2000 unter Windows 95/98/NT/2000/Me/XP
(wenn entsprechende Pfade gesetzt sind, kann man auch beliebige relationale DBMS (z.B. unter
Windows oder unter Unix) verwenden, z.B. Oracle oder Sybase siehe Extra-Anleitung in den
Abschnitten 2.4 bzw. 2.5.
2. Das Java Development Kit von Sun, z.B. für Windows XP muss installiert sein, z.B. unter
c:\j2sdk1.4.0_01. Von http://java.sun.com/js2e/1.4.2/download.html können Sie sich Java
kostenlos herunterladen (Java-Version 1.3. genügt aber auch).
3. ODBC muss installiert sein.
1.2. So wurde die Access-Datenbank erzeugt (nur 1x – ist jetzt nicht mehr nötig)
Seite 1 von 12
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 , ist aber an der Uni und zuhause nötig)
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 bzw. unter Windows XP Arbeitsplatz | Systemsteuerung | Verwaltung | Datenquellen(ODBC) und die
Karte Benutzer-DSN.
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 db2000.mdb für Access 2000 (bzw. db2.mdb für Access 97).
Wählen Sie mehrfach OK, um den ODBC-Datenquellen-Administrator zu verlassen.
1.4. Java-Pfade setzen am PC im Pool in F2 (welche Pfade hängt vom Pool-Rechner ab !)
Um Java 1.4. am PC-Pool der Uni zu benutzen, setzen Sie die Umgebungsvariablen path und classpath
in jeder DOS-Box mit einem Aufruf der Batch-Datei j13pool oder j14pool auf
set classpath=.;c:\j2sdk1.4.0_01\lib\dt.jar;c: j2sdk1.4.0_01\lib\tools.jar;%classpath%
set path=c:\j2sdk1.4.0_01\bin;%path%
oder auf
set classpath=.;c:\jdk1.3.1_04\lib\dt.jar;c:\jdk1.3.1_04\lib\tools.jar;%classpath%
set path=c:\jdk1.3.1_04\bin;%path%
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 > j14pool
<oder>
Arbeitsverzeichnis > javac dbinit.java
Arbeitsverzeichnis > java dbinit
j13pool
1.5. Überblick über die Beipielprogramme
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. Die Programn exinit.java,
exselect.java, exselect.java, exinf.java und extab.java zeigen dieselben Dinge für eine Excel-Tabelle. Das
Programm excel2db.java zeigt die Umwandlung von excel und access-Tabellen ineinander.
1
Unter Access XP existiert dieses Fenster nicht mehr. Stattdessen gibt es am rechten Rand eine Leiste, in der man
„Neu…/Leere Datenbank“ wählen muss.
Seite 2 von 12
2. Übungsaufgaben
2.1. Ü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“
und lassen Sie das Ergebnis via JDBC von Java ausgeben.
Ändern Sie ggf. Ihren Datenbestand, um die Korrektheit Ihrer Programme zu testen.
2.2. Ü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.java und dbtab.java)
2.3. Übungsaufgabe 3: Excel anschließen
2.3.1
Vorbereitung der Übungen zu Excel: Excel-Tabelle anschließen
Die Übung aus zum Zugriff auf eine Access-Datenbank mit JDBC wird vorausgesetzt. In dieser Übung geht
es insbesondere darum, diese Übung auf Excel zu erweitern. Beim Einrichten des ODBC-Treibers ist
folgendes 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 (rechts: Kästchen “Schreibgeschützt“ muss OHNE Kreuz sein).
Seite 3 von 12
Evtl. muss 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) muss der Excel-Datenquellenname angesprochen werden, z.B. 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.
2.3.2. Aufgabe 3.1. Zugriff auf Excel statt auf Access
A3.1. In den Beispielprogrammen mitgeliefert sind Dateien dbinit.java (2 Tabellen anlegen), dbselect.java
(Query an eine Tabelle stellen), dbinf (Datenbankschema abfragen), dbtab.java (Tabelle ausgeben). Ändern
Sie die 4 Programme so, dass Sie einen ODBC-Treiber für Excel (z.B. odbc2excel) anstatt einem ODBCTreiber für Access (odbc2access) benutzten, und schreiben Sie z.B. ein Programm excelinit.java statt
dbinit.java , usw. und testen Sie die Programme mit einer Excel-Datei aus (z.B. mit Mappe1.xls).
2.3.3. Datenaustausch von Access nach Excel
Entwickeln Sie aus den Programmen excel2db.java, dbinf.java und dbtab.java ein Programm, das eine
möglichst beliebige Access-Datenbank als Excel-Datei mit je einem Excel-Datenblatt pro Access-Tabelle
abspeichert. Sie sollen bzw. dürfen voraussetzen, dass zu jeder Access-Tabelle ein Excel-Datenblatt vorliegt,
das genügend groß ist (falls nötig legen Sie dieses per Hand an).
2.3.4. Datenaustausch von Excel zurück nach Access
Entwickeln Sie ein Programm für die Rückrichtung, das eine ggf. geänderte Excel-Datei in einer AccessDatenbank mit je einer Access-Tabelle pro Excel-Datenblatt abspeichert (ohne Blatt1-3).
Sie sollen bzw. dürfen voraussetzen, dass jedes Excel-Datenblatt zuvor aus einer Access-Tabelle erzeugt
wurde (wie im Programm zur Aufgabe 2.3.3.).
Seite 4 von 12
2.5. Alternative: Datenbankzugriff mit JDBC auf das Sybase-Datenbanksystem unter Unix
(für diejenigen, die lieber unter Unix arbeiten – Sie müssen Ihren alten Sybase-DatenbankAccount aus TSE2 verwenden)
Vorbereitung der Alternativübung: Sybase statt Access zugreifen
Zuerst auf einem Unix-Rechner einloggen, vom PC aus z.B. mit ssh:
Start | Ausführen | ssh
Im ssh-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-Passwort zu
ersetzen.
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).
2.5.1. Ändern Sie das Programm excel2db.java so, dass Sie statt auf Access auf Sybase zugreifen.
2.5.2. Formulieren Sie die SQL-Anfrage “Wer liefert alle Teile, die Reich bestellt hat“ (und testen Sie diese
Anfrage mit geeigneten Daten).
Seite 5 von 12
3. Folien Ü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 JD B C
S Q L D riv e r: T re ib e r fü r e in Z ie l-D B M S o d e r O D B C
S Q L D riv e rM a n a g e r : re g is trie rt T re ib e r
C o n n e c tio n : F ü r V e rb in d u n g e n
S ta te m e n t : fü r S ta te m e n t-O b je k t, z.B . Q u e ry
R e s u ltS e t : fü r E rg e b n is m e n g e
D ie K la s s e n zu d ie s e n In te rfa c e s w e rd e n v o n
D B -H e rs te lle rn im p le m e n tie rt
J D B C -O D B C -B rü c k e w ird v o n S U N m itg e lie fe rt
Seite 6 von 12
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 7 von 12
4. Die Beispielprogramme (Auszug)
4.1. 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 8 von 12
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)" );
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
Dasselbe mit Excel (statt Access)
Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Treiber für ODBC
Connection con =
DriverManager.getConnection("jdbc:odbc:odbc2excel");
//
: ODBC-Name der ExcelTabelle
Dasselbe mit Oracle (statt Access)
Class c = Class.forName("oracle.jdbc.OracleDriver");
// Treiber für Oracle
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@131.234.48.168:1521:oradb01",
"oracle_login" , "oracle_passwort");
Dasselbe mit Sybase (statt Access)
Class c = Class.forName("com.sybase.jdbc.SybDriver");
// Treiber für Sybase
Connection con = DriverManager.getConnection(
"jdbc:sybase:Tds:beethoven.uni-paderborn.de:4100/datenbanken01",
"sybase-userid" , "sybase-password" ) ;
Seite 9 von 12
4.2. 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 10 von 12
4.3. 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 11 von 12
4.4. 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 12 von 12