1

Tecnologie Web T

Accesso Diretto alle Basi di Dati:

Interfacce e Astrazioni

Home Page del corso: http://www-db.disi.unibo.it/courses/TW/

Versione elettronica: 4.01.AccessoDB.pdf

Versione elettronica: 4.01.AccessoDB-2p.pdf

Accesso DB

2

… un piccolo passo indietro

Services

Business Logic

Business Flows

Presentation

I servizi devono fornire tutte le funzionalità base (API) necessarie per

l’implementazione rapida ed efficace della logica di business, dalla gestione

della concorrenza al supporto alle transazioni, dall’interfacciamento ai DB al

monitoraggio/controllo/gestione delle performance

La logica di business contiene le caratteristiche delle applicazioni e

dipende sia dal modello dei dati che, ancora più rilevante, dalle logiche

di utilizzo degli stessi

A questo livello vengono implementati i flussi delle diverse

conversazioni che interagiscono per comporre una applicazione

Livello di presentazione si occupa della visualizzazione dei risultati

generati secondo il percorso definito nel flusso sottostante

Accesso DB

Modello frequente in applicazioni enterprise

3

Gestione della persistenza

Una parte rilevante degli sforzi nello sviluppo di ogni

applicazione distribuita di livello enterprise si concentra sul

layer di persistenza

Accesso, manipolazione e gestione di dati persistenti,

tipicamente mantenuti in un DB relazionale

Il mapping Object/Relational si occupa di risolvere il

potenziale mismatch fra dati mantenuti in un DB relazionale

(table-driven) e il loro processamento fatto da oggetti in

esecuzione

DB relazionali sono progettati per operazioni di query efficienti su

dati di tipo tabellare

Necessità di lavorare invece tramite interazione fra oggetti

Accesso DB

4

… ancora un piccolo passo indietro

Come accedere ai dati di un DB? Dal Corso di Sistemi

Informativi T, sappiamo che è possibile mediante

istruzioni SQL eseguite “interattivamente”

Accesso DB

5

Accesso diretto alle basi di dati (1/2)

