pdf (1/1)
Werbung
Datenbankanwendung Wintersemester 2014/15 Prof. Dr.-Ing. Sebastian Michel TU Kaiserslautern [email protected] Anmerkungen/Ankündigungen 1. Bemerkung/Klarstellung zur automatischen Erkennung von Endlosrekursion in Folien von Vorlesung 9. 2. Der Zugang zu den Musterlösungen ist nur noch Uni-intern oder via Login möglich. Die Zugangsdaten sind identisch zu den Zugangsdaten für die Beispieldatenbanken. Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 2 / 42 Relationenkalkül Übersicht/Motivation Der Relationenkalkül • Bisher: Relationale Algebra, prozedural • Jetzt: Relationenkalkül, deklarativ • beide sind gleich mächtig, wenn Relationenkalkül auf sichere Ausdrücke beschränkt ist • d.h. alle Ausdrücke der relationalen Algebra können auch im sicheren Relationenkalkül ausgedrückt werden und umgekehrt. • Zwei Varianten: • relationaler Tupelkalkül: hatte großen Einfluss auf SQL • relationaler Domänenkalkül: hatte großen Einfluss auf “Query-by Example” (QBE) Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 3 / 42 Relationenkalkül Tupelkalkül Der relationale Tupelkalkül Eine Anfrage im relationalen Tupelkalkül hat die Form {t|P (t)} wobei t eine Tupelvariable ist und P (t) ein Prädikat. P (t) muss erfüllt sein damit t Teil des Ergebnis ist. Auch in Kombination mit Tupelkonstruktor: {[t1 .A1 , ..., tn .An ]|P (t1 , ..., tn )} Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 4 / 42 Relationenkalkül Tupelkalkül Beispiele • C4-Professoren: {p|p ∈ P rof essoren ∧ p.Rang = C4} • Paare von Professoren (Name) und Assistenten (PersNr): {[p.N ame, a.P ersN r]|p ∈ P rof essoren ∧ a ∈ Assistenten ∧p.P ersN r = a.Boss} • Studenten mit mindestens einer Vorlesung von Prof. Curie: {s|s ∈ Studenten ∧∃h ∈ hören(s.M atrN r = h.M atrN r ∧∃v ∈ V orlesungen(h.V orlN r = v.V orlN r ∧∃p ∈ P rof essoren(p.P ersN r = v.gelesenV on ∧p.N ame = ‘Curie‘)))} Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 5 / 42 Relationenkalkül Tupelkalkül .... in SQL ... ist es sehr ähnlich: Studenten mit mindestens einer Vorlesung von Prof. Curie: SELECT s.* from Studenten s where exists ( select h.* from hören h where h.MatrNr=s.MatrNr and exists ( select * from Vorlesungen v where v.VorlNr=h.VorlNr and exists ( select * from Professoren p where p.Name=’Curie’ and p.PersNr=v.gelesenVon))) Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 6 / 42 Relationenkalkül Tupelkalkül Allquantor Wer hat alle vierstündigen Vorlesungen gehört? {s|s ∈ Studenten ∧ ∀v ∈ V orlesungen(v.SW S = 4 ⇒ ∃h ∈ hoeren(h.V orlN r = v.V orlN r ∧ h.M atrN r = s.M atrN r))} Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 7 / 42 Relationenkalkül Tupelkalkül Definition des Tupelkalküls Atome • s ∈ R, mit s Tupelvariable und R Relationenname • s.Aφt.B, mit s und t Tupelvariablen, A und B Attributnamen und φ Vergleichoperator (=, 6=, ≤, ...) • s.Aφc mit Konstante c Formeln • Alle Atome sind Formeln • Ist P Formel, so auch ¬P und (P ) • Sind P1 und P2 Formeln, so auch P1 ∧ P2 , P1 ∨ P2 und P1 ⇒ P2 • Ist P (t) eine Formel mit freier Variable t, so auch ∀t ∈ R(P (t)) und ∃t ∈ R(P (t)) Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 8 / 42 Relationenkalkül Tupelkalkül Sicherheit • Einschränkung auf Anfragen mit endlichem Ergebnis. • Zum Beispiel ist die Anfrage {n|¬(n ∈ P rof essoren)} nicht sicher. • Das Ergebnis ist unendlich. • Bedingung: Ergebnis des Ausdrucks muss Teilmenge der Domäne der Formel sein. • Die Domäne einer Formel enthält • alle in der Formel vorkommenden Konstanten • alle Attributwerte von Relationen, die in der Formel referenziert werden Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 9 / 42 Relationenkalkül Domänenkalkül Der relationale Domänenkalkül Der Ausdruck des relationalen Domänenkalküls hat die Form {[v1 , v2 , ..., vn ]|P (v1 , v2 , ..., vn )} mit v1 , ...., vn Domänenvariablen und P ein Prädikat (oder Formel). Beispiel: Matrikelnummer und Namen der Prüflinge von Prof. Russel: {[m, n]|∃s([m, n, s] ∈ Studenten ∧∃v, p, g([m, v, p, g] ∈ pruef en ∧∃a, r, b([p, a, r, b] ∈ P rof essoren ∧a =0 Russel0 )))} Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 10 / 42 Relationenkalkül Domänenkalkül Hinweis zu freien Variablen und Quantifizierung, hier und im Tupelkalkül {[m, n]|∃s([m, n, s] ∈ Studenten ∧∃v, p, g([m, v, p, g] ∈ pruef en ∧∃a, r, b([p, a, r, b] ∈ P rof essoren ∧a =0 Russel0 )))} • Wie wir hier sehen wird im innersten Quantor eine Variable a für den Namen eines Professors benutzt. • Man hätte auch in diesem speziellen Fall den Variablennamen n nochmal in dem Existenzquantor benutzen können, der Logik wegen, da hier kein Konflikt mit dem äußeren “n” des Studentennamens besteht (weil der Scope diesem eine neue Rolle zuteilt) • Aber ohne dieses neuen Bindens von n und dann mit . . . ∧ n =0 Russel0 ) wäre hier die Suche auf Studenten eingeschränkt worden, die auch 0 Russel0 heißen. Regel: Vermeiden Sie daher mehrfache Verwendung des gleichen (freien bzw. gebundenen) Scopes Prof. Dr.-Ing. S.Parameternamens Michel TU Kaiserslauternin unterschiedlichen Datenbankanwendung, WS 14/15 11 / 42 Relationenkalkül Domänenkalkül Sicherheit des Domänenkalküls • Sicherheit ist analog zum Tupelkalkül • zum Beispiel ist {[p, n, r, o]|¬([p, n, r, o] ∈ P rof essoren)} nicht sicher. • Ein Ausdruck {[x1 , x2 , ..., xn ]|P (x1 , x2 , ..., x3 )} ist sicher, falls folgende drei Bedingungen gelten: Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 12 / 42 Relationenkalkül Domänenkalkül {[x1 , x2 , ..., xn ]|P (x1 , x2 , ..., x3 )} sicher falls .... 1. Falls Tupel [c1 , c2 , ..., cn ] mit Konstanten ci im Ergebnis enthalten ist, so muss jedes ci (1 ≤ i ≤ n) in der Domäne von P enthalten sein. 2. Für jede existenz-quantifizierte Teilformel ∃x(P1 (x)) muss gelten, dass P1 nur für Elemente aus der Domäne erfüllbar sein kann - oder evtl. für gar keine. Das heisst: Wenn für eine Konstante c das Prädikat P1 (c) erfüllt ist, so muss c in der Domäne von P1 enthalten sein. 3. Für jede universal-quantifizierte Teilformel ∀x(P1 (x)) muss gelten, dass sie dann und nur dann erfüllt ist, wenn P1 (x) für alle Werte der Domäne von P1 erfüllt ist. Das heisst: P1 (d) muss für alle d, die nicht in der Domäne von P1 enthalten sind, auf jeden Fall erfüllt sein. Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 13 / 42 Relationenkalkül Domänenkalkül Ausdruckskraft Die drei Sprachen • relationale Algebra, • relationaler Tupelkalkül, eingeschränkt auf sichere Ausdrücke • und relationaler Domänenkalkül, eingeschränkt auf sichere Ausdrücke ... sind gleich mächtig! Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 14 / 42 JDBC Datenbank-Zugriff via JDBC Java Database Connectivity • bietet Schnittstelle für den Zugriff auf ein DBMS aus Java-Anwendungen Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 15 / 42 JDBC JDBC: Connect und einfache Anfrage 1 2 3 4 5 6 // r e g i s t r i e r e g e e i g n e t e n T r e i b e r ( h i e r f u e r P o s t g r e s q l ) C l a s s . forName ( ” o r g . p o s t g r e s q l . D r i v e r ” ) ; // e r z e u g e V e r b i n d u n g z u r Datenbank C o n n e c t i o n conn = D r i v e r M a n a g e r . g e t C o n n e c t i o n ( ” jdbc : postgresql :// l o c a l h o s t / u n i v e r s i t y ” , ” use rn a m e ” , ” p a s s w o r d ” ) ; 7 8 9 // e r z e u g e e i n e i n f a c h e s S t a t e m e n t O b j e k t S t a t e m e n t s t m t = conn . c r e a t e S t a t e m e n t ( ) ; 10 11 12 13 // m i t e x e c u t e Query koennen nun d a r a u f A n f r a g e n a u s g e f u e h r t werden // E r g e b n i s s e i n Form e i n e s R e s u l t S e t O b j e k t s R e s u l t S e t r s e t = s t m t . e x e c u t e Q u e r y ( ”SELECT p . p e r s n r from p r o f e s s o r e n p” ) ; Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 16 / 42 14 15 JDBC JDBC: Connect und einfache Anfrage // d i e s e s b e s i t z t Metadaten R e s u l t S e t M e t a D a t a m e t a d at a = r s e t . getMetaData ( ) ; 16 17 18 // w e l c h e A t t r i b u t e ( S p a l t e n ) b e s i t z e n d i e E r g e b n i s −T u p e l ? i n t c o l u m n c o u n t = m e ta d at a . getColumnCount ( ) ; 19 20 21 22 23 f o r ( i n t i n d e x =1; i n d e x <=c o l u m n c o u n t ; i n d e x ++) { System . o u t . p r i n t l n ( ” S p a l t e ”+i n d e x+” h e i s s t ” + m e t a d a t a . getColumnName ( i n d e x ) ) ; } 24 25 26 27 28 // i t e r i e r e nun u e b e r E r g e b n i s s e while ( r s e t . next () ) { System . o u t . p r i n t l n ( r s e t . g e t S t r i n g ( 1 ) ) ; } Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 17 / 42 JDBC JDBC Treiber für Postgresql http://jdbc.postgresql.org/ Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 18 / 42 JDBC JDBC - wichtige Funktionalitäten Laden des Treibers • Kann auf verschiedene Weise erfolgen, z.B. durch explizites Laden mit dem Klassenlader: C l a s s . forName ( D r i v e r C l a s s N a m e ) ; Aufbau einer Verbindung • Connection-Objekt repräsentiert die Verbindung zum DB-Server • Beim Aufbau werden URL der DB, Benutzername und Passwort aus Strings übergeben (teilweise optional). C o n n e c t i o n conn = D r i v e r M a n a g e r . g e t C o n n e c t i o n ( u r l , l o g i n , password ) ; Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 19 / 42 JDBC Anweisungen (Statements) JDBC - wichtige Funktionalitäten (2) Anweisungen • Mit dem Connection-Objekt könne u.a. Metadaten der DB erfragt und Statement-Objekte zum Absetzen von SQL-Anweisngen erzeugt werden • Erzeugen einer SQL-Anweisung zur direkten (einmaligen) Ausführung S t a t e m e n t s t m t = conn . c r e a t e S t a t e m e n t ( ) ; • PreparedStatement-Objekt erlaubt das Erzeugen und Vorbereiten von (parametrisierten) SQL-Anweisungen zur wiederholten Ausführung P r e p a r e d S t a t e m e n t pstmt = conn . p r e p a r e S t a t e m e n t ( ” s e l e c t ∗ from p e r s o n a l where g e h a l t >= ? ” ) ; Schließen von Verbindungen, Statements, usw. stmt . c l o s e ( ) ; conn . c l o s e ( ) ; Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 20 / 42 JDBC Anweisungen (Statements) JDBC - Anweisungen Anweisungen (Statements) • Werden in einem Schritt vorbereitet und ausgeführt Die Methode executeQuery • führt die Anfrage aus und liefert Ergebnis zurück S t a t e m e n t s t m t = conn . c r e a t e S t a t e m e n t ( ) ; R e s u l t S e t r s e t = stmt . executeQuery ( ” s e l e c t pnr , name , g e h a l t from p e r s o n a l where g e h a l t >=40000” ) ; // w i r s e h e n g l e i c h , w i e man m i t d i e s e m R e s u l t S e t a r b e i t e t Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 21 / 42 JDBC Anweisungen (Statements) JDBC - Anweisungen Die Methode executeUpdate • werden zur direkten Ausführung von UPDATE-, INSERT-, DELETE- und DDL-Anweisungen benutzt S t a t e m e n t s t m t = conn . c r e a t e S t a t e m e n t ( ) ; i n t n = stmt . executeUpdate ( ” update p e r s o n a l set gehalt = gehalt ∗ 1.10 where g e h a l t < 20000 ” ) ; // n e n t h a e l t d i e A n z a h l d e r a k t u a l i s i e r t e n Z e i l e n Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 22 / 42 JDBC Anweisungen (Statements) JDBC - Prepared Anweisungen PreparedStatement-Objekt P r e p a r e d S t a t e m e n t pstmt ; double g e h a l t = 5 0 0 0 0 . 0 0 ; pstmt = conn . p r e p a r e S t a t e m e n t ( ” s e l e c t ∗ from p e r s o n a l where g e h a l t >= ? ” ) ; • Symbol ? markiert hier freie Parameter • Vor der Ausführung sind dann die Parameter einzusetzen. • Durch Methoden entsprechend Datentyp, z.B. pst m t . s e t D o u b l e ( 1 , g e h a l t ) ; https://docs.oracle.com/javase/8/docs/api/java/sql/ PreparedStatement.html Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 23 / 42 JDBC Anweisungen (Statements) JDBC - Prepared Anweisungen (2) Ausführen einer Prepared-Anweisung als Anfrage P r e p a r e d S t a t e m e n t pstmt ; double g e h a l t = 5 0 0 0 0 . 0 0 ; pstmt = conn . p r e p a r e S t a t e m e n t ( ” s e l e c t ∗ from p e r s o n a l where g e h a l t >= ? ” ) ; Vorbereitung und Ausführung pstmt = con . p r e p a r e S t a t e m e n t ( ” d e l e t e from p e r s o n a l where name = ? ” ) ; pstmt . s e t S t r i n g ( 1 , ” M a i e r ” ) ; i n t n = pstmt . executeUpdate ( ) ; // Methoden d e r P r e p a r e d −An wei sun gen haben k e i n e Argumente Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 24 / 42 JDBC Ergebnismengen (ResultSets) JDBC - Ergebnismengen und Cursor Select-Anfragen und Ergebnisübergabe • Jede JDBC-Methode, mit der man Anfragen an das DBMS stellen kann, liefert ResultSet-Objekte als Rückgabewert R e s u l t S e t r s e t = stmt . executeQuery ( ” s e l e c t pnr , name , g e h a l t from p e r s o n a l where g e h a l t >= ” + g e h a l t ) ; • Cursor-Zugriff und Konvertierung der DBMS-Datentypen in passende Java-Datentypen erforderlich • JDBC-Cursor ist durch die Methode next() der Klasse ResultSet implementiert https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 25 / 42 JDBC Ergebnismengen (ResultSets) JDBC - Ergebnismengen und Cursor (2) Cursor → ↓ next() getInt(”pnr”) ↓ 123 456 getString(”name”) ↓ Maier Schulze getDouble(”gehalt”) ↓ 23352.00 34553.00 Zugriff aus Java-Programm while ( r s e t . next () ) { System . o u t . p r i n t ( r e s . g e t I n t ( ” p n r ” )+” \ t ” ) ; System . o u t . p r i n t ( r e s . g e t S t r i n g ( ”name” )+” \ t ” ) ; System . o u t . p r i n t l n ( r e s . g e t S t r i n g ( ” g e h a l t ” ) ) ; } Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 26 / 42 JDBC Ergebnismengen (ResultSets) JDBC - Versch. Typen von ResultSets TYPE FORWARD ONLY • nur Aufruf von next() möglich TYPE SCROLL INSENSITIVE • Scroll-Operationen sind möglich, aber Aktualisierungen der Datenbank verändern ResultSet nach seiner Erstellung nicht Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 27 / 42 JDBC Ergebnismengen (ResultSets) JDBC - Versch. Typen von ResultSets (2) TYPE SCROLL SENSITIVE • Scroll-Operationen möglich und Änderungen in der Datenbank werden berücksichtigt ResultSet lässt Änderungen zu oder nicht: • CONCUR READ ONLY • CONCUR UPDATABLE S t a t e m e n t s t m t = conn . c r e a t e S t a t e m e n t ( R e s u l t S e t . TYPE SCROLL SENSITIVE , R e s u l t S e t . CONCUR UPDATABLE) ; R e s u l t S e t r s e t = stmt . executeQuery ( . . . ) ; r s e t . u p d a t e S t r i n g ( ”name” , ” S c h m i t t ” ) ; r s e t . updateRow ( ) ; Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 28 / 42 JDBC Metadaten JDBC - Zugriff auf Metadaten Allgemeine Metadaten • Klasse DatabaseMetaData zum Abfragen von DB-Informationen Informationen über ResultSets • JDBC bietet die Klasse ResultSetMetaData R e s u l t S e t s r s e t = stmt . executeQuery ( ” s e l e c t . . . ” ) ; R e s u l t S e t M e t a D a t a rsmd = r s e t . getMetaData ( ) ; • Abfragen von Spaltenanzahl, Spaltennamen und deren Typen i n t a n z a h l S p a l t e n = rsmd . getColumnCount ( ) ; S t r i n g s p a l t e n N a m e = rsmd . getColumnName ( 1 ) ; S t r i n g typeName = rsmd . getColumnTypeName ( 1 ) ; Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 29 / 42 JDBC Fehlerbehandlung JDBC - Fehlerbehandlung SQLException: • Spezifikation der Ausnahmen, die eine Methode werfen kann, steht bei ihrer Deklaration (throws Exception) • Wird Code in einem try-Block ausgeführt, werden im catch-Block Ausnahmen abgefangen. try { // c o d e . . . . . . } catch ( SQLException e ) { System . o u t . p r i n t l n ( ” Es i s t e i n F e h l e r a u f g e t r e t e n : ” ) ; System . o u t . p r i n t l n ( ”Msg : ”+ e . g e t M e s s a g e ( ) ) ; System . o u t . p r i n t l n ( ” SQLState : ”+ e . g e t S Q L S t a t e ( ) ) ; System . o u t . p r i n t l n ( ” E r r o r C o d e : ”+ e . g e t E r r o r C o d e ( ) ) ; // und zum debuggen noch g l e i c h dazu e . printStackTrace () ; } Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 30 / 42 JDBC Transaktionen Anwendungsprogrammierung: Transaktionen • Nicht nur eine einzelne SQL-Anweisung, sondern ganze Folge davon, je nach Anwendung. • Eine oder mehrere Anweisungen werden als Transaktion zusammengefasst bzw. betrachtet. Z.B. Abheben von Geld am Geldautomat. begin transaction operation1; operation2; operation3; operation4; operation5; end transaction Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 31 / 42 JDBC Transaktionen Wiederholung: Transaktion - Klassisches Beispiel Bei einer typischen Transaktion in einer Bankanwendung: 1. Lese den Kontostand von A in die Variable a: read(A, a); 2. Reduziere den Kontostand um 50 Euro: a := a − 50; 3. Schreibe den neuen Kontostand in die Datenbasis: write(A,a); 4. Lese den Kontostand von B in die Variable b: read(B, b); 5. Erhöhe den Kontostand um 50 Euro: b := b + 50; 6. Schreibe den neuen Kontostand in die Datenbasis: write(B, b); Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 32 / 42 JDBC Transaktionen Wiederholung: Transaktionen - ACID Atomicity (Atomarität) • Alles oder nichts Consistency • Konsistenter Zustand der DB → konsistenter Zustand Isolation • Jede Transaktion hat die DB “für sich allein” Durability (Dauerhaftigkeit) • Änderungen erfolgreicher Transaktionen dürfen nie verloren gehen. Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 33 / 42 JDBC Transaktionen Operationen auf Transaktione-Ebene • begin of transaction (BOT): Mit diesem Befehl wird der Beginn einer eine Transaktion darstellende Befehlsfolge gekennzeichnet. Ist implizit, bei Beginn der Befehlssequenz. • commit: Hierdurch wird die Beendigung der Transaktion eingeleitet. Alle Änderungen der Datenbasis werden durch diesen Befehl festgeschrieben, d.h. sie werden dauerhaft in die Datenbank eingebaut. • abort: Dieser Befehl führt zu einem Selbstabbruch der Transaktion. Das Datenbanksystem muss sicherstellen, dass die Datenbasis wieder in den Zustand zurückgesetzt wird, der vor Beginn der Transaktionsausführung existierte. Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 34 / 42 JDBC Transaktionen Wiederholung: Transaktionsverwaltung in SQL • commit [work]: Die in der Transaktion vollzogenen Änderungen werden falls keine Konsistenzverletzung oder andere Probleme aufgedeckt werden festgeschrieben. Das Schlüsselwort work ist optional, d.h. das Transaktionsende kann auch einfach mit commit “befohlen” werden. • rollback [work]: Alle Änderungen sollen zurückgesetzt werden. Anders als der commit-Befehl muss das DBMS die “erfolgreiche” Ausführung eines rollback-Befehls immer garantieren können. Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 35 / 42 JDBC Transaktionen Wiederholung: Der “non-repeatable read” Fehler Abhängigkeit von anderen Updates (non-repeatable read) Transaktion T1 Transaktion T2 select sum(Kontostand) from Konten update Konten set Kontostand=42000 where kontoId=12345 select sum(Kontostand) from Konten Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 36 / 42 JDBC Transaktionen Wiederholung: Das “Phantomproblem” Abhängigkeit von neuen/gelöschten Tupeln (Phantomproblem) Transaktion T1 Transaktion T2 select sum(Kontostand) from Konten insert into Konten values (C,1000,...) select sum(Kontostand) from Konten Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 37 / 42 JDBC Transaktionen JDBC - Transaktionen Transaktionen • Bei der Erzeugung eines Connection-Objekts ist (in der Regel) als Default der Modus autocommit eingestellt. D.h. nach jeder Aktion wird ein Commit ausgeführt. • Um Transaktionen als Folgen von Anweisungen abwickeln zu können, ist dieser Modus auszuschalten. conn . setAutoCommit ( f a l s e ) ; • Für eine Transaktion können sogenannte Konsistenzstufen (isolation levels) wie TRANSACTION SERIALIZEABLE, TRANSACTION REPEATABLE READ usw. eingestellt werden. conn . s e t T r a n s a c t i o n I s o l a t i o n ( C o n n e c t i o n . TRANSACTION SERIALIZABLE ) ; Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 38 / 42 JDBC Transaktionen JDBC - Transaktionen (2) Beendigung oder Zurücksetzung conn . commit ( ) ; bzw. conn . r o l l b a c k ( ) ; // o d e r : conn . r o l l b a c k ( s a v e p o i n t ) Sicherungspunkte (Savepoints) S a v e p o i n t s p = conn . s e t S a v e p o i n t ( ) ; // bzw . m i t Namen S a v e p o i n t namedSp = conn . s e t S a v e p o i n t ( ” mySavePoint ” ) ; Programm kann mit mehreren DBMS verbunden sein • Selektives Beenden/Zurücksetzen von Transaktionen pro DBMS • Kein global atomares Commit möglich Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 39 / 42 JDBC Transaktionen JDBC - Transaktionen: Beispiel http://www.tutorialspoint.com/jdbc/jdbc-transactions.htm try { // Assume a v a l i d c o n n e c t i o n o b j e c t conn conn . setAutoCommit ( f a l s e ) ; S t a t e m e n t s t m t = conn . c r e a t e S t a t e m e n t ( ) ; S t r i n g SQL = ”INSERT INTO E m p l o y e e s ” + ”VALUES ( 1 0 6 , 2 0 , ' R i t a ' , ' Tez ' ) ” ; s t m t . e x e c u t e U p d a t e (SQL) ; // Submit a m a l f o r m e d SQL s t a t e m e n t t h a t b r e a k s S t r i n g SQL = ”INSERTED IN E m p l o y e e s ” + ”VALUES ( 1 0 7 , 2 2 , ' S i t a ' , ' Singh ') ” ; s t m t . e x e c u t e U p d a t e (SQL) ; // I f t h e r e i s no e r r o r . conn . commit ( ) ; } catch ( SQLException se ) { // I f t h e r e i s any e r r o r . conn . r o l l b a c k ( ) ; } Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 40 / 42 JDBC Transaktionen JDBC - Transaktionen: Konsistenzsstufen Default in Postgresql-JDBC ist TRANSACTION READ COMMITTED http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 41 / 42 JDBC Transaktionen Anmerkung: SQL Injections SQL Anfragen wird in Anwendung erstellt, wobei id eine Benutzereingabe ist . . . . ”SELECT a u t h o r , s u b j e c t , t e x t ” + ”FROM a r t i k e l WHERE ID=” + i d Aufruf z.B. durch Webserver http://webserver/cgi-bin/find.cgi?ID=42 SQL Injection zum Ausspähen von Daten http://webserver/cgi-bin/find.cgi?ID=42+UNION+SELECT+ login,+password,+’x’+FROM+user Führt zur SQL Anweisung: select author, subject, text from artikel where ID=42 union select login, password, ’x’ from user; Und andere Fälle bis hin zum Einschleußen von beliebigen Code auf Rechner + öffnen einer Shell (abh. von DBMS) Hilfe u.a. durch Benutzen von PreparedStatements Übersicht unter: http://de.wikipedia.org/wiki/SQL-Injection Prof. Dr.-Ing. S. Michel TU Kaiserslautern Datenbankanwendung, WS 14/15 42 / 42
