Übungsblatt 7 - Lösung - Informatik

Universität Augsburg, Institut für Informatik
Dr. W.-T. Balke
M. Endres, A. Huhn, T. Preisinger
WS 2006/2007
11. Dez. 2006
Lösungsblatt 7
Datenbanksysteme I
Aufgabe 2 + 3:
import java.sql.*;
import oracle.jdbc.driver.*;
import java.text.*;
// fuer SimpleDateFormat
// Fuer die Datenbankverbindung muessen USER und PWD
// entsprechend Ihrer Matrikelnummer angepasst werden
//
//
//
//
//
Mit
javac -cp .;Oracle10gJDBCjar.jar DBConnection.java
koennen Sie das Programm uebersetzen und mit
java -cp .;Oracle10gJDBCjar.jar DBConnection
ausfuehren
public class myDBConnection {
// Statement und Connection
private Statement stmt;
private Connection con;
// USER und PWD muessen Ihrem Login angepasst werden
private String USER="USER";
private String PWD="PWD";
private String URL="jdbc:oracle:thin:@info-dbis-srv1."
+ "informatik.uni-augsburg.de:1521:dbs1";
// *** Aufgabe 2a
// ctor
public myDBConnection() throws SQLException {
// Laden des Oracle-Treibers
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver());
// Datenbankverbindung oeffnen
con = DriverManager.getConnection(URL,USER,PWD);
}
// Datenbankverbindung schlieszen
public void close() throws SQLException {
con.close();
}
1
// *** Aufgabe 2b
// erstellen der Relationen
public void createRelations() throws SQLException {
// Vorstellung
String v = "create table vorstellung("
+ "Name varchar(100) primary key,"
+ "Datum Date not null, Uhrzeit Timestamp not null)";
// Buchung
// Als Tabellenname wurde ’kbuchung’
// anstelle von ’Buchung’ verwendet
String b = "create table kbuchung(Name varchar(100) "
+ "primary key references vorstellung,"
+ "Sitzplaetze_Max integer not null, "
+ "Sitzplaetze_Gebucht integer default 0)";
// zyklischer Fremdschluessel
String z = "alter table vorstellung add constraint VBFK"
+ " foreign key (Name) references kbuchung(name)"
+ " DEFERRABLE";
try {
// Transaktion, AutoCommit auf false setzen
con.setAutoCommit(false);
stmt = con.createStatement();
stmt.executeUpdate(v);
stmt.executeUpdate(b);
stmt.executeUpdate(z);
con.commit();
// Transaktion beenden
} catch (SQLException e) {
con.rollback(); // im Fehlerfall rollback
throw e;
// Exception
}
finally {
con.setAutoCommit(true);
stmt.close();
}
}
2
// Aufgabe 2c
// Einfuegen der Tupel in die Relationen
// java.sql.Date unterscheidet sich von java.util.Date
public void insertValues(String Name, java.sql.Date Datum,
java.sql.Time Uhrzeit, int Sitzplaetze_Max,
int Sitzplaetze_Gebucht)
throws SQLException {
// Statement fuer ’insert into Vorstellung’
// Verwendung von PreparedStatement um
// das Datum als Date verarbeiten zu koennen
PreparedStatement insV =
con.prepareStatement("insert into "
+ "vorstellung values( ? , ? , ?)" );
insV.setString(1,Name);
insV.setDate(2,Datum);
insV.setTime(3,Uhrzeit);
// Statement fuer ’insert into Buchung’
// PreparedStatement nicht notwendig
String insB = "insert into kbuchung values(’"
+ Name + "’," + Sitzplaetze_Max + ","
+ Sitzplaetze_Gebucht + ")";
try {
// Transaktion
con.setAutoCommit(false);
stmt = con.createStatement();
// Constraints auf deferred setzen,
//also bis zum commit hinauszoegern
stmt.executeUpdate("SET CONSTRAINTS VBFK DEFERRED");
// PreparedStatement ausfuehren
insV.executeUpdate();
stmt.executeUpdate(insB);
// Constraints muessen ab jetzt wieder beachtet werden
stmt.executeUpdate("SET CONSTRAINTS VBFK IMMEDIATE");
con.commit();
// Transaktion beenden
} catch (SQLException e) {
con.rollback();
// bei Exception rollback
throw e;
}
finally { // zum Schlusz noch ein wenig aufraeumen
con.setAutoCommit(true);
stmt.close();
}
}
3
// Aufgabe 2d
// Loeschen einer Vorstellung aus den Relationen
public void deleteShow(String name) throws SQLException {
String delV = "delete from vorstellung where name=’"
+ name + "’";
String delB = "delete from kbuchung where name=’"
+ name + "’";
// Transaktion wichtig !
try {
con.setAutoCommit(false); // autocommit false
stmt = con.createStatement();
// zunaechst die Constraints ’ausschalten’
stmt.executeUpdate("SET CONSTRAINTS VBFK DEFERRED");
stmt.executeUpdate(delB);
stmt.executeUpdate(delV);
// Constraints muessen ab jetzt wieder beachtet werden
stmt.executeUpdate("SET CONSTRAINTS VBFK IMMEDIATE");
con.commit();
// Transaktion committen
} catch (SQLException e) {
con.rollback();
// bei Fehlerfall rollback
throw e;
//
} finally { // zum Schluss ...
con.setAutoCommit(true);
stmt.close();
}
}
4
// Aufgabe 3a
// Alle Vorstellungsnamen zurueckgeben
public String[] getShowNames() throws SQLException {
String q = "select name from vorstellung";
ResultSet rs;
// createStatement mit
// TYPE_SCROLL_INSENSITIVE und CONCUR_READ_ONLY
// Mit TYPE_SCROLL_INSENSITIVE kann durch
// das ResultSet ’gesprungen’ werden
// wird gebraucht um die Anzahl der
// Vorstellungen zu ermitteln
// Auch moeglich: select count(*) ...
// Man koennte statt String[] natuerlich
// auch Vector oder aehnliches verwenden
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(q);
System.out.println("FetchSize = " + rs.getFetchSize());
// Anzahl der Zeilen ermitteln
rs.last();
String names[] = new String[rs.getRow()];
// wieder an den Anfang springen,
// sonst kann man das ResultSet nicht durchlaufen
rs.beforeFirst();
int c=0;
while(rs.next())
names[c++] = rs.getString("name");
// und wieder aufraeumen
rs.close();
stmt.close();
return names;
}
5
// Aufgabe 3b
// Anzahl der freien Plaetze ermitteln
public int getFreeSeats(String name) throws SQLException {
ResultSet rs;
// freie Plaetze = max. Anzahl Plaetze - gebuchte Plaetze
String s = "select Sitzplaetze_Max, Sitzplaetze_Gebucht " +
"from kbuchung where name=’" + name + "’";
int seats_max = 0; int seats_booked = 0;
stmt = con.createStatement();
// Query absetzen
rs = stmt.executeQuery(s);
// falls es ein Ergebnis gibt
if(rs.next()) {
seats_max = rs.getInt("Sitzplaetze_Max");
seats_booked = rs.getInt("Sitzplaetze_Gebucht");
}
// das uebliche
rs.close();
stmt.close();
return seats_max - seats_booked;
}
// Aufgabe 3c
// Sitzplaetze buchen
public boolean bookSeats(String v_name, int nr)
throws SQLException {
try {
con.setAutoCommit(false);
// falls noch genuegend Plaetze frei sind
if(getFreeSeats(v_name) < nr) {
return false;
}
// else
// update statement
String s = "update kbuchung set Sitzplaetze_Gebucht = "
+ nr
+ " + (select Sitzplaetze_Gebucht"
+ " from kbuchung where name = ’" + v_name + "’)"
+ " where name = ’" + v_name + "’";
stmt = con.createStatement();
6
stmt.executeUpdate(s);
con.commit();
} catch (SQLException e) {
con.rollback();
throw e;
} finally {
con.setAutoCommit(true);
stmt.close();
}
return true;
}
// Aufgabe 3d
// Datum und Zeit
// als Rueckgabetyp verwenden wir Object,
// da ein Date (Datum) und ein String (Uhrzeit)
// zurueckgegeben wird
// Eine innere Klasse mit oeffentlichen Attributen
// Date und Timestamp koennte hier auch als
// Rueckgabetyp Verwendung finden
public Object[] getDateAndTime(String name) throws SQLException {
ResultSet rs;
String s = "select datum, uhrzeit from "
+ "vorstellung where name=’" + name + "’";
Object data[] = new Object[2];
stmt = con.createStatement();
rs = stmt.executeQuery(s);
if(rs.next()) {
data[0] = rs.getDate("datum");
data[1] = rs.getTime("uhrzeit");
}
// aufraeumen
rs.close();
stmt.close();
return data;
}
// Anzahl der gebuchten Sitzplaetze auf 0 setzen
public boolean setBookedSeats2Zero(String name)
throws SQLException {
try {
7
stmt = con.createStatement();
// SQL Statement
String del = "update kbuchung set " +
"Sitzplaetze_Gebucht = 0 where name=’" + name + "’";
// wenn nicht genau ein Tupel
// geaendert wurde ist was schief gegangen
if(stmt.executeUpdate(del) != 1)
return false;
return true;
} catch (SQLException e) {
con.rollback();
throw e;
} finally {
// aufraeumen
stmt.close();
}
}
// main
public static void main(String args[]) {
try {
// Instanz erzeugen
myDBConnection dbcon = new myDBConnection();
// *** Aufgabe 2e
// erstellen der Relationen
// wenn was schiefgeht wird eine Exception geworfen
dbcon.createRelations();
// einfuegen der Tupel
// Date(int,int,int) deprecated
// aber fuer diese kleine Anwendung reichts noch
// Achtung: In Java werden die Monate von 0 bis 11 gezaehlt,
// die Jahre seit 1900 und die Tage ab 1
// GregorianCalender waere fuer das Datum besser geeignet
dbcon.insertValues("Schneewittchen",
new java.sql.Date(105,11,23),
new java.sql.Time(15,0,0), 80, 23);
dbcon.insertValues("Aschenputtel",
new java.sql.Date(106,0,6),
new java.sql.Time(19,0,0), 100, 20);
dbcon.insertValues("Dornroeschen",
new java.sql.Date(106,0,11),
new java.sql.Time(17,0,0), 60, 30);
dbcon.insertValues("DB1-Klausur",
8
new java.sql.Date(107,1,9),
new java.sql.Time(15,0,0), 255, 0);
// Aufgabe 3a
// alle Vorstellungsnamen holen
String[] s = dbcon.getShowNames();
// ausgeben
System.out.println("Namen der Vorstellungen:");
for(int i=0;i<s.length;i++)
System.out.println(s[i]);
// Aufgabe 3b
// Anzahl der freien Sitzplaetze
// fuer die Aschenputtel-Vorstellung
System.out.println("\n#freie Sitzplaetze "
+ "fuer Aschenputtel:"
+ dbcon.getFreeSeats("Aschenputtel"));
// Aufgabe 3c
if(!dbcon.bookSeats("Dornroeschen",2))
System.out.println("Anzahl freier "
+ "Sitzplaetze ueberschritten");
Object data[] = dbcon.getDateAndTime("DB1-Klausur");
// das richtige Format fuer das Datum
SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy");
// Ausgabe von Datum und Uhrzeit
System.out.println("\nDatum DB1-Klausur: " +
df.format((java.sql.Date)data[0])
+ " Uhrzeit = "
+ data[1].toString());
} catch(SQLException e) {
System.out.println("error: " + e.getMessage());
e.printStackTrace(); }
}
}
9