Datenbankzugriff mit JDBC

Praktikum aus
Softwareentwicklung 2
Datenbankzugriff
mit JDBC
Praktikum aus Softwareentwicklung 2
Java Praktikum – SS 2009 – [email protected]
1
Praktikum aus
Softwareentwicklung 2
Datenbankzugriff mit JDBC
•
•
HSQLDB
–
Installation und Anwendung
JDBC
–
Grundlagen, DriverManager, Connection, …
Java Praktikum – SS 2009 – [email protected]
2
Praktikum aus
Softwareentwicklung 2
HSQLDB
Einleitung & Installation
•
•
•
•
•
Open Source RDBMS (SQL 93, 99, 2003)
100% Java
Typ 4 JDBC-Treiber
Verfügbar unter http://hsqldb.sourceforge.net/
Installation
–
–
–
•
Entpacken des ZIP-Files
hsqldb.jar zum CLASSPATH
Default Port: 9001
Starten des Standalone-Servers
c:\ java –cp …/hsqldb.jar org.hsqldb.Server
Java Praktikum – SS 2009 – [email protected]
3
Praktikum aus
Softwareentwicklung 2
HSQLDB
Verbindung herstellen
•
JDBC-Treiber laden:
try {
Class.forName("org.hsqldb.jdbcDriver");
} catch (Exception e) {
…
}
•
Verbindung herstellen:
Connection c =
java.sql.DriverManager.getConnection (
"jdbc:hsqldb:hsql://localhost:9001/xdb",
"sa", // user
""); // password
Java Praktikum – SS 2009 – [email protected]
4
Praktikum aus
Softwareentwicklung 2
HSQLDB
Tools
•
•
•
•
•
•
org.hsqldb.util.DatabaseManager
org.hsqldb.util.DatabaseManagerSwing
org.hsqldb.util.Transfer
org.hsqldb.util.QueryTool
org.hsqldb.util.ScriptTool
Details siehe Doku
Java Praktikum – SS 2009 – [email protected]
5
Praktikum aus
Softwareentwicklung 2
HSQLDB
Tabellen Typen
•
•
•
MEMORY
–
CREATE TABLE
–
Daten immer vollständig im Speicher
CACHED
–
CREATE CACHED TABLE
–
Für große Datenmengen
TEXT
–
–
–
•
Tabelle als CSV File, Default Trennzeichen ','
CREATE TEXT TABLE
SET … SOURCE …
TEMP
–
Daten werden beim Beenden gelöscht
Java Praktikum – SS 2009 – [email protected]
6
Praktikum aus
Softwareentwicklung 2
HSQLDB
Constraints & Indizes
•
•
Primary Key
–
Spalte, die Datenzeile eindeutig identifiziert
–
Index wird automatisch erzeugt
IDENTITY
–
•
•
Typ INTEGER,
wird automatisch erhöht beim Einfügen
Unique
–
Spalte mit eindeutigem Wert pro Datenzeile
–
Index wird automatisch erzeugt
–
Der Wert NULL darf mehrfach vorkommen
Foreign Keys
–
Eine oder mehrere Spalten, die Datenzeile einer anderen Tabelle
identifizieren
Java Praktikum – SS 2009 – [email protected]
7
Praktikum aus
Softwareentwicklung 2
Datenbankzugriff mit JDBC
•
•
HSQLDB
–
Installation und Anwendung
JDBC
–
Grundlagen, DriverManager, Connection, …
Java Praktikum – SS 2009 – [email protected]
8
Praktikum aus
Softwareentwicklung 2
JDBC
Einleitung (1/2)
•
•
Call Level Interface zu relationalen Datenbanken
Übermittlung von SQL Statements
–
–
•
Package java.sql definiert die Standard-Schnittstelle
–
–
•
•
Datendefinition, Manipulation, Abfrage
Result Sets zur Verarbeitung von Abfrageergebnissen
JDBC 3.0 … inkludiert in J2SDK 1.4 und J2EE 1.4
javax.sql neue Features seit JDBC 2.0 Optional Package
Unterstütz die wichtigsten Features von SQL99
Herstellerunabhängiger Zugang zu den gemeinsamen
Features
Java Praktikum – SS 2009 – [email protected]
9
Praktikum aus
Softwareentwicklung 2
JDBC
Einleitung (2/2)
•
Implementierung durch datenbankspezifische Treiber
–
http://java.sun.com/products/jdbc/overview.html
•
Treiber werden vom DriverManager verwaltet
•
Kompatibilität mit unterschiedlichen Datenbanken?
–
Ja, aber...
–
Nicht alle Treiber implementieren JDBC vollständig
–
Verwendung proprietärer Treiber-Erweiterungen
–
Verwendung proprietärer SQL Konstrukte
Java Praktikum – SS 2009 – [email protected]
10
Praktikum aus
Softwareentwicklung 2
JDBC
Typ 1: JDBC/ODBC Bridge
Java Praktikum – SS 2009 – [email protected]
11
Praktikum aus
Softwareentwicklung 2
JDBC
Typ 2: Plattformeigene JDBC-Treiber
Java Praktikum – SS 2009 – [email protected]
12
Praktikum aus
Softwareentwicklung 2
JDBC
Typ 3: Universelle JDBC-Treiber
Java Praktikum – SS 2009 – [email protected]
13
Praktikum aus
Softwareentwicklung 2
JDBC
Typ 4: Native JDBC-Treiber
Java Praktikum – SS 2009 – [email protected]
14
Praktikum aus
Softwareentwicklung 2
JDBC
Treibertypen




