Accesul la baze de date folosind JDBC - notiuni generale de SQL

Baze de date relationale: generalitati

Notiuni generale de SQL

Conectarea la o baza de date folosind JDBC: Connection, Statement, ResultSet

Maparea tipurilor de date SQL / Java

Inserarea / stergerea / modificarea datelor

Parametrizarea unui query

Folosirea tranzactiilor

Framework-uri de acces la baza de date (Hibernate)

Baze de date relationale: generalitati

O baza de date relationala este o in esenta o colectie de tabele

Fiecare tabel contine inregistrari (echivalente rindurilor unui tabel)

Fiecare inregistrare este alcatuita din cimpuri (echivalentul coloanelor); toate inregistrarile unui tabel au aceleasi cimpuri, definite al constructia tabelului

Fiecare cimp are un nume si un tip; tipurile de date sint de 4 categorii principale: numerice, de tip caracter, date si de tip binar

Orice cimp al unei inregistrari poate avea o valoare speciala, NULL (cimpul nu contine “nimic”)

Asupra cimpurilor unui tabel se pot face limitari in ceea ce priveste valorile continute (de exemplu: nu pot fi null)

Identificarea unica a unei inregistrari intr-un tabel se face prin chei. O cheie e unul din cimpurile inregistrarii, marcat explicit ca fiind cheie (cheie primara). Un astfel de cimp nu poate fi null si are o valoare unica pentru fiecare inregistrare

Legatura intre tabele se face prin chei straine; o cheie straina este un cimp normal (care nu e cheie primara) al unei inregistrari, care indica un cimp cheie primara din alt tabel. Legatura se face prin valoare (cheia straina din tabel are aceeasi valoare cu cheia primara a inregistrarii corespunzatoare lui din tabelul referit)

Notiuni generale de SQL

- tipuri de date

Se vor prezenta in continuare tipurile standard de date. Fiecare server de baze de date are si alte tipuri de date proprii.

- crearea unei baze de date; conectarea la o baza de date

Pentru a putea crea tabele va trebui creata intii o baza de date.

Sintaxa SQL este CREATE DATABASE nume [optiuni]

Optiunile depind de serverul de baze de date folosit. O optiune generala este CHARACTER_SET (poate fi latin, unicode etc. in functie de serverul de base de date folosit)

Exemplu:

CREATE DATABASE promanagement;

Urmeaza conectarea la baza de date.

De asemenea sirul de conectare depinde de serverul de baze de date folosit.

Pentru MySQL, comanda este

USE database_name.

Pentru IBM DB2, comanda este

CONNECT TO database_name USER user_name USING password.

- crearea unui tabel

Sintaxa pentru crearea unui tabel este CREATE TABLE name ( columns );

Numele tabelului poate fi prefixat de catre numele bazei de date sau al schemei curente (in IBM DB2 numele schemei depinde de utilizatorul conectat; in MySQL versiunea 5 si mai noi schema este echivalenta cu baza de date).

Exemplu:

