Java DataBase Connectivity) JDBC ( M. Belguidoum Université Mentouri de Constantine Département Informatique M. Belguidoum (UMC) Programmation réseau 1 / 52 Plan 1 Introduction 2 Le paquetage java.sql 3 Mise en ÷uvre de JDBC 4 Equivalence des types java-SQL 5 Fonctionnalités supplémentaires de JDBC M. Belguidoum (UMC) Programmation réseau 2 / 52 Introduction Objectifs de JDBC Permettre aux programmeurs Java d'écrire un code indépendant de la base de données et du moyen de connexion utilisé API JDBC (Java DataBase Connectivity) 3.0 interface uniforme permettant un accès homogène aux SGBD simple à mettre en ÷uvre indépendant du SGBD support supportant les fonctionnalités de base du langage SQL M. Belguidoum (UMC) Programmation réseau 3 / 52 Introduction JDBC : introduction JDBC (Java Data Base Connectivity) est une API permettant l'accès à des bases de données relationnelles à partir du langage Java l'accès aux bases de données est réalisé en trois étapes : la création d'une connexion à la base. l'envoi d'instructions SQL. l'exploitation des résultats provenant de la base. JDBC est représentée par le paquetage java.sql (depuis la version 1.1 du JDK) M. Belguidoum (UMC) Programmation réseau 4 / 52 Introduction JDBC : les drivers chaque SGBD utilise un pilote (driver) qui lui est propre et qui permet de convertir les requêtes JDBC dans le langage natif du SGBD le driver est un ensemble de classes qui implantent les interfaces de JDBC les drivers dont le lien entre le programme Java et le SGBD ces drivers dits JDBC existent pour tous les principaux SGBD : Oracle, Sybase, Informix, DB2, MySQL,... M. Belguidoum (UMC) Programmation réseau 5 / 52 Introduction JDBC : introduction Les pilotes JDBC sont classés en quatre catégories : les pilotes de pont JDBC/ODBC : ce type de pilote, fourni avec le J2SE (Java 2 Standard Edition), permet de convertir les appels JDBC en appels ODBC (Open Database Connectivity). ODBC est fourni en standard sur les plates-formes Windows et permet la connexion à des bases de natures diverses (Access, SQL Server, Oracle, ...). les pilotes de type 2 : ils sont écrits, en partie, en Java, mais dépendent malgré tout de code natif. En termes de portage d'application Java, ces types de pilotes présentent quelques lacunes. les pilotes de type 3 : ce type de pilote passe par un autre pilote JDBC intermédiaire (fréquemment de type 1 ou 2). Il s'agit certainement du type de pilote le moins utile des quatre proposés. les pilotes de type 4 : leur code est écrit en Java, donc portable. Ces pilotes sont fournis par presque tous les constructeurs de bases de données. Chaque pilote étant adapté à une base de données particulière. Ces drivers constituent la meilleure solution pour le portage des applications Java. M. Belguidoum (UMC) Programmation réseau 6 / 52 Introduction JDBC : pilote de type 1 Pont JDBC-ODBC (Open Data Base Connectivity) M. Belguidoum (UMC) Programmation réseau 7 / 52 Introduction JDBC : pilote de type 2 API native M. Belguidoum (UMC) Programmation réseau 8 / 52 Introduction JDBC : pilote de type 3 M. Belguidoum (UMC) Programmation réseau 9 / 52 Introduction JDBC : pilote de type 4 Thin (protocole natif) M. Belguidoum (UMC) Programmation réseau 10 / 52 Introduction Les driver JDBC Liste des drivers disponibles à : http://developers.sun.com/product/jdbc/drivers sélection d'un driver choix entre vitesse, abilité et portabilité. Programme "standalone", avec une interface graphique qui s'exécute toujours sur un système Windows peut tirer bénéce de performances d'un driver type 2 (driver code-natif). Une applet peut nécessiter un driver de type 3 (pour passer un rewall). Une servlet déployée sur de multiples plateformes peut nécessiter la souplesse oerte par des drivers de type 4. ... M. Belguidoum (UMC) Programmation réseau 11 / 52 Le paquetage java.sql JDBC : le paquetage M. Belguidoum (UMC) java.sql Programmation réseau 12 / 52 Le paquetage java.sql JDBC : les interfaces : renvoie une instance de Connection Connection : connexion à une base Statement : instruction SQL PreparedStatement : instruction SQL paramétrée CallableStatement : procédure stockée dans la base ResultSet : n-uplets récupérés par une instruction SQL ResultSetMetaData : description des n-uplets récupérés DatabaseMetaData : informations sur la base de données Driver M. Belguidoum (UMC) Programmation réseau 13 / 52 Le paquetage java.sql JDBC : les classes DriverManager : gère les drivers, lance les connexions aux bases : date SQL Time : heures, minutes, secondes SQL TimeStamp : comme Time, avec une précision à la microseconde Types : constantes pour désigner les types SQL (conversions) Date M. Belguidoum (UMC) Programmation réseau 14 / 52 Le paquetage java.sql JDBC : les Exceptions : est levée dès qu'une connexion ou un ordre SQL ne se déroule pas correctement SQLException méthode getMessage() : pour obtenir le message en clair de l'erreur renvoie également des informations spéciques au SGBD :SQLState et le code d'erreur SGBD : avertissements SQL DataTruncation : lorsqu'une valeur est tronquée lors d'une conversion SGBD -Java SQLWarning M. Belguidoum (UMC) Programmation réseau 15 / 52 Le paquetage java.sql JDBC : DriverManager M. Belguidoum (UMC) Programmation réseau 16 / 52 Mise en ÷uvre de JDBC Mise en ÷uvre de JDBC 1 2 3 4 5 6 7 Importer le package java.sql Enregistrer le driver JDBC Etablir la connexion au SGBD Créer une requête (ou instruction SQL) Exécuter la requête Traiter les données retournées Fermer la connexion M. Belguidoum (UMC) Programmation réseau 17 / 52 Mise en ÷uvre de JDBC Interface Driver La méthode connect() de Driver nécessite un URL vers la base renvoie une instance de l'interface Connection (null si le driver ne convient pas) L'instance de Connection obtenue permet de lancer des requêtes URL pour accéder à la base (syntaxe dépend du SGBD cible) jdbc:<sous-protocole>:<nom-BD>?param=valeur, ... sous-protocole : mysql nom-BD : //foo.loria.fr:1114:maBase exemples String String String String M. Belguidoum (UMC) url url url url = = = = "jdbc:odbc:maBase" "jdbc:mysql://127.0.0.1:3306/maBase" "jdbc:oracle:thin:@charlemagne:infodb" "jdbc:mysql://foo.loria.fr:1114:maBase" Programmation réseau 18 / 52 Mise en ÷uvre de JDBC Étape 1 : enregistrer le driver Rôle de la classe DriverManager : elle gère les diérents drivers (instances de Driver) Pour qu'un driver soit disponible, il faut charger sa classe en mémoire Utiliser la méthode forName de la classe Class avec en paramètre le nom complet de la classe du driver : Class.forName("driverName") crée une instance de la classe enregistre cette instance auprès de la classe DriverManager Class . forName (" oracle . jdbc . driver . OracleDriver " ); // pilote Oracle Class . forName (" sun . jdbc . odbc . JdbcOdbcDriver " ); // pilote ODBC Class . forName (" com . mysql . jdbc . Driver " ); // pilote MySQL Certains compilateurs refusent cette notation et demandent plutôt : Class . forName (" driverName " ). newInstance (); M. Belguidoum (UMC) Programmation réseau 19 / 52 Mise en ÷uvre de JDBC Étape 2 : connexion à la base On utilise la méthode getConnection() de DriverManager avec trois arguments : l'URL (Uniform Ressource Locator) de la base de données de la forme : jdbc:typedriver:base le nom de l'utilisateur de la base : user son mot de passe : password Connection connexion = DriverManager . getConnection ( url , user , passw le DriverManager essaie tous les drivers enregistrés (chargés en mémoire avec Class.forName()) jusqu'à ce qu'il trouve un driver qui lui fournisse une connexion M. Belguidoum (UMC) Programmation réseau 20 / 52 Mise en ÷uvre de JDBC Etape 3 : Créer/exécuter une requête Une fois une Connection créée on peut l'utiliser pour créer et exécuter des requêtes (statements) SQL. 3 types (interfaces) d'objets statement : Statement : requêtes simples (SQL statique) PreparedStatement : requêtes précompilées (SQL dynamique si supporté par SGBD) qui peuvent améliorer les performances CallableStatement : encapsule procédures SQL stockées dans le SGBD 3 méthodes d'exécutions : executeQuery : pour les requêtes qui retournent un résultat (SELECT) résultat accessible au travers d'un objet ResultSet executeUpdate : pour les requêtes qui ne retournent pas de résultat (INSERT, UPDATE, DELETE, CREATE TABLE et DROP TABLE) execute : quand on ne sait pas si la requête retourne ou non un résultat, procédures stockées M. Belguidoum (UMC) Programmation réseau 21 / 52 Mise en ÷uvre de JDBC Etape 3 : Créer/exécuter une requête Création d'un statement : Statement state = connexion . createStatement (); Exécution de la requête : String myQuery = " SELECT prenom , nom , email " + " FROM employe " + " WHERE ( nom =' Dupont ') AND ( email IS NOT NULL ) " + " ORDER BY nom " ; ResultSet resultat = state . executeQuery ( myQuery ); executeQuery(String q) renvoie un objet de type ResultSet décrivant la table des résultats M. Belguidoum (UMC) Programmation réseau 22 / 52 Mise en ÷uvre de JDBC Etape 4 : traiter les données retournées executeQuery() : renvoie une instance de ResultSet qui permet d'accéder aux champs des n-uplets sélectionnés seules les données demandées sont transférées en mémoire par le driver JDBC il faut donc les lire "manuellement" et les stocker dans des variables pour un usage ultérieur Les rangées du ResultSet se parcourent itérativement ligne (row) par ligne boolean next() : permet d'avancer à la ligne suivante, retourne false si pas de ligne suivante Placé avant la première ligne à la création du ResultSet Les colonnes sont référencées par leur numéro ou par leur nom L'accès aux valeurs des colonnes se fait par des méthodes (String nomCol) ou getXXX(int numCol) où XXX dépend du type de la colonne dans la table SQL Pour les très gros row, on peut utiliser des streams. M. Belguidoum (UMC) Programmation réseau 23 / 52 Mise en ÷uvre de JDBC Etape 4 : Exemple de lecture des résultats java . sql . Statement stmt = conn . createStatement (); ResultSet rs = stmt . executeQuery (" SELECT a , b , c FROM Table1 " ); while ( rs . next ()) { int i = rs . getInt ("a " ); // rs . getInt (1); en SQL les numéros de c // débutent à 1 String s = rs . getString ("b" ); // rs . getString (2); byte b [] = rs . getBytes ("c" ); // rs . getBytes (3); System . out . println ( " ROW = " + i + " " + s + " " + b [0]); } M. Belguidoum (UMC) Programmation réseau 24 / 52 Mise en ÷uvre de JDBC ResultSet exécuter next() au moins une fois pour avoir le premier Impossible de revenir au n-uplet précédent ou de parcourir l'ensemble dans un ordre non séquentiel On peut parcourir le ResultSet d'avant en arrière : next() vs. previous() en déplacement absolu (aller à la n-ième ligne) : absolute(int row), first(), last(), ... en déplacement relatif (aller à la n-ième ligne à partir de la position courante du curseur) : relative(int row), afterLast(), beforeFirst(), ... M. Belguidoum (UMC) Programmation réseau 25 / 52 Mise en ÷uvre de JDBC ResultSet Méthodes de parcours first() : Positionne sur la première ligne (1er enregistrement) last() : Positionne sur la dernière ligne (dernier enregistrement) next() : Passe à la ligne suivante previous() : Passe à la ligne précédante beforeFirst() : Positionne avant la première ligne afterLast() : Positionne après la dernière ligne absolute(int) : Positionne à une ligne donnée relative(int) : Déplacement d'un nombre de lignes donné par rapport à ligne courante Méthodes de test de la position du curseur boolean isFirst() : True si curseur positionné sur la première ligne boolean isBeforeFirst() : True si curseur positionné avant la première ligne boolean isLast() : True si curseur positionné sur la dernière ligne boolean isAfterLast() : True si curseur positionné après la dernière ligne M. Belguidoum (UMC) Programmation réseau 26 / 52 Mise en ÷uvre de JDBC Étapes 5 : fermer les connexions Pour terminer proprement un traitement, il faut fermer les diérents espaces ouverts. Le ramasse-miettes peut le faire mais de façon moins ecace Chaque objet possède une méthode close() : resultset . close (); statement . close (); connection . close (); M. Belguidoum (UMC) Programmation réseau 27 / 52 Equivalence des types java-SQL Equivalence des types java-SQL M. Belguidoum (UMC) Programmation réseau 28 / 52 Equivalence des types java-SQL Exemple complet import java . sql .*; public class PrintAllEmployees { public static void main ( String [] args ) throws SQLException , ClassNotFoundException { String dbUrl = " jdbc : mysql :// localhost :3306/ yaps " ; String user = " root " , password = ""; // Chargement dynamique du driver Class . forName (" com . mysql . jdbc . Driver " ); Connection conn ; // Etablissement de la connexion conn = DriverManager . getConnection ( dbUrl , user , password ); Statement st = conn . createStatement (); // Création d 'un statement String anSQLquery = " SELECT prenom , nom , age FROM employe "; // Exécution de la requête ResultSet r = st . executeQuery ( anSQLquery ); // Parcours du résultat while (r. next ()) { String nom = r. getString (" nom " ); int age = r. getInt (" age " ); System . out . println ( nom + " , " + age + " ans " ); } r. close (); st . close (); conn . close (); } } M. Belguidoum (UMC) Programmation réseau 29 / 52 Equivalence des types java-SQL Invocation d'une requête SQL de sélection String anSQLquery = " SELECT prenom , nom , age FROM employe " ; // Création d 'un statement Statement st = conn . createStatement (); // Exécution de la requête ResultSet r = st . executeQuery ( anSQLquery ); M. Belguidoum (UMC) Programmation réseau 30 / 52 Equivalence des types java-SQL Parcours du résultat : ResultSet String query = " SELECT nom , age ... "; ResutSet r = st . executeQuery ( query ); while (r. next ()) { String nom = r. getString (" nom " ); int age = r. getInt (2); // ... } M. Belguidoum (UMC) Programmation réseau 31 / 52 Equivalence des types java-SQL Invocation d'une requête SQL de mise à jour Dans le cas d'une modication de donnée (INSERT, UPDATE, DELETE), on utilise la méthode executeUpdate(...) qui renvoie un entier spéciant le nombre d'enregistrements modiés String reqSql = " DELETE FROM ... "; int n = st . executeUpdate ( reqSql ) M. Belguidoum (UMC) Programmation réseau 32 / 52 Fonctionnalités supplémentaires de JDBC Avec JDBC, on peut aussi ... Exécuter des requêtes SQL pré-compilées : java.sql.PreparedStatement Exécuter des procédures stockées : java.sql.CallableStatement Utiliser des transactions Accéder aux méta-données (schéma) de la base : java.sql.DatabaseMetaData Manipuler des BLOBs M. Belguidoum (UMC) Programmation réseau 33 / 52 Fonctionnalités supplémentaires de JDBC Requêtes pré-compilées La plupart des SGBD ore la possibilité de n'analyser qu'une seule fois une requête JDBC permet de bénécier de cette fonctionnalité L'objet PreparedStatement envoie une requête sans paramètres à la base de données pour précompilation et spéciera le moment voulu la valeur des paramètres plus rapide qu'un Statement classique le SGBD n'analyse qu'une seule fois la requête (recherche d'une stratégie d'exécution adéquate) pour de nombreuses exécutions d'une même requête SQL avec des paramètres variables tous les SGBD n'acceptent pas les requêtes précompilées M. Belguidoum (UMC) Programmation réseau 34 / 52 Fonctionnalités supplémentaires de JDBC Création d'une requête pré-compilée La méthode prepareStatement() de l'objet Connection crée un PreparedStatement : PreparedStatement ps = c. prepareStatement (" SELECT * FROM Clients "+ " WHERE name = ? " ); les paramètres sont spéciés par un " ?" ils sont ensuite instanciés par les méthodes setInt(), setString(), setDate(),... ces méthodes nécessitent 2 arguments (setInt(n, valeur)) le premier (int) indique le numéro relatif de l'argument dans la requête le second indique la valeur à positionner setNull(n,type) positionne le paramètre à NULL (SQL) M. Belguidoum (UMC) Programmation réseau 35 / 52 Fonctionnalités supplémentaires de JDBC Exécution d'une requête pré-compilée PreparedStatement ps = c. prepareStatement (" UPDATE emp SET sal = ? " + " WHERE name = ?" ); for ( int i = 0; i < 10; i ++) { ps . setFloat (1 , salary [i ]); ps . setString (2 , name [i ]); int count = ps . executeUpdate (); } M. Belguidoum (UMC) Programmation réseau 36 / 52 Fonctionnalités supplémentaires de JDBC Avantages des PreparedStatement Traitement plus rapide si utilisés plusieurs fois avec plusieurs paramètres Amélioration de la portabilité car les méthodes setXXX() n'ont pas à tenir compte des diérences entre SGBD Exemple : les SGBD n'utilisent pas tous les mêmes formats de date ('JJ/MM/ AA' ou 'AAAA-MM-JJ') ou de chaînes de caractères (pour les caractères d'échappement) M. Belguidoum (UMC) Programmation réseau 37 / 52 Fonctionnalités supplémentaires de JDBC Les procédures stockées procédures permettant de fournir la même fonctionnalité à plusieurs utilisateurs procédure stockée dans la base côté serveur JDBC ore une interface dédiée CallableStatement dérive de l'interface PreparedStatement gère le retour de valeur avec ResultSet Comment création d'un objet CallableStatement en utlisant Connection.prepareCall(...) M. Belguidoum (UMC) Programmation réseau 38 / 52 Fonctionnalités supplémentaires de JDBC Appel des procédures stockées La requête doit être formatée encadrée par des accolades {...} utilisation du préxe call Trois modes pour l'appel la procédure renvoie une valeur : { ? = call nomProcédure(?,?,...)} la procédure ne renvoie aucune valeur : { call nomProcédure(?,?,...)} si on ne lui passe aucun paramètre : { call nomProcédure } Passage de paramètres possible : comme pour PreparedStatement M. Belguidoum (UMC) Programmation réseau 39 / 52 Fonctionnalités supplémentaires de JDBC Example CallableStatement sans valeur de retour CallableStatement testCall ; testCall = conn . prepareCall ( "{ call setSalary (? ,?) }" ); testCall . setString (1 , " EUR " ); testCall . setLong (2 , 2000); testCall . execute (); M. Belguidoum (UMC) Programmation réseau 40 / 52 Fonctionnalités supplémentaires de JDBC Accéder aux résultats Un PreparedStatement peut retourner des données grâce à un ResultSet Les procédures stockées étendent le modèle appel de la procédure précédé du passage des paramètres in et out grâce aux méthodes setXXX() type des paramètres out et in/out grâce à la méthode registerOutParameter() exécution de la requête grâce à executeQuery(), executeUpdate() ou execute() récupération des paramètres out et in/out grâce aux méthodes getXXX() M. Belguidoum (UMC) Programmation réseau 41 / 52 Fonctionnalités supplémentaires de JDBC Exemple d'une procédure stockée create or replace procedure augmentation ( unDept in integer , pourcentage in number , cout out number ) is begin update emp set sal = sal * (1 + pourcentage / 100) where dept = unDept ; select sum ( sal ) * pourcentage / 100 into cout from emp where dept = unDept ; end ; M. Belguidoum (UMC) Programmation réseau 42 / 52 Fonctionnalités supplémentaires de JDBC Exemple suite CallableStatement CallableStatement csmt = conn . prepareCall ( "{ call augmentation (? , ? , ?) } " ); // 2 chiffres après la virgule pour 3 ème paramètre csmt . registerOutParameter (3 , Types . DECIMAL , 2); // Augmentation de 2 ,5 % des salaires du dept 10 csmt . setInt (1 , 10); csmt . setDouble (2 , 2.5); csmt . executeQuery (); double cout = csmt . getDouble (3); System . out . println ( " Cout total augmentation : " + cout ); M. Belguidoum (UMC) Programmation réseau 43 / 52 Fonctionnalités supplémentaires de JDBC Transactions 3 services déclarés dans l'interface Connection : setAutoCommit(boolean b) commit() : valide une transaction rollback() : annulle une transaction Les nouvelles connexions sont initialement en mode auto-commit => commit implicite après chaque requête SQL Pour dénir une transaction composée de plusieurs requêtes SQL, il faut désactiver l'auto-commit : conn.setAutoCommit(false) Un appel à commit() ou rollback() va alors créer implicitement une nouvelle transaction M. Belguidoum (UMC) Programmation réseau 44 / 52 Fonctionnalités supplémentaires de JDBC Transactions : Exemple // Désactiver l 'auto - commit conn . setAutoCommit ( false ); try { // les requêtes SQL suivantes constituent // une seule transaction st . executeUpdate (" INSERT ... " ); st . executeUpdate (" DELETE ... " ); st . executeUpdate (" UPDATE ... " ); // valider la transaction conn . commit (); st . close (); } catch ( java . sql . SQLException e) { conn . rollback (); } M. Belguidoum (UMC) Programmation réseau 45 / 52 Fonctionnalités supplémentaires de JDBC Accès aux méta-données JDBC permet de récupérer des informations sur le type de données que l'on vient de récupérer par un SELECT (interface ResultSetMetaData) et sur la base elle-même (interface DatabaseMetaData) Interface ResultSetMetaData méthode getMetaData() permet d'obtenir des informations sur les types de données du ResultSet elle renvoie des instances de ResultSetMetaData on peut connaître : getColumnCount() getColumnName(int col) getTableName(int col) le nombre de colonne : le nom d'une colonne : le nom de la table : M. Belguidoum (UMC) Programmation réseau 46 / 52 Fonctionnalités supplémentaires de JDBC Exemple : ResultSetMetaData ResultSet rs = st . executeQuery ( " SELECT * FROM emp " ); ResultSetMetaData rsmd = rs . getMetaData (); int nbColonnes = rsmd . getColumnCount (); for ( int i = 1; i <= nbColonnes ; i ++) { String typeColonne = rsmd . getColumnTypeName ( i ); String nomColonne = rsmd . getColumnName (i ); System . out . println ( " Colonne " + i + " de nom " + nomColonne + " de type " + typeColonne ); } M. Belguidoum (UMC) Programmation réseau 47 / 52 Fonctionnalités supplémentaires de JDBC Interface DatabaseMetaData informations sur la base de données méthode getMetaData() de l'objet Connection dépend du SGBD avec lequel on travaille elle renvoie des instances de DatabaseMetaData on peut connaître : les tables de la base : getTables() le nom de l'utilisateur : getUserName()... M. Belguidoum (UMC) Programmation réseau 48 / 52 Fonctionnalités supplémentaires de JDBC CLOB et BLOB 2 types d'objets larges : CLOB : Character large object BLOB : Binary large Une colonne de type BLOB est en fait un pointeur vers un chier create table userImages ( user varchar (50) , image BLOB ) String q = " insert into userImages values ( ' dewez ', ?) "; Statement pstmt = con . prepareStatement (q ); File file = new File ( " dewez . jpg " ); InputStream fin = new FileInputStream ( file ); pstmt . setBinaryStream (1 , fin , file . length ()); pstmt . executeUpdate (); M. Belguidoum (UMC) Programmation réseau 49 / 52 Fonctionnalités supplémentaires de JDBC Lire un BLOB String q = " select image from userImages " ResultSet rs = stmt . executeQuery (q ); while ( rs . next ) { Blob b = rs . getBlob (" image " ); InputStream stream = b. getBinaryStream (); // ... } M. Belguidoum (UMC) Programmation réseau 50 / 52 Fonctionnalités supplémentaires de JDBC Conclusion Interface pour un accès homogène le concept de Driver masque au maximum les diérences des SGBD API de bas niveau : il faut connaître SQL Tous les éditeurs proposent un driver JDBC Problèmes JDBC n'eectue aucun contrôle sur les instructions SQL Correspondance entre classes Java et relations est un travail de bas niveau M. Belguidoum (UMC) Programmation réseau 51 / 52 Fonctionnalités supplémentaires de JDBC Références The JDBC Tutorial : Chapter 3 - Advanced Tutorial, http://java.sun.com/developer/Books/JDBCTutorial/ slides de Olivier Perrin et philippe Genoud Christian Soutou, "Apprendre SQL avec MySQL : Avec 40 exercices corrigés" M. Belguidoum (UMC) Programmation réseau 52 / 52