Dans la s

1
Dans la série  SQL et Programmer en Java 
Quintessence de JDBC(DESS AIST 2003-2004)

• Auteur stephane.pelle_at_ign.frInstitut
  Géographique National
• Service des Bases Vecteurs / Département
  Développements
• Direction des Études /Département Informatique
• École Nationale des Sciences Géographiques

2
Plan de la présentation

1. Les origines de JDBC
2. Les références
3. Quelques rappels sur SQL
4. Les éléments de JDBC
5. La chronologie classique des ordres JDBC
6. JDBC et les types de données SQL
7. Un exemple de base de données montrant les
   différences liées au SGBD et au pilote utilisé

3
1. Les origines de JDBC

• API (Application Programming Interface) JDBC1
  (Java Database Connectivity), présentée par Sun
  en 1996 pour permettre laccès au monde des bases
  de données et nettement améliorée en 1998.
• API conçue sur le modèle ODBC (Open Database
  Connectivity) de Microsoft, cette API permet de
  communiquer grâce au langage SQL (Structured
  Query Language) avec un SGBD (Systèmes de Gestion
  de Bases de Données) ou tout autre logiciel
  (Systèmes dInformation Géographiques, etc.)
  disposant dun pilote (driver) approprié.
• Contrairement à ODBC, JDBC nest pas une
  technologie propriétaire et offre donc la
  particularité dêtre indépendante de la
  plate-forme dutilisation (Windows, Linux, etc.),
  conformément à Java.
• Les outils JDBC se trouvent dans le paquetage
  java.sql. et son extension javax.sql.2.
• 1. Appelée JDBC2 à partir de la version 1.2 du
  JDK (Java Development Kit).
• 2. javax.sql. fait partie du J2EE (Java 2 SDK,
  Enterprise Edition) et de la version 1.4 du J2SE
  (Java 2 SDK, Standard Edition).

4
2. Les références

• Les sites web
• Le site de SUN Microsystems www.sun.com
• Le site SUN sur JDBC java.sun.com/products/jdbc/
  
• Le site  SQL Pro de A à Z  de Frédéric Brouard
  sqlpro.developpez.com/indexSQL.html
• Ouvrages
• Au cœur de Java 2, volume 2 Fonctions avancées
  édité par CampusPress en 2002 (ISBN
  2-7440-1332-3) et traduit de Core Java 2 volume 2
  Advanced Features de Cay S.Hortsmann Gary
  Cornell, édité par Sun Microsystems en 2002 (ISBN
  0-13-092738-4)
• UML et Java pour les données Géographiques,
  volume 2 Outils avancés de Denis Priou, Jean-Marc
  Le Gallic, Didier Richard et Stéphane Pelle,
  édité par Hermes en 2004 (ISBN 2-7462-0831-8)
• SQL développement de Frédéric Brouard édité par
  CampusPress en 2001 (ISBN 2-7440-1184-3)

5
3. Quelques rappels sur SQL

• Le langage SQL est issu du SEQUEL (Structured
  English as a Query Language) d'IBM Corporation
  pour mettre en œuvre le  modèle relationnel 
  présenté par E.F. Codd en 1970. La première
  version stable de SQL date de 1979 et était
  commercialisée par la société Relational Software
  (rebaptisée depuis Oracle Corporation).
• 5 grandes catégories de commandes SQL
• DDL, Data Definition Language  commandes CREATE,
  ALTER et DROP, etc. pour créer, modifier et
  détruire table, index, etc. 
• DML, Data Manipulation Language  commandes
  SELECT, INSERT, UPDATE, DELETE, etc. pour
  obtenir, insérer, mettre à jour, supprimer, etc.,
  les enregistrements 
• DCL, Data Control Language et TCL, Transaction
  Control Language  commandes GRANT, REVOKE,
  pour la gestion des droits des utilisateurs et
  les commandes COMMIT, ROLLBACK, LOCK, etc. pour
  la gestion des transactions et des verrous dans
  le cas dun accès concurrentiel 
• Contraintes dintégrité  valeurs par défaut
  (pour éviter les valeurs non renseignées),
  contraintes de domaines (supérieur à 0 mais
  inférieur à 20 par exemple), contraintes
  référentielles (clé primaire ou étrangère) 
• SQL intégré dans un langage de programmation 
  commandes OPEN, FETCH, CLOSE, etc., manipulant
  des curseurs (CURSOR) pour ouvrir une table,
  accéder à un enregistrement et refermer la table.

6
3.1a Les normes SQL

• SQL1 (SQL86 ou SQL89)
• appelée SQL86 (norme ANSI American National
  Standards Institute), date évidemment de 1986. Sa
  version la plus aboutie a été finalisée en 1989
  (norme ISO - ANSI), doù son nom SQL89.
• SQL2 (SQL92)
• version normalisée la plus utilisée  elle a pour
  références ISO/IEC (International Standards
  Organization / International Electrotechnical
  Commission) 90751992 et ANSI (American National
  Standards Institute) X3.135-1992  Database
  Language SQL .
• SQL3 (SQL99)
• approche relationnelle approche objet 
  procédure stockée, objet, déclencheur (TRIGGER),
  récursivité Cette version est mise en œuvre dans
  JDBC3 mais très partiellement dans les SGBD. Les
  fabricants de systèmes de gestion ont dailleurs
  déjà pris beaucoup de libertés avec les normes
  SQL1 et SLQ2.

7
3.1b SQL2 (SQL92)

• 4 niveaux de conformité (dans l'ordre
  croissant) 
• Entry  SQL89  codes réponses SQLSTATE,
  renommage des colonnes résultats, mots-clés
  utilisables entre guillemets, métabase normalisée
  (schémas) 
• Transitional  niveau  Entry   quelques
  éléments du niveau  Intermédiate  
