Baze de Date - Limbajul SQL-Intero gări complexe, Vederi- Universitatea din Craiova,

1. Baze de Date -Limbajul SQL-Interogări complexe, Vederi- Universitatea din Craiova, Facultatea de Automatica, Calculatoare si Electronica

2. Vederi • O vedere în SQL este creată utilizând comada CREATE VIEW. Pentru a defini o vedere, trebuie să specificăm numele vederii v și interogarea <expresie interogare>, al cărei rezultat va fi salvat în vedere . • create view v as <expresie interogare> • Observație: exemplele din acest subcapitol folosesc baza de date Banca din subcapitolul 4.3.

3. Vederi • Exemplu: Să considerăm vederea care constă din numele filialelor și numele clienților care au fie conturi, fie credite la aceeași filială. • create view clienti as • (select Credit.numefiliala,Client.codclient from Client,Credit,Imprumuta • where ((Client.codclient=Imprumuta.codclient) and(Credit.nrcredit=Imprumuta.nrcredit ))) • union • (select Cont.numefiliala, Client.codclient from Client,Cont, Depune • where ((Client.codclient=Depune.codclient) and (Cont.nrcont=Depune.nrcont )))

4. Vederi • Numele atributelor unei vederi pot fi specificate explicit: • create view SumaTotalaCredite(numefiliala, totalcredite) as • select numefiliala, sum(suma) • from Credit • group by numefiliala

5. Vederi • Vederile se mai numesc și tabele virtuale și pot apărea în orice loc în care apar și tabelele. • Folosind vederea precedentă, putem afișa valoarea totală a creditelor de la filiala ING1. • select totalcredite • from SumaTotalaCredite • where numefiliala = 'ING1‘ • Dacă dorim să ștergem o vedere, se folosește comanda DROP VIEW: • drop view totalcredite

6. Modificarea bazei de date • Comenzile de modificare a bazei de date INSERT, UPDATE, DELETE au fost descrise în capitolul 4.1. • În acest subcapitol, se vor exemplifica forme mai complexe ale acestor comenzi.

7. Modificarea bazei de date - DELETE • Exemple ale operației DELETE: • 1.Să se șteargă toate conturile de la filiala ING1. • delete from Cont • where numefiliala = 'ING1' • 2.Să se șteargă toate creditele cu valori între 2500 și 3000 • delete from Credit • where suma between 2500 and 3000 • 3.Să se șteargă toate conturile de le fiecare filială cu locația în Craiova. • delete from Cont • where numefiliala in (select numefiliala • from Filiala • where oras = 'Craiova')

8. Modificarea bazei de date - DELETE • Comanda DELETE poate conține subinterogări. • De exemplu, dacă dorim să ștergem conturile care au balanța sub media balantelor băncii, putem scrie: • delete from Cont • where balanta < (select avg (balanta) • from Cont) • Comanda DELETE testează mai întâi dacă fiecare tuplu din relația Cont îndeplinește condiția de a avea balanța mai mică decât media. Apoi toate tuplurile care îndeplinesc această condiție sunt șterse.

9. Modificarea bazei de date - INSERT • Pentru a insera date într-o relație, se specifică tuplul sau o interogare al cărei rezultat este o mulțime de tupluri. • Exemplu: • Dacă dorim să oferim un cadou de 200 ron tuturor clientilor care au împrumuturi la filiala ING1. Numărul de credit va servi ca și număr de cont. • insert into Cont • select nrcredit, numefiliala, 200 • from Credit • where numefiliala = 'ING1'

10. Modificarea bazei de date - INSERT • După ce în tabela Cont am adăugat câte un tuplu nou pentru fiecare număr credit (care a servit ca și număr de cont)de la filiala ING1 și o balanță inițială de 200 ron, este nevoie să adaugăm tupluri în tabela Depune (codclient și nrcredit), făcând astfel legătura între clienți și conturile noi adăugate. • insert into Depune • select Imprumuta.codclient, Credit.nrcredit • from Imprumuta, Credit • where Imprumuta.nrcredit = Credit.nrcredit and numefiliala = 'ING1'

