Blatt 11

Datenbanken
Musterlösung Übungsblatt 11
Julia Wolters
Abgabe: 06.07.2009
Aufgabe 41
1 −− Aufgabe 41
2
3 CREATE TABLE auftraege (
4
anfang TIMESTAMP NOT NULL ,
5
ende
TIMESTAMP ,
6
kunde VARCHAR ,
7
beschreibung VARCHAR ,
8
PRIMARY KEY ( anfang , kunde , beschreibung )
9 );
10
11 INSERT INTO auftraege VALUES ( ’2006 -12 -06 09:00 ’ , ’2006 -12 -06
Mueller ’ , ’Nikolausen ’ ) ;
12 INSERT INTO auftraege VALUES ( ’2006 -12 -02 15:00 ’ , ’2006 -12 -02
’ , ’Foo ’ ) ;
13 INSERT INTO auftraege VALUES ( ’2006 -12 -08 12:38 ’ , ’2006 -12 -08
’ , ’Bar ’ ) ;
14 INSERT INTO auftraege VALUES ( ’2006 -12 -04 12:38 ’ , ’2006 -12 -05
’ , ’Foobar2 ’ ) ;
15 INSERT INTO auftraege VALUES ( ’2006 -04 -28 23:30 ’ , ’2006 -05 -02
’ , ’Foobar3 ’ ) ;
16 INSERT INTO auftraege VALUES ( ’2006 -04 -20 12:00 ’ , ’2006 -12 -01
Muster AG ’ , ’Testen ’ ) ;
17
18 −− a ) Arbeitszeit pro Auftrag ausrechnen
19 DROP VIEW auftraege_dauer ;
20 CREATE VIEW auftraege_dauer AS
21
SELECT ∗ , ( ende − anfang ) AS dauer FROM auftraege ;
12:00 ’ , ’
15:32 ’ , ’Meier
22:05 ’ , ’Meier
11:11 ’ , ’Meier
10:00 ’ , ’Meier
12:00 ’ , ’
1
Übung WS 08 – 09
22
23
24
25
Datenbanken
Übungsblatt 11
Freitag, 10 – 12 Uhr
Musterlösung
SELECT ∗ FROM auftraege_dauer ;
SELECT EXTRACT ( MINUTE FROM dauer ) as minuten , EXTRACT ( HOUR FROM dauer ) as
stunden ,
( CASE
WHEN EXTRACT ( MINUTE FROM dauer ) = 0 THEN 0
WHEN EXTRACT ( MINUTE FROM dauer ) < 15 THEN 15
WHEN EXTRACT ( MINUTE FROM dauer ) < 30 THEN 30
WHEN EXTRACT ( MINUTE FROM dauer ) < 45 THEN 45
ELSE 60
END ) AS minuten15 FROM auftraege_dauer ;
26
27
28
29
30
31
32
33
34 SELECT kunde , beschreibung , dauer , dauer + ( (
35
( CASE
36
WHEN EXTRACT ( MINUTE FROM dauer ) = 0 THEN 0
37
WHEN EXTRACT ( MINUTE FROM dauer ) < 15 THEN 15
38
WHEN EXTRACT ( MINUTE FROM dauer ) < 30 THEN 30
39
WHEN EXTRACT ( MINUTE FROM dauer ) < 45 THEN 45
40
ELSE 60
41
END ) − EXTRACT ( MINUTE FROM dauer ) ) ∗ interval ’1 minute ’ ) AS dauer15
42
FROM auftraege_dauer ;
43
44 −− b ) Auftragsdauer für jeden Auftraggeber aufaddieren
45
46 SELECT kunde , sum ( dauer ) FROM auftraege_dauer GROUP BY kunde ;
47
48 −− c ) Kunden mit Aufträgen zwischen 0 1 . 0 5 . 2 0 0 6 und 3 0 . 1 1 . 2 0 0 6
49
50 SELECT DISTINCT kunde from auftraege
51
WHERE ende >= DATE ’2006 -05 -01 ’ AND anfang <= DATE ’2006 -11 -30 ’ ;
52
53
−− oder
54
55 SELECT DISTINCT kunde from auftraege
56
WHERE ( anfang , ende ) OVERLAPS ( DATE ’2006 -05 -01 ’ , DATE ’2006 -11 -30 ’ ) ;
57
58 −− d ) Aufträge zwischen 2 0 : 0 0 und 0 6 : 0 0 Uhr oder an Wochenenden
59
60 SELECT ∗ FROM auftraege_dauer WHERE
61
−− Zwischen 20 und 6 Uhr ?
62
( extract ( hour from anfang ) between 20 and 2 4 ) or
63
( extract ( hour from anfang ) between 0 and 6 ) or
64
( extract ( hour from ende ) between 20 and 2 4 ) or
65
( extract ( hour from ende ) between 0 and 6 ) or
66
( extract ( dow from anfang ) < extract ( dow from ende ) ) or
67
( dauer > INTERVAL ’1 day ’ )
68
−− Anfang am Wochenende ?
2
Julia Wolters
Freitag, 10 – 12 Uhr
Musterlösung
69
70
71
72
Datenbanken
Übungsblatt 11
Übung WS 08 – 09
extract ( dow from anfang ) = 0 or extract ( dow from anfang ) = 6
Ende am Wochenende ?
extract ( dow from ende ) = 0 or extract ( dow from ende ) = 6
Anfang und Ende in der Woche , aber in unterschiedlichen Wochen −
eigentlich unnötig
or (
extract ( dow from anfang ) between 1 and 5
and extract ( dow from ende ) between 1 and 5
and (
extract ( week from anfang ) < extract ( week from ende )
or extract ( year from anfang ) < extract ( year from ende ) ) ) ;
or
−−
or
−−
73
74
75
76
77
78 −− e ) Aufträge an denen gerade gearbeitet wird
79
80 SELECT ∗ FROM auftraege WHERE anfang < now ( ) AND ( now ( ) < ende OR ende IS
NULL ) ;
81
82 −− f ) Durchschnittliche Wochenarbeitszeit für abgeschlossene Aufträge
83
84 SELECT min ( anfang ) , max ( ende ) , max ( ende ) − min ( anfang ) AS zeitraum ,
85
extract ( days from ( max ( ende ) − min ( anfang ) ) ) as gesamttage ,
86
sum ( dauer ) AS gesamtdauer ,
87
( sum ( dauer ) / extract ( days from ( max ( ende ) − min ( anfang ) ) ) ) ∗ 7 AS
wochendauer
88
FROM auftraege_dauer
89
WHERE ende < now ( ) AND ende IS NOT NULL ;
Julia Wolters
3
■
■
■
)
(
!+
%
!*
#$
%
!'%
!"#$%
.
/
0
),
#
)
0
!!
■
0
)
!,,)-%
)
(
0
6,
#$7
,, !/##$%
&
' !###$%
/0&12+3'##$%
45631,,79)2+
)1,,5:$
4
■
.
/
;,
0
0
.
/
B
0
),
#
-;1,,A+,?"###
>?'%
0
,1
2
-;1,,?'/>?'%
0
4,"#$5
-;1,,-"
? ,,
>?'%
0
6,
#$7
-;1,,A+,?'###
>?'%
C* )2+D7E 1C3
CE 1)1A+,C$
,1
2
;<=>?'%
6,
#$7
;<=
>?%
41+@)1A79)2+
95:$
Freitag, 10 – 12 Uhr
Musterlösung
Julia Wolters
■
Datenbanken
Übungsblatt 11
)
-''#''.$%
&
'#$%
4,"#$5
(
!"###$%
&
'()*+,$%
,1
2
■
Übung WS 08 – 09
Freitag, 10 – 12 Uhr
Musterlösung
Datenbanken
Übungsblatt 11
Übung WS 08 – 09
Aufgabe 43
angestellter vorgesetzter
Schulz
Schulz
Meier
Schulz
Müller
Meier
Schmidt
Schulz
Tupelnikus
Müller
Relationica Tupelnikus
Tadagoliker
Müller
(a) Beim Löschen eines Tupels werden alle referenzierenden (nicht referenzierten) Tupel
auch gelöscht.
(b)
(1)
(2)
(3)
(2)
angestellter vorgesetzter
Schulz
Schulz
Meier
Schulz
Meier
Müller
Schmidt
Schulz
(
(
(
Müller
Tupelnikus
((
(
(
(
(
(((
Tupelnikus
Relationica
((((
(
(
(
(
Tadagoliker
Müller
((((
(
Aufgabe 44
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import java . sql . ∗ ;
import java . util . GregorianCalendar ;
import java . util . StringTokenizer ;
/∗ ∗
∗ D i e s e K l a s s e s t e l l t d i e Verbindung zu e i n e r SQL−Datenbank ü b e r JDBC h e r
und f ü h r t
∗ O pe ra t io ne n a u f e i n e r e v e n t u e l l noch zu e r z e u g e n d e n T a b e l l e ”
p e r s o n e n d a t e n ” durch . Die Daten
∗ d e r T a b e l l e entstammen einem Array { @ l i n k PersonenJDBC . p e r s o n s } von {
@ l i n k Person}−Obj ekte n ,
∗ d i e j e w e i l s d i e Daten b e z ü g l i c h e i n e r Person e n t h a l t e n .
∗
∗ @author Henrik Blunck
∗ @author Jörg Mensmann
∗ @version 1.1
∗/
Julia Wolters
5
Übung WS 08 – 09
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Datenbanken
Übungsblatt 11
Freitag, 10 – 12 Uhr
Musterlösung
p u b l i c c l a s s PersonenJDBC {
p u b l i c s t a t i c f i n a l String PSQL_DRIVER = "org. postgresql . Driver " ;
p u b l i c s t a t i c f i n a l String MYSQL_DRIVER = "com. mysql .jdbc. Driver " ;
private
private
private
private
Connection db ;
String dbName ;
String userName ;
String password ;
// Der Datenbestand , d e r i n d i e Datenbank i n t e g r i e r t werden s o l l
p r i v a t e Person [ ] persons = {
new Person ( " Jackson " , " Michael " , 1 7 3 2 3 4 5 , new GregorianCalendar
( 1 9 6 6 , 1 1 , 9 ) , Person . MALE ) ,
new Person ( " Bolton " , " Michael " , 5 3 2 2 3 4 5 , new GregorianCalendar
( 1 9 6 5 , 1 , 1 7 ) , Person . MALE ) ,
new Person ( " Gabriel " , "Peter " ,
4 6 3 2 3 4 5 , new GregorianCalendar
( 1 9 5 9 , 4 , 5 ) , Person . MALE ) ,
new Person ( " Jackson " , " Jennifer " , 2 3 2 3 3 4 5 , new GregorianCalendar
( 1 9 8 0 , 7 , 2 3 ) , Person . FEMALE ) ,
new Person ( " Hooker " , "John Lee" , 1 3 4 2 3 4 5 , new GregorianCalendar
( 1 9 1 6 , 3 , 7 ) , Person . MALE ) ,
new Person ( " Turner " , "Tina" ,
2 5 3 2 3 4 5 , new GregorianCalendar
( 1 9 5 8 , 1 , 1 7 ) , Person . FEMALE ) ,
new Person ( "Rush" ,
" Jennifer " , 6 1 3 2 3 4 5 , new GregorianCalendar
( 1 9 6 1 , 3 , 2 1 ) , Person . FEMALE )
};
p u b l i c PersonenJDBC ( ) {
}
/∗ ∗
∗ A b a r b e i t u n g d e r i n d e r A u f g a b e n s t e l l u n g angegebenen Anforderungen
∗/
p u b l i c v o i d doRequiredOperations ( ) {
// S t r i n g −V a r i a b l e , das j e w e i l s d i e SQL−S t a t e m e n t s a l s Z e i c h e n k e t t e
h ä l t
String sqlString ;
// Das R e s u l t S e t , das j e w e i l s d i e E r g e b n i s s e e i n z e l n e r Anfragen
h ä l t
ResultSet rs ;
b o o l e a n tableExists = t r u e ;
// Test , ob T a b e l l e mit Namen ” p e r s o n e n d a t e n ” b e r e i t s e x i s t i e r t
try {
// db . getMetaData ( ) l i e f e r t DatabaseMetaData−O b j e k t . g e t T a b l e s
(..) liefert
// R e s u l t S e t , d a s s a l l e T a b e l l e n e n t h ä l t , d i e d i e mit den durch
d i e Parametern
6
Julia Wolters
Freitag, 10 – 12 Uhr
Musterlösung
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
Datenbanken
Übungsblatt 11
Übung WS 08 – 09
// g e g e b e n e n K r i t e r i e n über einstim men . n e x t ( ) g i b t f a l s e zurück
, f a l l s kein
// n ä c h s t e s Element im R e s u l t S e t e x i s t i e r t e .
rs = db . getMetaData ( ) . getTables ( "" , "" , " personendaten " , n u l l ) ;
rs . beforeFirst ( ) ;
tableExists = rs . next ( ) ;
} c a t c h ( SQLException e ) {
reactOnSQLException ( e ) ;
}
// F a l l s T a b e l l e n n i c h t e x i s t i e r t e , e r z e u g e und f ü l l e s i e
i f ( ! tableExists ) {
// T a b e l l e n s c h e m a e r z e u g e n
try {
System . out . println ( " Erzeuge Tabellen ..." ) ;
constructPersonDatabase ( ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
}
// T a b e l l e f ü l l e n mit den Daten aus dem Person−Array p e r s o n s
try {
fillPersonDatabase ( ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// A u s l e s e n d e s gesamten D a t e n b e s t a n d e s d e r T a b e l l e ” p e r s o n e n d a t e n ”
und Ausgabe
try {
sqlString = " SELECT * FROM personendaten " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbank nach dem Einlesen des
Datenbestandes :" ) ;
printResultSet ( rs , 5 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// Löschen d e s Person−Arrays und Neuanlegen mit den den Daten aus
d e r Personen−Datenbank
try {
persons = n u l l ;
persons = extractPersonsFromDatabase ( ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
Julia Wolters
7
Übung WS 08 – 09
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
Datenbanken
Übungsblatt 11
Freitag, 10 – 12 Uhr
Musterlösung
// Ausgabe d e r O b j e k t e i n p e r s o n s [ ]
System . out . println ( " Ausgabe der Objekte in persons []:" ) ;
f o r ( i n t i = 0 ; i < persons . length ; i++) {
persons [ i ] . printPerson ( ) ;
}
// Anfrage an d i e Datenbank und Anzeigen i n d e r Konsole : Die
Anfrage g i b t Vor− und
// Nachnamen a l l e r Personen , deren Nachname mit ”J” b e g i n n t ,
g e o r d n e t nach
// a u f s t e i g e n d e n Nachnamen z u r ü c k .
try {
sqlString = " SELECT lastname , firstname FROM personendaten
WHERE " +
" lastname LIKE ’J%’ " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbestand selektiert mit:’ " + sqlString
+ "’" ) ;
printResultSet ( rs , 2 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// Anfrage an d i e Datenbank und Anzeigen i n d e r Konsole : Die
Anfrage g i b t Vor− und
// Nachnamen und G e b u r t s d a t e n a l l e r Personen , d i e v o r 1960 g e b o r e n
s i n d , g e o r d n e t nach
// a u f s t e i g e n d e n G e b u r t s t a g e n z u r ü c k .
try {
sqlString = " SELECT lastname , firstname , dayofbirth FROM" +
" personendaten WHERE dayofbirth < ’1960 1 1’ order " +
" by dayofbirth " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbestand selektiert mit:’ " + sqlString
+ "’" ) ;
printResultSet ( rs , 3 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// Anfrage an d i e Datenbank und Anzeigen i n d e r Konsole : Die
Anfrage g i b t Vor− und
// Nachnamen und G e b u r t s d a t e n a l l e r w e i b l i c h e n Personen , d i e
z w i s c h e n 1960 und 1970
// g e b o r e n s i n d , g e o r d n e t nach a u f s t e i g e n d e n G e b u r t s t a g e n z u r ü c k .
try {
8
Julia Wolters
Freitag, 10 – 12 Uhr
Musterlösung
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
Übung WS 08 – 09
sqlString = " SELECT lastname , firstname , dayofbirth FROM" +
" personendaten WHERE dayofbirth >= ’1960 1 1’ " +
"and dayofbirth < ’1970 1 1’ and gender = ’female ’" +
" order by dayofbirth " ;
rs = executeQuery ( sqlString ) ;
System . out . println ( " Datenbestand selektiert mit:’ " + sqlString
+ "’" ) ;
printResultSet ( rs , 3 ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
// E i n f ü g e n d e s genannten neuen E i n t r a g e s
try {
db . setAutoCommit ( f a l s e ) ;
sqlString = " DELETE FROM personendaten WHERE lastname = ’
Jackson ’" ;
executeUpdate ( sqlString ) ;
sqlString = " INSERT INTO personendaten values (’ Jackson ’, ’Five
’, 5, NULL , NULL)" ;
executeUpdate ( sqlString ) ;
db . commit ( ) ;
db . setAutoCommit ( t r u e ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
Datenbanken
Übungsblatt 11
// S c h l i e s s e n d e r Verbindung
try {
db . close ( ) ;
} c a t c h ( SQLException sqlException ) {
reactOnSQLException ( sqlException ) ;
}
}
/∗ ∗
∗ L e g t d i e T a b e l l e ” p e r s o n d e n d a t e n ” i n d e r Datenbank an
∗
∗ @throws SQLException
∗/
p u b l i c v o i d constructPersonDatabase ( ) throws SQLException {
String sqlString ;
Statement stmt ;
// Erzeugen d e r T a b e l l e
sqlString = " CREATE TABLE personendaten ( lastname VARCHAR (20) ," +
" firstname VARCHAR (20) , ssn integer , dayofbirth CHAR (12) ," +
Julia Wolters
9
Übung WS 08 – 09
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
Datenbanken
Übungsblatt 11
Freitag, 10 – 12 Uhr
Musterlösung
" gender CHAR (6) , PRIMARY KEY (ssn), CHECK " +
" ( gender in (’male ’,’female ’)));" ;
stmt = db . createStatement ( ) ;
stmt . execute ( sqlString ) ;
}
/∗ ∗
∗ Führt e i n e Anfrage an d i e v e rb un d en de Datenbank aus .
∗
∗ @param s q l S t r i n g Das a u s z u f ü h r e n d e SQL−S t a t e m e n t a l s S t r i n g .
∗ @return Das R e s u l t S e t , das das E r g b n i s d e r Anfrage an d i e Datenbank
e n t h ä l t .
∗ @throws SQLException
∗/
p u b l i c ResultSet executeQuery ( String sqlString ) throws SQLException {
Statement statement = db . createStatement ( ResultSet .
TYPE_SCROLL_INSENSITIVE ,
ResultSet . CONCUR_UPDATABLE
);
r e t u r n statement . executeQuery ( sqlString ) ;
}
/∗ ∗
∗ Führt e i n Update i n d e r verbundenden Datenbank aus .
∗
∗ @param s q l S t r i n g Das a u s z u f ü h r e n d e SQL−S t a t e m e n t a l s S t r i n g .
∗ @return Angabe ü b e r den E r f o l g d e s Updates
∗ @throws SQLException
∗/
p u b l i c i n t executeUpdate ( String sqlString ) throws SQLException {
Statement statement = db . createStatement ( ) ;
r e t u r n statement . executeUpdate ( sqlString ) ;
}
/∗ ∗
∗ F ü l l t d i e T a b e l l e ” p e r s o n e n d a t e n ” mit dem D a t e n b e s t a n d im
PersonenArray . Vorab werden
∗ d i e a l t e n T a b e l l e n i n h a l t e g e l ö s c h t .
∗
∗ @throws SQLException
∗/
p u b l i c v o i d fillPersonDatabase ( ) throws SQLException {
Statement stmt = db . createStatement ( ) ;
String sqlString = " DELETE FROM personendaten " ;
stmt . executeUpdate ( sqlString ) ;
f o r ( i n t i = 0 ; i < persons . length ; i++) {
sqlString = " INSERT INTO personendaten VALUES (’"
+ persons [ i ] . getLastname ( ) + "’, ’"
10
Julia Wolters
Freitag, 10 – 12 Uhr
Musterlösung
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
Datenbanken
Übungsblatt 11
Übung WS 08 – 09
+ persons [ i ] . getFirstname ( ) + "’, "
+ Integer . toString ( persons [ i ] . getSsn ( ) ) + ",’"
+ getSqlDateByJavaGregorianCalendar ( persons [ i ] .
getDayOfBirth ( ) ) + "’, ’"
+ persons [ i ] . getGender ( ) + " ’)" ;
stmt . executeUpdate ( sqlString ) ;
}
}
/∗ ∗
∗ K o n v e r t i e r t e i n O b j e k t vom Typ e i n O b j e k t vom Typ G r e g o r i a n C a l e n d a r
in
∗ e i n e n S t r i n g im SQL−Date−Format
∗
∗ @param c a l e n d a r
∗ @return e i n S t r i n g im SQL−Date−Format
∗/
p u b l i c s t a t i c String getSqlDateByJavaGregorianCalendar (
GregorianCalendar calendar ) {
String ret ;
ret = calendar . get ( java . util . Calendar . YEAR ) + " "
+ calendar . get ( java . util . Calendar . MONTH ) + " "
+ calendar . get ( java . util . Calendar . DAY_OF_MONTH ) ;
r e t u r n ret ;
}
/∗ ∗
∗ K o n v e r t i e r t e i n e n S t r i n g im SQL−Date−Format ( z .B. 1999 2 12) i n e i n
∗ O b j e k t vom Typ G r e g o r i a n C a l e n d a r .
∗
∗ @param s q l D a t e
∗
e i n S t r i n g im SQL−Date−Format
∗ @return e i n O b j e k t vom Typ G r e g o r i a n C a l e n d a r .
∗/
p u b l i c s t a t i c GregorianCalendar getJavaGreogorianCalendareBysqlDate (
String sqlDate ) {
i f ( sqlDate == n u l l ) r e t u r n new GregorianCalendar ( ) ;
else {
StringTokenizer tokenizer = new StringTokenizer ( sqlDate , " " ) ;
r e t u r n new GregorianCalendar ( Integer . parseInt ( tokenizer .
nextToken ( ) ) ,
Integer . parseInt ( tokenizer .
nextToken ( ) ) , Integer
. parseInt ( tokenizer . nextToken ( ) ) ) ;
}
}
/∗ ∗
Julia Wolters
11
Übung WS 08 – 09
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
Datenbanken
Übungsblatt 11
Freitag, 10 – 12 Uhr
Musterlösung
∗ E r z e u g t aus dem I n h a l t d e r T a b e l l e ” p e r s o n e n d a t e n ” d e r Datenbank e i n
Array von I n s t a n z e n
∗ d e r K l a s s e Person . Ruft e x t r a c t P e r s o n s F r o m R e s u l t S e t ( ) a u f .
∗
∗ @return @throws SQLException
∗/
p u b l i c Person [ ] extractPersonsFromDatabase ( ) throws SQLException {
Statement statement = db . createStatement ( ResultSet .
TYPE_SCROLL_INSENSITIVE ,
ResultSet . CONCUR_UPDATABLE
);
String sqlString = " SELECT * FROM personendaten " ;
ResultSet rs = statement . executeQuery ( sqlString ) ;
Person [ ] ret = extractPersonsFromResultSet ( rs ) ;
r e t u r n ret ;
}
/∗ ∗
∗ E r z e u g t aus einem R e s u l t S e t −O b j e k t e i n Array von I n s t a n z e n d e r
Klasse
∗ Person . Wird von e x t r a c t P e r s o n s F r o m D a t a b a s e ( ) a u f g e r u f e n .
∗
∗ @param r s Das R e s u l t S e t , aus dem d i e Personendaten zu e x t r a h i e r e n
sind .
∗ @return Ein Array von Person−Obj ekte n , das d i e e x t r a h i e r t e n Daten
∗
e n t h ä l t .
∗ @throws SQLException
∗/
p u b l i c s t a t i c Person [ ] extractPersonsFromResultSet ( ResultSet rs )
throws SQLException {
i n t count = 0 ;
rs . beforeFirst ( ) ;
w h i l e ( ! rs . isLast ( ) ) {
rs . next ( ) ;
count++;
}
Person [ ] ret = new Person [ count ] ;
rs . beforeFirst ( ) ;
count = −1;
w h i l e ( ! rs . isLast ( ) ) {
rs . next ( ) ;
count++;
b o o l e a n female ;
i f ( rs . getString ( " gender " ) != n u l l && rs . getString ( " gender " ) .
equalsIgnoreCase ( Person . FEMALE_STRING ) ) {
female = t r u e ;
} else {
12
Julia Wolters
Freitag, 10 – 12 Uhr
Musterlösung
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
Datenbanken
Übungsblatt 11
Übung WS 08 – 09
female = f a l s e ;
}
Person person = new Person ( rs . getString ( " lastname " ) , rs .
getString ( " firstname " ) , rs . getInt ( "ssn" ) ,
getJavaGreogorianCalendareBysqlDate ( rs . getString ( " dayofbirth
" ) ) , female ) ;
ret [ count ] = person ;
}
r e t u r n ret ;
}
/∗ ∗
∗ Lö s c h t a l l e Tupel aus d e r T a b e l l e ” p e r s o n e n d a t e n ” d e r Datenbank .
∗
∗ @throws SQLException
∗/
p u b l i c v o i d clearPersonDatabase ( ) throws SQLException {
Statement stmt = db . createStatement ( ) ;
String sqlString = " DELETE FROM personendaten " ;
stmt . executeUpdate ( sqlString ) ;
}
/∗ ∗
∗ @ d e p r i c a t e d H e r s t e l l e n d e r Verbindung z u r Datenbank und S e t z e n d e r
die
∗
Verbindung c h a r a k t i e r i s i e r e n d e n A t t r i b u t e d e s
aufrufenden
∗
Objektes
∗ @param i n D r i v e r
∗
Der Name d e s T r e i b e r s . E n t s p r i c h t dem a b s o l u t e n
Klassenpfad
∗
der T r e i b e r k l a s s e
∗ @throws ClassNotFoundException
∗
f a l l s e i n f a l s c h e r T r e i b e r s p e z i f i z i e r t wurde
∗ @throws SQLException
∗
f a l l s beim Ansprechen d e r Datenbank F e h l e r von d i e s e r
∗
g e m e l d e t werden
∗/
p u b l i c v o i d establishAndConnect ( String inDriver )
throws ClassNotFoundException , SQLException {
Class . forName ( inDriver ) ;
db = DriverManager . getConnection ( dbName , userName , password ) ;
}
/∗ ∗
∗ G i b t e i n R e s u l t S e t i n T a b e l l e n f o r m a u f den B i l d s c h i r m aus .
∗
∗ @param r s Das R e s u l t S e t , das a u s g e g e b e n werden s o l l
Julia Wolters
13
Übung WS 08 – 09
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
Datenbanken
Übungsblatt 11
Freitag, 10 – 12 Uhr
Musterlösung
∗ @param numOfColumns
∗
Die Anzahl d e r A t t r i b u t w e r t e ( d . h . S p a l t e n ) d e r
anzuzeigenden
∗
Tupel ( d . h . Z e i l e n )
∗ @throws SQLException
∗/
p u b l i c s t a t i c v o i d printResultSet ( ResultSet rs , i n t numOfColumns )
throws SQLException {
rs . beforeFirst ( ) ;
w h i l e ( ! rs . isLast ( ) && rs . next ( ) ) {
f o r ( i n t i = 1 ; i <= numOfColumns ; i++) {
System . out . print ( rs . getString ( i ) + " " ) ;
}
System . out . println ( ) ;
}
}
/∗ ∗
∗ Methode z u r Ausnahmebehandlung während d e s Versuches , mit d e r
Datenbank
∗ zu kommunizieren .
∗
∗ @param s q l E x c e p t i o n Die g e w o r f e n e Ausnahme
∗/
p r i v a t e v o i d reactOnSQLException ( SQLException sqlException ) {
b o o l e a n isFirstException = t r u e ;
w h i l e ( isFirstException | | ( sqlException . getNextException ( ) != n u l l
)) {
System . out
. println ( " Während der Arbeit mit der Datenbank trat
folgender Fehler auf: "
+ sqlException . toString ( )
+ " Code:"
+ sqlException . getErrorCode ( ) ) ;
System . out . println ( "Die Verbindung verblieb in folgendem Status
: "
+ sqlException . getSQLState ( ) ) ;
isFirstException = f a l s e ;
}
}
/∗ ∗
∗ @param a r g s
∗/
p u b l i c s t a t i c v o i d main ( String args [ ] ) {
PersonenJDBC personenJDBC = new PersonenJDBC ( ) ;
// B e r e i t s t e l l e n d e r E i n g a b e p a r a m e t e r f ü r d i e Verbindung mit d e r
Datenbank ; p r ü f t
14
Julia Wolters
Freitag, 10 – 12 Uhr
Musterlösung
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
Datenbanken
Übungsblatt 11
Übung WS 08 – 09
// vorab , ob e i n Passwort a l s A u f r u f p a r a m e t e r ü b e r g e b e n wurde
i f ( args . length == 3 ) {
personenJDBC . dbName = args [ 0 ] ;
personenJDBC . userName = args [ 1 ] ;
personenJDBC . password = args [ 2 ] ;
} e l s e i f ( args . length == 2 ) {
personenJDBC . dbName = args [ 0 ] ;
personenJDBC . userName = args [ 1 ] ;
// F a l l s k e i n Passwort ü b e r g e b e n wurde , s e t z e das Passwort a l s
leeren String
personenJDBC . password = "" ;
} else {
// F a l l s d i e P a r a m e t e r a n z a h l n i c h t s i n n v o l l i s t , beende das
Programm
// mit F e h l e r m e l d u n g
System . err
. println ( " Benutzung : java PersonenJDBC <dbname > <dbusername
> "
+ "[< dbuserpasswd >] \n" ) ;
System . err
. println ( " Beispiel : java PersonenJDBC jdbc: postgresql ://
padme .uni - muenster .de/ mustermanndb mustermann }\n" +
"
oder java PersonenJDBC jdbc: mysql :// cpc.uni muenster .de/ mustermanndb mustermann " ) ;
System . exit ( 1 ) ;
}
try {
i f ( personenJDBC . dbName . startsWith ( "jdbc: postgresql :" ) ) {
System . out . println ( " Benutze PostgreSQL ." ) ;
personenJDBC . establishAndConnect ( PersonenJDBC . PSQL_DRIVER ) ;
} e l s e i f ( personenJDBC . dbName . startsWith ( "jdbc: mysql :" ) ) {
System . out . println ( " Benutze MySQL ." ) ;
personenJDBC . establishAndConnect ( PersonenJDBC . MYSQL_DRIVER )
;
} else {
System . err . println ( " Unbekanntes Datenbankssystem .\n" ) ;
System . exit ( 1 ) ;
}
} c a t c h ( java . lang . ClassNotFoundException e ) {
System . out
. println ( " Verbindung mit der Datenbank fehlgeschlagen :
Treiber nicht gefunden . "
+ e . toString ( ) ) ;
} c a t c h ( SQLException sqlException ) {
personenJDBC . reactOnSQLException ( sqlException ) ;
}
Julia Wolters
15
Übung WS 08 – 09
438
439
440
441
442
Datenbanken
Übungsblatt 11
Freitag, 10 – 12 Uhr
Musterlösung
// Führt d i e i n d e r A u f g a b e n s t e l l u n g genannten Anforderungen aus .
personenJDBC . doRequiredOperations ( ) ;
}
}
16
Julia Wolters