Le SQL intégré Le SQL intégré Présentation de l`API JAVA : JDBC

Le SQL intégré
Fred Hémery
IUT Béthune
Département
Réseaux & Télécommunications
Base de Données (IC5) — 07/08
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 1 / 29
Le SQL intégré
Aujourd’hui les applications sont de plus en plus complexe. Un SGBD
associé au langage SQL ne peut pas répondre à toutes les questions des
utilisateurs.
La solution adoptée consiste à intégrer des fonctions de liaison dans les
langages de programmation classiques pour leurs permettre d’accéder
aux données stockées dans un SGBD.
Il existe des bibliothèques de fonctions ou classes pour les langages
IC
IC++
IJAVA
ITcl/Tk
IPerl ...
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 2 / 29
Présentation de l’API JAVA : JDBC
JDBC : Java Data Base Connection
Architecture
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 3 / 29
Présentation de l’API JAVA : JDBC
L’utilisation de l’API JDBC se fait en 4 étapes :
1chargement du pilote (driver) JDBC qui fait le lien entre l’application Java et
le SGBD
2connexion à une base de données du SGBD
3utilisation et traitement des données
4fermeture de la connexion
Les classes sont déclarées dans les paquetages
Ijava.sql
Ijavax.sql
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 4 / 29
Chargement du pilote
Il y a quatre types de pilote
1La passerelle JDBC-ODBC (Open DataBase Connectivity) qui permet de
transformer l’API JDBC vers l’API ODBC qui est très largement utilisée
dans le monde Microsoft.
2Le pilote qui implémente JDBC en natif dans un langage autre que java,
c’est à dire qu’il accède directement au SGBD cible. Il doit exister une
version du pilote par type d’architecture qui sera susceptible d’accueillir
l’application cliente.
3Le pilote JDBC transporte la requête jusqu’à un serveur d’applications
(middleware) de manière indépendante du SGBD cible. Ensuite le serveur
fournit la requête au SGBD.
4Le pilote qui transmet la requête directement vers le SGBD cible, mais le
pilote est implémenté en Java.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 5 / 29
Chargement du pilote
Architecture 2 tiers
Architecture 3 tiers
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 6 / 29
Chargement du pilote
Concrètement pour charger le pilote on tente de créer une instance de la
classe qu’il représente en utilisant l’introspection avec l’instruction
suivante :
Cl as s . forName ( " o rg . p o s t g r e s q l . D r i v e r " ) ;
Recherche la classe org.postgresql.Driver
dans la liste des chemins définis dans la variable
d’environnement CLASSPATH et tente de l’instan-
cier.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 7 / 29
Connexion à la Base de données
Cette étape consiste à utiliser le pilote pour faire la connexion entre
l’application et le SGBD cible. Pour cela on utilise l’instruction suivante :
Connection connexion = Dr iverManager . get Conn ect ion (
u r l ,
nomLogin , / / i d e n t i f i c a t i o n de l ’ u t i l i s a t e u r
motPasse ) ; / / a u t h e n t i f i c a t i o n de l ’ u t i l i s a t e u r
La variable url est de la forme :
j d b c : p o s t g r e s q l : / / machineServeur / nomBD
jdbc : odbc : nomBD
. . .
La connexion obtenue sera utilisée pour travailler avec le SGBD cible.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 8 / 29

Création d’une requête
Il y a deux types de requête
Iles requêtes d’interrogation, consultation qui renvoient un résultat ; et
Iles requêtes de modification
Les requêtes de consultation comme les requêtes de modification seront
véhiculées par une instance de la classe Statement.
Statement st mt = connexion . c r e ateS tatem ent ( )
Une requête est créée par la méthode createStatement() de la
classe Connection . Il n’y a qu’une seule instance de la classe
Statement par instance de la classe Connection.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 9 / 29
Création d’une requête
On utilise l’instance de la classe Statement pour indiquer le code que l’on
veut exécuter.
Suivant le type de requête :
IModification :
stm t . executeUpdate ( "CREATE TABLE agenda " +
" (nom v ar c ha r ( 2 0 ) , prenom va r ch ar ( 2 0 ) , " +
" ad ress e v a rc h ar ( 5 0 ) , t e l ch ar ( 1 2 ) " ) ;
IConsultation
stm t . executeQuery ( "SELECT ∗FROM agenda " ) ;
Contrairement aux requêtes de modification, les requêtes de consultation
ont besoin d’accéder au résultat de la requête.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 10 / 29
Traitement d’une requête
Le résultat de la requête est affecté à une instance de la classe
ResultSet.
R e su l t Se t r s = s t m t . ex ec ute Quer y ( " S e l e c t ∗from c l i e n t ; " ) ;
L’instance de la classe ResultSet contient la table résultat de la
requête.
Pour récupérer les données dans l’instance, on utilise un pointeur sur une
ligne courante et les méthodes du tableau.
Voir la documentation Java pour avoir l’ensemble des méthodes
disponibles.
int getInt(int i) int getInt(String
nomCol)
Renvoie l’entier stocké dans la ième colonne ou la co-
lonne de nom nomCol
String getString(int i) String getString(String
nomCol)
Renvoie la chaîne de caractères stockée dans la ième
colonne ou la colonne de nom nomCol
Date getDate(int i) Date getDate(String
nomCol)
Renvoie la date stockée dans la ième colonne ou la
colonne de nom nomCol
boolean next() Renvoie vrai si il y a encore une ligne à traiter, faux
sinon. Elle fait passer aussi d’une ligne à la ligne sui-
vante
boolean first() Place le curseur sur la première ligne
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 11 / 29
Clôture de la connexion
On utilise la méthode close() de la classe Connection.
connexion . c l o s e ( ) ;
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 12 / 29
Un exemple complet
import j av a . i o . ∗;
import j a va . s q l . ∗;
pub lic cl a ss TestJDBC {
private Conne ction con nexion ;
pub lic TestJDBC ( ) {
t r y {
C la s s . forNa me ( " o rg . p o s t g r e s q l . D r i v e r " ) ;
}catch ( Cl as sN otFound Ex ception e ) {
System . e r r . p r i n t l n ( " P i l o t e d ’ acces
p o s t g r e s q l i n t r o u v a b l e ( " + e . t o S t r i n g ( ) + " ) " ) ;
}
}
p ub l ic s t a t i c v oi d main ( S t r i n g [ ] a rgs ) {
TestJDBC i n s t a n c e = new TestJDBC ( ) ;
i n s t a n c e . o uv reConnexion ( " j d b c : p o s t g r e s q l : / / l o c a l h o s t / agenda " ,
" d uchm ol " , " s e c r e t " ) ;
i n st a nc e . t r a i t e m e n t ( ) ;
i n s t a n c e . fermeC o nn ex ion ( ) ;
}
}
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 13 / 29
Un exemple complet
pub lic void o uv re Connexion ( S t r i n g u r l , S t r i n g u se r , S t r i n g password ) {
t r y {
co nn exi on = D ri ve rM ana ge r . g e tC o nne cti on ( u r l , u se r , p asswo rd ) ;
System . o ut . p r i n t l n ( " Connecte a l a base de donnees URL = " + u r l ) ;
}catch ( SQLException e ) {
System . o ut . p r i n t l n ( " E xce pti on : o u ve rt ur e ( " + e . t o S t r i n g ( ) + " ) " ) ;
}
}
pub lic void fermeConnexion ( ) {
t r y {
conne x ion . cl os e ( ) ;
}catch ( SQLException e ) {
System . o ut . p r i n t l n ( " E xce pti on : f er me tu re ( " + e . t o S t r i n g ( ) + " ) " ) ;
}
}
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 14 / 29
Un exemple complet
public void t r a i t e m e n t ( ) {
Stat e ment s t m t = n u l l ;
R e s ul t S e t r s ;
t r y {
stmt = conne xion . c r e a t e S t at e m en t ( ) ;
s tmt . e xe cu te U pd at e ( " d r op t a b l e telep ho n e " ) ;
}catch ( SQLException e ) { }
t r y {
s tmt . e xe cu te U pd at e ( " c r e at e t a b l e t e le p h on e ( " +
" nom v a r ch a r ( 2 5 ) NOT NULL , " +
" pr enom v a r ch a r ( 2 5 ) NOT NULL , " +
" t e l e ph o n e v a r c h a r ( 1 5 ) NOT NULL ) " ) ;
s tm t . e xecut eU pdate ( " i n s e r t i n t o t elephone " +
" values ( ’ M ar tin ’ , ’ Robe r t ’ , ’00 00 00 00 0 0 ’ ) " ) ;
s tm t . e xecut eU pdate ( " i n s e r t i n t o t elephone " +
" values ( ’ Durant ’ , ’ Ge rard ’ , ’00 00 00 00 0 0 ’ ) " ) ;
r s = s t mt . e xe cu te Que ry ( " s e l e c t ∗fro m t e le p ho ne o rde r b y nom, prenom " ) ;
while ( r s . n ex t ( ) ) {
Sy stem . o u t . p r i n t l n ( "Nom : \ t " + r s . g e t S t r i n g ( " nom" ) ) ;
Sy stem . o u t . p r i n t l n ( " Pren om : \ t " + r s . g e t S t r i n g ( 2 ) ) ;
Sy stem . o u t . p r i n t l n ( " t e l ep h one : \ t " + r s . g e t S t r i n g ( " t el e p ho n e " ) + " \ n " ) ;
}
stmt . c lose ( ) ;
}catch ( SQLException e ) {
Syste m . e r r . p r i n t l n ( " e r r e ur e x ec u t io n r equete SQL " + e . t o S t r i n g ( ) ) ;
System . e x i t ( 1 ) ;
}
}
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 15 / 29
Résultat d’exécution
[ . . . ] ja va c TestJDBC . ja va
[ . . . ] Java −c l as s pa t h . : / u s r / share / l i b / pg s ql / j db c7 .1 −1. 2 . j a r TestJDBC
Con ne ct e a l a ba se de don nees URL = j d b c : p o s t g r e s q l : / / l o c a l h o s t / d uchmol
Nom : Dura nt
Prenom : Gerard
te lep ho ne : 00 00 00 00 00
Nom : M a rt i n
Prenom : R obert
te lep ho ne : 00 00 00 00 00
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 16 / 29

Requêtes particulières
Lorsque vous avez à exécuter plusieurs fois la même requête, il est
intéressant de pouvoir la compiler au préalable pour que le SGBD n’ait
plus qu’à l’exécuter.
Dans ce cas on utilise la classe PreparedStatement.
S t r i n g s q l = " upda te t ele ph one s e t t ele ph one = ?
where nom = ? " ;
PreparedStatement changeTel =
c on ne xi on . p r epa red St a tem ent ( s q l ) ;
On remplace, à l’utilisation de la requête préparée, les " ?" par des
valeurs actualisées
changeTel . s e t S t r i n g ( 1 , " 03 21 63 71 65 " ) ;
changeTel . s e t S t r i n g ( 2 , " m a rt i n " ) ;
i n t i = changeTel . executeUpdate ( ) ;
La méthode executeUpdate() renvoie le nombre de tuples ayant été
modifiés
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 17 / 29
Les méta-informations
JDBC dispose aussi de deux classes
IResultSetMetaData pour obtenir des informations sur une instance
de la classe ResulSet
IDatabaseMetaData pour obtenir des informations sur la base de
données
En effet ResultSetMetaData permet d’obtenir
ILe nombre de colonnes contenues dans le
ResultSet(getColumnCount())
ILe nom d’une colonne (getColumnLabel(int i))
ILe nom de la table d’une colonne (getTableName())
ILe type de donnée d’une colonne (getColumnTypeName())
ILa taille en nombre de caractères d’une colonne
(getColumnDisplaySize())
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 18 / 29
Les méta-informations
t r y {
st mt = conn e xion . c r ea te St at em e nt ( ) ;
r s = s tm t . executeQuery ( " s e l e c t ∗f ro m t ele pho ne o rde r by nom , prenom " ) ;
Resul tSetMe t aData meta = r s . getMetaData ( ) ;
i n t nbColonnes = meta . getColumnCount ( ) ;
while ( r s . ne xt ( ) ) {
fo r (i n t i = 1 ; i <= nbC ol on ne s ; i ++)
System . o ut . p r i n t l n ( meta . getColumnName ( i ) + " : \ t " +
meta . getColumnTypeName ( ) ) ;
}
st mt . c l o s e ( ) ;
}catch ( SQLException e ) {
System . e r r . p r i n t l n ( " e r r e u r e xe cu ti on r equ ete SQL " + e . t o S t r i n g ( ) ) ;
System . e x i t ( 1 ) ;
}
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 19 / 29
Les méta-informations
t r y {
DatabaseMetaData meta = conn exion . getMetaData ( ) ;
System . o ut . p r i n t l n ( "SGBD : \ t " + meta . getDatabaseProductName ( ) ) ;
System . o u t . p r i n t l n ( " JDBC D r i v e r : \ t " + meta . g etDriverName ( ) ) ;
System . o u t . p r i n t l n ( " V er si on : \ t " + meta . g e t D r i v e r V e r s i o n ( ) ) ;
}catch ( SQLException e ) {
System . e r r . p r i n t l n ( " e r r e u r " + e . t o S t r i n g ( ) ) ;
System . e x i t ( 1 ) ;
}
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 20 / 29
Utilisation d’un pool de connexions
Dans le cas d’une exécution d’un client qui accède au SGBD
directement, ce qui vient d’être présenté reste valide ; cependant
Dans le cas d’une utilisation d’un serveur d’applications dans une
architecture 3 tiers, il peut être utilise d’apporter des améliorations.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 21 / 29
Utilisation d’un pool de connexions
Le serveur d’application gère un ensemble de processus
Certains de ces processus ont besoin d’une connexion avec la base de
données
Plutôt que de créer une connexion avec le SGBD à chaque utilisation,
une solution plus efficace est de gérer un pool de connexions disponibles
mais pas affectées.
Lorsqu’un processus aura besoin d’une connexion, il demandera au
gestionnaire du pool de connexion, si il dispose d’une connexion libre.
Dans le cas positif, le gestionnaire affectera une connexion au processus.
Sinon il bloque le processus en attente d’une connexion.
Lorsque le processus aura terminé sa transaction avec le SGBD, il la
rendra au gestionnaire, qui l’ajoutera au pool des connexions disponibles
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 22 / 29
Utilisation d’un pool de connexions
Pour obtenir une connexion disponible dans un pool, il faut utiliser
l’interface : DataSource qui est définie dans la paquetage
javax.sql.
Pour pouvoir utiliser cette interface il faut utiliser une version de JDBC
2.0 ou JDBC 3.0
Avec postgreSQL on dispose d’une implémentation de JDBC3.0
(c.a.d java.sql.*,javax.sql.*)
Le programme client de base qui veut utiliser une connexion de type
DataSource au travers d’un pool de connexions disponibles va
effectuer deux étapes :
1Création d’un pool de connexion (étape effectuée par le serveur
d’applications, 1 fois à l’initialisation) ;
2Obtention d’une connexion dans le pool créé (par le client, à chaque
demande de connexion)
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 23 / 29
Les extensions
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 24 / 29

Utilisation d’une DataSource coté client
Conne ction conn = n u l l ;
t r y {
conn = sour c e . g etC onn ect ion ( ) ;
// use connection
}catch ( SQLException e ) {
/ / l o g e r r o r
}finally {
i f ( con != n u l l ) {
t r y { conn . c lo s e ( ) ; } catch ( SQLException e ) { }
}
}
La variable source est de type DataSource, elle aura été mis à
disposition par le serveur d’applications.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 25 / 29
DataSource & JNDI
La source de données (DataSource) peut avoir été créée par un serveur
d’application et ensuite enregistrée dans un annuaire (repository ). Les
ressources disponibles dans cet annuaire sont alors accessibles à partir d’un
client qui connaît la référence de la source de données.
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 26 / 29
Utilisation d’une DataSource & JNDI coté client
Conne ction conn = n u l l ;
t r y {
DataSource sour ce = ( DataSource )new I n i t i a l C o n t e x t ( ) . lo okup ( " DataSource " ) ;
conn = sour c e . g etC onn ect ion ( ) ;
// use connection
}catch ( SQLException e ) {
/ / l o g e r r o r
}catch ( NamingException e ) {
/ / D at aSource wasn ’ t f ou nd i n JNDI
}finally {
i f ( con != n u l l ) {
t r y { conn . c lo s e ( ) ; } catch ( SQLException e ) { }
}
}
La variable source de type DataSource est obtenue suite à l’interrogation
d’un annuaire (JNDI) qui stocke des références sur des ressources (ici une
source de données).
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 27 / 29
Initialisation d’une DataSource coté serveur : exemple de
tomcat
Il faut ajouter le pilote JDBC dans l’environnement de tomcat
($CATALINA_HOME/common/lib)
Il faut créer un fichier qui définit un contexte particulier pour votre
application. Si par exemple votre application a pour nom
"MagasinVirtuel", il faut créer un fichier context.xml qui
contient :
< Co nt e xt p a th= " / M a ga s i n Vi r t u el " docBase= " . "
c r o ss Co n te x t = " t r u e " r e l o a d a b l e = " t r u e " de bug = " 1 " >
<Re so urce name= " j d b c / p o st g re s " a uth = " C on ta i ne r "
t y pe = " j a v ax . s q l . D at aS ou rc e " d r iv e rC l as s Na m e= " o rg . p o s t g r e s q l . D r i v e r "
u r l = " j dbc : p os t gr es q l : / / 1 2 7 . 0 . 0 . 1 : 5 4 3 2 / mydb "
user name= " myuse r " pa sswor d= " mypasswd " m axActive = " 20 " m a xI d le = " 10 "
maxWait="−1" / >
</ Conte xt >
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 28 / 29
Initialisation d’une DataSource coté serveur : exemple de
tomcat
Dans le fichier web.xml il faut ajouter la balise suivante :
<resource−r e f >
< d e s c r i p t i o n > postg re SQL D at asour ce exa mple < / d e s c r i p t i o n >
<res−r e f −name> j d b c / p o st g r es < / r e s−r e f −name>
<res−t yp e > j a v a x . s q l . D at aS ource < / re s−type>
<res−auth >C onta iner < / res−auth >
</resource−r e f >
Enfin pour créer la variable source de données :
I n i t i a l C o n t e x t c x t = new InitialContext ();
i f ( c x t == n u l l ) {
throw new Ex ce pt i on ( " Uh oh −− no c o n t e x t ! " ) ;
}
DataSource ds = ( DataSource ) c xt . l ook up ( " j av a : / comp / env / jd bc / po st gr es " ) ;
i f ( ds == n u l l ) {
throw new Ex cept ion ( " Data sour ce not fou n d ! " ) ;
}
(IUT Béthune — Département R & T) Le SQL intégré Base de Données (IC5) — 07/08 29 / 29