TP 3.03 — JDBC avancé

Objectif du TP : mettre en œuvre les fonctionnalités avancées de JDBC vues au chapitre 4 — requêtes pré-compilées, traitements par lots (batchs) et méta-base — au travers de quatre programmes : un moteur de recherche d'abonné, une commande d'affichage générique, une course de vitesse entre quatre méthodes d'insertion, et un mini-psql écrit en Java.

Avant de commencer

• avoir lu les chapitres Ch. 3 — JDBC et Ch. 4 — JDBC évolué ;
• avoir le pilote JDBC dans le classpath à l'exécution : java -cp postgresql-42.7.4.jar:. MonProgramme (voir le point 7 du chapitre 3) ;
• disposer d'une table Abonne. Si vous n'en avez pas, voici une version minimale pour le TP — adaptez-la à votre schéma, les programmes lisent les colonnes par leur nom :

create table Abonne (nom varchar(20), prenom varchar(20), age int);
insert into Abonne values ('Durand','paul',30), ('Dupont','jean',50),
                          ('Martin','luc',40), ('D''Artagnan','charles',45);

1. Rechercher.java — la requête pré-compilée à l'œuvre

L'énoncé. Saisir le nom d'un abonné (avec Scanner) et afficher les données de cette personne si elle existe dans la table Abonne, et « Utilisateur inconnu » sinon. On utilise une requête pré-compilée PreparedStatement à qui on passe le nom en paramètre.

Le principe : le ? est le paramètre, setString(1, nom) le remplit, et la requête est jouée telle quelle — les apostrophes dans le nom ne cassent plus rien. C'est la situation vue au chapitre 3 (concaténation) que le PreparedStatement règle définitivement.

import java.sql.*;
import java.util.Scanner;

public class Rechercher {
    public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.print("Nom de l'abonné recherché : ");
        String nom = sc.nextLine();

        String url = "jdbc:postgresql://localhost:5432/votrebdd";
        Connection con = DriverManager.getConnection(url, "votrelogin", "votremdp");

        // requête pré-compilée : le nom recherché est un paramètre
        PreparedStatement ps = con.prepareStatement(
                "select prenom, age from abonne where nom = ?");
        ps.setString(1, nom);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("Trouvé : " + nom + " " + rs.getString("prenom")
                    + ", âge " + rs.getInt("age"));
        } else {
            System.out.println("Utilisateur inconnu");
        }
        con.close();
    }
}

Nos trois tests réels :

$ java Rechercher
Nom de l'abonné recherché : Durand
Trouvé : Durand paul, âge 30

$ java Rechercher
Nom de l'abonné recherché : Zorglub
Utilisateur inconnu

$ java Rechercher
Nom de l'abonné recherché : D'Artagnan
Trouvé : D'Artagnan charles, âge 45

Le troisième test est le plus instructif : avec la concaténation du chapitre 3 ("… where nom = '" + nom + "'"), la requête serait devenue … where nom = 'D'Artagnan' — une erreur de syntaxe SQL. Avec le paramètre ?, le pilote transmet le nom tel quel : plus d'apostrophe qui casse la requête.

2. Lister.java — un afficheur générique grâce aux métadonnées

L'énoncé. Afficher toutes les lignes d'une table dont le nom est passé en paramètre : java Lister abonne, java Lister comptes… Les pistes du sujet :

1. une requête select construite avec le premier argument ;
2. un select * pour tout récupérer ;
3. getMetaData() sur le ResultSet, puis getColumnCount() pour le nombre de colonnes ;
4. afficher les noms des colonnes ;
5. parcourir le ResultSet et convertir chaque valeur en chaîne.

import java.sql.*;

public class Lister {
    public static void main(String[] args) throws Exception {
        if (args.length < 1) {
            System.out.println("Usage : java Lister nomDeTable");
            return;
        }
        String table = args[0];

        String url = "jdbc:postgresql://localhost:5432/votrebdd";
        Connection con = DriverManager.getConnection(url, "votrelogin", "votremdp");

        // (1) la requête utilise le premier argument du programme
        Statement st = con.createStatement();
        String query = "select * from " + table;
        ResultSet rs = st.executeQuery(query);

        // (2) combien de colonnes renvoie ce select ?
        ResultSetMetaData rsmd = rs.getMetaData();
        int nbColonnes = rsmd.getColumnCount();

        // (3) les noms des colonnes, en en-tête
        for (int i = 1; i <= nbColonnes; i++) {
            System.out.print(rsmd.getColumnName(i));
            System.out.print(i < nbColonnes ? " | " : "\n");
        }

        // (4) chaque ligne : chaque valeur de colonne sous forme de chaîne
        while (rs.next()) {
            for (int i = 1; i <= nbColonnes; i++) {
                System.out.print(rs.getString(i));
                System.out.print(i < nbColonnes ? " | " : "\n");
            }
        }
        con.close();
    }
}

