Praktikum aus Softwareentwicklung 2
1Java Praktikum – SS 2009 – [email protected]
Datenbankzugriffmit JDBC
Praktikum aus Softwareentwicklung 2
Praktikum aus Softwareentwicklung 2
2Java Praktikum – SS 2009 – [email protected]
Datenbankzugriff mit JDBC
• HSQLDB– Installation und Anwendung
• JDBC– Grundlagen, DriverManager, Connection, …
Praktikum aus Softwareentwicklung 2
3Java Praktikum – SS 2009 – [email protected]
HSQLDBEinleitung & 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-Serversc:\ java –cp …/hsqldb.jar org.hsqldb.Server
Praktikum aus Softwareentwicklung 2
4Java Praktikum – SS 2009 – [email protected]
HSQLDBVerbindung 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
Praktikum aus Softwareentwicklung 2
5Java Praktikum – SS 2009 – [email protected]
HSQLDBTools
• org.hsqldb.util.DatabaseManager• org.hsqldb.util.DatabaseManagerSwing• org.hsqldb.util.Transfer• org.hsqldb.util.QueryTool• org.hsqldb.util.ScriptTool
• Details siehe Doku
Praktikum aus Softwareentwicklung 2
6Java Praktikum – SS 2009 – [email protected]
• 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
HSQLDBTabellen Typen
Praktikum aus Softwareentwicklung 2
7Java Praktikum – SS 2009 – [email protected]
• 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
HSQLDBConstraints & Indizes
Praktikum aus Softwareentwicklung 2
8Java Praktikum – SS 2009 – [email protected]
Datenbankzugriff mit JDBC
• HSQLDB– Installation und Anwendung
• JDBC– Grundlagen, DriverManager, Connection, …
Praktikum aus Softwareentwicklung 2
9Java Praktikum – SS 2009 – [email protected]
JDBCEinleitung (1/2)
• Call Level Interface zu relationalen Datenbanken• Übermittlung von SQL Statements
– Datendefinition, Manipulation, Abfrage– Result Sets zur Verarbeitung von Abfrageergebnissen
• Package java.sql definiert die Standard-Schnittstelle– 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
Praktikum aus Softwareentwicklung 2
10Java Praktikum – SS 2009 – [email protected]
JDBCEinleitung (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
Praktikum aus Softwareentwicklung 2
11Java Praktikum – SS 2009 – [email protected]
JDBCTyp 1: JDBC/ODBC Bridge
Praktikum aus Softwareentwicklung 2
12Java Praktikum – SS 2009 – [email protected]
JDBCTyp 2: Plattformeigene JDBC-Treiber
Praktikum aus Softwareentwicklung 2
13Java Praktikum – SS 2009 – [email protected]
JDBCTyp 3: Universelle JDBC-Treiber
Praktikum aus Softwareentwicklung 2
14Java Praktikum – SS 2009 – [email protected]
JDBCTyp 4: Native JDBC-Treiber
Praktikum aus Softwareentwicklung 2
15Java Praktikum – SS 2009 – [email protected]
JDBCTreibertypen
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
Praktikum aus Softwareentwicklung 2
16Java Praktikum – SS 2009 – [email protected]
JDBCEssentielle Klassen und Interfaces
• JDBC-Treiber Laden und Registrieren– java.sql.DriverManager, javax.sql.DataSource
• Datenbankverbindung– java.sql.Connection
• Ausführen von SQL Anweisungen– java.sql.Statement, java.sql.PreparedStatement
• Zugriff auf Ergebnisse einer SQL Abfrage– java.sql.ResultSet
Praktikum aus Softwareentwicklung 2
17Java Praktikum – SS 2009 – [email protected]
JDBCVerbindung 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"
Praktikum aus Softwareentwicklung 2
18Java Praktikum – SS 2009 – [email protected]
JDBCBsp: LVA-Anmeldungen
• CREATE TABLE Student (
matrNr IDENTITY PRIMARY KEY,
vorname VARCHAR(30),
nachname VARCHAR(30))
Student
matrNr:int
vorname:String
nachname:String
LVA
lvaNr:int
name:String
leiter:String
beschreibung:URL
m nist angemeldet ->
• 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)
Praktikum aus Softwareentwicklung 2
19Java Praktikum – SS 2009 – [email protected]
• DAO Pattern http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html
JDBCKapselung der DB-Zugriffe (1/2)
Praktikum aus Softwareentwicklung 2
20Java Praktikum – SS 2009 – [email protected]
• 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)
JDBCKapselung der DB-Zugriffe (2/2)
Praktikum aus Softwareentwicklung 2
21Java Praktikum – SS 2009 – [email protected]
JDBCSELECT 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
Praktikum aus Softwareentwicklung 2
22Java Praktikum – SS 2009 – [email protected]
JDBCResultSet
• 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();
Praktikum aus Softwareentwicklung 2
23Java Praktikum – SS 2009 – [email protected]
JDBCPrepared 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();
…
Praktikum aus Softwareentwicklung 2
24Java Praktikum – SS 2009 – [email protected]
JDBCINSERT, 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
Praktikum aus Softwareentwicklung 2
25Java Praktikum – SS 2009 – [email protected]
JDBCSkriptsprachen
• PL/SQL (Oracle), TSQL (MSSQL)...• HSQLDB: SqlTool Procedural Language
• Art des Ergebnisses nicht im Vorhinein bekannt– Aufruf von DB-Funktionen oder Prozeduren – Benutzereingaben
• Ausführung mit boolean execute(String sqlOrProgram)
– true, falls das Ergebnis ein ResultSet istResultSet getResultSet();
– sonst: int getUpdateCount() liefert die Anzahl der verarbeiteten Rows, oder -1
Praktikum aus Softwareentwicklung 2
26Java Praktikum – SS 2009 – [email protected]
• Transaktion: Zusammenfassung von mehreren DB Änderungen
– UPDATE, INSERT, DELETE • Isolation von parallelen Änderungen
– SELECT• Steuerung in Business Logik implementiert, also außerhalb
der DAO Schnittstellen
JDBCTransaktionssteuerung (1/3)
Zeit
T 1
Änderung-1 Änderung-n COMMIT
T 2
Änderung-1 Änderung-n ROLLBACK
Praktikum aus Softwareentwicklung 2
27Java Praktikum – SS 2009 – [email protected]
JDBCTransaktionssteuerung (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
Praktikum aus Softwareentwicklung 2
28Java Praktikum – SS 2009 – [email protected]
• 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
JDBCTransaktionssteuerung (3/3)
Praktikum aus Softwareentwicklung 2
29Java Praktikum – SS 2009 – [email protected]
JDBC 2.0Batch-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
Praktikum aus Softwareentwicklung 2
30Java Praktikum – SS 2009 – [email protected]
JDBC 2.0Batch-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);
}
}
Praktikum aus Softwareentwicklung 2
31Java Praktikum – SS 2009 – [email protected]
JDBCMeta-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);
...
Praktikum aus Softwareentwicklung 2
32Java Praktikum – SS 2009 – [email protected]
JDBCMeta-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();
...
Praktikum aus Softwareentwicklung 2
33Java Praktikum – SS 2009 – [email protected]
JDBCException 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!
Praktikum aus Softwareentwicklung 2
34Java Praktikum – SS 2009 – [email protected]
JDBCAbbildung von Datentypen
Praktikum aus Softwareentwicklung 2
35Java Praktikum – SS 2009 – [email protected]
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
Praktikum aus Softwareentwicklung 2
36Java Praktikum – SS 2009 – [email protected]
JDBC 2.0SQL3 Datentypen
• SQL3 Datentypen:– Blob (Binary Large Object)– Clob (Character Large Object)– Array– Struct– Ref
• getBlob(), setBlob(), updateBlob()...
Praktikum aus Softwareentwicklung 2
37Java Praktikum – SS 2009 – [email protected]
JDBC 3.0Neuerungen 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 KeysStatement 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);
}
Praktikum aus Softwareentwicklung 2
38Java Praktikum – SS 2009 – [email protected]
JDBC 3.0Neuerungen in JDBC 3.0 (2/3)
• Connection pooling• Prepared Statement pooling• Savepoints in Transaktionen
Praktikum aus Softwareentwicklung 2
39Java Praktikum – SS 2009 – [email protected]
JDBC 3.0Neuerungen 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();
Praktikum aus Softwareentwicklung 2
40Java Praktikum – SS 2009 – [email protected]
Ende der 4. Übung