Dans certaines situations, il est utile de stocker les requêtes SQL dans la base de données au lieu de les intégrer avec le code Java. C’est particulièrement utile lorsqu’il y a de nombreuses requêtes complexes. Une procédure stockée est du code qui est compilé à l’avance et stocké dans la base de données. Les procédures stockées sont généralement écrites dans une variante spécifique de SQL, qui varie selon les fournisseurs de logiciels de base de données.
L’utilisation d’une procédure stockée réduit les allers-retours réseau. Cela permet également aux experts en base de données de posséder cette partie du code. Cependant, les procédures stockées sont spécifiques à une base de données et introduisent de la complexité dans la maintenance de votre application.
Vous n’avez pas besoin de savoir comment lire ou écrire une procédure stockée. Par conséquent, nous n’en avons inclus aucune dans le livre. Elles sont dans le code pour configurer la base de données exemple si vous êtes curieux.
Vous n’avez pas besoin d’apprendre quoi que ce soit de spécifique à une base de données. Comme l’étude des procédures stockées peut être assez compliquée, nous vous recommandons de limiter votre étude sur CallableStatement à ce qui se trouve dans ce livre.
Nous utiliserons quatre procédures stockées dans cette section. Le tableau 15.7 résume ce que vous devez savoir à leur sujet. Dans le monde réel, aucune d’entre elles ne serait une bonne implémentation car elles ne sont pas assez complexes pour justifier d’être des procédures stockées. Comme vous pouvez le voir dans le tableau, les procédures stockées permettent des paramètres pour l’entrée uniquement, la sortie uniquement, ou les deux.
| Nom | Nom du paramètre | Type de paramètre | Description |
|---|---|---|---|
| lire_noms_e() | n/a | n/a | Renvoie toutes les lignes de la table noms qui ont un nom commençant par e ou E |
| lire_noms_par_lettre() | prefixe | IN | Renvoie toutes les lignes de la table noms qui ont un nom commençant par le paramètre spécifié (insensible à la casse) |
| nombre_magique() | num | OUT | Renvoie le nombre 42 |
| doubler_nombre() | num | INOUT | Multiplie le paramètre par deux et renvoie ce nombre |
Dans les quatre sections suivantes, nous examinons comment appeler chacune de ces procédures stockées.
Appeler une procédure sans paramètres
Notre procédure stockée lire_noms_e() ne prend aucun paramètre. Elle renvoie un ResultSet. Puisque nous avons travaillé avec un ResultSet dans la section PreparedStatement, nous pouvons nous concentrer sur la façon dont la procédure stockée est appelée.
String sql = "{call lire_noms_e()}";
try (CallableStatement cs = conn.prepareCall(sql);
ResultSet rs = cs.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString(3));
}
}
La ligne 12 introduit une nouvelle syntaxe. Une procédure stockée est appelée en mettant le mot call et le nom de la procédure entre accolades ({}). La ligne 13 crée un objet CallableStatement. Lorsque nous avons créé un PreparedStatement, nous avons utilisé la méthode prepareStatement(). Ici, nous utilisons la méthode prepareCall() à la place.
Les lignes 14-18 devraient vous sembler familières. C’est la logique standard que nous avons utilisée pour obtenir un ResultSet et le parcourir. Cette procédure stockée renvoie la table sous-jacente, donc les colonnes sont les mêmes.
Passer un paramètre IN
Une procédure stockée qui renvoie toujours la même chose n’est que moyennement utile. Nous avons créé une nouvelle version de cette procédure stockée qui est plus générique. La procédure stockée lire_noms_par_lettre() prend un paramètre pour le préfixe ou la première lettre de la procédure stockée. Un paramètre IN est utilisé pour l’entrée.
Il y a deux différences dans son appel par rapport à notre procédure stockée précédente.
var sql = "{call lire_noms_par_lettre(?)}";
try (var cs = conn.prepareCall(sql)) {
cs.setString("prefixe", "Z");
try (var rs = cs.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString(3));
}
}
}
À la ligne 25, nous devons passer un ? pour montrer que nous avons un paramètre. Cela devrait vous être familier des variables de liaison avec un PreparedStatement.
À la ligne 27, nous définissons la valeur de ce paramètre. Contrairement à PreparedStatement, nous pouvons utiliser soit le numéro du paramètre (à partir de 1), soit le nom du paramètre. Cela signifie que ces deux instructions sont équivalentes :
cs.setString(1, "Z");
cs.setString("prefixe", "Z");
Renvoyer un paramètre OUT
Dans nos exemples précédents, nous avons renvoyé un ResultSet. Certaines procédures stockées renvoient d’autres informations. Heureusement, les procédures stockées peuvent avoir des paramètres OUT pour la sortie. La procédure stockée nombre_magique() définit son paramètre OUT à 42. Il y a quelques différences ici :
var sql = "{?= call nombre_magique(?) }";
try (var cs = conn.prepareCall(sql)) {
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt("num"));
}
À la ligne 40, nous incluons deux caractères spéciaux (?=) pour spécifier que la procédure stockée a une valeur de sortie. C’est facultatif puisque nous avons le paramètre OUT, mais cela aide à la lisibilité.
À la ligne 42, nous enregistrons le paramètre OUT. C’est important. Cela permet à JDBC de récupérer la valeur à la ligne 44. N’oubliez pas d’appeler toujours registerOutParameter() pour chaque paramètre OUT ou INOUT (que nous couvrons ensuite).
À la ligne 43, nous appelons execute() au lieu de executeQuery() car nous ne renvoyons pas un ResultSet de notre procédure stockée.
Comportement spécifique à la base de données
Certaines bases de données sont indulgentes concernant certaines choses que ce chapitre dit être requises. Par exemple, certaines bases de données vous permettent d’omettre ce qui suit :
- Accolades ({})
- Variable de liaison (?) si c’est un paramètre OUT
- Appel à registerOutParameter()
Pour l’utilisation dans le monde réel, vous devez répondre selon les exigences complètes, qui sont décrites dans ce livre. Par exemple, vous devez supposer que les accolades sont requises.
Travailler avec un paramètre INOUT
Enfin, il est possible d’utiliser le même paramètre pour l’entrée et la sortie. En lisant ce code, voyez si vous pouvez repérer quelles lignes sont requises pour la partie IN et quelles lignes sont requises pour la partie OUT :
var sql = "{call doubler_nombre(?)}";
try (var cs = conn.prepareCall(sql)) {
cs.setInt(1, 8);
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt("num"));
}
Pour un paramètre IN, la ligne 52 est requise car elle définit la valeur. Pour un paramètre OUT, la ligne 53 est requise pour l’enregistrer. La ligne 54 utilise execute() à nouveau car nous ne renvoyons pas un ResultSet.
N’oubliez pas qu’un paramètre INOUT agit à la fois comme un paramètre IN et un paramètre OUT, donc il a toutes les exigences des deux.
Comparer les paramètres de CallableStatement
Le tableau 15.8 passe en revue les différents types de paramètres. Vous devez bien connaître cela.
| Type de paramètre | IN | OUT | INOUT |
|---|---|---|---|
| Utilisé pour l’entrée | Oui | Non | Oui |
| Utilisé pour la sortie | Non | Oui | Oui |
| Doit définir la valeur du paramètre | Oui | Non | Oui |
| Doit appeler registerOutParameter() | Non | Oui | Oui |
| Peut inclure ?= | Non | Oui | Oui |
Utiliser des options supplémentaires
Jusqu’à présent, nous avons créé des PreparedStatement et CallableStatement avec les options par défaut. Les deux prennent en charge les options de type et de concurrence ResultSet. Toutes les options ne sont pas disponibles sur toutes les bases de données. Heureusement, vous devez juste être capable de les reconnaître comme valides.
Il existe trois valeurs entières de type ResultSet :
- ResultSet.TYPE_FORWARD_ONLY : Peut parcourir le ResultSet uniquement une ligne à la fois
- ResultSet.TYPE_SCROLL_INSENSITIVE : Peut parcourir le ResultSet dans n’importe quel ordre mais ne verra pas les modifications apportées à la table de base de données sous-jacente
- ResultSet.TYPE_SCROLL_SENSITIVE : Peut parcourir le ResultSet dans n’importe quel ordre et verra les modifications apportées à la table de base de données sous-jacente
Il existe deux valeurs entières de mode de concurrence ResultSet :
- ResultSet.CONCUR_READ_ONLY : Le ResultSet ne peut pas être mis à jour.
- ResultSet.CONCUR_UPDATABLE : Le ResultSet peut être mis à jour.
Ces options sont des valeurs entières, pas des valeurs d’énumération, ce qui signifie que vous passez les deux comme paramètres supplémentaires après le SQL.
conn.prepareCall(sql, ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Si vous voyez ces options, faites attention à la façon dont elles sont utilisées. N’oubliez pas que le type vient toujours en premier. De plus, les méthodes qui prennent le type prennent également le mode de concurrence, alors méfiez-vous de toute question qui ne passe qu’une seule option.