使用jOOQ的供应商不可知的动态程序逻辑

现代RDBMS的优势之一是能够将强大的SQL语言与程序代码混合起来。

SQL是第四代编程语言（4GL），因此非常适用于查询和批量数据操作。它的功能声明性质允许它以高效的方式使用基于成本的优化，但也有静态的优化，正如我们之前在博客中所说。

然而，有时候，一个命令式的3GL更适合于一个特定的任务。这就是存储程序的魅力所在，或者更确切地说，RDBMS的过程性语言。

在jOOQ支持的语言中，至少这些语言支持程序：

• BigQuery
• Db2
• Exasol
• Firebird
• HANA
• HSQLDB
• Informix
• MariaDB
• MySQL
• Oracle
• PostgreSQL
• SQL Server
• Vertica

其他的可能也是如此，但jOOQ还不支持他们的方言。

许多人已经实现了他们自己的过程性语言，有些是根据ISO/IEC 9075-4持久性存储模块（SQL/PSM）标准，有些则有自己的。

jOOQ对过程性逻辑的支持

从jOOQ 3.12开始，我们的商业发行版已经支持匿名块和它们所包含的过程性语句，如IF 语句、LOOP 语句等。从jOOQ 3.15开始，我们还支持3种类型的语句来管理存储在目录中的过程性逻辑：

