DBMS & SQL: java.sql.*
Docente: Gabriele Lombardi
[email protected]
© 2010 - CEFRIEL
The present original document was produced by CEFRIEL and the Teacher for the benefit and internal use of this
course, and nobody else may claim any right or paternity on it. No right to use the document for any purpose other than
the Intended purpose and no right to distribute, disclose, release, furnish or disseminate it or a part of it in any way or
form to anyone without the prior express written consent of CEFRIEL and the Teacher.
© copyright Cefriel and the Teacher-Milan-Italy-23/06/2008. All rights reserved in accordance with rule of law and
international agreements.
© 2010 - CEFRIEL
Sommario
SLIDE
CONTENUTO
Introduzione
Il problema, le astrazioni
Un esempio pratico
Creiamo un DB di esempio con Derby
JDBC
Architettura, accedere a un DBMS, drivers
Query in java.sql.*
Query native, parametriche, tipi di dato parrticolari
Transazioni
Classiche, savepoint, …
Persistenza con JPA
Utilizzo di JPA con J2SE
Quering con JPA
EJB-QL e query in varie forme
© 2010 - CEFRIEL
Introduzione

Problema:
– accedere a un DBMS da Java “ignorando” i dettagli comunicativi
con il DBMS in questione:
•
•
•
•
modalità di connessione/autenticazione;
database a cui ci si sta connettendo;
variazioni sulla sintassi SQL standard;
tipi di dato, funzionalità, …
– utilizzare uno strumento OO per accedere a un DB.

Soluzione:
–
–
–
–
–
–
JDBC, ispirato a ODBC: java.sql.** e javax.sql.**;
interfacce descrivono le funzionalità;
driver custom per ogni DBMS offre le implementazioni;
indirizzo JDBC (stringa) definisce DBMS, DB e autenticazione;
DriverManager identifica il driver giusto e ne offre i servizi;
tutto in maniera trasparente per l’utilizzatore.
© 2010 - CEFRIEL
Derby setup

Installazione:
– scaricare ed eseguire il tool di installazione dal sito della Sun se
non si ha già derby;
– installando SJSAS derby è installato ed incluso:

• lo si trova in “Sun\SDK\javadb”;
• “javadb\frameworks\NetworkServer\bin” contiene i
tools che ci permettono di usarlo.
Configurazione:
– DERBY_HOME:
– PATH:
– CLASSPATH:
path fino a “javadb”;
aggiungere path fino a bin;
agginugere:
• $DERBY_HOME/lib/derby.jar;
$DERBY_HOME/lib/derbytools.jar;
$DERBY_HOME/lib/derbynet.jar;
$DERBY_HOME/lib/derbyclient.jar
© 2010 - CEFRIEL
Derby tools

A disposizione dalla cartella bin di javadb:
– setNetworkClientCP
setNetworkServerCP
• impostazione automatica delle variabili d’ambiente (locali);
• da modificare eliminando il commento e definendo
DERBY_INSTALL;
• ambiente sia per il client che per il server;
– sysinfo:
• informazioni sullo stato corrente del server;
– dblook:
• consente di mostrare il DDL di un DB;
– startNetworkServer
stopNetworkServer
• permettono di avviare e fermare il servizio Derby;
– NetworkServerControl
– ij:
• permette di controllare il funzionamento del server, avviarlo,
fermarlo, pingare, controllarne o modificarne i parametri;
• accesso a DB ed esecuzione comandi DDL ed SQL.
© 2010 - CEFRIEL
Creazione DB

Scegliere la cartella che conterrà i DB;
– eventualmente crearne una nuova appositamente;

solo nei nostri test:
– useremo “cmd” spostandoci nella cartella dei DB;
– eseguiremo il comando:
• NetworkServerControl start
– la cartella corrente verrà usata come “derby.system.home”;
– normalmente:
• creazione di uno script apposito;
• impostazione della proprietà da comando;
• esecuzione di:
– java -Dderby.system.home=<DBhome> <altreProps>
-jar %DERBY_HOME%\lib\derbyrun.jar server start

creazione DB:
– con il server in esecuzione:
• da “cmd” eseguire “ij” ed il comando:
– connect 'jdbc:derby://localhost:1527/Corsi;create=true';
© 2010 - CEFRIEL
Connessione e manipolazione del DB

Connettersi:

