Übungsblatt 8 - Lösung - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. A. Huhn, F. Wenzel, M. Endres
WS 2009/2010
18. Dez. 2009
Lösungsblatt 8
Datenbanksysteme I
Aufgabe 1: JDBC 2.0 und Scrollable Result Sets
Lösungen unter Berücksichtigung bereits implementierter Methoden aus Übungsblatt 7:
a) Methodenaufruf zur Erstellung der new product Relation:
executeStatement("CREATE TABLE new_product AS SELECT * FROM s_product");
b) Methode, die ein Scrollable Result Set zurückliefert, das wenn möglich updatebar ist:
private ResultSet executeJDBC2Statement(String statement){
try {
Statement stmt =
this.openConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE); return stmt.executeQuery(statement);
}
catch (SQLException e) {
e.printStackTrace();
return null;
}
}
Methodenaufruf und Bearbeitung des Result Sets um die unverbindliche Preisempfehlung zu erhöhen:
ResultSet rs = executeJDBC2Statement("SELECT id, name,
suggested_whlsl_price FROM new_product");
try {
rs.last();
float price = rs.getFloat("suggested_whlsl_price");
rs.updateFloat("suggested_whlsl_price", price+10);
rs.updateRow();
rs.close();
}
catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Update Result: ");
executeQuery("SELECT * FROM new_product");
1
c) Einfügen eines neuen Produkteintrags mittels Cursor:
rs = executeJDBC2Statement("SELECT id, name, short_Desc,
suggested_whlsl_price, whlsl_units FROM new_product");
try {
rs.last();
int id = rs.getInt("id");
rs.moveToInsertRow();
rs.updateString("id", new Integer(id+1).toString());
rs.updateString("name", "SpiderPig Protection Gear");
rs.updateString("short_Desc", "keeps your pig save");
rs.updateFloat("suggested_whlsl_price", (float) 11.99);
rs.updateString("whlsl_units", "100");
rs.insertRow();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Insert Result: ");
executeQuery("SELECT * FROM new_product");
d) Löschen des vierten Tupels aus der Relation:
rs = executeJDBC2Statement("SELECT id, name, short_Desc,
suggested_whlsl_price, whlsl_units FROM new_product");
try {
rs.absolute(4);
rs.deleteRow();
rs.close();
}
catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Delete Result: ");
executeQuery("SELECT * FROM new_product");
e) Result Set, das alle Produktnamen und Ids enthält, die bereits in einer Bestellung vorkamen:
rs = executeJDBC2Statement("SELECT p.id, p.name FROM s_product p,
s_item i WHERE p.id = i.product_id");
try {
System.out.println("Scrollable Result Set with Join updatable? "
+ (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
rs.close();
}
catch (SQLException e) {
e.printStackTrace();
}
Das Result Set ist nicht updatebar, da es sich nicht nur auf eine Relation bezieht, sondern einen Join enthält. Ein
Update könnte somit nicht eindeutig den Basisrelationen zugeordnet werden. Folgende Erklärung bestätigt die
Beobachtung: In some situations, a driver may need to choose an alternate result set type or concurrency type at
statement execution time. For example, a SELECT statement that contains a join over multiple tables might produce
a result set that is not updatable.[. . .] The driver will then choose an appropriate result set type and/or concurrency
type [. . .].
Aus: http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/resultset.html
2
f) Intuitive Definition eines Result Sets, das alle Tupel der Relation enthält:
rs = executeJDBC2Statement("SELECT * FROM new_product");
try {
System.out.println("Result Set with SELECT * updatable? "+
(rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
rs.close();
}
catch (SQLException e) {
e.printStackTrace();
}
Dieses Result Set ist nicht updatebar, wie dem Test auf rs.getConcurrency() und der angegebenen Oracle JDBC
Dokumentation zu entnehmen ist. Als Workaround gibt die Dokumentation folgende Lösung an:
rs = executeJDBC2Statement("SELECT p.* FROM new_product p");
try {
System.out.println("Workaround SELECT p.* updatable? "
+ (rs.getConcurrency()==ResultSet.CONCUR_UPDATABLE));
rs.close();
}
catch (SQLException e) {
e.printStackTrace();
}
Aufgabe 2: Erstellen einer Multimedia-Datenbank
Grundlage sind wiederum bereits implementierte Methoden aus Übunsblatt 7 (siehe Lösung 7):
a) siehe Methode private boolean establishConnection()
b) Methodenaufruf der bereits implementierten Methode executeStatement mit entsprechender DDL:
executeStatement("create table Papers("
+ "id integer primary key,"
+ "title varchar(100),"
+ "pdf blob)");
c) Neue Methode, die Batch Updates ausführt:
private void insertValues() throws SQLException {
try{
//set auto-commit to false for batch updates
this.openConnection.setAutoCommit(false);
Statement stmt = this.openConnection.createStatement();
// add inserts to batch
// initialize blobs as EMPTY_BLOB()
stmt.addBatch("insert into Papers values(1,’ECTS-GRUNDSÄTZE’ ,
EMPTY_BLOB())");
stmt.addBatch("insert into Papers values(2,’PRINCIPALES
CARACTERISTIQUS DE L´ECTS’ , EMPTY_BLOB())");
stmt.addBatch("insert into Papers values(3,’CARACTERÍSTICAS
PRINCIPALES DE LOS ECTS’ , EMPTY_BLOB())");
// execute batch and commit
stmt.executeBatch();
this.openConnection.commit();
stmt.close();
}
3
catch (BatchUpdateException e) {
// in case of batch update error -> rollback
this.openConnection.rollback();
throw e;
}
catch (SQLException e) {
// in case of SQL error -> rollback
this.openConnection.rollback();
throw e;
}
finally {
// set auto-commit to true and close statement
this.openConnection.setAutoCommit(true);
}
}
Zur Ausführung der Batch-Updates wird Auto-Commit deaktiviert. Anschließend werden die Werte eingefügt und
eventuell auftretende Fehler mit einem Rollback rückgängig gemacht.
d) Erstellen der Stored Procedure wie in Aufgabenblatt 7 via Methodenaufruf von executeStatement():
String procedure = new String("create procedure INSERT_PDF(idi integer, pdfi
blob) as begin "
+ "update Papers set pdf = pdfi "
+ "where id = idi; end INSERT_PDF;");
executeStatement(procedure);
e) Methode zum Einfügen der Pdfs:
private void insertData(Integer id, String pdf) throws Exception {
try {
File pdfFile= new File(pdf);
FileInputStream pdfFis = new FileInputStream(pdfFile);
// prepare call for the stored procedure
CallableStatement cstmt = this.openConnection.prepareCall("{CALL
INSERT_PDF(?,?)}");
cstmt.setInt(1,id);
// setting the id
cstmt.setBinaryStream(2, pdfFis, (int)pdfFile.length()); //setting the PDF
// execute
cstmt.executeUpdate();
// closing file input stream
pdfFis.close();
cstmt.close();
}
catch(Exception e){
throw e;
}
}
Die Methode ist mit den entsprechenden Pfadangaben als Parameter pdf aufzurufen. Sind die Dateien direkt im
Eclipse Projekt eingebunden, so lautet der entsprechende Methodenaufruf:
insertData(1, "key_de.pdf");
insertData(2, "key_fr.pdf");
insertData(3, "key_es.pdf");
4
Ob das Einfügen der Blobs erfolgreich war, kann mittels SQL-Developer mit folgendem Statement überprüft werden, welches die Dateigrößen der Blobs anzeigt:
SELECT ID, DBMS_LOB.GETLENGTH(PDF) FROM Papers;
5