====== TP Programmation et Bases de Données ====== === Rendu === Il faut rendre un zip contenant projet maven et le fichier sql commenté comprenant le codes des triggers et des procédures stockées. Bien indiquer dans les différents fichiers sources le nom et le numéro des deux membres du binôme. Le rendu devra être effectué avant le 19/12/2011 [[haytham.elghazel@univ-lyon1.fr?subject=[BDAV-TP2] Rendu|par mail]] === Liens === * [[http://download.oracle.com/javase/6/docs/api/|API Java 6]] * [[http://docs.postgresql.fr/9.0/plpgsql.html|Documentation PL/pgSQL]] * [[http://jdbc.postgresql.org/documentation/publicapi/index.html|API Driver PostgreSQL]] === Référence PL/SQL Oracle === [[http://www.techonthenet.com/oracle/]] Référence PL/SQL Oracle === Driver Oracle === Une dépendance maven n'est pas satisfaite. Télécharger [[http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html|ojdbc14.jar]]. Dans Netbeans, dans le projet, ouvrir //Bibliothèques//, faire un clic-droit sur //ojdbc14-1.0-RELEASE.jar// -> //Installer manuellement un artefact// et indiquer le fichier téléchargé. ===== Mise en place ===== ==== Base Postgres ==== Une base PostgreSQL est installée sur les machines de TP. L'utilitaire permettant de gérer la base se nom pgAdminIII et est également installé sur les machines de TP. La base possède un login ''etudiant'' ayant comme mot de passe ''mdpetu'' ou ''etudiant''. ==== Schema et données ==== On donne le schéma relationnel mis en place au [[tp-xml-xquery-oracle|tp précédent]], porté sur PostgreSQL: -- Schema CREATE TABLE membre(id integer PRIMARY KEY, nom character varying (40), email character varying (100) UNIQUE); CREATE TABLE livre(id integer PRIMARY KEY, editeur integer REFERENCES membre(id), titre character varying (100) UNIQUE); CREATE TABLE partie(id integer PRIMARY KEY, livre integer REFERENCES livre(id), inclus_dans integer NULL, titre character varying (100) NULL, contenu character varying (1000)); ALTER TABLE partie ADD constraint fk_inclus FOREIGN KEY (inclus_dans) REFERENCES partie(id); CREATE TABLE auteur_partie(auteur INTEGER REFERENCES membre(id), partie INTEGER REFERENCES partie(id), PRIMARY KEY (partie,auteur)); -- Donnees INSERT INTO membre VALUES(1,'Toto','toto@blagues.com'); INSERT INTO membre VALUES(2,'Titi','titi@canaries.com'); INSERT INTO membre VALUES(3,'Rominet','sylvestre@eating-birds.com'); INSERT INTO membre VALUES(4,'Bugs Bunny','bugs@whatsupdoc.com'); INSERT INTO livre VALUES(1,1,'De bonnes blagues'); INSERT INTO livre VALUES(2,2,'Dessins animés'); INSERT INTO partie VALUES(1,1,NULL,'Recueil','Les meilleures blagues sont les plus courtes.'); INSERT INTO partie VALUES(2,1,1 ,'Toto va à l''école','C''est Toto à l''école. Sa prof lui demande: '||CHR(10)||' - Toto quelle est la 5 ème lettre de l''alphabet? '||CHR(10)||' - Euh ?'); INSERT INTO partie VALUES(3,1,NULL,'D''autres blagues à venir','Quand j''aurais le temps ...'); INSERT INTO partie VALUES(4,2,NULL,'Personnages',''); INSERT INTO partie VALUES(5,2,4 ,'Les chats','Ils sont vils et imprévisibles.'); INSERT INTO partie VALUES(6,2,5 ,NULL,'Evidement, tout chat aime à manger les canaris.'); INSERT INTO partie VALUES(7,2,5 ,NULL,'Les chiens sont souvent mis à contribution pour les chasser. C''est épuisant'); INSERT INTO partie VALUES(8,2,NULL,'Les canaris','Ils sont si mignons (et si bons).'); INSERT INTO auteur_partie VALUES (1,1); INSERT INTO auteur_partie VALUES (1,2); INSERT INTO auteur_partie VALUES (1,3); INSERT INTO auteur_partie VALUES (4,4); INSERT INTO auteur_partie VALUES (2,5); INSERT INTO auteur_partie VALUES (3,6); INSERT INTO auteur_partie VALUES (4,7); INSERT INTO auteur_partie VALUES (2,8); INSERT INTO auteur_partie VALUES (3,8); ==== Projet Maven ==== Maven est un utilitaire de compilation/exécution/test de projet Java qui sera utilisé dans le cadre des TP de l'UE. Configurer Maven comme indiqué [[:enseignement:aide:start#maven|dans l'aide]]. Télécharger et décompresser le projet de base: {{:enseignement:bdav:jdbc_bdav.zip}} Afin d'initialiser maven correctement, lancer une compilation (bouton avec un marteau). Un projet Maven possède deux répertoires de sources: src/main et src/test. Le premier contient les classes "métier" alors que le second ne contient que le code de test. Dans Netbeans, un projet Maven est vu comme un projet Netbeans et peut ainsi être directement ouvert depuis l'EDI. ===== Connexion JDBC ===== Créer une classe ''epul.bdav.jdbc.Connexion'' et y ajouter une méthode: public java.sql.Connection seConnecter() throws java.sql.SQLException permettant d'obtenir un objet représentant une connexion à votre base PostgreSQL. On s'inspirera du code suivant: [[enseignement:aide:oracle#via_jdbc_dans_un_programme_java|Configuration d'une connexion oracle en Java]]. PGSimpleDataSource dataSource = new PGSimpleDataSource(); dataSource.setDatabaseName("postgres"); dataSource.setUser("etudiant"); dataSource.setPassword("etudiant"); return dataSource.getConnection(); Ajouter une méthode de test de connexion dans la classe epul.bdav.jdbc.TestJDBC: public void testSeConnecter() { try { Connexion connexion = new Connexion(); java.sql.Connection c = connexion.seConnecter(); c.close(); } catch (java.sql.SQLException e) { fail("Erreur de connexion: "+e.getMessage()); } } Lancer le test (depuis le menu contextuel du projet dans Netbeans). Voir également [[enseignement:aide:apis#junit|l'aide sur JUnit]]. Modifier la classe TestJDBC de la manière suivante: // Ajouter un champ c représentant la connexion à PostgreSQL private Connection c; // Initialiser ce champ dans une méthode setUp /** * Initialisation des tests */ public void setUp() throws Exception { c = (new Connexion()).seConnecter(); c.setAutoCommit(false); } // Fermer la connection après avoir annulé d'éventuelles // modifications via rollback dans la méthode tearDown /** * Ménage à la fin d'un test. */ public void tearDown() throws Exception { c.rollback(); c.close(); } Le code de connexion étant mis en commun pour tous les tests, le test de connexion devient une méthode vide. ===== Interrogation via JDBC ===== Afin d'interroger la base, créer un objet de type ''java.sql.Statement'', puis utiliser la méthode ''executeQuery(String sqlquery)'' afin de récupérer un objet de type ''java.sql.ResultSet''. On pourra par exemple utiliser la requête SQL suivante: SELECT titre FROM partie p WHERE 1 < (SELECT count(*) FROM partie p2 WHERE p2.inclus_dans = p.id) On peut ensuite parcourir le ''ResultSet'' ainsi obtenu grâce à une boucle ''while'' utilisant la méthode ''next()''. A chaque tour de boucle, on peut récupérer les valeur des différentes colonnes grâce à la méthode ''getXXX(col)'' où ''XXX'' est un type et ''col'' indique la colonne à considérer dans le résultat, soit sous forme d'un entier correspondant à son numéro, soit sous la forme d'un ''String'' correspondant au nom de la colonne. Cette page permet de savoir quelle méthode peut être utilisée en fonction du type SQL de la colonne du résultat que l'on souhaite regarder. L'exemple suivant montre comment afficher le tire de toutes les parties ayant au moins deux sous-parties. // Creation de l'objet Statement Statement stat = connect.createStatement(); // Obtention du résultat de la requête dans un ResultSet String requete = "SELECT titre "+ "FROM partie p "+ "WHERE 1 < (SELECT count(*) "+ "FROM partie p2 "+ "WHERE p2.inclus_dans = p.id)"; ResultSet result = stat.executeQuery(requete); // Parcours du résultat et affichage while (result.next()) { System.out.println(result.getString("titre")); System.out.println("----------------------------"); System.out.println(); } -> S'inspirer du code précédent pour créer une classe ''epul.bdav.LivreDAO'' contenant une méthode qui renvoie une ''java.util.List'' contenant les identifiants des parties ayant au moins deux sous-parties. La signature de la méthode sera la suivante: public java.util.List partiesComposees() throws java.sql.SQLException La ''Connection'' JDBC sera un champ de la classe. On ajoutera aussi une méthode: public java.sql.Connection getConnection(); -> Ajouter une méthode de test pour ''partiesComposees'' dans une classe ''epul.bdav.LivreDAOTest'' construite sur le modèle de TestJDBC. Il est également possible d'exécuter une mise à jour grâce à la méthode ''executeUpdate(requete)'' qui renvoie le nombre de lignes mises à jour, ou encore d'appeler une procédure stockée, via la méthode méthode ''execute(requeteGenerique)''. -> Utiliser la méthode ''executeUpdate(requete)'' pour insérer une nouvelle partie en utilisant un ''INSERT'' dans un nouveau test de la classe TestJDBC et vérifier que la mise à jour a bien eu lieu via une requête SELECT dans ce même test. Attention à une pas faire de commit qui pourrait causer des problèmes lors d'une deuxième exécution du test (risque de conflit sur la clé primaire). ===== Déclencheurs ===== On donne ici la syntaxe générale des déclencheurs PostgreSQL ([[http://docs.postgresql.fr/9.0/plpgsql-trigger.html|doc ici]], et {{:enseignement:fc:fc-05-pl-sql.pdf|transparents PL/SQL}} illustrés sur Oracle, utiles pour le principe des déclencheurs): CREATE OR REPLACE FUNCTION mon_trigger() RETURNS trigger AS $mon_trigger$ DECLARE /* declarations */ BEGIN /* instructions*/ END; $mon_trigger$ LANGUAGE plpgsql; CREATE TRIGGER mon_trigger BEFORE INSERT OR UPDATE ON ma_table FOR EACH ROW EXECUTE PROCEDURE mon_trigger(); ''BEFORE'' peut être remplacé par ''AFTER'' pour exécuter le déclencheur après la mise à jour. ''INSERT'' peut être remplacé par ''UPDATE'' ou ''DELETE''. Pour empêcher la mise à jour, on peut produire une erreur: RAISE EXCEPTION 'Pas d''insertion avec un parent sans titre'; ==== Titre de la partie englobante ==== -> Créer un déclencheur qui se déclenche lors de l’insertion d’une partie et qui vérifie que si la partie insérée est incluse dans une autre partie, cette dernière possède un titre. ==== Pas de partie autonome sans titre ==== -> Créer un déclencheur qui vérifie que si une partie n'a pas de titre, elle incluse dans une autre partie. ==== Livre du parent ==== -> Modifier le premier déclencheur afin de vérifier que le livre du parent est cohérent avec celui de l'enfant. ==== Test ==== -> Ecrire deux méthodes pour tester vos déclencheurs dans la classe TestJDBC. ===== Requêtes préparées ===== Il existe également des ''Statement'' un peu particulier: les ''PreparedStatement''. Il s'agit de requêtes paramétrées qui s'utilisent de la manière suivante: certaines valeurs de la requête SQL sont remplacées par des points d'interrogation (les paramètres de la requête) au moment de la création du ''PreparedStatement''. On peut ensuite donner une valeur aux paramètres de la requête grâce aux méthodes ''setXXX(param,valeur)'', où ''param'' indique le numéro du paramètre (le premier a le numéro 1), ''XXX'' correspond au type de donnée et valeur à la valeur du paramètre. La requête peut alors être exécutée via les méthodes ''executeQuery()'' (ou ''executeUpdate()'' s'il s'agit d'une mise à jour). Un ''PreparedStatement'' s'obtient par la méthode ''prepareStatement(String sql)'' où ''sql'' est la requête SQL paramétrée. Voici un exemple d'utilisation: // Creation de la requête paramétrée PreparedStatement pstat = connect.prepareStatement("SELECT titre FROM partie where id < ?"); // On fixe une première valeur pour l'argument pstat.setInt(1, 2); // On obtient le résultat result = pstat.executeQuery(); // On l'affiche while (result.next()) { System.out.println(result.getString("titre")); System.out.println("---------------"); } System.out.println("==="); // On fixe une deuxième valeur pour l'argument pstat.setInt(1, 5); // On obtient le 2eme résultat result = pstat.executeQuery(); // On l'affiche while (result.next()) { System.out.println(result.getString("titre")); System.out.println("---------------"); } -> Modifier vos tests pour utiliser des requêtes paramétrées en lieu et place des requêtes simples. ===== Numérotation automatisée ===== * [[http://docs.postgresqlfr.org/9.0/functions-sequence.html|Doc séquences]] Créer une séquence ''LIVRE_SEQ'' et lui donner 9 comme valeur courante au départ: CREATE SEQUENCE LIVRE_SEQ START WITH 9; La fonction ''nextval('ma_sequence')'' permet d'incrémenter la séquence et de renvoyer sa valeur. La fonction ''currval('ma_sequence')'' permet d'obtenir la dernière valeur renvoyée par ''nextval'' dans la session courante. Attention, cette dernière nécessite d'avoir appelé ''nextval'' au moins une fois dans la session auparavant. La fonction ''setval('ma_sequence', val)'' permet de changer le numéro de la séquence en une valeur arbitraire //val//. Écrire un déclencheur qui, lors d'une insertion dans la table partie: * Si l'identifiant du tuple inséré est NULL, utilise LIVRE_SEQ pour en générer un; * sinon change éventuellement la valeur de livre_seq pour éviter un conflit de clé ultérieur lors d'une insertion avec un identifiant NULL. Pour modfifier la valeur de la séquence avec ''setval'' en PL/pgSQL, il faut utiliser l'instruction ''[[http://docs.postgresqlfr.org/9.0/plpgsql-statements.html|PERFORM]]''. ===== Création de parties ===== On souhaite s'assurer que l'ajout d'une partie ne se fasse pas sans que cette partie n'aie un auteur. Pour cela, on va créer un procédure stockée à partir du squelette suivant: CREATE OR REPLACE FUNCTION ajoute_partie(p_livre IN INTEGER, p_inclus_dans IN INTEGER, p_titre IN character varying(100), p_contenu IN character varying(1000), p_auteur IN INTEGER) RETURNS INTEGER AS $ajoute_partie$ DECLARE -- DECLARATIONS BEGIN -- CORPS DE LA PROCEDURE RETURN 0; -- A changer END; $ajoute_partie$ LANGUAGE plpgsql; Remarque: en PostgreSQL, il est possible d'utiliser un SELECT sans FROM, ce qui revient en Oracle à faire un SELECT sur la vue DUAL. ==== Interfaçage Java ==== -> Créer une méthode dans la classe LivreDAO utilisant la fonction PL/pgSQL précédente pour ajouter une partie à un livre. On utilisera une requête préparée, que l'on mettra stockera dans un champ du DAO. public int ajoute_partie(int livre, int inclus_dans, String titre, String contenu, int premier_auteur) throws SQLException -> Ecrire un/des tests pour cette méthode dans la classe LivreDAOTest.