Transcript Chapitre 5

5 Interface entre SQL et
un programme

SQL incomplet

Défaut d'impédance (impedance mismatch)
20/07/2015
–
modèle de données BD 
–
modèle de données du langage de programmation
© Robert Godin. Tous droits réservés.
1
Interface programmatique (CallLevel Interface - SQL/CLI)


API spécifique au SGBD
–
e.g. Oracle Call Interface
–
non portable
API normalisée
–
standard de facto ODBC

20/07/2015
développé par Microsoft pour le C
–
pilote ODBC pour client/serveur
–
SQL/CLI de SQL:1999 inspirée de ODBC
–
JDBC pour Java
© Robert Godin. Tous droits réservés.
2
SQL enchâssé
(Embedded SQL)
Code SQL dans le source du langage hôte
 Syntaxe spéciale
 Pré-compilation

–

Oracle : pro*C/C++, pro*COBOL, …, JSQL
Moins portable
–
–
pré-compilateur spécifique au SGBD
traduit en API du SGBD
 SQLJ
20/07/2015
(partie 0) traduit en API standard JDBC
© Robert Godin. Tous droits réservés.
3
Extension procédurale à SQL
(Persistent Stored Modules SQL/PSM)

SQL +
–
–
–
–

structures de contrôle
procédures, fonctions, packages
support direct des types SQL pour les variables
exécution au niveau serveur de BD
Oracle
–
20/07/2015
PL/SQL
© Robert Godin. Tous droits réservés.
4
Routine invoquée en SQL
Stockée dans le schéma relationnel
 Exécutée au niveau du serveur de BD
 Langage

–
–
20/07/2015
SQL/PSM
SQLJ (partie 1) : routines stockées en Java
© Robert Godin. Tous droits réservés.
5
5.1

SQL enchâssé
Syntaxe
EXEC SQL opérationSQLEnchâssée END-EXEC {«;» en C}

Insertion d'une ligne dans la table Client
EXEC SQL INSERT INTO Client
VALUES (100, 'G. Lemoyne-Allaire', '911');
20/07/2015
© Robert Godin. Tous droits réservés.
6
Variables partagées

Déclaration

Utilisation de variables partagées
EXEC SQL BEGIN DECLARE SECTION;
int
no;
char
nom[15];
char
tel[15];
char
SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
scanf("%d%s%s",&no,nom,tel);
EXEC SQL INSERT INTO Client
VALUES (:no, :nom, :tel);

Vérification d ’exceptions avec SQLSTATE
If(strcmp(SQLSTATE, "xxxxx"))…
20/07/2015
© Robert Godin. Tous droits réservés.
7
5.1.1 SELECT à ligne
unique
EXEC SQL
SELECT nomClient, noTelephone
INTO :nom, :tel
FROM Client
WHERE noClient = 10;
printf("Nom: %s Téléphone: %s",nom, tel);
20/07/2015
© Robert Godin. Tous droits réservés.
8
5.1.2 Curseur SQL
void iterateurClient() {
/* Déclaration des variables partagées*/
EXEC SQL BEGIN DECLARE SECTION;
int
no;
char
nom[15];
char
SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
/*Déclaration du curseur*/
EXEC SQL DECLARE curseurClient CURSOR FOR
SELECT noClient, nomClient FROM Client WHERE noClient > 40;
/*Déclenchement de l'opération SQL*/
EXEC SQL OPEN curseurClient;
while(1){
/*Extraction de la ligne suivante*/
EXEC SQL FETCH FROM curseurClient INTO :no, :nom;
if(/* test de fin de table */
!strcmp(SQLSTATE, « 02000 »)) break;
/* traitement utilisant les variables partagées no et nom*/
}
/*Fermeture du curseur*/
EXEC SQL CLOSE curseurClient;
};
20/07/2015
© Robert Godin. Tous droits réservés.
9
5.1.3
Mises
à
jour
par
curseur
void iterateurModifieurArticle () {
/* Déclaration des variables partagées*/
EXEC SQL BEGIN DECLARE SECTION;
float
prix;
char
SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
/*Déclaration du curseur*/
EXEC SQL DECLARE curseurArticle CURSOR FOR
SELECT prixUnitaire FROM Article FOR UPDATE;
/*Déclenchement de l'opération SQL*/
EXEC SQL OPEN curseurArticle;
while(1){
/*Extraction de la ligne suivante*/
EXEC SQL FETCH FROM curseurArticle INTO :prix;
if(/* test de fin de table */
!strcmp(SQLSTATE, "02000")) break;
20/07/2015
if(prix > 25.0)
EXEC SQL DELETE FROM Article
WHERE CURRENT OF curseurArticle ;
Else
EXEC SQL UPDATE Article
SET prixUnitaire = prixUnitaire * 1.1
WHERE CURRENT OF curseurArticle ;
}
/*Fermeture du curseur*/
EXEC SQL CLOSE curseurArticle;
};
© Robert Godin. Tous droits réservés.
10
5.1.4 SQL dynamique
Char chaineSource[…];
printf("Entrez une opération SQL");
scanf("%s", chaineSource);
EXEC SQL PREPARE operationSQL FROM :chaineSource;
EXEC SQL EXECUTE operationSQL ;
20/07/2015
© Robert Godin. Tous droits réservés.
11
5.1.5 Connexion
(CONNECT)
BD
Réseau
Programme
d'application
Logiciel
intermédiaire
Pilote de
télécommunication
SGBD SQL
Logiciel
intermédiaire
Pilote de
télécommunication
Client
20/07/2015
Serveur
© Robert Godin. Tous droits réservés.
12
Suite

Syntaxe du CONNECT
CONNECT TO{[ nomServeur [AS nomConnexion]
[USER authorizationID]]|DEFAULT}

Exemple avec SQL enchâssé en C
EXEC SQL CONNECT TO labunix USER toto ;

Pour passer d ’un connexion à l ’autre
SET CONNECTION {[ nomConnexion]|DEFAULT}

