Département d’informatique et de recherche opérationnelle IFT 6800 – Démonstration 6 But de la démonstration Cette démonstration montre la communication entre un programme Java et une base de données mysql en utilisant la technologie JDBC (Java Database Connectivity). Préalables Révisez les notes de cours de la séance 8 du cours, ainsi que la démonstration sur mysql. Introduction JDBC est une technologie qui permet à un programme Java de communiquer avec de multiples types de bases de données. Nous verrons ici la communication avec une base de données mysql. Les étapes nécessaires à cette communication sont les suivantes : 1. Installation d’un pilote (« driver ») pour communiquer ; 2. Connexion à la base de données (emplacement, mot de passe, etc.) ; 3. Manipulation des données de la base ; 4. Fermeture de la connexion. Communication Installation d’un pilote Cette installation a déjà été faite pour vous. Assurez-vous d’avoir fait inclure jdk avant de commencer à travailler en Java. Pour activer le pilote, il faut faire, dans une console Linux : source /u/dift6800/HTML/cur/demos/6/prepjdbc Si vous voulez faire vous-même cette configuration, il faut télécharger le pilote Connector/J 5.0 sur le Web1 et altérer votre CLASSPATH pour l’y inclure. Un premier exemple Voici un premier exemple démontrant le code à écrire pour lancer une requête simple, en effectuant les étapes 2 à 4 mentionnées plus haut. Le code est sur le site du cours. 1 http://dev.mysql.com/downloads/connector/j/5.0.html 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 /** * Cet exemple demontre jdbc avec une base de donnes mysql. * * @author dift6800 * @date Sept. 2006 */ import java.sql.*; public class Connexion { public static void main(String args[]) { try { // on charge le pilote mysql Class.forName("com.mysql.jdbc.Driver"); ❶ System.err.println( "Pilote chargé." ); // on ouvre une connexion vers la base String protocole = "jdbc:mysql://mysql.iro.umontreal.ca:3306/"; String nomDeLaBase = "nom de la base"; String nomUtilisateur = "nom utilisateur"; String motDePasse = "mot de passe mysql"; Connection conn = ❷ DriverManager.getConnection(protocole + nomDeLaBase, nomUtilisateur, motDePasse); 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 System.err.println("On a obtenu une connexion."); // on cree un "statement", c'est-à-dire une instruction mysql Statement stmt = conn.createStatement(); ❸ // on lance l'instruction, et on recupère le résultat ❹ ResultSet rset = stmt.executeQuery("SELECT * FROM Clients"); // on peut ensuite parcourir tous les enregistrements retournés System.err.println("Impression des résultats."); while ( rset.next() ) // tant qu'il reste un enregistrement { // imprimer le premier champ, qui est un entier System.out.println( rset.getInt(1) ); ❺ } // on ferme ensuite le curseur ❻ rset.close(); // puis l'instruction stmt.close(); // et enfin la connexion conn.close(); System.err.println("Connexions fermées."); } 2 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 // cette partie plus technique intercepte certains erreurs. // contentez-vous de la recopier dans vos programmes catch (SQLException e) { System.err.println("Une erreur SQL est survenue !"); System.err.println("SQLException: " + e.getMessage()); System.err.println("SQLState: " + e.getSQLState()); System.err.println("VendorError: " + e.getErrorCode()); } catch (ClassNotFoundException e) { System.err.println( "Avez-vous fait source... ?" ); System.err.println(e.getMessage()); e.printStackTrace(); } } } ❶ On charge le pilote. Cette instruction ne change jamais dans le cadre de ce cours. ❷ Pour ouvrir une connexion vers la base, il faut utiliser la méthode getConnection, qui demande 3 arguments : l’url, le nom d’utilisateur et son mot de passe. L’url inclut le nom de la base de données qui sera utilisée. Pour les exercices, il vous faut compléter ces champs pour accéder à votre base de données Surf, créée pour le devoir 1. ❸ On crée un Statement, qui nous permet d’exécuter des instructions mysql. ❹ Ici, on exécute une instruction mysql, exactement comme vous l’auriez entrée à l’invite de commande de mysql. Notez cependant qu’il n’y a pas de point-virgule à la fin de l’instruction. ❺ L’instruction SELECT effectuée plus tôt a retourné des résultats. On peut les consulter en utilisant un curseur, qui permet de balayer tour à tour chacun des enregistrements retournés par l’instruction SELECT, présentés dans le même ordre que celui vu lors de la démonstration sur mysql. ❻ Il faut toujours fermer les curseurs, Statement et connexions ouverts dans le programme. Exercices 1. Téléchargez l’exemple plus haut (Connexion.java) sur le site du cours. Modifiez-le pour qu’il puisse se connecter à votre base de données Surf, puis compilez-le et exécutez-le. Travailler avec Statement Les objets Statement permettent l’exécution de requêtes SQL et d’en récupérer les résultats via la classe ResultSet. Pour créer un Statement, on fait comme à la 3 ligne 34 dans notre exemple plus haut. Avec cet objet en main, on peut faire deux choses : soit une requête de type SELECT, soit une mise à jour (UPDATE ou INSERT). Type de requête SELECT UPDATE ou INSERT Méthode à utiliser sur un objet Statement stmt stmt.executeQuery(String s) Résultat Un objet ResultSet contenant les résultats de la requête. stmt.executeUpdate(String s) Le nombre de rangées affectées par la mise à jour. Rappelez-vous que tous ces objets et toutes ces méthodes sont documentés en détail via la javadoc (la documentation Java). Vous trouverez tout ce qu’il vous faut à l’url http://java.sun.com/j2se/1.5.0/docs/api/. Cherchez ResultSet dans le panneau en bas à gauche et vous aurez toute la documentation sur la classe. Requêtes de type SELECT Voici un parallèle entre ce que nous aurions pu faire avec l’invite de commande de mysql et l’équivalent en code Java. mysql mysql> SELECT * FROM Clients; +----------+---------+---------+------+ | noClient | nom | prenom | age | +----------+---------+---------+------+ | 1 | Serien | Jean | 0 | | 2 | Tell | William | 0 | | 3 | Verse | Alain | 17 | | 4 | Leboeuf | Marine | 0 | +----------+---------+---------+------+ 4 rows in set (0.00 sec) Java ResultSet rset = stmt.executeQuery("SELECT * FROM Clients"); while ( rset.next() ) // tant qu'il reste un enregistrement { System.out.println( rset.getInt(1) ); } Ceci va afficher à l’écran la première colonne de la table plus haut. 1 2 3 4 Avec l’objet rset, on peut parcourir un à un chacun des enregistrements des résultats de la requête. On fait rset.getXXXXX(n) pour lire le contenu de la colonne n. Plusieurs 4 méthodes sont à notre disposition, dépendamment du type de la colonne (le type de champ) dans la table interrogée. Type de champ Méthode à utiliser sur un objet Type Java du résultat ResultSet rset dans la table mysql INTEGER rset.getInt(int n) int FLOAT rset.getFloat(int n) float DATE rset.getDate(int n) Date VARCHAR rset.getString(int n) String n est le numéro de la colonne où se trouve l’information que l’on recherche. Contrairement à Java, l’index de la première colonne est 1, pas 0. C’est donc pourquoi on fait rset.getInt(1) pour accéder au contenu de la première colonne pour l’enregistrement courant. À chaque fois que l’on fait rset.next(), on passe à la ligne suivante dans la table. Cette méthode retourne également un booléen qui indique s’il reste encore des lignes à lire. La boucle while permet donc de faire deux choses : parcourir chacun des enregistrements du résultat de la requête et s’assurer qu’il reste un résultat à lire. Pour afficher les noms de tous les clients, on aurait pu donc faire : while ( rset.next() ) // tant qu'il reste un enregistrement { System.out.println( rset.getString(2) ); // 2e colonne = nom } Exercices 2. Ouvrez une connexion sur votre base Surf, dans mysql (voir démonstration 2) et effectuez la requête suivante : SELECT AVG(age) FROM Clients; Ceci permet d’afficher la moyenne de la colonne noClient pour tous les clients de la table Clients. Créez ensuite un programme Java MoyenneAge.java qui effectue exactement la même requête et stocke le résultat du calcul de moyenne dans une variable moyenne, puis affiche la valeur de cette variable à l’écran. Requêtes de type UPDATE ou INSERT Pour modifier une table mysql, il faut utiliser la méthode stmt.executeUpdate(String s) sur un objet stmt de type Statement. L’argument de la méthode est l’instruction SQL, telle qu’on l’aurait saisie sur la console mysql. Ceci permet d’exécuter les ordres INSERT et UPDATE que nous avons vus en SQL. 5 Cette méthode retourne un entier qui désigne le nombre de rangées qui ont été modifiées par la mise à jour ou l’insertion d’enregistrements dans la base active. À nouveau, voyons un parallèle entre ce que nous aurions pu faire avec l’invite de commande de mysql et l’équivalent en code Java. mysql mysql> INSERT INTO Clients(noClient, nom, prenom, age) VALUES(5, 'Langlois', 'Francois', 22); Query OK, 1 row affected (0.00 sec) Remarquez que mysql répond en vous disant le nombre de rangées affectées. C’est cette valeur qui est retournée par la méthode executeUpdate. Java int nbrangees = stmt.executeUpdate("INSERT INTO Clients(noClient, nom, prenom, age) VALUES(5, 'Langlois', 'Francois', 22)" ); System.out.println("Le nombre de rangées affectées est " + nbrangees); Ces deux lignes de Java feront la même chose que l’instruction mysql. L’argument de la méthode executeUpdate peut être n’importe laquelle des commandes de mise à jour et de création de table mysql que nous avons vues, mais pas de SELECT (qui sont faits avec la méthode executeQuery, vue plus haut). On pourrait notamment faire ceci, par exemple : String commande = "DROP TABLE Clients"; int nbrangees = stmt.executeUpdate(commande); pour détruire la table Clients. Ou encore, on pourrait faire ceci String commande = "UPDATE Clients SET age=17 WHERE noClient=3"; int nbrangees = stmt.executeUpdate(commande); pour mettre à jour un enregistrement particulier dans la table Clients. Exercices 3. Créez un programme Employes.java qui crée la table suivante dans la base de données Surf (référez-vous à la démonstration 2 et au devoir 1 pour vous rafraîchir la mémoire). Respectez la contrainte PRIMARY KEY soulignée. Employes nom matricule position Jules Renard Romain Gary Alex Tremblay 847 47422 44496 president vp finances vp marketing 4. Dans le même programme, affichez le nom et le matricule de tous les employés dont le nom contient un ‘y’ en utilisant l’opérateur SQL LIKE. 6 Quelques références intéressantes Pour vous aider à travailler avec SQL et JDBC, voici quelques pistes. SOUTOU, Christian, Apprendre SQL avec MySQL, Paris, Eyrolles, 2006, 396 pages. Cet excellent livre est mis en réserve à la bibliothèque de Math-Info pour notre cours. http://www-sop.inria.fr/semir/personnel/Patrick.Itey/cours/jdbc/cnrs/tps/3.html Un bon tutorial en français. http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-basic.html La référence sur le site des créateurs de mysql. http://www.developer.com/java/data/article.php/3417381f Un bon tutorial en anglais. Sources Rédigé par Fabrizio Gotti, automne 2007. 7