PPT - Unibo

Basi di Dati e Sistemi
Informativi
SQL per Applicazioni
Home page del corso:
http://www.cs.unibo.it/~difelice/dbsi/
SQL per Applicazioni
L’accesso tipico ad una base di dati avviene
mediante applicazioni integrate nel sistema
informativo.
 Integrazione di SQL in linguaggi di
programmazione ad alto livello.
 Integrazione
di
SQL
applicazioni/interfacce
Web
Information System, WIS)
in
(Web
SQL per Applicazioni
L’integrazione del linguaggio SQL con i
normali linguaggi di programmazione di alto
livello (C, C++, Java, etc) presenta alcuni ostacoli.
 Differenze di sintassi tra i linguaggi.
 Differenze nel meccanismo di gestione dei
dati (SQL e’ set-oriented, mentre i
linguaggi di programmazione sono per lo
piu’ tuple-oriented).
SQL per Applicazioni
Due soluzioni per consentire l’uso di SQL
all’interno di un programma scritto in un
linguaggio di programmazione:
 SQL Embedded  il programma sorgente
contiene codice misto (es. SQL e Java)
 Call Level Interface (CLI)  il
programma sorgente contiene chiamate ad
una libreria per la gestione dei dati (es. JDBC).
SQL per Applicazioni
SQL Embedded Il programma contiene
istruzioni SQL, distinte dalle istruzioni del
linguaggio sorgente attraverso l’uso di
separatori (exec sql e ;)
 In fase di compilazione, il preprocessore
riconosce le istruzioni SQL e le sostituisce
con opportune chiamate di libreria del
DBMS.
SQL per Applicazioni
main() {
int i;
…
exec sql
insert into Impiegati
values(“Marco”,20);
…
}
LIBRERIA
ACCESSO
DBMS
PREPROCESSORE
DBMS
SQL per Applicazioni
Il preprocessore esegue i seguenti task:
 Verifica la sintassi del codice SQL
 Sostituisce il codice SQL con le rispettive
chiamate di libreria del DBMS.
Necessario un preprocessore specifico per
una
specifica
combinazione
DMBSlinguaggio-piattaforma (es. Oracle/C/Linux).
SQL per Applicazioni
#include<stdlib.h>
main(){
exec sql begin declare section;
char *NomeDip = "Manutenzione";
char *CittaDip = "Pisa";
int NumeroDip = 20;
exec sql end declare section;
exec sql connect to utente@librobd;
if (sqlca.sqlcode != 0) {
printf("Connessione al DB non riuscita\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:CittaDip,:NumeroDip);
exec sql disconnect all;
}
}
SQL per Applicazioni
#include<stdlib.h>
main(){
exec sql begin declare section;
char *NomeDip = "Manutenzione";
char *CittaDip = "Pisa";
Variabili C condivise
int NumeroDip = 20;
exec sql end declare section;
exec sql connect to utente@librobd;
if (sqlca.sqlcode != 0) {
printf("Connessione al DB non riuscita\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:CittaDip,:NumeroDip);
exec sql disconnect all;
}
}
SQL per Applicazioni
#include<stdlib.h>
main(){
exec sql begin declare section;
char *NomeDip = "Manutenzione";
char *CittaDip = "Pisa";
int NumeroDip = 20;
exec sql end declare section;
Struttura dati condivisa
exec sql connect to utente@librobd;
if (sqlca.sqlcode != 0) {
printf("Connessione al DB non riuscita\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:CittaDip,:NumeroDip);
exec sql disconnect all;
}
}
SQL per Applicazioni
#include<stdlib.h>
main(){
exec sql begin declare section;
char *NomeDip = "Manutenzione";
char *CittaDip = "Pisa";
int NumeroDip = 20;
exec sql end declare section;
exec sql connect to utente@librobd;
if (sqlca.sqlcode != 0) {
printf("Connessione al DB non riuscita\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:CittaDip,:NumeroDip);
exec sql disconnect all;
}
Esecuzione della query
}
SQL per Applicazioni
CODICE SORGENTE
int main() {
exec sql connect to universita
user pguser identified by pguser;
exec sql create table studente
(matricola integer primary key,
nome varchar(20),
annodicorso integer);
exec sql disconnect;
return 0;
}
SQL per Applicazioni
#include <ecpgtype.h>
#include <ecpglib.h>
CODICE PRE-COMPILATO
#include <ecpgerrno.h>
#include <sqlca.h>
int main() {
ECPGconnect(__LINE__, "universita" , "pguser" ,
"pguser" , NULL, 0);
ECPGdo(__LINE__, NULL, "create table studente ( matricola integer
primary key , nome varchar ( 20 ) , annodicorso integer )",
ECPGt_EOIT, ECPGt_EORT);
ECPGdisconnect(__LINE__, "CURRENT");
return 0;
}
SQL per Applicazioni
Conflitto d’impedenza sui dati:
 SQL e’ un linguaggio set-oriented,
un’interrogazione puo’ restituire come
risultato un’intera tabella (es. SELECT).
 I linguaggi di programmazione accedono
agli elementi di una struttura dati
scandendo le righe una per volta.
SOLUZIONE: Usare i cursori …
SQL per Applicazioni
Cursore  Iteratore per accedere alla righe
di una tabella una alla volta.
BUFFER del
PROGRAMMA
CODICE
SORGENTE
DBMS
Il cursore puo’ muoversi avanti/indietro sulle righe della tabella.
SQL per Applicazioni
 Definizione del cursore
declare
NomeCursore
cursor for Select SQLCode
[scroll]
declare NomeCursore scroll cursor
for SELECT Cognome, Stipendio
FROM Impiegati
WHERE (Stipendio > 1000)
SQL per Applicazioni
 Esecuzione delle interogazione
open NomeCursore
 Deallocazione delle interogazione
close NomeCursore
 Recupero dei risultati
fetch NomeCursore into ListaVariabili
SQL per Applicazioni
void VisualizzaStipendiDipart(char NomeDip[])
{
char Nome[20], Cognome[20];
long int Stipendio;
$ declare ImpDip cursor for
select Nome, Cognome, Stipendio
from Impiegato
where Dipart = :NomeDip;
printf("Dipartimento %s\n",NomeDip);
$ open ImpDip;
$ fetch ImpDip into :Nome, :Cognome, :Stipendio;
while (sqlcode == 0)
{
printf("Attuale stipendio: %d\n",Stipendio);
$ fetch ImpDip into :Nome, :Cognome,
:Stipendio;
}
$ close cursor ImpDip;
}
SQL per Applicazioni
SQLJ  Tecnica per includere codice SQL
all’interno di programmi Java.
import java.sql.*;
public class SimpleDemoSQLJ {
public Address getEmployeeAddress(int empno)
throws SQLException
{
Address addr;
#sql { SELECT office_addr INTO :addr FROM employees
WHERE empnumber = :empno };
return addr;
}
}
SQL per Applicazioni
Tecnica vista fin qui: Static SQL Embedded.
 PROBLEMA: Non sempre le istruzioni SQL
da eseguire sono note quando si scrive il
programma …
 Dynamic SQL Embedded  Tecnica che
permette di eseguire istruzioni SQL
costruite dal programma (o addirittura ricevute
dal programma attraverso parametri o da input).
SQL per Applicazioni
Due soluzioni per consentire l’uso di SQL
all’interno di un programma scritto in un
linguaggio di programmazione:
 SQL Embedded  il programma sorgente
contiene codice misto (es. SQL e Java)
 Call Level Interface (CLI)  il
programma sorgente contiene chiamate ad
una libreria per la gestione dei dati (es. JDBC).
SQL per Applicazioni
Nella modalita’ CLI, il linguaggio sorgente
offre una libreria specifica per interagire con
un DBMS (indipendente dal DBMS).
INTERAZIONE
1. Si crea una connessione con il DBMS.
2. Si invia un comando SQL con la richiesta.
3. Si riceve la risposta dal DBMS, e la si gestisce
per mezzo di cursori.
4. Si chiude la connessione con il DBMS.
SQL per Applicazioni
Sono disponibili diverse tecnologie di CLI
per piattaforme/linguaggi differenti…
Tecnologia
Piattaforma
ODBC
Interfaccia standard, multi-piattaforma
OLE DB
Microsoft, basata sul modello .COM
ADO
Microsoft, interfaccia record-oriented
ADO.NET
Microsoft, estensione ADO per piattaforma
.NET
JDBC
Interfaccia Java, multi-piattaforma
SQL per Applicazioni
Java Database Connectivity (JDBC) 
insieme di librerie Java per accesso ai dati di
un database relazionale, indipendenti dallo
specifico DBMS in uso.
 JDBC
denota
un’interfaccia
implementata da classi dette driver.
standard,
 Un driver e’ specifico di un certo DBMS (es.
MySQL Connector/J per MySQL)
SQL per Applicazioni
JDBC puo’ essere usato in 4 architetture:
1. Bridge JDBC-ODBC: si richiama un driver ODBC, che
deve essere disponibile sul client.
2. Driver nativo sul client: si richiama un componente
proprietario (non necessariamente Java) sul client.
3. Driver puro Java con server intermedio ("middleware
server"): si comunica via protocollo di rete con il server
intermedio, che non deve risiedere sul client.
4. Driver puro Java, con connessione al DBMS: si
interagisce direttamente con il DBMS.
SQL per Applicazioni
SQL per Applicazioni
 Vantaggi di SQLJ su JDBC:
 SQLJ richiede meno codice.
 La sintassi delle istruzioni SQL puo’ essere
controllata durante la compilazione
 Vantaggi di JDBC su SQLJ:
 SQLJ richiede un preprocessore, non sempre
disponibile su tutte le piattaforme/DBMS.
SQL per Applicazioni
Le API di JDBC (java.sql) consentono di:
 Creare la connessione con un DBMS
(supponendo di aver gia’ installato i driver).
 Eseguire query SQL sul DBMS.
 Processare il risultato della query SQL.
 Gestire transazioni ed operazioni batch.
SQL per Applicazioni
Per creare una connessione, sono disponibili le
seguenti classi:
 Driver  interfaccia di collegamento verso il
DBMS (implementato da ogni DBMS).
 DriverManager  servizio di base per la
gestione dei drivers attualmente presenti.
 Connection  sessione di connessione ad uno
specifico DBMS, il cui driver e’ gestito dal
DriverManager.
SQL per Applicazioni
Supponendo di avere i driver del DBMS (es.
MySQL) installati sul proprio host, il passo
successivo e’ creare la connessione dal
DriverManager.
static Connection getConnection(String
url, String user, String password)
 url  URL del DB, tipo e nome.
 user, password  credenziali d’accesso.
SQL per Applicazioni
Supponendo di avere i driver del DBMS (es.
MySQL) installati sul proprio host, il passo
successivo e’ creare la connessione dal
DriverManager.
String
url=“jdbc:mysql://localhost:3306/provadb”;
String user=“root”;
String password=“root”;
Connection
con=DriverManager.getConnection(url,
user,
password);
SQL per Applicazioni
Per eseguire una query e processarne il risultato,
sono disponibili le seguenti classi:
 Connection  sessione di connessione ad uno
specifico DBMS, il cui driver e’ gestito dal
DriverManager.
 Statement  componente usata per eseguire
una query SQL su una Connection.
 ResultSet  tabella di dati che contiene il
risultato di esecuzione di uno Statement.
SQL per Applicazioni
Una volta creata la Connection, e’ possibile creare
uno Statement associato, e quindi eseguire una
query sul DBMS.
Connection
con=DriverManager.getConnection(url,
password);
Statement state= con.createStatement();
String sql=“ SELECT * FROM IMPIEGATI”;
ResultSet rs=state.executeQuery(sql);
user,
SQL per Applicazioni
Per gestire il risultato di una query, si utilizza la
classe ResultSet  tabella di dati generata
dall’esecuzione di una query SQL + cursore.
 ResultSet mantiene un puntatore alla riga
corrente della tabella.
 Tramite il metodo next() e’ possibile spostarsi
alla riga successiva; next() ritorna falso quando
non ci sono piu’ righe nel ResultSet.
SQL per Applicazioni
Per gestire il risultato di una query, si utilizza la
classe ResultSet  tabella di dati generata
dall’esecuzione di una query SQL + cursore.
 ResultSet mantiene un puntatore alla riga
corrente della tabella.
 Tramite il metodo next() e’ possibile spostarsi
alla riga successiva; next() ritorna falso quando
non ci sono piu’ righe nel ResultSet.
SQL per Applicazioni
Data una riga del ResultSet, si possono utilizzare
metodi tipizzati per accedere ad una specifica
colonna, conoscendone l’indice:






String getString(int columnIndex)
boolean getBoolean(int columnIndex)
int getInt(int columnIndex)
float getFloat(int columnIndex)
Date getDate(int columnIndex)
…
SQL per Applicazioni
Data una riga del ResultSet, si possono utilizzare
metodi tipizzati per accedere ad una specifica
colonna, conoscendone l’indice:
Statement st=con.createStatement();
ResultSet
rs=st.executeQuery(“SELECT
NOME,
STIPENDIO FROM IMPIEGATI”);
while(rs.next) {
System.out.println(rs.getString(1));
System.out.println(rs.getInt());
}
SQL per Applicazioni
Eseguire una query senza un controllo preventivo
sui parametri forniti dall’utente puo’ essere
pericoloso dal punto di vista della sicurezza (es.
SQL Injection); per questo, si preferisce:
 Preparare lo Statement inserendo dei
placeholder al posto dei parametri della query.
 Fornire i valori dei parametri, ed eseguire la
query (in maniera sicura).
SQL per Applicazioni
Esempio di esecuzione di una query SQL mediante
PreparedStatement (anziche’ Statement).
PreparedStatement pst;
String author=“Michele”;
pst=con.PreparedStatement(“INSERT
IMPIEGATI(Nome) VALUES(?)”);
pst.setString(1, author);
pst.executeUpdate();
INTO
SQL per Applicazioni
In Java JDBC, una transazione indica un insieme
di comandi SQL cui puo’ essere associata una
modalita’ di esecuzione del “tutto o niente”, ossia:
 Tutti i comandi della transazione sono stati
eseguiti correttamente, OPPURE:
 In caso di errori, e’ possibile fare rollback
dell’esecuzione di tutti comandi della
transazione.
SQL per Applicazioni
Per costruire una transazione in JDBC:
 Si costruiscono le query, e si eseguono mediante
executeUpdate() o executeQuery().
 Si utilizza il metodo commit() del Connection
per eseguire le operazioni sul DBMS in maniera
persistente.
 Si utilizza il metodo rollback() del
Connection per annullare le operazioni sul
DBMS eseguite dopo la precedente commit.
SQL per Applicazioni
try {
st.executeupdate(“UPDATE IMPIEGATO
Name=“Marco” WHERE Name=“Michele”);
SET
st.executeupdate(“UPDATE IMPIEGATO
Name=“Mario” WHERE Name=“Giovanni”);
SET
con.commit();
}
catch(SQLException) {
con.rollback();
}
SQL per Applicazioni
Per poter lavorare con le transazioni in JDBC, e’
necessario impostare a false la modalita’ di
autocommit dei comandi SQL.
Connection con=DriverManager(url. user,
password);
con.setAutoCommit(false);
 Se si utilizza l’autocommit, non e’ possibile fare
rollback delle transazioni …
SQL per Applicazioni
Nel caso si debbano eseguire operazioni di
DELETE, UPDATE, INSERT in serie, e’ possibile
usare il meccanismo dei batch update in JDBC:
 Le query sono raggruppate in un unica query,
che viene inviata al DMBS (vantaggi in
terminimi di efficienza di esecuzione).
 Il batch restituisce un array con il numero di
righe affette da ciascuna query.
SQL per Applicazioni
Nel caso si debbano eseguire operazioni di
DELETE, UPDATE, INSERT in serie, e’ possibile
usare il meccanismo dei batch update in JDBC:
 addBatch(String sql)  aggiunge una
query SQL all’insieme batch.
 executeBatch()  esegue il batch update,
ritorna un array di dimensione pari alle query
SQL, con il numero di righe affette da ciascuna.
SQL per Applicazioni
st=con.createStatement();
st.addBatch(“DELETE FROM Impiegati”);
st.addBatch(“INSERT INTO IMPIEGATI(Nome)
VALUES(‘Marco’”);
st.addBatch(“INSERT INTO IMPIEGATI(Nome)
VALUES(‘Michele’”);
int counts=st.executeBatch();
con.commit();
SQL per Applicazioni
st=con.createStatement();
st.addBatch(“DELETE FROM Impiegati”);
st.addBatch(“INSERT INTO IMPIEGATI(Nome)
VALUES(‘Marco’”);
st.addBatch(“INSERT INTO IMPIEGATI(Nome)
VALUES(‘Michele’”);
int counts=st.executeBatch();
con.commit();
SQL per Applicazioni
In un DBMS, le informazioni sul modello logico di
rappresentazione di una tabella si dicono metadati,
e sono rappresentati a sua volta tramite tabelle.





Nomi delle tabelle
Nomi degli attributi di una tabella
Tipi degli attributi
Vincoli relazionali
…
SQL per Applicazioni
In JDBC, e’ possibile accedere al campo metadati
di un ResultSet tramite il metodo getMetaData().
PreparedStatement pst;
pst=con.PreparedStatement(“SELECT
Nome,
Cognome FROM IMPIEGATI”);
ResultSet rs=pst.executeQuery();
ResultSetMetaData meta=pst.getMetaData();
String column1=meta.getColumnName(1);
String column2=meta.getColumnName(2);