En Java, vous avez le choix de travailler avec un Statement, PreparedStatement, ou CallableStatement. Les deux derniers sont des sous-interfaces de Statement, comme illustré dans la Figure 15.4.
Types de déclarations
Plus tard dans ce chapitre, vous apprendrez à utiliser CallableStatement pour les requêtes qui se trouvent dans la base de données. Dans cette section, nous examinons PreparedStatement.
Et Statement, me direz-vous ? C’est une interface que PreparedStatement et CallableStatement étendent. Un Statement et un PreparedStatement sont similaires, sauf qu’un PreparedStatement prend des paramètres, tandis qu’un Statement n’en prend pas. Un Statement exécute simplement la requête SQL que vous lui donnez.
Bien qu’il soit possible d’exécuter du SQL directement avec Statement, vous ne devriez pas le faire. PreparedStatement est bien supérieur pour les raisons suivantes :
- Performance : Dans la plupart des programmes, vous exécutez des requêtes similaires plusieurs fois. Lorsque vous utilisez PreparedStatement, le logiciel de base de données conçoit souvent un plan pour bien exécuter la requête et s’en souvient.
- Sécurité : Vous êtes protégé contre une attaque appelée injection SQL lorsque vous utilisez correctement PreparedStatement.
- Lisibilité : C’est agréable de ne pas avoir à gérer la concaténation de chaînes pour construire une chaîne de requête avec beaucoup de paramètres.
- Utilisation future : Même si votre requête n’est exécutée qu’une seule fois ou n’a pas de paramètres, vous devriez quand même utiliser un PreparedStatement. De cette façon, les futurs éditeurs du code n’auront pas à ajouter une variable et à se souvenir de passer à PreparedStatement.
Petit Bobby Tables
L’injection SQL est souvent causée par un manque de sanitisation appropriée des entrées utilisateur. L’auteur de la populaire bande dessinée web xkcd.com s’est un jour demandé ce qui se passerait si le nom de quelqu’un contenait une instruction SQL ?
“Exploits of a Mom” reproduit avec permission de xkcd.com/327/
Oups ! On dirait que l’école aurait dû utiliser un PreparedStatement et lier le nom de chaque élève à une variable. Si elle l’avait fait, la chaîne entière aurait été correctement échappée et stockée dans la base de données.
Obtenir un PreparedStatement
Pour exécuter SQL, vous devez en informer un PreparedStatement. Obtenir un PreparedStatement à partir d’une Connection est facile.
try (PreparedStatement ps = conn.prepareStatement(
"SELECT * FROM exhibits")) {
// travailler avec ps
}
Une instance de PreparedStatement représente une instruction SQL que vous souhaitez exécuter à l’aide de la Connection. Elle n’exécute pas encore la requête ! Nous y arrivons bientôt.
Passer une instruction SQL lors de la création de l’objet est obligatoire. Voici un exemple qui ne compile pas :
try (var ps = conn.prepareStatement()) { // NE COMPILE PAS
}
L’exemple précédent ne compile pas car SQL n’est pas fourni au moment où un PreparedStatement est demandé. Nous avons également utilisé var dans cet exemple. Nous écrivons du code JDBC à la fois en utilisant var et les noms de classe réels pour vous habituer aux deux approches.
Il existe des signatures surchargées qui vous permettent de spécifier un type ResultSet et un mode de concurrence. Par défaut, les résultats sont traités dans l’ordre.
Exécuter un PreparedStatement
Maintenant que nous avons un PreparedStatement, nous pouvons exécuter l’instruction SQL. La méthode d’exécution SQL varie en fonction du type d’instruction SQL. Vous n’êtes pas censé savoir lire le SQL, mais vous devez connaître la signification du premier mot-clé.
Modifier des données avec executeUpdate()
Commençons par les instructions qui modifient les données d’une table. Ce sont des instructions SQL qui commencent par DELETE, INSERT ou UPDATE. Elles utilisent généralement une méthode appelée executeUpdate(). Le nom est un peu trompeur car l’instruction SQL UPDATE n’est pas la seule instruction qui utilise cette méthode.
La méthode prend l’instruction SQL à exécuter comme paramètre. Elle renvoie le nombre de lignes qui ont été insérées, supprimées ou modifiées. Voici un exemple des trois types de mise à jour :
var insertSql = "INSERT INTO exhibits VALUES(10, 'Cerf', 3)";
var updateSql = "UPDATE exhibits SET name = '' " +
"WHERE name = 'None'";
var deleteSql = "DELETE FROM exhibits WHERE id = 10";
try (var ps = conn.prepareStatement(insertSql)) {
int result = ps.executeUpdate();
System.out.println(result); // 1
}
try (var ps = conn.prepareStatement(updateSql)) {
int result = ps.executeUpdate();
System.out.println(result); // 0
}
try (var ps = conn.prepareStatement(deleteSql)) {
int result = ps.executeUpdate();
System.out.println(result); // 1
}
La ligne qui crée l’instruction d’insertion exécute cette instruction pour insérer une ligne. Le résultat est 1 car une ligne a été affectée. L’instruction de mise à jour vérifie toute la table pour les enregistrements correspondants à mettre à jour. Comme aucun enregistrement ne correspond, le résultat est 0. L’instruction de suppression supprime la ligne créée par l’insertion. Encore une fois, une ligne est affectée, donc le résultat est 1.
Lire des données avec executeQuery()
Ensuite, examinons une instruction SQL qui commence par SELECT. Cette fois, nous utilisons la méthode executeQuery().
var sql = "SELECT * FROM exhibits";
try (var ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery() ) {
// travailler avec rs
}
Nous créons un PreparedStatement pour notre requête SELECT, puis nous l’exécutons. Puisque nous exécutons une requête pour obtenir un résultat, le type de retour est ResultSet. Dans la section suivante, nous vous montrons comment traiter le ResultSet.
Traiter des données avec execute()
Il existe une troisième méthode appelée execute() qui peut exécuter soit une requête, soit une mise à jour. Elle renvoie un booléen pour que nous sachions s’il y a un ResultSet. De cette façon, nous pouvons appeler la méthode appropriée pour obtenir plus de détails. Le modèle ressemble à ceci :
boolean isResultSet = ps.execute();
if (isResultSet) {
try (ResultSet rs = ps.getResultSet()) {
System.out.println("requête exécutée");
}
} else {
int result = ps.getUpdateCount();
System.out.println("mise à jour exécutée");
}
Si le PreparedStatement fait référence à sql qui est un SELECT, le booléen est vrai, et nous pouvons obtenir le ResultSet. Si ce n’est pas un SELECT, nous pouvons obtenir le nombre de lignes mises à jour.
Utiliser la méthode correcte
Que pensez-vous qu’il se passe si nous utilisons la mauvaise méthode pour une instruction SQL ? Jetons un coup d’œil :
var sql = "SELECT * FROM names";
try (var ps = conn.prepareStatement(sql)) {
var result = ps.executeUpdate();
}
Cela lance une SQLException similaire à la suivante :
Exception in thread “main” java.sql.SQLException: statement does not generate a row count
Nous ne pouvons pas obtenir d’erreur de compilateur puisque le SQL est une chaîne. Nous pouvons toutefois obtenir une exception, et c’est ce qui se produit. Nous obtenons également une SQLException lorsque nous utilisons executeQuery() avec du SQL qui modifie la base de données.
Exception in thread “main” java.sql.SQLException: statement does not generate a result set
Encore une fois, nous obtenons une exception car le pilote ne peut pas traduire la requête dans le type de retour attendu.
Revoir les méthodes PreparedStatement
Pour récapituler, assurez-vous de bien connaître les tableaux 15.3 et 15.4. Le tableau 15.3 montre quelles instructions SQL peuvent être exécutées par chacune des trois méthodes clés sur PreparedStatement. Le tableau 15.4 montre ce qui est renvoyé par chaque méthode.
| Méthode | DELETE | INSERT | SELECT | UPDATE |
|---|---|---|---|---|
| ps.execute() | Oui | Oui | Oui | Oui |
| ps.executeQuery() | Non | Non | Oui | Non |
| ps.executeUpdate() | Oui | Oui | Non | Oui |
| Méthode | Type de retour | Ce qui est renvoyé pour SELECT | Ce qui est renvoyé pour DELETE/INSERT/UPDATE |
|---|---|---|---|
| ps.execute() | boolean | true | false |
| ps.executeQuery() | ResultSet | Lignes et colonnes renvoyées | n/a |
| ps.executeUpdate() | int | n/a | Nombre de lignes ajoutées/modifiées/supprimées |
Travailler avec des paramètres
Supposons que notre zoo acquiert un nouvel éléphant et que nous voulons l’enregistrer dans notre table names. Nous avons déjà appris suffisamment pour le faire.
public static void enregistrer(Connection conn) throws SQLException {
var sql = "INSERT INTO names VALUES(6, 1, 'Edith')";
try (var ps = conn.prepareStatement(sql)) {
ps.executeUpdate();
}
}
Cependant, tout est codé en dur. Nous voulons pouvoir passer les valeurs comme paramètres. Heureusement, un PreparedStatement nous permet de définir des paramètres. Au lieu de spécifier les trois valeurs dans le SQL, nous pouvons utiliser un point d’interrogation (?). Une variable de liaison est un espace réservé qui vous permet de spécifier les valeurs réelles au moment de l’exécution. Une variable de liaison est comme un paramètre, et vous verrez les variables de liaison référencées à la fois comme variables et comme paramètres. Nous pouvons réécrire notre instruction SQL en utilisant des variables de liaison.
String sql = "INSERT INTO names VALUES(?, ?, ?)";
Les variables de liaison rendent le SQL plus facile à lire car vous n’avez plus besoin d’utiliser de guillemets autour des valeurs String dans le SQL. Maintenant, nous pouvons passer les paramètres à la méthode elle-même.
public static void enregistrer(Connection conn, int cle,
int type, String nom) throws SQLException {
String sql = "INSERT INTO names VALUES(?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, cle);
ps.setString(3, nom);
ps.setInt(2, type);
ps.executeUpdate();
}
}
La ligne qui crée un PreparedStatement utilise notre SQL qui contient trois variables de liaison. Les lignes suivantes définissent ces variables. Vous pouvez considérer les variables de liaison comme une liste de paramètres, où chacun est défini à son tour. Notez comment les variables de liaison peuvent être définies dans n’importe quel ordre. La dernière ligne exécute la requête et effectue la mise à jour.
Notez que les variables de liaison sont comptées à partir de 1 plutôt que de 0. C’est vraiment important, donc nous allons le répéter.
Rappel : JDBC commence à compter les colonnes à partir de 1 plutôt que de 0. Une question d’examen courante teste que vous savez cela !
Dans l’exemple précédent, nous avons défini les paramètres dans le désordre. C’est parfaitement acceptable. La règle est seulement qu’ils sont tous définis avant que la requête ne soit exécutée. Voyons ce qui se passe si vous ne définissez pas toutes les variables de liaison.
var sql = "INSERT INTO names VALUES(?, ?, ?)";
try (var ps = conn.prepareStatement(sql)) {
ps.setInt(1, cle);
ps.setInt(2, type);
// il manque le set pour le paramètre numéro 3
ps.executeUpdate();
}
Le code compile, et vous obtenez une SQLException. Le message peut varier en fonction de votre pilote de base de données.
Exception in thread “main” java.sql.SQLException: Parameter not set
Et si vous essayez de définir plus de valeurs que vous n’avez de variables de liaison ?
var sql = "INSERT INTO names VALUES(?, ?)";
try (var ps = conn.prepareStatement(sql)) {
ps.setInt(1, cle);
ps.setInt(2, type);
ps.setString(3, nom);
ps.executeUpdate();
}
Encore une fois, vous obtenez une SQLException, cette fois avec un message différent.
Exception in thread “main” java.sql.SQLException: row column count mismatch in statement [INSERT INTO names VALUES(?, ?)]
Le tableau 15.5 montre les méthodes que vous devez connaître pour définir les variables de liaison. Celles que vous devez connaître sont faciles à mémoriser car elles s’appellent set suivi du nom du type que vous définissez. Il en existe beaucoup d’autres, comme les dates, mais elles sont hors de portée de cet article.
| Méthode | Type de paramètre | Exemple de type de base de données |
|---|---|---|
| setBoolean | boolean | BOOLEAN |
| setDouble | double | DOUBLE |
| setInt | int | INTEGER |
| setLong | long | BIGINT |
| setNull | int | Tout type |
| setObject | Object | Tout type |
| setString | String | CHAR, VARCHAR |
La première colonne indique le nom de la méthode, la deuxième colonne indique le type que Java utilise. La troisième colonne indique le nom de type qui pourrait se trouver dans la base de données. Il y a une certaine variation selon les bases de données, alors consultez la documentation spécifique à votre base de données.
La méthode setNull() prend un paramètre int représentant le type de colonne dans la base de données. Notez que la méthode setObject() fonctionne avec n’importe quel type Java. Si vous passez un type primitif, il sera automatiquement encapsulé dans un type enveloppe. Cela signifie que nous pouvons réécrire notre exemple comme suit :
String sql = "INSERT INTO names VALUES(?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setObject(1, cle);
ps.setObject(2, type);
ps.setObject(3, nom);
ps.executeUpdate();
}
Java gérera la conversion de type pour vous. Il est néanmoins préférable d’appeler les méthodes setter plus spécifiques, car cela vous donnera une erreur de compilation si vous passez le mauvais type, au lieu d’une erreur d’exécution.
Mettre à jour plusieurs enregistrements
Supposons que nous obtenions deux nouveaux éléphants et que nous voulions les ajouter tous les deux. Nous pouvons utiliser le même objet PreparedStatement.
var sql = "INSERT INTO names VALUES(?, ?, ?)";
try (var ps = conn.prepareStatement(sql)) {
ps.setInt(1, 20);
ps.setInt(2, 1);
ps.setString(3, "Ester");
ps.executeUpdate();
ps.setInt(1, 21);
ps.setString(3, "Elias");
ps.executeUpdate();
}
Notez que nous avons défini les trois paramètres lors de l’ajout d’Ester, mais seulement deux pour Elias. Le PreparedStatement est suffisamment intelligent pour se souvenir des paramètres qui ont déjà été définis et les conserver. Vous n’avez qu’à définir ceux qui sont différents.
Scénario du monde réel
Traitement par lots des instructions
JDBC prend en charge le traitement par lots afin que vous puissiez exécuter plusieurs instructions en moins de voyages vers la base de données. Souvent, la base de données est située sur une machine différente de celle où le code Java s’exécute. Économiser des voyages vers la base de données permet de gagner du temps car les appels réseau peuvent être coûteux. Par exemple, si vous devez insérer 1 000 enregistrements dans la base de données, les insérer en un seul appel réseau (par opposition à 1 000 appels réseau) est généralement beaucoup plus rapide.
public static void enregistrer(Connection conn, int premiereCle,
int type, String... noms) throws SQLException {
var sql = "INSERT INTO names VALUES(?, ?, ?)";
var indexSuivant = premiereCle;
try (var ps = conn.prepareStatement(sql)) {
ps.setInt(2, type);
for(var nom: noms) {
ps.setInt(1, indexSuivant);
ps.setString(3, nom);
ps.addBatch();
indexSuivant++;
}
int[] result = ps.executeBatch();
System.out.println(Arrays.toString(result));
}
}
Maintenant, nous appelons cette méthode avec deux noms :
enregistrer(conn, 100, 1, "Elias", "Ester");
La sortie montre que le tableau a deux éléments puisqu’il y a deux éléments différents dans le lot. Chacun a ajouté une ligne dans la base de données.
[1, 1]
Vous pouvez utiliser le traitement par lots pour décomposer de grandes opérations, comme l’insertion de 10 millions d’enregistrements en groupes de 100. En pratique, il faut un peu de travail pour déterminer une taille de lot appropriée, mais les performances de l’utilisation du lot sont normalement bien meilleures que l’insertion d’une ligne à la fois (ou de dix millions à la fois).