Basi di dati e sistemi informativi
This presentation is the property of its rightful owner.
Sponsored Links
1 / 49

Basi di Dati e Sistemi Informativi PowerPoint PPT Presentation


  • 88 Views
  • Uploaded on
  • Presentation posted in: General

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 .

Download Presentation

Basi di Dati e Sistemi Informativi

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Basi di dati e sistemi informativi

Basi di Dati e SistemiInformativi

SQL per Applicazioni

Home page del corso:

http://www.cs.unibo.it/~difelice/dbsi/


Sql per applicazioni

SQL per Applicazioni

  • L’accessotipico ad una base di datiavvienemedianteapplicazioni integrate nelsistemainformativo.

  • Integrazione di SQL in linguaggi di programmazione ad alto livello.

  • Integrazione di SQL in applicazioni/interfacce Web (Web Information System, WIS)


Sql per applicazioni1

SQL per Applicazioni

  • L’integrazione del linguaggio SQL con inormalilinguaggi di programmazione di alto livello(C, C++, Java, etc) presentaalcuniostacoli.

  • Differenze di sintassitrailinguaggi.

  • Differenzenelmeccanismo di gestionedeidati (SQL e’ set-oriented, mentreilinguaggi di programmazionesono per lo piu’ tuple-oriented).


Sql per applicazioni2

SQL per Applicazioni

  • Due soluzioniper consentirel’uso di SQL all’interno di un programmascritto in un linguaggio di programmazione:

  • SQL Embedded  ilprogrammasorgentecontienecodicemisto(es. SQL e Java)

  • Call Level Interface (CLI)  ilprogrammasorgentecontienechiamate ad unalibreria per la gestionedeidati(es. JDBC).


Sql per applicazioni3

SQL per Applicazioni

  • SQL Embedded Il programmacontieneistruzioni SQL, distintedalleistruzioni del linguaggiosorgenteattraversol’uso di separatori (exec sqle ;)

  • In fase di compilazione, ilpreprocessorericonosce le istruzioni SQL e le sostituisce con opportune chiamate di libreria del DBMS.


Sql per applicazioni4

SQL per Applicazioni

main() {

inti;

exec sql

insert into Impiegati

values(“Marco”,20);

}

PREPROCESSORE

CHIAMATA LIBRERIA

LIBRERIA

ACCESSO

DBMS

DBMS


Sql per applicazioni5

SQL per Applicazioni

  • Il preprocessoreesegueiseguentitask:

  • Verifica la sintassidel codice SQL

  • Sostituisceilcodice SQL con le rispettivechiamate di libreria del DBMS.

  • Necessario un preprocessorespecificoper unaspecificacombinazioneDMBS-linguaggio-piattaforma(es. Oracle/C/Linux).


Sql per applicazioni6

SQL per Applicazioni

#include<stdlib.h>

main(){

execsqlbegindeclaresection;

char *NomeDip = "Manutenzione";

char *CittaDip = "Pisa";

intNumeroDip = 20;

execsql end declaresection; execsqlconnect to [email protected]; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; }

}

C con SQL Embedded


Sql per applicazioni7

SQL per Applicazioni

#include<stdlib.h>

main(){

execsqlbegindeclaresection;

char *NomeDip = "Manutenzione";

char *CittaDip = "Pisa";

intNumeroDip = 20;

execsql end declaresection;execsqlconnect to [email protected]; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; }

}

C con SQL Embedded

Variabili C condivise


Sql per applicazioni8

SQL per Applicazioni

#include<stdlib.h>

main(){

execsqlbegindeclaresection;

char *NomeDip = "Manutenzione";

char *CittaDip = "Pisa";

intNumeroDip = 20;

execsql end declaresection; execsqlconnect to [email protected]; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; }

}

C con SQL Embedded

Strutturadaticondivisa


Sql per applicazioni9

SQL per Applicazioni

#include<stdlib.h>

main(){

execsqlbegindeclaresection;

char *NomeDip = "Manutenzione";

char *CittaDip = "Pisa";

intNumeroDip = 20;

execsql end declaresection; execsqlconnect to [email protected]; if (sqlca.sqlcode != 0) {printf("Connessione al DB non riuscita\n"); } else {execsqlinsertinto Dipartimentovalues(:NomeDip,:CittaDip,:NumeroDip); execsqldisconnectall; }

}

C con SQL Embedded

Esecuzionedella query


Sql per applicazioni10

SQL per Applicazioni

CODICE SORGENTE

intmain() {

execsqlconnect to universita

userpguseridentified by pguser;

execsql create table studente

(matricola integerprimarykey,

nome varchar(20),

annodicorsointeger);

execsqldisconnect;

return 0;

}