Typ 1: JDBC Api ist ein Mapping zu einem anderen Api, wie zum
Beispiel ODBC
–
JDBC-ODBC Bridge wird von Sun mitgeliefert
–
erfordert client-seitige Installation des ODBC Treibers (native Code)
Typ 2: Native-API partly-Java drivers, native Library und Java Code
–
Erfordert client-seitige Installation eines DB-spezifischen Treibers
–
z.B. Oracle OCI Driver (basiert auf SQL*Net)
Typ 3: Pure Java Treiber der mit einem Middleware Server
kommuniziert
–
Middleware-Server vermittelt auf verschiedene DBMS
–
z.B. DataDirect (Merant)
Type 4: Native-protocol all-Java drivers
–
baut DB-Client/Server-Protokoll via Java TCP/IP Sockets (Package
java.net) nach
–
z.B. Oracle Thin Driver (simuliert SQL*Net)
Reines Java
Java Praktikum – SS 2009 – [email protected]
15
Praktikum aus
Softwareentwicklung 2
JDBC
Essentielle Klassen und Interfaces
•
JDBC-Treiber Laden und Registrieren
–
•
Datenbankverbindung
–
•
java.sql.Connection
Ausführen von SQL Anweisungen
–
•
java.sql.DriverManager, javax.sql.DataSource
java.sql.Statement, java.sql.PreparedStatement
Zugriff auf Ergebnisse einer SQL Abfrage
–
java.sql.ResultSet
Java Praktikum – SS 2009 – [email protected]
16
Praktikum aus
Softwareentwicklung 2
JDBC
Verbindung zur DB herstellen
•
Laden eines DB Treibers
–
•
java.sql.DriverManager.registerDriver(new OracleDriver());
Verbindung herstellen
–
Connection con =
DriverManager.getConnection(datenbankURL [,username,password]);
–
Datenbank-URL jdbc:subprotocol:details
•
Oracle Thin Treiber:
"jdbc:oracle:thin:@hostname:1521:serverid"
•
HSQLDB: hsql://hostname:9001/xdb
•
MySQL: "jdbc:mysql://localhost:3306/test"
Java Praktikum – SS 2009 – [email protected]
17
Praktikum aus
Softwareentwicklung 2
JDBC
Bsp: LVA-Anmeldungen
LVA
Student
matrNr:int
vorname:String
nachname:String
•
m
ist angemeldet ->
CREATE TABLE Student (
matrNr IDENTITY PRIMARY KEY,
vorname VARCHAR(30),
nachname VARCHAR(30))
•
n lvaNr:int
name:String
leiter:String
beschreibung:URL
CREATE TABLE LVA (
lvaNr IDENTITY PRIMARY KEY,
name VARCHAR(80),
leiter VARCHAR(30))
CREATE TABLE Anmeldung (
matrNr INTEGER,
lvaNr INTEGER,
FOREIGN KEY (matrNr) REFERENCES Student,
FOREIGN KEY (lvaNr) REFERENCES LVA)
Java Praktikum – SS 2009 – [email protected]
18
Praktikum aus
Softwareentwicklung 2
JDBC
Kapselung der DB-Zugriffe (1/2)
•
DAO Pattern
http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html
Java Praktikum – SS 2009 – [email protected]
19
Praktikum aus
Softwareentwicklung 2
JDBC
Kapselung der DB-Zugriffe (2/2)
•
Data Access Objects (DAO)
Kapseln und Abstraktion von Zugriffen auf
Datenquellen (Files, DBMS, ODBMS…)
DAO Umgebung
–
Transfer Object (Bean)
• Keine Logik, nur die Daten
–
Data Source (kapselt Datenquelle)
–
Business Object (Anwendungslogik)
–
•
Java Praktikum – SS 2009 – [email protected]
20
Praktikum aus
Softwareentwicklung 2
JDBC
SELECT Statement
•
SQL-Query: Alle Anmeldungen zum PK SWE2, sortiert nach 'nachname'
String select =
"SELECT s.nachname, s.vorname, s.matrNr " +
" FROM Student s " +
" JOIN Anmeldung a ON a.matrNr=s.matrNr " +
" WHERE a.lvaNr = ? " +
" ORDERED BY s.nachname";
•
Statement erzeugen, Werte setzen:
–
PreparedStatement stmt =
con.prepareStatement(select);
–
stmt.setString(1, "365.009");
Query am DB Server ausführen:
–
ResultSet rs = stmt.executeQuery();
–
Pro Statement nur ein ResultSet gleichzeitig
•
Java Praktikum – SS 2009 – [email protected]
21
Praktikum aus
Softwareentwicklung 2
JDBC
ResultSet
•
ResultSet: zeilenweiser Iterator über Query Ergebnis
•
Zugriff auf Felder der aktuellen Zeile:
public String getXXX(columnIndex); // Index beginnt bei 1
public String getXXX("columnName");
–
–
–
•
XXX … String, Date, Int, Boolean, Object, ...
SQL-Typen werden automatisch konvertiert.
NULL-Werte liefert null für Objekttypen, aber die Zahl 0 bzw.
false für primitive Datentypen; wasNull() prüft, ob das gelesene
Feld NULL war.
Bsp: alle Zeilen des ResultSet lesen:
while(rs.next()) {
String nachname = rs.getString(1);
String vorname = rs.getString(2);
int matrNr = rs.getInt(3);
}
rs.close();
Java Praktikum – SS 2009 – [email protected]
22
Praktikum aus
Softwareentwicklung 2
JDBC
Prepared Statements
•
•
•
Werden von DBMS wiederverwendet
Setter für Datentypen
JDBC Treiber konvertiert in native DB Typen und
deren Formatierung (Dezimalzahlen, Datum…)
String insStr = "INSERT INTO STUDENT VALUES(?, ?, ?)";
PreparedStatement updateStmt;
updateStmt = con.prepareStatement(insStr);
…
updateStmt.setInt(1, 9455123);
updateStmt.setString(2, "Helge");
//für NULL-Werte
updateStmt.setNull(3, java.sql.Types.VARCHAR);
int i = updateStmt.executeUpdate();
…
Java Praktikum – SS 2009 – [email protected]
23
Praktikum aus
Softwareentwicklung 2
JDBC
INSERT, UPDATE, DELETE
•
Neuen Datensatz Einfügen - INSERT
•
Datensatz Ändern - UPDATE
•
Datensatz Löschen - DELETE
•
Ausführung mit
int executeUpdate(String statement)
–
Rückgabewert ist die Anzahl der betroffenen Tupel
Java Praktikum – SS 2009 – [email protected]
24
Praktikum aus
Softwareentwicklung 2
JDBC
Skriptsprachen
•
PL/SQL (Oracle), TSQL (MSSQL)...
•
•
Art des Ergebnisses nicht im Vorhinein bekannt
–
–
•
HSQLDB: SqlTool Procedural Language
Aufruf von DB-Funktionen oder Prozeduren
Benutzereingaben
Ausführung mit
boolean execute(String sqlOrProgram)
–
true, falls das Ergebnis ein ResultSet ist
ResultSet getResultSet();
–
sonst: int getUpdateCount() liefert die Anzahl der
verarbeiteten Rows, oder -1
Java Praktikum – SS 2009 – [email protected]
25
Praktikum aus
Softwareentwicklung 2
JDBC
Transaktionssteuerung (1/3)
•
Transaktion: Zusammenfassung von mehreren DB
Änderungen
–
•
Isolation von parallelen Änderungen
–
•
UPDATE, INSERT, DELETE
SELECT
Steuerung in Business Logik implementiert, also außerhalb
der DAO Schnittstellen
Zeit
T1
Änderung-1
T2
Änderung-n
COMMIT
Änderung-1
Änderung-n
Java Praktikum – SS 2009 – [email protected]
ROLLBACK
26
Praktikum aus
Softwareentwicklung 2
JDBC
Transaktionssteuerung (2/3)
•
•
•
Methoden commit() und rollback() des Interfaces
java.sql.Connection;
Weitere Methoden: getAutoCommit(),setAutoCommit(boolean b)
•
Auto-commit:
nach jeder Anweisung erfolgt commit (default);
Transaction Isolation Levels
int mode = con.getTransactionIsolation()
•
•
•
•
read uncommitted: gelesene Daten können nicht committed sein
(dirty read)
read committed: gelesene Daten sind committed
repeatable reads: Änderungen von Rows bleiben innerhalb
Transaktion unsichtbar, neue Rows werden sichtbar (phantom reads)
serializable: DBMS führt keine Transaktionen parallel aus
Java Praktikum – SS 2009 – [email protected]
27
Praktikum aus
Softwareentwicklung 2
JDBC
Transaktionssteuerung (3/3)
•
Optimistic Locking
–
Mehrere Benutzer ändern gleichzeitig bestimmten Datensazt
–
Erkennung durch spezielle Spalte modify_counter oder version
•
Transaktion:
SELECT … // modify_counter = 1
…
UPDATE SET modify_counter = 2 …
WHERE modify_counter = 1
…
COMMIT // im Fehlerfall ROLLBACK
•
Falls UPDATE fehlschlägt
–
Concurrent Update Exception
–
Transaktion abbrechen (rollback) und wiederholen
Java Praktikum – SS 2009 – [email protected]
28
Praktikum aus
Softwareentwicklung 2
JDBC 2.0
Batch-Updates (1/2)
•
•
Batch-Updates: mehrere Updates werden am
DB-Server in einem Durchgang abgarbeitet
Normales Update mit: executeUpdate()
●
•
100 Aufrufe = 100 Client-Server Round Trips
Ab JDBC 2.0: executeBatch()
●
100 Aufrufe = 1 Client-Server Round Trip
Java Praktikum – SS 2009 – [email protected]
29
Praktikum aus
Softwareentwicklung 2
JDBC 2.0
Batch-Updates (2/2)
•
Beispiel:
Statement stmt = con.createStatement();
stmt.addBatch(
"INSERT INTO COFFEES VALUES('Amaretto', 49, 9.99)");
stmt.addBatch(
"INSERT INTO COFFEES VALUES('Hazelnut', 49, 9.99)");
...
int [] updateCounts = stmt.executeBatch();
•
Batch-Update Exceptions:
…
} catch(BatchUpdateException b) { …
for (int updateCount : b.getUpdateCounts()) {
System.err.printf("statement %d: %d%n", i, updateCount);
}
}
Java Praktikum – SS 2009 – [email protected]
30
Praktikum aus
Softwareentwicklung 2
JDBC
Meta-Daten (1/2)
•
ResultSetMetaData, Daten über das Ergebnis einer
Abfrage:
ResultSetMetaData metaData = rs.getMetaData();
columnCount = metaData.getColumnCount();
String cn = metaData.getColumnName(i);
int ct = metaData.getColumnType(i);
//see java.sql.Types
int ds = metaData.getColumnDisplaySize(i);
...
Java Praktikum – SS 2009 – [email protected]
31
Praktikum aus
Softwareentwicklung 2
JDBC
Meta-Daten (2/2)
•
DatabaseMetaData, umfangreiche Methoden, um
Details wie Schema, Zugriffsrechte, usw. eines
DBMS abfragen zu können:
DatabaseMetaData dbmd = con.getMetaData();
ResultSet rset = dbmd.getCatalogs();
int mv = dbmd.getDriverMajorVersion();
...
Java Praktikum – SS 2009 – [email protected]
32
Praktikum aus
Softwareentwicklung 2
JDBC
Exception Handling
•
•
•
•
Klasse SQLException
–
kann von den meisten JDBC-Methoden geworfen werden
–
getMessage(), getSQLState(), getErrorCode() etc.
–
SQLException getNextException()
Klasse SQLWarning (spez. DataTruncation)
–
Connection, Statement, ResultSet verwalten eine Liste
von Warnings
–
getWarnings() und getNextWarning() bzw.
clearWarnings()
Kombination mit Transaktionssteuerung
–
rollback im catch-Block einer Transaktion; ggf.
Wiederholung
Statement und/oder Connection MÜSSEN im
finally geschlossen werden!
Java Praktikum – SS 2009 – [email protected]
33
Praktikum aus
Softwareentwicklung 2
JDBC
Abbildung von Datentypen
Java Praktikum – SS 2009 – [email protected]
34
Praktikum aus
Softwareentwicklung 2
JDBC 2.0
ResultSet Erweiterungen
•
Scrollbare ResultSets
–
–
•
Cursor in einem ResultSet kann nach vor und
zurück bewegt werden
Funktionalität muss extra eingeschaltet werden
Änderung der aktuellen Row eines
ResultSets
–
UPDATE, INSERT, DELETE
Java Praktikum – SS 2009 – [email protected]
35
Praktikum aus
Softwareentwicklung 2
JDBC 2.0
SQL3 Datentypen
•
•
SQL3 Datentypen:
–
Blob (Binary Large Object)
–
Clob (Character Large Object)
–
Array
–
Struct
–
Ref
getBlob(), setBlob(), updateBlob()...
Java Praktikum – SS 2009 – [email protected]
36
Praktikum aus
Softwareentwicklung 2
JDBC 3.0
Neuerungen in JDBC 3.0 (1/3)
•
•
•
Änderungen in der Metadata API
Änderungen an den Datentypen (z.B. neuer
Datentyp java.sql.Types.BOOLEAN)
Auswerten von automatisch generierten Keys
Statement stmt = conn.createStatement();
// Obtain the generated key that results from the query.
stmt.executeUpdate("INSERT INTO authors " +
"(first_name, last_name) " +
"VALUES ('George', 'Orwell')",
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if ( rs.next() ) {
// Retrieve the auto generated key(s).
int key = rs.getInt(1);
}
Java Praktikum – SS 2009 – [email protected]
37
Praktikum aus
Softwareentwicklung 2
JDBC 3.0
Neuerungen in JDBC 3.0 (2/3)
•
•
•
Connection pooling
Prepared Statement pooling
Savepoints in Transaktionen
Java Praktikum – SS 2009 – [email protected]
38
Praktikum aus
Softwareentwicklung 2
JDBC 3.0
Neuerungen in JDBC 3.0 (3/3)
•
Beispiel:
conn.setAutoCommit(false);
// Set a conservative transaction isolation level.
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE)
;
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate("INSERT INTO authors " +
"(first_name, last_name) VALUES " +
"('Lewis', 'Carroll')");
// Set a named savepoint.
Savepoint svpt = conn.setSavepoint("NewAuthor");
// ...
rows = stmt.executeUpdate("UPDATE authors set type = 'fiction' "
+
"WHERE last_name = 'Carroll'");
// ...
conn.rollback(svpt);
// ...
// The author has been added, but not updated.
conn.commit();
Java Praktikum – SS 2009 – [email protected]
39
Praktikum aus
Softwareentwicklung 2
Ende der 4. Übung
Java Praktikum – SS 2009 – [email protected]
40