JDBC

Développement des Applications des
Bases de Données
Chapitre 6
1
Survol
SQL dans les codes d’application
 SQL imbriqué
 Curseurs
 SQL dynamique
 JDBC
 SQLJ
 Procédures stockées

2
SQL dans les codes d’Application

Les commandes SQL peuvent être appelées à partir
d’un programme d’application («langage hôte» -C++, Java, etc).



Les instructions SQL peuvent référer à des variables du
langage hôte (y compris des variables utilisées pour
retourner les statuts).
On doit inclure une instruction pour connecter à la base de
données appropriée.
Deux approches d’intégration existent:
 Instructions SQL imbriquées dans le langage hôte
(« Embedded SQL », SQLJ)
 Création d’une API spéciale pour appeler les commandes
SQL (JDBC)
3
SQL dans les codes d’Application (Suite)



Les relations SQL sont des (multi)ensembles de tuples
qui n’imposent aucune limite a priori sur le nombre de
tuples. Les langages de programmation traditionnels
(C, C++, etc.) n’ont pas une telle structure de données.
Ce problème est appelé inadaptation d’impédance
(« impedance mismatch ») .
SQL supporte un mécanisme appelé curseur pour
résoudre ce problème.
Un curseur est un élément additionnel de SQL qui
comble le fossé causé par l’inadaptation d’impédance.
4
SQL Imbriqué: Variables
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20];
long c_sid;
short c_rating;
float c_age;
EXEC SQL END DECLARE SECTION

Deux problèmes avec les variables:
 correspondance des types (solution: casting/correspondance
explicite)
 inadaptation d’impédance (solution: le mécanisme de curseur)

Deux variables spéciales d’“erreur” (une au moins doit être
déclarée):
 SQLCODE (long, est négative si une erreur est apparue)
 SQLSTATE (char[6], codes prédéfinies pour des erreurs
usuelles)
5
SQL Imbriqué: Instructions

Approche: instructions SQL imbriquées dans le
langage hôte.
 Un préprocesseur convertit les instructions SQL en
des appels API.
 Ensuite un compilateur régulier prend le relais pour
compiler le code.

Éléments du langage:
 Pour connecter à une base de données:
EXEC SQL CONNECT
 Pour exécuter des instructions SQL:
EXEC SQL Statement;
6
SQL Imbriqué: Instructions (Suite)

Exemple:
 Insérer un tuple formé avec des valeurs issues des
variables du langage hôte:
EXEC SQL
INSERT INTO Sailors
VALUES (:c_sname, :c_sid, :c_rating, :c_age);
 Commande spéciale pour contrôler les erreurs après des
instructions SQL:
EXEC SQL
WHENEVER [SQLERROR|NOT FOUND]
[CONTINUE|GOTO smt]
7
Curseurs: Intuition


On peut déclarer un curseur sur une relation ou une
instruction de requête (qui en fait génère une relation).
On peut ouvrir un curseur déclaré, et l’utiliser pour puiser
(fetch) de manière répétée des tuples et, après, déplacer le
curseur, jusqu’à ce que tous les tuples aient été récupérés.




On peut utiliser une clause spéciale, appelée ORDER BY, dans les
requêtes accessibles aux curseurs afin de contrôler l’ordre dans le
quel les tuples doivent être récupérés.
Les attributs mentionnés dans la clause ORDER BY doivent aussi
l’être dans la clause SELECT.
On peut enfin clore un curseur ouvert.
On peut aussi modifier/effacer des lignes en utilisant un
curseur. Cependant, INSERT, DELETE, et UPDATE peuvent
être utilisés sans curseur.
8
Exemple d’Utilisation de Curseur
EXEC SQL DECLARE sinfo CURSOR FOR
SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’
ORDER BY S.sname
sinfo est le curseur.
 Il est illégal de remplacer S.sname par S.sid dans la
clause ORDER BY !
 On peut ajouter S.sid à la clause SELECT et
remplacer S.sname par S.sid dans la clause ORDER BY.

9
Curseur: Syntaxe

Syntaxe générale pour déclarer les curseurs:
DECLARE cursorname
[INSENSITIVE] [SCROLL]
CURSOR [WITH HOLD] FOR query
[ORDER BY order-item-list]
[FOR READ ONLY | FOR UPDATE]

