Übungsblatt 7 - 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
11. Dez. 2009
Lösungsblatt 7
Datenbanksysteme I
Aufgabe 1: Basisoperationen mit JDBC
Lösung:
a) Das erstellte Programm muss den JDBC-Treiber sowie das java.sql Paket via Import einbinden:
import java.sql.*;
import oracle.jdbc.driver.*;
Dazu muss die zum Treiber gehörige jar-Datei als externes Archiv in das Eclipse Projekt importiert werden.
• Herstellen der Verbindung zur Datenbank (analog zur Ausführung im Skript):
private String
dbUrl="jdbc:oracle:thin:@lynx.informatik.uni-augsburg.de:1521:DB1";
private String login;
private String pwd;
private Connection openConnection;
private boolean establishConnection(){
//load driver
try{
DriverManager.registerDriver(new OracleDriver());
this.openConnection = DriverManager.getConnection(dbUrl,login,pwd);
return !this.openConnection.isClosed();
}
catch(SQLException e){
e.printStackTrace();
return false;
}
}
Die Strings login und pwd müssen natürlich mit dem entsprechenden Benutzernamen und Passwort noch an
einer Stelle in der Klasse initialisiert werden.
• Erstellen einer Relation:
private boolean executeStatement(String statement){
Statement stmt;
try {
stmt = this.openConnection.createStatement();
stmt.executeUpdate(statement);
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
Die Methode nimmt ein SQL-Statement entgegen, welches keine Rückgabewerte liefert (CREATE, DELETE, INSERT, UPDATE) und reicht dieses an die Datenbank weiter. Tritt dabei ein Fehler auf, so wird der
entsprechende Fehlerverlauf auf der Konsole ausgegeben.
1
• Einfügen von Tupeln in die neu erstellte Relation: siehe executeStatement
Da die bereits vorgestellte Methode alle Statements ohne Rückgabewert verarbeitet, kann sie auch zum
Einfügen von Tupeln verwendet werden.
• Stellen einer Query und Ausgabe der Ergebnisse:
private boolean executeQuery(String query){
Statement stmt;
try {
stmt = this.openConnection.createStatement();
ResultSet result = stmt.executeQuery(query);
displayResults(result);
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
private void displayResults(ResultSet result){
try {
int cols = result.getMetaData().getColumnCount();
while(result.next()){
String row = new String(" | ");
for(int i=1;i<=cols;i++){
row = row.concat(result.getString(i)+ " | ");
}
System.out.println(row);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
Die erste der beiden Methoden dient dazu, eine Query an die Datenbank zu stellen, die ein Ergebnis zurückliefert.
Dieses wird in einem ResultSet gespeichert. Die Ausgabe dieses Ergebnisses erfolgt über die Methode displayResults. Das ResultSet wird dabei Tupel für Tupel abgearbeitet und jedes Attribut einer Zeile einzeln
ausgelesen. Ist der Spaltenname bekannt, so kann eine Spalte auch direkt über den dazugehörigen Namen
angesprochen werden. Im vorliegenden Fall ist aber nichts über den Aufbau der Relation bekannt, lediglich
die Anzahl der Attribute kann über MetaData ausgelesen werden.
• Löschen einer Relation: siehe executeStatement. Schließen der Verbindung:
private boolean closeConnection(){
try{
this.openConnection.close();
this.openConnection = null;
return true;
}
catch(SQLException e){
e.printStackTrace();
return false;
}
}
Da das Löschen wiederum eine Operation ohne Rückgabewert darstellt, können DELETE Anweisungen mit
der vorgestellten Methode an die Datenbank weitergegeben werden.
2
b) Sofern ihr Programm sorgfältig implementiert wurde und Sie im Zweifelsfall entsprechende Warnmeldungen
von Eclipse beachtet haben, sollte der Java-Code jeglichen Zugriff auf die Datenbank in einem try-catch-Block
ausführen, da bei einem fehlerhaften Zugriff eine SQLException von Java geworfen wird. Die Aktion im Falle
eines Fehlers, also die Implementierung des catch-Blocks bleibt dabei dem Anwendungsentwickler überlassen. So
kann z.B. der Fehlerverlauf (stack trace) zu Fehlerbehebungszwecken auf Java-Ebene oder genauere Informationen
auf SQL-Ebene via getMessage ausgegeben werden.
Aufgabe 2: JDBC Fehlerbehandlung und Meta Data
Lösung:
• Teilaufgaben a) und b) bauen auf den Methoden von Aufgabe 1 auf. Zur Lösung dieser Aufgaben sind folgende
Methodenaufrufe notwendig:
String table1 = new String("CREATE TABLE new_region(id VARCHAR(2) PRIMARY
KEY,name VARCHAR(26))");
executeStatement(table1);
String table2 = new String("CREATE TABLE new_dept(id VARCHAR(2) PRIMARY KEY,
name VARCHAR(20),region_id VARCHAR(2),
CONSTRAINT new_dept_fk FOREIGN KEY(region_id)
REFERENCES new_region(id))");
executeStatement(table2);
String insert1= new String("INSERT INTO new_region(id,name)
SELECT id, name FROM s_region");
executeStatement(insert1);
String insert2= new String("INSERT INTO new_dept(id, name,region_id)
SELECT id, name, region_id FROM s_dept");
executeStatement(insert2);
• Löschen eines Tupels mit Ausgabe auftretender SQL-Fehler:
private void deleteSavely(String statement){
try{
executeSecureStatement(statement);
}
catch(SQLException e){
System.out.println(e.getMessage());
System.out.println("DELETE omited!");
}
}
private void executeSecureStatement(String statement) throws SQLException{
Statement stmt = this.openConnection.createStatement();
stmt.executeUpdate(statement);
}
Im Gegensatz zu executeStatement behandelt die vorgestellte Methode executeSecureStatement auftretende Fehler nicht, sondern gibt diese weiter. Somit müssen Fehler beim Aufruf von executeSecureStatement abgefangen
werden. Im vorliegenden Java-Code wird die SQL-Fehlermeldung via getMessage ausgegeben, zusammen mit
einer vordefinierten Zusatzinformation.
3
• Ausgabe des Benutzernamens und verwendeten JDBC-Treibers über die Methode getMetaData des offenen
Connection Objekts:
try {
DatabaseMetaData dbMeta = this.openConnection.getMetaData();
System.out.println("Database user: "+ dbMeta.getUserName());
System.out.println("JDBC driver version: "+dbMeta.getDriverName()
+ " "+dbMeta.getDriverVersion());
}
catch (SQLException e) {
e.printStackTrace();
}
• Attribute und zugehöriger Datentyp der new region Relation lassen sich über den Aufruf von getMetaData eines
entsprechenden ResultSets ausgeben:
ResultSet queryResult = getResult("SELECT * FROM new_region");
if (queryResult != null){
try {
ResultSetMetaData resultMeta = queryResult.getMetaData();
System.out.println("new_region relation contains " +
resultMeta.getColumnCount() +" columns:");
for(int i=1;i<=resultMeta.getColumnCount();i++){
System.out.println(resultMeta.getColumnName(i) + " : " +
resultMeta.getColumnTypeName(i));
}
}
catch (SQLException e) {
e.printStackTrace();
}
}
Aufgabe 3: Stored Procedures und Prepared Statements
Lösung:
a) Zur Lösung dieses Aufgabenteils sind folgende Teillösungen erforderlich, die nur aus Methodenaufrufen der bereits
in den Aufgaben 1 und 2 implementierten Methoden bestehen:
• Methodenaufruf zum Erstellen einer temporären Tabelle:
executeStatement("CREATE GLOBAL TEMPORARY TABLE tmp_orders(id
VARCHAR(3) PRIMARY KEY, sum NUMERIC(11,2)) on COMMIT preserve rows");
• Methodenaufruf zum Erstellen der Relation new ord:
executeStatement("CREATE TABLE new_ord AS SELECT * FROM s_ord");
• Methodenaufruf zum Erstellen der Stored Procedure:
String procedure = new String("CREATE OR REPLACE PROCEDURE CUSTOMER_TOTAL
AS BEGIN INSERT INTO tmp_orders SELECT DISTINCT customer_id, sum(total)
FROM new_ord GROUP BY customer_id; END CUSTOMER_TOTAL;");
executeStatement(procedure);
4
b) Methode zum Aufruf der Stored Procedure:
private void callProcedure(String procedureName){
try {
CallableStatement procedureCall =
this.openConnection.prepareCall("{CALL "+procedureName+"}");
procedureCall.execute();
}
catch (SQLException e) {
e.printStackTrace();
}
}
Methodenaufrufe zur Ausführung der Stored Procedure und Ausgabe der Ergebnisse aus der temporären Tabelle:
callProcedure("CUSTOMER_TOTAL");
executeQuery("SELECT * FROM tmp_orders");
c) Methodenaufruf, um alle Produktinformationen zurückzugeben, deren Name ’Weight’ oder ’Boot’ enthält:
executeQuery("SELECT * FROM s_product WHERE name LIKE
’%Weight%’ OR name LIKE’%Boot%’");
d) Methode zum Erstellen eines Prepared Statements, um beliebige Substrings im Produktnamen zu finden:
private void runPreparedStatement(String search){
try {
PreparedStatement prodSearch =
this.openConnection.prepareStatement("SELECT * FROM s_product
WHERE name LIKE ?");
prodSearch.setString(1, search);
ResultSet result = prodSearch.executeQuery();
displayResults(result);
} catch (SQLException e) {
e.printStackTrace();
}
}
Hier wird der String der LIKE Bedingung als Parameter definiert, der später vom Benutzer via übergebenem
Methodenargument gesetzt werden kann. Der dazugehörige Methodenaufruf für einen beliebig initialisierten String
search lautet:
runPreparedStatement("%"+search+"%");
Die Prozentzeichen dienen dabei als Platzhalter, so dass der Produktname nicht exakt dem definierten String entsprechen muss, sondern auch andere Vorkommen des gesuchten Strings berücksichtigt werden.
5