• connect 'jdbc:derby://localhost:1527/Corsi';
Creare lo schema del DB:
– Usando “ij”;
– usando il comando:
– utilizzando i comandi DDL:
• CREATE TABLE Argomento (
ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
PRIMARY KEY,
nome VARCHAR(30) NOT NULL UNIQUE,
descr CLOB,
numOre
INTEGER NOT NULL);
– con un file contenente i comandi DDL e il comando:
• run ‘nomefile’;
• si leggano i sorgenti di:
– corsiCreate.sql
– corsiDrop.sql
• li si lanci su un database di esempio;
• si provi a manipolare ed ispezionare il DB.
© 2010 - CEFRIEL
Lo schema SYS

Schemi:
– sorta di “viste” del DB (ad esempio per applicare
autorizzazioni diverse, o mostrare solo parte del DB);
– associati agli utenti;
– associati a ruoli;
– SYS è di sistema (del DBMS)
– APP è il nostro.

Diamo un occhio:
SET SCHEMA SYS;
SELECT TABLENAME FROM SYSTABLES WHERE TABLENAME LIKE 'SYS%';
SELECT CONSTRAINTNAME FROM SYSCONSTRAINTS;
SELECT SCHEMANAME FROM SYSSCHEMAS;
© 2010 - CEFRIEL
Riferimenti

Era solo un:
– rinfresco dei vecchi ricordi di basi di dati;
– un assaggio delle features di JavaDB (Derby);
• non è Oracle…
– …però offre quanto serve nella maggior parte dei casi.

Per informarsi meglio:
–
–
–
–

Java DB Tools and Utilities Guide
Java DB Server and Administration Guide
Java DB Developer's Guide
Java DB Reference Manual
Ricordiamoci:
– NON SIAMO DBA, non tentiamo di sostituirli!
© 2010 - CEFRIEL
Il nostro DB di esempio

Contenuto:
– corsi erogabili dal CEFRIEL, corredati dalla descrizione degli
argomenti, durata e difficoltà;
– tabelle “Corso” “Argomento” e relazione n-n tra loro;
– vedere “corsiCreate.sql” e “corsiDrop.sql”…
– …e usarli per creare il DB su cui lavorare!

Noi utilizzeremo:
– solo la tabella “Argomento” nell’esempio “preconfezionato”;
– la tabella corso e la relazione n-n negli esercizi.
© 2010 - CEFRIEL
Connettersi al DB tramite JDBC

JDBC:
– infrastruttura di astrazione dei dettagli di comunicazione con un
DBMS (protocollo);
– simile nello scopo a ODBC (by Microsoft);
– database identificato da un JDBS path;
– “comunicatore” nascosto in un java.sql.Driver…
• …da includere nel classpath;
– driver automaticamente istanziato dal DriverManager.

Esempio (nostro caso):
– java.sql.Connection conn =
DriverManager.getConnection(
"jdbc:derby://localhost:1527/Corsi");
– solo nel JDBC path è presente la dipendenza dal tipo di
DBMS/server/porta/database, il resto del codice è agnostico.
© 2010 - CEFRIEL
Eseguire una query

Interfacce con cui interagire:
– java.sql.Statment:
• rappresenta un comando SQL con possibilità di
restituzione di un ResultSet (o di un intero);
• la connessione contiene i factory-methods;
• permette di utilizzare i cursori e fare pooling;
• permette l’esecuzione batch di più comandi.
– java.sql.PreparedStatment:
• sotto-interfaccia della precedente;
• permette di formattare parametri tipizzati;
• permette di eseguire lo stesso statment più volte
con parametri differenti (reimpostati ogni volta);
• modo migliore per evitare bachi di SQL-injection.
© 2010 - CEFRIEL

Esempio in pratica: inserimento
In 07_DB&SQL\ClientCorsi:
–
–
–
–
finestra FrameAggiuntaArgomenti;
esecuzione di una INSERT con parametri;
notare i tipi (stringa, intero… e CLOB);
notare che non ci stiamo preoccupando della
gestione della transazione… che comunque avviene.
// Mi connetto:
Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Corsi");
// Creo lo statment preparato (con argomenti) e da eseguire:
PreparedStatment st = conn.prepareStatement(
"INSERT INTO Argomento (nome, descr, numOre) VALUES (?,?,?)");
Connessione al DBMS
// Creo il CLOB contenente la descrizione:
Clob desc = conn.createClob();
desc.setString(1, txtDescrizione.getText());
Preparazione statment
// Imposto i parametri:
st.setString(1, txtNome.getText());
st.setClob(2, desc);
st.setInt(3, sldNumeroOre.getValue());
Assegnazione parametri
// Scrivo nel DB:
st.execute();
Reale esecuzione SQL
© 2010 - CEFRIEL