Nos tests réels :

$ java Lister abonne
nom | prenom | age
Durand | paul | 30
Dupont | jean | 50
Martin | luc | 40
D'Artagnan | charles | 45

$ java Lister comptes
pers | solde
Dupont | 0
Durand | 0

Trois remarques :

• getString(i) fonctionne même pour une colonne numérique (solde) : JDBC convertit la valeur en chaîne. C'est ce qui rend l'afficheur générique — il n'a pas besoin de connaître le type des colonnes ;
• les index vont de 1 à getColumnCount(), comme pour les paramètres des requêtes pré-compilées ;
• le nom de table ne peut pas être un paramètre ? (ce n'est pas une valeur) : ici on concatène… mais attention, on ne le fera jamais avec des données saisies par un utilisateur.

3. Speed1 → Speed4 — la course à l'insertion

L'énoncé. On repart de l'ancien Insert.java (TP 3.01). Objectif : insérer 10 000 clients dans la table client de quatre façons différentes et mesurer à chaque fois avec System.currentTimeMillis() :

1. Speed1 — Statement classique, un executeUpdate par client ;
2. Speed2 — PreparedStatement : la requête n'est créée qu'une fois, seuls les paramètres changent ;
3. Speed3 — Statement + addBatch() / executeBatch() : tout part en un seul envoi ;
4. Speed4 — PreparedStatement + batch : les deux à la fois.

Les quatre programmes sont identiques sauf au cœur :

Statement st = con.createStatement();
st.executeUpdate("truncate table client");
long t0 = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
    st.executeUpdate("insert into client values(" + i + ",'nom" + i
            + "','prenom" + i + "'," + (i % 100) + ")");
}
long t1 = System.currentTimeMillis();

PreparedStatement ps = con.prepareStatement("insert into client values(?,?,?,?)");
long t0 = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
    ps.setInt(1, i);
    ps.setString(2, "nom" + i);
    ps.setString(3, "prenom" + i);
    ps.setInt(4, i % 100);
    ps.executeUpdate();
}
long t1 = System.currentTimeMillis();

long t0 = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
    st.addBatch("insert into client values(" + i + ",'nom" + i
            + "','prenom" + i + "'," + (i % 100) + ")");
}
int[] compteurs = st.executeBatch();   // tout part en une seule communication
long t1 = System.currentTimeMillis();

PreparedStatement ps = con.prepareStatement("insert into client values(?,?,?,?)");
long t0 = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
    // … les quatre setXXX …
    ps.addBatch();
}
int[] compteurs = ps.executeBatch();   // un seul aller-retour, 10 000 ordres
long t1 = System.currentTimeMillis();

Les résultats mesurés (10 000 clients, PostgreSQL local, sur notre machine) :

Quels sont les rapports de temps ? Environ ×7,6 entre Speed1 et Speed4 (×6,9 entre Speed2 et Speed4). L'explication tient en un mot : les allers-retours.

• Speed1 et Speed2 envoient 10 000 requêtes au SGBD, qui 10 000 transactions (autocommit) : chaque aller-retour se paie, même sur localhost ;
• Speed3 et Speed4 envoient 10 000 ordres dans la même communication : le batch amortit tout, et en plus le PreparedStatement évite de réanalyser la requête 10 000 fois ;
• Speed2 (pré-compilée) ne gagne presque rien sur Speed1 ici — l'essentiel du coût est le nombre d'échanges, pas l'analyse de la requête. C'est le batch qui change tout. À noter : le TP 3.04 reprend Speed2 pour ajouter encore un levier : la transaction (un seul commit pour les 10 000 inserts).

4. MonPSQL.java — un client de base de données générique

L'énoncé (pour aller plus loin) : un programme qui lit une ligne au clavier et

• s'arrête sur \q ;
• exécute toute ligne qui n'est pas une commande (\d, \i) comme une requête SQL — avec un affichage correct pour les select (le sujet suppose les requêtes sur une seule ligne) ;
• \d affiche les tables disponibles, via DatabaseMetaData et sa méthode getTables ;
• \i fichier exécute le fichier de commandes dont le nom suit.

Les deux briques clés. D'abord le catalogue — c'est la même call que dans le cours 4, mais côté Java :

DatabaseMetaData dbmd = con.getMetaData();
ResultSet rs = dbmd.getTables(con.getCatalog(), "public", null,
        new String[] { "TABLE", "VIEW" });
// puis, pour chaque ligne : rs.getString("TABLE_NAME")

Ensuite l'exécution d'une requête « inconnue » à l'avance : Statement.execute() renvoie true si le résultat est un ResultSet (un select), false sinon (on récupère alors le nombre de lignes touchées) :