INSENSITIVE: une copie privée du curseur est faite.
SCROLL: permet plus de flexibilité pour l’opération FETCH.
WITH HOLD: curseur n’est pas fermé quand la transaction est validée.

Variante de la commande UPDATE pour les curseurs:
UPDATE Sailors
SET
S.age = S.age+10
WHERE CURRENT OF sinfo
/* Modifie la valeur courante du curseur sinfo */
10
Imbriquer SQL dans C: Exemple
char SQLSTATE[6];
EXEC SQL BEGIN DECLARE SECTION
char c_sname[20]; short c_minrating; float c_age;
EXEC SQL END DECLARE SECTION
c_minrating = random();
EXEC SQL DECLARE sinfo CURSOR FOR
SELECT S.sname, S.age
FROM Sailors S
WHERE S.rating > :c_minrating
ORDER BY S.sname;
EXEC SQL OPEN sinfo;
do {
EXEC SQL FETCH sinfo INTO :c_sname, :c_age;
printf(“%s is %d years old\n”, c_sname, c_age);
} while (SQLSTATE != ‘02000’);
EXEC SQL CLOSE sinfo;
11
SQL Dynamique




Les instructions SQL imbriquées sont connues par le
compilateur à l’avance («compile time»).
Il y a des situations exigeant l’exécution d’instructions SQL
en passant (« on-the-fly » /«run time»)!
Deux commandes principales à ce sujet:
PREPARE sql_cmd FROM sql_string
EXECUTE sql_cmd
Exemple:
char c_sqlstring[]=
{“DELETE FROM Sailors WHERE raiting>5”};
EXEC SQL PREPARE deleteCommand FROM :c_sqlstring;
EXEC SQL EXECUTE deleteCommand;
12
APIs en Lieu et Place de l’Imbrication





Au lieu de modifier le compilateur, ajouter des
bibliothèques avec des instructions d’appel des bases de
données (API).
Interface spéciale standard: procédures/objets
Passer les instructions SQL (en forme de chaînes de
caractères) à partir du langage hôte, et présenter les
ensembles de résultats dans une manière compréhensible
pour le langage hôte.
Exemples: ODBC; JDBC (Sun Corporation: API Java)
JDBC est supposé être indépendant de tout SGBD


Un pilote (“driver”) reçoit les instructions d’appel et les traduit
dans le code spécifique du SGBD.
La base de données peut se trouver à un autre bout du réseau.
13
JDBC: Architecture

Quatre composantes architecturales:
 Application (initie et termine des connexions, soumet
des instructions SQL)
 Gestionnaire des pilotes (‘’Driver manager’’) (choisit
--‘’loads’’-- un driver JDBC)
 Driver (connecte à la source des données, transmet
des requêtes et retourne/traduit les résultats et les
codes d’erreur)
 Source des données (exécute les instructions SQL)
14
JDBC: Types de Drivers
Pont (’’Bridge’’):
 Traduit les commandes SQL en une API non propre.
Exemple: le pont JDBC-ODBC. Le code pour les drivers ODBC et
JDBC doivent être présents sur chaque client.
Traduction directe en une API propre via un driver hors Java:
 Traduit les commandes SQL en une API propre de la source des
données. Besoin d’un code binaire spécifique à chaque système
d’opération sur chaque client.
Pont sur réseau (‘’network bridge’’):
 Les commandes sont envoyées via le réseau à un module
intermédiaire (‘’middleware’’) qui est un serveur parlant à la source
des données. Seul besoin: un driver JDBC sur chaque client.
Traduction directe en une API propre via un driver Java:
 Convertit les instructions d’appel JDBC directement dans le
protocole de réseau que le SGBD utilise. Besoin d’un driver Java
spécifique à chaque SGBD sur chaque client.
15
Classes et Interface pour JDBC
Étapes pour soumettre une requête:
1.
2.
3.
Choisir le driver JDBC (via un gestionnaire)
Connecter à la source de données
Exécuter les instructions SQL
16
Gestionnaire des Pilotes JDBC
Tous les drivers sont gérés par la classe
DriverManager
 Choix d’un driver JDBC (3 voies possibles dont):

 Dans le code Java:
Class.forName(“oracle/jdbc.driver.Oracledriver”);
Retourne l’objet de la classe driver au vu de son nom
complet.
 En démarrant une application Java (à la ligne des
commandes):
-Djdbc.drivers=oracle/jdbc.driver
 Instanciation explicite d’un driver