• Intermediate  niveau  Entry   types de
  données DATE avec opérations (DATE, TIME et
  TIMESTAMP), intervalles de temps, cascade des
  mises a jour, suppression en cas d'intégrité
  référentielle avec options, différents alphabets
  et ordres de lettres, possibilité de créer des
  domaines de valeurs, jointure externe
  ( outer-join ), expressions de SELECT (OUTER
  UNION, INTERSECT, EXCEPT) 
• Full  niveau  Intermediate   extension des
  dates et temps, expressions étendues avec
  correspondances de colonnes, possibilité de
  SELECT en argument d'un FROM, vues concrètes,
  contraintes d'intégrité impliquant plusieurs
  tables, contrôles d'intégrité différés.

8
3.2a Les bases de SQL les tables
Une colonne est appelée attribut et contient les
caractéristiques des enregistrements sous la
forme de valeurs prise dans un ensemble appelé
domaine

• Une table

Une ligne est appelée tuple ou enregistrement,
voire objet
Requêtes SQL2 CREATE TABLE Personnes (Nom
VARCHAR(20), Prenom VARCHAR(30)) INSERT INTO
Personnes VALUES ('Martin', 'Pierre') INSERT INTO
Personnes VALUES ('Martin', 'Josiane') INSERT
INTO Personnes VALUES ('Lefevre',
'Julienne') INSERT INTO Personnes(Prenom,Nom)
VALUES ('Michel','Dupont') INSERT INTO Personnes
VALUES ('Dupont', 'Jean') SELECT FROM
Personnes
9
3.2b Les bases de SQL les tables relationnelles
Il est préférable dajouter systématiquement une
clé primaire concise par exemple sous la forme
dun identifiant ID (compteur des enregistrements
insérés dans la table)

• Une table
• relationnelle

Une table nest une relation (ou  table
relationnelle ) que lorsquun sous-groupe de ses
attributs détermine une clé un tuple unique (à
tout instant). Une clé primaire est une clé
utilisée en priorité pour toute recherche ou
indexation.
Requêtes SQL2 DROP TABLE Personnes CREATE
TABLE Personnes (Id INTEGER PRIMARY KEY, Nom
VARCHAR(20), Prenom VARCHAR(30)) INSERT INTO
Personnes VALUES (1,'Martin', 'Pierre') INSERT
INTO Personnes VALUES (2,'Martin',
'Josiane') INSERT INTO Personnes VALUES
(3,'Lefevre', 'Julienne') INSERT INTO Personnes
VALUES (4,'Dupont', 'Michel') INSERT INTO
Personnes VALUES (5,'Dupont', 'Jean') SELECT
FROM Personnes
10
3.2c Les bases de SQL suppression dune ligne

• Utilisation de la clé primaire

Requêtes SQL2 DELETE FROM Personnes WHERE ID
3 SELECT FROM Personnes
Requêtes SQL2 DROP TABLE Personnes CREATE
TABLE Personnes (Id INTEGER PRIMARY KEY, Nom
VARCHAR(20), Prenom VARCHAR(30)) INSERT INTO
Personnes VALUES (1,'Martin', 'Pierre') INSERT
INTO Personnes VALUES (2,'Martin',
'Josiane') INSERT INTO Personnes VALUES
(3,'Lefevre', 'Julienne') INSERT INTO Personnes
VALUES (4,'Dupont', 'Michel') INSERT INTO
Personnes VALUES (5,'Dupont', 'Jean') SELECT
FROM Personnes
11
3.2d Les bases de SQL la projection
Sélection des colonnes (ou des pseudo-colonnes
résultant dun calcul) à afficher

• La projection

Requête SQL2 SELECT Nom, Prenom FROM Personnes
12
3.2e Les bases de SQL la restriction
Sélection des lignes à afficher éventuellement
grâce à des jokers( pour nimporte quelle suite
de caractères et _ nimporte quel caractère)

• La restriction

Requête SQL2 SELECT FROM Personnes WHERE Nom
like 'D'
13
3.2f Les bases de SQL les clés étrangères
Les valeurs prises par la clé primaire dune
table peuvent servir pour une colonne dune autre
table. On parle alors de clé étrangère
Les valeurs prises par la clé primaire dune
table peuvent servir pour une colonne dune autre
table. On parle alors de clé étrangère

• Une clé étrangère

Requêtes SQL2 ALTER TABLE Personnes ADD
Direction INTEGER REFERENCES Directions(Id) UPDATE
Personnes SET Direction 2 WHERE Id 1 OR ID
4 UPDATE Personnes SET Direction 1 WHERE Id
2 SELECT FROM Personnes
14
3.2g Les bases de SQL la jointure
Restriction du produit cartésien utilisant les
clés primaires et étrangères Elle est souvent
complétée par une projection pour éliminer
laffichage des clés et éventuellement une
restriction

• La jointure

Requête SQL2 SELECT Personnes.Nom, Prenom,
Directions.Nom AS "Direction", Adresse FROM
Personnes INNER JOIN Directions ON
Personnes.Direction Directions.Id WHERE
Personnes.Nom like 'D'
Requête SQL1 SELECT FROM Personnes,
Directions WHERE Personnes.Direction  Directions.
Id
15
3.2h Les bases de SQL les jointures externes
Restriction du produit cartésien utilisant les
clés primaires et étrangères Elle est souvent
complétée par une projection pour éliminer
laffichage des clés et éventuellement une
restriction

• Une jointure à gauche

Requêtes SQL2 SELECT Personnes.Nom, Prenom,
Directions.Nom AS "Direction", Adresse FROM
Personnes INNER JOIN Directions ON
Personnes.Direction Directions.Id WHERE
Personnes.Nom like 'D'
Requête SQL2 SELECT Personnes.Nom, Prenom,
Directions.Nom AS "Direction", Adresse FROM
Personnes LEFT JOIN Directions ON
Personnes.Direction Directions.Id WHERE
Personnes.Nom like 'D'
16
3.2i Les bases de SQL les vues
On peut stocker la requête sous la forme dune
vue une table  calculée  (mise à jour
dynamiquement) qui sera manipulée par la suite
comme les autres tables.

