Laborator FIS 7

Baze de date SQL

Exista doua mari categorii de baze de date (BD) mai raspandite, clasificate dupa modul in care o aplicatie se poate conecta la ele:

• 1. baze de date care instaleaza un server de date - este cazul BD gen MySQL, PostgreSQL, ... care de obicei sunt accesate folosind un socket de retea pe care BD il pune la dispozitia aplicatiilor, pentru a se conecta la el. Practic se creeaza un punct unic de interactiune cu BD, astfel incat mai multe accesari simultane sa poata fi totusi ordonate una fata de cealalta, astfel incat sa nu apara coruperi de date, care ar fi putut sa apara daca mai multe aplicatii ar fi accesat simultan pentru scriere/citire acelasi fisier (datele). Totodata, in acest fel datele pot fi accesate si prin retea, aparand astfel posibilitatea de a avea un server care sa fie dedicat doar pentru baza de date, iar unul (sau mai multe) calculatoare sa ruleze aplicatiile utilizator. Dezavantajele majore ale acestui tip de baze de date, sunt:

     1. • daca dorim sa rulam aplicatia pe un alt calculator, trebuie prima oara sa instalam acolo serverul de baze de date, ceea ce s-ar putea sa nu fie posibil, din ratiuni de securitate sau de copyright. Totodata, pe langa instalarea serverului, mai trebuie creat pe el un utilizator, baza de date si instalate si datele aplicatiei in server.

        • din cauza ca datele sunt in alt loc decat aplicatia, cand dorim sa facem backup-uri, instalari, dezinstalari, etc...trebuie sa actionam in doua locuri: atat la nivelul aplicatiei cat si in serverul de date

        • daca la acelasi server de BD sunt multi clienti, care fiecare are propria sa BD (de exemplu un server de internet cu mai multe site-uri fara legatura unul cu altul), atunci va aparea o "gatuire" a fluxului de date la nivelul punctului comun de acces, astfel incat apare o degradare a performantei intregului sistem

  2. baze de date accesate direct de aplicatie - este cazul BD gen SQLite, care pun la dispozitia aplicatiei un set de functii prin care aplicatia acceseaza direct BD. BD este continuta intrun fisier din interiorul aplicatiei si care este particular acesteia. Din acest motiv, nemaifiind si alte aplicatii care trebuie sa acceseze acea BD, nu mai trebuie sa existe un server, pentru a se ordona apelurile simultane (concurente). Aceste apeluri simultane ar putea sa apara doar daca aplicatia este de tip multithreading si de obicei in acest caz functiile puse la dispozitie de BD stiu sa ordoneze apelurile venite simultan de la mai multe thread-uri. Evident, dezavantajele acestui sistem sunt ca doar o singura aplicatie poate accesa simultan in mod scriere/citire acea BD si ca BD trebuie sa fie pe acelasi calculator cu aplicatia (ceea ce este cazul la cele mai multe aplicatii). Avantajele sunt urmatoarele:

     1. • deoarece nu trebuie instalat un server si baza de date in el, aplicatia poate fi usor instalata/dezinstalata pe alte calculatoare

        • in caz ca avem doar citiri de date, nici macar nu trebuie instalat ceva pe calculatorul gazda (zero-setup), ceea ce permite realizarea CD-urilor multimedia de prezentare, care contin o baza de date care este doar citita, nu si modificata

        • in cazul calculatoarelor cu mai multe core-uri sau procesoare, fiecare aplicatie isi poate accesa propria BD simultan si atunci nu mai apar puncte comune de acces, care sa trebuiasca ordonate, si care sa duca la degradarea performantelor

Din motivele expuse mai sus, tinand cont ca temele de laborator nu necesita accesari concurente din partea mai multor aplicatii, vom folosi o BD din a doua categorie, si anume SQLite ( www.sqlite.org ). Pentru a lucra mai usor cu BD, vom folosi o aplicatie de management a BD, si anume "SQLite Administrator" ( sqliteadmin.orbmu2k.de ). Nici sqliteadmin nu necesita instalare, asa ca poate fi folosit direct, dupa ce este extras din arhiva. Vom crea in sqliteadmin o BD pentru managementul unei aplicatii de gestiune a unei biblioteci simple, care in final va avea structura urmatoare:

Pornind de la aceste inregistrari, vom introduce mai multe comenzi SQL in tab-ul "SQL Query" din sqliteadmin, pentru a ne familiariza cu limbajul SQL. O comanda introdusa se poate executa cu butoanele de "Execute Query...", cu sau fara rezultat, sau direct apasand pe F9 sau F8. Dupa comenzile care modifica datele, trebuie dat un "Refresh" in tab-ul "Edit Data", pentru a aparea noile valori ale datelor.

