JDBC - ssw.jku.at

Java Database Connectivity-API (JDBC)
Motivation
Design
Grundlagen
Typen
Metadaten
Transaktionen
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
1
Motivation
Problem: Zugriff auf ein DBMS ist Herstellerabhängig
Anwendung
Anwendung
Anwendung
MySQL API
MySQL
DB2 API
DB2
Oracle API
Oracle
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
2
Motivation
Lösung: Zwischenschicht
MySQL API
Anwendung JDBC API
JDBC
MySQL
DB2 API
DB2
Oracle API
Oracle
JDBC Treiber
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
3
Design
Entwicklung seit: 1995
Erster Ansatz: Java erweitern
Zweiter Anzatz: 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 JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
4
Design
Java Anwendung
JDBC-Treibermanager
JDBC/ODBC
-Brücke
JDBCTreiber
ODBCTreiber
Datenbank
Datenbank
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
5
Treiber
Typ 1: JDBC/ODBC-Brücke
ODBC ist sehr weit verbreitet Leistung Wartung Testen, Experimentieren
kein JDBC Treiber verfügbar
Windows Plattformen
JDBCAnwendung
ODBC
Brücke
•
•
•
•
•
•
DB
Client
Server
Typ 2: Partial Java Driver
•
•
•
•
konvertiert JDBC Aufruf in DB abhängigen API Aufruf
schnell, weil API Aufruf kompiliert ist A
DB + OS abhängig JDBCP
Nutzer braucht plattformabhängige API Anw.
I
Client
DB
Server
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
6
Treiber
Typ 3: Reiner Java Treiber zu Middleware
•
•
•
•
Keine plattformabhängigen Treiber am Client DB unabhängig JDBCFlexibel, mehrere DB möglich Anw.
DB abhängiger Code in Middleware
Client
M
W
DB
Server Server
Typ 4: Reiner Java Treiber direkt zur DB
•
•
•
•
JDBC in DB spezifische Netzwerkaufrufe verpackt
Schnell Keine plattformabhängigen Treiber am Client Client braucht für verschiedene DB verschiedene
Treiber JDBCAnw.
DB
Client
Server
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
7
Treiberinstallation
Download
• http://developers.sun.com/product/jdbc/drivers
• Datenbankhersteller
Installation
• Eintragen in den Klassenpfad
Registrieren
• Bei dem Programmstart durch Parameter:
-
java –Djdbc.drivers=com.mysql.jdbc.Driver <Programm>
• Setzen der Systemeigenschaft "jdbc.drivers":
-
System.setProperty("jdbc.drivers",
"com.mysql.jdbc.Driver");
• Händisches Instanzieren der Treiber-Klasse:
- Class.forName("com.mysql.jdbc.Driver");
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
8
Verbindungsaufbau
Verbindungsaufbau erfolgt mit
• Url zur Datenbank
• Benutername, Passwort
Datenbank Url
• jdbc:<Datenbanktreiber>:<treiberspezifische Angaben>
• MySql:
- jdbc:mysql://<host>:<port>/< Datenbankname>
• JavaDB (Derby):
- jdbc:derby:/path/to/Database
- jdbc:derby:Database
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
9
Grundlagen: DriverManager
DriverManager
• Verwaltet registrierte Treiber
• Aufbau von Verbindungen
getConnection(String url,
url, String user,
user, String password)
password)
- Liefert eine Connection zu der gegebenen url wenn ein passender Treiber
registriert ist.
/* Registrieren des Treibers */
Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Anfordern einer Datenbankverbindung */
Connection con = DriverManager.getConnection(url, user, pass);
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
10
Grundlagen: Connection
Connection: Verwaltet die Verbindung (Session) zu einer Datenbank.
• close()
- Schließen der Verbindung
• commit()
- Bestätigen alle bisher vorgenommenen Änderungen, standard ist auto-commit
• Statement createStatement()
- Erzeugt ein Statement mit dem SQL-Statements an die Datenbank
abgegeben werden können.
Statement stat = con.createStatement();
stat.executeUpdate("INSERT INTO test VALUES ('Hallo')");
• PreparedStatement prepareStatement(String sql)
- Erzeugt Statements welche von der Datenbank vorkompiliert werden können.
PreparedStatement stat;
stat = con.prepareStatement("INSERT INTO test VALUES ('Welt')");
...
stat.executeUpdate();
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
11
Grundlagen: Statement
Keine Paramerter verwenden!
Stichwort: SQL-Injection
Statement:
• ResultSet executeQuery(String sql)
- Ausführen einer SQL Abfrage (SELECT)
• int executeUpdate(String sql)
- Ausführen eines Updates (UPDATE, INSERT, DELETE, CREATE).
- Rückgabewert zeigt die Anzahl der betroffenen Zeilen.
• boolean execute(String sql)
- Ausführen einer beliebigen SQL Anweisung.
- Rückgabewert Zeigt an ob eine Ergebnismenge geliefert wurde.
• int getUpdateCount()
- Anzahl der von der letzten Anweisung betroffenen Zeilen oder -1 wenn die
Anweisung keinen Zähler hatte.
• ResultSet getResultSet()
- Ergebnismenge der letzten Abfrage oder null wenn wenn die Anweisung
keine Ergebnismenge hatte.
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
12
Grundlagen: PreparedStatement
PreparedStatement: Absetzen von vorkompilierten Statements.
• void set<Typ>(int n, <Typ> x)
- Setzen des Parameters an der Stelle n (1 .. m).
• void clearParameters()
- Löschen aller Parameterwerte.
• ResultSet executeQuery()
- Ausführen der vorkompilierten SQL Abfrage (SELECT)
• int executeUpdate()
- Ausführen des vorkompilierten Updates (UPDATE, INSERT, DELETE,
CREATE).
- Rückgabewert zeigt die Anzahl der betroffenen Zeilen.
PreparedStatement stat;
stat = con.prepareStatement("INSERT INTO test VALUES (?)");
stat.setString(1, "Hallo");
stat.executeUpdate();
stat.setString(1, "Welt!");
stat.executeUpdate();
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
13
Grundlagen: Callable Statement
CallableStatement: Ausfüren von Datenbankprozeduren (SQL stored
procedures) über spezielle SQL strings:
•
•
•
•
Parameterlose Prozedur: {call procedure_name}
Prozedur: {call procedure_name[(?, ?, ...)]}
Funktion: {? = call procedure_name[(?, ?, ...)]}
Das setzen der Parameter erfolgt analog zu den PreparedStatements
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
14
Grundlagen: ResultSet
ResultSet: Ermöglicht das zeilenweise Abarbeiten der Ergebnistabelle.
• boolean next()
- Anspringen der nächsten Zeile, begonnen wird vor der ersten Zeile.
- true solange noch eine gültige Zeile erreicht wurde.
• <Typ> get<Typ>(int spalte)
• <Typ> get<Typ>(String spaltenName)
• int findColumn(String spaltenName)
getString(3) => 25
getString("Name") => Max
findColumn("Nr") => 1
next()
Nr
1
2
...
Name
Max
Kurt
...
Age
25
27
...
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
15
Grundlagen: ResultSet Fortsetzung
• boolean first()
- Erste Zeile im ResultSet.
- true wenn eine gültige Zeile erreicht wurde.
• beforeFirst()
- Vor die erste Zeile im ResultSet.
• boolean last()
- Letzte Zeile im ResultSet.
- true wenn eine gültige Zeile erreicht wurde.
• afterLast()
- Nach letzter Zeile im ResultSet.
• boolean absolute(int row)
- Eine Zeile anspringen
row > 0 ... von oben gezählt (1 erste Zeile, 2 zweite Zeile, ...)
row < 0 ... von unten gezählt (-1 letzte Zeile, -2 vorletzte Zeile, ...)
- true wenn eine gültige Zeile erreicht wurde.
• int getRow()
- Nummer der aktuellen Zeile
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
16
Grundlagen: Beispiel ResultSet
Wie viele Zeilen hat ein ResultSet?
Connection con;
...
Statement stat = con.createStatement();
ResultSet result = stat.executeQuery("SELECT ...");
int rowAmount;
result.last();
rowAmount = result.getRow();
result.beforeFirst();
// Mit ResultSet arbeiten
while (result.next()) {
...
}
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
17
Typen
Standard-Typemapping zwischen SQL und JAVA
SQL Type
Java Type
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
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
18
Metadaten
Beschreibung der Struktur der Datenbank und deren Tabellen
DatabaseMetaData:
• <Connection>.getMetaData()
• 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:
Typische namen: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
"LOCAL TEMPORARY", "ALIAS", "SYNONYM"
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
19
Metadaten
ResultSetMetaData:
Nr
1
2
...
• <ResultSet>.getMetaData()
• int getColumnCount()
- Anzahl der Spalten im ResultSet.
•
•
•
•
•
Name Title ...
Max Mag. ...
Kurt DI
...
...
...
Age
25
27
...
String getColumnName(int column)
int getColumnType(int column)
String getColumnTypeName(int column) SELECT Nr, Name,
Age
String getTableName(int column)
FORM users;
...
getColumnCount() => 3
getColumnName(1) => "Nr"
getColumnType(3) => 4
getColumnTypeName(3) => "int"
getTableName(2) => "users"
Nr
1
2
...
Name Age
Max 25
Kurt 27
...
...
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
20
Transaktionen
Auto Commit: Jede Anweisung ist eine abgeschlossene Transaktion.
• Abfragen:
- <Connection>.getAutoCommit()
• Setzen:
- <Connection>.setAutoCommit(bool)
Abschliessen einer Transaktion:
• <Connection>.commit()
Rücksetzen im Fehlerfall (z.B.: SQLException):
• <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(); }
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
21
Zusammenfassung
Datenbankunabhängigkeit
• Zwischenschicht
• Treiberschnittstelle (mind. SQL 92)
- 4 Treiberarten:
JDBC -> ODBC
Teilweise Java
Nur Java zu einer Middleware
Nur Java zur Datenbank
• Einfachere Programmentwicklung
Beliebige SQL-Kommandos absetzbar
• Optimierung / Datenbankabhängigkeit
Arten von Statements
• java.sql.Statement
- Statisch oder vom Benutzer frei wählbar (Achtung: SQL injection)
• java.sql.PreparedStatement
- Vorbereitete Statements (Sicher gegen SQL injection, schnell)
• java.sql.CallableStatement
- Ausführen von SQL stored procedures
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
22
Neuerungen in JDBC 4.0 (Java 6.0)
Spezifiziert in JSR-221
Automatisches Laden des Treibers beim Verbindungsaufbau
SQL:2003
Unterstützung großer Objekte (CLOB, BLOB)
Mehr Datentypen (SQLXML)
Neue Exceptions
•
•
•
•
SQLTransientException
SQLRecoverableException
SQLNonTransientException
V
JavaDB (Derby)
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
23
Java Typ JDBC Typ (PreparedStatement)
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
JSR 221, JDBC Specification 4, November 7, 2006, Page 198
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
24
JDBC Typ Java Typ (ResultSet)
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
JSR 221, JDBC Specification 4, November 7, 2006, Page 199
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
25
Arbeiten mit Derby
Umgebungsvariablen
• JAVA_HOME= Pfad zur Java-Installation
• DERBY_INSTALL=%JAVA_HOME%\db
• CLASSPATH
+ %DERBY_INSTALL%\lib\derby.jar
+ %DERBY_INSTALL%\lib\derbytools.jar
• PATH
+ %JAVA_HOME%\db\frameworks\embedded\bin
Systeminformationen
• java org.apache.dery.tools.sysinfo
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
26
Arbeiten mit Derby
Kommandozeilenwerkzeug
• java org.apache.derby.tools.ij
Verbinden zu, und
erzeugen einer
Datenbank
Erzeugen einer Tabelle
Beschreibung
einer Tabelle
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
27
Arbeiten mit Derby
Abfragen von
Datensätzen
Aktualisieren eines
Datensatzes
Löschen einer
Tabelle
JOHANNES KEPLER UNIVERSITY LINZ
Research and teaching network
Pratikum SWE 2
© M. Löberbauer, T. Kotzmann, H. Prähofer
28