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 par mail

• API Java 6
• Documentation PL/pgSQL
• API Driver PostgreSQL

http://www.techonthenet.com/oracle/ Référence PL/SQL Oracle

Une dépendance maven n'est pas satisfaite. Télécharger 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é.

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.

On donne le schéma relationnel mis en place au 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); 

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é dans l'aide.

Télécharger et décompresser le projet de base: 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.

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: 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 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.

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<Integer> contenant les identifiants des parties ayant au moins deux sous-parties. La signature de la méthode sera la suivante:

public java.util.List<Integer> 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).

On donne ici la syntaxe générale des déclencheurs PostgreSQL (doc ici, et 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';

→ 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.

→ Créer un déclencheur qui vérifie que si une partie n'a pas de titre, elle incluse dans une autre partie.

→ Modifier le premier déclencheur afin de vérifier que le livre du parent est cohérent avec celui de l'enfant.

→ Ecrire deux méthodes pour tester vos déclencheurs dans la classe TestJDBC.

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.

• 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 PERFORM.

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.

→ 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.