java database connectivity (jdbc) lsa laboratorio di sistemi informativi economico aziendali...

Java DataBase Connectivity (JDBC)

LSA Laboratorio di Sistemi Informativi Economico Aziendali

Salvatore Ruggieri

Dipartimento di Informatica, Università di Pisa

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 serversOPENROWSETOPENXML



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?)



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



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



1. Registrare il driver JDBC

2. Connettersi al DB

3. Definire una query SQL

4. Processare i risultati della query

5. Chiudere la connessione

JDBC: Accesso ai dati



JDBC: (1) Registrare il driver

import java.sql.*; // package JDBC

public int count(String[] args)throws ClassNotFoundException

{ // registra il driver MS Sql ServerClass.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");



JDBC: (1) Registrare il driverSignifica 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



JDBC: (1) Registrare il driverPer 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



JDBC: (2) Connessione al DB

String url = “jdbc:microsoft:sqlserver:” + // driver “//tera.di.unipi.it/” + // host

“;DatabaseName=name”; // databaseString user = “foo”;String password = “hello”;Connection conn =

DriverManager.getConnection(url, user, password);

…



JDBC: (2) Connessione al DBL’URL di connessione è specifica del driver

definita nella documentazione del driver

IBM DB2String 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 registrataString url = “jdbc:odbc:<nome_sorgente>”;



JDBC: (3) Definire query SQL

// oggetto per la gestione di comandi SQLStatement 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 );



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);}…



Mapping tipi di dato JDBC - Java



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

…



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();

}



JDBC: (5) Chiusura Connessione

…

// chiude la connessione al DB conn.close();

…



1. Sul database

2. Su un ResultSet

JDBC: MetaDati



JDBC: Metadati database

… Connection con = …. ; DatabaseMetaData dbmd = con.getMetaData();

String catalog = null; // null = tuttiString schema = null; String table = “sys%”; // le tabelle che iniziano per sysString[] types = null;

ResultSet rs = dbmd.getTables(catalog , schema , table , types

); …



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));

…



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));

}}



JDBC: Classi Java



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 (?, ?)



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();

}



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 )




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




Classi java.sql Date, Time, Timestamp

Ereditano da java.util.Date Rappresentano tipo di dato SQL

DATE TIME TIMESTAMP

resultSet.getDate(“dataTransazione”);



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



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



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();



Esercitazione: FORMATO

1. Scrivere un programma Java per la conversione da query SQL a file ARFF.

2. Scrivere un programma Java per l’inserimento in una tabella delle righe contenute in un file ARFF.



Esercitazione: EXPORT

1. Scrivere un programma Java per l’esportazione di una tabella da un database ad un altro.



Esercitazione: BINARIZZAZIONE

1. 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 0medio 0 1 0medio 0 1 0basso 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.



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.



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.




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




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.

java database connectivity (jdbc) lsa laboratorio di sistemi informativi economico aziendali...

Documents

rdbms slide

accesso ai dati slide

rdbms esporta su

java java esteso

rdbms sqlj sql

programma java

packages java

connessione jdbc