• Une vue

Requêtes SQL2 CREATE VIEW Personnes_Directions
AS SELECT Personnes.Nom, Prenom, Directions.Nom
AS "Direction", Adresse FROM Personnes LEFT JOIN
Directions ON Personnes.Direction Directions.Id
WHERE Personnes.Nom like 'D SELECT FROM
Personnes_Directions
17
3.2j Les bases de SQL les sélections avancées

• Des sélections avancées et des opérations
  ensemblistes 300 pages pour décrire SELECT
• En résumé
• SELECT DISTINCT ALL ltliste d'attributs
  ou de fonctionsgt FROM ltliste de tables, de vues
  ou de sélections entre parenthèsesgt WHERE
  ltconditions portant sur des attributs de tables,
  de vues ou de sélections entre parenthèsesgt
  GROUP BY ltliste d'attributsgt HAVING
  ltconditions portant sur des attributs de tables,
  de vues ou de sélections entre parenthèses gt
  ORDER BY ltliste d'attributsgt

18
3.2k Les bases de SQL les clauses where, group
by et having

• Un exemple de clauses Where, Group by et Having

Requêtes SQL2 INSERT INTO Personnes(Id,Nom,Preno
m) VALUES (7,'Pelle','Stephane') SELECT Nom,
COUNT(Nom) FROM Personnes WHERE Nom NOT LIKE
'M GROUP BY Nom HAVING COUNT(Nom) gt 1
Having permet de sélectionner a posteriori des
regroupements faits par GROUP BY
19
4.a Les éléments de JDBC les paquetages et les
pilotes

• Le paquetage java.sql.
• Quelques classes, des exceptions et des
  interfaces pour écrire les  drivers  (pilotes)
  et utiliser les services offerts par JDBC
  (connexions, requêtes, etc.)
• Les  drivers  (ou pilotes)
• La liste des pilotes référencés par Sun
  Microsystems avec les principales et
  caractéristiques et les fournisseurs se trouvent
  à ladresse
• java.sun.com/products/jdbc/jdbc.drivers.html
• On distingue 4 types de pilotes

20
4.b Les éléments de JDBC les services

• Les principaux services
• gérer une connexion à une source de données
  classes DriverManager et DriverPropertyInfo, et
  interfaces Driver et Connection
• créer et exécuter une requête SQL interfaces
  Statement, PreparedStatement et CallableStatement
  
• gérer le résultat dune requête SQL interface
  ResultSet
• associer une valeur de type SQL à un type Java
  interfaces Array, Blob, Clob, Date, Ref, Struct,
  Time, Timestamp et Types
• définir des associations entre les types SQL
  définis par lutilisateur et et des types Java
  interfaces SQLData, SQLInput et SQLOutput
• fournir des informations sur la base de données
  ou le résultat dune requête interfaces
  DataBaseMetaData et ResultSetMetaData
• contrôler lexécution exceptions SQLException,
  SQLWarning, DataTruncation, BatchUpdateException
  
• gérer la sécurité interface SQLPermission.

21
4.c Les éléments de JDBC les types de pilotes

• 4 types de pilotes JDBC
• type 1   Passerelle ou Pont  JDBC-ODBC écrit
  en Java, il fait appel aux fonctions dODBC
  écrites en C (maintenance à prévoir et problèmes
  de sécurité)
• type 2  interface dAPI native du SGBD, par
  exemple OCI (Oracle Call Interface), généralement
  écrite en C, impose le chargement du code de
  lAPI
• type 3  appelé JDBC-NET, écrit en Java, il
  envoie les requêtes à un serveur intermédiaire
  respectant le protocole du SGBD (parfait pour les
  applets si le serveur intermédiaire et le serveur
  web sont sur la même machine)
• type 4  interface écrite en Java, par exemple
  Thin dOracle, utilisant directement le protocole
  réseau du SGBD (préconisé pour les intranets).

22
4.d Les éléments de JDBC les différences entre
connexions

• Quelques éléments de comparaisons entre une
  connexion vers Microsoft Access 97 via le Pont
  JDBC-ODBC de Sun et une connexion vers Oracle 9i
  via le pilote Thin dOracle.

23
5. La chronologie classique des ordres JDBC

• Pour bien utiliser JDBC, il faut appliquer la
  chronologie suivante
• 1. indiquer le chemin des pilotes utilisés
• 2. gérer les pilotes
• 3. gérer les erreurs et les avertissements
  envoyés par JDBC et le SGBD
• 4. déterminer le pilote à utiliser pour obtenir
  une connexion
• 5. obtenir une connexion à une base de données
• 6. exploiter les métadonnées de la base
• 7. créer une requête SQL
• 8. exécuter la requête SQL (éventuellement
  plusieurs fois)
• 9. exploiter la table de résultats de la requête
  (en fonction des métadonnées associées aux
  résultats)
• 10. fermer la table de résultats
• 11. fermer la requête
• 12. valider ou annuler la transaction
• 13. fermer la connexion.

24
5.1 La chronologie JDBC le chemin des pilotes

• Si le fichier contenant par exemple le pilote
  Thin dOracle sappelle classe12.zip et se trouve
  dans le répertoire oracle sur le disque C, on
  peut employer une des trois techniques suivantes
  (présentées ici avec la syntaxe Windows) 
• modifier la variable denvironnement CLASSPATH 
• set CLASSPATH CLASSPATHC\oracle\classe12.z
  ip
• utiliser le paramètre classpath lors de chaque
  appel à java 
• java classpath "...C\oracle\classe12..."
  ... MonProgramme
• ou bien ajouter le fichier de classes du pilote
  dans le sous-répertoire jre\lib\ext du JDK (Java
  Development Kit).

25
5.2a La chronologie JDBC gérer les pilotes
Méthode préconisée avec un fichier de propriétés
dans lequel on ajoutera le nom de lutilisateur
et éventuellement son mot de passe.

