Utilisation de blocs de texte Java 13+ pour SQL simple avec jOOQ – Java, SQL et jOOQ.

Online Coding Courses for Kids

La plupart des utilisateurs de jOOQ utilisent l’API jOOQ DSL, qui offre une sécurité de type de temps de compilation et un moyen facile d’écrire SQL dynamique.

Mais parfois, cette DSL gêne, car il pourrait être

  • Overkill pour quelques requêtes SQL très simples et rapides
  • Trop limité lors de l’exécution de SQL très avancé spécifique au fournisseur, comme Oracle MODEL ou MATCH_RECOGNIZE clauses

Dans de tels cas, vous pouvez toujours bénéficier des nombreuses fonctionnalités secondaires de jOOQ, y compris par exemple sa belle intégration avec l’API Stream, fonctionnalité d’exportation, et beaucoup plus. Considérez jOOQ comme un JDBC amélioré!

À partir de Java 13, lorsque vous activez les fonctionnalités de prévisualisation, vous pouvez désormais utiliser des blocs de texte, ou «chaînes de plusieurs lignes», qui sont très utile pour incorporer des chaînes SQL statiques dans du code Java (et XML, JSON, expressions régulières, etc.). Il existe deux cas d’utilisation principaux pour utiliser des blocs de texte dans jOOQ:

SQL simple

Le cas d’utilisation principal est d’utiliser SQL simple et aussi modèles SQL simples. Par exemple, pour exécuter une requête jOOQ rapide et sale comme celle-ci sur H2 ou PostgreSQL, par exemple:

System.out.println(ctx.fetch("""
        SELECT table_schema, count(*)
        FROM information_schema.tables
        GROUP BY table_schema
        ORDER BY table_schema
        """));

(malheureusement, le surligneur de syntaxe de ce blog n’est pas encore là…)

La sortie est le résultat du texte joker de jOOQ:

+------------------+--------+
|TABLE_SCHEMA      |COUNT(*)|
+------------------+--------+
|INFORMATION_SCHEMA|      33|
|MCVE              |       2|
|PUBLIC            |       1|
+------------------+--------+

Ce qui précède est une simple requête SQL simple, mais nous pouvons également utiliser modèles SQL simples avec des blocs de texte! Et si on voulait avoir une dynamique GROUP BY clause?

En supposant toujours cette importation statique:

import static org.jooq.impl.DSL.*;

On peut maintenant écrire:

Stream.of(
        field("table_schema"),
        list(field("table_schema"), field("table_type")))
    .forEach(q -> {
        System.out.println(ctx.fetch("""
          SELECT {0}, count(*), row_number() OVER (ORDER BY {0}) AS rn
          FROM information_schema.tables
          GROUP BY {0}
          ORDER BY {0}
          """, q));
    });

Nous avons écrit une requête SQL dynamique avec jOOQ, mais sans utiliser beaucoup de sécurité de type (nous pourrions toujours utiliser des références d’objet de schéma générées et sécurisées si nous le voulions). jOOQ modèles SQL simples les capacités sont un ensemble de fonctionnalités très sous-estimé. Les gens utilisent MyBatis depuis de nombreuses années précisément à ces fins, ou ont peut-être construit des cadres basés sur des modèles de vitesse pour produire du SQL dynamique à l’aide de chaînes SQL natives.

Vous pouvez également utiliser jOOQ pour cela et profiter de toutes les API jOOQ, y compris, encore une fois, la sortie formatée de ce qui précède:

+------------------+--------+----+
|TABLE_SCHEMA      |COUNT(*)|  RN|
+------------------+--------+----+
|INFORMATION_SCHEMA|      33|   1|
|MCVE              |       2|   2|
|PUBLIC            |       1|   3|
+------------------+--------+----+

+------------------+------------+--------+----+
|TABLE_SCHEMA      |TABLE_TYPE  |COUNT(*)|  RN|
+------------------+------------+--------+----+
|INFORMATION_SCHEMA|SYSTEM TABLE|      33|   1|
|MCVE              |TABLE       |       1|   2|
|MCVE              |VIEW        |       1|   3|
|PUBLIC            |TABLE       |       1|   4|
+------------------+------------+--------+----+

Tout cela était disponible avant les blocs de texte, mais avec les blocs de texte, il est encore plus logique d’utiliser ces fonctionnalités. Il n’y a pas qu’une seule façon d’utiliser jOOQ, mais cette approche est vraiment très sous-estimée!

L’analyseur

Une autre fonctionnalité jOOQ très importante qui fonctionne avec du SQL basé sur des chaînes est la analyseur jOOQ. Il existe une variété de cas d’utilisation pour utiliser l’analyseur de jOOQ, car nous y ajoutons de plus en plus de fonctionnalités. Un cas d’utilisation est le formatage simple de SQL. Pourquoi voudriez-vous faire ça? Un exemple est de standardiser vos chaînes SQL pour une meilleure utilisation du cache du plan d’exécution. Comme vous le savez peut-être, certains fournisseurs de bases de données (par exemple Oracle) traitent ces deux chaînes SQL comme différentes:

SELECT 1 FROM dual
SELECT  1 FROM dual