Sql per applicazioni11

SQL per Applicazioni

#include <ecpgtype.h>

#include <ecpglib.h>

#include <ecpgerrno.h>

#include <sqlca.h>

intmain() {

ECPGconnect(__LINE__, "universita" , "pguser" , "pguser" , NULL, 0);

ECPGdo(__LINE__, NULL, "create table studente ( matricola integerprimarykey , nome varchar ( 20 ) , annodicorsointeger )", ECPGt_EOIT, ECPGt_EORT);

ECPGdisconnect(__LINE__, "CURRENT");

return 0;

}

CODICE PRE-COMPILATO


Sql per applicazioni12

SQL per Applicazioni

  • Conflittod’impedenzasui dati:

  • SQL e’ un linguaggioset-oriented, un’interrogazionepuo’ restituire come risultatoun’interatabella(es. SELECT).

  • I linguaggi di programmazioneaccedonoaglielementi di unastrutturadatiscandendo le righeuna per volta.

  • SOLUZIONE: Usareicursori …


Sql per applicazioni13

SQL per Applicazioni

Cursore Iteratore per accedereallarighe di unatabellaunaallavolta.

BUFFER del

PROGRAMMA

CODICE

SORGENTE

CURSORE

DBMS

SELECT

Il cursorepuo’ muoversiavanti/indietrosullerighedellatabella.


Sql per applicazioni14

SQL per Applicazioni

  • Definizione del cursore

  • declareNomeCursore [scroll] cursor for SelectSQLCode

  • declare NomeCursore scroll cursor for SELECT Cognome, Stipendio

  • FROM Impiegati

  • WHERE (Stipendio > 1000)


Sql per applicazioni15

SQL per Applicazioni

  • Esecuzionedelleinterogazione

  • openNomeCursore

  • Deallocazionedelleinterogazione

  • closeNomeCursore

  • Recuperodeirisultati

  • fetchNomeCursoreinto ListaVariabili


Sql per applicazioni16

SQL per Applicazioni

voidVisualizzaStipendiDipart(charNomeDip[])

{

char Nome[20], Cognome[20];

long int Stipendio;

$ declareImpDipcursor for

select Nome, Cognome, Stipendio

from Impiegato

whereDipart = :NomeDip;

printf("Dipartimento %s\n",NomeDip);

$ open ImpDip;

$ fetchImpDipinto :Nome, :Cognome, :Stipendio;

while (sqlcode == 0)

{

printf("Attuale stipendio: %d\n",Stipendio);

$ fetchImpDipinto :Nome, :Cognome, :Stipendio;

}

$ closecursorImpDip;

}


Sql per applicazioni17

SQL per Applicazioni

SQLJ Tecnica per includerecodice SQL all’interno di programmi Java.

import java.sql.*;

public class SimpleDemoSQLJ{

public Address getEmployeeAddress(intempno)

throws SQLException

{

Address addr;

#sql { SELECT office_addr INTO :addr FROM employees

WHERE empnumber = :empno };

return addr;

}

}


Sql per applicazioni18

SQL per Applicazioni

  • Tecnica vista fin qui: Static SQL Embedded.

  • PROBLEMA: Non sempre le istruzioni SQL da eseguiresono note quandosiscriveilprogramma …

  • Dynamic SQL Embedded  Tecnicache permette di eseguire istruzioni SQL costruite dal programma (o addirittura ricevute dal programma attraverso parametri o da input).


Sql per applicazioni19

SQL per Applicazioni

  • Due soluzioniper consentirel’uso di SQL all’interno di un programmascritto in un linguaggio di programmazione:

  • SQL Embedded  ilprogrammasorgentecontienecodicemisto(es. SQL e Java)

  • Call Level Interface (CLI)  ilprogrammasorgentecontienechiamate ad unalibreria per la gestionedeidati(es. JDBC).


Sql per applicazioni20

SQL per Applicazioni

  • Nellamodalita’ CLI, illinguaggiosorgenteoffreunalibreriaspecifica per interagire con un DBMS (indipendente dal DBMS).

  • Si creaunaconnessione con il DBMS.

  • Si invia un comando SQL con la richiesta.

  • Si riceve la risposta dal DBMS, e la sigestisce per mezzo di cursori.

  • Si chiude la connessione con il DBMS.

INTERAZIONE


Sql per applicazioni21

SQL per Applicazioni

Sonodisponibilidiverse tecnologie di CLI per piattaforme/linguaggidifferenti…


Sql per applicazioni22

