Année universitaire Département Matière Enseignant Intitulé TD/TP : Durée

2012-2013 Informatique Bases de données avancées Haytham Elghazel TP JDBC/PLSQL 4h Année 4A

Ce TP est à réaliser seul ou en binôme (trinômes interdits). Il est à rendre sur spiral (http://spiralconnect.univ-­‐lyon1.fr/spiral/spiral.html#/activities/goto_folder/1940969) pour le 01/12/2012, 23h30. Il faut rendre le programme (projet maven + script SQL commenté dans un fichier zip). Ne pas oublier de mentionner les deux étudiants du binôme dans le nom du fichier zip qui sera sous la forme : TP2_BDAV_Nom1_Prenom1_Nom2_Prenom2.zip. Nom1_Prenom1 est le nom et le prénom du premier membre du binôme, Nom2_Prenom2 est le nom et le prénom du second membre du binôme. Le non-­‐respect de ces consignes pourra être sanctionné dans la note de ce TP. On utilisera le schéma relationnel du forum de discussion mis en place au TP précédent auquel il faut rajouter la ligne suivante afin de préciser l’état d’un message qui pourra être Ouvert ou Clot (dans ce cas on ne pourra plus poster) : ALTER TABLE message ADD (etat varchar2(8) DEFAULT 'Ouvert' CHECK (etat IN ('Ouvert','Clot')))

Rappel du schéma du TP précédent :

-­‐-­‐ Schema CREATE TABLE membre(id integer PRIMARY KEY, nom varchar2(40), email varchar2(100)); CREATE TABLE salle(id integer PRIMARY KEY, moderateur integer REFERENCES membre(id), titre varchar2(100)); CREATE TABLE message(id integer PRIMARY KEY, auteur integer REFERENCES membre(id), salle integer REFERENCES salle(id), parent integer, date_envoi timestamp, titre varchar2(100), corps varchar2(1000)); ALTER TABLE message ADD constraint fk_parent FOREIGN KEY (parent) REFERENCES message(id);

-­‐-­‐ 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 salle VALUES(1,1,'Bonne humeur'); INSERT INTO salle VALUES(2,NULL,'Dessins animés');

INSERT INTO message VALUES(1,1,1,NULL,sysdate,'Toto à 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 message VALUES(2,3,2,NULL,sysdate,'Les plus beaux personnages','Ce sont les chats, évidement !'); INSERT INTO message VALUES(3,2,2,2,sysdate,'Re: Les plus beaux personnages','Non, ce sont les canaris !'); INSERT INTO message VALUES(4,3,2,3,sysdate,'Re: Les plus beaux personnages','Les chats !'); INSERT INTO message VALUES(5,2,2,4,sysdate,'Re: Les plus beaux personnages','Grand mère, à l''aide !!!'); INSERT INTO message VALUES(6,4,2,2,sysdate,'Re: Les plus beaux personnages','Moi, je dis que ce sont les lapins !');

commit;

Page 1 sur 5 BDAV – TP n°2

Projet Maven Maven est un utilitaire de compilation/exécution/test de projet Java qui sera utilisé dans le cadre de ce TP. Télécharger et décompresser le projet de base : http://www710.univ-­‐lyon1.fr/~elghazel/Cours-­‐2012-­‐ 2013/BD2-­‐Initiale/TP2/BDAV-­‐JDBC.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.

I. Configuration Driver Oracle Une dépendance maven n'est pas satisfaite. Télécharger http://www710.univ-­‐ lyon1.fr/~elghazel/Cours-­‐2012-­‐2013/BD2-­‐Initiale/TP2/ojdbc14.jar. Dans Netbeans, dans le projet, ouvrir Dependencies, faire un clic-­‐droit sur ojdbc14-­‐1.0-­‐RELEASE.jar → Installer manuellement un artefact et indiquer le fichier téléchargé. Question 1 : Dans un premier temps, vérifier que les tests se déroulent bien. Il faut bien faire attention de ne pas oublier de modifier les paramètres de connexion.

II. Premiers bouts de code JDBC Question 2 : Modifier la classe TestJDBC de la manière suivante: // Ajouter un champ c représentant la connexion à Oracle private Connection c; // Initialiser ce champ dans une méthode setUp /** * Initialisation des tests */ public void setUp() throws Exception { c = (new Connexion()).seConnecterOracle(); 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. Afin d'interroger la base, créer un objet de type java.sql.Statement, puis utiliser la méthode executeQuery(String sqlquery) pour récupérer un objet de type java.sql.ResultSet. On pourra par exemple utiliser la requête SQL suivante : Page 2 sur 5 BDAV – TP n°2

SELECT titre FROM message m1 WHERE 1 < (SELECT count(*) FROM message m2 WHERE m2.parent = m1.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. L'exemple suivant montre comment afficher le titre de tous les messages ayant au moins deux réponses.

// 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 message m1 "+ "WHERE 1 < (SELECT count(*) "+ "FROM message m2 "+ "WHERE m2.parent = m1.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(); }

Question 3 : S'inspirer du code précédent pour créer une classe polytech.bdav.FORUMDAO contenant une méthode MessagesavecdeuxReponses qui renvoie une java.util.List contenant les identifiants des messages ayant au moins deux réponses. La signature de la méthode sera la suivante:

public java.util.List MessagesavecdeuxReponses() throws java.sql.SQLException

La Connexion JDBC sera un champ de la classe. On ajoutera dans FourmDAO aussi une méthode :

public java.sql.Connection getConnection();

Question 4 : Ajouter une méthode de test testReponses pour MessagesavecdeuxReponses dans la classe 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. Question 5 : Ajouter une méthode insertMessage utilisant la méthode executeUpdate(requete) pour insérer un nouveau message qui n’est pas une réponse. Ajouter un nouveau test unitaire testInsertMessage dans 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).

III. 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 Page 3 sur 5 BDAV – TP n°2

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 message where id < ?"); // On fixe une première valeur pour l'argument pstat.setInt(1, 2); // On obtient le résultat ResultSet 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("---------------"); }

Question 6 : Proposer une modification de votre dernière méthode insertMessage d’insertion d’un message ainsi que de son test unitaire testinsertMessage pour utiliser des requêtes paramétrées à la place des requêtes simples.

IV. Séquences, procédures et fonctions PLSQL et interfaçage JAVA Les Séquences Question 7 : Les clés des tables sont des entiers, pour obtenir des entiers différents on utilise sous Oracle des séquences. Créer trois séquences pour générer la clé de la table membre, salle et message. Il faut ainsi créer ces séquences en initialisant chacune d’elle à la plus grande valeur dans sa table correspondante.

CREATE SEQUENCE seq1 START WITH nb1 increment by nb2;

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. Les Procédures stockées et les déclencheurs Question 8 : Créer une procédure pour poster un message qui sera le départ d'un nouveau fil de discussion. Créer une seconde procédure qui créée une réponse à un message précédent. La date d’insertion d’un message est donnée par la date du jour. L’id du message est quant à lui obtenu à Page 4 sur 5 BDAV – TP n°2

partir de la séquence du message. Pour obtenir cette date vous pouvez utiliser SELECT CURRENT_DATE FROM DUAL.
CREATE OR REPLACE PROCEDURE nouveau_message(id_salle IN INTEGER, auteur IN INTEGER, titre IN VARCHAR2, corps IN VARCHAR2) CREATE OR REPLACE PROCEDURE nouvelle_reponse(parent IN INTEGER, auteur IN INTEGER, titre IN VARCHAR2, corps IN VARCHAR2)

Question 9 : Ecrire deux méthodes dans ForumDAO pour l’appel de vos deux procédures ainsi que leurs tests respectifs dans la classe TestJDBC. (cf. CallableStatement) Question 10 : Créer une procédure pour clore un sujet (1 message). La procédure générera une erreur si l’utilisateur n’est pas modérateur dans la salle où se trouve le sujet qu’il souhaite clore.
CREATE OR REPLACE PROCEDURE clore_sujet(id_message IN INTEGER, id_utilisateur IN INTEGER)

Question 11 : Créer un déclencheur (trigger) pour empêcher de poster des réponses dans un sujet clôt. Question 12 : Créer une procédure pour clore un fil de discussion (l’ensemble des messages du fil). Cette procédure prendra en paramètre un message faisant parti du fil (pas forcément la racine du fil, mais le fil devra être clôt depuis sa racine). La procédure prendra également en paramètre l’identifiant de l’utilisateur souhaitant clore le thread. La procédure générera une erreur si l’utilisateur n’est pas modérateur dans la salle où se trouve le sujet qu’il souhaite clore. Utilisez la procédure permettant de clore un sujet de la question 8 ainsi que les requêtes hiérarchiques. Question 13 : Ecrire une méthode dans ForumDAO pour l’appel de cette procédure ainsi que son test unitaire dans la classe TestJDBC. Les fonctions Question 14 : On souhaite compter le nombre de fois où un membre intervient sur le forum. Pour cela créer un fonction PLSQL qui retourne ce nombre de messages postés pour un membre du forum. Créer une méthode calculNbActions dans la classe ForumDAO utilisant cette fonction PLSQL. On utilisera une requête préparée, que l'on mettra stockera dans un champ du DAO. Ecrire le test unitaire

AfficheNbActions pour cette méthode dans la classe TestJDBC.

Page 5 sur 5 BDAV – TP n°2