Java Database Connectivity API / JDBC · 2009-05-19 · SQL-Typ Java-Typ CHAR, VARCHAR, LONGVARCHAR String NUMERIC, DECIMAL java.math.BigDecimal BIT boolean TINYINT byte SMALLINT

1 Pratikum SWE 2 © M. Löberbauer, T. Kotzmann, H. Prähofer

Java Database Connectivity API / JDBC

Motivation

Design Treiber

Grundlagen Metadaten

Transaktionen

Exceptions

2

Motivation

•! Problem: Zugriff auf DBMS nicht einheitlich

MySQL API Anwendung

DB2 API Anwendung

Oracle API Anwendung

MySQL

DB2

Oracle

3

Motivation

•! Lösung: Zwischenschicht

JDBC

MySQL MySQL API

DB2 API DB2

Oracle API Oracle

Anwendung JDBC API

4

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 "

5

Design

Java Anwendung

JDBC-Treibermanger

JDBC/ODBC-Brücke

JDBC-Treiber

ODBC-Treiber

Datenbank Datenbank

6

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

•! Typ 2: Partial Java Driver –! Gibt Aufruf an native

API weiter –! DB und OS abhängig "

–! Nutzer braucht plattformabhängige API "

JDBC-Anwendung

Brücke

ODBC

ODBC-Treiber

DB

Client Server

DB JDBC-

Anwendung

A P I

Client Server

7

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 –! Portabel !

–! Schnell !

–! Verbindung

•! Netz

•! File I/O

•! Embedded

–! Client braucht DB abhängige Treiber "

JDBC-Anwendung

MW DB

Client Server Server

JDBC-Anwendung DB

Client Server

8

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

9

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>

!"#$%&'()*+,-)

10

Verbindung

•! Connection: Verwaltet Verbindung (Session) zur DB –! close()

•! Schließen der Verbindung

–! commit()

•! Bestätigen vorangegangener Änderungen, Standard ist auto-commit

–! Statement createStatement()

–! PreparedStatement prepareStatement(String sql)

Statement stat = con.createStatement(); stat.executeUpdate("INSERT INTO test VALUES ('Hallo')");

PreparedStatement stat = con .prepareStatement("INSERT INTO test VALUES ('Hallo')"); ... stat.executeUpdate();

11

Statement

•! ResultSet executeQuery(String sql)

–! 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

Parameterlos! SQL-Injection

12

PreparedStatement

•! 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 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();

90% Lösung

13

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);

14

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();

15

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:

Statement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException

ResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.TYPE_SCROLL_SENSITIVE

ResultSet.CONCUR_READONLY ResultSet.CONCUR_UPDATABLE

ResultSet.HOLD_CURSORS_OVER_COMMIT ResultSet.CLOSE_CURSORS_AT_COMMIT

16

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)

!"# !$%&# '(&#

*) .$/) ,+)

,) 0%1') ,-)

222) 222) 222)

getInt(3) => 25 wasNull() => false getString("Name") => Max findColumn("Nr") => 1

next()

17

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

18

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

19

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

20

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

21

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


–! types:

•! zB: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"


22

Metadaten, Ergebnismenge

•! Erstellen –! ResultSetMetaData <ResultSet>.getMetaData()

•! Eigenschaften –! int getColumnCount()

–! String getColumnName(int!column)

–! int getColumnType(int!column)

–! String getColumnTypeName(int!column)

–! String getTableName(int!column)

–! boolean isCurrency(int column)

Nr Name Title ... Age

1 Max DI ... 30

2 Susi Mag ... 28

... ... ... ... ...

SELECT Nr, Name, Age

FROM users;

Nr Name Age

1 Max 30

2 Susi 28

... ... ...

getColumnCount() => 3 getColumnName(1) => "Nr" getColumnType(3) => 4 // INTEGER getColumnTypeName(3) => "int" getTableName(2) => "users"

23

Transaktionen

•! Standard: auto-commit, jedes Statement eine Transaktion –! boolean <Connection>.getAutoCommit()

–! void <Connection>.setAutoCommit(boolean ac)

•! Transaktion startet automatisch

•! Transaktion abschießen –! 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(); }

Auto-Commit

Savepoints

Transaction-Isolation

NONE READ_UNCOMMITTED READ_COMMITTED REPEATABLE_READ SERIALIZABLE

24

Exceptions

SQLException

SQLNonTransient Exception

SQLTransient Exception

SQLRecoverable Exception

Erneuter Versuch wird fehlschlagen

Erneuter Versuch kann durchgehen

Erneuter Versuch nach Eingriffen kann durchgehen

25

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

26

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

–! jdbc:derby:Name_of_DB Derby Network-Server muss gestartet sein!

Embedded, Zugriff auf Dateien: Nur eine Verbindung möglich!

27

JavaDB, Derby, Kommandozeilen-Werkzeuge

•! Systeminformation: sysinfo

28


•! Kommandozeilenwerkzeug: ij Verbinden zu (und evtl. erzeugen einer)

Datenbank

Tabelle erzeugen

Beschreibung einer Tabelle

29


•! Kommandozeilenwerkzeug: ij

Einfügen eines Datensatzes

Aktualisieren eines Datensatzes

Löschen einer Tabelle

30

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

Documents

Java Database Connectivity API / JDBC · 2009-05-19 · SQL-Typ Java-Typ CHAR, VARCHAR, LONGVARCHAR String NUMERIC, DECIMAL java.math.BigDecimal BIT boolean TINYINT byte SMALLINT