Connessione ai database      

La connessione di Java ai database avviene tramite atti formali che poi si riscontrano anche in altri linguaggi.
Per comodità, noi realizziamo la connessione con dei database Access (2003), ma per altre versioni di Access o per collegamenti a DB SQL cambia solo la stringa di connessione.
Lavorando con Access, facciamo riferimento ad un file su disco che ha un nome ed una estensione (mdb).
Un altro identificatore importante è il nome della tabella o delle tabelle sulle quali eseguiamo le operazioni.
Infine, dobbiamo aver sempre presente l'esatto nome dei campi (colonne) del database che vogliamo interpellare.
Creiamo un file (database) Access chiamato 'data.mdb' al suo interno scriviamo la tabella 'compensi' :

il campo 'i' è chiave primaria (non ammette duplicati). Possiamo interrogare il database via Java attraverso uno scritto come questo:

import java.sql.*;
public class connessione {
public static void main(String[] args) throws SQLException {
Connection cn;
Statement st;
ResultSet rs;
String url,sql;
//________________________________connessione
try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch (
ClassNotFoundException e) {
   System.out.println("ClassNotFoundException: ");    System.err.println(e.getMessage());
}//fine try-catch
url = "jdbc:odbc:;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=data.mdb";
cn = DriverManager.getConnection(url,"","");
sql="SELECT * FROM compensi;";
//________________________________query
try{
st=cn.createStatement();
rs=st.executeQuery(sql);
while(rs.next()==true)    System.out.println(rs.getString("nome")+"\t"+rs.getString("target")); }catch(SQLException e) {
System.out.println("errore:"+e.getMessage());
}//fine try-catch
cn.close(); //chiusura connessione
}//fine main
}//fine classe

Per collegarci al database ci serve un oggetto Connection (cn) uno Statement (st) che si occupa di effettuare l'interrogazione (query) un ResultSet (rs).
Il ResultSet in altri linguaggi (VB, ASP) viene anche chiamato recordset: essa è in pratica una tabella che viene prodotta alla fine della query (è il risultato della query) e risiede nella RAM del computer. Le due istruzioni

st=cn.createStatement();
rs=st.executeQuery(sql);

eseguono materialmente l'interrogazione. In output viene stampata la tabella senza l'indice i. (infatti, noi gli diciamo di stampare solo il nome e il target) Questo avviene col ciclo

while(rs.next()==true)     System.out.println(rs.getString("nome")+"\t"+rs.getString("target"));

Fondamentali sono la stringa di connessione (url)

url = "jdbc:odbc:;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=data.mdb";

Che dichiara il database con cui ci si collega. Altrettanto importante è la stringa della query (sql) scritta appunto in linguaggio SQL.

sql="SELECT * FROM compensi;";

Il questo caso l'istruzione dice di selezionare tutti (*) i record (righe) della tabella compensi.
La query può subire articolazioni, ad esempio:

sql="SELECT * FROM compensi WHERE target>=5;";

avrebbe stampato solo i primi due record; infatti, solo loro soddisfano la condizione di avere un campo target maggiore o uguale a 5.

SQL      

L'istruzione SELECT viene usata per effettuare delle interrogazioni nel DB, quindi viene usata per operazioni di ricerca di dati. La sua sintassi è:

SELECT ElencoCampi FROM NomeTabella WHERE condizione;

L'elenco dei campi non è altro che la sequenza delle colonne che devono essere visualizzate, la clausola WHERE che è comunque opzionale contiene la condizione logica che deve essere rispettata per effettuare la selezione.

Le altre operazioni che si possono fare su un DB sono quelle che ci possiamo immaginare:

Bastano? Si direbbe di si, ma ovviamente esistono altre operazioni come quella corrispondente all'istruzione CREATE che permette di creare intere tabelle con campi stabiliti da noi. La sintassi generale per il comando INSERT è:

INSERT INTO NomeTabella (elencocampi) VALUES (elencovalori);

se volessimo applicarla alla nostra tabella, potrebbe essere :

INSERT INTO compensi (nome,target) VALUES ('fabio',5);

Con SQL quando ci riferiamo alle stringhe ('fabio') bisogna usare gli apici mentre quando ci riferiamo a valori numerici (5) non bisogna usarli. Il costrutto try-catch si presenta nel modo seguente:

try{
     st=cn.createStatement();
     st.executeUpdate(sql);
}catch(SQLException e) {
     System.out.println("errore:"+e.getMessage());
}

Da notare come deve essere applicato il metodo executeUpdate(sql); perché si tratta di un aggiornamento della tabella e non di una query.

Un modo analogo può essere usato per le cancellazioni che in SQL si dichiarano:

DELETE FROM NomeTabella WHERE nomecampo=valore;

se volessimo applicarla alla nostra tabella, potrebbe essere

DELETE FROM compensi WHERE target=5;

Il blocco try-catch rimane invariato ma la nostra tabella viene ridotta nel modo seguente:

Questo perchè abbiamo cancellato tutti i record aventi target=5.
Resta da provare l'operazione di aggiornamento.

UPDATE NomeTabella SET nomecampo=valore WHERE nomecampo=valore;

Ad esempio se volessimo portare paola ad un target=5:

UPDATE compensi SET target=5 WHERE nome='paola';

Anche in questo caso il blocco try-catch rimane invariato .
Per la creazione della tabella e la definizione del tracciato dei campi si usa il comando:

CREATE TABLE NomeTabella (
campo_1 datoCampo_1,
campo_2 datoCampo_2,
. . .
campo_n datoCampo_n);

Per i nostri scopi nei campi della tabella usiamo una chiave primaria (id) di tipo contatore, di sola lettura che si autoincrementa all'inserimento di ogni nuovo record (COUNTER PRIMARY KEY).
Per le stringhe di caratteri useremo l'identificatore CHAR(n) dove n è un intero che definisce la lunghezza massima della stringa. Useremo dati di tipo intero dichiarati come INTEGER e di tipo FLOAT per i numeri con la virgola.
Talvolta può essere usato il tipo di dato DATE per indicare una specifica data del calendario. Più raramente viene usato il tipo BIT che indica un dato di tipo booleano (occupa infatti solo un bit di memoria) che può essere zero o non-zero.

A titolo di esempio useremo una tabella di anagrafica per i dipendenti di una azienda che ha diverse filiali sul territorio:

CREATE TABLE Anagrafica (
id COUNTER PRIMARY KEY,
cognome CHAR(20),
filiale CHAR(20),
funzione CHAR(20),
livello INTEGER,
stipendio INTEGER,
coniugato BIT,
provincia CHAR(2));

Si tratta di un comando piuttosto lungo che può essere gestito con le seguenti istruzioni:

sql="CREATE TABLE Anagrafica ( ";
sql+="id COUNTER PRIMARY KEY, ";
sql+="cognome CHAR(20), ";
sql+="filiale CHAR(20), ";
sql+="funzione CHAR(20), ";
sql+="livello INTEGER, ";
sql+="stipendio INTEGER, ";
sql+="coniugato BIT, ";
sql+="provincia CHAR(2));";
//________________________________query
try{
     st=cn.createStatement();
     st.executeUpdate(sql);
}catch(SQLException e) {
     System.out.println("errore:"+e.getMessage());
}//fine try-catch
Il blocco try-catch è invariato rispetto le istruzioni INSERT,DELETE e UPDATE.

Ora inseriamo i seguenti dati anche manualmente, dato che sono pochi.

Notiamo come per convenzione se il campo BIT non è nullo Access lo riempie con un -1. Per noi il valore 0 corrisponde ad una persona non coniugata, mentre un valore -1 ad una persona coniugata.
Ovviamente su una base di dati di questo tipo è possibile svolgere già un buon numero di interrogazioni:

SELECT cognome, filiale FROM Anagrafica WHERE Funzione='operaio';

ci mostra tutti i campi delle persone che sono residenti in provincia di Como.

SELECT DISTINCT funzione FROM Anagrafica;

Ci restituisce tutte le funzioni dei dipendenti specificate una sola volta.

Funzioni di aggregazione      

Assieme al SELECT possono essere usate funzioni che intervengono su insiemi di record, chiamate funzioni di aggregazione.

SELECT COUNT(*) AS TOTALE FROM Anagrafica;

ci dice quante righe ci sono nella tabella personale; è stata aggiunta la clausola AS associata all'identificatore TOTALE per avere una intestazione della colonna (a quale colonna facciamo riferimento durante la stampa a video?). In questo caso il blocco di lettura sarà:

try{
   st=cn.createStatement();
   rs=st.executeQuery(sql);    while(rs.next()==true)System.out.println(rs.getString("TOTALE")); }catch(SQLException e) {
   System.out.println("errore:"+e.getMessage());
}//fine try-catch

Le funzioni di aggregazione possono essere usate anche con la clausola WHERE.

SELECT COUNT(*) AS NUBILI FROM Anagrafica WHERE coniugato=0;

Ci dice quanti dipendenti non sono sposati.

SELECT livello, COUNT(*) AS TOT FROM Anagrafica GROUP BY livello;

ci restituisce l'elenco dei livelli presenti con a fianco il numero di occorrenze rilevate per ogni livello:

Più avanti si noterà come le query si possano nidificare fra loro

SELECT COUNT(*) AS TOT FROM (
SELECT livello, COUNT(*) FROM Anagrafica GROUP BY livello
);

Conteggia il numero totale di livelli presenti.

La funzione SUM restituisce la somma di tutti i valori contenuti in una colonna.

SELECT SUM(stipendio) AS TOT FROM Anagrafica WHERE livello=4;

restituisce la somma degli stipendi che appartengolo al 4°livello.

SELECT AVG(stipendio) AS MEDIA FROM Anagrafica WHERE funzione='impiegato';

restituisce la media dello stipendio di un impiegato