SQL (Structured Query Language) este un limbaj pentru managementul bazelor de date relationale. El permite interogari si modificari de date, managementul structurii bazei de date, etc. SQL nu tine cont de litere mari si mici, astfel incat nu conteaza cu ce litere scriem comenzile. O comanda poate fi scrisa pe mai multe linii. Vom explica in continuare sintaxa a 4 comenzi SQL care se folosesc mai des, si anume interogare de date (SELECT), modificare (UPDATE), adaugare (INSERT) si stergere (DELETE):

• • SELECT - se foloseste pentru a interoga (extrage) datele din BD si are urmatoarea sintaxa generala (simplificata):

  • SELECT campuri FROM tabele WHERE conditie ORDER BY campuri_de_sortat (ASC|DESC)

    • • campurile sunt cele pe care dorim sa le obtinem din baza de date si se despart prin "," unul de altul. Se poate folosi "*" pentru a obtine toate campurile. Daca doua campuri au acelasi nume, se pot prefixa cu numele tabelei urmat de ".". Totodata, in rezultat putem schimba numele campului, folosind sintaxa "nume_camp_original AS nume_camp_schimbat".

      • tabelele sunt cele din care fac parte campurile pe care dorim sa le obtinem si se despart prin ","

      • conditia este o conditie logica pentru a restrange rezultatele interogarii doar la inregistrarile dorite. Aceasta conditie poate sa cuprinda mai multe subexpresii unite prin AND sau OR, poate cuprinde anumite functii predefinite, constante si nume de campuri. Ea este optionala si toata clauza WHERE poate sa lipseasca, daca dorim sa obtinem toate campurile.

      • campuri_de_sortat sunt o lista de campuri, separata prin ",", dupa care dorim sa sortam inregistrarile. Optional, dupa fiecare camp putem adauga ASC pentru sortare ascendenta (implicita) sau DESC, pentru sortare descendenta. Clauza ORDER BY este si ea optionala, putand lipsi cu totul

  • Exemple de SELECT:

  • SELECT * FROM t_utilizatori

  • SELECT count(f_id) FROM t_carti

  • SELECT f_nume FROM t_carti ORDER BY f_nume

  • SELECT t_carti.f_nume AS carte, t_utilizatori.f_nume AS utilizator FROM t_carti,t_imprumuturi,t_utilizatori WHERE t_carti.f_id=f_carte_id AND t_utilizatori.f_id=f_utilizator_id ORDER BY utilizator DESC

  • UPDATE - modifica inregistrari din baza de date si are urmatoarea sintaxa simplificata:

  • UPDATE tabela SET camp1=expresie1,camp2=expresie2,... WHERE conditie

    • • tabela este tabela in care dorim sa modificam inregistrarile

      • camp1,camp2,...sunt campurile pe care dorim sa le modificam

      • expresie1,expresie2,...sunt expresiile noi pe care le vor avea campurile

      • conditie este o conditie cu aceasi sintaxa ca la SELECT si care delimiteaza campurile care vor fi modificate. WHERE este optionala

  • Exemple de UPDATE:

  • UPDATE t_utilizatori SET f_nume="Alin" WHERE f_id=3

  • UPDATE t_imprumuturi SET f_utilizator_id=3 WHERE f_utilizator_id=2

  • INSERT - insereaza inregistrari noi intro tabela si are urmatoarea sintaxa simplificata:

  • INSERT INTO tabela (camp1,camp2,...) VALUES (expr1,expr2,...)

    • • tabela este tabela in care dorim sa inseram o noua inregistrare

      • camp1,camp2,.... sunt TOATE campurile din tabela (in varianta simplificata)

      • expr1,expr2,... sunt valorile pe care le vor avea campurile din noua inregistrare

      • daca un camp este marcat ca fiind "Autoincrement", atunci putem sa-i dam valoarea initiala "NULL", si BD va sti automat sa-i dea o noua valoare unica, mai mare cu "1" decat orice inregistrare din tabela

  • Exemple de INSERT:

  • INSERT INTO t_utilizatori (f_id,f_nume) VALUES (NULL,"Ana")

  • INSERT INTO t_imprumuturi (f_utilizator_id,f_carte_id,f_data) VALUES (3,1,date('now'))

  • DELETE - sterge una sau mai multe inregistrari dintro tabela, si are umatoarea sintaxa simplificata:

  • DELETE FROM tabela WHERE conditie

    • • tabela este tabela de unde dorim sa stergem inregistrari

      • conditie este o conditie cu acceasi sintaxa ca la SELECT si care delimiteaza inregistrarile care vor fi sterse. WHERE este optionala

  • Exemple de DELETE:

  • DELETE FROM t_imprumuturi WHERE f_utilizator_id=3

  • DELETE FROM t_utilizatori WHERE f_id=3

Pe langa comenzile de mai sus, SQL mai are comenzi pentru crearea sau modificarea de tabele, etc. Din pacate, anumite aspecte ale limbajului SQL nu sunt standardizate si atunci cand folosim o alta aplicatie de BD anumite comenzi trebuie usor modificate. Pentru lista de comenzi SQL si de functii predefinite care sunt acceptate de SQLite, se poate consulta pagina de documentatie de la sqlite.org.

