前言:
现时咱们对“oraclemodel语法”大致比较珍视,朋友们都想要知道一些“oraclemodel语法”的相关资讯。那么小编在网摘上网罗了一些对于“oraclemodel语法””的相关内容,希望姐妹们能喜欢,各位老铁们一起来学习一下吧!大多数jOOQ用户使用jOOQ DSL API,它提供了编译时的类型安全和编写动态SQL的简单方法。 但偶尔,这个DSL会碍事,因为它可能是
对于一些非常简单的快速和肮脏的SQL查询来说,是过犹不及。当运行非常高级的供应商特定的SQL时,例如Oracle的 MODEL 或 MATCH_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语法