SQL per Applicazioni

  • Java Database Connectivity (JDBC)  insieme di librerie Java per accessoaidati di un database relazionale, indipendentidallospecifico DBMS in uso.

  • JDBC denotaun’interfaccia standard, implementata da classidettedriver.

  • Un driver e’ specifico di un certo DBMS (es. MySQL Connector/J per MySQL)


Sql per applicazioni23

SQL per Applicazioni

JDBC puo’ essereusato in 4 architetture:

Bridge JDBC-ODBC: sirichiamaun driver ODBC, chedeveesseredisponibilesulclient.

Driver nativosul client: sirichiamaun componenteproprietario(non necessariamente Java) sulclient.

Driver puro Java con server intermedio("middleware server"): sicomunicavia protocollo di rete con il server intermedio, che non deverisiederesulclient.

Driver puro Java, con connessione al DBMS: siinteragiscedirettamente con ilDBMS.


Sql per applicazioni24

SQL per Applicazioni


Sql per applicazioni25

SQL per Applicazioni

  • Vantaggi di SQLJ su JDBC:

  • SQLJ richiedemenocodice.

  • La sintassidelleistruzioni SQL puo’ esserecontrollatadurante la compilazione

  • Vantaggi di JDBC su SQLJ:

  • SQLJ richiede un preprocessore, non sempredisponibilesututte le piattaforme/DBMS.


Sql per applicazioni26

SQL per Applicazioni

  • Le API di JDBC (java.sql) consentono di:

  • Creare la connessionecon un DBMS (supponendo di aver gia’ installatoi driver).

  • Eseguirequery SQL sul DBMS.

  • Processareilrisultatodella query SQL.

  • Gestiretransazioniedoperazioni batch.


Sql per applicazioni27

SQL per Applicazioni

  • Per creareunaconnessione, sonodisponibili le seguenticlassi:

  • Driver interfaccia di collegamento verso il DBMS (implementato da ogni DBMS).

  • DriverManager servizio di base per la gestionedei drivers attualmentepresenti.

  • Connection  sessione di connessione ad unospecifico DBMS, il cui driver e’ gestito dal DriverManager.


Sql per applicazioni28

SQL per Applicazioni

  • Supponendo di avereidriver del DBMS (es. MySQL) installatisulproprio host, ilpassosuccessivo e’ creare la connessione dal DriverManager.

  • static Connection getConnection(String url, String user, String password)

  • url URL del DB, tipo e nome.

  • user, password  credenzialid’accesso.


Sql per applicazioni29

SQL per Applicazioni

Supponendo di avereidriver del DBMS (es. MySQL) installatisulproprio host, ilpassosuccessivo 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 applicazioni30

SQL per Applicazioni

  • Per eseguireuna query e processarneilrisultato, sonodisponibili le seguenticlassi:

  • Connection  sessione di connessione ad unospecifico DBMS, il cui driver e’ gestito dal DriverManager.

  • Statement  componenteusata per eseguireuna query SQL suunaConnection.

  • ResultSet  tabella di datichecontieneilrisultato di esecuzione di unoStatement.


Sql per applicazioni31

SQL per Applicazioni

Unavoltacreata la Connection, e’ possibilecreareunoStatementassociato, e quindieseguireuna querysul DBMS.

Connection con=DriverManager.getConnection(url, user, password);

Statement state= con.createStatement();

String sql=“ SELECT * FROM IMPIEGATI”;

ResultSetrs=state.executeQuery(sql);


Sql per applicazioni32

SQL per Applicazioni

  • Per gestireilrisultato di una query, siutilizza la classeResultSet tabella di datigeneratadall’esecuzione di una query SQL + cursore.

  • ResultSetmantiene un puntatoreallarigacorrentedellatabella.

  • Tramiteilmetodonext() e’ possibilespostarsiallarigasuccessiva; next()ritornafalsoquando non ci sonopiu’ righenelResultSet.


Sql per applicazioni33

SQL per Applicazioni

  • Per gestireilrisultato di una query, siutilizza la classeResultSet tabella di datigeneratadall’esecuzione di una query SQL + cursore.

  • ResultSetmantiene un puntatoreallarigacorrentedellatabella.

  • Tramiteilmetodonext() e’ possibilespostarsiallarigasuccessiva; next()ritornafalsoquando non ci sonopiu’ righenelResultSet.


Sql per applicazioni34

SQL per Applicazioni

  • Data unariga del ResultSet, sipossonoutilizzaremetoditipizzatiper accedere ad unaspecificacolonna, conoscendonel’indice:

  • String getString(intcolumnIndex)

  • booleangetBoolean(intcolumnIndex)

  • intgetInt(intcolumnIndex)

  • float getFloat(intcolumnIndex)

  • Date getDate(intcolumnIndex)


Sql per applicazioni35

SQL per Applicazioni

