Lo sviluppo di applicazioni web (in ambiente Java, come in altri ambienti di sviluppo) vi avrà sicuramente portato a sviluppare logiche applicative che in qualche punto avevano necessità di salvare i dati su uno strato di persistenza. Il più delle volte lo strato di persistenza è un DBMS a cui vi siete collegati mediante driver, nel caso di Java, driver JDBC.
Utilizzare le basi di dati per accedere ai servizi di persistenza è, grazie alla presenza di questi driver, un'operazione piuttosto semplice. La complessità, semmai, risiede nelle operazioni di debug, in quanto l'errore può dipendere da un'infinita serie di casi legati al fatto che Java è un linguaggio di programmazione e SQL è un'altro linguaggio, utilizzato all'interno del precedente.
In alcuni casi, la programmazione lato database può semplificare di molto la vita dello sviluppatore, consentendogli di liberarsi dei tipici problemi legati all'integrazione, oltre che a fornire ottimizzazioni di performance spesso anche notevoli.
Le stored procedures, in particolare, e i triggers, permettono di gestire delle funzioni lato database (le prime) o degli eventi (i secondi) permettendo quindi al livello di logica di business di liberarsi di alcuni gravosi compiti. Come potete approfondire anche nelle guide di HTML (vi suggerisco la lettura della guida MySQL, database che useremo), queste funzioni consentono di creare uno strato di programmazione direttamente nel DBMS, che è standard e quindi potrà essere facilmente utilizzato da tutti i linguaggi di programmazione. Noi chiaramente ci concentreremo su Java e su come attraverso questo linguaggio sia possibile utilizzare questo livello di programmazione.
Esempio concreto
Il nostro esempio ci farà vedere direttamente come creare delle stored procedures e dei trigger, e come utilizzarli. Si tratta di un sistema web il cui scopo è quello di registrare l'inserimento di utenti sul database e dei relativi acquisti. Inoltre è possibile cancellare gli acquisti. La web application viene utilizzata da un sistema terzo, e non da interfacce grafiche: il nostro sistema è quindi un servizio di relay utilizzato da altri. Al momento della cancellazione, vogliamo che (per una questione statistica, un audit o qualsiasi cosa di simile vi venga in mente) vengano registrati gli acquisti superiori ad un certo importo, in modo da mantenerne traccia (ad esempio per sapere quanti acquisti superiori a 100 euro falliscono ed in che periodo).
Dal punto di vista della programmazione Java avremo una servlet che gestisce il servizio ed una classe che si occupa di gestire la comunicazione con lo strato di persistenza (quindi con le stored procedures).
Logica di persistenza
Iniziamo la discussione proprio con il lato di programmazione sul database. Per l'esempio utilizzeremo MySQL, che dalla versione 5.0 ha introdotto queste nuove caratteristiche di programmazione, ma potete usare qualunque database. È importante dire che la sintassi potrebbe variare da database a database.
Lo schema del database è molto semplice:
Listato 1. Creazione database
-- Database
CREATE DATABASE 'dbstoredandtrigger';
-- Tabelle
CREATE TABLE 'dbstoredandtrigger'.'user' (
'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
'name' VARCHAR(45) NOT NULL,
PRIMARY KEY ('id')
)ENGINE = InnoDB;
CREATE TABLE 'dbstoredandtrigger'.'purchase' (
'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
'price' DOUBLE NOT NULL,
'purchase_date' DATETIME NOT NULL,
PRIMARY KEY ('id')
)ENGINE = InnoDB;
CREATE TABLE 'dbstoredandtrigger'.'userPurchase' (
'id_user' INTEGER UNSIGNED NOT NULL,
'id_purchase' VARCHAR(45) NOT NULL,
PRIMARY KEY ('id_user', 'id_purchase')
)ENGINE = InnoDB;
CREATE TABLE 'dbstoredandtrigger'.'deletedPurchase' (
'id' INTEGER UNSIGNED NOT NULL,
'price' DOUBLE NOT NULL,
'purchase_date' DATETIME NOT NULL,
'delete_time' DATETIME NOT NULL,
PRIMARY KEY ('id')
)ENGINE = InnoDB;
Le tabelle User e Purchase (rappresentanti utenti e acquisti) sono legate da una tabella userPurchase. In più abbiamo inserito la tabella deletePurchase dove inseriremo gli acquisti cancellati, superiori ad una soglia.
Per la nostra applicazione avremo bisogno di creare tre stored procedures (utilizzate dalla servlet): una per inserire gli utenti, una per inserire gli acquisti ed una per cancellare gli acquisti.
Listato 2. Creazione di tre stored procedures
-- Procedura inserimento utente
CREATE PROCEDURE 'insertUser'(nomeUtente VARCHAR(45))
BEGIN
INSERT INTO user(name) VALUES(nomeUtente);
END
-- Procedura inserimento acquisto
CREATE PROCEDURE 'addPurchase'(idUser INT, price DOUBLE, pdate DATETIME)
BEGIN
insert into purchase (price,purchase_date) values (price,pdate);
insert into userpurchase values (idUser,LAST_INSERT_ID());
END
-- Procedura cancellazione acquisto
CREATE PROCEDURE 'deletePurchase'(idPurchase INT)
BEGIN
delete from purchase where id=idPurchase;
END
Il linguaggio utilizzato dipende dal database su cui le stored procedures verranno eseguite (in questo caso si tratta di MySQL). La logica è molto semplice: nel primo caso effettuiamo una query di insert, nel secondo caso due query di insert (una per la tabella purchase e l'altra per la tabella userpurchase) ed infine la stored procedure di eliminazione. C'è da dire che all'interno delle vostre procedure potrete creare delle logiche anche complesse (in base ai requisiti dell'applicazione, di certo).
Vediamo invece come si presenta il trigger:
Listato 3. Trigger
-- Trigger cancellazione acquisti
CREATE TRIGGER deletePurchase before DELETE ON Purchase
FOR EACH ROW BEGIN
IF (OLD.price>100) THEN
INSERT INTO deletedPurchase (id,price,purchase_date,delete_date) values (OLD.id, OLD.price, OLD.purchase_date, CURDATE());
END IF
DELETE userpurchase WHERE id_purchase = OLD.id;
END
Il trigger, è una funzione che automaticamente verrà richiamata a seguito di un evento. In questo caso stiamo definendo un'azione da compiere prima di ogni cancellazione sulla tabella Purchase. Se il prezzo è superiore alla soglia, salviamo il risultato nella tabella deletedPurchase, infine ripuliamo la tabella userpurchase.
Questo trigger, nel nostro caso sarà attivato dalla stored procedure deletePurchase, richiamata dal layer di programmazione.
Logica di business
E vediamo allora come si presenta la nostra applicazione Java. Abbiamo detto che si tratta di una servlet che si occupa di effettuare la logica (inserimento utente, inserimento acquisto, cancellazione acquisto). Prima di vedere la servlet, discutiamo della classe che gestisce le connessioni verso il database e le chiamate alle stored procedures:
Listato 4. Gestisce le connessioni verso il database e le chiamate alle stored procedures
//Parametri per la connessione al database
private String url_conn;
private String db_driver;
private Connection connection;
public DBManager(String url,String driver) throws ClassNotFoundException{
this.url_conn=url;
this.db_driver=driver;
//Carica il driver: fate in modo da avere la libreria di driver sotto il vostro classpath
Class.forName(db_driver);
}
/**
* Metodo usato per l'apertura della connessione
*
* @throws SQLException
*/
private void openConnection() throws SQLException{
//Stabilisce la connessione
this.connection=DriverManager.getConnection(url_conn);
}
/**
* Metodo usato per la chiusura della connessione
*
* @throws SQLException
*/
private void closeConnection() throws SQLException{
this.connection.close();
}
Innanzitutto vediamo i metodi che gestiscono la connessione al database. Essendo operazioni che richiedono molte risorse, ci preoccuperemo di aprire e chiudere le connessioni prima e dopo il loro utilizzo (con i metodi openConnection() e closeConnection()).
Listato 5. Stored procedures sul DB
..//
/**
* Inserimento utente, richiamando la stored procedure "insertUser"
* @throws SQLException
* */
public void insertUser(String name) throws SQLException{
//Apriamo la connessione al DB
this.openConnection();
//Eseguiamo la procedura
CallableStatement storedProcedure = null;
storedProcedure = connection.prepareCall("{ call insertUser(?) }");
storedProcedure.setString(1, name);
storedProcedure.execute();
//Chiudiamo le risorse aperte
storedProcedure.close();
this.closeConnection();
}
/**
* Inserimento acquito, richiamando la stored procedure "addPurchase"
* @throws SQLException
* */
public void addPurchase(int id_user, double price) throws SQLException{
//Calcolo la data attuale
Date now=new Date(System.currentTimeMillis());
//Apriamo la connessione al DB
this.openConnection();
//Eseguiamo la procedura
CallableStatement storedProcedure = null;
storedProcedure = connection.prepareCall("{ call addPurchase(?,?,?) }");
storedProcedure.setInt(1, id_user);
storedProcedure.setDouble(2, price);
storedProcedure.setDate(3, now);
storedProcedure.execute();
//Chiudiamo le risorse aperte
storedProcedure.close();
this.closeConnection();
}
/**
* Eliminazione acquisto utente, richiamando la stored procedure "deletePurchase"
* @throws SQLException
* */
public void deletePurchase(int id_purchase) throws SQLException{
//Apriamo la connessione al DB
this.openConnection();
//Eseguiamo la procedura
CallableStatement storedProcedure = null;
storedProcedure = connection.prepareCall("{ call deletePurchase(?) }");
storedProcedure.setInt(1, id_purchase);
storedProcedure.execute();
//Chiudiamo le risorse aperte
storedProcedure.close();
this.closeConnection();
}
..//
Vediamo che ogni metodo rappresenta un ben definito caso di business. E vediamo pure come sia davvero semplice richiamare le stored procedures. Come esempio citiamo il metodo addPurchase(): viene preparata la stored procedure, vengono settati i parametri e poi segue l'esecuzione. Senza stored procedures ci saremmo dovuti preoccupare di:
- salvare l'acquisto nella tabella Purchase;
- caricare la chiave dell'inserimento appena effettuato;
- salvare l'associazione tra acquisto ed utente sulla tabella UserPurchase.
Ben tre distinte operazioni SQL, che vuol dire un bel carico computazionale in più rispetto alla chiamata di una stored procedure.
A questo punto la servlet risulta davvero semplicemente un dispatcher verso questa classe di logica.
Listato 6. Servlet, dispatcher verso la classe di logica
..//
//Metodo Dispatcher
protected void service(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
String op=req.getParameter("op");
if (op.equalsIgnoreCase("insert")){
doInsertUser(req,resp);
}
if (op.equalsIgnoreCase("purchase")){
doAddPurchase(req,resp);
}
if (op.equalsIgnoreCase("delete")){
doDeletePurchase(req,resp);
}
}
/**
* Inoltra la richiesta di inserimento utente, recuperando gli opportuni parametri dalla form
* @throws IOException
* */
private void doInsertUser(HttpServletRequest req, HttpServletResponse resp) throws IOException {
String name=req.getParameter("name");
try {
db.insertUser(name);
} catch (SQLException e) {
resp.getOutputStream().println("Errore nell'inserimento dell'utente, prego riprovare.");
}
resp.getOutputStream().println("L'utente "+name+" è stato inserito.");
}
/**
* Recupera i parametri per completare l'acquisto. Poi passa il controllo al gestore del database
* @throws IOException
*/
private void doAddPurchase(HttpServletRequest req, HttpServletResponse resp) throws IOException {
//Recupero i parametri
String iduser=req.getParameter("iduser");
String p=req.getParameter("price");
//Conversione dei parametri (nessun controllo, bisognerebbe farli!)
int id_user=Integer.parseInt(iduser);
double price=Double.parseDouble(p);
try {
db.addPurchase(id_user, price);
} catch (SQLException e) {
resp.getOutputStream().println("Errore nell'inserimento dell'acquisto, prego riprovare.");
}
resp.getOutputStream().println("L'acquisto è stato registrato.");
}
/**
* Inoltra la richiesta di cancellazione
* @throws IOException
*/
private void doDeletePurchase(HttpServletRequest req, HttpServletResponse resp) throws IOException {
//Recupero i parametri
String idpurchase=req.getParameter("idpurchase");
//Conversione dei parametri (nessun controllo, bisognerebbe farli!)
int id_purchase=Integer.parseInt(idpurchase);
try {
db.deletePurchase(id_purchase);
} catch (SQLException e) {
resp.getOutputStream().println("Errore nella cancellazione dell'acquisto, prego riprovare.");
}
resp.getOutputStream().println("L'acquisto è stato cancellato.");
}
Il codice, come si vede, è estremamente semplice in tutti i casi d'uso: recupero dei parametri e chiamata al rispettivo metodo dello strato di logica di business.
Per testare la vostra applicazione potrete eseguire questi link (inserimento utente, inserimento acquisto, cancellazione acquisto):
http://localhost:8080/DBProcedures/Controller?op=insert&name=nomeUtente
http://localhost:8080/DBProcedures/Controller?op=purchase&iduser=1&price=1330
http://localhost:8080/DBProcedures/Controller?op=delete&idpurchase=4
Potete anche creare delle semplici interfacce JSP che si collegano alla servlet.