Observez la différence d’espaces non pertinente. Oracle analysera une nouvelle chaîne SQL, produira un nouveau SQL_ID et proposera le même plan d’exécution. Vous ne voulez pas que cela se produise trop souvent, car cela crée beaucoup de conflits sur le cache du plan et ses verrous et verrous, et crée un travail supplémentaire.

Cela peut néanmoins arriver avec du SQL dynamique basé sur des chaînes. En utilisant l’analyseur de jOOQ, vous pouvez normaliser toutes sortes de chaînes SQL (y compris la suppression de commentaires)

System.out.println(
    ctx.parser()
       .parseResultQuery("""
            SELECT table_schema, count(*)
            FROM information_schema.tables
            GROUP BY table_schema
            -- Order by column index!
            ORDER BY 1
            """)
       .fetch()
);

La chaîne SQL qui est envoyée au pilote JDBC est la suivante:

select table_schema, count(*) from information_schema.tables group by table_schema order by 1

Alternativement, vous pouvez spécifier une mise en forme dans jOOQ:

DSLContext ctx = DSL.using(connection, 
    new Settings().withRenderFormatted(true));

Et maintenant, la chaîne SQL qui est envoyée au pilote JDBC est la suivante:

select 
  table_schema, 
  count(*)
from information_schema.tables
group by table_schema
order by 1

Vous pouvez modifier les différentes règles de formatage prises en charge et l’utiliser pour enregistrer vos chaînes SQL manuscrites d’une manière lisible par l’homme, par exemple, plutôt que d’exécuter le SQL via jOOQ. Les options sont infinies. Vous pouvez également traduire la sortie de l’analyseur dans un autre dialecte SQL. En supposant que vous exécutez vos requêtes sur SQL Server, mais que vous souhaitez les traduire en H2. Aucun problème! La requête spécifique SQL Server suivante peut également être exécutée facilement sur H2:

System.out.println(
    ctx.parser()
       .parseResultQuery("""
            SELECT TOP 1 table_schema, count(*)
            FROM information_schema.tables
            GROUP BY table_schema
            ORDER BY count(*) DESC
            """)
       .fetch()
);

Le SQL traduit qui s’exécute sur H2 est le suivant:

select 
  table_schema, 
  count(*)
from information_schema.tables
group by table_schema
order by count(*) desc
limit 1

Ou parce que j’aime me montrer:

System.out.println(
    ctx.parser()
       .parseResultQuery("""
            SELECT TOP 1 WITH TIES table_schema, count(*)
            FROM information_schema.tables
            GROUP BY table_schema
            ORDER BY count(*) DESC
            """)
       .fetch()
);

Produire ceci dans H2:

select 
  TABLE_SCHEMA, 
  count(*)
from INFORMATION_SCHEMA.TABLES
group by TABLE_SCHEMA
order by 2 desc
fetch next 1 rows with ties

Ou ceci, dans PostgreSQL:

select 
  "v0" as table_schema, 
  "v1" as "count"
from (
  select 
    table_schema as "v0", 
    count(*) as "v1", 
    rank() over (order by 2 desc) as "rn"
  from information_schema.tables
  group by table_schema
) "x"
where "rn" > 0
and "rn" <= (0 + 1)
order by "rn"

N'est-ce pas?

Fonctionnalités dérivées de l'analyseur

L'analyseur est utilisé dans de plus en plus de nombreuses fonctionnalités de jOOQ. Y compris par exemple dans utilitaire de gestion et de comparaison de schéma de jOOQ 3.13.

Exécutez cela avec jOOQ:

System.out.println(
    ctx.meta("""
    create table t (
      i int
    )
    """).apply("""
    alter table t
      add j int;
    alter table t
      add constraint t_pk primary key (i)
    """)
);

C'est à dire. il vous suffit de copier-coller vos définitions et incréments de schéma SQL simples (tels qu'utilisés dans Flyway, par exemple, ou manuscrites lors de leur développement sur votre base de données), et de commencer à utiliser la chaîne d'outils complète dessus. Par exemple, les impressions ci-dessus:

create table T(
  I int null,
  J int null,
  constraint T_PK
    primary key (I)
);

Au lieu d'appliquer des incréments à un schéma, vous pouvez également calculer la différence entre deux versions de schéma:

System.out.println(
    ctx.meta("""
    create table t (
      i int
    )
    """).migrateTo(ctx.meta("""
    create table t (
      i int,
      j int,
      constraint t_pk primary key (i)
    )
    """))
);

Et maintenant, la sortie est l'incrément que nous avons appliqué plus tôt

alter table T
  add J int null;
alter table T
  add constraint T_PK
    primary key (I);

Conclusion

Le DSL de jOOQ est très puissant car:

  • Il offre une sécurité de type
  • C'est le temps de compilation vérifié
  • Il aide à la complétion automatique

Mais parfois, cela gêne et les chaînes SQL simples fonctionnent mieux, en particulier lors de l'utilisation de blocs de texte. Dans ce cas, jOOQ est toujours aussi très puissant. L'intégralité de la chaîne d'outils jOOQ est toujours à votre disposition dans les coulisses. Vous n'utilisez simplement plus la DSL comme API.


Close Menu