Java DataBase Connectivity (JDBC)

1. Java DataBase Connectivity (JDBC) LSA Laboratorio di Sistemi Informativi Economico Aziendali Salvatore Ruggieri Dipartimento di Informatica, Università di Pisa

2. Lab. Sistemi Informativi Economico-Aziendali Accesso ai dati su RDBMS • Protocolli e API • ODBC, OLE DB, ADO • Programmazione JDBC • Classi java.sql • Introduzione a SQL Server 2000 • Enterprise manager • Query analyser • Accesso ai dati esterni • Linked servers • OPENROWSET • OPENXML

3. Lab. Sistemi Informativi Economico-Aziendali JDBC • JDBC è una libreria di API per l’accesso uniforme a database relazionali • Definisce classi per: • connessione a database • invio di query SQL • navigazione dei risultati delle query • accesso ai metadati del database/risultati • Versioni • JDBC 1.0 -- JDK 1.1 • JDBC 2.0 -- JDK 1.3 • Packages Java • java.sql • javax.sql • JDBC 3.0 -- JDK 1.4 • JDBC 4.0 -- JDK 1.5 (?estate 2004?)

4. Lab. Sistemi Informativi Economico-Aziendali JDBC • Architettura di connessione • Driver di tipo 1 • traducono JDBC in ODBC • JDBC-ODBC Bridge • inefficienti • Driver di tipo 2 • parte in Java e parte in codice nativo • Driver di tipo 3 • pure Java • protocollo indipendente dal RDBMS • Driver di tipo 4 • pure Java • protocollo specifico del RDBMS

5. Lab. Sistemi Informativi Economico-Aziendali Risorse on-line • Sito JDBC • http://java.sun.com/products/jdbc • API Java • <directory java>/docs/guide/jdbc • Tutorial on-line • http://java.sun.com/docs/books/tutorial/jdbc

6. Lab. Sistemi Informativi Economico-Aziendali JDBC: Accesso ai dati • Registrare il driver JDBC • Connettersi al DB • Definire una query SQL • Processare i risultati della query • Chiudere la connessione

7. Lab. Sistemi Informativi Economico-Aziendali JDBC: (1) Registrare il driver import java.sql.*; // package JDBC public int count(String[] args) throws ClassNotFoundException { // registra il driver MS Sql Server Class.forName(" com.microsoft.jdbc.sqlserver.SQLServerDriver"); … // modalità alternativa (elenco di driver separati da ‘:’)System.setProperty("jdbc.drivers", "com.microsoft.jdbc.sqlserver.SQLServerDriver: oracle.jdbc.OracleDriver");

8. Lab. Sistemi Informativi Economico-Aziendali JDBC: (1) Registrare il driver • Significa caricare dinamicamente una classe Java • sun.jdbc.odbc.JdbcOdbcDriver è il JDBC-ODBC Bridge fornito con JDK • Altri driver: • IBM DB2 com.ibm.db2.jcc.DB2Driver • SQL Server com.microsoft.jdbc.sqlserver.SQLServerDriver • Oracle oracle.jdbc.OracleDriver • MySQL com.mysql.jdbc.Driver • Elenco driver • http://servlet.java.sun.com/products/jdbc/drivers

9. Lab. Sistemi Informativi Economico-Aziendali JDBC: (1) Registrare il driver • Per caricare la classe occorre: • avviare il programma con –classpath <file_jar_del_driver> • o, modificare CLASSPATH con <file_jar_del_driver> • o, copiare <file_jar_del_driver> in jre/lib/ext • File Jar di SQL Server: • mssbase.jar • mssqlserver.jar • msutil.jar • File Jar di DB2: • db2jcc.jar • db2jcc_license_cu.jar

10. Lab. Sistemi Informativi Economico-Aziendali JDBC: (2) Connessione al DB String url = “jdbc:microsoft:sqlserver:” + // driver “//tera.di.unipi.it/” + // host “;DatabaseName=name”; // database String user = “foo”; String password = “hello”; Connection conn = DriverManager.getConnection(url, user, password); …

11. Lab. Sistemi Informativi Economico-Aziendali JDBC: (2) Connessione al DB • L’URL di connessione è specifica del driver • definita nella documentazione del driver • IBM DB2 String url = “jdbc:db2:” + “//tera.di.unipi.it:50000/” + “sample” • MS Access (via ODBC) String url = “jdbc:odbc:;” + “DRIVER=Microsoft Access Driver (*.mdb);” + “DBQ=db1.mdb” • Sorgente ODBC registrata String url = “jdbc:odbc:<nome_sorgente>”;

