One of many strengths of contemporary RDBMS is the potential to combine the highly effective SQL language with procedural code.
SQL is a 4th era programming language (4GL), and as such, extraordinarily effectively suited to querying and bulk information manipulation. Its functional-declarative nature permits for it to be optimised in extremely environment friendly methods utilizing cost-based optimisation, but in addition statically as we’ve blogged earlier than.
Generally, nevertheless, an crucial 3GL is healthier suited to a given activity. That’s the place saved procedures shine, or extra particularly, procedural languages of RDBMS.
Amongst those that jOOQ helps, at the very least these ones help procedures:
- BigQuery
- Db2
- Exasol
- Firebird
- HANA
- HSQLDB
- Informix
- MariaDB
- MySQL
- Oracle
- PostgreSQL
- SQL Server
- Vertica
Others could do, as effectively, however jOOQ isn’t supporting their dialects but.
Many have carried out their very own procedural languages, some in keeping with the ISO/IEC 9075-4 Persistent saved modules (SQL/PSM) normal, others have their very own.
jOOQ help for procedural logic
Since jOOQ 3.12, our business distributions have supported nameless blocks and the procedural statements they comprise, such because the IF assertion, LOOP statements, and many others. Beginning with jOOQ 3.15, we additionally help 3 sorts of statements to handle storing procedural logic within the catalog:
Utilizing these statements through jOOQ is probably not your each day use-case. You could want managing that logic through the native syntax, which remains to be extra highly effective than what jOOQ 3.15 helps (particularly while you’re utilizing Oracle’s PL/SQL), in case of which you’ll u se jOOQ purely to name your process from Java within the ordinary kind secure method.
However perhaps, you’ve gotten considered one of these use-cases?
- You’re a product vendor, and also you revenue from procedural logic being vendor agnostic with the intention to help a number of of your shoppers’ RDBMS
- Your procedural logic is dynamic, similar to your SQL logic (and what apart from jOOQ to make use of for that?)
- You don’t have the required privileges to create procedures, features, or triggers in your schema
In all of these instances, jOOQ is right here that will help you.
How does it work?
The primary constructing block is the nameless block, which isn’t supported by the entire above dialects, regrettably. jOOQ can emulate it on MySQL as mentioned right here, however not presently in different dialects.
Right here’s a easy, empty nameless block:
-- Db2
BEGIN
END
-- Firebird
EXECUTE BLOCK AS
BEGIN
END
-- MariaDB
BEGIN NOT ATOMIC
END;
-- Oracle
BEGIN
NULL;
END;
-- PostgreSQL
DO $$
BEGIN
NULL;
END;
$$
It doesn’t actually do a lot, however you may strive executing it as follows, with jOOQ:
Now, let’s do one thing extra attention-grabbing, equivalent to:
// Assuming the standard static imports:
import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;
// Then write
Variable<Integer> i = variable(unquotedName("i"), INTEGER);
Desk<?> t = desk(unquotedName("t"));
Discipline<Integer> col = subject(unquotedName("col"), INTEGER);
ctx.start(
declare(i).set(1),
while_(i.le(10)).loop(
insertInto(t).columns(c).values(i),
i.set(i.plus(1))
)
).execute();
The above block executes:
-- 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 quantity(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;
Straightforward as pie. Maybe you like a FOR loop, as an alternative? Do that:
ctx.start(
for_(i).in(1, 10).loop(
insertInto(t).columns(c).values(i)
)
).execute();
It produces the required emulations, if required, as a result of regrettably, not all dialects help 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 vs procedures
In fact, this specific SQL assertion can be higher carried out utilizing a single bulk insertion assertion, purely with SQL, not with procedural logic
ctx.insertInto(t, c)
.choose(selectFrom(generateSeries(1, 10)))
.execute();
Which interprets to:
-- 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 (degree + (1 - 1)) generate_series
FROM DUAL
CONNECT BY degree <= ((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
… however you get the purpose.
Storing the procedural logic
If in case you have the required privileges, and your procedural logic isn’t tremendous dynamic, chances are you’ll select to retailer your logic in a process or operate instantly in your database. In some databases, this implies a compiler will have the ability to eagerly translate the logic to one thing very environment friendly (e.g. machine code), as an alternative of decoding the logic on the fly.
Take the above WHILE loop, for instance. You could wish to retailer that as a process P:
Title 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();
This produces the next statements:
-- 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 quantity(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;
And now, what higher solution to name this process than, once more, an nameless block?
ctx.start(name(unquotedName("p"))).execute();
Producing:
-- 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;
For those who’re utilizing jOOQ in Flyway or Liquibase to generate procedures throughout your database migrations, you may clearly generate jOOQ process stubs to name in a extra kind secure method, as an alternative of the above dynamic process name.
Parsing procedural logic
This jOOQ characteristic is just not actually distinctive. You possibly can mess around with our parser / translator right here: https://www.jooq.org/translate. It will probably undoubtedly enable you translate your (easier) saved procedures between dialects, equivalent to PL/SQL, T-SQL, PL/pgSQL, and many others.
Conclusion
As a rule of thumb, if you are able to do it with SQL (the 4GL), do it with SQL alone. However generally, you may’t. A 3GL is a more sensible choice for an algorithm. When utilizing jOOQ, you’ll naturally consider utilizing Java to implement that 3GL algorithm. However wait, you might transfer the logic to the server for (drastically) elevated efficiency!
Because of jOOQ, you may generate procedural logic that’s:
- Dynamic
- Vendor agnostic
- Nameless or saved
Similar to you’re used to, from jOOQ, for SQL
