pdf (1/1)

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