CREATE TABLE promanagement.task (

TaskID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Name VARCHAR(45),

Description VARCHAR(255),

PRIMARY KEY (`TaskID`)

Daca inainte de a crea tabelul s-a efectuat conectarea la baza de date, numele schemei nu mai este obligatoriu.

Exemplu:

CREATE TABLE task (

TaskID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Name VARCHAR(45),

Description VARCHAR(255),

PRIMARY KEY (`TaskID`)

Nota: unele cimpuri pot avea valori implicite. In exemplul de mai sus se poate scrie Name VARCHAR(45) DEFAULT null

- modificarea unui tabel

Se foloseste comanda ALTER TABLE. Se pot adauga coloane, sterge coloane sau adauga constringeri de tipul cheie straina.

Exemplu:

ALTER TABLE task ADD COLUMN comments VARCHAR(100);

Pentru a sterge o coloana:

ALTER TABLE task DROP COLUMN comments;

Exemplu de adaugare de constringere:

ALTER TABLE task ADD CONSTRAINT FOREIGN KEY(newtaskid) REFERENCES newtask(newtaskid);

Nota: O constringere se poate adauga si la crearea tabelului

Nota Mecanismul de verificare a cheilor straine din MySQL se poate dezactiva folosind comanda SET FOREIGN_KEY_CHECKS = 0

Pentru verificarea constringerilor se poate rula comanda din exemplul de mai jos:

SHOW TABLE STATUS FROM promanagement LIKE 'task'

- selectii simple

Pentru selectie se foloseste comanda SELECT [lista_cimpuri] FROM tabel

Exemplu:

SELECT taskid, description FROM task;

Pentru ca in tabelul rezultat sa se inlocuiasca numele coloanelor cu alt nume mai sugestiv se poate folosi un alias.

Exemplu:

SELECT taskid as ID, description as 'Descrierea taskului' FROM task

- adaugarea de inregistrari

Se foloseste comanda INSERT INTO table_name( lista_cimpuri) VALUES (lista_valori)

Exemplu:

INSERT INTO task(name,description) VALUES('task1','primul task inserat');

In cazul in care nu se specifica lista de cimpuri vor trebui date valori pentru toate cimpurile, in ordinea in care au fost definite.

- stergerea de inregistrari

Se foloseste comanda DELETE FROM tabel WHERE conditie.

Exemplu:

DELETE FROM task WHERE name = “task1”;

Nota: daca se doreste stergerea unui singur rind, se recomanda stergerea dupa cheia primara

- selectii complexe, din mai multe tabele; tipuri de join-uri, selectii imbricate

Pentru exemplificare se vor considera urmatoarele tabele:

Employees

Orders:

Cel mai simplu tip de selectie din mai multe tabele (din punct de vedere al sintaxei) este utilizarea instructiunii:

SELECT lista_cimpuri FROM tabel1, tabel2, . . tabeln WHERE conditii

Exemplu:

SELECT Employees.Name, Orders.Product

FROM Employees, Orders

WHERE Employees.Employee_ID=Orders.Employee_ID

Acesta va afisa cimpul Name din Employees si cimpul Product din Orders pentru toate rindurile unde este satisfacuta relatia Employees.Employee_ID=Orders.Employee_ID.

Nota: daca un cimp din cadrul relatiei contine valori NULL, rindul corespunzator nu va aparea in rezultate.

La acest select, pe linga conditia de egalitate, se pot adauga si conditii de filtrare.

Exemplu:

SELECT Employees.Name

FROM Employees, Orders

WHERE Employees.Employee_ID=Orders.Employee_ID

AND Orders.Product=”Birou”

INNER JOIN:

Este o alta sintaxa pentru instructiunea SELECT de mai sus, pentru doua tabele. Se face produsul cartezian al celor doua tabele (toate combinatiile de linii) si se aleg ca rezultat cele care indeplinesc conditia. Rindurile care au valoarea NULL in cimpurile din conditie sint eliminate din rezultat.

Sintaxa este:

SELECT lista_cimpuri FROM tabel1 INNER JOIN tabel 2 ON conditie

Exemplu:

SELECT Employees.Name, Orders.Product

FROM Employees

INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

Se poate adauga si o clauza WHERE:

SELECT Employees.Name, Orders.Product

FROM Employees

INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

WHERE Orders.Product=”Birou”

LEFT JOIN

Se face produsul cartezian al celor doua tabele si se aleg ca rezultat rindurile care indeplinesc conditia din clauza ON a instructiunii. Aditional, se aleg si rindurile din primul tabel care nu indeplinesc conditia.

Sintaxa este

SELECT lista_cimpuri FROM tabel1 LEFT  JOIN tabel 2 ON conditie

Exemplu:

SELECT Employees.Name, Orders.Product

FROM Employees

LEFT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

Se vor selecta toti angajatii din tabelul Employee, chiar daca unii (in acest caz angajatul cu ID = 2) nu au inregistrari corespondente in tabelul Orders. Cimpurile din al doilea tabel pentru care nu exista valori vor fi umplute cu NULL.

RIGHT JOIN

Se face produsul cartezian al celor doua tabele si se aleg ca rezultat rindurile care indeplinesc conditia din clauza ON a instructiunii. Aditional, se aleg si rindurile din al doilea tabel care nu indeplinesc conditia.

Sintaxa este

SELECT lista_cimpuri FROM tabel1 RIGHT JOIN tabel 2 ON conditie

Exemplu:

SELECT Employees.Name, Orders.Product

FROM Employees

RIGHT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

Ordonarea inregistrarilor

Se foloseste clauza ORDER BY cimp   plasata la sfirstitul instructiunilor de selectie.

Exemplu:

SELECT * FROM task ORDER BY Name

Daca se doreste ordonarea descrescatoare, se poate scrie:

SELECT * FROM task ORDER BY Name DESC

Pentru a se ordona dupa mai multe cimpuri, dupa clauza ORDER BY se scrie o lista de cimpuri. Dupa fiecare cimp din lista se poate adauga sau nu DESC.

Exemplu:

SELECT * FROM task ORDER BY Name DESC, Description

Conectarea la o baza de date folosind JDBC: Connection, Statement, ResultSet

Primul pas pentru conectarea la o baza de date dintr-un program Java este obtinerea driver-ului JDBC pentru serverul de baze de date respectiv. Acesta este de obicei o arhiva JAR sau o colectie de clase/arhive si, dupa copierea pe masina unde ruleaza aplicatia Java, va trebui adaugat in CLASSPATH.

Fiecare driver are propria sa metoda de a defini conexiunea la baza de date. Sirul de conexiune este asemanator unui URL de pagina Web, dar protocolul nu este HTTP ci JDBC.

In continuare se va exemplifica conectarea la un server MySQL:

Driver-ul este com.mysql.jdbc.Driver, iar sirul de conexiune este de forma jdbc:mysql://HOST/DATABASE, unde HOST este numele masinii pe care ruleaza serverul, iar DATABASE este numele bazei de date la care se face conexiunea. In cazul in care serverul ruleaza pe aceeasi masina ca si programul Java, HOST va lua valoarea localhost.

Conexiunea la o baza de date este un obiect de tipul java.sql.Connection. Exemplul de mai jos ilustreaza conectarea la o baza de date si obtinerea obiectului Connection:

Connection conn = null;

String userName = 'root';

String password = 'promanagement';

String url = 'jdbc:mysql://localhost/promanagement';

Class.forName ('com.mysql.jdbc.Driver').newInstance ();

conn = DriverManager.getConnection (url, userName,
                                               password);

Dupa folosire, obiectul Connection trebuie inchis prin apelarea metodei close(). De obicei acest apel se face intr-un bloc finalize.

Pentru a putea executa comenzi SQL, pe baza obiectului Connection se creeaza un obiect java.sql.Statement.

Exemplu:

Statement st = conn.createStatement();

Dupa folosire, obiectul Statement trebuie inchis prin apelarea metodei close(). De obicei acest apel se face intr-un bloc finalize.

Un obiect de tipul Statement poate executa comenzi SQL de extragere de date (prin intermediul metodei executeQuery(..)) si de actualizare a continutului bazei de date (prin intermediul metodei executeUpdate()).

Metoda executeQuery returneaza un obiect de tipul java.sql.ResultSet, care contine datele citite din baza de date.

Exemplu:

String sql = 'SELECT * FROM task';
ResultSet rs = st.executeQuery(sql);

Navigarea printr-un ResultSet

Un obiect de tipul ResultSet poate fi modificat in doua privinte:

Felul de parcurgere a datelor obtinute

Felul in care schimbarile datelor se reflecta in obiectul ResultSet

Tipurile de ResultSet sint urmatoarele (sint constante definite in interfata ResultSet):

TYPE_FORWARD_ONLY: cursorul se poate deplasa doar inainte

TYPE_SCROLL_INSENSITIVE: cursorul se poate deplasa in ambele directii si poate fi pozitionat absolut; schimbarile facute in baza de date nu se reflecta in obiect dupa ce acesta a fost creat

TYPE_SCROLL_SENSITIVE: cursorul se poate deplasa in ambele directii si poate fi pozitionat absolut; schimbarile facute in baza de date se reflecta in obiect dupa ce acesta a fost creat

O utilizare tipica a lui ResultSet include preluarea de date si parcurgerea liniara a rezultatelor ca in exemplul de mai jos:

ResultSet rs = st.executeQuery(sqlString);

while(rs.next())

Obtinerea de date din rindul curent se face cu ajutorul metodelor get() ale obiectului ResultSet. De exemplu, metoda getString(..) intoarce o valoare de tipul String, metoda getInt(..) intoarce o valoare de tipul int etc.

Metodele get() au doua versiuni: una care ia ca parametru un String reprezentind numele coloanei si alta care ia  ca parametru un int, reprezentind numarul coloanei in tabela de rezultate (1 este prima coloana din stinga, 2 a doua etc.)

Maparea tipurilor de date SQL / Java

Metodele get . () ale obiectului ResultSet incearca sa interpreteze datele din coloana data ca parametru si intorc un tip de date Java. Interpretarea nu depinde de tipul SQL al datelor din coloana respectiva, ci de metoda apelata.

De exemplu:

Coloana TaskID contine un INTEGER cu valoarea 1.

rs.getInt(“TaskID”) va intoarce intregul 1

rs.getString(“TaskID”) va intoarce un String cu valoarea “1”.

Daca tipul de date nu poate fi convertit se arunca o exceptie.

Tabelul urmator indica tipurile de date SQL si maparea lor corespunzatoare in tipuri de date Java:

Inserarea / stergerea / modificarea datelor

Operatiunile de modificare a datelor se efectueaza cu ajutorul metodei executeUpdate() a lui ResultSet. Aceasta intoarce  un intreg care reprezinta numarul de rinduri afectate in cazul operatiunilor INSERT, DELETE, UPDATE sau 0 pentru celelalte operatiuni (cum ar fi DROP TABLE).

In caz de eroare se arunca exceptii.

Exemplu:

String sql = 'INSERT INTO task(Name,Description)
           VALUES('automatic','this task was inserted
automatically')';

int i = st.executeUpdate(sql);

Parametrizarea unui query

In loc de a construi sirul SQL ca String si a-l pasa unui Statement, exista posibilitatea de a crea un sir SQL “generic”, in care valorile cimpurilor sa fie inserate ulterior. Aceasta abordare permite o mai mare claritate a codului (evitarea folosirii repetate a caracterelor de escape pentru string-uri, scurtarea comenzii SQL etc.) si de asemenea imbunatateste viteza de executie cind se aplica aceeasi comanda SQL pentru mai multe seturi de parametri.

In acest scop se foloseste, in locul lui java.sql.Statement, un obiect de tipul java.sql.PreparedStatement. Cu un astfel de obiect se lucreaza in doi pasi:

Se creeaza prin apelarea metodei prepareStatement a obiectului Connection

Se inlocuiesc parametrii cu valorile efective, apelind metodele set . () ale obiectului PreparedStatement creat

Exemplu:

String sql = 'SELECT * FROM task WHERE Name = ?';

st = conn.prepareStatement(sql);

st.setString(1, 'automatic');

Valorile efective sint inlocuite in comanda SQL prin caracterul ? (semnul intrebarii).

Pentru fiecare tip de date Java exista o metoda specifica a lui PreparedStatement pentru a seta un atribut. Ele au nume corespunzatoare metodelor get . din ResultSet. Exista deci un setString, setInt etc.  Primul parametru al unei metode set . () este numarul parametrului. Se paseaza argumentul 1 pentru a seta primul atribut, 2 pentru a seta al doilea atribut etc.

Folosirea tranzactiilor

In mod normal, fiecare executie a metodei executeUpdate(..) a lui ResultSet se reflecta imediat in baza de date. Exista cazuri in care acest lucru nu este dorit. De exemplu cind trebuie actualizate mai multe tabele care depind unele de altele. Daca s-a actualizat un numar de  tabele si apoi se produce o exceptie, datele vor ramine inconsistente.

Clasele JDBC din limbajul Java pun la indemina un mecanism pentru a executa “atomic” un numar de comenzi SQL. “Atomic” inseamna ca se vor executa fie toate instructiunile, fie nici una. Acest mecanism poarta numele de tranzactii.

Obiectul Connection creat pentru conexiune dispune de metoda setAutoCommit(..) care poate modifica comportamentul la apelarea metodelor executeUpdate(). Prin apelarea lui setAutoCommit(false) executia metodelor executeUpdate() nu se va reflecta imediat in baza de date, ci se va astepta apelarea metodei update() a obiectului Connection. In caz de eroare (de exemplu in blocul catch) se poate apela metoda rollback() a obiectului Connection pentru a anula toate modificarile facute.

Pseudocodul unei metode care foloseste tranzactii este:

Connection.setAutoCommit(false);

try catch(SQLException e)

Nota: exista optiunea de a efectua rollback doar la portiuni dintr-o tranzactie. In interiorul tranzactiei se poate apela metoda setSavepoint a obiectului Connection pentru a seta un punct de intoarcere, iar in cazul unei exceptii, in functie de anumite conditii, se poate apela rollback(Savepoint sp) a obiectului Connection pentru a reveni la un savepoint creat anterior.

Framework-uri de acces la baza de date (Hibernate)

Hibernate este un framework care permite stocarea obiectelor Java intr-o baza de date relationala. Stocarea se face obiectual, apelind metode de genul object.save() sau object.load(), fara a fi nevoie sa se scrie comenzi SQL.

Hibernate este disponibila spre descarcare la https://www.hibernate.org/

Pentru a lucra cu Hibernate se vor introduce in proiect bibliotecile hibernate3.jar, jta.jar, dom4j.jar, log4j*.jar, commons-logging.jar, cglib*.jar, asm.jar, asm-attrs.jar, antlr.jar

Primul pas in lucrul cu Hibernate este crearea claselor care vor fi stocate in baza de date. Acestea sint simple clase Java care respecta conventia JavaBeans.

Exemplu:

package promanagement.hibernate;

import java.sql.Date;

public class Task

public Long getId()

private void setId(Long id)

public String getDescription()

public void setDescription(String description)

public String getName()

public void setName(String name)

public Date getTaskDate()

public void setTaskDate(Date taskDate)

Se pot face urmatoarele observatii:

S-a definit un cimp “id” care va folosi drept cheie primara pentru clasa persistata. Setter-ul pentru acest cimp este declarat private pentru a nu putea fi folosit de catre clasele proiectului. Hibernate il va putea insa accesa.

S-a definit un constructor fara parametri si fara corp. Acesta este necesar pentru ca Hibernate sa poata instantia obiectul

Odata clasa creata, ea va trebui inregistrata cu Hibernate, pentru a putea fi salvata in baza de date.

In acest scop se va crea fisierul Task.hbm.xml in acelasi director ca si fisierul Task.java. Acesta va contine maparea cimpurilor in baza de date.

Exemplu:

<?xml version='1.0'?>

<!DOCTYPE hibernate-mapping PUBLIC

'-//Hibernate/Hibernate Mapping DTD 3.0//EN'

'https://hibernate.sourceforge.net/hibernate- mapping-3.0.dtd'>

<hibernate-mapping>

<class name='promanagement.hibernate.Task'
table='TASK'>

<id name='id' column='ID'>

<generator class='native'/>

</id>

<property name='name'/>

<property name='description' column='DESCRIPTION'/>

<property name='taskDate' type='timestamp' column='TASKDATE'/>

</class>

</hibernate-mapping>

Se remarca in primul rind maparea clasei Task in tabela TASK. Urmeaza specificarea cheii primare, prin tag-ul “id”.

Tag-ul “id”, ca si tag-ul “property” care indica un cimp din clasa, pot avea atributul optional “column” care indica numele coloanei in baza de date. Daca acest atribut lipseste, numele coloanei este egal cu numele cimpului.

In ceea ce priveste atributul type: Hibernate incearca sa mapeze tipul de date Java al cimpului respectiv la tipul de date SQL corespunzator. Atributul type este folosit doar cind maparea este ambigua. De exemplu, tipul java.sql.Date poate fi translatat in tipul SQL DATE, sau in tipul SQL TIMESTAMP.

Dupa configurarea individuala a fiecarei clase mapate urmeaza configurarea globala a Hibernate. Aceasta consta in configurarea serverului de baze de date, a URL-ului bazei de date, a tipului de cache folosit etc.

Fisierul de configurare se numeste hibernate.cfg.xml si se plaseaza in radacina directorului cu surse Java al proiectului.

Exemplu de fisier de configurare:

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC

'-//Hibernate/Hibernate Configuration DTD
3.0//EN'

'https://hibernate.sourceforge.net/hibernate- configuration-3.0.dtd'>

<hibernate-configuration>

<session-factory>

<!-- Database connection settings -->

<property name='connection.driver_class'>

com.mysql.jdbc.Driver

</property>

<property name='connection.url'>

jdbc:mysql://localhost/promanagement

</property>

<property name='connection.username'>

root

</property>

<property name='connection.password'>

promanagement

</property>

<!-- JDBC connection pool (use the built-in) -->

<property name='connection.pool_size'>

1

</property>

<!-- SQL dialect -->

<property name='dialect'>

org.hibernate.dialect.MySQLDialect

</property>

<!-- Enable Hibernate's automatic session context

management -->

<property name='current_session_context_class'>

thread

</property>

<!-- Disable the second-level cache -->

<property name='cache.provider_class'>

org.hibernate.cache.NoCacheProvider

</property>

<!-- Echo all executed SQL to stdout -->

<property name='show_sql'>true</property>

<!-- Drop and re-create the database schema on

startup -->

<property name='hbm2ddl.auto'>

create

</property>

<mapping

resource='promanagement/hibernate/Task.hbm.xml'/>

</session-factory>

</hibernate-configuration>

Pentru a putea lucra cu Hibernate este necesara initierea unei sesiuni (asemanatoare unei tranzactii JDBC). Ea este reprezentata de un obiect de tip org.hibernate.Sesssion. Acesta nu se instantiaza direct, ci prin intermediul unui obiect org.hibernate.SessionFactory.

O sesiune incepe in momentul in care se apeleaza prima data metoda getCurrentSession() a lui SessionFactory si se termina in momentul in care se executa un session.getTransaction().commit() sau un session.getTransaction().rollback().

De asemenea, pentru initierea explicita a unei tranzactii se poate apela session.beginTransaction()

Pentru crearea unui SessionFactory dintr-un fisier hibernate.cfg.xml se apeleaza

sessionFactory =
new Configuration().configure().buildSessionFactory();

Exemplu de salvare a unui obiect in baza de date:

Session session = HibernateUtil.getSessionFactory().getCurrentSession();

session.beginTransaction();

Task task = new Task();

task.setName('title');

task.setDescription('this is a task generated by

Hibernate');

session.save(task);

session.getTransaction().commit();

Incarcarea din baza de date:

Hibernate foloseste un limbaj intermediar de query, numit HQL. El este asemanator cu SQL-ul, dar utilizeaza entitatile definite in Hibernate (obiectele mapate) in locul tabelelor din baza de date.

Exemplul urmator prezinta modalitatea de a incarca toate inregistrarile dintr-un tabel (echivalent: toate obiectele de un anume tip serializate)

Session session =

HibernateUtil.getSessionFactory().getCurrentSession();

session.beginTransaction();

List result = session.createQuery('from Task').list();

session.getTransaction().commit();