Introduction à l'écriture de procédures stockées en Java sous PostgreSQL

Lorsque l'on conçoit une application qui utilise une base de données, on peut faire en sorte que les traitements métier soient effectués en dehors du système de gestion de bases de données relationnelles (dans le serveur d'application par exemple) ou directement dans ce dernier. Dans la première approche, le SGBDR (système de gestion de bases de données relationnelles) se contente d'exécuter des requêtes et de renvoyer les résultats. Dans la deuxième approche, qualifiée de développement en base de données épaisse, le SGBDR embarque des procédures stockées (PL/SQL, PL/pgSQL, etc.) qui implémentent les traitements métier et continue bien sûr à exécuter des requêtes.
La première approche est la plus populaire. Elle a pour avantage de mettre à disposition des développeurs une multitude d'outils favorisant la productivité et la qualité : EDI sophistiqués (Eclipse, Visual Studio), frameworks de tests automatisés (JUnit, NUnit), etc.
L' approche de développement en base de données épaisse ( http://img1.lemondeinformatique.fr/fichiers/telechargement/plaidoyer-de-frederic-brouard-sur-le-concept-de-bases-de-donnees-epaisses.pdf ) a pour avantage principal d'offrir des performances de premier plan. En effet, les données sont traitées directement depuis le SGBDR et n'ont pas à faire d'allers-retours inutiles entre ce dernier et le serveur d'application.
Sommes-nous condamnés à privilégier la productivité au détriment de la performance ou faire l'inverse ? Pas tout à fait. Si des langages de procédures stockées comme PL/SQL et PL/psgSQL montrent rapidement leurs limites lorsque l'on implémente des traitements métiers sophistiqués, il est possible de se passer de ces langages pour écrire des procédures stockées. On peut par exemple écrire des procédures stockées en Java appelées PL/Java... Ceci peut sembler anodin, voire gadget, mais ça implique de nombreuses choses :

• que vous puissiez désormais utiliser votre EDI favori pour écrire vos procédures et donc ainsi conserver un excellent niveau de productivité ;
• de factoriser du code entre le serveur d'application et le SGBDR ;
• d'utiliser la multitude de librairies tierces disponibles en Java (apache commons, gava, etc.) ;
• exploiter la puissance d'un langage de programmation orienté objet pour écrire vos procédures stockées (héritage, design patterns, etc.).

Et la liste est non exhaustive... Mais quel est donc l'inconvénient d'écrire des procédures stockées en Java ? Les requêtes ne sont pas vérifiées lors de la compilation de la procédure. Si dans une procédure PL/SQL par exemple, vous écrivez une requête, le compilateur s'assurera que celle-ci est valide, en d'autres termes qu'elle :

• soit correcte d'un point de vue syntaxique ;
• ne fasse pas référence à des tables inexistantes ;
• ne fasse pas référence à des colonnes inexistantes.

Ces vérifications ne sont pas effectuées lors de la compilation des procédures stockées écrites en Java, et ce pour la simple et bonne raison, que les requêtes sont écrites dans des chaînes de caractères.

package fr.procedures;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SampleProc {
    public static void  method(Long id) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        
        String query = "Select * from TOTO where id = ?";
        
        PreparedStatement  stmt = conn.prepareStatement(query);
        stmt.setLong(1, id);
        
        ResultSet rs = stmt.executeQuery(query);
        //traitement des resultset...
    }
}

