Créez des clauses SQL facultatives vides avec jOOQ – Java, SQL et jOOQ.

Online Coding Courses for Kids

Lorsque vous utilisez jOOQ pour créer instructions SQL dynamiques (une des propositions de valeur de base de jOOQ), il est souvent nécessaire d’ajouter des éléments de requête de manière conditionnelle, avec un comportement par défaut “No-op”. Pour les utilisateurs débutants, ce comportement par défaut «sans opération» n’est pas toujours évident car l’API jOOQ est vaste et, comme pour toute vaste API, il existe de nombreuses options différentes pour faire des choses similaires.

Comment ne pas le faire

Un écueil commun est d’être tenté de travailler avec les nombreux XYZStep les types. De quels types s’agit-il? Ils sont généralement invisibles pour le développeur car les développeurs utilisent l’API DSL de jOOQ de manière fluide, tout comme l’API JDK Stream. Par exemple:

DSLContext ctx = ...;

Result result =
ctx.select(T.A, T.B)
   .from(T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))
   .fetch();

Décomposons la requête ci-dessus pour voir ce qui se passe dans l’API. Nous pourrions affecter chaque résultat de méthode à une variable locale:

SelectFromStep s1 = ctx.select(T.A, T.B);
SelectWhereStep s2 = s1.from(T);
SelectConditionStep s3 = s2.where(T.C.eq(1));
SelectConditionStep s4 = s3.and(T.D.eq(2))

Result result = s4.fetch();

Notre précédent article de blog sur la conception d’API explique cette technique de conception d’API.

Ce n’est pas ce que les gens font habituellement avec les instructions «SQL statique», mais ils pourraient être tentés de le faire s’ils voulaient ajouter le dernier prédicat (T.D = 2) conditionnellement, par exemple:

DSLContext ctx = ...;

SelectConditionStep c =
ctx.select(T.A, T.B)
   .from(T)
   .where(T.C.eq(1));

if (something)
    c = c.and(T.D.eq(2));

Result result = c.fetch();

Il s’agit d’une utilisation d’API parfaitement valide, mais nous ne la recommandons pas car elle est très salissante et conduit à un code client difficile à maintenir. De plus, c’est absolument inutile, car il existe une meilleure façon:

Composer des requêtes à partir de ses parties

Le problème avec l’approche ci-dessus est qu’elle essaie d’utiliser une approche impérative pour ajouter des éléments à une requête étape par étape. C’est ainsi que de nombreux développeurs ont tendance à structurer leur code, mais avec SQL (et par conséquent, jOOQ), cela peut s’avérer difficile à obtenir correctement. Une approche fonctionnelle a tendance à mieux fonctionner.

Notez que non seulement la structure DSL entière peut être affectée à des variables locales, mais aussi l’individu SELECT arguments de la clause. Par exemple:

DSLContext ctx = ...;

List> select = Arrays.asList(T.A, T.B);
Table from = T;
Condition where = T.C.eq(1).and(T.D.eq(2));

Result result =
ctx.select(select)
   .from(from)
   .where(where)
   .fetch();

En réalité, chaque La requête jOOQ est une requête SQL dynamique. De nombreuses requêtes ressemblent à des requêtes statiques, en raison de la conception de l’API de jOOQ.

Encore une fois, nous n’attribuerions pas chaque SELECT argument de clause à une variable locale, uniquement les véritables dynamiques. Par exemple:

DSLContext ctx = ...;

Condition where = T.C.eq(1);

if (something)
    where = where.and(T.D.eq(2));

Result result =
ctx.select(T.A, T.B)
   .from(T)
   .where(where)
   .fetch();

Cela semble déjà assez décent.

Évitez de briser la lisibilité

Beaucoup de gens ne sont pas satisfaits non plus de cette approche, car elle rompt la lisibilité d’une requête en rendant ses composants non locaux. Les prédicats de la requête sont déclarés à l’avance, loin de la requête elle-même. Ce n’est pas le nombre de personnes qui aiment raisonner sur SQL.

Et vous n’avez pas à le faire! Il est totalement possible d’incorporer la condition directement dans le WHERE clause comme celle-ci:

DSLContext ctx = ...;

Result result =
ctx.select(T.A, T.B)
   .from(T)

   // We always need this predicate
   .where(T.C.eq(1))

   // This is only added conditionally
   .and(something
      ? T.D.eq(2)
      : DSL.noCondition())
   .fetch();

La magie est dans l’utilisation ci-dessus de DSL.noCondition, qui est un pseudo-prédicat qui ne génère aucun contenu. C’est un espace réservé où un org.jooq.Condition le type est requis sans en matérialiser un.

Il y a aussi:

… Mais cela nécessite de penser constamment à ces identités et aux réductions. De plus, si vous ajoutez plusieurs de ces trueCondition() ou falseCondition() à une requête, le SQL résultant a tendance à être assez moche, par exemple pour les personnes devant analyser les performances en production. noCondition() ne génère jamais de contenu.

Notez que noCondition() Est-ce que ne pas agir comme une identité! Si votre noCondition() est le seul prédicat qui reste dans un WHERE clause, il n’y aura pas de WHERE , que vous travailliez avec AND prédicats ou OR prédicats.

Expressions sans opération dans jOOQ