In acest laborator vom creea o aplicatie simpla care se va conecta la baza de date de mai sus si va permite unele operatii asupra ei. Se va porni de la o aplicatie predefinita care contine deja implementata interfata utilizator si conexia la baza de date. La aceasta aplicatie se vor adauga functiile de interogare si modificare a BD. In final aplicatia va arata astfel:

Se va porni de la o aplicatie data (a se vedea fisierul atasat) si dupa modelul din cod, care expliciteaza implementarea butoanelor "Listare utilizatori", "Adauga utilizator" si "Carti", se vor implementa functionalitatile celorlalte butoane. Cateva explicatii legate de aplicatie si de cod:

• • unde se cere un Id, se va introduce id-ul numeric. Nu se vor face verificari de validitate in BD, de exemplu daca exista sau nu un utilizator, daca o carte este deja imprumutata, etc.

  • la "Utilizatori inregistrati", "Carti" listeaza toate cartile imprumutate de acel utilizator, iar "Sterge" sterge utilizatorul din BD, inclusiv toate imprumuturile sale

  • la "Carti din biblioteca", "Returneaza" sterge cartea cu id-ul dat din lista de carti imprumutate (pentru a redeveni disponibila), iar "Sterge" sterge cartea din baza de date, inclusiv de la imprumuturi, daca este imprumutata

  • la "Imprumutare", "Imprumuta" creeaza o noua inregistrare in tabela "t_imprumuturi", cu id-urile date si data imprumutului ca fiind data curenta ( date("now") )

  • "Adauga utilizator", cere un nume de utilizator, si daca nu s-a apasat "Ok" (valoarea returnata de "JOptionPane.showInputDialog" este nenula), creaza un nou utilizator in DB si afiseaza si id-ul acestuia

  • "Adauga carte" cere un nume de carte, verifica daca este nenul, apoi cere un nume de autor, verifica daca este nenul, adauga cartea in DB si afiseaza id-ul acesteia

  • "Listare utilizatori" afiseaza in "Rezultate" toti utilizatorii din BD (id|nume), sortati dupa nume

  • "Listare carti" afiseaza in "Rezultate" toate cartile din BD (id|autor|nume), sortate dupa autor si secundar dupa nume

Pentru implementarea noilor functionalitati se va urmari sa se refoloseasca din codul deja existent (metodele err, msg, sqlSelect, sqlInsert, afiseaza). Totodata, se vor consulta metodele deja implementate (bListareUtilizatoriBtnActionPerformed, bAdaugaUtilizatorBtnActionPerformed, uiCartiBtnActionPerformed) pentru modele de implementare. Unde comenzile sql sunt ceva mai complexe, se pot testa inainte niste exemple de cod sql direct in aplicatia sqliteadmin. Pe parcursul rularii programului nu se vor folosi simultan si sqliteadmin si programul, pentru a nu aparea coruperi de date, deci se va inchide sqliteadmin. Ca mod general de operare cu SQL, se va foma comanda SQL necesara intrun String, iar apoi acest string se paseaza metodei "sql" sau "prepare".

Java pune la dispozitia programatorilor un set foarte puternic de functii pentru baze de date (JDBC - Java Database Connectivity), dar care este putin mai complex. In aceasta aplicatie tutorial s-au folosit niste functii mai simple, implementate de libraria sqlite4java ( code.google.com/p/sqlite4java ). In arhiva care se aduce de pe net, exista si documentatia functiilor de librarie. Pentru ca aceasta librarie sa fie folosita intr-o aplicatie, ea trebuie adaugata in aplicatie (fisierele sqlite4java.jar, sqlite4java-win32-x64.dll, sqlite4java-win32-x86.dll trebuie sa existe in folderul aplicatiei), iar in NetBeans, in fereastra "Projects" la "Libraries" trebuie adaugat (cu click dreapta "Add JAR/Folder...") sqlite4java.jar din folderul aplicatiei. Baza de date (in cazul de mai sus l7.s3db ), trebuie si ea sa existe in folderul aplicatiei. In aceste conditii, conexiunea la BD se face dupa modelul din constructorul clasei L7View. Cand in sqlite4java are loc un SELECT, campurile din select se pot accesa dupa indexul lor (asa cum este cerut in SELECT), incepand cu indexul 0. De exemplu, daca are loc urmatorul select: "SELECT f_nume from t_utilizatori", vom avea la indexul 0 in SQLiteStatement (care reprezinta un cursor de inregistrari returnate de SELECT), valoarea curenta pentru f_nume. Aceste valori pot fi returnate in diverse formate, folosind "columnString", "columnInt", etc. Trecerea la urmatoarea inregistrare din setul returnat de SELECT se face cu metoda "step" din SQLiteStatement, care, daca exista o noua inregistrare returnata de SELECT, trece la ea si intoarce true.