Esempio in pratica: ricerca
In 07_DB&SQL\ClientCorsi:
–
–
–
–
finestra FrameArgomenti;
esecuzione di una SELECT senza parametri;
notare i tipi (stringa, intero… e CLOB);
non gestiamo la transazione… che avviene. (sicuri?)
// Mi connetto:
Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Corsi");
// Effettuo la query:
ResultSet res = conn.createStatement().executeQuery(
"SELECT nome, numOre, descr FROM Argomento");
// Aggiungo le righe:
while (res.next()) {
Connessione al DBMS
Esecuzione query
// Ottengo i dati:
String nome = res.getString(1);
int numOre = res.getInt(2);
Clob desc = res.getClob(3);
// Aggiungo una riga:
tbl.addRow(new Object[]{nome, numOre,
desc.getSubString(1, (int) desc.length())});
// Ora posso chiudere il result set e la connessione:
res.close(); conn.close();
© 2010 - CEFRIEL
Fetch dei dati
}
Chiusura connessione
Esempio in pratica: cancellazione

In 07_DB&SQL\ClientCorsi:
– finestra FrameArgomenti;
– esecuzione di una DELETE con parametri;
– gestiamo noi la transazione:

• autoCommit disabilitatato;
• commit e rollback esplicite.
Da notare:
– in realtà la connessione viene sempre chiusa in finally;
– nel blocco catch viene effettuata la rollback della transazione.
Connessione al DBMS
con disabilitazione autoCommit
// Mi connetto:
Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Corsi");
conn.setAutoCommit(false);
// Eseguo la cancellazione:
pst = conn.prepareStatement("DELETE FROM Argomento WHERE nome=?");
pst.setString(1, nome);
pst.execute();
// Ora posso committare e chiudere la connessione:
conn.commit(); conn.close();
© 2010 - CEFRIEL
Cancellazione
Chiusura connessione
Esercizio

Modificare l’esempio realizzando:
– aggiungere l’operazione update in FrameArgomenti;
– una finestra unica di gestione dei corsi
(aggiunta, visualizzazione, aggiornamento e cancellazione
direttamente nella stessa finestra);
– permettere l’accesso alla finestra degli argomenti sia per
modificarli che per selezionarne uno;
– permettere l’associazione di un argomento a un corso tramite
selezione in FrameArgomenti e richiesta del numero di giorno in
cui inserire l’argomento;
– aggiungere una serie di strumenti di quering come:
•
•
•
•
•
argomenti di un corso;
argomenti di un corso in un giorno;
numero ore di un corso;
programma completo di un corso;
altro a vostra discrezione.
– NOTA: in quest’ultimo punto mostrare i risultati in tabella.
© 2010 - CEFRIEL
Cenni su JPA

Java Persistence API… scopo:
– fornire un’infrastruttura di gestione dell’ORM;
– sfruttare sotto le potenzialità di JDBC;
– permettere di manipolare solamente oggetti.

Soluzione proposta:
– ORM definito tramite:

• descrittore XML (non lo vedremo);
• classi annotate (si veda l’esempio).
Nel nostro caso:
– abbiamo di già un DB, quindi “importiamo” lo schema;
– lasciamo che NetBeans si appoggi a Forte4J.

Ma prima… un po’ di teoria!
© 2010 - CEFRIEL
Persistence units

Unità che mettono assieme tecnologie di peristenza:
– database accessibili come data sources;
– un Presistence Provider per la gestione dell’ORM;
– le entità da gestire nella persistenza e ORM.

Supporto per:
– gli EntityManager, atti alla gestione delle entità;
– applicazioni enterprise usufruenti dell’EntityManager e delle PU.

DB
DB
DB
La PU è il collante che descrive come mettere assieme
le varie parti, è un descrittore di deploy in XML.
Data
source
EApp
Entity
Data
source
DB
DB
EntityManager
Persistence
Provider
Data
source
Entity
Persistence
Provider
Entity
Entity
Entity
© 2010 - CEFRIEL
Persistence Unit:
persistence.xml glues
together persistence pieces
and technologies to provide
support to the EntityManager
Entity beans