• On présente ci-dessous quatre méthodes pour
  enregistrer simultanément les drivers Thin
  dOracle et le Pont JDBC-ODBC de Sun 
•  modifier la propriété jdbc.drivers du système
  pour fournir la liste des pilotes à
  charger (séparés par le caractère   ) 
• System.setProperty("jdbc.drivers",
  "oracle.jdbc.driver.OracleDriversun.jdbc.odbc.Jdb
  cOdbcDriver")
•  charger chaque classe de pilote par son nom 
• Class.forName("oracle.jdbc.driver.OracleDriver")
  
• //Class.forName(...).newInstance()pour certaines
  JVM
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").new
  Instance()
•  fournir à la méthode registerDriver du
  DriverManager une instance de chaque classe
• DriverManager.registerDriver (new oracle.jdbc.dri
  ver.OracleDriver())
• DriverManager.registerDriver (new sun.jdbc.odbc.J
  dbcOdbcDriver())
• On peut désactiver un pilote grâce à la méthode
  deregisterDriver du DriverManager.
•  ou bien définir la propriété jdbc.drivers avec
  la liste des pilotes à charger (séparés par le
  caractère   ) lors de chaque appel à java 
• java -Djdbc.driverssun.jdbc.odbc.JdbcOdbcDriver
  oracle.jdbc.driver.OracleDriver ... MonProgramme

Méthode utilisée pour permettre à lutilisateur
dajouter un pilote lors de lexécution.
La méthode setLogWriter permet de rediriger les
messages émanant du gestionnaire de pilotes
26
5.2b La chronologie JDBC gérer les pilotes

• Exemples de procédures statiques pour gérer les
  pilotes
• Attention, le premier appel à la méthode
  DriverManager.getDrivers charge les propriétés du
  système et toute modification ultérieure par la
  méthode System.setProperty ne sera pas prise en
  compte  il faudra alors utiliser une autre
  méthode.
• Résultats de lexécution

27
5.3 La chronologie JDBC gérer les erreurs et
les avertissements envoyés par JDBC et le SGBD

• Le SGBD et JDBC renvoient des erreurs et des
  avertissements sous la forme dExceptions de type
  SQLException et SQLWarning. Mais ces
   exceptions  sont un peu particulières car
  elles sont chaînées et possèdent deux codes
  derreur 
• un code derreur défini par la norme SQL92 que
  lon obtient grâce à la méthode getSQLState 
• un code défini par le fournisseur que lon
  obtient grâce à la méthode getErrorCode.
• Exemples de procédures statiques pour gérer les
  messages du SGBD et de JDBC
• Attention, si les avertissements de type
  SQLWarning sont construits sur le modèle des
  SQLException, ils ne sont pas levés comme des
  exceptions mais attachés  silencieusement  aux
  différents objets JDBC. On précisera par la suite
  la possibilité dobtenir des avertissements.

28
5.4 La chronologie JDBC déterminer le pilote à
utiliser pour obtenir une connexion

• Les pilotes implémentent nécessairement
  linterface Driver qui contient quelques méthodes
  permettant de connaître les caractéristiques de
  chaque pilote.
• La méthode Driver.jdbcCompliant ne devrait
  renvoyer true que pour les pilotes conformes aux
  spécifications de JDBC (et à  SQL92 Entry )
• Les méthodes Driver.GetMajorVersion et
  Driver.GetMajorVersion renvoient quant à elles
  les numéros de version et de révision du pilote.
• Lorsquon lui demande une connexion, le
  DriverManager choisit le premier pilote
  enregistré compatible avec lURL (Unified
  Resource Locator) fournie.
• On peut prévoir ce choix grâce à la méthode
  statique DriverManager.getDriver qui renvoie le
  premier pilote compatible avec lURL fournie sous
  forme dune String.
• Cette méthode fait appel à la méthode
  Driver.acceptsURL qui renvoie simplement true ou
  false. Ainsi si on sait que le driver qui sera
  choisi nest pas celui souhaité pour telle ou
  telle raison, on pourra le désactiver au
  préalable.

29
5.5a La chronologie JDBC obtenir une connexion

• Connection conn DriverManager.getConnection(Stri
  ng URL, String Utilisateur, String Mot_De_Passe)
• Utiliser la méthode statique DriverManager.getConn
  ection qui renvoie un objet dune classe
  implémentant linterface Connection (le moyen le
  plus simple).
• La méthode DriverManager.getConnection a
  plusieurs formes mais il faut dans tous les cas
  fournir lURL de la base de données et le plus
  souvent un nom dutilisateur connu par le SGBD
  avec son mot de passe.
• Si la demande de connexion na pas abouti, une
  erreur de type SQLException est levée.
• On peut également utiliser la méthode
  getConnection dune classe implémentant
  linterface DataSource du paquetage javax.sql..

30
5.5b La chronologie JDBC utiliser une source de
données

• Obtenir autrement un objet Connection (grâce au
  paquetage javax.sql.)
• utiliser linterface DataSource
• le pilote Thin (classe12.zip) dOracle permet par
  exemple dutiliser la classe OracleDataSource
  lorsquon importe le paquetage oracle.jdbc.pool.
• garder les connexions préétablies tout en
  libérant les ressources inutiles ( pool  de
  connexions)
• le pilote Thin (classe12.zip) dOracle permet par
  exemple dutiliser les classes PooledConnection
  et OracleConnectionPoolDataSource lorsquon
  importe le paquetage oracle.jdbc.pool.

31
5.5c La chronologie JDBC les URL

• Forme générale des URL pour JDBC
• jdbcltsous-protocolegtltparamètresgt
• Le nom du sous-protocole ainsi que la forme des
  paramètres sont fournis dans la documentation du
  pilote et du SGBD.
• Quelques exemples
• jdbcodbcTestBD
• Pour se connecter, via le Pont JDBC-ODBC (pilote
  de type 1), à une base de données dont le DSN
  (Data Source Name) est TestBD dans le
  gestionnaire de liaisons ODBC.
• jdbcoraclethin_at_colargol.ensg.ign.fr1521cpri
• Pour se connecter, via le pilote Thin (pilote de
  type 4), à une base de données cpri gérée par
  Oracle9i sur un serveur nommé colargol.ensg.ign.fr
  à partir du port 1521.
• jdbcoraclethin_at_(description(address(hostcola
  rgol.ensg.ign.fr)(protocoltcp)(port1521))(connec
  t_data(sidcpri)))
• On peut également préciser la fonction de chaque
  paramètre.

32
5.5d La chronologie JDBC la connexion

• La fermeture dune connexion sera nécessaire pour
  libérer les ressources utilisées sans attendre le
  ramasse-miettes (Garbage Collector).
• Exemple de méthode statique qui gère louverture
  dune connexion, les erreurs, les avertissements
  et la fermeture (automatique en cas derreur)

33
5.6 La chronologie JDBC les paramètres de la
connexion et les métadonnées de la base

• Une connexion et les requêtes SQL qui pourront
  être émises grâce à elle dépendent des
  caractéristiques du pilote JDBC, du SGBD et des
  droits de lutilisateur de la base de données.
• Pour gérer les paramètres de la connexion, on
  dispose de méthodes get... ou is pour obtenir et
  set... pour modifier les paramètres de la
  connexion
• Exemple de méthode statique donnant un aperçu des
  paramètres les plus utiles
• Exemple de méthode statique donnant un aperçu des
  métadonnées

34
5.7a La chronologie JDBC créer et exécuter une
requête SQL

• Exemple de requête renvoyant un nombre
  dopérations
• Statement requete connexion.createStatement()
• int nb requete.executeUpdate(
• "INSERT INTO Personnes(Nom,Prenom) VALUES
  ('Martin','Michel')")
• System.out.println("Nombre de lignes insérées
  dans la table"
• "Personnes " nb)
• requete.close()

Interprétation et exécution dune requête DDL ou
DML.
Création dune requête.
executeUpdate renvoie un code ou le nombre
dopérations effectuées.
Fermeture de la requête pour libérer
immédiatement les ressources devenues inutiles.
35
5.7b La chronologie JDBC préparer une requête
SQL

• Exemple de requête préparée renvoyant un ensemble
  de résultats
• PreparedStatement requetepreparee
  connexion.prepareStatement(
• "SELECT FROM Personnes")
• ResultSet resultat requetepreparee.executeQuery(
  )
• / exploitation du résultat /
• Montrer_ResultSet(resultat,System.out,'
  ',"NULL")
• resultat.close()
• requetepreparee.close()

Précompilation dune requête.
Exécution directe dune requête SELECT. Elle
renvoie un ensemble de résultats.
Fermeture de lensemble de résultats pour libérer
immédiatement les ressources devenues inutiles.
Fermeture de la requête pour libérer
immédiatement les ressources devenues inutiles.
36
5.7c La chronologie JDBC paramétrer une
requête SQL

• Exemple de requête paramétrée renvoyant un
  ensemble de résultats
• PreparedStatement requeteparametree
  connexion.prepareStatement(
• "INSERT INTO Personnes(Nom,Prenom)
  VALUES ( ? , ? )")
• requeteparametree.setString(1, "Martin")
• requeteparametree.setString(2, "Pierre")
• int nb requeteparametree.executeUpdate()
• System.out.println("Nombre de lignes insérées
  dans la table Personnes "nb)
• requeteparametree.setString(2, "Josiane")
• nb requeteparametree.executeUpdate()

Précompilation dune requête paramétrée par des
'?'.
Pour fournir les paramètres 1 et 2 à partir de
chaînes de caractères Java (choisir la méthode en
fonction du type de la valeur SQL)
Les paramètres sont conservés tant quon appelle
pas la méthode clearParameters ou une méthode
daffectation.
Fermeture de la requête pour libérer
immédiatement les ressources devenues inutiles.
37
5.7d La chronologie JDBC appeler une procédure
stockée

• Exemple dappel de procédure stockée
• CallableStatement fonctionstockee
  connexion.prepareCall(
• "? call Length(?)" )
•  
• fonctionstockee.registerOutParameter(1,Types.VARCH
  AR)
•  
• fonctionstockee.setString(2, "Toto")
• fonctionstockee.executeQuery()
• System.out.println("Length('Toto')gt"
  fonctionstockee.getString(1))
•  

Précompilation dun appel de procédure stockée
paramétrée par des '?'.
Méthode utilisée pour déclarer le
pseudo-paramètre 1 en sortie et le récupérer sous
la forme dune chaîne SQL.
Les méthodes get (corollaires des méthodes set)
permettent de traduire en Java les valeurs en
sortie.
Fermeture de la requête pour libérer
immédiatement les ressources devenues inutiles.
38
5.7e La chronologie JDBC la traduction des
requêtes SQL

• JDBC fournit une syntaxe entre accolades    
  indépendante des SGBD pour certaines requêtes
• appel de procédure stockée call Procedure(?)
• par Thin pour Oracle9i gt BEGIN Procedure(1)
  END
• appel de fonction stockée fn Fonction(?) ou
  ? call Fonction(?)
• par Thin pour Oracle9i gt BEGIN 1
  Fonction(2) END
• conversion dune chaîne en date d '2003-10-8'
• par Thin pour Oracle9i gt TO_DATE
  ('2003-10-8','YYYY-MM-DD')
• joker neutralisé dans une comparaison de chaînes
  Like '\' escape '\' Oracle9i via Thin 
  like '\' ESCAPE '\'
• par Thin pour Oracle9i gt like '\' ESCAPE '\'
• jointure externe gauche oj Personnes left
  outer join Directions on Personnes.direction
  Directions.id
• Access97 via JDBC-ODBC supporte cette syntaxe
  mais pas Oracle9i via Thin
• La méthode nativeSQL de linterface Connection
  permet dobtenir la version traduite de la
  requête SQL sous la forme dune Java String.

39
5.7f La chronologie JDBC grouper des ordres DDL
ou DML

• Exemple de regroupement de requêtes DDL ou DML
• Statement requete connexion.createStatement()
•  
• requete.addBatch(
• "INSERT INTO Personnes(Nom,Prenom) VALUES
  ('Martin','Pierre')")
• requete.addBatch(
• "INSERT INTO Personnes(Nom,Prenom) VALUES
  ('Martin','Josiane')")
•  
• int nb requete.executeBatch()
• for (int i0iltnb.lengthi)
• System.out.println("Nombre de lignes insérées
  dans la table"
• " Personnes  " nbi)
•  

Ajout dune requête DDL ou DML.
Exécution globale de toutes les requêtes.
Exploitation des résultats.
40
5.8 La chronologie JDBC exécuter une requête

• Utiliser executeQuery pour les requêtes de
  sélection
• Utiliser executeUpdate pour les requêtes DDL ou
  DML
• Utiliser execute pour les autres requêtes ou
  celles susceptibles de renvoyer plusieurs
  ensembles de résultats chaînés
• Exemple de méthode statique pour exécuter une
  requête en fonction de la syntaxe SQL
• Exemple de méthode statique pour exécuter un
  groupes de requêtes en fonction de la syntaxe SQL

41
5.9a La chronologie JDBC les métadonnées dun
ResultSet

• Un ResultSet est un ensemble virtuel de lignes et
  de colonnes. Les caractéristiques des colonnes
  (label, type de données, taille de la valeur,
  etc.) sont obtenues grâce aux métadonnées.
• On accède à ces métadonnées ResultSetMetaData
  grâce à la méthode getMetaData de linterface
  ResultSet. Les métadonnées ResultSetMetaData ont
  des méthodes get ou is qui permettent de
  caractériser les colonnes.
• Les colonnes sont numérotées à partir de 1. Leur
  nombre est fourni par la méthode getColumnCount
  de linterface ResultSetMetaData.
• Exemple de méthode statique pour exploiter les
  métadonnées dun ResultSet

42
5.9b La chronologie JDBC le contenu dun
ResultSet

• Un ResultSet est en fait un curseur (par défaut
  en mode  lecture seulement  du premier au
  dernier enregistrement).
• Le numéro dun enregistrement sobtient grâce à
  la méthode getRow.
• Un enregistrement est, en général, atteint par la
  méthode next
• On accède aux valeurs grâce à des méthodes get
  en fonction du type Java à obtenir. Les méthodes
  get... ont deux formes
• la première utilise un numéro de colonne de 1 à
  getColumnCount de linterface ResultSetMetaData
• la deuxième utilise le nom de la colonne sous
  forme dune chaîne de caractères de type String.
  La méthode findColumn renvoie le numéro de la
  première colonne dont le nom est égal au
  paramètre sous forme de String.
• Exemple de méthode statique pour afficher le
  contenu dun ResultSet

43
5.9c La chronologie JDBC lutilisation dun
ResultSet

• La fermeture dun ResultSet se fait par la
  méthode close. Elle est automatique lorsque la
  requête est fermée ou réexécutée. Cependant, il
  est toujours préférable de libérer le plus tôt
  possible les ressources inutiles.
• Lorsque la méthode getType renvoie un type
  différent de TYPE_FORWARD_ONLY, alors ce
  ResultSet est  défilant  et on peut utiliser la
  méthode previous, ainsi que des méthodes de
  positionnement direct
• beforeFirst  avant le premier enregistrement 
• first  sur le premier enregistrement 
• last  sur le dernier enregistrement 
• afterLast  après le dernier enregistrement.
• absolute  directement sur lenregistrement
  numéro n (si n est négatif, le positionnement se
  fait à partir de la fin)  si n vaut 1, on se
  positionne sur le premier enregistrement et si n
  vaut 1 on se positionne sur le dernier (le
  premier en partant de la fin) 
• relative  directement sur lenregistrement situé
  à une distance n (si n est négatif le déplacement
  se fait en remontant)  si n vaut 0, il ny a pas
  de déplacement, si n vaut 1 on se positionne sur
  lenregistrement suivant et sil vaut 1, on se
  positionne sur le précédent.

44
5.9d La chronologie JDBC la mise à jour dun
ResultSet

• Lorsque la méthode getConcurrency dun ResultSet
  renvoie CONCUR_UPDATABLE alors ce ResultSet est
   actualisable  et peut servir à modifier la
  base de données
• exemple de modification des valeurs dun
  enregistrement
• rs.updateString("NOM", "MARTIN") //remplace la
  valeur dans NOM
• // modification des autres valeurs
• rs.updateRow() // modification de
  lenregistrement dans la BD
• // On peut également annuler les modifications
  grâce à la méthode cancelRowUpdates.
• exemple dajout dun enregistrement dans la base
  de données
• rs.moveToInsertRow() // on se met sur
  lenregistrement dinsertion
• rs.updateString("NOM", "DUPONT") // modification
  dune valeur
• // modification des autres valeurs
• rs.insertRow() // ajout de lenregistrement dans
  la BD
• rs.moveToCurrentRow() //retour sur
  lenregistrement "courant"
• La suppression de lenregistrement courant dans
  la base de données deleteRow
• Lactualisation dun enregistrement à partir de
  la BD refreshRow
• Attention, cette méthode est soumise au niveau
  disolation de la connexion ainsi quau type du
  curseur ResultSet. Cette méthode est évidemment
  gourmande en ressources et doit être utilisée
  avec parcimonie

45
5.10,11,12 13 La chronologie JDBC les
fermetures

• Il est fortement conseillé de fermer les
  ResultSet, Statement, PreparedStatement et
  Connection dès que possible pour libérer les
  ressources utilisées, sans attendre le
  ramasse-miettes (Garbage Collector).
• La fermeture dun ensemble de résultats
  (ResultSet) est normalement automatique lors de
  la réexécution ou de la fermeture de la requête
  (Statement ou PreparedStatement).
• La fermeture dune requête (Statement ou
  PreparedStatement) est normalement automatique
  lors de la fermeture de la connexion
  (Connection).
• La fermeture se fait grâce à la méthode close des
  interfaces ResultSet, Statement,
  PreparedStatement et Connection. Elle peut
  éventuellement lever une SQLException, notamment
  lorsquon na pas validé, ni annulé la dernière
  transaction.
• Pour savoir si une connexion a été fermée, on
  utilise la méthode isClosed de linterface
  Connection qui renvoie true si la connexion a été
  fermée. Cette méthode peut également lever une
  SQLException

46
6a JDBC et les types de données SQL nouveaux
types SQL

• Dans certains SGBD, on peut définir un nouveau
  type de données SQL.
• Exemple de requêtes Oracle9i 
• CREATE TYPE tPoint AS OBJECT (X NUMBER, Y NUMBER)
• CREATE TABLE Points (Id INTEGER PRIMARY KEY,
  Point tPoint)
• INSERT INTO Points VALUES (1, tPoint(122.59,740.52
  ))
• Attention, le pilote Thin dOracle ne supporte
  pas toujours la récupération des chaînes de
  caractères lorsque le jeu de caractères de la
  base nest pas US7ASCII ou WE9iSO8859P1.
  Lexemple choisi nutilise donc que des nombres
  flottants.
• Les méthodes getObject et setObject de
  linterface ResultSet peuvent être utilisées
  conjointement avec linterface STRUCT et ses
  méthodes getSQLTypeName et getAttributes.
• Exemple dutilisation de linterface STRUCT

47
6b JDBC et les types de données SQL exemple de
STRUCT

• Exemple dutilisation de linterface STRUCT pour
  lexemple précédent
• Statement requete connexion.createStatement()
• ResultSet rs_obj requete.executeQuery("select
  from Points")
• while (rs_obj.next())
• int id rs_obj.getInt(1)
• System.out.print("Point Id "id" ")
• java.sql.Struct Point (java.sql.Struct)rs_obj.
  getObject(2)
• Object attr Point.getAttributes()
• System.out.println("X "((java.math.BigDecimal
  )attr0).doubleValue())
• System.out.println("Y "((java.math.BigDecimal
  )attr1).doubleValue())

Exécution dune requête
Récupération de lidentifiant sous forme dun
entier.
Récupération de la valeur dans la colonne de type
tPoint
Récupération des attributs
Utilisation des valeurs après conversion
48
6c JDBC et les types de données SQL linterface
SQLData

• Pour accéder aux  variables  présentes dans le
  type tPoint présenté en exemple, on peut créer
  une classe Java SQLPoint qui implémente
  linterface SQLData.
• Chaque colonne du type créé précédemment doit
  avoir un champ homologue (dun type Java ici
  double), auquel il faut ajouter une String pour
  conserver le nom du type dans la base de données.
  Les méthodes à mettre en œuvre sont 
• getSQLTypeName  renvoie le nom du type de
  données sous forme dune String 
• readSQL  remplit les champs de la classe Java à
  partir dun flux dentrée de type SQLInput et
  dune String contenant le nom du type dans la
  base de données. Les lectures se font grâce à des
  méthodes read... qui dépendent du type Java 
• writeSQL  écrit les champs de la classe Java
  dans un flux de sortie de type SQLOutput. Les
  écritures se font grâce à des méthodes write...
  qui dépendent du type Java.
• Il faut compléter la table dassociations liées à
  la connexion grâce à la classe Map du paquetage
  java.util. et à la méthode getTypeMap de
  linterface Connection.

49
6d JDBC et les types de données SQL nouveaux
types SQL
Implémentation de linterface SQLData

• import java.sql.
• public class SQLPoint implements SQLData
• public double X
• public double Y
• private String sql_type
•   public SQLPoint(String sql_type, double X,
  double Y)
• this.sql_type sql_type this.X X
  this.Y Y
• public String getSQLTypeName() throws
  SQLException
• return sql_type
•   public void writeSQL(SQLOutput flux_sortie)
  throws SQLException
• flux_sortie.writeDouble(X)
  flux_sortie.writeDouble(Y)
• public void readSQL(SQLInput flux_entree,
  String NomDuType)
• throws SQLException
• sql_type NomDuType
• X flux_entree.readDouble() Y
  flux_entree.readDouble()

Utilisation dune String pour conserver le nom du
type SQL
Renvoie le nom du type SQL dans le SGBD
Écriture de lobjet dans le SGBD
Lecture de lobjet dans le SGBD
50
6e JDBC et les types de données SQL linterface
Map
Ajout dans la table dassociations de la
connexion (interface Map)

• java.util.Map correspondances
  connexion.getTypeMap()
• correspondances.put("TPOINT", SQLPoint.class)
•  
• Statement requete connexion.createStatement()
• ResultSet rs_obj requete.executeQuery("select
  from Points")
• while (rs_obj.next())
• int id rs_obj.getInt(1)
• System.out.print("Point Id "id" ")
•  
• SQLPoint point (SQLPoint)rs_obj.getObject(2)
•  
• System.out.println("X "point.X)
• System.out.println("Y "point.Y)
•  
• rs_obj.close()

Récupération de lidentifiant sous forme dun
entier.
Récupération de la valeur dans la colonne de type
tPoint
Utilisation directe des valeurs
51
7a Un exemple de gestion de BD des  scripts
SQL 

• On se propose dutiliser des fichiers de
  commandes que lon appellera scripts SQL.
• On définit plusieurs méthodes statiques pour
• charger les propriétés telles que le nom du
  pilote, lURL de la base de données, le nom de
  lutilisateur reconnu par le SGBD (avec
  éventuellement son mot-de-passe) ainsi que le nom
  du script à exécuter 
• lire des lignes du script 
• exécuter les requêtes ou groupe de requêtes SQL
  écrites sur une ou plusieurs lignes.

52
7b Un exemple de gestion de BD un exemple de
script SQL

• Les scripts SQL utiliseront des balises ltgt et
  lt\gt comme en html.
• ltREMgt fichier "bdtrains.sql 
• ltPROMPTgt
• GESTION DU RESEAU FERRE DE GARES ET DES
  HORAIRES DES TRAINS
• lt/PROMPTgt
• / Activation du mode transactionnel
• pour pouvoir annuler (ROLLBACK) /
• SET TRANSACTION
• create table tTrains (\
• Id INTEGER primary key,\
• numero VARCHAR(10) not null)

Balise ltREMgt commentaires sur une ligne
Balises ltPROMPTgtlt\PROMPTgt texte à afficher
Balises // commentaires éventuellement sur
plusieurs lignes
Pas de balise ordre SQL sur une seule ligne
\ ordre SQL complété sur la ligne suite
53
7c Un exemple de gestion de BD Un exemple de
script

• Cet exemple de base de données doit permettre de
  gérer la vente de billets de trains (diagramme de
  classes UML)
•  Script SQL  de la  partie réseau  pour
  Access via le Pont JDBC-ODBC de Sun
• On verra plus loin le  script SQL  équivalent
  pour Oracle9i via Thin dOracle

54
7d Un exemple de gestion de BD les fichiers de
propriétés

• Des fichiers de propriétés (fichiers textes
  formatés)
• chaque ligne a pour structure ltrubriquegtltvaleurgt
• le chargement des valeurs se fait grâce aux
  méthodes getProperty (de la classe Properties).
• Elles prennent en paramètre le nom de la rubrique
  (et éventuellement une valeur par défaut) sous la
  forme dune chaîne de caractères String.
• Elles renvoient une chaîne de caractères String
  contenant la valeur trouvée ou éventuellement la
  valeur par défaut.
• Exemple de méthode statique pour utiliser un
  fichier de propriétés

55
7e Un exemple de gestion de BD 2 exemples de
Properties

• Exemple de fichier Properties pour Access via le
  Pont JDBC-ODBC de Sun
• jdbc.driverssun.jdbc.odbc.JdbcOdbcDriver
• jdbc.urljdbcodbcbdtrains
• jdbc.user
• jdbc.password
• jdbc.scriptsaccess_bdtrains_reseau.sqlaccess_bdt
  rains_billets.sql
• //les noms de scripts sont séparés par
• Exemple de fichier Properties pour Oracle via
  Thin dOracle
• jdbc.driversoracle.jdbc.driver.OracleDriver
• jdbc.urljdbcoraclethin_at_colargol.ensg.ign.fr15
  21cpri
• jdbc.userbdtrains
• jdbc.passwordmot_de_passe_de_bdtrains
• jdbc.scriptsoracle_bdtrains_reseau.sqloracle_bdt
  rains_billets.sql
• //comme les pilotes, les noms de scripts sont
  séparés par 

56
7f Un exemple de gestion de BD exécuter un
 script SQL 

• Interprétation des requêtes SQL écrites dans un
  ou plusieurs fichiers textes avec des balises
• Appel de fonction ou de procédure stockée
• préparer lappel de fonction ou de procédure
  stockée
• analyser les paramètres
• exécuter lappel de fonction ou de procédure
  stockée
• Utilisation dune requête paramétrée
• préparer la requête paramétrée
• exécuter la requête paramétrée
• Application dune requête à un ensemble de
  résultats ( Fetch  ou  boucle SQL )
• transformer une ligne de résultats en Vector de
  String
• exécuter la même requête sur chaque ligne d'un
  ResultSet

57
7g Un exemple de gestion de BD 3  scripts SQL 

• Exemple de  script SQL  pour Access via le Pont
  JDBC-ODBC de Sun
• la création de la source de données ODBC
• le  script SQL 
• le résultat de lexécution
• le fichier de traces
• Exemples de  scripts SQL  pour Oracle9i via
  Thin dOracle
• le  script SQL   équivalent pour Oracle9i via
  Thin
• les différences avec le  script SQL  pour
  Access
• un  script SQL  pour une procédure stockée

58
The End Maintenant vous pouvez presque
Utiliser JDBC avec le
59
Annexe le site du DESS AIST
60
Annexe le Département Informatique de lENSG
LENSG (une direction de lIGN)
La Direction des Études(un service de lIGN)
Le Département Informatique (ex-CPRI)
61
Annexe la CPRI
62
Annexe lENSG
63
Annexe lIGN
64
Annexe ISO/IEC 9075
65
Annexe ANSI
66
Annexe IEEE
67
Annexe ASCII
68
Annexe de lalgorithme à Java
Java
69
Annexe de lalgorithme à Java (boucles suite)
Java
70
Annexe de lalgorithme à Java
(débranchements,...)
Java
71
Annexe le mécanisme d'héritage...
72
Annexe le diagramme de classes UML
73
Annexe les différences entre  scripts SQL 

• Le script précédent pour Oracle9i via le pilote
  Thin ressemble beaucoup à celui écrit pour Access
  via JDBC-ODBC, à ceci près que 
• les vues sont des éléments à part entière et
  gérées sous la forme de requêtes stockées 
• le type  counter  utilisé pour les identifiants
  nexiste pas dans Oracle et il est remplacé par
  la création dune  sequence  de type  int 
  (conformément au modèle UML) 
• la possibilité de définir un  trigger  de type
   instead of insert on  permet deffectuer
  linsertion dans la table en incrémentant le
  compteur alors que lutilisateur demande
  linsertion dans une vue (cette solution est
  pratique pour la notion dinterface chère à
  UML) 
• le type  double  nest pas reconnu par Oracle9i
  via Thin et il faut le remplacer par le type
   number  
• la concaténation de chaînes se fait grâce à deux
  barres verticales () au lieu de dans Access.