Baze de Date Anca Ion 2011 2012 Baze

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

Vederi Baze de Date Anca Ion 2011 -2012 • 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.

Vederi Baze de Date Anca Ion 2011 -2012 • 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 )))

Vederi • Numele atributelor unei vederi pot fi specificate explicit: create view Suma. Totala. Credite(numefiliala, totalcredite) as select numefiliala, sum(suma) from Credit group by numefiliala Baze de Date Anca Ion 2011 -2012

Baze de Date Anca Ion 2011 -2012 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 ING 1. select totalcredite from Suma. Totala. Credite where numefiliala = 'ING 1‘ • Dacă dorim să ștergem o vedere, se folosește comanda DROP VIEW: • drop view totalcredite

Modificarea bazei de date Baze de Date Anca Ion 2011 -2012 • 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.

Modificarea bazei de date - DELETE Baze de Date Anca Ion 2011 -2012 • Exemple ale operației DELETE: 1. Să se șteargă toate conturile de la filiala ING 1. delete from Cont where numefiliala = 'ING 1' 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')

Modificarea bazei de date - DELETE Baze de Date Anca Ion 2011 -2012 • 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.

Modificarea bazei de date - INSERT Baze de Date Anca Ion 2011 -2012 • 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 ING 1. Numărul de credit va servi ca și număr de cont. insert into Cont select nrcredit, numefiliala, 200 from Credit where numefiliala = 'ING 1'

Modificarea bazei de date - INSERT Baze de Date Anca Ion 2011 -2012 • 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 ING 1 ș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 = 'ING 1'

Modificarea bazei de date - UPDATE Baze de Date Anca Ion 2011 -2012 • 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)

Modificarea bazei de date - UPDATE Baze de Date Anca Ion 2011 -2012 • 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ă.

Modificarea bazei de date - UPDATE Baze de Date Anca Ion 2011 -2012 • 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 pred 1 then rezultat 1 when pred 2 then rezultat 2. . . when predn then rezultatn else rezultat 0 end

Modificarea unei vederi Baze de Date Anca Ion 2011 -2012 • Să considerăm următoarea vedere: create view Credite. Filiale 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 Credite. Filiale values ('ING 5', 17)

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 • Programarea bazelor de date cu JDBC • JDBC (Java Database Connectivity) este o interfata standard SQL de acces la baze de date. • JDBC este constituita dintr-un set de clase si interfete scrise în Java, furnizând mecanisme standard pentru 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 Meta. Data, etc.

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 • 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 secvente SQL 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

Programarea bazelor de date Pașii pentru a lucra cu JDBC sunt: Baze de Date Anca Ion 2011 -2012 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 Driver. Manager, 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 Driver. Property. Info. interfata Connection, descrie obiectele ce modeleaza o conexiune propriu-zisa cu baza de date.

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 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. Driver. Manager. register. Driver(new sun. jdbc. odbc. Jdbc. Odbc. Driver()); 2. Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); 3. System. set. Property("jdbc. drivers", "sun. jdbc. odbc. Jdbc. Odbc. Driver"); 4. java -Djdbc. drivers=sun. jdbc. odbc. Jdbc. Odbc. Driver • Clasele Driver (arhiva. jar) trebuie căutate și salvate pentru fiecare SGBD. În anexele acestui curs, există următoarele arhive pentru: -My. SQL, Postgre. SQL, MSSQL, Oracle.

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 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, db 2 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, Driver. Manager-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.

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 D. Realizarea unei conexiuni • Metoda folosita pentru realizarea unei conexiuni este get. Connection din clasa Driver. Manager si poate avea mai multe forme: Connection c = Driver. Manager. get. Connection(url); Connection c = Driver. Manager. get. Connection(url, username, password); Connection c = Driver. Manager. get. Connection(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, set. Auto. Commit.

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 E. Efectuarea de secvente SQL • O dată facută conectarea cu Driver. Manager. get. Conection(), 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. create. Statement() returnează un obiect de tip Statement. 2. prepare. Statement(Q), unde Q este o interogare SQL transmisă ca și String, returnează un obiect de tip Prepared. Statement.

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 • Există 4 metode pentru a executa comenzi SQL. 1. execute. Query(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 Result. Set, care este o colecție de tupluri produse de interogarea Q. 2. execute. Query() este aplicată unui obiect Prepared. Statement. Deoarece obiectul Prepared. Statement are deja asociata o interogare, nu mai exista parametru transmis acestei metode. Această metodă returnează tot un obiect de tip Result. Set. 3. execute. Update(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. execute. Update(), cu nici un argument, este aplicată pe un obiect Prepared. Statement. În aceste caz, comanda SQL asociata cu Prepared. Statement va fi excutată.

Programarea bazelor de date • Exemplu 1: Să presupunem că avem o conexiune c executăm interogarea: Baze de Date Anca Ion 2011 -2012 și dorim să 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 Result. Set. Statement s = c. create. Statement(); Result. Set r = s. execute. Query("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. Prepared. Statement s = c. prepare. Statement("SELECT Nume FROM Student"); Result. Set r = s. execute. Query(); • Dacă interogarea executată nu are nici un parametru, cele 2 modalități sunt similare.

Baze de Date Anca Ion 2011 -2012 Programarea bazelor de date • Exemplu 2: Să presupunem că avem o conexiune c executăm operația de inserare: și dorim să Insert into Student values ('Georgescu'); • Folosind prima variantă cu Statement: Statement s = c. create. Statement(); s. execute. Update("Insert into Student values ('Georgescu') "); • sau folosind varianta cu Prepared. Statement: Prepared. Statement s = c. prepare. Statement("Insert into Student values ('Georgescu') "); s. execute. Update();

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 • Exemplu 3: Să presupunem că dorim să executăm operația de inserare folosind parametri: Statement s = c. create. Statement(); String nume; s. execute. Update("Insert into Student values (' "+nume+" ')"); Sau Prepared. Statement s=c. prepare. Statement("Insert into Student values (? )"); s. set. String(1, nume); s. execute. Update();

Programarea bazelor de date Baze de Date Anca Ion 2011 -2012 • Exemplu 4: Obținerea și prelucrarea rezultatelor • Când executăm o interogare si obținem un rezultat de tip Result. Set, dorim să prelucrăm tuplurile din obiectul returnat. Astfel, clasa Result. Set oferă următoarele metode: 1. next()- când se aplică unui obiect Result. Set, 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. get. String(i) sau get. String(nume. Atribut), get. Int(i) sau get. Int(nume. Atribut), get. Float(i) sau get. Float(nume. Atribut) și alte metode analoage pentru tipuri de date SQL –returnează valoarea atributului al i-lea (sau a atributului cu denumirea nume. Atribut) din tuplul curent. while(r. next()) { String nume = r. get. String(1); //sau : String nume = r. get. String("Nume"); } }

Tema Baze de Date Anca Ion 2011 -2012 • Dati exemple de vederi si operatii de modificare complexe (ca in exemplele de la curs) pe o baza de date aleasa de voi.