1 / 66

Sprachschnittstellen von Datenbanksystemen

Sprachschnittstellen von Datenbanksystemen. Prof. Klaus Gremminger Fachhochschule Karlsruhe Fachbereich Informatik Moltkestraße 30 76133 Karlsruhe email: gremminger@fh-karlsruhe.de. Inhalt. Structured Query Language2 (SQL92) Java Database Connectivity (JDBC)

clinton-galloway
Download Presentation

Sprachschnittstellen von Datenbanksystemen

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SprachschnittstellenvonDatenbanksystemen Prof. Klaus Gremminger Fachhochschule Karlsruhe Fachbereich Informatik Moltkestraße 30 76133 Karlsruhe email: gremminger@fh-karlsruhe.de

  2. Inhalt • Structured Query Language2 (SQL92) • Java Database Connectivity (JDBC) • Einbettung von SQL in Java (SQLJ) • Structured Query Language3 (SQL99)

  3. Historie • SEQUEL als Anfragesprache des Prototyps System R der Firma IBM (Structured English Query Language) • 1981: SQL/Data System als kommerzielles System • Heute weit verbreitet IBM, Microsoft, Oracle, Sybase • Wachsende Popularität erforderte Standardisierung • 1986: ISO-SQL1 (auch SQL86 genannt) • 1989: Addendum-1 SQL89 • 1992: ISO-SQL92 (auch SQL2 genannt, ISO 9075) • 2000: SQL99 (ISO 9085)

  4. Implementierungsmethoden • Direktes SQLAusführen von SQL-Anweisungen von einem interaktiven Terminal aus • ModulspracheBeschreibung von SQL-Anweisungen in reinem SQL in Form getrennt übersetztem Modul • Call Language Interface (CLI)Aufruf von SQL-Anweisungen der SQL-Implementierung, welche wiederum das DBS mittels dynamischem SQL aufrufen • Embedded SQLEinbettung von SQL-Anweisungen in Hostsprache mit Vorübersetzung (Pre-Compiler)

  5. Structured Query Language2 (SQL92) • Bestandteile der Sprache • Datendefinition (DDL) • Datenmanipulation (DML) • Datenkontrolle (DCL) • Die weiteren Ausführungen basieren auf SQL92. Neu: • Zusätzliche Datentypen • Auf-/Abbau von Client-Server-Verbindungen • Unterstützung von dynamischem SQL • Scrollbare Cursor • Outer-Joins • Intersect- und Minus-Mengenoperationen

  6. Datentypen • integer (oder auch integer4) • smallint (oder auch integer2) • numeric(p,q) • float(p) • character(n), character varying(n) oder varchar(n) • bit(n), bit varying(n) • date, time, timestamp • blob (raw), clob (binary oder character large object)

  7. Datendefinition • Schemadefinition • Tabellendefinition • Integritätsbedingungen • Sichten

  8. Schemadefinition • SQL-Objekte können u.a. seinDomain-, Tabellen-,View-, Rechte-, Bedingungs-Definition • Benannte Sammlung von SQL-Objekten innerhalb eines Katalogs heißt Schema • Benannte Sammlung von SQL-Schemata innerhalb einer SQL-Umgebung heißt Katalog • CREATE SCHEMA[schema-name][AUTHORIZATION user-name][DEFAULT CHARACTER SET character-set][list-of-schema-objects]

  9. Tabellendefinition • Anlegen einer neuen Tabelle:CREATE TABLE Professoren(PersNr INTEGER NOT NULL, Name VARCHAR(10) NOT NULL, Rang CHAR(2) ); • Hinzufügen neuer Spalten:ALTER TABLE Professoren ADD (Raum INTEGER); • Verändern des Wertebereichs einer Spalte:ALTER TABLE Professoren MODIFY (Name VARCHAR(20));

  10. Integritätsbedingungen • Attribut-Bedingung: UNIQUE, NOT UNIQUE • Primärschlüssel-DefinitionCREATE TABLE Studenten(MatrNr INTEGER PRIMARY KEY...);CREATE TABLE hören(MatrNr ..., VorlNr ..., PRIMARY KEY(MatrNr, VorlNr)); • Fremdschlüssel-DefinitiongelesenVon REFERENCES Professoren ON DELETE SET NULLCONSTRAINT Ticket_fk FOREIGN KEY(Nation, PassNr) REF... • Check-KlauselCREATE TABLE prüfen... Note NUMBER(2,1) CHECK(Note BETWEEN 0.7 AND 5.0),

  11. Sichten • Virtuelle Tabellen • Berechnung der „Tupel“ zur Laufzeit • Zur Anpassung an spezielle Benutzerbedürfnisse und zum Datenschutz • Zum Verbergen von komplexen Datenstrukturen(z.B. Primär-Fremdschlüssel-Beziehungen) • Beispiel: • CREATE VIEW Prüfungen ASSELECT MatrNr, VorlNr, PersNrFROM prüfen; • SELECT *FROM Prüfungen;

  12. Datenmanipulation • Einfügen von Zeilen:INSERT INTO ProfessorenVALUES(1,‘Gremminger‘,‘C3‘,309);INSERT INTO Professoren (Name, PersNr)VALUES (‘Gmeiner‘,2); • Verändern von Zeilen:UPDATE ProfessorenSET Rang = ‘C3‘ WHERE Name = ‘Gremminger‘; • Löschen von Zeilen:DELETE FROM Vorlesungen;DELETE FROM Vorlesungen WHERE gelesenVon = 1;

  13. Die SELECT-Anweisung • Erstellt eine Ergebnismenge in Form einer Tabelle • Select [All | Distinct] {(Pseudo-) Spaltenliste | *} [Spaltenalias] • From Tabellenliste [Tabellenalias] • [Where Bedingungen auf Zeilen] • [Group By Gruppen] • [Having Bedingungen auf Gruppen] • [{Union | Intersect | Minus} Abfrage] • [Order By Sortierspalten] • Abarbeitung • Bilde das kartesische Produkt der Tabellen in der From-Klausel • Wähle hieraus die Spalten der Tupel aus, die in der Select-Klausel aufgeführt sind

  14. Operatoren und (Aggregat-) Funktionen • Arithmetische Operatoren: +,-,*./ • Zeichenketten-Operatoren: || • Funktionen • to_number, to_char, to_date • nvl, decode • count, distinct, max, min, sum, avg • Beispiele: • SELECT Nation || PassNrFROM FlugGast; • SELECTCOUNT(DISTINCT name)FROM Stadt;

  15. WHERE-Klausel • Auswahlbedingungen auf Zeilen • Vergleichsoperatoren • =,<>,>,,>=,<= • LIKE • BETWEEN • AND • [NOT] IN • IS [NOT] NULL • Beispiele: • ... WHERE FlzTypus = ‘747‘ OR Hersteller LIKE ‘B%‘ • ... WHERE FlugGes IN (‘LH‘, ‘BA‘,‘TA‘);

  16. ORDER BY-Klausel • Geordnete Anzeige der Anfrageergebnisse • Beispiele: • SELECT PLZ, Name, EinwohnerFROM StadtORDER BY PLZ ASC; • SELECT Semester, Name, AnschriftFROM StudentORDER BY Semester DESC, Name ASC;

  17. Zusammenfassung von Tupeln, welche in einer ausgewählten Attributkombination den gleichen Wert aufweisen. • Dieser Wert erscheint in der Basistabelle in mehreren Zeilen, in der gruppierten Ergebnistabelle nur noch einmal. • In der SELECT-Klausel darf projeziert werden auf • Attribute, auf die gruppiert wird • Gruppenfunktionen, angewendet auf andere Attribute der Basistabelle • Konstante • Beispiel: SELECT gelesenVon, SUM( SWS )FROM VorlesungenGROUP BY gelesenVon; GROUP BY-Klausel

  18. HAVING-Klausel • Ähnlich der WHERE-Klausel können an die mit GROUP BY gebildeten Gruppen zusätzliche Bedingungen gestellt werden • Beipiele: • SELECT gelesenVon, SUM( SWS)FROM VorlesungenGROUP BY gelesenVonHAVINGAVG( SWS ) > 3; • SELECT Name, SUM( SWS)FROM Vorlesungen, ProfessorenWHERE gelesenVon = PersNrGROUP BY NameHAVINGAVG( SWS ) > 3;

  19. Geschachtelte Abfragen (subqueries) • Bisher: Festlegung der WHERE-Klausel mit einer (Menge von) Konstanten • „Zu welchen Flugcoupons gibt es keine Flugtickets?“ • SELECT AusstGes, AusstNrFROM FlCoupon; • SELECT AusstGes, AusstNrFROM FlTicketWHERE AusstGes <> ... AND AusstNr <> ... ; • Jetzt: Definition einer kompletten SQL-Anfrage mit Rückgabe der Ergebnismenge als Vergleichswerte an die äußere WHERE-Klausel

  20. Geschachtelte Abfragen (Forts.) • Beispiele: • SELECT AusstGes, AusstNrFROM FlTicketWHERE (AusstGes, AusstNr) NOT IN (SELECT AusstGes, AusstNr FROM FlCoupon ); • SELECT NameFROM ProfessorenWHERE PersNr NOT IN (SELECT gelesenVonFROM Vorlesungen);

  21. Der Existenzquantor EXISTS • Liefert TRUE, wenn eine Unterabfrage keine leere Ergebnismenge zurückgibt, sonst FALSE • Beispiel: „Professoren, die keine Vorlesungen halten“ SELECT NameFROM ProfessorenWHERE NOT EXISTS (SELECT *FROM VorlesungenWHERE gelesenVon = PersNr );

  22. Mengen-Operationen • Verknüpfung der Ergebnisse von SELECT-Anfragen • Operationen: INTERSECT, UNION, MINUS • Beispiel:„Flugplätze, auf denen Flüge starten und landen“ SELECT AbFlPlatz FROM FlStrecke WHERE AbFlPlatz IS NOT NULL INTERSECT SELECT AnFlPlatz FROM FlStrecke WHERE AnFlPlatz IS NOT NULL;

  23. Verbindung mehrerer Tabellen (JOIN) • Grundlage: Primär-Fremdschlüssel-Beziehungen • CROSS, NATURAL, INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, UNION-JOIN • Beispiele: • SELECT G.FlugGes ‘Kürzel‘, Name ‘Gesellschaft‘, AbFlPlatz ‘Start‘, AnFlPlatz ‘Ziel‘FROM FlGes G INNER JOIN FlStrecke SON G.FlugGes = S. FlugGes; • SELECT P.PersNr, P.Name, V.TitelFROM Professoren P LEFT OUTER JOINVorlesung V ON P.PersNr = V.gelesenVon;

  24. Rechte • Regeln den Zugang (Authentifizierung) und den Zugriff auf die Objekte in der Datenbank (Autorisierung) • Beispiele: • GRANTCONNECT, RESOURCETO EmilIDENTIFIED BY lime; • GRANTSELECTON FlCoupon, FlGastTO Max, Moritz: • REVOKEUPDATE( Preis )ON FlTicketTO Max;

  25. JavaDataBaseConnectivity (JDBC) Allgemeine Datenbank-Zugriffsschnittstelle für SQL (aktuelle Version 2.1) Übertragung der JAVA-Portabilität auf Datenbanken Teil von Java 2 (JDK1.2, Package java.sql) Basiert auf X/Open SQL CLI (und damit ODBC) Übersichtlicher und einfacher benutzbar als ODBC Unterstützt ANSI SQL92-Basisfunktionalität Erlaubt dynamische SQL-Anweisungen

  26. Beziehungen zwischen den Interfaces executeQuery DriverManager Statement ResultSet getMoreResults DriverManager Statement ResultSet createStatement executeQuery getConnection execute prepareStatement getXXX Connection PreparedStatement Connection PreparedStatement setXXX prepareCall Datentypen: Date, Time, getXXX Datentypen: Date, Time, commit, rollback CallableStatement TimeStamp, Numeric, CallableStatement TimeStamp, Numeric, Java Datentypen, etc. Java Datentypen, etc. Funktionen Unterklasse weitere Methoden

  27. Das JDBC API java.sql.DriverManager java.sql.Connection java.sql.Statement java.sql.ResultSet java.sql.SQLException java.sql.Date, java.sql.Timestamp java.sql.DatabaseMetaData,java.sql.ResultSetMetaData

  28. Abbildung SQL- auf Java-Datentypen

  29. Java Database Connectivity (JDBC) Java Applikation JDBC API JDBC Manager JDBC Treiber API JDBC Net Treiber für Treiber A ... Treiber B Treiber JDBC-ODBC Bridge Treiber für ODBC und DB JDBC Implementierungs- alternativen Öffentliches Datenbankabhängige Zugriffsprotokolle Protokoll

  30. JDBC-Treiber-Implementierung • JDBC-ODBC-Bridge (Typ 1) • Nutzen vorhandener ODBC-Treiber • Wegen Binärcode keine Eignung für Web-Anwendungen • Native-API-Treiber (Typ 2) • Basiert auf Bibliotheken des DBMS-Herstellers • Wegen Binärcode keine Eignung für Web-Anwendungen • JDBC-Net-Treiber (Typ 3) • Aufteilung Treiber in JDBC-Client und JDBC-Server • JDBC-Client kann vollständig in Java realisiert werden • Native-Protokoll-Treiber (Typ 4) • Direkte Übersetzung der JDBC-Aufrufe in DBS-Aufrufe • Client direkt mit Datenbankserver verbunden • Nachteilig sind die i.A. proprietären DBS-Protokolle

  31. Ablauf einer JDBC-Anwendung Aufbau einer Verbindung zur Datenbank Explizites Laden eines Datenbanktreibers Automatisches Laden eines Datenbanktreibers (sql.drivers) Verbindungsaufbau mit UserID und Passwort Senden einer SQL-Anweisung Erzeugung einer SQL-Anweisung Verarbeiten der Anfrageergebnisse Über Resultset-Objekt nach dem Cursor-Prinzip

  32. Aufbau einer Verbindung zur Datenbank import java.net.URL; import java.sql.*; class JDBCExample { public static void main (String args[]) { try { // jdbc-odbc-bridge Treiber laden Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”); String url = “jdbc:odbc:thin:@nero:4711:demoDb”; Connection con = DriverManager.getConnection (url,‘userid‘, ‘password‘); } } }

  33. Senden einer SQL-Anweisung // Suche eines Kunden String stmtSelect = “SELECT KundenNr FROM Kunden “+ “WHERE Nachname = ‘Gremminger’; “; // Anweisung erzeugen Statement stmt = con.createStatement( ); // Select -Anweisung ausführen resultSet rs = stmt.executeQuery( stmtSelect ); // SQL-Anweisungen ohne Resultset // Tabelle erzeugen stmt.executeUpdate( stmtcreate ); // Datensatz in Tabelle einfügen int rowcount = stmt.executeUpdate( stmtInsert)

  34. Verarbeiten der Anfrageergebnisse // Zeilenweiser Zugriff auf Ergebnismenge, Cursor zeigt vor 1. Zeile while (rs.next () ) { // Spaltenweiser Zugriff auf die aktuelle Zeile mittels // getXXX-Methoden und Index der Spalte oder Spaltennamen String s = rs.getString (1); double d = rs.getDouble (2); int i = rs.getInt (3); System.out.println (s + ‘ ‘ + d + ‘ ‘ + i); } // Freigabe der benutzten Ressourcen rs.close;

  35. Fehlerbearbeitung // Fehler werden als Ausnahmen (Exceptions) der Klasse SQLException // signalisiert try { // Aufruf von JDBC-Anweisungen, die Exception generieren } catch (SQLException exc) { System.out.println (‘SQLException: ‘ + exc.getMessage () ); }

  36. Verbindungen und Transaktionen Es sind mehrere Verbindungen zu einer oder mehreren Datenbanken möglich (con1, con2, ... conn) Auswahl des Datenbankbereichs über Tripel Katalogname, Schemaname, Objektname(con.setCatalog( ... )) Transaktionssteuerung erfolgt auf aktiver Verbindung Commit/Rollback (con.rollback( )) AutoCommit (con.setAutoCommit( false ) Lesetransaktion (con.setReadOnly( true )) Isolationlevel(con.setTransactionIsolationlevel( Transaction_Serializable ))

  37. Connection Pooling Reduktion der Zeiten für DB-Verbindungsaufbau/-abbau Ein Connection Pool ist ein Puffer von DB-Verbindungen Logische und physische DB-Verbindungen Instanzen von java.sql.Connection und java.sql.PooledConnection PooledConnection pcon = datastore.getPooledConnection ();Connection con = pcon.getConnection ();con.close; Vorteilhaft für mehrstufige Systemarchitekturen Web-Applikation mit Servlets und JDBC Applikationsserver für EJB

  38. Gespeicherte Prozeduren Ausführung von in der Datenbank gespeicherten Prozeduren Form: (call ProcedureName( Par1, Par2, ... ) Eingabeparameter müssen vor Ausführung der Anweisung initialisiert werden Ausgabeparameter müssen vor Ausführung der Anweisung registriert werden Rückgabe der Werte mit get-Operationen:getString, getDouble, getDate, getTimestamp, ...

  39. Programmierung Stored Procedures // Anweisung zum Aufruf der Prozedur HoleKundenNr String stmtCall = “(call HoleKundenNr(?,?))”; // Call vorbereiten stmt = con.prepareCall( stmtCall ); // IN-Parameter setzen stmt.setString( 1, ‘Gremminger’ ); // Registrieren 2. Platzhalter als OUT-Parameter stmt.RegisterOutParameter( 2, java.sql.types.INTEGER ); // Anweisung ausführen stmt.executeQuery( ); int Kundennummer = stmt.getInt( 2 );

  40. Dynamische SQL-Anweisungen Ausführung von nicht vorübersetzten SQL-Anweisungen Die SQL-Anweisung enthält als Platzhalter das Zeichen “?” Platzhalter werden mittels set-Operationen und Bezug auf eine Position gesetzt:setString, setByte, setShort, setFloat, ...

  41. Programmierung dynamischer SQL PreparedStatement stmt = null; // Such-Anweisung String stmtSelect = “SELECT KundenNr, Nachname “+ “FROM Kunden “+ “WHERE Nachname = ?; “; // Select-Anweisung vorbereiten stmt = con.prepareStatement( stmtSelect ); // Gesuchten Nachnamen einsetzen stmt.setString( 1, “Gremminger” ); // Select-Anweisung ausführen ResultSet rset = stmt.executeQuery( );

  42. Einbettung von SQL in Java (SQLJ) • Statische SQL-Anweisungen werden in den Java-Quelltext eingebettet • SQL-Anweisungen werden zur Übersetzungszeit definiert • Einzige Variabilität sind Parameter • Übersetzung durch Präcompiler (Translator) in Aufrufe des SQLJ-Laufzeitsystems und SQLJ-Profile • SQLJ-Laufzeitsystem (Package sqlj.runtime) benutzt i.d.R. JDBC-Aufrufe • Translator führt syntaktische und semantische Überprüfungen durch

  43. Java-Quelltext mit SQLJ Codierung Java-Quelltext SQLJ-Profil Translator Java-Compiler Bytecode SQLJ-Laufzeitsystem JDBC Programmierprinzip SQLJ Code .sqlj .java Java Code .class JavaByte Code Run Program

  44. SQLJ-Konventionen • SQLJ-Quelltexte besitzen die Endung “.sqlj“ • Anweisungen beginnen mit “#sql“ und werden in geschweifte Klammern gesetzt #sql { INSERT INTO book VALUES (‘3-932588-13-4‘, ‘Oracle 8‘, 1, 3, 68.00); } • Anweisungen werden mit “;“ abgeschlossen • Verbindung zur Datenbank mittels JDBC-Mechanismen • Es ist eine Default-Verbindung zu definieren

  45. Host-Variablen • In allen Embedded SQL-Lösungen • Sind Variablen der Hostsprache • Werden mit ":“ gekennzeichnet String findBook (String isbn) throws SQLException { String title; #sql { SELECT title INTO :title FROM book WHERE isbn = :isbn }; return title; } • Dienen der Datenkommunikation zwischen SQL und der Host-Programmiersprache (IN, OUT, INOUT) • Es sind Host-Ausdrücke erlaubt#sql price { VALUES (compute_price (:IN orderNo) ) };

  46. Cursor Datenbank Anwendungsprogramm SQL Java Cursor-Konzept • Zugriff auf Mengen von Ergebnistupeln • In SQLJ Iteratoren • Vergleichbar Resultsets von JDBC • Spaltentypen zur Über-setzungszeit festgelegt • Arten • benannter Iterator • Positionsiterator

  47. Namens-Iterator • Spalten werden über Namen gebunden #sql public iterator PersIter (string Name, int Jahr); PersIter iter; String Name = null; int Jahr = 0; #sql iter ={SELECT Name, Jahr FROM Personen} while (iter.next()) { Name = iter.name(); Jahr = iter.Jahr(); } iter.close(); DeklarationIterator-Klasse DeklarationIterator-Variable InstanziierungIterator-Objekt Iterator-Methoden FreigebenRessourcen

  48. Positions-Iterator • Spalten werden über Position gebunden #sql public iterator PersIter (string, int); PersIter iter; String Name = null; int Jahr = 0; #sql iter ={SELECT Name, Jahr FROM Personen} while (true) { #sql {FETCH :iter INTO :Name, :Jahr}; if (iter.endFetch()) break; } iter.close(); Auslesen überHost-Variablen

  49. Transaktionssteuerung • Basiert auf den JDBC-Mechanismen • Manuelle Kontrolle#sql { COMMIT }; • Festlegung des Zugriffsmodus#sql { SET TRANSACTION READ ONLY }; • Festlegung des Isolationlevels#sql { SET TRANSACTION READ WRITE ISOLATIONLEVEL SERIALIZABLE };

  50. Kontexte • Verbindungskontext beschreibt Datenbankverbindung • Pro Verbindung ein Kontext-ObjektConnection con = DriverManager.getConnection (url, uid, pwd);DefaultContext ctx = new DefaultContext( con );DefaultContext.setDefaultContext( ctx ); • Umschaltung auf KontextDefaultContext.setDefaultContext( ctx1 );#sql { SQL-Operations };alternativ: #sql [ctx1] { SQL-Operation }; #sql [ctx2] { SQL-Operation }; • Ausführungskontextctx.getExecutionContext().setQueryTimeout( 5 );#sql [ctx] { SQL-Operation };

More Related