Java : gérer une base de données avec JDBC Achref El Mouelhi Docteur de l’université d’Aix-Marseille Chercheur en programmation par contrainte (IA) Ingénieur en génie logiciel [email protected] H & H: Research and Training 1 / 41 Plan 1 Introduction 2 Utilisation 3 Transactions 4 Restructuration du code Classes connexion et dao DataSource et fichier de propriétés H & H: Research and Training 2 / 41 Introduction JDBC Pour se connecter à une base de données Il nous faut un JDBC (qui varie selon le SGBD utilisé) I H L E SGBD : Système de Gestion de BasesU de données O M L E ref h c c A JDBC : Java DataBase Connectivity H & H: Research and Training c 3 / 41 Introduction JDBC Pour se connecter à une base de données Il nous faut un JDBC (qui varie selon le SGBD utilisé) I H L E SGBD : Système de Gestion de BasesU de données O M L E ref h c JDBC ? c A JDBC : Java DataBase Connectivity c Une API (interface d’application) créée par Sun Microsystems Il permet d’accéder aux bases de données en utilisant un driver JDBC H & H: Research and Training 3 / 41 Introduction JDBC c I H EL Aller sur U MO https://dev.mysql.com/downloads/file/?id=480292 L f E l’archive .zip e r Télécharger et Dh écompresser c c A JDBC 8.0.13 H & H: Research and Training 4 / 41 Introduction JDBC c I H Ldans New > Faire un clic droit sur le nom du projet et aller E U Folder MO L E puis valider Renommer le répertoire f lib e r h c Copier le .jar c A de l’archive décompressée dans lib Intégrer le driver dans votre projet H & H: Research and Training 5 / 41 Introduction JDBC Ajouter JDBC au path du projet Faire clic droit sur .jar qu’on a placé dans lib Aller dans Build Path et choisir Add to Build Path L E f hre HI L E U MO c c c A H & H: Research and Training 6 / 41 Introduction JDBC Ajouter JDBC au path du projet Faire clic droit sur .jar qu’on a placé dans lib Aller dans Build Path et choisir Add to Build Path Ou aussi HI L E U MO c Faire clic droit sur le projet dans Package Explorer et aller dans Properties Properties L E f hre Dans Java Build Path, aller dans l’onglet Libraries c c A Cliquer sur Add JARs Indiquer le chemin du .jar qui se trouve dans le répertoire lib du projet Appliquer H & H: Research and Training 6 / 41 Introduction JDBC Ajouter JDBC au path du projet Faire clic droit sur .jar qu’on a placé dans lib Aller dans Build Path et choisir Add to Build Path Ou aussi HI L E U MO c Faire clic droit sur le projet dans Package Explorer et aller dans Properties Properties L E f hre Dans Java Build Path, aller dans l’onglet Libraries c c A Cliquer sur Add JARs Indiquer le chemin du .jar qui se trouve dans le répertoire lib du projet Appliquer Vérifier qu’une section Referenced Libraries a apparu. H & H: Research and Training 6 / 41 Utilisation JDBC Avant de commencer, voici le script SQL qui permet de créer la base de données utilisée dans ce cours CREATE DATABASE jdbc; HI L CREATE TABLE personne( E num INT PRIMARY KEY AUTO_INCREMENT, OU M nom VARCHAR(30), L E prenom VARCHAR(30) ref ); h c c A SHOW TABLES; USE jdbc; c INSERT INTO personne (nom, prenom) VALUES ("Wick", "John"), ("Dalton", "Jack"); SELECT * FROM personne; H & H: Research and Training 7 / 41 Utilisation JDBC c I H L cas) Enotre Charger le driver JDBC (pour MySQL dans U MO Établir la connexion avec E la L base de données ref h Créer et exécuter des requêtes SQL c c A Trois étapes H & H: Research and Training 8 / 41 Utilisation JDBC I H L E Avant de commencer OU Tous les imports de ce chapitre sontM de java.sql.*; L E f re h c c A H & H: Research and Training c 9 / 41 Utilisation JDBC Chargement du driver 5 try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); } L E f r8e Chargement du driver h c try { c A HI L E U MO c Class.forName("com.mysql.cj.jdbc.Driver"); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); } H & H: Research and Training 10 / 41 Utilisation JDBC Se connecter à la base de données c I H ELMySQL est installé hote : le nom de l’hôte sur lequel le serveur U (dans notre cas localhost ou 127.0.0.1) MO L Eé par défaut par MySQL est 3306 futilis port : le port TCP/IP e r h c nombd :A c le nom de la base de données MySQL Il faut spécifier l’URL (de la forme jdbc:mysql://hote:port/nombd) Il faut aussi le nom d’utilisateur et son mot de passe (qui permettent de se connecter à la base de données MySQL) H & H: Research and Training 11 / 41 Utilisation JDBC Connexion à la base String url = "jdbc:mysql://localhost:3306/jdbc"; String user = "root"; String password = ""; Connection connexion = null; try { connexion = DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); } finally { if (connexion != null) try { connexion.close(); } catch (SQLException ignore) { ignore.printStackTrace(); } } L E f hre HI L E U MO c c c A H & H: Research and Training 12 / 41 Utilisation JDBC HI L E U MO c En cas de problème avec le chargement du driver, modifier l’URL de connexion ainsi : String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false& serverTimezone=UTC"; L E f hre c c A H & H: Research and Training 13 / 41 Utilisation JDBC Préparation et exécution de la requête // création de la requête (statement) Statement statement = connexion.createStatement(); c I H EL U O // Exécution de la requête M L ResultSet result = e statement.executeQuery(request); fE r h c c A // Préparation de la requête String request = "SELECT * FROM Personne;"; H & H: Research and Training 14 / 41 Utilisation JDBC Préparation et exécution de la requête // création de la requête (statement) Statement statement = connexion.createStatement(); c I H EL U O // Exécution de la requête M L ResultSet result = e statement.executeQuery(request); fE r h Ac c On utilise // Préparation de la requête String request = "SELECT * FROM Personne;"; executeQuery() pour les requêtes de lecture : select. executeUpdate() pour les requêtes d’écriture : insert, update, delete et create. H & H: Research and Training 14 / 41 Utilisation JDBC Récupération des données c while (result.next()) { // on indique chaque fois le nom de la colonne et le type int num = result.getInt("num"); String nom = result.getString("nom"); String prenom = result.getString("prenom"); System.out.println(num + " " + nom + " " + prenom); } L E f hre HI L E U MO c c A H & H: Research and Training 15 / 41 Utilisation JDBC On peut faire aussi while (result.next()) { // on peut indiquer aussi l’index de la colonne et le type int idPersonne = result.getInt(1); String nom = result.getString(2); String prenom = result.getString(3); L E f hre HI L E U MO c c A c // pareil pour tous les autres attributs System.out.println(nom + " " + prenom); } H & H: Research and Training 16 / 41 Utilisation JDBC Pour faire une insertion Statement statement = connexion.createStatement(); String request = "INSERT INTO Personne (nom,prenom) VALUES (’Wick’,’John’);"; int nbr = statement.executeUpdate(request); if (nbr ! = 0) System.out.println("insertion réussie"); L E f hre HI L E U MO c c c A H & H: Research and Training 17 / 41 Utilisation JDBC Pour faire une insertion Statement statement = connexion.createStatement(); String request = "INSERT INTO Personne (nom,prenom) VALUES (’Wick’,’John’);"; int nbr = statement.executeUpdate(request); if (nbr ! = 0) System.out.println("insertion réussie"); L E f hre HI L E U MO c c A c La méthode executeUpdate() retourne 0 en cas d’échec de la requête d’insertion, et 1 en cas de succès le nombre de lignes respectivement mises à jour ou supprimées H & H: Research and Training 17 / 41 Utilisation JDBC Pour récupérer la valeur de la clé primaire auto-générée Statement statement = connexion.createStatement(); String request = "INSERT INTO Personne (nom,prenom) Wick’,’John’);"; HI L E U MO VALUES (’ c // on demande le renvoi des valeurs attribuées à la clé primaire statement.executeUpdate(request,Statement.RETURN_GENERATED_KEYS ); L E f // on parcourt les valeurs re attribuées à l’ensemble de tuples h c ajoutés ResultSet resultat c A = statement.getGeneratedKeys(); // on vérifie s’il contient au moins une valeur if (resultat.next()) { System.out.println("Le numéro généré pour cette personne : " + resultat.getInt(1)); } H & H: Research and Training 18 / 41 Utilisation JDBC Pour éviter les injections SQL, il faut utiliser les requêtes préparées (pour la consultation, l’ajout, la suppression et la modification) String request = "INSERT INTO Personne (nom,prenom) VALUES (?,?);"; PreparedStatement ps = connexion.prepareStatement( request,PreparedStatement.RETURN_GENERATED_KEYS); ps.setString(1, "Wick"); ps.setString(2, "John"); ps.executeUpdate(); ResultSet resultat = ps.getGeneratedKeys(); if (resultat.next()) System.out.println("Le numéro généré pour cette personne : " + resultat.getInt(1)); L E f hre HI L E U MO c c c A H & H: Research and Training 19 / 41 Utilisation JDBC Pour éviter les injections SQL, il faut utiliser les requêtes préparées (pour la consultation, l’ajout, la suppression et la modification) String request = "INSERT INTO Personne (nom,prenom) VALUES (?,?);"; PreparedStatement ps = connexion.prepareStatement( request,PreparedStatement.RETURN_GENERATED_KEYS); ps.setString(1, "Wick"); ps.setString(2, "John"); ps.executeUpdate(); ResultSet resultat = ps.getGeneratedKeys(); if (resultat.next()) System.out.println("Le numéro généré pour cette personne : " + resultat.getInt(1)); L E f hre HI L E U MO c c c A Attention à l’ordre des attributs H & H: Research and Training 19 / 41 Transactions JDBC c I H ensemble de requête SQL EL U appliquant le principe soit tout (toutes MOles requête SQL) soit rien L E f MySQL activées par défautravec e h c pouvantcêtreA désactivées et gérées par le développeur Les transactions H & H: Research and Training 20 / 41 Transactions JDBC Pour désactiver l’auto-commit connection.setAutoCommit(false); Pour valider une transaction L E f hre connection.commit(false); HI L E U MO c c c A Pour annuler une transaction connection.rollback(false); H & H: Research and Training 21 / 41 Transactions JDBC Exemple avec les transactions // désactiver l’auto-commit connexion.setAutoCommit(false); c String request = "INSERT INTO Personne (nom,prenom) VALUES (?,?);"; PreparedStatement ps = connexion.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS); ps.setString(1, "Wick"); ps.setString(2, "John"); ps.executeUpdate(); L E f hre HI L E U MO c c A // valider l’insertion connexion.commit(); ResultSet resultat = ps.getGeneratedKeys(); if (resultat.next()) System.out.println("Le numéro généré pour cette personne : " + resultat.getInt(1)); H & H: Research and Training 22 / 41 Restructuration du code Classes connexion et dao JDBC Organisation du code c Il faut mettre toutes les données (url, nomUtilisateur, motDePasse...) relatives à notre connexion dans une classe connexion L E f hre HI L E U MO Pour chaque table de la base de données, on crée une classe java ayant comme attributs les colonnes de cette table c c A Il faut mettre tout le code correspondant à l’accès aux données (de la base de données) dans des nouvelles classes et interfaces (qui constitueront la couche DAO : Data Access Object) H & H: Research and Training 23 / 41 Restructuration du code Classes connexion et dao La classe MyConnection package org.eclipse.config; public class MyConnection { private static String url = "jdbc:mysql://localhost:3306/jdbc?useSSL= false&serverTimezone=UTC"; private static String utilisateur = "root"; private static String motDePasse = ""; private static Connection connexion = null; private MyConnection() { try { Class.forName("com.mysql.cj.jdbc.Driver"); connexion = DriverManager.getConnection( url, utilisateur, motDePasse ); } catch ( Exception e ) { e.printStackTrace(); } } public static Connection getConnection() { if (connexion == null) { new MyConnection(); } return connexion; } L E f hre HI L E U MO c c c A H & H: Research and Training 24 / 41 Restructuration du code Classes connexion et dao JDBC La classe MyConnection (suite) public static void stop() { if (connexion != null) { try { connexion.close(); } catch (SQLException e) { e.printStackTrace(); } } } L E f hre HI L E U MO c c c A } H & H: Research and Training 25 / 41 Restructuration du code Classes connexion et dao JDBC La classe Personne package org.eclipse.model; public class Personne{ private int num; private String nom; private String prenom; L E f hre HI L E U MO c c c A // + getters + setters + constructeur sans param ètre + constructeur avec 2 paramètres nom et prénom + constructeur avec 3 paramètres } H & H: Research and Training 26 / 41 Restructuration du code Classes connexion et dao JDBC L’interface PersonneDao package org.eclipse.dao; HI L E import org.eclipse.model.Personne; OU M L { E public interface PersonneDao f re h Personne save(Personne personne); c void c Aremove(Personne personne); import java.util.List; c Personne update(Personne personne); Personne findById(int id); List<Personne> getAll(); } H & H: Research and Training 27 / 41 Restructuration du code Classes connexion et dao La classe PersonneDaoImpl définie dans org.eclipse.dao public class PersonneDaoImpl implements PersonneDao { @Override public Personne save(Personne personne) { Connection c = MyConnection.getConnection(); if (c != null) { try { PreparedStatement ps = c.prepareStatement("insert into personne (nom,prenom) values (?,?); ", PreparedStatement. RETURN_GENERATED_KEYS); ps.setString(1, personne.getNom()); ps.setString(2, personne.getPrenom()); ps.executeUpdate(); ResultSet resultat = ps.getGeneratedKeys(); if (resultat.next()) { personne.setNum(resultat.getInt(1)); return personne; } } catch (SQLException e) { e.printStackTrace(); } } return null; } L E f hre HI L E U MO c c c A H & H: Research and Training 28 / 41 Restructuration du code Classes connexion et dao Exemple de la méthode save en utilisant les transactions public Personne save(Personne personne) { Connection c = MyConnection.getConnection(); if (c != null) { try { c.setAutoCommit(false); PreparedStatement ps = c.prepareStatement("insert into personne (nom,prenom) values (?,?); ", PreparedStatement. RETURN_GENERATED_KEYS); ps.setString(1, personne.getNom()); ps.setString(2, personne.getPrenom()); ps.executeUpdate(); ResultSet resultat = ps.getGeneratedKeys(); if (resultat.next()) { c.commit(); personne.setNum(resultat.getInt(1)); return personne; } } catch (SQLException e) { e.printStackTrace(); } } return null; } L E f hre HI L E U MO c c c A H & H: Research and Training 29 / 41 Restructuration du code Classes connexion et dao JDBC La classe PersonneDaoImpl (suite) @Override public Personne findById(int id) { Personne personne = null; Connection c = MyConnection.getConnection(); if (c != null) { try { PreparedStatement ps = c.prepareStatement("select * from personne where num = ?; "); ps.setInt(1, id); ResultSet r =ps.executeQuery(); if (r.next()) personne = new Personne(r.getInt("num"),r.getString("nom"),r. getString("prenom")); } catch (SQLException e) { e.printStackTrace(); } } return personne; } L E f hre HI L E U MO c c c A } Il faut implémenter toutes les méthodes de l’interface PersonneDao H & H: Research and Training 30 / 41 Restructuration du code Classes connexion et dao Le Main pour tester toutes ces classes package org.eclipse.classes; import org.eclipse.dao.PersonneDaoImpl; import org.eclipse.model.Personne; c I H public static void main(String args []) { L E U O PersonneDaoImpl personneDaoImpl new PersonneDaoImpl(); M =("Wick","John"); L Personne personne = newE Personne Personne insertedPersonne ref = personneDaoImpl.save(personne); h c A if (insertedPersonne != null) c System.out.println("personne numéro " + insertedPersonne. public class Main { getNum() + " a été insérée"); else System.out.println("problème d’insertion"); } } H & H: Research and Training 31 / 41 Restructuration du code Classes connexion et dao JDBC c I H Remarque EL U Oautres méthodes de N’oublions pas d’implémenter les quatre M L l’interface Dao fE e r h c c A H & H: Research and Training 32 / 41 Restructuration du code Classes connexion et dao JDBC c I H Nous devons créer autant d’interfaces DAO que EL tables de la U bases de données MO L E une seule interface Dao avec un Pour éviter cela, on peut ef utiliser rtoutes type génériquecque les classes d’accès aux données h A doiventc l’implémenter. Utilisation de la généricité avec les DAO H & H: Research and Training 33 / 41 Restructuration du code Classes connexion et dao JDBC L’interface Dao package org.eclipse.dao; HI L E U public interface Dao <T> { MO L E T save(T obj); f re obj); void remove(T h c A obj); Tcupdate(T T findById(int id); import java.util.List; c List<T> getAll(); } H & H: Research and Training 34 / 41 Restructuration du code Classes connexion et dao JDBC La classe PersonneDaoImpl c I H EL U public class PersonneDaoImpl implements MO Dao<Personne>{ L fE e ... r h c A c Rien ne change pour le reste package org.eclipse.dao; H & H: Research and Training 35 / 41 Restructuration du code DataSource et fichier de propriétés JDBC Encore de la restructuration du code HI L E U MO c Mettre les données (url, nomUtilisateur, motDePasse...) relatives à notre connexion dans un fichier de propriétés que nous appelons db.properties (utilisé par certain framework comme Spring) L E f hre Créer une nouvelle classe (DataSourceFactory) qui va lire et construire les différentes propriétés de la connexion Ac c Utiliser DataSourceFactory dans MyConnection H & H: Research and Training 36 / 41 Restructuration du code DataSource et fichier de propriétés JDBC Le fichier db.properties situé à la racine du projet (ayant la forme clé = valeur, le nom de la clé est à choisir par l’utilisateur) c I H EL U url=jdbc:mysql://localhost:3306/jdbc?useSSL=false& MO L serverTimezone=UTCf E username=root chre password=root c A H & H: Research and Training 37 / 41 Restructuration du code DataSource et fichier de propriétés Créons la classe MyDataSourceFactory dans org.eclipse.config import import import import import java.io.FileInputStream; java.io.IOException; java.util.Properties; javax.sql.DataSource; com.mysql.jdbc.jdbc2.optional.MysqlDataSource; c public class MyDataSourceFactory { public static DataSource getMySQLDataSource() { Properties props = new Properties(); FileInputStream fis = null; MysqlDataSource mysqlDataSource = null; try { fis = new FileInputStream("db.properties"); props.load(fis); mysqlDataSource = new MysqlDataSource(); mysqlDataSource.setURL(props.getProperty("url")); mysqlDataSource.setUser(props.getProperty("username")); mysqlDataSource.setPassword(props.getProperty("password")); } catch (IOException e) { e.printStackTrace(); } return mysqlDataSource; } } L E f hre HI L E U MO c c A H & H: Research and Training 38 / 41 Restructuration du code DataSource et fichier de propriétés JDBC c I H EpasL le driver U Dans MyDataSourceFactory, on ne pr écise O com.mysql.jdbc.Driver car L on M utilise un objet de la classe f Elui même le driver. MysqlDataSource quircharge e h c c A Remarque H & H: Research and Training 39 / 41 Restructuration du code DataSource et fichier de propriétés La classe MyConnection du package org.eclipse.config import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; public class MyConnection { private static Connection connexion = null; private MyConnection() { HI L E U MO c DataSource dataSource = MyDataSourceFactory.getMySQLDataSource(); try { connexion = dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); } L E f hre c c A } public static Connection getConnection() { if (connexion == null) { new MyConnection(); } return connexion; } H & H: Research and Training 40 / 41 Restructuration du code DataSource et fichier de propriétés Relançons le Main et vérifier que tout fonctionne correctement package org.eclipse.classes; import org.eclipse.dao.PersonneDaoImpl; import org.eclipse.model.Personne; c I H public static void main(String args []) { L E U O PersonneDaoImpl personneDaoImpl new PersonneDaoImpl(); M =("Wick","John"); L Personne personne = newE Personne Personne insertedPersonne ref = personneDaoImpl.save(personne); h c A if (insertedPersonne != null) c System.out.println("personne numéro " + insertedPersonne. public class Main { getNum() + " a été insérée"); else System.out.println("problème d’insertion"); } } H & H: Research and Training 41 / 41