11. JDBC

11. JDBC
•
•
•
•
•
Grundsätzliche Nutzung von JDBC
Verbindungsaufbau
Anfragen
Analyse des erhaltenen Ergebnisses
Veränderungen des Ergebnisses
Ziel: Verständnis für Konzepte von JDBC aufbauen
Datenbanken
Prof. Dr.
Stephan Kleuker
270
Überblick: Datenbankanfragen mit JDBC
class DriverManager
Datenbankverbindung Connection con=
DriverManager.getConnection(...);
herstellen
Statement stmt=
con.createStatement();
Datenbankanfrage
Ergebnisse
verarbeiten
Verbindung zur DB
schließen
Datenbanken
ResultSet rs =
stmt.executeQuery(...);
rs.next();
int n = rs.getInt("KNr");
con.close();
Prof. Dr.
Stephan Kleuker
271
Verbindungsaufbau mit einer Datenbank
• Laden des Datenbanktreibers
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver());
• Aufbau einer Verbindung
Connection con =
DriverManager.getConnection
("jdbc:postgresql://rechner:5712/eshop"
,"user", "password");
• JDBC-URL identifiziert Datenbank
Aufbau: jdbc:subprotocol:subname
• Treibermanager übergibt JDBC-URL der Reihe nach
an registrierte Treiber
Driver.acceptsURL(String url)
liefert true, falls der Treiber den String akzeptiert
Datenbanken
Prof. Dr.
Stephan Kleuker
272
Auslesen der Oracle-Verbindungsdaten
• Oracle „versteckt“ irgendwo Verbindungsdaten in
tnsnames.ora
• benötigt wird IP-Adresse, Port (default 1521), Name der DBInstanz (z. B. in SID) + Nutzername und Passwort
• Daraus abgeleiteter Connection-String für HS-DB (SID: Ora11)
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:"
+ "@oracle-srv.edvsz.hs-osnabrueck.de"
+ ":1521:Ora11", "username", "passwort");
• Connection-String für DB auf lokalem Rechner (Oracle XE)
Connection con = DriverManager
.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe"
, "username", "passwort");
Datenbanken
Prof. Dr.
Stephan Kleuker
273
Connection
• Ergebnis von DriverManager.getConnection() ist eine
Connection con (oder eine SQLException)
• Connection ist Verbindung zur Datenbank
• Connection ist teure Ressource:
– Aufbau der Verbindung kostet viel Zeit
– Anzahl gleichzeitiger Verbindungen häufig beschränkt
• Wichtigste Aufgaben:
– Erzeugen von Statement-Objekten
– Beschreibungen von Datenbank und DBMS erfragen
– Transaktionen handhaben
Datenbanken
Prof. Dr.
Stephan Kleuker
274
Verbindungsanalyse durch Metadaten (Ausschnitt)
DatabaseMetaData dbmd = con.getMetaData();
System.out.println("DB-Name: " + dbmd.getDatabaseProductName()
+ "\nDB-Version: " + dbmd.getDatabaseMajorVersion()
+ "\nDB-Release: " + dbmd.getDriverMinorVersion()
+ "\nTransaktionen erlaubt: " + dbmd.supportsTransactions()
+ "\nbeachtet GroßKlein :" + dbmd.storesMixedCaseIdentifiers()
+ "\nunterstützt UNION :" + dbmd.supportsUnion()
+ "\nmax. Prozedurname: " + dbmd.getMaxProcedureNameLength());
DB-Name: Oracle
DB-Version: 11
DB-Release: 2
Transaktionen erlaubt: true
beachtet GroßKlein :false
unterstützt UNION :true
max. Prozedurname: 30
Datenbanken
Prof. Dr.
Stephan Kleuker
275
Statement / Anfrage ausführen
• Statement stmt = con.createStatement();
• Wird immer aus bestehender Connection erzeugt
• Aufgabe: Ausführen einer SQL-Anweisung über die
Connection
• Mehrere parallele Statements pro Connection möglich
• SELECT-Anweisung ausführen:
ResultSet rs =
stmt.executeQuery("SELECT * FROM Kunde");
• Daten ändern:
int updates =
stmt.executeUpdate("DELETE FROM Kunde
...");
Datenbanken
Prof. Dr.
Stephan Kleuker
276
Metadaten des Anfrageergebnisses
ResultSet rs = stmt.executeQuery("SELECT * FROM Continent");
ResultSetMetaData rsmd = rs.getMetaData();
int spalten = rsmd.getColumnCount();
for (int i = 1; i <= spalten; i++) { // nicht i=0
System.out.println(i + ". Name: " + rsmd.getColumnName(i)
+ " Typ: " + rsmd.getColumnTypeName(i)
+ " Javatyp: " + rsmd.getColumnClassName(i));
}
1. Name: NAME Typ: VARCHAR2 Javatyp: java.lang.String
2. Name: AREA Typ: NUMBER Javatyp: java.math.BigDecimal
Datenbanken
Prof. Dr.
Stephan Kleuker
277
Analyse von ResultSet
ResultSet rs =
stmt.executeQuery("SELECT * FROM Kunde");
• Ergebnis einer Selektionsanweisung: Tabelle
• ResultSet enthält einen Datensatz-Zeiger (Cursor) zum
Durchlauf der Tabelle
• Voreinstellung: sequenziell und lesend
• ab JDBC 2: nichtsequenzielle und aktualisierbare ResultSets
• Zeiger steht initial vor der ersten Tabellenzeile
• rs.next() positioniert zur nächsten Zeile, liefert false, falls
bereits auf letzter Zeile
Datenbanken
Prof. Dr.
Stephan Kleuker
278
Attributwerte auslesen (1/2)
• Spaltenwerte (Attribute) einer Zeile mit getXXX()-Methoden
lesen
• Treiber konvertiert Daten, falls möglich, deshalb (fast)
immer getString() nutzbar
• Beispiel: Lesen einer ganzen Zahl in DB-Spalte kundenNr:
int n = rs.getInt("kundenNr");
• Effizientere Methode, falls Spaltenindex bekannt:
int n = rs.getInt(4);
• Spaltenindex zum Spaltennamen finden
int findColumn(String columnName)
• Strategie: Spaltenindex einmalig ermitteln und merken,
Werte danach immer über den Index abrufen
Datenbanken
Prof. Dr.
Stephan Kleuker
279
Attributwerte auslesen (2/2)
• Methode getObject()
– Liest jeden beliebigen SQL-Datentyp
– Liefert Ergebnis als entsprechenden Java-Typ
• Nullwerte
– Spalte kann leere Zellen enthalten (Nullwert, SQL-NULL)
– Bei leerer Zelle liefert getInt() ebenfalls das Ergebnis 0
– Unterscheidung zu echter 0 möglich durch wasNull()
• true, falls zuletzt mit getXXX() gelesene Zelle SQLNULL enthielt
• false sonst
Datenbanken
Prof. Dr.
Stephan Kleuker
280
Beispiel: Ausgabe eines Anfrageergebnisses
ResultSet rs = stmt.executeQuery("SELECT * FROM Continent");
ResultSetMetaData rsmd = rs.getMetaData();
int spalten = rsmd.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= spalten; i++) {
System.out.print(rs.getString(i) + " ");
}
System.out.print("\n");
Europe 9562488
}
Asia 45095292
Australia/Oceania 8503474
Africa 30254708
America 39872000
Datenbanken
Prof. Dr.
Stephan Kleuker
281
ResultSet: positionieren und ändern
• Statement createStatement(int
resultSetType, int resultSetConcurrency)
Parameter (ResultSet-Konstanten) ermöglichen beliebiges
Positionieren (Scrolling) und Ändern der Datensätze
• TYPE_FORWARD_ONLY: sequentieller Durchlauf
• TYPE_SCROLL_INSENSITIVE: positionierbar, Änderungen an
Datenbank werden nicht bemerkt
• TYPE_SCROLL_SENSITIVE: positionierbar, Änderungen an
Datenbank werden bemerkt
•
• CONCUR_READ_ONLY: nur lesen
• CONCUR_UPDATABLE: Änderungen möglich
Datenbanken
Prof. Dr.
Stephan Kleuker
282
ResultSet: positionieren (1/3)
• void beforeFirst()
Positioniert vor den ersten Satz
• boolean first()
Positioniert auf den ersten Satz
• boolean last()
Positioniert auf den letzten Satz
• void afterLast()
Positioniert hinter den letzten Satz
Datenbanken
Prof. Dr.
Stephan Kleuker
283
ResultSet: positionieren (2/3)
• boolean absolute(int pos)
Positioniert ausgehend vom Anfang (pos positiv) oder vom
Ende (pos negativ)
• boolean relative(int rows)
Positioniert relativ zum aktuellen Satz vorwärts (rows
positiv) oder rückwärts (rows negativ)
• boolean next()
Positioniert auf den nächsten Satz
• boolean previous()
Positioniert auf den vorigen Satz
Datenbanken
Prof. Dr.
Stephan Kleuker
284
ResultSet: positionieren (3/3)
• int getRow()
Liefert aktuelle Satznummer
• boolean isBeforeFirst()
Liefert true, falls vor dem ersten Satz
• boolean isFirst()
Liefert true, falls auf dem ersten Satz
• boolean isLast()
Liefert true, falls auf dem letzten Satz
• boolean isAfterLast()
Liefert true, falls hinter dem letzten Satz
Datenbanken
Prof. Dr.
Stephan Kleuker
285
ResultSet: Datensatz ändern
•
Methoden updateXXX() ändern Werte in aktueller Zeile.
rs.absolute(5);
rs.updateString("Name", "Heinz");
rs.updateInt(2, 42);
rs.updateNull(3);
rs.updateRow();
• void updateRow()
Schreibt geänderte Zeile in die Datenbank (*)
• void cancelRowUpdates()
Macht Änderungen rückgängig – nur vor updateRow()
• void deleteRow()
Löscht aktuelle Zeile aus ResultSet
(*) Ob Bearbeitung des ResultSet sich direkt auf die Datenbank
auswirkt, hängt von Autocommit-Einstellung ab [später]
Datenbanken
Prof. Dr.
Stephan Kleuker
286
ResultSet: Datensatz einfügen
•
Einfügezeile: im ResultSet, nicht in der Datenbank
rs.moveToInsertRow();
rs.updateString("Name", "Callaghan");
rs.updateInt(2, 42);
rs.insertRow();
rs.moveToCurrentRow();
• void moveToInsertRow()
Positioniert auf die Einfügezeile
• void insertRow()
Schreibt Einfügezeile in ResultSet und Datenbank (abhängig
von Autocommit-Einstellung)
• void moveToCurrentRow()
Positioniert von der Einfügezeile auf die aktuelle Zeile im
ResultSet
Datenbanken
Prof. Dr.
Stephan Kleuker
287
Daten ändern
• ResultSet executeQuery(String) führt SELECT-
Anweisung aus
• int executeUpdate(String) führt INSERT-, UPDATE-
oder DELETE-Anweisung aus
– Liefert Anzahl betroffener Zeilen (Update count)
– Auch für sonstige Befehle (CREATE ...) geeignet
• boolean execute(String) führt beliebige SQLAnweisung aus
– Liefert true, falls Ergebnis ein ResultSet ist, dann mit
getResultSet() Ergebnis abrufbar
– Liefert false, falls Ergebnis ein Update count ist
Datenbanken
Prof. Dr.
Stephan Kleuker
288
PreparedStatement (1/2)
• PreparedStatement Objekt enthält vorübersetzte SQLBefehle
• geeignet, wenn Statement mehr als einmal ausgeführt
werden muss
• PreparedStatement kann Variablen enthalten, die
jedesmal bei Ausführung definiert werden
• Ansatz: Erzeuge PreparedStatement, identifiziere
Variablen mit ?
PreparedStatement pstmt =
con.prepareStatement ("UPDATE Kunde "
+ "SET Status = ? where Umsatz > ?");
Datenbanken
Prof. Dr.
Stephan Kleuker
289
PreparedStatement (2/2)
1.
Variablen-Werte übergeben
pstmt.setXXX(index,value);
2.
Statement ausführen
pstmt.executeQuery();
pstmt.executeUpdate();
int goldenerKunde=42000;
PreparedStatement pstmt =
con.prepareStatement ("UPDATE Kunde "
+ "SET Status = ? where Umsatz > ?");
pstmt.setString(1, "Gold");
pstmt.setInt(2, goldenerKunde);
pstmt.executeUpdate();
Datenbanken
Prof. Dr.
Stephan Kleuker
290
COMMIT
• Für Connection con ist automatisch ein „Auto-COMMIT“
eingestellt, alle Aktionen direkt auf der Datenbank
• Mit con.setAutoCommit(boolean) einstellbar
• Starteinstellung über con.getAutoCommit() ermitteln (bei
Oracle default: true !)
• con.commit() zum erfolgreichen Abschließen
• con.rollback() zum Verwerfen der Änderungen seit dem
letzten Commit
Datenbanken
Prof. Dr.
Stephan Kleuker
291
Verbindungsaufbau (Ausschnitt)
import oracle.jdbc.*; // besser ausschreiben
import java.sql.*;
...
try {
// Oracle JDBC-Treiber laden
DriverManager.
registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.
getConnection("jdbc:oracle:thin:@oracle-srv.edvsz"
+".hs-osnabrueck.de:1521:Ora11",
"kleuker", "passwort");
// Oracle DatabaseMetaData fuer Meta-Informationen
DatabaseMetaData meta = con.getMetaData();
// Beispielmetainformation: Treiberversion
System.out.println("JDBC driver version is "+
meta.getDriverVersion()+"\n");
befehl=con.createStatement(); //private Statement befehl
}
catch (SQLException e) {...
}
Datenbanken
Prof. Dr.
Stephan Kleuker
292
Einfache Verbindungsnutzung (Ausschnitt)
ResultSet result;
ResultSetMetaData metaresult;
try {
result=befehl.executeQuery("SELECT * FROM City");
metaresult=result.getMetaData();
int spalten = metaresult.getColumnCount();
while(result.next()){
for(int i=1; i<=spalten;i++)
ausgabe.append(result.getString(i)+" ");
ausgabe.append("\n");
}
ausgabe.append("Bearbeitung abgeschlossen\n");
result.close();
}
catch (SQLException es) {
while (es!=null){ //evtl. mehr als eine Exception
fehler.append("Fehlercode: "+es.getErrorCode()+"\n");
fehler.append("SQL State: "+es.getSQLState()+"\n");
fehler.append(es+"\n");
es= es.getNextException();
}
}
Datenbanken
Prof. Dr.
Stephan Kleuker
293
Einbindung von Oracle-JDBC in Eclipse
Rechtsklick auf Projekt,
dann Properies wählen,
„Java Build Path“, dann
Reiter Libraries, dann
„Add External JARs...“
Quellen: Oracle Web-Seiten
C:\sqldeveloper\jdbc\lib\ojdbc6.jar
Dann passenden Treiber suchen,
z. B. ojdbc6.jar in sqldeveloper\jdbc\lib
Datenbanken
Prof. Dr.
Stephan Kleuker
294
Einbindung von Oracle-JDBC in NetBeans
• Rechtsklick im Projekt auf
Libraries -> Add JAR/Folder
• manövrieren zum Verzeichnis
mit JDBC-Treiber (sinnvoll
Unterordner lib des Projekts)
• markieren und „Öffnen“
Datenbanken
Prof. Dr.
Stephan Kleuker
295
Erinnerung: PL/SQL-Funktion
CREATE OR REPLACE
FUNCTION anzahlNasen(namePar Angestellte.Name%TYPE)
RETURN INTEGER
/* oder NUMBER */
IS
ergebnis INTEGER;
BEGIN
IF namePar = 'Meier'
THEN
RAISE_APPLICATION_ERROR(-20300,
'Meiers nicht zählbar');
END IF;
SELECT COUNT(*)
INTO ergebnis
FROM Angestellte
WHERE Angestellte.Name=namePar;
RETURN ergebnis;
END;
Datenbanken
Prof. Dr.
Stephan Kleuker
296
PL/SQL in Java
• Grundsätzlich besteht bei der Ausführung große Ähnlichkeit
zu PreparedStatements
• benötigt wird Connection-Objekt con
CallableStatement stmt=con.prepareCall(
"{? = call anzahlNasen(?)}");
• man beachte geschweifte Klammern, bei Prozeduraufruf wird
„ ? = “ am Anfang weggelassen
• in Oracle auch möglich:
CallableStatement stmt=con.prepareCall(
"BEGIN ? := anzahlNasen(?); END;");
• Ausgabeparameter müssen registriert werden
stmt.registerOutParameter(1,Types.INTEGER);
Datenbanken
Prof. Dr.
Stephan Kleuker
297
Beispiel
public int nasenZaehlen(String nachname){
int ergebnis = 0;
try {
CallableStatement stmt=con.prepareCall(
"{? = call anzahlNasen(?)}");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.setString(2, nachname);
stmt.execute();
ergebnis=stmt.getInt(1);
} catch (SQLException e) {
if(e.getErrorCode() == 20300) // von RAISE_APPLICATION_ERROR
ergebnis = -1;
else{
ausnahmeAusgeben(e);
ergebnis = Integer.MIN_VALUE;
}
}
return ergebnis;
}
Datenbanken
Prof. Dr.
Stephan Kleuker
298
Nutzung von Cursor als Ergebnis (1/3)
import
import
import
import
import
import
java.sql.CallableStatement;
java.sql.Connection;
java.sql.DriverManager;
java.sql.ResultSet;
java.util.Scanner;
oracle.jdbc.OracleTypes;
/* in Oracle fuer das MondialBeispiel
CREATE OR REPLACE PROCEDURE STAEDTEIN(
Land IN VARCHAR,
stadtcursor IN OUT SYS_REFCURSOR) AS
BEGIN
OPEN stadtcursor for
SELECT *
FROM City
WHERE City.Country=Land;
END STAEDTEIN;
*/
/* Hinweis: Cursor nur lesbar, nicht schreibbar! */
Datenbanken
Prof. Dr.
Stephan Kleuker
299
Nutzung von Cursor als Ergebnis (2/3)
public class PLSQLCursorNutzungInJava { //
public static void main(String[] s){
String nutzer = "kleuker";
String passwort = "kleuker";
try {
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
//Verbindung aufbauen in der HS zur DB
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@oracle-srv.edvsz.hs-osna"
+ "brueck.de:1521:Ora11", nutzer, passwort);
//Vorbereitung der PL/SQL-Nutzung
ResultSet rset = null;
CallableStatement cstmt = conn.
prepareCall("{call staedtein(?,?)}");
cstmt.registerOutParameter(2,
OracleTypes.CURSOR);
Datenbanken
Prof. Dr.
Stephan Kleuker
300
Nutzung von Cursor als Ergebnis (3/3)
//Testprogramm
String eingabe = "";
while(!eingabe.equalsIgnoreCase("Ende")){
System.out.print(
"Laenderkuerzel (Ende mit \"Ende\"): ");
eingabe=(new Scanner(System.in)).next();
cstmt.setString(1,eingabe);
cstmt.execute();
rset = (ResultSet) cstmt.getObject(2);
while(rset.next())
System.out.println("Stadt: "
+rset.getString("name")
+" Einwohner:"
+rset.getInt("population"));
}
conn.close();
}
catch(Exception e){ // ausarbeiten, wie immer
}
}
}
Datenbanken
Prof. Dr.
Stephan Kleuker
301
Beispieldialog
Laenderkuerzel (Ende mit "Ende"): GR
Stadt: Piraeus Einwohner:196389
Stadt: Patrai Einwohner:142163
Stadt: Larisa Einwohner:102426
Stadt: Iraklion Einwohner:102398
Stadt: Volos Einwohner:71378
Stadt: Kavalla Einwohner:56705
Stadt: Athens Einwohner:885737
Stadt: Thessaloniki Einwohner:406413
Laenderkuerzel (Ende mit "Ende"): LAR
Stadt: Tripoli Einwohner:0
Stadt: Bengasi Einwohner:0
Laenderkuerzel (Ende mit "Ende"): AND
Stadt: Andorra la Vella Einwohner:15600
Laenderkuerzel (Ende mit "Ende"): enDE
Datenbanken
Prof. Dr.
Stephan Kleuker
302