boolean aUnResultat = st.execute(requete);
if (aUnResultat) {
    ResultSet rs = st.getResultSet();   // un select : on parcourt et on affiche
    // …
} else {
    System.out.println(st.getUpdateCount() + " ligne(s) affectée(s)");
}

import java.sql.*;
import java.io.*;
import java.util.Scanner;

public class MonPSQL {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/votrebdd";
        Connection con = DriverManager.getConnection(url, "votrelogin", "votremdp");

        Scanner sc = new Scanner(System.in);
        while (true) {
            System.out.print("monpsql> ");
            System.out.flush();
            if (!sc.hasNextLine()) break;
            String ligne = sc.nextLine().trim();
            if (ligne.isEmpty()) continue;
            if (ligne.startsWith("\\q")) break;            // quitter
            if (ligne.startsWith("\\d")) {                 // les tables disponibles
                afficherTables(con);
                continue;
            }
            if (ligne.startsWith("\\i")) {                 // exécuter un fichier
                executerFichier(con, ligne.substring(2).trim());
                continue;
            }
            executer(con, ligne);                          // sinon : une requête
        }
        con.close();
    }

    private static void afficherTables(Connection con) throws SQLException {
        DatabaseMetaData dbmd = con.getMetaData();
        ResultSet rs = dbmd.getTables(con.getCatalog(), "public", null,
                new String[] { "TABLE", "VIEW" });
        while (rs.next()) {
            System.out.println("  " + rs.getString("TABLE_NAME"));
        }
    }

    private static void executer(Connection con, String requete) {
        try (Statement st = con.createStatement()) {
            boolean aUnResultat = st.execute(requete);
            if (aUnResultat) {   // c'était un select : on affiche joliment
                try (ResultSet rs = st.getResultSet()) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int nbColonnes = rsmd.getColumnCount();
                    StringBuilder entete = new StringBuilder();
                    for (int i = 1; i <= nbColonnes; i++) {
                        entete.append(rsmd.getColumnName(i));
                        if (i < nbColonnes) entete.append(" | ");
                    }
                    System.out.println("  " + entete);
                    while (rs.next()) {
                        StringBuilder l = new StringBuilder();
                        for (int i = 1; i <= nbColonnes; i++) {
                            l.append(rs.getString(i));
                            if (i < nbColonnes) l.append(" | ");
                        }
                        System.out.println("  " + l);
                    }
                }
            } else {
                System.out.println("  " + st.getUpdateCount() + " ligne(s) affectée(s)");
            }
        } catch (SQLException e) {
            System.out.println("  ERREUR : " + e.getMessage());
        }
    }

    private static void executerFichier(Connection con, String nomFichier) {
        try (BufferedReader br = new BufferedReader(new FileReader(nomFichier))) {
            String ligne;
            while ((ligne = br.readLine()) != null) {
                ligne = ligne.trim();
                if (!ligne.isEmpty() && !ligne.startsWith("--")) {
                    System.out.println("  > " + ligne);
                    executer(con, ligne);
                }
            }
        } catch (IOException e) {
            System.out.println("  Impossible de lire " + nomFichier + " : " + e.getMessage());
        }
    }
}

Notre session réelle (les \d, un select, un insert, puis un script) :

$ java MonPSQL
monpsql> \d
  abonne
  client
  comptes
  produits
monpsql> select nom, prenom, age from abonne;
  nom | prenom | age
  Durand | paul | 30
  Dupont | jean | 50
  Martin | luc | 40
  D'Artagnan | charles | 45
monpsql> insert into abonne values ('Testeur','alice',20);
  1 ligne(s) affectée(s)
monpsql> \i script.sql
  > insert into abonne values ('Script','bob',33)
  1 ligne(s) affectée(s)
  > insert into abonne values ('Script2','carol',44)
  1 ligne(s) affectée(s)
monpsql> \q

A retenir

• PreparedStatement : le paramètre ? + setString(1, nom) règle les apostrophes et pré-compile la requête — c'est le réflexe par défaut dès qu'une valeur vient de l'extérieur ;
• ResultSetMetaData (getColumnCount, getColumnName, getString(i)) permet d'écrire des programmes génériques, qui s'adaptent à n'importe quelle table ;
• le batch (addBatch / executeBatch) envoie tout en une seule communication : dans nos mesures, le temps est divisé par plus de 7 par rapport aux insertions une par une ;
• DatabaseMetaData.getTables interroge le catalogue : c'est la base de tous les clients SQL génériques (comme MonPSQL — ou psql !) ;
• et toujours la règle du chapitre 3 : compiler n'a pas besoin du pilote, exécuter si (java -cp postgresql-42.7.4.jar:. MonProgramme).