11. Modificarea bazei de date - UPDATE • Ca și comenzile DELETE și INSERT, comanda UPDATE poate conține o subinterogare. • De exemplu: • Să se adauge un procent de 5% pentru conturile a căror balanță este mai mare decât media: • update Cont • set balanta = balanta * 1.05 • where balanta >= (select avg (balanta) • from Cont)

12. Modificarea bazei de date - UPDATE • Să presupunem că dorim să adaugam un procent de 6% pentru conturile cu balanța mai mare de 3000, în timp ce la celelalte conturi adăugăm 5%. • Vom scrie 2 comenzi UPDATE: • update Cont • set balanta = balanta * 1.06 • where balanta > 3000 • update Cont • set balanta = balanta * 1.05 • where balanta <= 3000 • Ordinea în care se execută cele 2 comenzi este foarte importantă. Dacă schimbăm ordinea celor 2 UPDATE-uri, un cont cu o balanță sub 3000, după mărirea cu 5% poate ajunge cu balanța peste 3000. Astfel, acesta se va mări și în a 2-a comandă.

13. Modificarea bazei de date - UPDATE • SQL oferă o construcție CASE, care presupune execuția unor operații de modificare într-o singură comandă UPDATE, eliminând problema ordinii de executare. • update Cont • set balanta = case • when balanta <= 3000 then balanta * 1.05 • else balanta * 1.06 • end • Forma generală a acestei construcții este: • case • when pred1 then rezultat1 • when pred2 then rezultat2 • . . . • when predn then rezultatn • else rezultat0 • end

14. Modificarea unei vederi • Să considerăm următoarea vedere: • create view CrediteFiliale as • select numefiliala, nrcredit • from Credit • Operații de inserare, modificare și ștergere pe o vedere pot fi ambigue și dificile în cazul în care vederea se bazează pe mai multe relații. Astfel, multe SGBD-uri au impus următoarele constrângeri pentru a rezolva problemele modificărilor vederilor în cazul în care vederile se bazează pe mai multe relații: • O modificare este permisă dacă vederea implică doar o relație și nu conține funcții de agregare. • Exemplu: • insert into CrediteFiliale • values ('ING5', 17)

15. Programarea bazelor de date • Programarea bazelor de date cu JDBC • JDBC (Java Database Connectivity) este o interfata standard SQL de acces la baze de date. • JDBCeste constituita dintr-un set de clase si interfete scrise în Java, furnizând mecanisme standardpentru proiectanții aplicațiilor de baze de date. • JDBC 2.0 API include doua pachete: java.sql, cunoscut ca JDBC 2.0 core API si javax.sql, cunoscut ca JDBC Standard Extension. • JDBC 3.0 realizat in October 2001, introduce mai multe caracteristici aditionale, care includ extensii ale tipurilor de date, capabilitati aditionale pentru MetaData, etc.

16. Programarea bazelor de date • Folosind JDBC este usor sa transmitem secvente SQL catre baze de date relationale. • Cu alte cuvinte, nu este necesar sa scriem un program pentru a accesa o baza de date Oracle, alt program pentru a accesa o baza de date Sybase si asa mai departe. • Este de ajuns sa scriem un singur program folosind API-ul JDBC si acesta va fi capabil sa trimita secventeSQL bazei de date dorite. • Bineînteles, scriind codul sursa în Java, ne este asigurata portabilitatea programului. • Cu JDBC se realizeaza: • conectarea la o baza de date • transmiterea de secvente SQL • prelucrarea rezultatelelor primite de la SGBD

