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
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
JDBC

• JDBC è una libreria di API per laccesso
  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
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
Risorse on-line

• Sito JDBC
• http//java.sun.com/products/jdbc
• API Java
• ltdirectory javagt/docs/guide/jdbc
• Tutorial on-line
• http//java.sun.com/docs/books/tutorial/jdbc

6
JDBC Accesso ai dati

1. Registrare il driver JDBC
2. Connettersi al DB
3. Definire una query SQL
4. Processare i risultati della query
5. Chiudere la connessione

7
JDBC (1) Registrare il driver
import java.sql. // package JDBC public int
count(String args) throws ClassNotFoundExceptio
n // registra il driver MS Sql
Server Class.forName(" com.microsoft.jdbc.sqlserv
er.SQLServerDriver ") // modalità
alternativa (elenco di driver separati da )
System.setProperty("jdbc.drivers",
"com.microsoft.jdbc.sqlserver.SQLServerDriver
oracle.jdbc.OracleDriver")
8
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.SQLServerD
  river
• Oracle oracle.jdbc.OracleDriver
• MySQL com.mysql.jdbc.Driver
• Elenco driver
• http//servlet.java.sun.com/products/jdbc/drivers

9
JDBC (1) Registrare il driver

• Per caricare la classe occorre
• avviare il programma con classpath
  ltfile_jar_del_drivergt
• o, modificare CLASSPATH con ltfile_jar_del_drivergt
• o, copiare ltfile_jar_del_drivergt 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
JDBC (2) Connessione al DB
String url jdbcmicrosoftsqlserver //
driver //tera.di.unipi.it/
// host DatabaseNamename
// database String user foo String
password hello Connection conn
DriverManager.getConnection(url, user,
password)
11
JDBC (2) Connessione al DB

• LURL di connessione è specifica del driver
• definita nella documentazione del driver
• IBM DB2
• String url jdbcdb2
• //tera.di.unipi.it50000/
• sample
• MS Access (via ODBC)
• String url jdbcodbc
• DRIVERMicrosoft Access Driver
  (.mdb)
• DBQdb1.mdb
• Sorgente ODBC registrata
• String url jdbcodbcltnome_sorgentegt

12
JDBC (3) Definire query SQL
// oggetto per la gestione di comandi
SQL Statement stmt con.createStatement() St
ring query "SELECT nome,eta FROM elenco //
metodo per lesecuzione di query SQL ResultSet
rs stmt.executeQuery( query ) String update
UPDATE elenco SET eta eta 1 // metodo
per lesecuzione di update/insert/create table
SQL int affectedRows stmt.executeUpdate(update
)
13
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
Mapping tipi di dato JDBC - Java
15
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
JDBC (4) ResultSet aggiornabili
Statement stmt con.createStatement(ResultSet
.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATA
BLE) String query "SELECT nome,eta FROM
elenco ResultSet rs stmt.executeQuery( query
) while ( rs.next() ) int eta
rs.getInt(eta) rs.updateInt(eta,
eta1) rs.updateRow()
17
JDBC (5) Chiusura Connessione
// chiude la connessione al DB
conn.close()
18
JDBC MetaDati

1. Sul database
2. Su un ResultSet

19
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
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 i1 i lt nCols i) System.out.print(
md.getColumnName(i)",") System.out.println(
md.getColumnName(nCols))
21
JDBC Metadati resultset
. // output resultset while ( rs.next()
) for(int i1 i lt nCols
i) System.out.print( rs.getString(i)",")
System.out.println( rs.getString(nCols))

22
JDBC Classi Java
23
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
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
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 ( 50335.25 PM, 170335.25 )
• per Timestamp ( Date Time )

26
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
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
JDBC ed oltre

• Esecuzione di Stored Procedures
• metodo execute() di Connection
• Aggiornamenti batch
• Più comandi UPDATE o INSERT raggruppati in
  ununica richiesta
• Transazioni
• commit, rollback, transazioni distribuite
• Connection pooling
• riuso delle connessioni aperte

29
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 gt sqlj

30
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.exec
ute () stmt.close()
31
Esercitazione FORMATO

• Scrivere un programma Java per la conversione da
  query SQL a file ARFF.
• Scrivere un programma Java per linserimento in
  una tabella delle righe contenute in un file ARFF.

32
Esercitazione EXPORT

• Scrivere un programma Java per lesportazione di
  una tabella da un database ad un altro.

33
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 dellattributo originario con
  valore Vr esiste una riga della tabella avente
  valori Ai 0 per i ! r e con valore Ar 1.
• A gt 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
Esercitazione RANDOMIZZAZIONE

1. 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
Esercitazione SUBSAMPLING (1)

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
Esercitazione SUBSAMPLING (2)

1. Scrivere un metodo Java che data una tabella in
   RDBMS con una colonna A binaria (0/1) esporta su
   un file CSV l80 delle righe che hanno A0. Le
   righe sono selezionate in modo casuale.

37
Esercitazione SUBSAMPLING (3)

1. 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.