Interfacce per DBMS Interfacce DBMS – in breve z z z z Corso di BD1 Ripasso comandi applicazione JDBC Manipolazione estensioni OR in JDBC z z Problema: Far accedere un’applicazione ad una base di dati Soluzione: Librerie di funzioni (SQL/CLI) Chi crea queste librerie? – – 1 2 Interfacce per DBMS z – z Architettura di riferimento Sono state quindi definite interfacce standard: – ODBC (Open DataBase Connectivity ): scritta in C JDBC (Java Data Base Connectivity): scritta in Java le operazioni supportate sono sostanzialmente le stesse, anche se in JDBC la sintassi è più semplice 3 4 JDBC (Java Data Base Connectivity) z z z API JDBC JDBC (che non è solo un acronimo ma un trademark della SUN) è stato sviluppato nel 1996 dalla Sun Rappresenta una API standard per interagire con basi di dati da un programma Java (grazie a Java c’è indipendenza dalla piattaforma) Operazioni possibili: – – 5 Caso 1: Librerie proprietarie del DBMS, applicazione dipendente dal DBMS (es. Oracle Call Interface OCI) Caso 2: Librerie standard, applicazione indipendente dal DBMS – Connessione ad DBMS Creazione ed invio di statement SQL Ricezione e manipolazione del risultato Java Applications JDBC API API JDBC Driver Manager API JDBC Driver DBMS JDBC Driver DBMS 6 1 JDBC – Driver Manager JDBC - Driver z Livello di gestione z Gestisce i driver disponibili, scegliendo quello piu’ appropriato z z Realizzano la connessione col DBMS Esistono quattro tipi di driver: 1. 2. 3. 4. 7 JDBC-ODBC Bridge + ODBC Driver A native-API partly Java technology-enabled driver Pure Java Driver for -portabili Database Middleware Direct-to-Database Pure Java Driver. -scritti java 8 JDBC – Architettura generale client JDBC - Tipi di dato Java Application z JDBC definisce un insieme di tipi SQL, che vengono poi mappati in tipi Java z Gli identificatori sono definiti nella classe java.sql.Types server JDBC Driver Manager JDBC/ODBC bridge (1) JDBC/API nativa(2) ODBC Driver Native driver 9 JDBC/Mid dleware(3) JDBC/ java driver(4) DB Middleware DBMS 10 JDBC - Tipi di dato JDBC - Flusso applicativo J D B C T y p e s M a p p e d to J a v a T y p e s 11 JD B C Type C H AR V AR C H A R L O NG V A R C H AR N U M E R IC D E C IM AL B IT T IN Y IN T S M A L L IN T IN T E G E R B IG IN T R E AL FL O AT D OU B LE B IN A R Y V AR B IN A R Y L O NG V A R B IN AR Y D ATE T IM E T IM E S T AM P Java Type S trin g S trin g S trin g ja va .m a th .B ig D e cim a l ja va .m a th .B ig D e cim a l b o o le a n b yte sh o rt in t lo n g flo a t d o u b le d o u b le b yte [] b yte [] b yte [] ja va .sq l.D a te ja va .sq l.T im e ja va .sq l.T im e sta m p z z z z z z Passo 1: Caricamento driver JDBC Passo 2: Connessione DBMS Passo 3: Creazione ed esecuzione statement Passo 4: Elaborazione risultato Passo 5: Disconnessione La gestione delle eccezioni è necessaria (SQLException) 12 2 JDBC-Passo 1: Caricamento JDBC - Connessione z driver Per realizzare la connessione vengono utilizzate le seguenti classi ed interfacce: – interfaccia java.sql.Driver – classe java.sql.DriverManager – interfaccia java.sql.Connection 13 z interfaccia java.sql.Driver: – Non viene esplicitamente utilizzata a livello applicativo – Per registrare driver utilizzati nella connessione 14 JDBC - Passo 2: Connessione JDBC - Passo 1: Caricamento driver z import java.sql.*; //import package classe java.sql.DriverManager – class JdbcTest { public static void main (String args []) { Class.forName ("oracle.jdbc.OracleDriver"); //caricamento del driver che si intende utilizzare } } 15 – – 16 JDBC - Passo 2: Connessione z JDBC - Passo 2: Connessione interfaccia java.sql.Connection: – Rappresenta una connessione attiva la DBMS – permette di inviare una serie di richieste SQL al DBMS z È possibile connettersi a qualunque database, locale e remoto, specificandone l’URL z In JDBC, l’URL è formato da tre parti: jdbc: <subprotocol>: <subname> – – 17 Insieme all’interfaccia java.sql.Driver gestisce i driver disponibili Quando viene istanziato un oggetto Driver viene automaticamente registrato nella classe DriverManager Effettua la connessione al DBMS <subprotocol> identifica il driver o il meccanismo di connessione al database (dipende dal DBMS) <subname> dipende da subprotocol ed identifica lo specifico database (dipende dal DBMS) 18 3 JDBC - Passo 2: Connessione JDBC - Passo 2: Connessione-Esempi z jdbc:oracle:thin:@everest:1521:GEN: – – z z z z z – Connection con = DriverManager.getConnection("jdbc:oracle:thin:@lu mpy:1521:DBname", “myLogin", “myPWD"); subprotocol: MySQL subname: z z z z thin specifica che deve essere utilizzato Oracle ODBC Thin driver Everest specifica il nome della macchina 1521: numero porta GEN: nome database Oracle cannings.org specifica il nome della macchina 3306 : numero porta test : nome database MySQL se si usa JDBC-ODBC driver: jdbc:odbc:subname z JDBC - Passo 3: Creazione statement … z Class JdbcTest { Public static void main (String args []) { Class.forName ("oracle.jdbc.OracleDriver"); Connection con= DriverManager.getConnection("jdbc:oracle:thin:@localhost:15 21:dylan", "scott", "tiger"); } } 21 Se uno dei driver caricati riconosce l’URL fornito dal metodo, il driver stabilisce la connessione 20 JDBC - Passo 2: Connessione Tipi statement: – Statement non preparati: il piano di accesso viene generato immediatamente prima dell’esecuzione, query semplici SENZA parametri; – Statement preparati: la generazione del piano di accesso e l’esecuzione sono due fasi distinte z Il piano può essere generato una sola volta ed eseguito più volte z Utile quando la stessa operazione deve essere eseguita con parametri diversi 22 JDBC - Passo 3: Creazione statement JDBC - Passo 3: Creazione statement non preparati preparati z z Un oggetto di tipo Statement viene creato a partire da un oggetto di tipo Connection e permette di inviare comandi SQL al DBMS: Connection con; … Statement stmt = con.createStatement(); //e’ overloaded z z z Tale istruzione verrà specificata al momento dell’esecuzione Uno stesso oggetto Connection puo’ creare piu’ oggetti Statement Un oggetto di tipo PreparedStatement viene creato a partire da un oggetto di tipo Connection e permette di inviare comandi SQL al DBMS: PreparedStatement queryImp = con.prepareStatement(”SELECT * FROM IMPIEGATI"); Si noti che l’oggetto statement non è ancora associato all’istruzione SQL da eseguire – 23 La connessione avviene chiamando il metodo getConnection() della classe DriverManager, che restituisce un oggetto di tipo Connection jdbc:mysql://cannings.org:3306/test – 19 z subprotocol: Oracle subname: La creazione di uno statement preparato richiede la specifica dello statement che dovrà poi essere eseguito 24 4 JDBC - Passo 3: Esecuzione statement non preparati z JDBC - Passo 3: Esecuzione statement non preparati Esistono 3 metodi diversi in base al tipo dello statement SQL query: – Query SQL (SELECT): (StatementObj).executeQuery(query) RISULTATO: Oggetto ResultSet – Statement di modifica (DML o DDL): (StatementObj).executeUpdate(query) RISULTATO: Numero righe modificate (0 in caso di statement DDL) – Statement SQL (non conosciuto a priori) : (StatementObj).execute(query) RISULTATO: true o false 25 z Per eseguire una query: stmt.executeQuery(”SELECT * FROM IMPIEGATI"); z Per eseguire una operazione di aggiornamento (DML), inclusi gli statement DDL: stmt.executeUpdate(”INSERT INTO IMPIEGATI VALUES (‘AB34’,‘Gianni’, ‘Rossi’,’GT67’,1500)"); stmt.executeUpdate("CREATE TABLE PROVA (CAMPO1 NUMBER)”); 26 JDBC - Passo 3: Esecuzione JDBC - Passo 3: Uso parametri in statement preparati statement preparati z Esistono 3 metodi diversi in base al tipo dello statement SQL: – Query SQL (SELECT): (StatementObj).executeQuery() RISULTATO: Oggetto ResultSet – Statement di modifica (DML o DDL): (StatementObj).executeUpdate() RISULTATO: Numero righe modificate (0 in caso di statement DDL) – 27 Statement SQL (non conosciuto a priori): (StatementObj).execute() RISULTATO: true o false z z z Utilizzando PreparedStatement posso passare parametri ad una query I parametri sono identificati da ‘?’ I parametri vengono settati mediante: (StatementObj).setXXX(n, value) Dove : -XXX e’ un tipo Java (String, Byte, Short, …) -n e’ il numero del parametro “?” da settare -value e’ il valore del parametro 28 JDBC - Passo 3: Uso parametri in JDBC - Passo 3: Esecuzione statement statement preparati z Esempio: PreparedStatement queryImp = con.prepareStatement( ”SELECT * FROM IMPIEGATI WHERE Nome = ? And Cognome=?"); z z I parametri possono poi essere associati allo statement preparato quando diventano noti. queryImp.setString(1, ‘Paolo’); // il primo parametro (1) identifica il ? che si vuole considerare queryImp.setString(2, ‘Rossi’); queryImp.executeQuery(): //esegue lo statement 29 z Il terminatore dello statement (es. ‘;’) viene inserito direttamente dal driver prima di sottomettere lo statement al DBMS per l’esecuzione Metodo (StatementObj).close() fine processamento dello statement e rilascio delle risorse 30 5 JDBC - Passo 4: Elaborazione JDBC - Passo 4: Elaborazione risultato risultato z z Utilizzo un oggetto ResultSet per ritornare e manipolare il risultato di una query, cioe’ tabella (soprattutto oggetti Statement) Esempio: z Un oggetto ResultSet mantiene il cursore sulla riga corrente (inizialmente e’ la prima) z Il metodo next() permette di spostarsi nel result set (cursore): while (rs.next()) //ritorna true,false {/* get current row */} lI valore di ritorno e’ false quando non ci sono più tuple da analizzare Esistono altri metodi per muovere il cursore (previous(), first(),…) String query = " SELECT * FROM IMPIEGATI "; Statement stmt= conn.createStatement(); ResultSet rs = stmt.executeQuery(query); z 31 32 JDBC - Passo 4: Elaborazione risultato z JDBC - Passo 5: Disconnessione Accesso agli attributi con: z A) (ResultsetObj).getXXX(column-name) – B) (ResultsetObj).getXXX(column-number) Dove: - XXX è il tipo Java nel quale il valore deve essere convertito A) String s = rs.getString(”Cognome"); B) String s = rs.getString(2); int n = rs.getInt(5); – z z z Usare getInt() per valori numerici, getString() per char, varchar 33 34 Utilizzo di funzionalità OR da JDBC 35 Per risparmiare risorse, può essere utile chiudere gli oggetti di classe Connection, Statement, ResultSet quando non vengono più utilizzati metodo close() la chiusura di un oggetto di tipo Connection chiude tutti gli Statement associati mentre la chiusura di uno Statement chiude ResultSet associati z Consideriamo JDBC 3 (ma molte estensioni già presenti in JDBC 2) z JDBC 3 conforme SQL99, tipi collezione: no MULTISET, solo ARRAY z Nuove interfacce per implementare mapping di tipi object relational in tipi Java JDBC - Creazione nuovi tipi z Essendo un comando DDL, si utilizza il metodo executeUpdate String type = "CREATE TYPE t_indirizzo AS OBJECT (" + "via VARCHAR(50),"+ "numero_civico INTEGER," + "cap INTEGER," + "città VARCHAR(20),"+ "provincia VARCHAR(2),"+ "stato VARCHAR(2))"; 36 Statement st = con.createStatement( ); // non prepared statement st.executeUpdate(type); 6 JDBC - Manipolazione valori nuovi tipi JDBC - Manipolazione nuovi tipi z Nuove interfacce standard JDBC (solo per tipi standard): – STRUCT – REF – ARRAY – SQLDATA z z z z z Per ogni interfaccia, il driver JDBC relativo ad un particolare DBMS, specificherà una classe che implementa l’interfaccia z Questo permette di gestire le eventuali differenze esistenti tra DBMS per mappare ADT per mappare valori di tipo riferimento per mappare valori di tipo array per semplificare mapping di ADT 37 38 JDBC - Tipi semplici z z z JDBC - ADT Non è introdotta nuova interfaccia Si manipolano utilizzando i metodi del tipo di base Esempio – CREATE TYPE id_impiegato AS INTEGER FINAL; – Si usano i metodi getInt() e setInt() per leggere e scrivere campi di tipo id_impiegato z z z Le istanze di tipi ADT vengono mappate in istanze di classe Struct Un oggetto di tipo Struct contiene un valore per ogni attributo dell’ADT a cui si riferisce I metodi per poter leggere e scrivere valori di attributi con tipo ADT sono – – – 39 40 JDBC - ADT - Esempio Esempio (continua) type="CREATE TABLE Impiegati ("+ "imp# INTEGER,"+ "nome VARCHAR(20),"+ "cognome VARCHAR(20),"+ "indirizzo t_indirizzo)"; String query = “select indirizzo from Impiegati where imp# = 12”; Statement st = con.createStatement(); ResultSet rs = st.executeQuery(query); rs.next(); Struct indirizzo = (Struct) rs.getObject(1); //si ottiene l’istanza Object[ ] ind_attr = indirizzo.getAttributes(); //si accedono gli attributi System.out.print( “Via ” + ind_attr[0] + “ “ + ind_attr[1] + “ “ + ind_attr[2] + “ “ + ind_attr[4] + “ “ + ind_attr[3]); vogliamo attribuire all’impiegato con nome= “Gianni” cognome= “Verdi” lo stesso indirizzo dell’impiegato che ha imp#=12 41 ResultSet - getObject(int): per accedere istanza ADT Struct - getAttributes(): per accedere le componenti di un’istanza (attributi) PreparedStatement - setObject(int, Struct): per settare parametri di tipo ADT 42 7 Esempio (continua) Tipi riferimento String up = “update Impiegati set indirizzo = ? where nome =‘Verdi’”; PreparedStatement pst = con.prepareStatement(up); pst.setObject(1, indirizzo); pst.executeUpdate( ); z z Le istanze di tipi riferimento (puntatori) vengono mappate in istanze di classe Ref i metodi per poter leggere e scrivere valori di attributi con tipo riferimento sono – – – – 43 ResultSet - getRef(int): per accedere attributi di tipo riferimento Ref - getObject( ): dato un valore di tipo riferimento, restituisce l’oggetto puntato (solo JDBC 3) Ref - setObject(Struct): il parametro diventa il nuovo oggetto puntato (solo JDBC 3) PreparedStatement - setRef(int, Ref): per settare parametri di tipo riferimento 44 Tipi riferimento - Esempio (continua) Tipi riferimento - Esempio CREATE TYPE t_progetto AS prj# INTEGER, nome VARCHAR(20), descrizione VARCHAR(50), budget INTEGER; z Si vuole associare il progetto dell’impiegato 12 a Verdi e visualizzare le informazioni su tale progetto CREATE TABLE Progetti of t_progetto; 45 CREATE TABLE Impiegati( imp# INTEGER PRIMARY KEY, nome VARCHAR(50), indirizzo t_indirizzo, assegnamento REF(t_progetto)); 46 Tipi riferimento - Esempio (continua) Tipi riferimento - Esempio (continua) String query = “select assegnamento from Impiegati where imp# = 12”; Ref assegn_ref = rs.getRef(1); //si ottiene l’identificatore String update = ‘”update impiegati set assegnamento = ? where nome = “Verdi”; PreparedStatement pst = con.prepareStatement(update); pst.setRef(1,assegn_ref); pst.executeUpdate( ); Statement st = con.createStatement(); ResultSet rs = st.executeQuery(query); rs.next(); 47 48 Struct progetto = (Struct) assegn_ref.getObject( ); Object [ ] prog_attr = progetto. getAttributes(); System.out.print(ind_attr[0] + “ “ + ind_attr[1] + “ “ + ind_attr[2] + “ “ + + ind_attr[3]); 8 z z Tipi array Tipi array - Esempio Le istanze di tipi SQL Array vengono mappate in istanze di classe Array I metodi per poter leggere e scrivere valori di attributi di tipo array sono CREATE TABLE Impiegati( imp# INTEGER, nome VARCHAR(50), indirizzo t_indirizzo, competenze VARCHAR(20) ARRAY[10]); – – – – ResultSet - getArray(): per accedere attributi di tipo array (restituisce un puntatore all’istanza) Array - getArray( ): permette di copiare i dati contenuti nell’array in strutture locali al programma Array - getResultSet (): restituisce un result set che contiene una tupla per ogni elemento dell’array, con due colonne. La prima contiene l’indice (partendo da 1), la seconda il valore PreparedStatement - setArray(int,Array): per settare parametri di tipo Array 49 si vogliono stampare tutte le competenze dell’impiegato 12 50 Tipi array - Esempio (continua) Tipi collezione String query = “select competenze from Impiegati where imp# = 12”; Statement st = con.createStatement(); ResultSet rs = st.executeQuery(query); rs.next(); Array comp = rs.getArray(1); //restituisce un puntatore all’array String [ ] comp_arr = (String [ ]) comp.getArray( ); for (int i = 0; i < comp_arr.length(); i++) System.out.print(comp_arr[i]) z 51 z VarrayÆ tipo array il driver potrà eventualmente utilizzare la classe Array anche per gestire altri tipi collezione – in JDBC le nested table di Oracle si mappano in oggetti di tipo Array 52 SQL Data z z Interfaccia ogni classe che implementa SQLData permette di definire un mapping esplicito tra un tipo SQL e una classe Java – z – – z casting definito esplicitamente dall’utente semplifica l’accesso agli oggetti strutturati la lettura di un oggetto restituisce un’istanza della classe associata dal mapping non si devono piu’ utilizzare Struct e Object non lo vediamo 53 9