17. Programarea bazelor de date • Pașii pentru a lucra cu JDBC sunt: • A. Conectarea la o bază de date • Procesul de conectare la o baza de date implică două operații: • 1. încarcarea în memorie a unui driver corespunzator • 2. realizarea unei conexiuni propriu-zise • O conexiune (sesiune) la o baza de date reprezinta un context prin care sunt trimise secvente SQL si primite rezultate. Într-o aplicație pot exista mai multe conexiuni simultan la baze de date diferite sau la aceeași baza. • Clasele si interfetele responsabile cu realizarea unei conexiuni sunt: • clasa DriverManager, ce se ocupa cu înregistrarea driverelor ce vor fi folosite în aplicatie. • interfata Driver, pe care trebuie sa o implementeze orice clasa ce descrie un driver. • clasa DriverPropertyInfo. • interfata Connection, descrie obiectele ce modeleaza o conexiune propriu-zisa cu baza de date.

18. Programarea bazelor de date • B.Încărcarea în memorie a unui driver • Primul lucru pe care trebuie să-l faca o aplicație în procesul de conectare la o baza de date este sa încarce în memorie clasa ce implementeaza driver-ul necesar comunicarii cu respectiva baza de date. • Acest lucru poate fi realizat prin mai multe modalitati: • 1. DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver()); • 2. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); • 3. System.setProperty("jdbc.drivers","sun.jdbc.odbc.JdbcOdbcDriver"); • 4. java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver • Clasele Driver (arhiva .jar) trebuie căutate și salvate pentru fiecare SGBD. În anexele acestui curs, există următoarele arhive pentru: • -MySQL, PostgreSQL, MSSQL, Oracle.

19. Programarea bazelor de date • C.Specificarea unei baze de date • O dată ce un driver JDBC a fost încarcat în memorie, acesta poate fi folosit la stabilirea unei conexiuni cu o baza de date. Având în vedere faptul că pot exista mai multe drivere înregistrate în memorie, trebuie să avem posibilitea de a specifica pe lânga identificatorul bazei de date si driverul ce trebuie folosit. • Aceasta se realizeaza prin intermediul unei adrese specifice, numita JDBC URL, ce are urmatorul format: • jdbc:sub-protocol:identificator_baza_de_date • Câmpul sub-protocol denumeste tipul de driver ce trebuie folosit pentru realizarea conexiunii si poate fi odbc, oracle, sybase, db2 si asa mai departe. Identificatorul bazei de date este un indicator specific fiecarui driver care specifica baza de date cu care aplicatia doreste sa interactioneze. In functie de tipul driver-ului acest identificator poate include numele unei masini gazda, un numar de port, numele unui fisier sau al unui director, etc. • jdbc:odbc:testdb • jdbc:oracle:thin:@machine_name:1521:nume_bazadedate • jdbc:mysql://localhost:3306/nume_bazadedate • jdbc:postgresql://localhost:5432/nume_bazadedate • La primirea unui JDBC URL, DriverManager-ul va parcurge lista driver-elor înregistrate în memorie, pâna când unul dintre ele va recunoaste URL-ul respectiv. Daca nu exista nici unul potrivit, atunci va fi lansata o exceptie de tipul SQLException, cu mesajul No suitable driver.

20. Programarea bazelor de date • D.Realizarea unei conexiuni • Metoda folosita pentru realizarea unei conexiuni este getConnection din clasa DriverManager si poate avea mai multe forme: • Connection c = DriverManager.getConnection(url); • Connection c = DriverManager.getConnection(url, username, password); • Connection c = DriverManager.getConnection(url, dbproperies); • O conexiune va fi folosita pentru: • crearea de secvențe SQL ce vor fi folosite pentru interogarea sau actualizarea bazei • aflarea unor informatii legate de baza de date (meta-date) • Clasa Connection asigura suport pentru controlul tranzactiilor din memorie catre baza de date prin metodele commit, rollback, setAutoCommit .

21. Programarea bazelor de date • E.Efectuarea de secvente SQL • O dată facută conectarea cu DriverManager.getConection(), se poate folosi obiectul Connection rezultat pentru a se crea un obiect de tip Statement, cu ajutorul caruia putem trimite secvente SQL catre baza de date. • Cele mai uzuale comenzi SQL sunt cele folosite pentru: • interogarea bazei de date (SELECT) • actualizarea bazei de date (INSERT, UPDATE, DELETE) • Există 2 metode care pot fi aplicate pe un obiect de tip Connection pentru a crea un obiect de tip Statement. Acestea diferă în numărul de argument: • 1.createStatement() returnează un obiect de tip Statement. • 2.prepareStatement(Q), unde Q este o interogare SQL transmisă ca și String, returnează un obiect de tip PreparedStatement.