12. Lab. Sistemi Informativi Economico-Aziendali JDBC: (3) Definire query SQL // oggetto per la gestione di comandi SQL Statement stmt = con.createStatement(); String query = "SELECT nome,eta FROM elenco“; // metodo per l’esecuzione di query SQL ResultSet rs = stmt.executeQuery( query ); String update = “UPDATE elenco SET eta = eta + 1“; // metodo per l’esecuzione di update/insert/create table SQL int affectedRows = stmt.executeUpdate(update );

13. Lab. Sistemi Informativi Economico-Aziendali JDBC: (4) Processare i risultati … // ResultSet è un iteratore sulle righe while ( rs.next() ) { // accesso ai valori dei campi per nome String nome = rs.getString("nome"); int eta = rs.getInt("eta"); // accesso ai valori dei campi per posizione (a partire da 1) int eta2 = rs.getInt( 2 ); System.out.println(nome + " " + eta); } …

14. Lab. Sistemi Informativi Economico-Aziendali Mapping tipi di dato JDBC - Java

15. Lab. Sistemi Informativi Economico-Aziendali JDBC: (4) ResultSet scorrevoli … Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String query = "SELECT nome,eta FROM elenco“; ResultSet rs = stmt.executeQuery( query ); rs.previous(); // riga precedente rs.relative(-5); // 5 righe indietro rs.relative(7); // 7 righe avanti rs.absolute(100); // 100-esima riga …

16. Lab. Sistemi Informativi Economico-Aziendali JDBC: (4) ResultSet aggiornabili … Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); String query = "SELECT nome,eta FROM elenco“; ResultSet rs = stmt.executeQuery( query ); … while ( rs.next() ) { int eta = rs.getInt(“eta”); rs.updateInt(“eta”, eta+1); rs.updateRow(); }

17. Lab. Sistemi Informativi Economico-Aziendali JDBC: (5) Chiusura Connessione … // chiude la connessione al DB conn.close(); …

18. Lab. Sistemi Informativi Economico-Aziendali JDBC: MetaDati • Sul database • Su un ResultSet

19. Lab. Sistemi Informativi Economico-Aziendali JDBC: Metadati database … Connection con = …. ; DatabaseMetaData dbmd = con.getMetaData(); String catalog = null; // null = tutti String schema = null; String table = “sys%”; // le tabelle che iniziano per sys String[] types = null; ResultSet rs = dbmd.getTables(catalog , schema , table , types ); …