SELECT MAX(stipendio) AS MASSIMO, MIN(stipendio) AS MINIMO FROM Anagrafica;

restituisce il minimo e il massimo stipendio erogato.

Raggruppamenti e ordinamenti      

SELECT cognome, stipendio FROM Anagrafica ORDER BY stipendio DESC;

ci restituisce l'elenco dei dipendenti ordinati da quello che guadagna di più a quello che guadagna di meno. Per invertire l'ordine basta usare la parola chiave ASC al posto di DESC.
L'ordinamento è crescente per default.

Se nel SELECT oltre a dei campi normali è presente una funzione di aggregazione si usa la clausola GROUP BY.

SELECT funzione, SUM(stipendio) AS SOMMA FROM Anagrafica GROUP BY funzione;

ci dà l'elenco delle funzioni con a fianco lo stipendio per tutti i dipendenti che hanno quella funzione.

Bisogna osservare come sia obbligatorio usare la clausola GROUP BY quando nel SELECT è presente una funzione di aggregazione.

Il seguente comando ci consente di analizzare la quantità degli operai, raggruppati per livello

SELECT livello, COUNT(livello) AS CONTA
FROM Anagrafica
WHERE funzione='operaio'
GROUP BY livello;

Il comando GROUP BY può essere potenziato dalla presenza della clausola HAVING tramite la quale è possibile controllare le condizioni dei raggruppamenti effettuati.

SELECT funzione, AVG(stipendio) AS MEDIA
FROM Anagrafica
GROUP BY funzione
HAVING COUNT(*)>2;

Questa query valuta la media dello stipendio dei dipendenti raggruppati per funzione a patto che per quella funzione ci siano più di 2 dipendenti.

HAVING serve, dunque, per specificare delle condizioni che devono essere soddisfatte per effettuare i raggruppamenti.

Se il comando SELECT contiene la clausola WHERE, i valori vengono raggruppati dopo aver operato la selezione sulle righe che rispettano la condizione imposta dal WHERE.

SELECT filiale, COUNT(filiale) AS CONTA
FROM Anagrafica
WHERE funzione='operaio'
GROUP BY filiale
HAVING COUNT(*)>1;

Valuta il numero di operai presenti in ogni filiale a patto che nella filiale ci siano almeno due operai.

Per certi versi HAVING somiglia al WHERE, ma mentre il WHERE impone condizioni sulle righe della tabella, HAVING impone le condizioni sul risultato prodotto dalle funzioni di aggregazione applicati a gruppi di righe.

Criteri di selezione      

Sono le condizioni di ricerca che vengono usate nelle clausole WHERE ed HAVING. In esse possono essere usati tutti i simboli delle operazioni relazionali (=, =>,<=, <, >,) e delle operazioni logiche AND OR ; l'operatore NOT se presente, deve essere applicato prima di AND o OR.

Un operatore talvolta usato è BETWEEN che controlla se un determinato valore è compreso all'interno di un determinato intervallo (estremi inclusi).

SELECT cognome, stipendio
FROM Anagrafica
WHERE stipendio BETWEEN 1500 AND 1300;

come si intuisce è la stessa cosa che eseguire

SELECT cognome, stipendio
FROM Anagrafica
WHERE stipendio<=1500 AND stipendio>=1300;

L'operatore IN verifica, inoltre, se un dato valore appartiene ad un insieme specificato.

SELECT cognome, provincia FROM Anagrafica WHERE provincia IN ('CO','VA');

L'operatore IN può anche essere preceduto dall'operatore NOT.

SELECT cognome, provincia
FROM Anagrafica
WHERE provincia NOT IN ('CO','VA');

Restituisce

L'operatore LIKE, viene usato quando si eseguono delle query sui dati di tipo stringa. Vengono usati due metacaratteri :'?' (punto di domanda) indica un singolo carattere in quela posizione della stringa '*' (asterico) indica una qualsiasi sequenza di caratteri in quella posizione della stringa.

ATTENZIONE! Questa sintassi vale solo per Access, in altri DBMS al posto di '?' si usa '_' (underscore) e al posto di '*' si usa '%' (percentuale).

LIKE 'abc*' tutte le stringhe che iniziano per 'abc'
LIKE '*abc' tutte le stringhe che terminano per 'abc'
LIKE '*abc*' tutte le stringhe che contengono la sequenza 'abc'
LIKE '?abc' tutte le stringhe di quattro caratteri che terminano per 'abc'

SELECT cognome FROM Anagrafica WHERE cognome LIKE 'om*';

restituisce

In Access, nei campi che non sono chiave primaria, sono ammessi anche campi vuoti. Per andare incontro a questa eventualità si usa il predicato IS NULL. Ad esempio:

SELECT cognome FROM Anagrafica WHERE coniugato IS NOT NULL

restituisce l'elenco delle persone il cui stato civile è definito. Nel nostro caso verrà restituito tutto l'elenco dei cognomi dato che nel campo 'coniugato' non vi sono celle vuote.