龙空技术网

使用jOOQ的普通SQL的Java 13+文本块(附实例)

Java熬夜党 68

前言:

现时咱们对“oraclemodel语法”大致比较珍视,朋友们都想要知道一些“oraclemodel语法”的相关资讯。那么小编在网摘上网罗了一些对于“oraclemodel语法””的相关内容,希望姐妹们能喜欢,各位老铁们一起来学习一下吧!

大多数jOOQ用户使用jOOQ DSL API,它提供了编译时的类型安全和编写动态SQL的简单方法。 但偶尔,这个DSL会碍事,因为它可能是

对于一些非常简单的快速和肮脏的SQL查询来说,是过犹不及。当运行非常高级的供应商特定的SQL时,例如Oracle的 MODELMATCH_RECOGNIZE 子句,就会受到太多限制。

在这种情况下,你仍然可以从jOOQ的许多次要功能中受益,包括例如 它与Stream API的良好整合,导出功能,以及更多。把jOOQ看作是一个改进的JDBC!从Java 13开始,当启用预览功能时,你现在可以利用文本块,或 "多行字符串",这对在Java代码中嵌入静态SQL字符串(以及XML、JSON、正则表达式等) 非常 有用。在jOOQ中使用文本块有两个主要用例。

纯粹的SQL

主要的用例是使用纯SQL,也是纯SQL模板化。例如,在H2或PostgreSQL上运行这样一个快速而肮脏的jOOQ查询:

<pre class="prettyprint hljs awk" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">System.out.println(ctx.fetch("""        SELECT table_schema, count(*)        FROM information_schema.tables        GROUP BY table_schema        ORDER BY table_schema        """));</pre>

(不幸的是,这个语法高亮器还没有完全到位......) 输出的是jOOQ的格式很好的文本结果:

<pre class="prettyprint hljs ruby" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">+------------------+--------+|TABLE_SCHEMA      |COUNT(*)|+------------------+--------+|INFORMATION_SCHEMA|      33||MCVE              |       2||PUBLIC            |       1|+------------------+--------+</pre>

以上是一个简单的纯SQL查询,但我们也可以利用纯SQL模板的文本块!如果我们想有一个动态的 GROUP BY 子句呢? 总是假设这个静态导入:

<pre class="prettyprint hljs css" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">import static org.jooq.impl.DSL.*;</pre>

我们现在可以写:

<pre class="prettyprint hljs awk" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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));    });</pre>

我们已经用jOOQ写了一个动态SQL查询,但没有使用太多的类型安全(如果我们想的话,我们仍然可以使用一些类型安全的、生成的模式对象引用)。jOOQ的普通SQL模板能力是一个被低估的功能集。人们已经使用MyBatis很多年了,正是为了这些目的,或者也许建立了家庭成长的、基于速度的模板框架,以使用本地SQL字符串产生动态SQL。 你也可以使用jOOQ来实现这一点,并从所有的jOOQ API中获益,包括,再次从上面的格式化输出:

<pre class="prettyprint hljs ruby" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">+------------------+--------+----+|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|+------------------+------------+--------+----+</pre>

所有这些在文本块之前都可以使用,但是有了文本块,使用这些功能就更有意义了。使用jOOQ的方法并不是只有一种,但这种方法确实非常被低估了!

我印象深刻的是你可以一块一块地组成它;例如你可以创建一个条件(例如通过一个工厂方法),并在该上下文中使用paremeters,然后把它组成一个更大的查询,而不用担心任何事情,jOOQ处理一切。

解析器

另一个非常重要的与基于字符串的SQL合作的jOOQ功能是jOOQ解析器。使用jOOQ的解析器有多种用例,因为我们正在为它添加越来越多的功能。一个用例是简单的SQL格式化。你为什么要这样做呢?一个例子是为了标准化你的SQL字符串,以便更好地使用执行计划缓存。你可能知道,一些数据库供应商(例如Oracle)将这两个SQL字符串视为不同的字符串。

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT 1 FROM dualSELECT  1 FROM dual</pre>

观察一下无关紧要的空白处的区别。Oracle会解析一个新的SQL字符串,产生一个新的SQL_ID,但得出的执行计划却是一样的。你不希望这种情况经常发生,因为它给计划缓存及其锁和锁存带来了很多争论,并产生了额外的工作。 然而,这种情况可能发生在基于字符串的动态SQL上。使用jOOQ的解析器,你可以对各种SQL字符串进行规范化处理(包括删除注释):

<pre class="prettyprint hljs awk" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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());</pre>

被发送到JDBC驱动的SQL字符串是这样的:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select table_schema, count(*) from information_schema.tables group by table_schema order by 1</pre>

或者,你可以在jOOQ中指定一些格式化:

<pre class="prettyprint hljs cpp" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">DSLContext ctx = DSL.using(connection,     new Settings().withRenderFormatted(true));</pre>

现在,发送到JDBC驱动的SQL字符串是这样的:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select   table_schema,   count(*)from information_schema.tablesgroup by table_schemaorder by 1</pre>

你可以调整各种支持的格式化规则,并将其用于以人类可读的方式记录你手写的SQL字符串,例如,而不是通过jOOQ执行SQL。选项是无穷无尽的。 你也可以把分析器的输出翻译成其他SQL方言。假设你针对SQL Server运行你的查询,但想把它们翻译成H2。没问题!下面的SQL Server特定查询也可以在H2上轻松运行:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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());</pre>

在H2上执行的翻译后的SQL是这样的:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select   table_schema,   count(*)from information_schema.tablesgroup by table_schemaorder by count(*) desclimit 1</pre>

或者,因为我喜欢炫耀:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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());</pre>

在H2中产生这个:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select   TABLE_SCHEMA,   count(*)from INFORMATION_SCHEMA.TABLESgroup by TABLE_SCHEMAorder by 2 descfetch next 1 rows with ties</pre>

或者这个,在PostgreSQL中:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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" > 0and "rn" <= (0 + 1)order by "rn"</pre>
解析器派生的功能

解析器在jOOQ的许多功能中被使用得越来越多。包括例如在 jOOQ 3.13的模式差异和管理工具中。 用jOOQ运行这个。

<pre class="prettyprint hljs awk" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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)    """));</pre>

即只需复制粘贴你的纯SQL模式定义和增量(例如在Flyway中使用的,或在针对你的数据库开发时手写的),并开始在上面使用整个工具链。例如,上面的打印结果是:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">create table T(  I int null,  J int null,  constraint T_PK    primary key (I));</pre>

而不是对模式应用增量,你也可以计算两个模式版本之间的差异:

<pre class="prettyprint hljs awk" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">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)    )    """)));</pre>

而现在,输出的是我们先前应用的增量:

<pre class="prettyprint hljs sql" style="padding: 0.5em; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: rgb(68, 68, 68); border-radius: 4px; display: block; margin: 0px 0px 1.5em; font-size: 14px; line-height: 1.5em; word-break: break-all; overflow-wrap: break-word; white-space: pre; background-color: rgb(246, 246, 246); border: none; overflow-x: auto; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">alter table T  add J int null;alter table T  add constraint T_PK    primary key (I);</pre>
结论

jOOQ的DSL是非常强大的,因为:

它提供了类型安全它是在编译时检查的它有助于自动完成

但有时,它也会碍事,普通的SQL字符串效果更好,特别是在使用文本块时。在这种情况下,jOOQ也仍然非常强大。整个jOOQ的工具链在幕后仍然可以为你所用。你只是不再把DSL作为API使用了。

标签: #oraclemodel语法