Lorsque vous utilisez du SQL dynamique comme celui-ci et que vous ajoutez des éléments de manière conditionnelle aux requêtes, ces «expressions sans opération» deviennent obligatoires. Dans l’exemple précédent, nous avons vu comment ajouter un «prédicat sans opération» à un WHERE clause (la même approche fonctionnerait évidemment avec HAVING et toutes les autres clauses qui fonctionnent avec des expressions booléennes).

Les trois types de requêtes jOOQ les plus importants sont:

Les utilisateurs peuvent souhaiter ajouter tous ces éléments de manière conditionnelle aux requêtes.

org.jooq.Condition

Nous avons déjà vu comment procéder avec org.jooq.Condition.

org.jooq.Field

Qu’en est-il des expressions de colonnes dynamiques dans la projection (la SELECT clause)? En supposant que vous souhaitez projeter des colonnes uniquement dans certains cas. Dans notre exemple, le T.B la colonne est quelque chose dont nous n’avons pas toujours besoin. C’est facile! La même approche peut être utilisée (en supposant T.B est une colonne de chaîne):

DSLContext ctx = ...;

Result> result =
ctx.select(T.A, something ? T.B : DSL.inline("").as(T.B))
   .from(T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))
   .fetch();

En utilisant paramètres intégrés via DSL.inline (), vous pouvez facilement produire une valeur sans opération dans votre projection si vous ne souhaitez pas modifier le type de ligne de la projection. L’avantage est que vous pouvez désormais utiliser cette sous-requête dans une union qui attend deux colonnes:

DSLContext ctx = ...;

Result> result =

// First union subquery has a conditionally projected column
ctx.select(T.A, something ? T.B : DSL.inline("").as(T.B))
   .from(T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))

   .union(

// Second union subquery has no such conditions
    select(U.A, U.B)
   .from(U))
   .fetch();

Vous pouvez aller plus loin et rendre conditionnelle une sous-requête d’union entière!

DSLContext ctx = ...;

Result> result =

// First union subquery has a conditionally projected column
ctx.select(T.A, T.B)
   .from(T)
   .union(
      something
        ? select(U.A, U.B).from(U)
        : select(inline(""), inline("")).where(falseCondition())
   )
   .fetch();

C’est un travail un peu plus syntaxique, mais il est agréable de voir à quel point il est facile d’ajouter quelque chose de manière conditionnelle à une requête jOOQ sans rendre la requête complètement illisible. Tout est local à l’endroit où il est utilisé. Aucune variable locale n’est nécessaire, aucun flux de contrôle impératif n’est appelé.

Et comme tout est désormais une expression (et non une instruction / pas de flux de contrôle), nous pouvons factoriser certaines parties de cette requête en méthodes auxiliaires, qui peuvent être réutilisées.

org.jooq.Table

Les expressions de table conditionnelles apparaissent généralement lors des jointures conditionnelles. Cela n’est généralement pas fait de manière isolée, mais avec d’autres éléments conditionnels dans une requête. Par exemple. si certaines colonnes sont projetées conditionnellement, ces colonnes peuvent nécessiter une jointure supplémentaire, car elles proviennent d’une autre table que les tables qui sont utilisées sans condition. Par exemple:

DSLContext ctx = ...;

Result result =
ctx.select(
      T.A, 
      T.B, 
      something ? U.X : inline(""))
   .from(
      something
      ? T.join(U).on(T.Y.eq(U.Y))
      : T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))
   .fetch();

Il n’y a pas de moyen plus simple de produire le conditionnel JOIN expression, car JOIN et ON doivent être fournis indépendamment. Pour les cas simples comme indiqué ci-dessus, c’est parfaitement bien. Dans des cas plus complexes, certaines méthodes auxiliaires peuvent être nécessaires, ou des vues.

Conclusion

Il y a deux messages importants ici dans cet article:

  1. Les types XYZStep sont types auxiliaires uniquement. Ils sont là pour faire votre instruction SQL construite dynamiquement ressembler SQL statique. Mais vous ne devriez jamais ressentir le besoin de les affecter à des variables locales ou de les renvoyer à partir de méthodes. Bien qu’il ne soit pas faux de le faire, il existe presque toujours une meilleure façon d’écrire du SQL dynamique.
  2. En jOOQ, chaque requête est une requête dynamique. C’est l’avantage de composer des requêtes SQL à l’aide d’une arborescence d’expressions comme celle qui est utilisée dans les internes de jOOQ. Vous ne pouvez pas voir l’arborescence des expressions car l’API jOOQ DSL imite la syntaxe des instructions SQL statiques. Mais dans les coulisses, vous construisez efficacement cet arbre d’expression. Chaque partie de l’arborescence d’expression peut être produite dynamiquement, à partir de variables, méthodes ou expressions locales, telles que des expressions conditionnelles. J’ai hâte d’utiliser le nouveau Expressions de commutateur JEP 361 en SQL dynamique. Tout comme un SQL CASE , certaines parties d’instructions SQL peuvent être construites dynamiquement dans le client, avant de les transmettre au serveur.

Une fois ces deux choses internalisées, vous pouvez écrire du SQL dynamique très sophistiqué, notamment en utilisant des approches FP pour construire des structures de données, comme un objet de requête jOOQ.

Close Menu