17
Connexions JDBC
L’interaction avec une source de données se fait par
l’entremise d’une session qui est démarrée par la création
d’un objet de type ‘’Connection’’. Chaque session est
démarrée via un URL JDBC.
 URL JDBC(i.e. un URL utilisant le protocole JDBC):
jdbc:<sousprotocole>:<autresParametres>
Exemple: Etablir une connexion à une BD Oracle précise:
String url=“jdbc:oracle:www.bookstore.com:3083”;
connexion con;
try{
con = DriverManager.getconnexion(url,usedId,password);
} catch SQLException excpt { …}
18
Interface pour les Connexions




public int getTransactionIsolation() et
void setTransactionIsolation(int level)
Détermine le degré d’isolation de la connexion en cours.
public Boolean getReadOnly() et
void setReadOnly(boolean b)
Spécifie si les transactions dans la connexion sont pour
lecture seulement (‘’read-only’’).
public boolean getAutoCommit() et
void setAutoCommit(boolean b)
Si autocommit est vrai, chaque instruction SQL est
considérée comme une transaction autonome. La
transaction est validée par commit(), ou abandonnée par
rollback().
public boolean isClosed()
Vérifie si la connexion est encore ouverte.
19
Exécution des Instructions SQL

Trois manières différentes de le faire (i.e. en
utilisant 3 classes appropriées pour des objets de
type ‘’statement’’):
 Statement (permet des requêtes avec soit des instructions
SQL statiques soit dynamiques)
 PreparedStatement (instructions semi statiques)
 CallableStatement (procédures stockées)

La classe PreparedStatement:
génère dynamiquement des instructions SQL
précompilées et paramétrisées:
 Structure fixe
 Valeurs des paramètres déterminées pendant l’exécution
20
Exécution des Instructions SQL (Suite)
String sql=“INSERT INTO Sailors VALUES(?,?,?,?)”;
PreparedStatment pstmt=con.prepareStatement(sql);
pstmt.clearParameters();
pstmt.setInt(1,sid); // Supposons que sid, sname, …
pstmt.setString(2,sname); // sont des vars Java avec
pstmt.setInt(3, rating);
// des valeurs à insérer.
pstmt.setFloat(4,age);
// Savons que aucune ligne n’est retournées,
// d’où nous utilisons executeUpdate()
int numRows = pstmt.executeUpdate();
21
Ensembles des Resultats


PreparedStatement.executeUpdate retourne
seulement le nombre de ligne affectées.
PreparedStatement.executeQuery retourne les
donnees, repris dans un objet ResultSet (un curseur)
ResultSet rs=pstmt.executeQuery(sql);
// rs est un curseur; next() puise la prochaine ligne
While (rs.next()) {
// traiter les données
}
22
Ensemble des Resultats (Suite)
Un ResultSet est un curseur très puissant:
 previous(): reculer d’une ligne
 absolute(int num): avancer à la ligne dont le
numéro d’ordre est indiqué
 relative (int num): avancer ou reculer selon la
valeur indiquée
 first() et last()
23
Correspondance entre les Types de
Données: Java vs SQL
SQL Type
BIT
Java class
Boolean
ResultSet get method
getBoolean()
CHAR
VARCHAR
DOUBLE
FLOAT
String
String
Double
Double
getString()
getString()
getDouble()
getDouble()
INTEGER
REAL
DATE
Integer
Double
java.sql.Date
getInt()
getFloat()
getDate()
TIME
java.sql.Time
getTime()
TIMESTAMP
java.sql.TimeStamp getTimestamp()
24
JDBC: Exceptions et Avertissements
java.sql émet des exceptions SQLException si
une erreur apparaît.
 SQLWarning est une sousclasse de
SQLException; (pas du tout émis mais leur
existence doit être explicitement testé si on
veut les voir)
 Méthodes dans la classe SQLException:
getMessage(), getSQLState(), getErrorCode(),
getNextException(), …

25
JDBC: Exceptions et Avertissements (Suite)
try {
stmt=con.createStatement();
warning=con.getWarnings();
while(warning != null) {
// traiter le SQLWarnings;
warning = warning.getNextWarning():
}
con.clearWarnings();
stmt.executeUpdate(queryString);
warning = con.getWarnings();
…
} //end try
catch( SQLException SQLe) {
// traiter l’exception
}
26