JavaBeans:
– semplici POJO con:
• costruttore di default:
– senza parametri affinché possano essere istanziati;
• proprietà:
– rappresentate da metodi getter e setter;
– una proprietà “prop” deve avere:
» un getter “public type getProp();” se è read;
» un setter “public void setProp(type val);” se è write;
– rappresentano:
• entità reali a cui sono associate proprietà;
• Entity EJB se annotate come @Entity;
– sono mappati:
• su tabelle del DB.
© 2010 - CEFRIEL
EntityManager
efact = Persistence.createEntityManagerFactory(PUname);
manager = efact.createEntityManager();

Scopo:
– gestione degli EntityBeans:
• persistenza e quering;
– gestione transazioni:
• utilizzabile per 0..* transazioni (esplicite in JSE);
– da considerarsi come una connessione al DB.

Può essere ottenuto:
– in JSE tramite una factory:
• la persistence unit deve essere dichiarata in
persistence.xml;
– in JEE come in JSE o sfruttando l’injection (tramite annotazioni).
© 2010 - CEFRIEL
Managed e unmanaged
DB



La PU è legata a un DB;
l’EntityManager alla PU;
un pool di managed entity beans viene
mantenuto sincronizzato con il DB:
– a seconda della flush policy scelta;
– regole di attach/detach;
– modifica di proprietà aggiornata sul DB;

ciò che non è managed è unmanaged:
–
–
–
–
nessun aggiornamento automatico;
bean come normali POJO;
possibilità di merge;
al di fuori di una transazione:
• sempre unmanaged;
• a meno di extended contexts.
© 2010 - CEFRIEL
PU
EntityManager
Entity
Entity
Entity
managed
Entity
Entity
Entity
unmanaged
Transazioni

begin
…
ops
…
commit/
rollback
Una o più operazioni correlate su uno o più DB;
– devono/possono essere:
• Atomiche:
• Consistenti:
• Isolate:
con i vincoli imposti
dal DB (relazioni)
se concorrenti non
interferiscono
in JSE
em.getTransaction().begin();
…
em.getTransaction().commit();
modifiche con successo
sono permanenti
– concetto di transazioni ACID;

gli entity beans sono managed:

le transazioni sono gestite da:
– (quasi) solo durante una transazione;
– JPA in SE;
– JTA in EE.
injection
…
ops
…
Transaction
scope
• Durature:
come fossero eseguite
istantaneamente
in JEE
@PersistenceContext(unitName=“PUname”)
private EntityManager em;
…
<transaction-scoped operations>
© 2010 - CEFRIEL
Creazione automatica del DB
<properties>
<property name="toplink.jdbc.url" value="jdbc:derby://localhost:1527/aTest"/>
<property name="toplink.jdbc.driver" value="org.apache.derby.jdbc.ClientDriver"/>
<property name="toplink.jdbc.user" value="APP"/>
<property name="toplink.jdbc.password" value="APP"/>
<property name="toplink.ddl-generation" value="drop-and-create-tables"/>
<property name="toplink.create-ddl-jdbc-file-name" value="DB_aTest_create.sql"/>
<property name="toplink.drop-ddl-jdbc-file-name" value="DB_aTest_drop.sql"/>
</properties>

Nel container JEE dell’AS…
…e da poco anche in JSE fuori dal container:
–
–
–
–
è possibile richiedere la creazione automatica del DB;
vengono generati 2 file con DDL di drop e create;
sequenza drop-create al deploy (o exe. App. JSE);
possibili scelte di toplink.ddl-generation:
• none (default)  non viene fatto nulla;
• create-tables  solo le create;
• drop-and-create-tables  sequenze drop-create.
© 2010 - CEFRIEL
JPA: Conclusioni

Consente:
– di accedere a un DB in maniera “nativa” rispetto al
paradigma di programmazione Object Oriented;
– nasconde tutti i dettagli di Object Relational Mapping;
– è uno strumento utilizzato in ambiente enterprise…
– … si vedano anche altri strumenti come Hibernate e Spring.

Di nostro…
– … abbiamo solo raschiato la superficie;
– analizzando “ClientCorsi” si risponda alle domande:
– Come sono state mappate le tabelle?
– Come sono stati mappati i campi? Chiavi primarie? Identificativi
generati? La chiave primaria composta? Il CLOB?
– Come sono state mappate le relazioni?
– Come sono state “nascoste” le query?
© 2010 - CEFRIEL