À la leçon précédente, nous manipulions une liste de noms d’arbres — Pommier, Chêne — construite à la main en mémoire. Cette liste est évaporée dès que le programme se termine. Or la réutilisation du code, fil conducteur de tout le cours, passe souvent par des données qui survivent à l’exécution : celles d’un système de gestion de bases de données relationnelles (SGBDR). JDBC (Java Database Connectivity) est la couture standard entre les deux mondes : les objets Java d’un côté, les tables SQL de l’autre.
JDBC : API standard d’accès aux SGBDR depuis Java (java.sql, javax.sql)
Un driver par fournisseur de base de données, chargé à la volée
Cycle de la leçon : connexion → requêtes → résultat → objets, puis injection SQL, transactions, lot (batch), portabilité
Continuité : les noms d’arbres de la leçon 07 deviennent ici les arrêts d’un réseau
PostGIS et données géolocalisées : le TP
Le driver et la base de travail
Comme toute bibliothèque externe, le driver doit être au classpath. En gestion de dépendances Maven, une simple coordonnée suffit — le noyau Java, lui, n’a pas besoin d’enregistrement explicite du driver : depuis JDBC 4.0, DriverManager.getConnection associe l’URL au driver approprié automatiquement (les anciens Class.forName("org.postgresql.Driver") sont historiques).
Nous chargerons aussi le driver H2 pour la section portabilité : les deux drivers cohabitent sur le classpath du notebook.
%maven com.h2database:h2:2.3.232
Où tourne cette base ?
Le noyau Java ne parle à personne tout seul : il faut un serveur. Comme pour les TP conteneurs du cours, nous lançons une instance PostgreSQL locale au rendu, dans un conteneur joignable depuis le noyau Java par l’adresse dind (le réseau Docker de la machine d’exécution), port 5432 ; en local, cette cellule démarre et attend un conteneur Docker (détail au TP conteneurs) ; chez vous, remplacez dind par localhost — et attendez-vous à une base recréée à chaque exécution. La cellule suivante prépare la base et attend sa disponibilité ; son écriture (appels docker via ProcessBuilder, boucle de disponibilité) est de la plomberie d’environnement, sans intérêt pédagogique en soi, et donc masquée.
Base prête (tentative 3)
Première connexion : DriverManager et try-with-resources
Une connexion est une ressource coûteuse et à cycle de vie court : ouverture coûteuse, verrous côté serveur, descripteur réseau. Elle est AutoCloseable — la leçon sur l’IO l’avait dit pour les flux, le constat s’applique ici à l’identique : try-with-resources garantit la fermeture, succès ou exception. Notez que SQLException est une exception contrôlée : le compilateur vous force à la traiter.
importjava.sql.*;finalStringURL="jdbc:postgresql://dind:5432/oocol";finalString USER ="oocol";finalString PASS ="oocol";try(Connection connexion =DriverManager.getConnection(URL, USER, PASS)){DatabaseMetaData meta = connexion.getMetaData();System.out.printf("Connecté à %s%s%n", meta.getDatabaseProductName(), meta.getDatabaseProductVersion());}
Connecté à PostgreSQL 17.11
Du listing d’arrêts à la table
Trois familles de requêtes, trois méthodes : executeQuery pour un SELECT (retourne un ResultSet), executeUpdate pour INSERT/UPDATE/DELETE/DDL (retourne un nombre de lignes), execute pour le cas indéterminé. Création de la table et reprise des deux noms de la leçon 07, érigés en arrêts :
try(Connection connexion =DriverManager.getConnection(URL, USER, PASS);Statement stmt = connexion.createStatement()){ stmt.execute(""" CREATE TABLE IF NOT EXISTS arrets ( id TEXT PRIMARY KEY, nom TEXT NOT NULL, latitude DOUBLE PRECISION NOT NULL, longitude DOUBLE PRECISION NOT NULL )"""); stmt.executeUpdate(""" INSERT INTO arrets VALUES ('POM', 'Pommier', 43.125, 6.032), ('CHE', 'Chêne', 43.130, 6.040) ON CONFLICT (id) DO NOTHING""");System.out.println("Table arrets prête.");}
Table arrets prête.
Du ResultSet à l’objet : le premier DAO
ResultSet est un curseur ligne par ligne, accessible par nom ou index de colonne (getString, getDouble…) — un flux tabulaire, en aucun cas une collection Java. Le passage de l’un à l’autre s’appelle le mappage, et on l’enveloppe dans un Data Access Object (DAO) : une classe qui isole le reste du programme des détails SQL.
Le résultat est exactement la liste triée de la leçon 07 — mais alimentée par la base. Toute la suite de ce cours consomme des listes ainsi produites.
L’injection SQL : pourquoi la concaténation est une faille
Construire une requête par concaténation de chaînes fusionne le code SQL et les données. Un utilisateur malveillant peut alors injecter du SQL dans son champ de saisie. Démonstration sur une table annexe de contacts. Première attaque : la saisie x' OR '1'='1 ferme la chaîne, ajoute une condition toujours vraie ; le guillemet final du gabarit se retrouve orphelin et referme la vôtre. La requête « filtrante » retourne tout :
String saisie ="x' OR '1'='1";try(Connection connexion =DriverManager.getConnection(URL, USER, PASS);Statement stmt = connexion.createStatement()){ stmt.execute("DROP TABLE IF EXISTS contacts"); stmt.execute("CREATE TABLE contacts (nom TEXT, courriel TEXT)"); stmt.executeUpdate(""" INSERT INTO contacts VALUES ('Pommier', 'pommier@oocol.fr'), ('Chêne', 'chene@oocol.fr')""");String sql ="SELECT nom, courriel FROM contacts WHERE nom = '"+ saisie +"'";System.out.println("Requête exécutée : "+ sql);try(ResultSet rs = stmt.executeQuery(sql)){while(rs.next())System.out.printf("%s <%s>%n", rs.getString("nom"), rs.getString("courriel"));}}
Requête exécutée : SELECT nom, courriel FROM contacts WHERE nom = 'x' OR '1'='1'
Pommier <pommier@oocol.fr>
Chêne <chene@oocol.fr>
Pire : une saisie peut contenir plusieurs instructions — le -- final ouvre un commentaire SQL qui neutralise la fin du gabarit. Celle-ci détruit la table :
String saisieMechante ="y'; DROP TABLE contacts; --";try(Connection connexion =DriverManager.getConnection(URL, USER, PASS);Statement stmt = connexion.createStatement()){ stmt.execute("SELECT nom FROM contacts WHERE nom = '"+ saisieMechante +"'");}
Et le dégât devient visible — toute requête ultérieure sur la table échoue, la base ne sait pas pardonner une table supprimée :
EvalException: ERROR: relation "contacts" does not exist
Position: 22
---------------------------------------------------------------------------
org.postgresql.util.PSQLException: ERROR: relation "contacts" does not exist
Position: 22
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:251)
at .(#57:3)
La correction tient en un mot : ne jamais concaténer de donnée. Une requête ?-paramétrée (PreparedStatement) transmet le SQL au serveur une fois, et les valeurs à part ; une saisie n’est plus jamais du code :
try(Connection connexion =DriverManager.getConnection(URL, USER, PASS)){try(Statement stmt = connexion.createStatement()){ stmt.execute("DROP TABLE IF EXISTS contacts"); stmt.execute("CREATE TABLE contacts (nom TEXT, courriel TEXT)"); stmt.executeUpdate(""" INSERT INTO contacts VALUES ('Pommier', 'pommier@oocol.fr'), ('Chêne', 'chene@oocol.fr')""");}String sql ="SELECT nom, courriel FROM contacts WHERE nom = ?";for(String s :newString[]{"Pommier","x' OR '1'='1","y'; DROP TABLE contacts; --"}){try(PreparedStatement p = connexion.prepareStatement(sql)){ p.setString(1, s);int n =0;try(ResultSet rs = p.executeQuery()){while(rs.next()) n++;}System.out.printf("Saisie %-32s -> %d ligne(s)%n","\""+ s +"\"", n);}}try(Statement stmt = connexion.createStatement();ResultSet rs = stmt.executeQuery("SELECT count(*) FROM contacts")){ rs.next();System.out.println("Table intacte : "+ rs.getInt(1)+" contacts.");}}
Concaténer = fusionner code et données : l’attaque est triviale
Contournement : ' OR '1'='1 retourne toute la table
Destruction : '; DROP TABLE …; -- exécute une seconde instruction
Parade : PreparedStatement — le plan SQL est fixé, les valeurs restent des valeurs
Transactions : tout ou rien
Par défaut chaque instruction est auto-commitée. Un virement de titres entre deux cartes est deux UPDATE qui n’ont de sens que groupés. On désactive l’auto-commit, on valide par commit(), on annule par rollback() — et la contrainte CHECK (titres >= 0) de la table joue le rôle de l’incident :
try(Connection connexion =DriverManager.getConnection(URL, USER, PASS);Statement stmt = connexion.createStatement()){ stmt.execute("DROP TABLE IF EXISTS cartes"); stmt.execute(""" CREATE TABLE cartes ( id TEXT PRIMARY KEY, titulaire TEXT NOT NULL, titres INT NOT NULL CHECK (titres >= 0) )"""); stmt.executeUpdate("INSERT INTO cartes VALUES ('C1','Alice',10), ('C2','Bob',4)");}
Stringtransferer(Connection connexion,String de,String vers,int montant)throwsSQLException{ connexion.setAutoCommit(false);try(PreparedStatement d = connexion.prepareStatement("UPDATE cartes SET titres = titres - ? WHERE id = ?");PreparedStatement c = connexion.prepareStatement("UPDATE cartes SET titres = titres + ? WHERE id = ?")){ d.setInt(1, montant); d.setString(2, de); d.executeUpdate(); c.setInt(1, montant); c.setString(2, vers); c.executeUpdate(); connexion.commit();return"validé";}catch(SQLException e){ connexion.rollback();return"annulé ("+ e.getMessage().split("\n")[0]+")";}finally{ connexion.setAutoCommit(true);}}try(Connection connexion =DriverManager.getConnection(URL, USER, PASS)){System.out.println("15 titres C1 -> C2 : "+transferer(connexion,"C1","C2",15));System.out.println(" 5 titres C1 -> C2 : "+transferer(connexion,"C1","C2",5));try(Statement stmt = connexion.createStatement();ResultSet rs = stmt.executeQuery("SELECT titulaire, titres FROM cartes ORDER BY id")){while(rs.next())System.out.printf("%s : %d titres%n", rs.getString(1), rs.getInt(2));}}
15 titres C1 -> C2 : annulé (ERROR: new row for relation "cartes" violates check constraint "cartes_titres_check")
5 titres C1 -> C2 : validé
Alice : 5 titres
Bob : 9 titres
Le premier transfert échoue (Alice n’a que 10 titres) : le rollback() a effacé le débit déjà exécuté — Alice n’a pas perdu 15 titres « à mi-chemin ». Le second passe. Sans transaction, le premier cas aurait laissé une carte débitée sans contrepartie : c’est la propriété atomique du A de ACID.
Transaction : setAutoCommit(false) → commit() / rollback() dans un finally
Atomicité vérifiée par contrainte : un débit orphelin est effacé au rollback
Le lot (batch) : mille requêtes en une poignée d’échanges
addBatch() accumule des exécutions paramétrées et executeBatch() les envoie d’un coup — beaucoup moins d’allers-retours réseau. En production, on borne la mémoire par un clearBatch() périodique et des lots de taille finie (100–1000 lignes), validés d’une seule transaction. Insertion de 100 arrêts par lot :
try(Connection connexion =DriverManager.getConnection(URL, USER, PASS)){try(Statement stmt = connexion.createStatement()){ stmt.execute("DROP TABLE IF EXISTS arrets_massif"); stmt.execute("CREATE TABLE arrets_massif (id SERIAL PRIMARY KEY, nom TEXT)");} connexion.setAutoCommit(false);try(PreparedStatement p = connexion.prepareStatement("INSERT INTO arrets_massif(nom) VALUES (?)")){for(int i =1; i <=100; i++){ p.setString(1,"Arrêt "+ i); p.addBatch();} p.executeBatch(); connexion.commit();}finally{ connexion.setAutoCommit(true);}try(Statement stmt = connexion.createStatement();ResultSet rs = stmt.executeQuery("SELECT count(*) FROM arrets_massif")){ rs.next();System.out.println(rs.getInt(1)+" lignes insérées par lot.");}}
100 lignes insérées par lot.
Batch : addBatch() + executeBatch() = un envoi au lieu de mille
Gardez la transaction courte ; validez par lots de taille bornée (100–1000)
Le contrat des collections, version DAO
Rappel de la leçon 07 : une méthode qui retourne une collection retourne une collection vide, jamais null. C’est en traversant la couture JDBC que le contrat se brise le plus souvent — le code ci-dessous, hérité de pratiques anciennes, retourne null quand rien n’est trouvé :
List<Stop>chercherParNom(Connection connexion,String nom)throwsSQLException{List<Stop> trouve =newArrayList<>();try(PreparedStatement p = connexion.prepareStatement("SELECT id, nom, latitude, longitude FROM arrets WHERE nom = ?")){ p.setString(1, nom);try(ResultSet rs = p.executeQuery()){while(rs.next()) trouve.add(newStop(rs.getString(1), rs.getString(2), rs.getDouble(3), rs.getDouble(4)));}}return trouve.isEmpty()?null: trouve;// ← la mauvaise idée}try(Connection connexion =DriverManager.getConnection(URL, USER, PASS)){for(Stop s :chercherParNom(connexion,"Laurier"))// aucun arrêt de ce nomSystem.out.println(s);}
EvalException: Cannot invoke "java.util.List.iterator()" because the return value of "REPL.$JShell$63.chercherParNom(java.sql.Connection, String)" is null
---------------------------------------------------------------------------
java.lang.NullPointerException: Cannot invoke "java.util.List.iterator()" because the return value of "REPL.$JShell$63.chercherParNom(java.sql.Connection, String)" is null
at .(#64:1)
La boucle for-each appelle iterator() sur null — NullPointerException, avec le message enrichi (Java 14+) qui nomme la méthode fautive. La correction tient en une ligne : return trouve; — le vide s’exprime par un ResultSet sans ligne, donc une List sans élément, exactement le contrat posé à la leçon 07.
Portabilité : le même code DAO sur H2
JDBC promet une interface uniforme ; la réalité adoucit la promesse (le dialecte SQL reste fournisseur-spécifique). Vérifions la partie portable — le motif d’accès — sur un SGBD embarqué, H2 en mode compatibilité PostgreSQL, en mémoire :
finalString H2_URL ="jdbc:h2:mem:oocol;MODE=PostgreSQL;DB_CLOSE_DELAY=-1";try(Connection h2 =DriverManager.getConnection(H2_URL,"sa","")){try(Statement stmt = h2.createStatement()){ stmt.execute(""" CREATE TABLE IF NOT EXISTS arrets ( id VARCHAR(3) PRIMARY KEY, nom VARCHAR(50) NOT NULL, latitude DOUBLE PRECISION NOT NULL, longitude DOUBLE PRECISION NOT NULL)"""); stmt.execute(""" MERGE INTO arrets KEY(id) VALUES ('POM','Pommier',43.125,6.032), ('CHE','Chêne',43.130,6.040)""");}lireLesArrets(h2).forEach(System.out::println);// la MÉME méthode qu'avant}
lireLesArrets n’a pas bougé d’un caractère : c’est tout l’intérêt de l’interface Connection et du DAO. Ce qui, lui, n’est pas portable : ON CONFLICT (H2 : MERGE), les types fins, et les fonctions spécifiques comme ST_Distance — une raison de concentrer le SQL dialectal dans la couche DAO, jamais dans le reste du programme.
JDBC normalise l’accès (DriverManager, Statement, ResultSet), pas le dialecte
Le même DAO marche sur PostgreSQL et H2 : seule l’URL change
Différences vues : ON CONFLICT vs MERGE ; fonctions absentes en H2 (ST_*)
Conséquence d’architecture : enfermer le SQL spécifique dans la couche DAO
Base arrêtée.
Conclusion
JDBC est la couture entre les objets de la leçon 07 et les données persistantes : une connexion AutoCloseable, un curseur ResultSet à mapper en collections — puis trois durcissements sans lesquels le code n’est pas professionnel : le PreparedStatement contre l’injection, la transaction pour l’atomicité « tout ou rien », le batch pour le volume. La portabilité est réelle au niveau de l’API et illusoire au niveau du dialecte — d’où le DAO comme lieu de confinement.
Questions de vérification :
Pourquoi Class.forName sur le driver n’est-il plus nécessaire, et qu’est-ce qui décide du driver à utiliser ?
Une saisie utilisateur arrive dans WHERE nom = ? via setString, puis dans une requête concaténée : lequel des deux est sûr, et pourquoi ?
Un transfert en deux UPDATE échoue au second, en auto-commit par défaut : que reste-t-il en base, et comment l’éviter ?
Notre chercherParNom historique renvoie null sur résultat vide. Qui le découvre, quand, et avec quelle exception ?
Pour aller plus loin (TP) : jeu complet GTFS, requêtes de proximité à l’échelle, ResultSetMetaData, et pool de connexions (DataSource) — évoqués mais hors du périmètre de cette leçon.