20. Lab. Sistemi Informativi Economico-Aziendali JDBC: Metadati resultset public static void printRS(ResultSet rs) throws SQLException { ResultSetMetaData md = rs.getMetaData(); // output nomi delle colonne int nCols = md.getColumnCount(); for(int i=1; i < nCols; ++i) System.out.print( md.getColumnName(i)+","); System.out.println( md.getColumnName(nCols)); …

21. Lab. Sistemi Informativi Economico-Aziendali JDBC: Metadati resultset …. // output resultset while ( rs.next() ) { for(int i=1; i < nCols; ++i) System.out.print( rs.getString(i)+","); System.out.println( rs.getString(nCols)); } }

22. Lab. Sistemi Informativi Economico-Aziendali JDBC: Classi Java

23. Lab. Sistemi Informativi Economico-Aziendali JDBC: comandi preparati • Obiettivo: leggere un elenco di N nomi da un file CSV ed inserirli in una tabella • N query SQL? INSERT INTO names (id, name) VALUES (1, ‘Luigi Rossi’) INSERT INTO names (id, name) VALUES (2, ‘Mario Bianchi’) … • Tutte uguali tra loro, a parte i valori inseriti (parametri) • Usando ? come segnaposto per dei valori: INSERT INTO names (id, name) VALUES (?, ?)

24. Lab. Sistemi Informativi Economico-Aziendali JDBC: comandi preparati Connection con = …. ; String query = “INSERT INTO names (id, name) VALUES (?, ?)”; PreparedStatement st = con.prepareStatement(query); BufferedReader r = … ; int id = 1; String name; while( (name = r.readLine()) != null ) { st.setInt( 1, id++); st.setString( 2, name); st.executeUpdate(); }

25. Lab. Sistemi Informativi Economico-Aziendali JDBC: Date, Time e Timestamp • Rappresentazione delle date • 30.1.2004 • 30/1/2004 • 1.30.2004 • Jan 30, 2004 • 30 Gen 2004 • 30/Gen/2004 • January 30, 2004 • … • Identica pletora di rappresentazioni • per Time ( 5:03:35.25 PM, 17:03:35.25 ) • per Timestamp (= Date + Time )

26. Lab. Sistemi Informativi Economico-Aziendali JDBC: Date, Time e Timestamp • Classi java.util • Date • Data posteriore al 1 Gen 1970 • Precisione al millisec • Indipendente dalla rappresentazione • DateFormat / SimpleDateFormat • Formatta/parsa una Date • TimeZone • Dettagli relativi a fuso orario e ora legale/solare • Locale • Dettagli relativi a convenzioni regionali sulla rappresentazione • Calendar • Accede/opera sui campi di una Date

27. Lab. Sistemi Informativi Economico-Aziendali JDBC: Date, Time e Timestamp • Classi java.sql • Date, Time, Timestamp • Ereditano da java.util.Date • Rappresentano tipo di dato SQL • DATE • TIME • TIMESTAMP • resultSet.getDate(“dataTransazione”);

28. Lab. Sistemi Informativi Economico-Aziendali JDBC ed oltre • Esecuzione di Stored Procedures • metodo execute() di Connection • Aggiornamenti batch • Più comandi UPDATE o INSERT raggruppati in un’unica richiesta • Transazioni • commit, rollback, transazioni distribuite • Connection pooling • riuso delle connessioni aperte

29. Lab. Sistemi Informativi Economico-Aziendali JDBC ed oltre • (JNDI) Java Naming and Directory Interface • repository URL di connessione • (JDO) Java Data Object • Persistenza di oggetti, avendo come repository un RDBMS • Save, load da RDBMS • (SQLJ) SQL in Java • Java esteso con direttive #sql • Controllo dei tipi a tempo di compilazione • Metodi statici Java come SQL stored procedures • Classi Java come tipi SQL • SQLJ translator: > sqlj

30. Lab. Sistemi Informativi Economico-Aziendali SQLJ // SQLJ int n; #sql { INSERT INTO emp VALUES (:n)}; // JDBC int n; Statement stmt = conn.prepareStatement (“INSERT INTO emp VALUES (?)”); stmt.setInt(1,n); stmt.execute (); stmt.close();

31. Lab. Sistemi Informativi Economico-Aziendali Esercitazione: FORMATO • Scrivere un programma Java per la conversione da query SQL a file ARFF. • Scrivere un programma Java per l’inserimento in una tabella delle righe contenute in un file ARFF.

32. Lab. Sistemi Informativi Economico-Aziendali Esercitazione: EXPORT • Scrivere un programma Java per l’esportazione di una tabella da un database ad un altro.

33. Lab. Sistemi Informativi Economico-Aziendali Esercitazione: BINARIZZAZIONE • La binarizzazione di un attributo A con valori distinti V1 … Vk consiste in una tabella con k attributi binari (valori 0/1) A1 … Ak. Per ciascuna riga dell’attributo originario con valore Vr esiste una riga della tabella avente valori Ai = 0 per i != r e con valore Ar = 1. A => Aalto Amedio Abasso alto 1 0 0 medio 0 1 0 medio 0 1 0 basso 0 0 1 Scrivere un metodo Java che data una colonna di una tabella in RDBMS produce una nuova tabella (o file ARFF) ottenuta “binarizzando” tale colonna.

34. Lab. Sistemi Informativi Economico-Aziendali Esercitazione: RANDOMIZZAZIONE • Scrivere un metodo Java che data una tabella in RDBMS (con una chiave ID) produce una nuova tabella (esportata come file CSV) ottenuta “mescolando” in modo casuale le chiavi della tabella.

35. Lab. Sistemi Informativi Economico-Aziendali Esercitazione: SUBSAMPLING (1) • Scrivere un metodo Java che data una tabella in RDBMS esporta su un file CSV il 30% delle righe. Le righe sono selezionate in modo casuale.

36. Lab. Sistemi Informativi Economico-Aziendali Esercitazione: SUBSAMPLING (2) • Scrivere un metodo Java che data una tabella in RDBMS con una colonna A binaria (0/1) esporta su un file CSV l’80% delle righe che hanno A=0. Le righe sono selezionate in modo casuale.

37. Lab. Sistemi Informativi Economico-Aziendali Esercitazione: SUBSAMPLING (3) • Scrivere un metodo Java che data una tabella in RDBMS con una colonna A binaria (0/1) esporta su un file CSV il 30% delle righe. Le righe sono selezionate in modo casuale. Le righe esportate devono mantenere la stessa proporzione di valori di A presente nella tabella originaria.