11. JDBC • Grundsätzliche Nutzung von JDBC • Verbindungsaufbau

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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
253
Überblick: Datenbankanfragen mit JDBC
Datenbankverbindung
herstellen
Datenbankanfrage
Ergebnisse
verarbeiten
Verbindung zur DB
schließen
Datenbanksysteme
class DriverManager
Connection con=
DriverManager.getConnection(...);
Statement stmt=
con.createStatement();
ResultSet rs =
stmt.executeQuery(...);
rs.next();
int n = rs.getInt("KNr");
con.close();
Prof. Dr. Stephan Kleuker
254
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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
255
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:@srv20.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");
Datenbanksysteme
Prof. Dr. Stephan Kleuker
256
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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
257
Statement
• 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
...");
Datenbanksysteme
Prof. Dr. Stephan Kleuker
258
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
• 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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
259
Attributwerte auslesen (1/2)
• Spaltenwerte (Attribute) einer Zeile mit getXXX()Methoden lesen
• Treiber konvertiert Daten, falls möglich
• 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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
260
Attributwerte auslesen (2/2)
• Methode getObject()
– Liest jeden beliebigen SQL-Datentyp
– Liefert Ergebnis als entsprechenden Java-Typ
• Nullwerte
– Spalte kann leere Zellen enthalten (Nullwert, SQLNULL)
– Bei leerer Zelle liefert getInt() ebenfalls das
Ergebnis 0.
– Unterscheidung zu echter 0 möglich durch
wasNull()
• true, falls zuletzt mit getXXX() gelesene Zelle
SQL-NULL enthielt
• false sonst
Datenbanksysteme
Prof. Dr. Stephan Kleuker
261
Daten ändern
• ResultSet executeQuery(String) führt SELECTAnweisung aus
• int Statement.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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
262
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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
263
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.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
264
ResultSet: positionieren (2/3)
• boolean absolute(int pos)
Positioniert relativ zum Anfang (pos positiv) oder
relativ zum 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.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
265
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.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
266
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]
Datenbanksysteme
Prof. Dr. Stephan Kleuker
267
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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
268
PreparedStatement (1/2)
• PreparedStatement Objekt enthält vorübersetzte
SQL-Befehle
• 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 > ?");
Datenbanksysteme
Prof. Dr. Stephan Kleuker
269
PreparedStatement (2/2)
1.
2.
Variablen-Werte übergeben
pstmt.setXXX(index,value);
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();
Datenbanksysteme
Prof. Dr. Stephan Kleuker
270
COMMIT
• Für Connection con ist automatisch ein „AutoCOMMIT“ 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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
271
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:@srv20.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) {...
} Datenbanksysteme
Prof. Dr. Stephan Kleuker
272
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();
}
}
Datenbanksysteme
Prof. Dr. Stephan Kleuker
273
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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
274
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;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
275
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);
Datenbanksysteme
Prof. Dr. Stephan Kleuker
276
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;
}
Datenbanksysteme
Prof. Dr. Stephan Kleuker
277
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! */
Datenbanksysteme
Prof. Dr. Stephan Kleuker
278
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:@srv20.edvsz.hs-osnabrueck"
+".de:1521:Ora11", nutzer, passwort);
//Vorbereitung der PL/SQL-Nutzung
ResultSet rset = null;
CallableStatement cstmt = conn.
prepareCall("{call staedtein(?,?)}");
cstmt.registerOutParameter(2,
OracleTypes.CURSOR);
Datenbanksysteme
Prof. Dr. Stephan Kleuker
279
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
}
}
Datenbanksysteme
}
Prof. Dr. Stephan Kleuker
280
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
Datenbanksysteme
Prof. Dr. Stephan Kleuker
281