22. Programarea bazelor de date • Există 4 metode pentru a executa comenzi SQL. • 1.executeQuery(Q) primește ca și argument o comandă SQL care trebuie să fie o interogare, și este aplicată unui obiect Statement. Această metodă returnează un obiect de tip ResultSet, care este o colecție de tupluri produse de interogarea Q. • 2.executeQuery() este aplicată unui obiect PreparedStatement. Deoarece obiectul PreparedStatement are deja asociata o interogare, nu mai exista parametru transmis acestei metode. Această metodă returnează tot un obiect de tip ResultSet. • 3.executeUpdate(U) primește o comandă SQL care nu este interogare, ci Update, Insert sau Deleteși este aplicată unui obiect Statement. Efectul executării acestei comenzi se reflectă asupra bazei de date; metoda nu returnează nimic. • 4.executeUpdate(), cu nici un argument, este aplicată pe un obiect PreparedStatement. În aceste caz, comanda SQL asociata cu PreparedStatement va fi excutată.

23. Programarea bazelor de date • Exemplu 1: Să presupunem că avem o conexiune c și dorim să executăm interogarea: • Select Nume from Student; • O modalitate este de crea un obiect Statement și apoi să executăm interogarea direct. Rezultatul va fi plasat într-un obiect de tip ResultSet. • Statement s = c.createStatement(); • ResultSet r = s.executeQuery("SELECT Nume FROM Student"); • Cealaltă alternativă este de a pregăti o interogare și de a o executa mai târziu. Această modalitate este de preferat, atunci când interogarea se execută de mai multe ori. • PreparedStatement s = c. prepareStatement("SELECT Nume FROM Student"); • ResultSet r = s.executeQuery(); • Dacă interogarea executată nu are nici un parametru, cele 2 modalități sunt similare.

24. Programarea bazelor de date • Exemplu 2: Să presupunem că avem o conexiune c și dorim să executăm operația de inserare: • Insert into Student values ('Georgescu'); • Folosind prima variantă cu Statement: • Statement s = c.createStatement(); • s.executeUpdate("Insert into Student values ('Georgescu') "); • sau folosind varianta cu PreparedStatement: • PreparedStatement s = c. prepareStatement("Insert into Student values ('Georgescu') "); • s.executeUpdate();

25. Programarea bazelor de date • Exemplu 3: Să presupunem că dorim să executăm operația de inserare folosind parametri: • Statement s = c.createStatement(); • String nume; • s.executeUpdate("Insert into Student values (' "+nume+" ')"); • Sau • PreparedStatement s=c.prepareStatement("Insert into Student values (?)"); • s.setString(1,nume); • s.executeUpdate();

26. Programarea bazelor de date • Exemplu 4: Obținerea și prelucrarea rezultatelor • Când executăm o interogare si obținem un rezultat de tip ResultSet, dorim să prelucrăm tuplurile din obiectul returnat. Astfel, clasa ResultSet oferă următoarele metode: • 1.next()- când se aplică unui obiect ResultSet, un cursor implicit se poziționează la următorul tuplu (la primul tuplu atunci când se aplică prima dată); metoda returnează FALSE, dacă nu există nici un tuplu. • 2.getString(i)sau getString(numeAtribut), getInt(i) sau getInt(numeAtribut), getFloat(i) sau getFloat(numeAtribut) și alte metode analoage pentru tipuri de date SQL –returnează valoarea atributului al i-lea (sau a atributului cu denumirea numeAtribut) din tuplul curent. • while(r.next()) • { • String nume = r.getString(1); • //sau : String nume = r.getString("Nume"); } • }

27. Tema • Dati exemple de vederi si operatii de modificare complexe (ca in exemplele de la curs) pe o baza de date aleasa de voi.