È anche possibile (ed è di gran lunga l’accesso a DB più tipico) inserire istruzioni SQL direttamente nel codice di un’applicazione scritta in un linguaggio di programmazione “ospite” (ad es. C, C++, Java, C#)

Esempio Java - JDBC:

System.out.println("Retrieve some data from the

database..");

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");

// display the result set

while (rs.next()) {

String number = rs.getString(1);

String name = rs.getString(2);

System.out.print(" empno= " + number);

System.out.print(" firstname= " + name);

System.out.print("\n");

}

rs.close(); stmt.close();

Accesso DB

6

Accesso diretto alle basi di dati (2/2)

Il problema da risolvere è relativo all’integrazione tra i

comandi SQL, i quali sono responsabili di realizzare

l’accesso al DB, e le normali istruzioni del linguaggio di

programmazione

Due soluzioni possibili:

SQL Embedded o “incastonamento”: SQL viene

integrato direttamente all’interno del normale linguaggio

di programmazione

Es. di soluzione sono i cursori e SQL dinamico

Call Level Interface (CLI): l’integrazione con SQL

avviene tramite l’invocazione di una opportuna libreria di

funzioni

Es. di soluzione sono ODBC, OLE DB, ADO e JDBC

Accesso DB

7

SQL Embedded

L’incastonamento prevede di introdurre direttamente nel

programma sorgente le istruzioni SQL distinguendole dalle

normali istruzioni tramite un opportuno separatore

Lo standard SQL prevede che il codice SQL sia preceduto dalla stringa exec sql e termini con il carattere ';’

Preprocessore che espande i comandi SQL

Cursori: strumento che permette a un programma di

accedere alle righe di una tabella una alla volta; viene

definito su una generica interrogazione

SQL dinamico: permette all’applicazione di definire al

momento dell’esecuzione le interrogazioni SQL da

effettuare sulla base di dati Valore dei parametri (per istruzioni con struttura predefinita)

Forma delle interrogazioni

Accesso DB

8

Call Level Interface

Mette direttamente a disposizione del programmatore una

libreria di funzioni che permettono di interagire con un

DBMS

Rispetto a SQL Embedded, CLI rappresenta uno strumento

più flessibile e meglio integrato con il linguaggio di

programmazione

anche se occorre gestire esplicitamente aspetti che con

SQL Embedded sono demandati direttamente al

preprocessore

Accesso DB

9

Modo d’uso generale CLI

Una applicazione che accede (in lettura e/o scrittura) ad

una sorgente di dati ha bisogno di fare le seguenti

operazioni: 1. aprire una connessione alla sorgente dati

2. inviare attraverso la connessione istruzioni (di interrogazione e

aggiornamento) alla sorgente dati

3. processare i risultati ricevuti dalla sorgente dati in risposta alle istruzioni

inviate

Le nostre sorgenti dati sono DB relazionali, gestiti da un

DBMS

a scelta tra DB2 (consigliato), MySQL, Hsqldb

Ogni DBMS espone una API (Application Program Interface)

Le applicazioni, per noi Java, interagiscono con le API del

DBMS attraverso un driver

Accesso DB

10

Soluzioni CLI

Piattaforma software Microsoft

ODBC (Object DataBase Connectivity ): interfaccia standard che

permette di accedere a DB in qualunque contesto, realizzando

interoperabilità con diverse combinazioni DBMS-Piattaforma-Reti

OLE DB (Object Linking and Embedding for DataBases): soluzione

proprietaria Microsoft che permette ad applicazioni Windows di

accedere a sorgenti dati generiche, non solo relazionali ma anche a

una vasta tipologia di archivi dati, quali caselle di posta elettronica;

basata sul modello a oggetti COM

ADO (ActiveX Data Object): interfaccia proprietaria Microsoft di alto

livello ai sevizi offerti da OLE DB basata su una interfaccia

“record(tupla)-oriented”

JDBC

Soluzione per l’accesso ai dati in Java sviluppata da Sun

Microsystem; controparte di ODBC per Java

Accesso DB

11

Diverse soluzioni a confronto

Nome Descrizione Pro :) Contro :(

SQL

Embedded

Le istruzione SQL sono introdotte

direttamente all’interno del listato

programma, distinte da un

separatore

(exec sql <istruzione> ;)

Cursori per gestire

risultati composti da più

tuple;

SQL dinamico per

ottenere flessibilità

Necessità di un

preprocessore e di un

supporto

“DBMS-piattaforma-

linguaggio-compilatore”

OLE DB/ ADO Soluzione proprietaria Microsoft

che consente, grazie all’uso di

driver specifici, di interfacciare il

linguaggio di programmazione

con il DBMS

Integrato in Windows;

Driver offerti dai maggiori

produttori di DBMS;

Interfacciamento con altri

tipi di dato (documenti,

mailbox, ecc.)

Dialetto SQL ristretto;

Proprietario;

Utilizzato solo su linguag-

gi e su piattaforme

Microsoft

JDBC Interfaccia fra il mondo Java ed i

diversi DBMS. Utilizza dei driver

specifici ma offre anche un ponte

con ODBC (non è vero l’inverso)

Java, multipiattaforma,

codice aperto;

Disponibile per qualsiasi

DBMS grazie anche al

ponte ODBC

Come ODBC richiede la

disponibilità di un driver

offerto dal DBMS

Accesso DB

12

ODBC

Acronimo di Open Database Connectivity

API standard definita da Microsoft nel 1992

Permette l’accesso a dati residenti in DB relazionali in un

contesto eterogeneo e distribuito

Es. DBMS: Access, MySQL, DB2, Oracle, Hsqldb, …

Permette ai programmatori di formulare richieste SQL che

accederanno a dati relativi a DB distinti senza dover

conoscere le interfacce proprietarie di ogni singolo DB

Gestisce richieste SQL convertendole in un formato

comprensibile al particolare DBMS

Accesso DB

13

Architettura ODBC

Application

ODBC Driver

Manager

ODBC Driver

(DBMS/OS/network)

Data Source

Accesso DB

14

JDBC

API Java standard definita da Sun Microsystems nel 1996

Sun assicura che JDBC NON è acronimo di Java Database

Connectivity… ma è un semplice marchio registrato!!

Rappresenta la controparte Java di ODBC

Permette di accedere ai database (locali e remoti) in modo

uniforme

Garantisce accesso ai database in modo indipendente

dalla piattaforma

I driver JDBC sono collezioni di classi Java che

implementano metodi definiti dalle specifiche JDBC

Le classi Java che realizzano funzionalità JDBC sono

contenute nei package java.sql: classi fondamentali

javax.sql: estensioni

Accesso DB

15

JDBC vs ODBC

ODBC non è appropriato per un uso diretto dal linguaggio

Java perché usa interfacce scritte in linguaggio C

Una traduzione da API ‘C’ ODBC a API ‘Java’ non è

raccomandata

Una API Java come JDBC è necessaria per permettere una

soluzione Java “pura”

JDBC è normalmente utilizzato da programmatori Java per

connettersi a DB relazionali

Attraverso un piccolo programma “bridge” è possibile

usare l’interfaccia JDBC per accedere a DB accessibili

via ODBC

Accesso DB

16

Storia di JDBC

Prima distribuzione (jdbc.sql)

JDBC fa parte del pacchetto software JDK a partire dalla

versione 1.1 (pakage java.sql)

Con Java 2, è stato introdotto JDBC 2.0:

Migliorate le funzionalità e i tipi di dato disponibili

Offerto come package opzionale per funzionalità estese

Java SE 8 include JDBC 4.2

Accesso DB

17

Architettura JDBC

Accesso DB

Java Application

JDBC Driver Manager

JDBC/Native

Bridge

DBMS

Native Driver

(DBMS specific)

JDBC/ODBC

Bridge

ODBC Driver

All Java

JDBC/Net Bridge

All Java

JDBC Driver

Network

Server

utilizza le

interfacce

java.sql

e javax.sql

implementano

java.sql e,

in alcuni casi

javax.sql

data

source

Type 2 Type 1 Type 4 Type 3

18

JDBC Driver Manager

Rappresenta il livello di gestione di JDBC e opera tra

l’utente e i driver

Tiene traccia dei driver disponibili e gestisce la creazione

di una connessione tra un DB e il driver appropriato

Accesso DB

19

Driver JDBC

JDBC è un insieme di interfacce; per usare un’interfaccia

è necessario fornire un’implementazione per tutti i metodi

che la compongono

L’insieme delle classi Java che implementano le interfacce

JDBC rappresentano un modulo software chiamato driver

JDBC

Ogni DBMS ha il proprio driver rilasciato dal produttore o

sviluppato da terze parti

Sono i driver che realizzano la vera comunicazione con il

DB

Accesso DB

20

Tipi di driver (1/2)

Sono di quattro tipi e possono essere suddivisi in due

categorie:

2-tier: client colloquiano direttamente con DB

3-tier: client comunicano con un middle-tier che accede

al DB

Accesso DB

21

Tipi di driver (2/2)

Modello 3-tier: affinché l’applicazione possa interagire con

il DB occorre che le chiamate JDBC siano convertite in

chiamate API native (caso JDBC/native bridge) o in

chiamate ODBC (caso JDBC/ODBC bridge) L’utente può utilizzare una API di alto livello (di più semplice

utilizzo) che viene tradotta dal driver in chiamate di basso livello

Non è realmente portabile in quanto richiede l’utilizzo di componenti nativi (specifici dell’ambiente in cui vengono eseguiti)

Modello 2-tier: l’applicazione interagisce direttamente con il DB mediante un opportuno protocollo di rete, per es. TCP/IP (caso driver JDBC middleware, detto anche Net-Driver), oppure mediante un protocollo di rete proprietario (caso driver JDBC Driver) Si appoggia su un ambiente completamente Java

Accesso DB

22

Tipi di driver a confronto

Tipo 1: Ponte JDBC-ODBC Driver

prestazioni scadenti

non indipendente dalla piattaforma

fornito a corredo di JDK

Tipo 2: Ponte JDBC-Native Driver

migliori prestazioni

non indipendente dalla piattaforma

Tipo 3: Driver Java Puro per Accesso a DB via Middleware

client indipendente dalla piattaforma

servizi avanzati (caching)

architettura complessa

Tipo 4: Driver Java Puro per Accesso Diretto a DB

client indipendente dalla piattaforma

buone prestazioni

Per approfondimenti: http://java.sun.com/products/jdbc/driverdesc.html

Accesso DB

23

Schema di uso di JDBC

Accesso a DB con JDBC consiste nel:

Caricare la classe del driver JDBC

Ottenere una connessione dal driver

Eseguire statement SQL

Utilizzare risultati delle query

Chiudere la connessione e rilasciare strutture dati utilizzate per la

gestione del dialogo con il DB

Class.forName(“com.ibm.db2.jcc.DB2Driver").newInstance();

Connection conn =

DriverManager.getConnection("jdbc:db2:MYDB");

Statement stm = conn.createStatement();

ResultSet res = stm.executeQuery("SELECT * FROM MYTABLE");

while (res.next())

{

String col1 = res.getString("MYCOL1");

int col2 = res.getInt("MYCOL2");

}

Accesso DB

24

Interfacce e calssi JDBC

L’API JDBC 4.x core mette a disposizione più di 20 tra

interfacce e classi

Alcune fra le più importanti sono

Driver

DriverManager

Connection

Statement

ResultSet

Accesso DB

25

Interfaccia Driver (1/2)

Rappresenta il punto di partenza per ottenere una connessione a un DBMS

I produttori di driver JDBC implementano l’interfaccia Driver (mediante opportuna classe) affinché possa funzionare con un tipo particolare di DBMS

La classe che implementa Driver può essere considerata

la “factory” per altri oggetti JDBC

ad esempio, oggetti di tipo Connection

Ad esempio, IBM nel suo Driver Java “Puro” implementa

l’interfaccia Driver mediante la classe:

com.ibm.db2.jcc.DB2Driver

Accesso DB

26

Interfaccia Driver (2/2)

È possibile ottenere un’istanza effettiva della classe Driver ricercando la classe con il metodo forName:

Driver d =

Class.forName(“com.ibm.db2.jcc.DB2Driver”).

.newInstance();

Avendo a disposizione un oggetto Driver è possibile ottenere la connessione al database Ogni driver JDBC ha una stringa di connessione che

riconosce nella forma:

jdbc:product_name:database_alias

dove database_alias specifica il DB a cui connettersi nel caso specifico della configurazione DB2 in LAB4:

jdbc:db2://diva.disi.unibo.it:50000/[sample|tw_stud]

Accesso DB

27

Classe DriverManager

Facilita la gestione di oggetti di tipo Driver

Quando un oggetto Driver viene istanziato, esso viene

automaticamente registrato nella classe DriverManager

Ogni applicazione può registrare uno o più driver JDBC

diversi tra loro

Consente la connessione con il DBMS sottostante

Mediante il metodo statico getConnection

Usa il driver opportuno tra quelli registrati

Accesso DB

28

Interfaccia Connection

Un oggetto di tipo Connection rappresenta una connessione attiva con il DB

Il metodo getConnection di DriverManager, se non

fallisce, restituisce un oggetto di tipo Connection

L’interfaccia mette a disposizione una serie di metodi per le

operazioni, tra le quali:

Preparazione di query SQL da inviare tramite oggetti

Statement

PreparedStatement

CallableStatement

Accesso DB

29

Interfaccia Statement

Gli oggetti di tipo Statement possono essere usati per

inviare query SQL semplici verso il DBMS sottostante

query che non fanno uso di parametri

La query può essere di modifica

UPDATE, INSERT, CREATE

oppure di selezione

SELECT

Per query di tipo SELECT il risultato è inserito in un oggetto ResultSet

Un oggetto Statement può essere creato con il metodo

createStatement di Connection

Accesso DB

30

Interfaccia PreparedStatement

Gli oggetti di tipo PreparedStatement possono essere

usati per creare query SQL parametriche (parametri di

tipo IN) e precompilate (“prepared”)

Il valore di ciascun parametro non è specificato nel

momento in cui lo statement SQL è definito, ma

rimpiazzato dal carattere ‘?’

È più efficiente perché una volta che la query è preparata,

sarà pre-compilata per usi futuri

Un oggetto PreparedStatement può essere creato con

il metodo prepareStatement di Connection

Accesso DB

31

Interfaccia CallableStatement

Gli oggetti di tipo CallableStatement possono essere usati per definire query parametriche con parametri di tipo IN, OUT e INOUT

Permettono di eseguire una invocazione a una stored procedure memorizzata sul server DB

Un oggetto CallableStatement può essere creato con

il metodo prepareCall di Connection

Accesso DB

32

Interfaccia ResultSet

L’oggetto ResultSet è il risultato di una query di

selezione (SELECT)

Rappresenta una tabella composta da righe

(gli elementi selezionati) e colonne (gli attributi richiesti)

Nella versione 1.x di JDBC era possibile accedere a un ResultSet obbligatoriamente dal primo elemento

all’ultimo e una sola volta

Da JDBC 4.x si può accedere in modo casuale alle

righe, inserirle, cancellarle e modificarle

Accesso DB

33

JDBC e DB2

DB2 prevede 2 driver JDBC di tipo two-tier:

1. Net-Driver: com.ibm.db2.jcc.DB2Driver

2. Driver Java “puro”: com.ibm.db2.jcc.DB2Driver

entrambi contenuti nel file db2jcc.jar

(direttorio C:\Programmi\IBM\SQLLIB\java

della macchina client del laboratorio)

Il Net-Driver serve per connettersi via rete (ad esempio,

mediante il protocollo di rete TCP/IP) a server remoti – è un

driver universale, ovvero indipendente dalla piattaforma

a condizione che questi abbiano attivo il servizio

DB2 Jdbc Applet Server

Il Driver Java puro permette di connettersi ad istanze DB2

residenti sulla macchina locale o catalogate localmente

Accesso DB

34

Programmare un’applicazione JDBC

1. Importazione package

2. Registrazione driver JDBC

3. Apertura connessione al DB (Connection)

4. Creazione oggetto Statement

5. Esecuzione query e eventuale restituzione oggetto

ResultSet

6. Utilizzo risultati

7. Chiusura oggetto/i ResultSet e oggetto/i Statement

8. Chiusura connessione

Passi principali:

Accesso DB

35

1: Importazione package

Accesso DB

// Questo programma mostra un *semplice esempio* di

// applicazione Java (Esempio.java) in grado di eseguire

// interrogazioni/aggiornamenti sul database DB2 “SAMPLE”

// utilizzando JDBC

//importazione package

import java.sql.*; //package JDBC

36

2: Registrazione driver JDBC

Accesso DB

class Esempio {

public static void main(String argv[]) {

try {

// caricamento e registrazione driver

Class.forName(“com.ibm.db2.jcc.DB2Driver").newInstance();

…

37

3: Apertura connessione DB

Accesso DB

Connection con = null;

// URL jdbc:db2:database_alias

String url = "jdbc:db2://diva.disi.unibo.it:50000/sample";

if (argv.length == 2) {

String userid = argv[0];

String passwd = argv[1];

// connessione con id/passwd forniti dall’utente

con = DriverManager.getConnection(url, userid, passwd);

}

else {

System.out.println("\nUsage: java Esempio username

password\n");

System.exit(0);

}

…

38

4. Creazione oggetto Statement

Accesso DB

// interrogazione table EMPLOYEE

System.out.println("Retrieve some data from the database");

Statement stmt = con.createStatement();

…

39

5. Esecuzione query 6. Utilizzo oggetto ResultSet

Accesso DB

// esegue la query

ResultSet rs = stmt.executeQuery("SELECT * FROM

DB2INST1.EMPLOYEE");

System.out.println("Received results:");

// mostra i risultati

// rs.next() = “false” se non ci sono più righe risultato

while (rs.next()) {

String number = rs.getString(1);

String name = rs.getString(2);

System.out.print(" empno= " + number);

System.out.print(" firstname= " + name);

System.out.print("\n");

}

…

40

7. Chiusura oggetti ResultSet e Statement

Accesso DB

// chiude ResultSet e Statement

rs.close();

stmt.close();

// Esecuzione di altre istruzioni SQL

// aggiorna il database… prova ad aggiornarlo!

System.out.println("\n\nUpdate the database... ");

stmt = con.createStatement();

int rowsUpdated = stmt.executeUpdate("UPDATE

DB2INST1.EMPLOYEE SET firstname = 'SHILI’

WHERE empno = '000010'");

System.out.print("Changed "+rowsUpdated);

if (1 == rowsUpdated)

System.out.println(" row.");

else

System.out.println(" rows.");

stmt.close(); // chiude Statement

…

41

8. Chiusura connessione

Accesso DB

// chiude Connection

con.close();

} // try

catch( Exception e ) {

e.printStackTrace();

}

} // main

} // classe

Scopo di questo semplice esempio didattico è solo quello di evidenziare

l’uso di base delle API JDBC

Come vedremo meglio in laboratorio

connessione, statement, resultSet devono essere sempre “chiusi” dopo

essere stati usati (rilasciamo risorse!!)

Per aver questa garanzia, effettuiamo queste operazioni sempre nella clausola finally

Ogni metodo JDBC “lancia” una eccezione; tali eccezioni possono essere

gestite rilanciando una eccezione di livello logico opportuno

42

Un oggetto Statement fornisce tre metodi per eseguire

una query SQL:

(StatementObj.) executeQuery(stmt SQL), per

statement che generano un unico result set (SELECT)

(StatementObj.) executeUpdate(stmt SQL), per

statement di modifica (UPDATE, INSERT, ecc.)

(StatementObj.) execute(stmt SQL), per statement

che generano più di un risultato o più di un contatore di

aggiornamento

Oggetto Statement

Accesso DB

43

executeQuery

Usato tipicamente per query di tipo SELECT

Restituisce un oggetto ResultSet

Accesso DB

44

executeUpdate

Usato per statement di tipo DML quali INSERT, UPDATE o

DELETE

e per statement di tipo DDL quali CREATE TABLE e DROP

TABLE

Restituisce un intero rappresentante il numero di righe che

sono state inserite/aggiornate/cancellate

contatore di aggiornamento

In caso di statement di tipo DDL, restituisce sempre il

valore 0

Accesso DB

45

execute

Usato quando la query restituisce più di un risultato o più di

un contatore di aggiornamento

Utilizza i seguenti metodi:

(StatementObj.) getResultSet() per ottenere il result

set successivo

(StatementObj.) getUpdateCount() per ottenere il

contatore di aggiornamento successivo

(StatementObj.) getMoreResults() per sapere se ci

sono altri result set o contatori di aggiornamento

Restituisce true se il primo risultato è di tipo ResultSet;

false se il risultato è di tipo Count o non ci sono più risultati

Accesso DB

46

Oggetto ResultSet

Un oggetto ResultSet contiene il risultato di una query SQL

(cioè una tabella)

Un oggetto ResultSet mantiene un cursore alla riga

corrente

Per ottenere un valore relativo alla riga corrente:

(ResultSetObj.) getXXX(column-name)

(ResultSetObj.) getXXX(column-number)

Per spostare il cursore dalla riga corrente a quella

successiva:

(ResultSetObj.) next() (restituisce true in caso di

successo; false se non ci sono più righe nell’insieme

risultato)

Accesso DB

47

getByte

getShort

getInt

getLong

getFloat

getDouble

getBigDecimal

getBoolean

I metodi getxxx

getString

getBytes

getDate

getTime

getTimestamp

getAsciiStream

getUnicodeStream

getBinaryStream

getObject

Accesso DB

Permettono la lettura degli attributi di una tabella

48

Controllo sui valori NULL

I valori NULL SQL sono convertiti in null, 0, o false, dipendentemente dal tipo di metodo getXXX

Per determinare se un particolare valore di un risultato

corrisponde a NULL in JDBC:

Si legge la colonna

Si usa il metodo (ResultSetObject.) wasNull()

Accesso DB

49

Controllo sui tipi di dato

Il dialogo tra una applicazione Java e i DB sottostanti

richiede che i tipi di dato SQL siano mappati in

corrispondenti tipi di dato Java e viceversa. La conversione

(SQL2Java) riguarda tre categorie:

Tipi SQL che hanno diretti equivalenti in Java (es. il tipo SQL INTEGER è equivalente al tipo Java int)

Tipi SQL che possono essere convertiti negli equivalenti

tipi Java (es. i tipi SQL CHAR e VARCHAR possono

essere convertiti nel tipo Java String)

Tipi SQL che sono unici (una minoranza) e che

necessitano la creazione di uno speciale oggetto Java

(es. il tipo SQL DATE si converte nell’oggetto Date definito dall’omonima classe Java)

Accesso DB

50

Mapping SQL2Java

SQL type Java Type

CHAR String

VARCHAR String

LONGVAR

CHAR String

NUMERIC java.math.Big

Decimal

DECIMAL java.math.Big

Decimal

BIT boolean

TINYINT byte

SMALLINT short

INTEGER int

SQL Type Java Type

BIGINT long

REAL float

FLOAT double

DOUBLE double

BINARY byte[]

VARBINARY byte[]

LONGVAR

BINARY byte[]

DATE java.sql.Date

TIME java.sql.Time

TIMESTAMP java.sql.Time

stamp

Accesso DB

51

Oggetto PreparedStatement

Usato quando la query SQL prende uno o più parametri

come input, o quando una query semplice deve essere

eseguita più volte

L’interfaccia PreparedStatement estende l’interfaccia

Statement ereditandone tutte le funzionalità. In più sono

presenti metodi per la gestione dei parametri

L’oggetto viene creato con l’istruzione

Connection.prepareStatement(stmt SQL)

I parametri vengono poi settati mediante il metodo

(StatementObj.) setXXX(n,value)

La query pre-compilata viene eseguita mediante i metodi executeQuery(), executeUpdate()o execute()

senza bisogno di passare alcun parametro!!

Accesso DB

52

PreparedStatement: Esempio

I parametri sono specificati con “?”

Esempio:

PreparedStatement ps = con.prepareStatement(

"UPDATE MyTable SET a = ? WHERE b = ?");

Per istanziare i parametri “?” (necessariamente prima

dell’esecuzione):

ps.setInt(1, 20);

ps.setInt(2,100);

Per eseguire lo statement: int res = ps.executeUpdate();

Accesso DB

53

setByte

setShort

setInt

setLong

setFloat

setDouble

setBigDecimal

setBoolean

setNull

I metodi setxxx

setString

setBytes

setDate

setTime

setTimestamp

setAsciiStream

setUnicodeStream

setBinaryStream

setObject

Accesso DB

Permettono l’assegnamento dei parametri di uno statement

SQL

54

Mapping Java2SQL

Java Type SQL type

String

VARCHAR or

LONG-

VARCHAR

java.math.

BigDecimal NUMERIC

boolean BIT

byte TINYINT

short SMALLINT

int INTEGER

long BIGINT

Java Type SQL type

float REAL

double DOUBLE

byte[] VARBINARY or

LONGVARBINARY

java.sql.Date DATE

java.sql.Time TIME

java.sql.Timestamp TIMESTAMP

Accesso DB

55

Il problema dell’SQL injection

Cosa succede se, data una query con parametri di input

dell’utente (es. tramite interfaccia Web), questi ha la

possibilità di agire direttamente sul valore dell’input di tipo

stringa (oggetto String), aggiungendo, ad esempio, apici e

altre istruzioni di controllo??

Può inserire istruzioni arbitrarie che verranno eseguite

dal DBMS!!!

Esempio:

Accesso DB

Statement =

“SELECT * FROM users WHERE name = ‘“ + userName + ”’;”

con la variabile userName assegnata al valore: a’;DROP TABLES users;

56

SQL injection

È una tecnica che sfrutta la vulnerabilità a livello di

sicurezza dello strato DB di una applicazione

Tale vulnerabilità è presente quando i dati di input

dell’utente sono:

Filtrati in modo incorretto per la verifica di caratteri literal

escape nelle stringhe

Non sono fortemente “tipizzati” e/o controllati i vincoli di

tipo

SQL injection in quanto l'utente può “iniettare” statement

SQL arbitrari con risultati catastrofici:

divulgazione di dati sensibili o esecuzione di codice

Per proteggere le nostre applicazioni dall’SQL injection, i

dati di input dell’utente NON devono essere direttamente

incastonati all’interno di Statement SQL

Accesso DB

57

Prevenire SQL injection

A prevenzione del problema, l’interfaccia

PreparedStatement permette di gestire in modo corretto

anche l’inserimenti di dati “ostili”

Statement “parametrizzati” che permettono di lavorare

con parametri (o variabili bind) invece che di incastonare

i dati di input dell’utente direttamente nello statement

SQL statement è fisso

i dati di input dell’utente sono “assegnati” ai parametri

(binding) mediante l’invocazione dei metodi setXXX(n,value)

Accesso DB

58

La storia del piccolo “Bobby Tables”

(From the comic strip xkcd)

School: "Hi, this is your son's school. We're having some computer trouble."

Mom: "Oh, dear -- Did he break something?"

School: "In a way. Did you really name your son Robert'); DROP TABLE Students;-- ?"

Mom: "Oh. Yes. Little Bobby Tables we call him."

School: "Well, we've lost this year's student records. I hope you're happy."

Mom: "And I hope you've learned to sanitize your database inputs." (Alt-text: "Her daughter is named Help I'm trapped in a driver's license factory.")

C’è solo un modo per evitare attacchi di tipo Bobby Tables Non creare mai statement SQL che includono dati di input utente

Usare sempre chiamate SQL parametrizzate (PreparedStatement)

Accesso DB

59

Riferimenti

Sito Sun - The Java Tutorial http://java.sun.com/docs/books/tutorial/jdbc

/index.html

Sito Sun – Java SE - Java Database Connectivity

(JDBC) http://java.sun.com/javase/technologies/data

base/index.jsp

Per la documentazione relativa ai pakage java.sql e

javax.sql, fare riferimento a Java2Docs

Atzeni, Ceri, Paraboschi, Torlone. “Basi di dati: Modelli

e linguaggi di interrogazione”, McGraw-Hill Italia, 2009

Accesso DB