jdbc - ssw.jku.at

Java Database Connectivity API / JDBC
Motivation
Design
Treiber
Grundlagen
Metadaten
Transaktionen
Exceptions
Pratikum SWE 2 © M. Löberbauer, T. Kotzmann, H. Prähofer
1
Motivation
•! Problem: Zugriff auf DBMS nicht einheitlich
Anwendung
Anwendung
Anwendung
MySQL API
DB2 API
Oracle API
MySQL
DB2
Oracle
2
Motivation
•! Lösung: Zwischenschicht
MySQL API
Anwendung
JDBC API
J
D
B
C
DB2 API
Oracle API
MySQL
DB2
Oracle
3
Design
•!
•!
•!
•!
•!
Entwicklung seit: 1995
Erster Ansatz: Java erweitern
Zweiter Ansatz: Treiber der Datenbankhersteller
Vorbild: ODBC
Unterschiede:
–! ODBC wenige Befehle, viele Optionen
–! JDBC viele einfache Methoden
–! ODBC nutzt void-Zeiger
–! Java kennt keine Zeiger
•! Flexibilität: JDBC erlaubt beliebige Zeichenfolgen
–! Anpassung an Datenbank möglich.
•! Optimierung !
•! Bindung "
4
Design
Java Anwendung
JDBC-Treibermanger
JDBC/ODBCBrücke
JDBC-Treiber
ODBC-Treiber
Datenbank
Datenbank
5
Treiber, Typ 1 und 2
•! Typ 1: Brücke
–! zB: JDBC/ODBC
•! ODBC ist verbreitet !
•! Testen, Experimentieren
•! Windows Plattformen
–! Leistung "
–! Wartung "
–! Wenn kein JDBC-Treiber
verfügbar ist
ODBC
ODBCTreiber
DB
Server
Client
•! Typ 2: Partial Java Driver
–! Gibt Aufruf an native
API weiter
–! DB und OS abhängig "
–! Nutzer braucht
plattformabhängige API "
B
r
ü
c
k
e
JDBCAnwendung
JDBCAnwendung
Client
A
P
I
DB
Server
6
Treiber, Typ 3 und 4
•! Typ 3: Reiner Java Treiber zu Middleware
–!
–!
–!
–!
Portabel !
DB unabhängig !
Flexibel, mehrere DB möglich !
DB abhängiger Teil in Middleware
•! Typ 4: Reiner Java Treiber zur DB
JDBCAnwendung
Client
–! Portabel !
–! Schnell !
JDBC–! Verbindung
Anwendung
•! Netz
•! File I/O
•! Embedded
Client
–! Client braucht DB abhängige Treiber "
M
W
DB
Server
Server
DB
Server
7
Treiber, Installation
•! Download
–! http://developers.sun.com/product/jdbc/drivers
–! Datenbankhersteller
•! Installation
–! Eintragen in den Klassenpfad
•! Laden eines JDBC Treibers
–! System Property: jdbc.drivers
•! java -Djdbc.drivers=org.apache.derby.jdbc.EmbeddedDriver Xyz
•! System.setProperty("jdbc.drivers",
"org.apache.derby.jdbc.EmbeddedDriver");
–! Manuelles laden der Treiberklasse
•! Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
–! Automatisch durch DriverManager
•! Java Service: java.sql.Driver
8
Verbindungsaufbau
•! DriverManager
–! Verwaltet registrierte Treiber, baut Verbindungen auf
•! Connection getConnection(String url,
String user, String password)
•! Datenbank URL
–! Aufbau: jdbc:<subprotrokoll>:<subname>
•! jdbc:<Datenbanktreiber>:<treiberspezifische Angaben>
–! Derby
•! jdbc:derby:/path/to/Database
•! jdbc:derby:Database
–! MySQL
•! jdbc:mysql://<host>:<port>/<Database>
!"#$%&'()*+,-)
9
Verbindung
•! Connection: Verwaltet Verbindung (Session) zur DB
–! close()
•! Schließen der Verbindung
–! commit()
•! Bestätigen vorangegangener Änderungen, Standard
ist auto-commit
–! Statement createStatement()
Statement stat = con.createStatement();
stat.executeUpdate("INSERT INTO test VALUES ('Hallo')");
–! PreparedStatement prepareStatement(String sql)
PreparedStatement stat = con
.prepareStatement("INSERT INTO test VALUES ('Hallo')");
...
stat.executeUpdate();
10
Statement
Parameterlos!
•! ResultSet executeQuery(String sql)
SQL-Injection
–! Ausführen einer SQL-Abfrage (SELECT)
•! int executeUpdate(String sql)
–! Ausführen eines Updates (UPDATE, INSERT, DELETE)
–! Liefert die Anzahl der betroffenen Zeilen
•! boolean execute(String sql)
–! Ausführen beliebiger SQL-Anweisungen
–! Liefert true wenn eine Ergebnismenge geliefert wurde
•! int getUpdateCount()
–! Anzahl der betroffenen Zeilen, oder -1 wenn Anweisung keinen
Zähler hatte
•! ResultSet getResultSet()
–! Ergebnismenge der letzten Abfrage, oder null wenn keine
Ergebnismenge geliefert wurde
•! void close()
–! Gibt alle JDBC Ressourcen frei
11
PreparedStatement
•! void set<Typ>(int n, <Typ> x)
90% Lösung
–! Setzen des Parameters an der Stelle n (1..m)
•! void clearParameters()
–! Löschen aller Parameterwerte
•! ResultSet executeQuery()
–! Ausführen der vorübersetzten SQL-Anfrage (SELECT)
•! int executeUpdate()
–! Ausführen des vorübersetzten Updates (UPDATE, INSERT, DELETE)
–! Gibt die Anzahl betroffener Zeilen zurück
PreparedStatement stat;
stat = con.prepareStatement("INSERT INTO test VALUES (?,?)");
stat.setString(1, "Hallo");
stat.setString(2, "Welt");
stat.executeUpdate();
stat.setString(2, "Jane");
stat.executeUpdate();
12
CallableStatement
•! Ausführen von DB-Prozeduren (SQL stored procedures)
•! Parameterlose Prozedur
–! {call procedure_name }
•! Prozedur
–! {call procedure_name(?,?,...)}
•! Funktion
–! { ? = call procedure_name(?,?,...) }
•! Eingangsparameter analog zu PreparedStatement
•! Ausgabeparameter müssen registriert werden
–! void registerOutParameter(int index, int sqlType)
CallableStatement cs = con
.prepareCall("{ CALL GET_NUMBER_FOR_NAME(?, ?) }");
cs.registerOutParameter(2, java.sql.Types.INTEGER);
cs.setString(1, "Duke");
cs.execute();
int number = cs.getInt(2);
13
Batch Statements
•! Kommandos Anfügen
–! Statements: void addBatch(String sqlCmd)
–! PreparedStatements: void addBatch()
•! Löschen der Kommandos: void clearBatch()
•! Ausführen der Kommandos: int[] executeBatch()
Statement stmt = conn.createStatement();
stmt.addBatch("insert into Person values(5, 'Herman', 'Hollerith', 1983)");
stmt.addBatch("insert into Person values(6, 'Larry', 'Ellison', 1977)");
int[] upds = stmt.executeBatch();
PreparedStatement pStmt =
conn.prepareStatement("insert into Person values(?, ?, ?, ?)");
for (Person p : persons) {
pStmt.setInt(1, p.getId()); pStmt.setString(2, p.getFirstName());
pStmt.setString(3, p.getLastName()); pStmt.setInt(4, p.getYear());
pStmt.addBatch();
}
int[] res = pStmt.executeBatch();
14
ResultSet
•! ResultSet ist die Ergebnistabelle einer Abfrage
•! Arten
–!
–!
–!
–!
einfache: können nur sequentiell von vorne nach hinten durchlaufen werden
scrollbare: erlaubt beide Richtungen und Positionierung
änderbare: erlauben Änderungen in der Datenbank
sensitive: zeigen Änderungen in Datenbank an
•! Art beim Erzeugen des Statements festlegen:
ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE
Statement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability) throws SQLException
ResultSet.CONCUR_READONLY
ResultSet.CONCUR_UPDATABLE
ResultSet.HOLD_CURSORS_OVER_COMMIT
ResultSet.CLOSE_CURSORS_AT_COMMIT
15
ResultSet Lesen
•! Zeilenweise abarbeiten der Ergebnistabelle
–! boolean next()
•! Anspringen der nächsten Zeile
•! true solange gültige Zeile erreicht wird
•! ResultSet beginnt vor der ersten Zeile
–! <Typ> get<Typ>(int spalte)
–! <Typ> get<Typ>(String spaltenName)
–! boolean wasNull()
•! true wenn letzter Wert SQL-NULL war
–! int findColumn(String spaltenName)
!"# !$%&# '(&#
getInt(3) => 25
wasNull() => false
getString("Name") => Max
findColumn("Nr") => 1
next()
*)
.$/)
,+)
,)
0%1')
,-)
222) 222)
222)
16
ResultSet Navigation
•! boolean first()
–! Erste Zeile im ResultSet
–! true wenn gültige Zeile erreicht
•! void beforeFirst()
–! Vor die erste Zeile im ResultSet
•! boolean last()
–! Letzte Zeile im ResultSet
–! true wenn gültige Zeile erreicht
•! void afterLast()
–! Nach letzter Zeile im ResultSet
•! boolean absolute(int row)
–! Eine Zeile anspringen
•! row > 0 ... von oben (1 erste Zeile, 2 zweite Zeile, ...)
•! row < 0 ... von unten (-1 letzte Zeile, -2 vorletzte Zeile, ...)
–! true wenn gültige Zeile erreicht
•! int getRow()
–! Nummer der aktuellen Zeile
17
ResultSet Update
•! void update<Typ>(int col, <Typ> x)
–! ändert die aktuelle Zeile
•! void updateRow()
–! schreibt die aktuelle Zeile in die Datenbank zurück
•! void moveToInsertRow()
–! bewegt den Cursor auf die "insert row"
•! void insertRow()
–! der Cursor muss vorher mit moveToInsertRow() auf die "insert
row" bewegt werden.
–! fügt eine neue Zeile in die Datenbank ein
•! void deleteRow()
–! löscht aktuelle Zeile aus der Datenbank
18
Typen
SQL-Typ
Java-Typ
CHAR, VARCHAR, LONGVARCHAR
String
NUMERIC, DECIMAL
java.math.BigDecimal
BIT
boolean
TINYINT
byte
SMALLINT
short
INTEGER
int
BIGINT
long
REAL
float
FLOAT, DOUBLE
double
BINARY, VARBINARY, LONGVARBINARY
byte[]
DATE
java.sql.Date
TIME
java.sql.Time
TIMESTAMP
java.sql.Timestamp
...
siehe JSR-221, Appendix B, Date
Type Conversion Tables
19
Metadaten, Datenbank
•! Erstellen über Verbindung
–! DatabaseMetaData <Connection>.getMetaData()
•! Allgemeines
–!
–!
–!
–!
Connection getConnection()
String getURL()
String getUserName()
boolean isReadOnly()
•! Eigenschaften
–! boolean supportsANSI92EntryLevelSQL()
–! boolean supportsTransactions()
–! boolean supportsGroupBy()
•! Beschränkungen
–! int getMaxStatementLength()
–! int getMaxStatements()
–! int getMaxConnections()
0 .. kein Limit oder
unbekannt
20
Metadaten, Tabellen
•! ResultSet getTables(String catalog, String schema,
String table, String[] types)
•! ResultSet getColumns(String catalog, String schema,
String table, String column)
–! catalog: Name des Katalogs
•! "" Tabellen ohne Katalog, null Katalognamen nicht
berücksichtigen
–! scheme: Schemaname
•! "" Tabellen ohne Schema, null nicht berücksichtigen
–! table: Tabellenname
•! null nicht berücksichtigen
–! column: Spaltenname
•! null nicht berücksichtigen
–! types:
•! zB: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
"LOCAL TEMPORARY", "ALIAS", "SYNONYM"
•! null nicht berücksichtigen
21
Metadaten, Ergebnismenge
•! Erstellen
–! ResultSetMetaData <ResultSet>.getMetaData()
•! Eigenschaften
–!
–!
–!
–!
–!
–!
Nr Name Title ... Age
int getColumnCount()
1
String getColumnName(int!column)
2
int getColumnType(int!column)
...
String getColumnTypeName(int!column)
String getTableName(int!column)
boolean isCurrency(int column)
getColumnCount() => 3
getColumnName(1) => "Nr"
getColumnType(3) => 4 // INTEGER
getColumnTypeName(3) => "int"
getTableName(2) => "users"
Max
DI
... 30
Susi
Mag ... 28
...
...
... ...
SELECT Nr,
Name, Age
FROM users;
Nr Name Age
1
Max
30
2
Susi
28
...
...
...
22
Transaktionen
•! Standard: auto-commit, jedes Statement eine Transaktion
–! boolean <Connection>.getAutoCommit()
–! void <Connection>.setAutoCommit(boolean ac)
•! Transaktion startet automatisch
•! Transaktion abschießen
Auto-Commit
–! void <Connection>.commit()
•! Transaktion rücksetzen (zB: SQLException)
–! void <Connection>.rollback()
Connection con;
...
try {
con.setAutoCommit(false);
Statement stat = con.createStatement();
stat.executeUpdate("INSERT ...");
stat.executeUpdate("INSERT ...");
stat.executeUpdate("UPDATE ...");
con.commit();
} catch (SQLException e) { con.rollback(); }
Savepoints
TransactionIsolation
NONE
READ_UNCOMMITTED
READ_COMMITTED
REPEATABLE_READ
SERIALIZABLE
23
Exceptions
SQLException
SQLNonTransient
Exception
Erneuter
Versuch wird
fehlschlagen
SQLTransient
Exception
Erneuter
Versuch kann
durchgehen
SQLRecoverable
Exception
Erneuter Versuch
nach Eingriffen
kann durchgehen
24
JavaDB, Derby
•! Seit Java 6.0 mitgeliefert
•! Web-Seite: http://db.apache.org/derby
•! Umgebungsvariablen
–! JAVA_HOME=Pfad zur Java JDK Installation
–! DERBY_HOME=Pfad zur Derby Installation
–! PATH um DERBY_HOME/bin erweitern
•! JARs in DERBY_HOME/lib
–!
–!
–!
–!
–!
derby.jar, Engine, genügt für embedded DB
derbynet.jar, Netzzugriff, Serverseitig
derbyclient.jar, Netzzugriff, Clientseitig
derbytools.jar, Verwaltungs-Werkzeuge
derbyrun.jar verweist auf:
•! derby.jar derbyclient.jar derbytools.jar
derbynet.jar
25
JavaDB, Derby Installation & Treiber
•! DERBY_HOME (Windows)
–! C:\Programme\Sun\JavaDB
•! Derby Hilfsprogramme
–! DERBY_HOME\bin
•! Derby Driver
–! org.apache.derby.jdbc.ClientDriver
–! org.apache.derby.jdbc.EmbeddedDriver
•! Derby Connection-String
–! jdbc:derby://localhost:1527/Name_of_DB
Derby Network–! jdbc:derby:Name_of_DB
Embedded, Zugriff auf
Dateien: Nur eine
Verbindung möglich!
Server muss
gestartet sein!
26
JavaDB, Derby, Kommandozeilen-Werkzeuge
•! Systeminformation: sysinfo
27
JavaDB, Derby, Kommandozeilen-Werkzeuge
•! Kommandozeilenwerkzeug: ij
Verbinden zu (und evtl.
erzeugen einer)
Datenbank
Tabelle erzeugen
Beschreibung einer Tabelle
28
JavaDB, Derby, Kommandozeilen-Werkzeuge
•! Kommandozeilenwerkzeug: ij
Einfügen eines Datensatzes
Aktualisieren eines Datensatzes
Löschen einer Tabelle
29
Zusammenfassung
•! Datenbankunabhängigkeit
–! Zwischenschicht -> einfachere Programmentwicklung
–! Treiberschnittstelle (min. SQL 92 entry level)
•! 4 Treiberarten
–! Brücke (zB: JDBC -> ODBC)
–! Teilweise Java
–! Java zu Middleware
–! Java zu Datenbank
•! Beliebige SQL-Kommandos absetzbar
–! Optimierung vs. Datenbankunabhängigkeit
•! Statements
–! PreparedStatement
•! Vorkompiliert, mit Parametern (Sicher gegen SQL-Injection)
–! Statement
•! Statisch, Abfragen vom Benutzer, keine Parameter
–! CallableStatement
•! Stored Procedures, mit IN, OUT und INOUT Parametern
30