On peut donc supposer qu'en écrivant une procédure stockée en Java, on risque de ne repérer qu'à l'exécution des erreurs qu'on aurait pu repérer à la compilation si l'on avait écrit la même procédure en PL/SQL. Il y a donc un risque pour la qualité de l'application.
Ceci dit, si on écrit une procédure en Java, on peut utiliser n'importe quelle librairie Java pour écrire la procédure, donc rien ne nous empêche d'utiliser jOOQ ( http://www.jooq.org/ ). Qu'est-ce que jOOQ ? C'est une librairie permettant d'écrire des requêtes SQL fortement typées en Java, un peu comme les type-safe criteria que l'on peut trouver dans JPA 2 ( https://ronnyguillaume.developpez.com/presentation-d-outils-a-utiliser-avec-hibernate/#LII ). Écrire des requêtes avec jOOQ présente plusieurs avantages :

• le compilateur vérifie que vos requêtes sont correctes ;
• vous pouvez utiliser l'autocomplétion pour écrire vos requêtes ;
• le compilateur vérifie que les types des valeurs retournées correspondent bien à ceux que vous attendez ;
• le compilateur vérifie que dans les clauses « WHERE », vous comparez des objets de mêmes types, afin d'éviter de comparer des blobs avec des chaînes de caractères par exemple ;

etc.

Connection conn = DriverManager.getConnection("jdbc:default:connection");
PostgresFactory create = new PostgresFactory(conn);
create.select(TERMACCOUNT.AMOUNT).from(TERMACCOUNT).            where(TERMACCOUNT.USER_ID.equal(id));

SELECT AMOUT FROM TERMACCOUNT WHERE USER_ID= id

Comme dans le cas des type-safe criteria, il va falloir générer des classes via un utilitaire pour pouvoir utiliser jOOQ. Plus précisément, l'utilitaire de jOOQ va scanner la base de données de votre application et va générer les classes correspondantes aux tables présentes. Ce sont justement ces classes Java que vous utiliserez dans vos requêtes, et c'est grâce à celles-ci que vous bénéficierez des avantages cités plus haut.

Quelle différence avec les type-safe criteria ? Les criteria ont été inventés pour que les développeurs puissent écrire des requêtes totalement indépendantes de la base de données utilisée. Aussi, les criteria se basent uniquement sur la structure des classes sur lesquelles les requêtes sont effectuées et non sur les tables correspondantes. À tel point qu'on pourrait imaginer que l'ORM (Hibernate, EclipseLink, OpenJPA ou Datanucleus) utilise des requêtes avec des criteria pour interroger une base de données noSQL.

Dans jOOQ, l'approche est diamétralement opposée : on se base uniquement sur la structure des tables. En écrivant des requêtes avec cette librairie, on est plus proche de la base de données. D'ailleurs, en lisant les requêtes écrites avec jOOQ, il n'est pas difficile de deviner la requête SQL qui va être générée. Et les classes ? jOOQ s'en fiche, car ce n'est pas vraiment un ORM. Notez toutefois que jOOQ peut être utilisé depuis le serveur d'application et qu'il peut dans certains cas se substituer totalement à un ORM (consultez la documentation officielle pour plus d'informations).

Avec cette librairie, on peut donc écrire des procédures en PL/Java aussi sûres que des procédures en PL/SQL ou PL/pgSQL.
Les exemples suivants s'appliquent avec PostgreSQL, mais je ne doute pas qu'on puisse en faire autant avec d'autres SGBDR.