• [CREATE PROCEDURE](https://www.jooq.org/doc/latest/manual/sql-building/ddl-statements/create-statement/create-procedure-statement/)
• [CREATE FUNCTION](https://www.jooq.org/doc/latest/manual/sql-building/ddl-statements/create-statement/create-function-statement/)
• [CREATE TRIGGER](https://www.jooq.org/doc/latest/manual/sql-building/ddl-statements/create-statement/create-trigger-statement/)

通过jOOQ使用这些语句可能不是你每天的用例。你可能更喜欢通过本地语法来管理这些逻辑，这仍然比jOOQ 3.15所支持的更强大（特别是当你使用Oracle的PL/SQL时），在这种情况下，你将纯粹使用jOOQ来以通常的类型安全方式从Java调用你的过程。

但是，也许你也有这样的使用情况？

• 你是一个产品供应商，为了支持你的客户的多个RDBMS，你从过程逻辑的供应商无关性中获益。
• 你的程序逻辑是动态的，就像你的SQL逻辑一样（除了jOOQ，还有什么可以用来做这个？）
• 您没有必要的权限来创建模式中的程序、函数或触发器

在所有这些情况下，jOOQ都能帮助您。

它是如何工作的？

第一个构件是匿名块，遗憾的是，上述所有方言都不支持匿名块。jOOQ可以在MySQL上模拟它，正如这里所讨论的，但目前在其他方言中不支持。

这里有一个简单的、空的匿名块：

-- Db2
BEGIN
END

-- Firebird
EXECUTE BLOCK AS
BEGIN
END

-- MariaDB
BEGIN NOT ATOMIC
END;

-- Oracle
BEGIN
  NULL;
END;

-- PostgreSQL
DO $$
BEGIN
  NULL;
END;
$$

它其实并没有做什么，但你可以尝试用jOOQ执行它，如下所示：

ctx.begin().execute();

现在，让我们做一些更有趣的事情，比如说：

// Assuming the usual static imports:
import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

// Then write
Variable<Integer> i = variable(unquotedName("i"), INTEGER);
Table<?> t = table(unquotedName("t"));
Field<Integer> col = field(unquotedName("col"), INTEGER);

ctx.begin(
    declare(i).set(1),

    while_(i.le(10)).loop(
        insertInto(t).columns(c).values(i),
        i.set(i.plus(1))
    )
).execute();

上面的块执行了：

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END

-- FIREBIRD
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
BEGIN NOT ATOMIC
  DECLARE i int;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Oracle
DECLARE
  i number(10);
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;

-- PostgreSQL
DO $$
DECLARE
  i int;
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;
$$

-- SQL Server
BEGIN
  DECLARE @i int;
  SET @i = 1;
  WHILE @i <= 10 BEGIN
    INSERT INTO t (c)
    VALUES (@i);
    SET @i = (@i + 1);
  END;
END;

易如反掌。也许你更喜欢一个FOR 循环，而不是？试试这个：

ctx.begin(
    for_(i).in(1, 10).loop(
        insertInto(t).columns(c).values(i)
    )
).execute();

如果需要的话，它可以产生必要的模拟，因为很遗憾，不是所有的方言都支持FOR ：

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END

-- Firebird
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
BEGIN NOT ATOMIC
  FOR i IN 1 .. 10 DO
    INSERT INTO t (c)
    VALUES (i);
  END FOR;
END;

-- Oracle
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;

-- PostgreSQL
DO $$
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;
$$

-- SQL Server
BEGIN
  DECLARE @i int;
  BEGIN
    SET @i = 1;
    WHILE @i <= 10 BEGIN
      INSERT INTO t (c)
      VALUES (@i);
      SET @i = (@i + 1);
    END;
  END;
END;

SQL与程序

当然，这个特殊的SQL语句最好用一个单一的批量插入语句来实现，纯粹是用SQL，而不是用程序逻辑

ctx.insertInto(t, c)
   .select(selectFrom(generateSeries(1, 10)))
   .execute();

这可以翻译成：

-- Db2
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH
    generate_series(generate_series) AS (
      SELECT 1
      FROM SYSIBM.DUAL
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) generate_series;

-- Firebird
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH RECURSIVE
    generate_series(generate_series) AS (
      SELECT 1
      FROM RDB$DATABASE
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) generate_series;

-- MariaDB
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH RECURSIVE
    generate_series(generate_series) AS (
      SELECT 1
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) AS generate_series;

-- Oracle
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  SELECT (level + (1 - 1)) generate_series
  FROM DUAL
  CONNECT BY level <= ((10 + 1) - 1)
) generate_series;

-- PostgreSQL
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM generate_series(1, 10);

-- SQL Server
WITH
  generate_series(generate_series) AS (
    SELECT 1
    UNION ALL
    SELECT (generate_series + 1)
    FROM generate_series
    WHERE generate_series < 10
  )
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  SELECT generate_series
  FROM generate_series
) generate_series

......但你会明白的。

储存程序性逻辑

如果你有必要的权限，而且你的程序性逻辑不是超级动态的，你可以选择将你的逻辑直接存储在数据库的过程或函数中。在一些数据库中，这意味着编译器能够急切地将逻辑转化为非常有效的东西（例如机器代码），而不是在飞行中解释逻辑。

以上面的WHILE 循环为例。你可能想把它存储为一个过程P ：

Name p = unquotedName("p");

ctx.createProcedure(p)
   .modifiesSQLData()
   .as(
        declare(i).set(1),

        while_(i.le(10)).loop(
            insertInto(t).columns(c).values(i),
            i.set(i.plus(1))
        )
   )
   .execute();

这将产生以下语句：

-- Db2
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Firebird
CREATE PROCEDURE p()
AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
  DECLARE i int;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Oracle
CREATE PROCEDURE p
AS
  i number(10);
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;

-- PostgreSQL
CREATE PROCEDURE p()
LANGUAGE plpgsql
AS
$$
DECLARE
  i int;
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;
$$

-- SQL Server
CREATE PROCEDURE p
AS
BEGIN
  DECLARE @i int;
  SET @i = 1;
  WHILE @i <= 10 BEGIN
    INSERT INTO t (c)
    VALUES (@i);
    SET @i = (@i + 1);
  END;
END;

而现在，还有什么比匿名块更好的方式来调用这个过程呢？

ctx.begin(call(unquotedName("p"))).execute();

生成：

-- Db2
BEGIN
  CALL p();
END

-- Firebird
EXECUTE BLOCK AS
BEGIN
  EXECUTE PROCEDURE p;
END

-- MariaDB
BEGIN NOT ATOMIC
  CALL p();
END;

-- Oracle
BEGIN
  p();
END;

-- PostgreSQL
DO $$
BEGIN
  CALL p();
END;
$$

-- SQL Server
BEGIN
  EXEC p ;
END;

如果你在Flyway或Liquibase中使用jOOQ来在你的数据库迁移中生成过程，你显然可以生成jOOQ过程存根，以更安全的方式调用，而不是上面的动态过程调用。

解析程序性逻辑

这个jOOQ功能其实并不特别。你可以在这里玩一玩我们的解析器/翻译器：https://www.jooq.org/translate。它绝对可以帮助你在方言之间翻译你的（较简单的）存储过程，如PL/SQL、T-SQL、PL/pgSQL等。

总结

作为一个经验法则，如果你能用SQL（4GL）来做，就只用SQL来做。但有时，你不能这样做。对于一个算法来说，3GL是一个更好的选择。当使用jOOQ时，你自然会想到用Java来实现那个3GL算法。但等等，你可以把逻辑移到服务器上，以获得（大幅）提高的性能

感谢jOOQ，你可以生成程序化的逻辑，即：

• 动态的
• 与供应商无关
• 匿名或存储

就像你所习惯的那样，从jOOQ，对于SQL