Data unariga del ResultSet, sipossonoutilizzaremetoditipizzatiper accedere ad unaspecificacolonna, conoscendonel’indice:

Statement st=con.createStatement();

ResultSetrs=st.executeQuery(“SELECT NOME, STIPENDIO FROM IMPIEGATI”);

while(rs.next) {

System.out.println(rs.getString(1));

System.out.println(rs.getInt());

}


Sql per applicazioni36

SQL per Applicazioni

  • Eseguireuna query senza un controllopreventivo sui parametrifornitidall’utentepuo’ esserepericoloso dal punto di vista dellasicurezza (es. SQL Injection); per questo, sipreferisce:

  • Preparare lo Statementinserendodeiplaceholder al postodeiparametridella query.

  • Fornireivalorideiparametri, edeseguire la query (in manierasicura).


Sql per applicazioni37

SQL per Applicazioni

Esempio di esecuzionedi una query SQL mediantePreparedStatement (anziche’ Statement).

PreparedStatementpst;

String author=“Michele”;

pst=con.PreparedStatement(“INSERT INTO IMPIEGATI(Nome) VALUES(?)”);

pst.setString(1, author);

pst.executeUpdate();


Sql per applicazioni38

SQL per Applicazioni

  • In Java JDBC, unatransazioneindica un insieme di comandi SQL cui puo’ essereassociataunamodalita’ di esecuzione del “tutto o niente”, ossia:

  • Tuttiicomandidellatransazionesonostatieseguiticorrettamente, OPPURE:

  • In caso di errori, e’ possibile fare rollbackdell’esecuzione di tutticomandidellatransazione.


Sql per applicazioni39

SQL per Applicazioni

  • Per costruireunatransazione in JDBC:

  • Si costruiscono le query, e sieseguonomedianteexecuteUpdate() o executeQuery().

  • Si utilizzailmetodocommit() del Connection per eseguire le operazionisul DBMS in manierapersistente.

  • Si utilizzailmetodorollback() del Connectionper annullarele operazionisulDBMSeseguitedopo la precedente commit.


Sql per applicazioni40

SQL per Applicazioni

try {

st.executeupdate(“UPDATE IMPIEGATO SET Name=“Marco” WHERE Name=“Michele”);

st.executeupdate(“UPDATE IMPIEGATO SET Name=“Mario” WHERE Name=“Giovanni”);

con.commit();

}

catch(SQLException) {

con.rollback();

}


Sql per applicazioni41

SQL per Applicazioni

  • Per poterlavorare con le transazioni in JDBC, e’ necessarioimpostare a false la modalita’ di autocommitdeicomandi SQL.

  • Connection con=DriverManager(url. user, password);

  • con.setAutoCommit(false);

  • Se siutilizzal’autocommit, non e’ possibile fare rollback delletransazioni …


Sql per applicazioni42

SQL per Applicazioni

  • Nelcasosidebbanoeseguireoperazioni di DELETE, UPDATE, INSERT in serie, e’ possibileusareilmeccanismodeibatch update in JDBC:

  • Le query sonoraggruppate in un unica query, chevieneinviata al DMBS (vantaggi in terminimi di efficienza di esecuzione).

  • Il batch restituisce un array con ilnumero di righeaffette da ciascuna query.


Sql per applicazioni43

SQL per Applicazioni

  • Nelcasosidebbanoeseguireoperazioni di DELETE, UPDATE, INSERT in serie, e’ possibileusareilmeccanismodeibatch update in JDBC:

  • addBatch(String sql)  aggiungeuna query SQL all’insieme batch.

  • executeBatch() esegueil batch update, ritorna un array di dimensioneparialle query SQL, con ilnumero di righeaffette da ciascuna.


Sql per applicazioni44

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 applicazioni45

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’”);

intcounts=st.executeBatch();

con.commit();


Sql per applicazioni46

SQL per Applicazioni

  • In un DBMS, le informazionisulmodellologico di rappresentazione di unatabellasidiconometadati, e sonorappresentati a suavoltatramitetabelle.

  • Nomi delletabelle

  • Nomi degliattributi di unatabella

  • Tipi degliattributi

  • Vincolirelazionali

ESEMPI DI METADATI


Sql per applicazioni47

SQL per Applicazioni

In JDBC, e’ possibileaccedere al campo metadati

di un ResultSettramiteilmetodogetMetaData().

PreparedStatementpst;

pst=con.PreparedStatement(“SELECT Nome, Cognome FROM IMPIEGATI”);

ResultSetrs=pst.executeQuery();

ResultSetMetaData meta=pst.getMetaData();

String column1=meta.getColumnName(1);

String column2=meta.getColumnName(2);


  • Login