Pour installer PL/Java, il faut tout d'abord que vous ayez installé PostgreSQL sur votre machine. Dans notre cas, on fera l'hypothèse que vous possédez la version 9.1 en 64 bits. Il faut aussi que vous ayez une machine virtuelle Java (JVM) d'installée, on supposera que vous avez la version 1.7 en 64 bits. Référencez, si ce n'est pas déjà fait, le répertoire « bin » de votre JVM dans la variable d'environnement « PATH ». Référencez aussi le répertoire « bin\client » de votre JVM dans le « PATH ».
Allez sur le site de PL/Java ( http://pgfoundry.org/projects/pljava/ ), cliquez sur l'onglet « Files » ( http://pgfoundry.org/frs/?group_id=1000038 ) et téléchargez la version 1.4.3 (intitulée « pljava-x86_64-w64-mingw32-pg9.1-1.4.3.tar.gz » si vous êtes sur une version 64 bits de Windows). Décompressez l'archive de PL/Java dans le répertoire de votre choix (on supposera par la suite que vous l'avez décompressée dans le répertoire « D:\pljava ») .

Si vous voulez éviter quelques déconvenues, je vous conseille vivement de prendre une version de PL/Java adaptée à votre version de PostgreSQL (pg9.1 si vous êtes sous PostgreSQL 9.1, pg9.0 si vous êtes sur PostgreSQL 9.0).
Allez maintenant sur le github de PL/Java ( https://github.com/tada/pljava ), suivez la procédure d'installation correspondant à votre système d'exploitation ( https://github.com/tada/pljava/wiki/Installation-guide ). Si vous êtes sous Windows, vous devrez donc suivre cette procédure : https://github.com/tada/pljava/wiki/Windows-installaion . Si vous êtes sur un Unix ou un Linux, vous devrez suivre cette procédure https://github.com/tada/pljava/wiki/Installing-on-linux-%28or-other-*nix%29 .

Dans le cas où vous êtes sous Windows, vous devrez donc :

• éditer le fichier de configuration de PostgreSQL « postgresql.conf » présent dans le répertoire « data » de votre SGBDR (par exemple « D:\PostgreSQL » si vous avez installé PostgreSQL sur « D: ») de la façon suivante :

   

  Sélectionnez

  dynamic_library_path ='$libdir;D:\\pljava'
  custom_variable_classes = 'pljava'
  pljava.classpath='D:\\pljava\\pljava.jar'

• rajouter la jar du driver de PostgreSQL (dans notre cas: « postgresql-9.1-901-1.jdbc4.jar ») dans le répertoire « D:\pljava » (l'endroit où vous avez décompressé PL/Java) ;

• redémarrer PostgreSQL ;

• lancer un shell (invite de commande MS-DOS par exemple), vous rendre dans le répertoire où vous avez décompressé PL/Java et lancer la commande suivante:

java -cp «*» org.postgresql.pljava.deploy.Deployer -install -user postgres -password postgres -database pljavatest -host localhost -port 5432

jOOQ étant une librairie, pour l'utiliser dans votre application, il faudra l'inclure dans le classpath de votre projet. Nous faisons l'hypothèse dans cet article que vous utilisez Maven 3. Dans notre cas, nous l'intégrons dans les dépendances Maven du projet. En outre, nous utiliserons la version « 2.6.2 » de jOOQ:

<dependencies>

        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.1-901-1.jdbc4</version>
        </dependency>
        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>pljava</artifactId>
            <version>1.4.3</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <!-- artefacts are jooq, jooq-meta, jooq-codegen -->
            <artifactId>jooq</artifactId>
            <version>2.6.2</version>
        </dependency>

    </dependencies>

Pour générer les classes dont on a besoin pour utiliser jOOQ, nous allons (bien qu'il soit possible de faire autrement, cf documentation officielle de jOOQ) utiliser un plugin Maven:

<plugin>

                <!-- Specify the maven code generator plugin -->
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>2.6.2</version>

                <!-- The plugin should hook into the generate goal -->
                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>9.1-901-1.jdbc4</version>
                    </dependency>
                </dependencies>


                <!-- Specify the plugin configuration -->
                <configuration>

                    <!-- JDBC connection parameters -->
                    <jdbc>
                        <driver>org.postgresql.Driver</driver>
                        <url>jdbc:postgresql://localhost:5432/pljavatest</url>
                        <user>postgres</user>
                        <password>postgres</password>
                    </jdbc>

                    <!-- Generator parameters -->
                    <generator>
                        <name>org.jooq.util.DefaultGenerator</name>
                        <database>
                            <name>org.jooq.util.postgres.PostgresDatabase</name>
                            <includes>.*</includes>
                            <excludes></excludes>
                            <inputSchema>public</inputSchema>
                        </database>
                        <target>
                            <packageName>fr.jooq.util</packageName>
                            <directory>generated-src</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>

Nous allons prendre un cas volontairement simpliste, celui d'une application bancaire où nous avons des utilisateurs et des comptes bancaires. Naturellement un utilisateur peut avoir plusieurs comptes bancaires, mais un compte bancaire ne peut être rattaché qu'à un seul utilisateur. Il existe plusieurs types de comptes bancaires : comptes courants, livrets d'épargne et comptes à terme. Le compte courant sert dans la vie de tous les jours, c'est sur ce compte qu'on reçoit le salaire. Le livret d'épargne sert à mettre un peu d'argent de côté, sa durée de vie est celle du titulaire du compte, mais on peut le clôturer à tout moment. Le compte à terme est aussi un compte de type épargne, sa durée de vie n'est que de quelques années, et le taux d'intérêt est plus élevé que sur un livret d'épargne. Pour avoir un livret d'épargne ou un compte à terme, il faut impérativement posséder un compte courant dans la même banque. Nous allons inventer deux règles de gestion :

• lorsque l'on clôture un livret d'épargne, on récupère le solde du compte et on le crédite sur le compte courant du même utilisateur ;
• au bout de trois ans, le compte à terme doit être clos. On récupère le solde du compte et on le crédite sur le compte courant du même utilisateur.

Voici le code SQL permettant de générer notre base de données :

CREATE TABLE TERMACCOUNT (ID BIGINT NOT NULL, AMOUNT FLOAT, USER_ID BIGINT, PRIMARY KEY (ID)) ;
CREATE TABLE PASSBOOKACCOUNT (ID BIGINT NOT NULL, AMOUNT FLOAT, USER_ID BIGINT, PRIMARY KEY (ID)) ;
CREATE TABLE CURRENTACCOUNT (ID BIGINT NOT NULL, AMOUNT FLOAT, USER_ID BIGINT, PRIMARY KEY (ID)) ;
CREATE TABLE public.USER (ID BIGINT NOT NULL, FIRSTNAME VARCHAR(255), LASTNAME VARCHAR(255), VERSION BIGINT, PRIMARY KEY (ID)) ;
ALTER TABLE TERMACCOUNT ADD CONSTRAINT FK_TERMACCOUNT_USER_ID FOREIGN KEY (USER_ID) REFERENCES USER (ID) ;
ALTER TABLE PASSBOOKACCOUNT ADD CONSTRAINT FK_PASSBOOKACCOUNT_USER_ID FOREIGN KEY (USER_ID) REFERENCES USER (ID) ;
ALTER TABLE CURRENTACCOUNT ADD CONSTRAINT FK_CURRENTACCOUNT_USER_ID FOREIGN KEY (USER_ID) REFERENCES USER (ID) ;
CREATE SEQUENCE User_seq INCREMENT BY 50 START WITH 50 ;
CREATE SEQUENCE PassbookAccount_seq INCREMENT BY 50 START WITH 50 ;
CREATE SEQUENCE TermAccount_seq INCREMENT BY 50 START WITH 50 ;
CREATE SEQUENCE CurrentAccount_seq INCREMENT BY 50 START WITH 50 ;

III-A. Écriture d'un trigger (déclencheur)▲

package fr.triggers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import org.jooq.Record;
import org.jooq.util.postgres.PostgresFactory;
import org.postgresql.pljava.TriggerData;

import fr.jooq.util.tables.Currentaccount;
import fr.jooq.util.tables.Passbookaccount;

public class PassbookAccountTrigger {

    public static void  beforeDelete(TriggerData td) {
        try{
        ResultSet rsOld = td.getOld();
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        PostgresFactory create = new PostgresFactory(conn);
        
        final Passbookaccount PASSBOOKACCOUNT=Passbookaccount.PASSBOOKACCOUNT;
        final Currentaccount CURRENTACCOUNT=Currentaccount.CURRENTACCOUNT;
        
        Long userId =rsOld.getLong(PASSBOOKACCOUNT.USER_ID.getName());
        Double passbookAccountAmount= rsOld.getDouble(PASSBOOKACCOUNT.AMOUNT.getName());

        Record currentAccountAmountInRecord= create.select(CURRENTACCOUNT.AMOUNT).
                from(CURRENTACCOUNT).where(CURRENTACCOUNT.USER_ID.
                        equal(userId)).fetchOne();
        
        Double sum = passbookAccountAmount + currentAccountAmountInRecord.getValue(CURRENTACCOUNT.AMOUNT);

        create.update(CURRENTACCOUNT).set(CURRENTACCOUNT.AMOUNT, sum)
        .where(CURRENTACCOUNT.USER_ID.equal(userId)).execute();
        }catch(Exception e){
            throw new RuntimeException(e);
        }
    }

}

CREATE FUNCTION before_delete_passbook_account()
  RETURNS trigger
  AS 'fr.triggers.PassbookAccountTrigger.beforeDelete'
  LANGUAGE java;

CREATE TRIGGER before_delete_passbook_account_trigger
  BEFORE DELETE ON passbookaccount
  FOR EACH ROW
  EXECUTE PROCEDURE before_delete_passbook_account ();

III-B. Écriture d'une procédure stockée▲

package fr.procedures;

import java.sql.Connection;
import java.sql.DriverManager;

import org.jooq.Record;
import org.jooq.util.postgres.PostgresFactory;

import fr.jooq.util.tables.Currentaccount;
import fr.jooq.util.tables.Termaccount;

public class TermAccountProc {
    
    public static void  close(long id) {
        try{
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        PostgresFactory create = new PostgresFactory(conn);
        final Termaccount TERMACCOUNT=Termaccount.TERMACCOUNT;
        final Currentaccount CURRENTACCOUNT=Currentaccount.CURRENTACCOUNT;
        
        Record termAccountAmountInRecord= create.select(TERMACCOUNT.AMOUNT).
                from(TERMACCOUNT).
                where(TERMACCOUNT.USER_ID.equal(id))
                .fetchOne();
        
        
        
        Double termAccountAmount= termAccountAmountInRecord.getValue(TERMACCOUNT.AMOUNT);
        Record currentAccountAmountInRecord= create.select(CURRENTACCOUNT.AMOUNT).
                from(CURRENTACCOUNT).
                where(CURRENTACCOUNT.USER_ID.equal(id))
                .fetchOne();
        Double sum = termAccountAmount + currentAccountAmountInRecord.getValue(CURRENTACCOUNT.AMOUNT);
        create.update(CURRENTACCOUNT).set(CURRENTACCOUNT.AMOUNT, sum)
        .where(CURRENTACCOUNT.USER_ID.equal(id)).execute();
        create.delete(TERMACCOUNT).where(TERMACCOUNT.USER_ID.equal(id)).execute();
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
}

CREATE OR REPLACE FUNCTION close_term_account(id int8)
  RETURNS void AS
'fr.procedures.TermAccountProc.close'
  LANGUAGE java ;

III-C. Intégration du code PL/Java dans la base de données▲

SELECT sqlj.install_jar('file:///d:/applicationbdd.jar', 'applicationbdd', false);

SELECT sqlj.install_jar('file:///d:/jooq.jar', 'jooq', false);

SELECT sqlj.set_classpath('public', 'applicationbdd:jooq');

III-D. Invocation des procédures stockées depuis le serveur d'application▲

CREATE OR REPLACE FUNCTION close_term_account(id bigint)
  RETURNS void AS
'fr.procedures.TermAccountProc.close'
  LANGUAGE java ;

CloseTermAccount closeTermAccount = new CloseTermAccount();
closeTermAccount.setId(1L);
closeTermAccount.execute();

III-E. Quelle approche de développement privilégier?▲

Désormais, on peut écrire des procédures stockées intégralement en Java. On y gagne en productivité et le framework jOOQ nous aide à éviter les erreurs dans l'écriture des requêtes.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>ApplicationBDD</groupId>
  <artifactId>ApplicationBDD</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <dependencies>

        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.1-901-1.jdbc4</version>
        </dependency>
        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>pljava</artifactId>
            <version>1.4.3</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <!-- artefacts are jooq, jooq-meta, jooq-codegen -->
            <artifactId>jooq</artifactId>
            <version>2.6.2</version>
        </dependency>

    </dependencies>
    <build>
        <defaultGoal>package</defaultGoal>
        <sourceDirectory>src</sourceDirectory>
        <resources>
            <resource>
                <directory>resources</directory>
            </resource>
        </resources>
        <plugins>
        <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-jar-plugin</artifactId>
         <version>2.4</version>
        <configuration>
          <archive>
            <manifest>
              <addClasspath>true</addClasspath>
            </manifest>
          </archive>
        </configuration>
      </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.7</source>
                    <target>1.7</target>
                    <generatedSourcesDirectory>generated-src</generatedSourcesDirectory>
                </configuration>
            </plugin>
            <plugin>

                <!-- Specify the maven code generator plugin -->
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>2.6.2</version>

                <!-- The plugin should hook into the generate goal -->
                <executions>
                    <execution>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>9.1-901-1.jdbc4</version>
                    </dependency>
                </dependencies>


                <!-- Specify the plugin configuration -->
                <configuration>

                    <!-- JDBC connection parameters -->
                    <jdbc>
                        <driver>org.postgresql.Driver</driver>
                        <url>jdbc:postgresql://localhost:5432/pljavatest</url>
                        <user>postgres</user>
                        <password>postgres</password>
                    </jdbc>

                    <!-- Generator parameters -->
                    <generator>
                        <name>org.jooq.util.DefaultGenerator</name>
                        <database>
                            <name>org.jooq.util.postgres.PostgresDatabase</name>
                            <includes>.*</includes>
                            <excludes></excludes>
                            <inputSchema>public</inputSchema>
                        </database>
                        <target>
                            <packageName>fr.jooq.util</packageName>
                            <directory>generated-src</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>
            
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-dependency-plugin</artifactId>
        <version>2.6</version>
        <executions>
          <execution>
            <id>copy-dependencies</id>
            <phase>package</phase>
            <goals>
              <goal>copy-dependencies</goal>
            </goals>
            <configuration>
              <outputDirectory>${project.build.directory}/lib</outputDirectory>
              <overWriteReleases>false</overWriteReleases>
              <overWriteSnapshots>false</overWriteSnapshots>
              <overWriteIfNewer>true</overWriteIfNewer>
            </configuration>
          </execution>
        </executions>
      </plugin>
        </plugins>
    </build>

</project>

Je tiens à remercier Keulkeul et Gueritarish pour leur relecture technique ainsi que ced pour sa relecture orthographique.