Pour terminer une connexion
DISCONNECT {[nomConnection]|CURRENT|ALL}
20/07/2015
© Robert Godin. Tous droits réservés.
13
5.2 Introduction à l'extension
procédurale PL/SQL D'Oracle
PROCEDURE pStatutCommande
(leNoCommande
Commande.noCommande%TYPE ) IS
-- Déclaration de variables
leNoClient
Client.noClient%TYPE;
laDateCommande
Commande.dateCommande%TYPE ;
leNoArticle
Article.noArticle%TYPE ;
laQuantitéCommandée
LigneCommande.quantité%TYPE ;
laQuantitéLivrée
INTEGER;
laQuantitéEnAttente
INTEGER;
-- Déclaration d'un curseur (CURSOR) PL/SQL pour itérer sur les
lignes
-- de la commande
CURSOR lignesCommande(unNoCommande Commande.noCommande%TYPE)IS
SELECT
noArticle, quantité
FROM
LigneCommande
WHERE
LigneCommande.noCommande = unNoCommande ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Commande #:'||TO_CHAR(leNoCommande));
SELECT
INTO
FROM
WHERE
noClient, dateCommande
leNoClient, laDateCommande
Commande
noCommande = leNoCommande;
DBMS_OUTPUT.PUT_LINE('noClient:'||TO_CHAR(leNoClient));
DBMS_OUTPUT.PUT_LINE('dateCommande:'||TO_CHAR(laDateCommande));
20/07/2015
© Robert Godin. Tous droits réservés.
14
OPEN lignesCommande(leNoCommande);
-- Le OPEN ouvre le CURSOR en lui passant les paramètres
LOOP
FETCH lignesCommande INTO leNoArticle, laQuantitéCommandée;
-- Le FETCH retourne la ligne suivante
EXIT WHEN lignesCommande%NOTFOUND;
-- %NOTFOUND est un attribut du CURSOR qui permet de déterminer
-- si le FETCH a atteint la fin de la table
DBMS_OUTPUT.PUT('noArticle :');
DBMS_OUTPUT.PUT(leNoArticle);
DBMS_OUTPUT.PUT(' quantité commandée:');
DBMS_OUTPUT.PUT(laQuantitéCommandée);
-- Chercher
SELECT
INTO
FROM
WHERE
la quantité déjà livrée
SUM(quantitéLivrée)
laQuantitéLivrée
DétailLivraison
noArticle = leNoArticle AND
noCommande = leNoCommande ;
IF (laQuantitéLivrée IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(' livraison en attente');
ELSE
laQuantitéEnAttente:= laQuantitéCommandée -laQuantitéLivrée;
IF (laQuantitéEnAttente = 0) THEN
DBMS_OUTPUT.PUT_LINE(' livraison complétée');
ELSE
DBMS_OUTPUT.PUT (' quantité en attente :');
DBMS_OUTPUT.PUT_LINE(laQuantitéEnAttente);
END IF ;
END IF ;
END LOOP;
-- Le CLOSE ferme le CURSOR
CLOSE lignesCommande;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Numéro de commande inexistant');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Exception levée par la procédure');
END pStatutCommande;
20/07/2015
© Robert Godin. Tous droits réservés.
15
Compilation et stockage dans
le schéma avec SQL*plus
SQL> CREATE PROCEDURE pStatutCommande
2 (leNoCommande Commande.noCommande%TYPE ) IS
3
4
-- Déclaration de variables
5 leNoClient
Client.noClient%TYPE;
6
laDateCommande Commande.dateCommande%TYPE ;
7
leNoArticle
Article.noArticle%TYPE ;
8 laQuantitéCommandée LigneCommande.quantité%TYPE ;
9 laQuantitéLivrée INTEGER;
10 laQuantitéEnAttente INTEGER;
11
…
69 EXCEPTION
70
WHEN NO_DATA_FOUND THEN
71
DBMS_OUTPUT.PUT_LINE('Numéro de commande inexistant');
72
WHEN OTHERS THEN
73
RAISE_APPLICATION_ERROR(-20001,'Exception levée par la procédure');
74 END pStatutCommande;
75 /
Procedure created.
20/07/2015
© Robert Godin. Tous droits réservés.
16
Appel de la procédure
avec SQL*plus
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE pStatutCommande(2);
Commande #:2
noArticle :40 quantité :2 livraison complétée
noArticle :95 quantité :3 quantité en attente :2
PL/SQL procedure successfully completed.
SQL> EXECUTE pStatutCommande(5);
Commande #:5
noArticle :10 quantité :5 livraison en attente
noArticle :20 quantité :5 livraison en attente
noArticle :70 quantité :3 quantité en attente :1
PL/SQL procedure successfully completed.
SQL> EXECUTE pStatutCommande(10);
Commande #:10
Numéro de commande inexistant
PL/SQL procedure successfully completed.
20/07/2015
© Robert Godin. Tous droits réservés.
17
5.2.1 Bloc PL/SQL
[DECLARE
déclaration [déclaration] ...]
BEGIN
séquenceEnoncés
[EXCEPTION
exception_énoncé [exception_énoncé] ...]
END
20/07/2015
© Robert Godin. Tous droits réservés.
18
Exécution d ’un bloc
sous SQL*plus
SQL> DECLARE
2
laQuantitéEnStock Article.quantitéEnStock%TYPE;
3 BEGIN
4
SELECT quantitéEnStock INTO laQuantitéEnStock
5
FROM Article
6
WHERE noArticle = 10;
7
IF laQuantitéEnStock = 0 THEN
8
DBMS_OUTPUT.PUT_LINE('L article est en rupture de stock');
9
ELSE
10
DBMS_OUTPUT.PUT('Quantité en stock :');
11
DBMS_OUTPUT.PUT_LINE(laQuantitéEnStock);
12
END IF;
13 EXCEPTION
14
WHEN NO_DATA_FOUND THEN
15
DBMS_OUTPUT.PUT_LINE('Numéro d article inexistant');
16
WHEN OTHERS THEN
17
RAISE_APPLICATION_ERROR(-20001,'Erreur soulevée par le SELECT');
18 END;
19 /
Quantité en stock :20
PL/SQL procedure successfully completed.
20/07/2015
© Robert Godin. Tous droits réservés.
19
5.2.2 Déclaration de variables
PL/SQL (DECLARE)
20/07/2015
leNoClient
Client.noClient%TYPE;
leNoClient
INTEGER;
© Robert Godin. Tous droits réservés.
20
5.2.3 Transfert d'une valeur de colonne d'un
SELECT dans une variable (clause INTO)
SELECT
INTO
FROM
WHERE
20/07/2015
noClient, dateCommande
leNoClient, laDateCommande
Commande
noCommande = leNoCommande;
© Robert Godin. Tous droits réservés.
21
5.2.4 Affectation en
PL/SQL
laQuantitéEnAttente:= laQuantitéCommandée -laQuantitéLivrée;
20/07/2015
© Robert Godin. Tous droits réservés.
22
5.2.5 Structure de
contrôle IF
IF (laQuantitéLivrée IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(' livraison en attente');
ELSE
laQuantitéEnAttente:= laQuantitéCommandée -laQuantitéLivrée;
IF (laQuantitéEnAttente = 0) THEN
DBMS_OUTPUT.PUT_LINE(' livraison complétée');
ELSE
DBMS_OUTPUT.PUT (' quantité en attente :');
DBMS_OUTPUT.PUT_LINE(laQuantitéEnAttente);
END IF ;
END IF ;
20/07/2015
© Robert Godin. Tous droits réservés.
23
5.2.6 Boucles (LOOP,
FOR, WHILE)
LOOP
séquenceEnoncés
END LOOP ;
FOR indice IN [REVERSE] valeurInitiale..valeurFinale LOOP
séquenceEnoncés
END LOOP ;
WHILE condition LOOP
séquenceEnoncés
END LOOP ;
20/07/2015
© Robert Godin. Tous droits réservés.
24
5.2.7 Traitement d'exception
(EXCEPTION, RAISE)

Déclarer
nomException

EXCEPTION;
Soulever
RAISE nomException

Attraper
WHEN nomException THEN
séquenceÉnoncés
20/07/2015
© Robert Godin. Tous droits réservés.
25
5.2.8 Curseur PL/SQL
(CURSOR)
CURSOR lignesCommande(unNoCommande Commande.noCommande%TYPE)IS
SELECT
noArticle, quantité
FROM
LigneCommande
WHERE
LigneCommande.noCommande = unNoCommande ;
OPEN lignesCommande(leNoCommande);
-- Le OPEN ouvre le CURSOR en lui passant les paramètres
LOOP
FETCH lignesCommande INTO leNoArticle, laQuantitéCommandée;
-- Le FETCH retourne la ligne suivante
EXIT WHEN lignesCommande%NOTFOUND;
-- %NOTFOUND est un attribut du CURSOR qui permet de déterminer
-- si le FETCH a atteint la fin de la table
…
END LOOP;
-- Le CLOSE ferme le CURSOR
CLOSE lignesCommande;
20/07/2015
© Robert Godin. Tous droits réservés.
26
Boucle FOR pour
curseur PL/SQL
FOR uneLigne IN lignesCommande(leNoCommande) LOOP
DBMS_OUTPUT.PUT('noArticle :');
DBMS_OUTPUT.PUT(uneLigne.noArticle);
DBMS_OUTPUT.PUT(' quantité commandée:');
DBMS_OUTPUT.PUT(uneLigne.quantité);
-- Chercher
SELECT
INTO
FROM
WHERE
la quantité déjà livrée
SUM(quantitéLivrée)
laQuantitéLivrée
DétailLivraison
noArticle = uneLigne.noArticle AND
noCommande = leNoCommande ;
IF (laQuantitéLivrée IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(' livraison en attente');
ELSE
laQuantitéEnAttente:= uneLigne.quantité -laQuantitéLivrée;
IF (laQuantitéEnAttente = 0) THEN
DBMS_OUTPUT.PUT_LINE(' livraison complétée');
ELSE
DBMS_OUTPUT.PUT (' quantité en attente :');
DBMS_OUTPUT.PUT_LINE(laQuantitéEnAttente);
END IF ;
END IF ;
END LOOP;
20/07/2015
© Robert Godin. Tous droits réservés.
27
5.2.9 Procédures et
fonctions PL/SQL stockées
SQL>
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FUNCTION fQuantitéEnStock
(unNoArticle Article.noArticle%TYPE)
RETURN Article.quantitéEnStock%TYPE IS
uneQuantitéEnStock Article.quantitéEnStock%TYPE;
BEGIN
SELECT quantitéEnStock
INTO
uneQuantitéEnStock
FROM
Article
WHERE
noArticle = unNoArticle;
RETURN uneQuantitéEnStock;
END fQuantitéEnStock;
/
Function created.
SQL> select fQuantitéEnStock(10) from dual;
FQUANTITÉENSTOCK(10)
-------------------10
20/07/2015
© Robert Godin. Tous droits réservés.
28
Procédure stockée
SQL>
2
3
4
5
6
7
8
9
CREATE PROCEDURE pModifierQuantitéEnStock
(unNoArticle
Article.noArticle%TYPE,
nouvelleQuantitéEnStock
Article.quantitéEnStock%TYPE) IS
BEGIN
UPDATE Article
SET quantitéEnStock = nouvelleQuantitéEnStock
WHERE noArticle = unNoArticle;
END pModifierQuantitéEnStock;
/
Procedure created.
SQL> EXECUTE pModifierQuantitéEnStock(10,20);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Article WHERE noArticle = 10;
NOARTICLE DESCRIPTION
PRIXUNITAIRE QUANTITÉENSTOCK
---------- -------------------- ------------ --------------10 Cèdre en boule
10,99
20
20/07/2015
© Robert Godin. Tous droits réservés.
29
5.2.9.1 PRIVILÈGES DANS
UNE ROUTINE STOCKÉE

Privilèges de l'appelant (invoker rights)
CREATE PROCEDURE pModifierQuantitéEnStock
(unNoArticle
Article.noArticle%TYPE,
nouvelleQuantitéEnStock
Article.quantitéEnStock%TYPE)
AUTHID CURRENT_USER IS
BEGIN
UPDATE
Article
SET
quantitéEnStock = nouvelleQuantitéEnStock
WHERE
noArticle = unNoArticle;
END pModifierQuantitéEnStock;

Privilèges de la routine (definer rights)
–
–
20/07/2015
privilèges du créateur
par défaut
© Robert Godin. Tous droits réservés.
30
Exemple privilège de l’appelant
SQL> connect godin/oracle
Connected.
SQL> CREATE OR REPLACE PROCEDURE pModifierQuantitéEnStock
2 (unNoArticle
Article.noArticle%TYPE,
3
nouvelleQuantitéEnStock
Article.quantitéEnStock%TYPE)
4 AUTHID CURRENT_USER IS
5 BEGIN
6
UPDATE Article
7
SET quantitéEnStock = nouvelleQuantitéEnStock
8
WHERE noArticle = unNoArticle;
9 END pModifierQuantitéEnStock;
10 /
Procedure created.
SQL> grant execute on pModifierQuantitéEnStock to public;
Grant succeeded.
SQL> connect test/oracle
Connected.
SQL> EXECUTE godin.pModifierQuantitéEnStock (10,500);
PL/SQL procedure successfully completed.
SQL> select * from article;
NOARTICLE
---------10
20
…
DESCRIPTION
PRIXUNITAIRE QUANTITÉENSTOCK
-------------------- ------------ --------------Cèdre en boule
10,99
500
Sapin
12,99
10
SQL> select * from godin.article;
select * from godin.article
*
ERROR at line 1:
ORA-00942: table or view does not exist
20/07/2015
© Robert Godin. Tous droits réservés.
31
Exemple privilège du créateur
SQL> connect godin/oracle
Connected.
SQL> CREATE OR REPLACE PROCEDURE pModifierQuantitéEnStock
2 (unNoArticle
Article.noArticle%TYPE,
3
nouvelleQuantitéEnStock
Article.quantitéEnStock%TYPE)
4 IS
5 BEGIN
6
UPDATE Article
7
SET quantitéEnStock = nouvelleQuantitéEnStock
8
WHERE noArticle = unNoArticle;
9 END pModifierQuantitéEnStock;
10 /
Procedure created.
SQL> grant execute on pModifierQuantitéEnStock to public;
Grant succeeded.
SQL> connect test/oracle
Connected.
SQL> EXECUTE godin.pModifierQuantitéEnStock (10,700);
PL/SQL procedure successfully completed.
SQL> select * from article;
NOARTICLE
---------10
20
…
20/07/2015
DESCRIPTION
PRIXUNITAIRE QUANTITÉENSTOCK
-------------------- ------------ --------------Cèdre en boule
10,99
500
Sapin
12,99
10
© Robert Godin. Tous droits réservés.
32
Exemple privilège du créateur
(suite)
SQL> select * from godin.article;
select * from godin.article
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect godin/oracle
Connected.
SQL> select * from article;
NOARTICLE
---------10
20
40
50
60
70
80
81
90
95
DESCRIPTION
PRIXUNITAIRE QUANTITÉENSTOCK
-------------------- ------------ --------------Cèdre en boule
10,99
700
Sapin
12,99
10
Épinette bleue
25,99
10
Chêne
22,99
10
Érable argenté
15,99
10
Herbe à puce
10,99
10
Poirier
26,99
10
Catalpa
25,99
10
Pommier
25,99
10
Génévrier
15,99
10
10 rows selected.
20/07/2015
© Robert Godin. Tous droits réservés.
33
5.2.10 Package PL/SQL
CREATE PACKAGE nomPaquetage AS
listeDesSignaturesDesFonctions&Procédures
END nomPaquetage ;
CREATE PACKAGE BODY nomPaquetage AS
délaration [déclaration]…
BEGIN
séquenceÉnoncésInitialisation
END nomPaquetage;
nomPaquetage.nomObjet
20/07/2015
© Robert Godin. Tous droits réservés.
34
5.2.11 Déboguage du code PL/SQL



SHOW ERRORS sous SQL*plus
Package DBMS_OUTPUT
Table USER_SOURCE dans la métabase
SQL>
2
3
4
SELECT text
FROM
USER_SOURCE
WHERE name = 'FQUANTITÉENSTOCK' AND type = 'FUNCTION'
ORDER BY line;
TEXT
------------------------------------------------------------------------------FUNCTION fQuantitéEnStock
(unNoArticle Article.noArticle%TYPE)
RETURN Article. quantitéEnStock%TYPE IS
uneQuantitéEnStock Article.quantitéEnStock%TYPE;
BEGIN
SELECT quantitéEnStock
INTO
uneQuantitéEnStock
FROM
Article
WHERE
noArticle = unNoArticle;
RETURN uneQuantitéEnStock;
TEXT
------------------------------------------------------------------------------END fQuantitéEnStock;
13 rows selected.
20/07/2015
© Robert Godin. Tous droits réservés.
35
5.3 JDBC
API standard pour JAVA
 http://java.sun.com/products/jdbc/
 Ensemble de classes
 Besoin d ’installer un pilote JDBC dans
l ’environnement JAVA

20/07/2015
© Robert Godin. Tous droits réservés.
36
5.3.1 Architecture pour
les pilotes JDBC
Application Java
(import java.sql.*)
API JDBC
Type 1
Passerelle JDBCODBC
Type 2
Partie Java
Pilote ODBC
(e.g. pilote ODBC
Oracle)
API client du
SGBD
(e.g. ocijdbc8.dll
pour Oracle)
Type 3
Pilote JDBC
générique
Type 4
Pilote JDBC tout
Java
(e.g. Oracle thin)
Type ?
Oracle serverside thin et
internal driver
Serveur
JDBC
Serveur de BD
20/07/2015
© Robert Godin. Tous droits réservés.
37
5.3.2 Chargement d'un pilote JDBC
(DriverManager) et établissement
d'une connexion (Connection)

Charger les pilotes JDBC d ’ Oracle
Class.forName ("oracle.jdbc.driver.OracleDriver");
–
N.B. La librairie des pilotes Oracle doit être accessible à la machine virtuelle Java. Voir
–
Avec JDK 1.1 (problème avec bloc statique) utiliser :
http://www.info.uqam.ca/~godin/livres.html


DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Établir une connexion avec le pilote OCI8 pour un serveur Oracle local
–
OCI8 est un pilote de type 2
Connection uneConnection =
DriverManager.getConnection
("jdbc:oracle:oci8:@", "toto", "secret");

Voir aussi interface javax.sql.DataSource (chap. 15)
–
–
–
–
20/07/2015
package optionnel de JDBC 2
meilleure transparence, plus grande versatilité
maintenant préférée à DriverManager
obligatoire avec J2EE
© Robert Godin. Tous droits réservés.
38
5.3.3 Création d ’un
énoncé SQL (Statement)
Statement unEnoncéSQL = uneConnection.createStatement ();
<<Interface>>
Statement
executeQuery(arg0 : String) : ResultSet
executeUpdate(arg0 : St ring) : int
close() : void
getMaxFieldSize() : int
setMaxFieldSize(arg0 : int) : void
getMaxRows() : int
setMaxRows(arg0 : int) : void
setEscapeProcessing(arg0 : boolean) : void
getQueryTimeout() : int
setQueryTimeout(arg0 : int) : void
cancel() : void
getW arnings() : SQLWarning
clearWarnings() : void
setCursorName(arg0 : String) : void
execute(arg0 : String) : boolean
getResultSet() : ResultSet
getUpdat eCount() : int
getMoreResults() : boolean
setFet chDirection(arg0 : int) : void
getFet chDirection() : int
setFet chSize(arg0 : int) : void
getFet chSize() : int
getResultSetConcurrency() : int
getResultSetType() : int
addBatch(arg0 : String) : void
clearBatc h() : void
executeBatch() : int[]
getConnection() : Connection
20/07/2015
© Robert Godin. Tous droits réservés.
39
5.3.4 Exécution d'une opération de mise à
jour (INSERT, DELETE, UPDATE)
import java.sql.*;
class ClientInsertJDBC
{
public static void main (String args [])
throws SQLException, ClassNotFoundException, java.io.IOException
{
// Charger le pilote JDBC d'Oracle
Class.forName ("oracle.jdbc.driver.OracleDriver");
// Connexion à une BD
Connection uneConnection =
DriverManager.getConnection ("jdbc:oracle:oci8:@", "godin", "oracle");
// Création d'un énoncé associé à la Connection
Statement unEnoncéSQL = uneConnection.createStatement ();
// Insertion d'une ligne dans la table Client
int n = unEnoncéSQL.executeUpdate
("INSERT INTO CLIENT " +
"VALUES (100, 'G. Lemoyne-Allaire', '911')");
System.out.println ("Nombre de lignes inserees:" + n);
// Fermeture de l'énoncé et de la connexion
unEnoncéSQL.close();
uneConnection.close();
}
}
20/07/2015
© Robert Godin. Tous droits réservés.
40
5.3.6 Exécution d'un
SELECT (ResultSet)
… Début analogue à l'exemple précédent
// Création d'un énoncé associé à la Connexion
Statement unEnoncéSQL = uneConnection.createStatement();
// Exécution d'un SELECT
ResultSet résultatSelect = unEnoncéSQL.executeQuery
("SELECT noClient, nomClient "+
"FROM CLIENT " +
"WHERE noClient > 40");
// Itérer sur les lignes du résultat du SELECT et extraire les valeurs
// des colonnes dans des variables JAVA
while (résultatSelect.next ()){
int noClient = résultatSelect.getInt ("noClient");
String nomClient = résultatSelect.getString ("nomClient");
System.out.println ("Numéro du client:" + noClient);
System.out.println ("Nom du client:" + nomClient);
}
}
}
20/07/2015
© Robert Godin. Tous droits réservés.
41
5.3.7 ResultSet défilable (scrollable),
modifiable (updatable), sensible
(sensitive) - JDBC2
// Création d'un énoncé avec ResultSet défilable (srollable)
Statement unEnoncéSQL =
uneConnection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
// Exécution d'un SELECT
ResultSet résultatSelect = unEnoncéSQL.executeQuery
("SELECT noClient, nomClient FROM CLIENT");
//Positionnement à la première ligne du ResultSet
résultatSelect.first();
// Positionnement à la dernière ligne du ResultSet
résultatSelect.last();
// Positionnement à la troisième ligne
résultatSelect.absolute(3);
// Positionnement relatif (avancer de 2 lignes)
résultatSelect.relative(2);
// Positionnement à la ligne précédente
résultatSelect.previous();
20/07/2015
© Robert Godin. Tous droits réservés.
42
ResultSet modifiable
(updatable)
// Création d'un énoncé avec ResultSet défilable (srollable)
Statement unEnoncéSQL =
uneConnection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
// Exécution d'un SELECT
ResultSet résultatSelect = unEnoncéSQL.executeQuery
("SELECT noClient, nomClient, noTéléphone FROM CLIENT");
// Exemples de mises-à-jour par ResultSet updatable
mises à jour susceptibles
’être vues pas le ResultSet
//Positionnement à la première ligne du ResultSet
d
résultatSelect.first();
// Mise-à-jour de la colonne noTéléphone de la ligne courante
résultatSelect.updateString("noTéléphone","(111)111-1111");
// Effectuer le UPDATE
résultatSelect.updateRow();
// Positionnement à la dernière ligne de la table
résultatSelect.last();
// Supprimer la ligne courante
résultatSelect.deleteRow();
// Insertion d'une nouvelle ligne
résultatSelect.moveToInsertRow();
résultatSelect.updateInt("noClient", 100);
résultatSelect.updateString("nomClient", "G. Lemoyne-Allaire");
résultatSelect.updateString("noTéléphone", "911");
résultatSelect.insertRow();
20/07/2015
© Robert Godin. Tous droits réservés.
43
5.3.8 Support des types
SQL:1999 sous JDBC 2
CREATE TABLE tableBlob (
idBlob
INTEGER PRIMARY KEY,
imageBLOB)
// Chercher le BLOB locator
ResultSet unResultSet = unEnoncéSQL.executeQuery ("SELECT *
if (unResultSet.next()){
FROM tableBlob WHERE idBlob = 1");
int idBlob = unResultSet.getInt(1);
Blob unBlob = unResultSet.getBlob(2);
// Chercher la taille du BLOB et l'afficher
int taille = (int)unBlob.length();
System.out.println("Taille du BLOB" + taille);
// Lire le BLOB dans un tableau d'octets
byte octets[] = unBlob.getBytes(1, taille);
// Créer un fichier contenant les octets lus
FileOutputStream unFichier =
new FileOutputStream("C:/forte4j/Development/ExemplesJDBC/CopieCoq1.gif");
unFichier.write(octets);
unFichier.close();
20/07/2015
© Robert Godin. Tous droits réservés.
44
5.3.9 Exécution en lot
(batch) sous JDBC 2
// Création d'un PreparedStatement associé à la Connection
PreparedStatement unEnoncéSQL = uneConnection.prepareStatement
("INSERT INTO Client VALUES(?,?,?)");
// Ajout d'un INSERT dans le lot
unEnoncéSQL.setInt(1,90);
unEnoncéSQL.setString(2,"Edgar Degas");
unEnoncéSQL.setString(3,"(222)222-2222");
unEnoncéSQL.addBatch();
// Ajout d'un autre INSERT dans le lot
unEnoncéSQL.setInt(1,100);
unEnoncéSQL.setString(2,"Claude Monet");
unEnoncéSQL.setString(3,"(111)111-1111");
unEnoncéSQL.addBatch();
// Exécution du lot en un appel
int [] résultats = unEnoncéSQL.executeBatch();
20/07/2015
© Robert Godin. Tous droits réservés.
45
5.3.10
Gestion des
transactions
Par défaut : auto-commit
 Pour modifier

uneConnection.setAutoCommit(false);

Pour un commit explicite :
uneConnection.commit();

Pour un rollback :
uneConnection.rollback();
20/07/2015
© Robert Godin. Tous droits réservés.
46
Suite

Changer le niveau d ’isolation de défaut
uneConnection.setTransactionIsolationLevel(unNiveau) ;

Transactions réparties JDBC 2
–
Java Transaction API (JTA)
 interface
–
–
20/07/2015
UserTransaction
begin(), commit()
voir chap. 15
© Robert Godin. Tous droits réservés.
47
5.3.11
Gestion des
exceptions

Mécanisme try/catch de JAVA
try
{Class.forName ("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e)
{System.err.println(" ClassNotFoundException:" + e.getMessage());
}
20/07/2015
© Robert Godin. Tous droits réservés.
48
5.3.12 Utilisation de JDBC
dans une applette Java
Déploiement avec le pilote thin
 Contrainte de carré de sable
 Exemple

–
20/07/2015
http://www.labunix.uqam.ca/~r11340/ExemplesAppletJDBC/PageAppletJDBC.html
© Robert Godin. Tous droits réservés.
49
Exemple d ’applette avec pilote JDBC type 4 (thin)
import java.awt.*;
import java.applet.*;
import java.sql.*;
public class AppletJDBC extends Applet {
public void paint (Graphics g)
//N.B. On ne peut lancer (throw) des exceptions non déclarées dans le paint() de Applet...
//Pour simplifier l'exemple, il n'y a qu'un seul try pour tous les appels à JDBC
{
try{
// NB Charger le pilote JDBC d'Oracle avec DriverManager pour Java 1.1 sous Explorer 5
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
// Connection à une BD à distance avec un pilote thin
Connection uneConnection =
DriverManager.getConnection ("jdbc:oracle:thin:@arabica.cafe.uqam.ca:1521:o8db", "r11340", "motDePasse");
// Création d'un énoncé associé à la Connection
Statement unEnoncéSQL = uneConnection.createStatement ();
// Exécution d'un SELECT
ResultSet résultatSelect = unEnoncéSQL.executeQuery
("SELECT noClient, nomClient "+
"FROM CLIENT " +
"WHERE noClient > 40");
// Itérer sur les lignes du résultat du SELECT et extraire les valeurs
// des colonnes dans des variables JAVA
g.drawString("Résultat du SELECT :", 5,15);
g.drawString("Numéro", 5,30); g.drawString("Nom", 100,30);
int i = 1;
while (résultatSelect.next ()){
int noClient = résultatSelect.getInt ("noClient");
String nomClient = résultatSelect.getString ("nomClient");
g.drawString(""+noClient,5,i*15+30);
g.drawString(""+nomClient,100,i*15+30);
i = i+1;
}
// Fermeture de l'énoncé et de la connexion
unEnoncéSQL.close();
uneConnection.close();
}
catch(Exception e){System.err.println(" Exception:" + e.getMessage());}
5.3.13
Compilation et exécutions
multiples avec la classe PreparedStatement

Compilation + exécution combinée au executeQuery
Statement unEnoncéSQL = uneConnection.createStatement();
ResultSet résultatSelect = unEnoncéSQL.executeQuery
("SELECT noClient, nomClient "+
"FROM CLIENT " +
"WHERE noClient > 40");

Compilation au prepareStatement
PreparedStatement unEnoncéSQL = uneConnection.prepareStatement
("SELECT noClient, nomClient "+
"FROM CLIENT " +
"WHERE noClient > 40");
ResultSet résultatSelect = unEnoncéSQL.executeQuery();

Statement
Compilation avec paramètres
PreparedStatement unEnoncéSQL = uneConnection.prepareStatement
("SELECT noClient, nomClient "+
"FROM CLIENT " +
"WHERE noClient > ?");
unEnoncéSQL.setInt(1,40);
ResultSet résultatSelect = unEnoncéSQL.executeQuery();
20/07/2015
© Robert Godin. Tous droits réservés.
PreparedStatement
CallableStatement
51
5.3.14 Exécution d'une procédure ou
fonction stockée (CallableStatement)
// Création d'un appel de fonction associé à la Connection
CallableStatement unCall =
uneConnection.prepareCall("{ ? = call fQuantitéEnStock(?)}");
// Spécification du paramètre d'entrée
unCall.setInt(2,10);
// Inscription de la sortie
unCall.registerOutParameter(1, java.sql.Types.INTEGER);
// Exécution de l'appel
unCall.execute();
// Récupération de la sortie
int laQuantite = unCall.getInt(1);
System.out.println("Quantité en stock :"+laQuantite);
};
unCall.close();
uneConnection.close();
20/07/2015
© Robert Godin. Tous droits réservés.
52
Appel de procédure stockée
CallableStatement unCall =
uneConnection.prepareCall("{call pModifierQuantitéEnStock(?,?)}");
// Spécification des paramètres d'entrée
unCall.setInt(1,10);
unCall.setInt(2,20);
// Exécution de l'appel
unCall.execute();
unCall.close();
uneConnection.close();
20/07/2015
© Robert Godin. Tous droits réservés.
53
5.3.15 Accès aux métadonnées (MetaData)
ResultSet résultatSelect = unEnoncéSQL.executeQuery
("SELECT noClient, nomClient "+
"FROM CLIENT " +
"WHERE noClient > 40");
// Consultation de quelques méta-données du ResultSetMetaData
ResultSetMetaData unRSMD = résultatSelect.getMetaData();
int nombreColonnes = unRSMD.getColumnCount();
System.out.println("Le résultat du SELECT contient "+nombreColonnes+" colonnes");
for (int indice = 1; indice <= nombreColonnes; indice++){
System.out.println("La colonne "+indice+
" qui se nomme "+unRSMD.getColumnName(indice)+
" est de type "+unRSMD.getColumnTypeName(indice));
Le résultat du SELECT contient 2 colonnes
La colonne 1 qui se nomme NOCLIENT est de type NUMBER
La colonne 2 qui se nomme NOMCLIENT est de type VARCHAR2
20/07/2015
© Robert Godin. Tous droits réservés.
54
DatabaseMetaData
// Consultation de quelques méta-données de la BD
DatabaseMetaData unDBM = uneConnection.getMetaData();
System.out.println("Nom du SGBD :"+unDBM.getDatabaseProductName());
System.out.println("Version du SGBD :"+unDBM.getDatabaseProductVersion());
System.out.println("Niveau d'isolation par défaut :"+unDBM.getDefaultTransactionIsolation());
System.out.println("Support du niveau entrée de SQL2 :"+unDBM.supportsANSI92EntryLevelSQL());
System.out.println("Nom du pilote JDBC :"+unDBM.getDriverName());
Nom du SGBD :Oracle
Version du SGBD :Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Niveau d'isolation par défaut :2
Support du niveau entrée de SQL2 :true
Nom du pilote JDBC :Oracle JDBC driver
20/07/2015
© Robert Godin. Tous droits réservés.
55
5.4 SQLJ

Partie 0 de SQLJ
–

Partie 1 de SQLJ
–

SQL enchâssée en Java
Routines stockées
Partie 2 de SQLJ
–
20/07/2015
Utilisation de classes Java en tant que types SQL
© Robert Godin. Tous droits réservés.
56
5.4.1 SQL enchâssé en
Java (SQLJ : partie 0)
Ne vise que le statique
 Traduit en JDBC
 Peut combiner SQLJ et JDBC

#sql "{" énoncéSQL "}" ;
#sql {INSERT INTO CLIENT VALUES (100, 'G. Lemoyne-Allaire', '911')};
#sql {DELETE FROM CLIENT WHERE noClient = 10};
20/07/2015
© Robert Godin. Tous droits réservés.
57
Variables partagées
Int no;
String nom;
String tel;
#sql {INSERT INTO CLIENT VALUES (:no, :nom, :tel)};
20/07/2015
© Robert Godin. Tous droits réservés.
58
5.4.1.1 CONTEXTE DE
CONNEXION
//Exemple d'insertion d'un Client avec SQLJ
package ExemplesSQLJ;
Contexte implicite
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
public class ClientInsertSQLJ{
public static void main (String args [])
throws SQLException, ClassNotFoundException, java.io.IOException
{
// Charger le pilote JDBC d'Oracle
Class.forName ("oracle.jdbc.driver.OracleDriver");
// Création du contexte de connexion de défaut avec autocommit (true)
DefaultContext unContexte = new DefaultContext
("jdbc:oracle:thin:@localhost:1521:ora817i", "godin", "oracle", true);
DefaultContext.setDefaultContext(unContexte);
// Insertion en utilisant le contexte de défaut
#sql {INSERT INTO CLIENT VALUES (100, 'G. Lemoyne-Allaire', '911')};
// Fermeture du contexte de connexion
unContexte.close();
System.out.println("Insertion réussie !");
}
}
20/07/2015
© Robert Godin. Tous droits réservés.
59
Contexte explicite
// Déclaration de la classe du contexte explicite
#sql context Contexte;
public class ContexteExpliciteSQLJ{
public static void main (String args [])
throws SQLException, ClassNotFoundException, java.io.IOException
{
// Charger le pilote JDBC d'Oracle
Class.forName ("oracle.jdbc.driver.OracleDriver");
// Création du contexte de connexion avec autocommit (true)
Contexte unContexte = new Contexte
("jdbc:oracle:thin:@localhost:1521:ora817i", "godin", "oracle", true);
// Insertion en utilisant le contexte explicite unContexte
#sql [unContexte] {INSERT INTO CLIENT VALUES (100, 'G. Lemoyne-Allaire', '911')};
20/07/2015
© Robert Godin. Tous droits réservés.
60
Commit explicite
// Création du contexte de connexion de défaut sans autocommit (false)
DefaultContext unContexte = new DefaultContext
("jdbc:oracle:thin:@localhost:1521:ora817i", "godin", "oracle", false);
DefaultContext.setDefaultContext(unContexte);
// Insertion en utilisant le contexte de défaut
#sql {INSERT INTO CLIENT VALUES (100, 'G. Lemoyne-Allaire', '911')};
// Confirmer la transaction
#sql {COMMIT};
20/07/2015
© Robert Godin. Tous droits réservés.
61
5.4.1.3 SELECT QUI RETOURNE
UN SINGLETON (CLAUSE INTO)
String nom;
String tel;
// Utilisation de la clause INTO
#sql
{ SELECT nomClient, noTéléphone
INTO :nom, :tel
FROM Client WHERE noClient = 10};
20/07/2015
© Robert Godin. Tous droits réservés.
62
5.4.1.4 ITÉRATEUR DE
RÉSULTAT SQLJ
// Définition de la classe IteratorClient avec liaison par nom
#sql iterator IteratorClient(int noClient, String nomClient);
// Création d'un objet itérateur
IteratorClient unIteratorClient;
// Liaison de l'énoncé SELECT de l'itérateur
#sql
unIteratorClient =
{ SELECT noClient, nomClient
FROM Client WHERE noClient > 40};
// Accès au résultat du SELECT par itération sur les lignes
while (unIteratorClient.next()){
System.out.println("Numéro du client : " + unIteratorClient.noClient());
System.out.println("Nom du client : " + unIteratorClient.nomClient());
}
// Fermer l'itérateur
unIteratorClient.close();
20/07/2015
© Robert Godin. Tous droits réservés.
63
Mise-à-jour par itérateur
// Définition de la classe IteratorClient avec liaison par nom
#sql iterator IteratorClient implements sqlj.runtime.ForUpdate
(int noClient, String nomClient);
// Création d'un objet itérateur
IteratorClient unIteratorClient;
// Liaison de l'énoncé SELECT de l'itérateur
#sql
unIteratorClient =
{ SELECT noClient, nomClient, noTéléphone
FROM Client WHERE noClient > 40};
// Accès au résultat du SELECT par itération sur les lignes
while (unIteratorClient.next()){
if (unIteratorClient.noClient()== 60){
#sql {UPDATE Client
SET noTéléphone = '(111)111-1111'
WHERE CURRENT of :unIteratorClient};
} else if (unIteratorClient.noClient()== 80){
#sql {DELETE FROM Client
WHERE CURRENT of :unIteratorClient};
}
20/07/2015
© Robert Godin. Tous droits réservés.
64
5.4.1.5 APPEL DE ROUTINES
STOCKÉES EN SQLJ
int laQuantite = 0;
int noArticle = 10 ;
// Appel de la fonction stockée
#sql laQuantite = {VALUES (fQuantitéEnStock(:noArticle))};
// Fermeture du contexte de connexion
unContexte.close();
System.out.println("Quantité en stock :" + laQuantite);
}
// Appel de la procédure stockée
#sql {CALL pModifierQuantitéEnStock(:in noArticle,:in laQuantite)};
20/07/2015
© Robert Godin. Tous droits réservés.
65
5.4.1.6 INTEROPÉRABILITÉ
AVEC JDBC

Création d'un contexte SQLJ à partir d'une Connection JDBC
Connection uneConnection =
DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:ora817i", "godin", "oracle");
Contexte unContexte = new Contexte(uneConnection);

Extraction de la Connection d'un contexte
Connection uneConnection = unContexte.getConnection();

Conversion d'un itérateur en un ResultSet JDBC
ResultSet résultatSelect = unIteratorClient.getResultSet();

Conversion d'un ResultSet JDBC en un iterateur SQLJ
#sql unIteratorClient = {CAST :unResultSet};
20/07/2015
© Robert Godin. Tous droits réservés.
66
5.4.1.7 PROCESSUS DE
TRADUCTION SQLJ
NomClasse.SQLJ
Traducteur
SQLJ (partie 0)
Schéma SQL
NomClasse.Java
Compilateur Java
Classes de l'
environnement
d'exécution SQLJ
NomClasse.class
NomClasse_SJProfile0.ser
NomClasse_SJProfile1.ser
...
Fichier de déploiement
(.jar)
Adaptation spécifique à un
SGBD
20/07/2015
© Robert Godin. Tous droits réservés.
67
5.4.2 SQLJ partie 1:
routines stokées en Java
import java.sql.*;
import java.io.*;
public class RoutineServeur extends Object {
public static int getQuantiteEnStock (int noArticle) throws SQLException
{
// Retourne -1 si l'article n'existe pas
PreparedStatement unEnoncéSQL = null;
int quantitéEnStock = -1;
try {
Connection uneConnection =
DriverManager.getConnection("jdbc:default:connection:");
unEnoncéSQL = uneConnection.prepareStatement
("SELECT quantitéEnStock FROM Article WHERE noArticle = ? ");
unEnoncéSQL.setInt(1,noArticle);
ResultSet résultatSelect = unEnoncéSQL.executeQuery();
if (résultatSelect.next ()){
quantitéEnStock = résultatSelect.getInt(1);
}
}
catch (SQLException e) {System.err.println(e.getMessage());}
finally{unEnoncéSQL.close();}
return quantitéEnStock;
}
20/07/2015
public
static
void
setQuantiteEnStock
(int
noArticle,
int
quantitéEnStock) throws SQLException {
PreparedStatement unEnoncéSQL = null;
try {
Connection uneConnection =
DriverManager.getConnection("jdbc:default:connection:");
unEnoncéSQL = uneConnection.prepareStatement
("UPDATE Article SET quantitéEnStock = ? WHERE noArticle = ? ");
unEnoncéSQL.setInt(1,quantitéEnStock);
unEnoncéSQL.setInt(2,noArticle);
unEnoncéSQL.executeUpdate();
}
catch (SQLException e) {System.err.println(e.getMessage());}
finally{unEnoncéSQL.close();}
© Robert Godin. Tous droits réservés.
}
}
68
Déploiement Oracle

Charger le code dans un schéma
loadjava -user godin/oracle RoutineServeur.class

Publier sous forme de routine stockée :
SQL>
2
3
4
5
CREATE OR REPLACE FUNCTION getQuantiteEnStock(noArticle NUMBER)
RETURN NUMBER
AS LANGUAGE JAVA
NAME ' RoutineServeur.getQuantiteEnStock (int) return int';
/
Function created.

Appeler la fonction en SQL
SQL> select getQuantiteEnStock(10) from dual;
GETQUANTITEENSTOCK(10)
---------------------20
20/07/2015
© Robert Godin. Tous droits réservés.
69