With jOOQ – Java, SQL and jOOQ.

jOOQ is principally identified for its highly effective sort secure, embedded, dynamic SQL capabilities which are made obtainable via code era. Nonetheless, a secondary use case of code era is to make use of it for saved procedures (presumably solely for saved procedures).

Saved procedures are highly effective methods of shifting advanced knowledge processing logic to the server. This ought to be finished extra typically than most functions are doing it for efficiency causes. See e.g. this text about saving server roundtrips. However it may additionally work as a sensible solution to supply APIs to purchasers and conceal the SQL based mostly particulars (e.g. schema, desk buildings, transaction scripts, and many others.) from purchasers if that’s a helpful factor in an utility / crew.

In any case, jOOQ will enormously assist you by producing stubs for all features, procedures, packages, UDTs, and many others.

An instance JDBC process name

A easy instance process in Oracle could be this one:

CREATE OR REPLACE PROCEDURE my_proc (
  i1 NUMBER,
  io1 IN OUT NUMBER,
  o1 OUT NUMBER,
  o2 OUT NUMBER,
  io2 IN OUT NUMBER,
  i2 NUMBER
) IS
BEGIN
  o1 := io1;
  io1 := i1;

  o2 := io2;
  io2 := i2;
END my_proc;

It makes use of IN, OUT, and IN OUT parameters. When calling this process with JDBC, we’d have to write down one thing like this:

strive (CallableStatement s = c.prepareCall(
    "{ name my_proc(?, ?, ?, ?, ?, ?) }"
)) {

    // Set all enter values
    s.setInt(1, 1); // i1
    s.setInt(2, 2); // io1
    s.setInt(5, 5); // io2
    s.setInt(6, 6); // i2

    // Register all output values with their varieties
    s.registerOutParameter(2, Sorts.INTEGER); // io1
    s.registerOutParameter(3, Sorts.INTEGER); // o1
    s.registerOutParameter(4, Sorts.INTEGER); // o2
    s.registerOutParameter(5, Sorts.INTEGER); // io2

    s.executeUpdate();

    System.out.println("io1 = " + s.getInt(2));
    System.out.println("o1 = " + s.getInt(3));
    System.out.println("o2 = " + s.getInt(4));
    System.out.println("io2 = " + s.getInt(5));
}

That method suffers from varied issues:

• The standard parameter index is error susceptible. In case you’re including yet another parameter, the indexes shift and that’s exhausting to handle. You might use named parameters, however then you might nonetheless have typos, and never all JDBC drivers assist this. All of them assist listed parameters, although.
• There’s no apparent distinction between IN, IN OUT, and OUT parameters within the API. You need to know which parameter has which mode. The JDBC API doesn’t assist you right here.
• You additionally need to know what parameter is of which kind and get this proper

There are various different caveats and particulars, however these are crucial ones.

Utilizing jOOQ generated code

jOOQ’s code generator simply generates a stub for this process. Or relatively, 2 stubs. A category modelling the decision with parameters, and a comfort technique that permits for calling the process in a single technique name. That is what it appears to be like like:

// Generated code
public class MyProc extends AbstractRoutine<java.lang.Void> {

    // [...]
    non-public static last lengthy serialVersionUID = 1L;

    public void setI1(Quantity worth) {
        setNumber(I1, worth);
    }

    public void setIo1(Quantity worth) {
        setNumber(IO1, worth);
    }

    public void setIo2(Quantity worth) {
        setNumber(IO2, worth);
    }

    public void setI2(Quantity worth) {
        setNumber(I2, worth);
    }

    public BigDecimal getIo1() {
        return get(IO1);
    }

    public BigDecimal getO1() {
        return get(O1);
    }

    public BigDecimal getO2() {
        return get(O2);
    }

    public BigDecimal getIo2() {
        return get(IO2);
    }
}

The Oracle generated code makes use of Quantity for enter values and BigDecimal for output values to bind to the NUMBER sort. Different RDBMS assist INTEGER varieties, in case that’s extra what your code makes use of. You may clearly use pressured varieties, identical to with tables, to rewrite the information sort definitions within the jOOQ code generator.

So, one solution to name the process is now:

MyProc name = new MyProc();
name.setI1(1);
name.setIo1(2);
name.setIo2(5);
name.setI2(6);

// Use the same old jOOQ configuration, e.g. the one configured by
// Spring Boot, and many others.
name.execute(configuration);

System.out.println("io1 = " + name.getIo1());
System.out.println("o1 = " + name.getO1());
System.out.println("o2 = " + name.getO2());
System.out.println("io2 = " + name.getIo2());

That’s already fairly easy and permits for dynamic calls to procedures. Now, normally, jOOQ may also generate a comfort technique that permits for calling this process in a 1-liner. The generated comfort technique appears to be like like this:

public class Routines {
    // [...]

    public static MyProc myProc(
          Configuration configuration
        , Quantity i1
        , Quantity io1
        , Quantity io2
        , Quantity i2
    ) {
        MyProc p = new MyProc();
        p.setI1(i1);
        p.setIo1(io1);
        p.setIo2(io2);
        p.setI2(i2);

        p.execute(configuration);
        return p;
    }
}

So, it does the plumbing of enter parameters for you, so you possibly can name it like this:

MyProc consequence = Routines.myProc(configuration, 1, 2, 5, 6);

System.out.println("io1 = " + consequence.getIo1());
System.out.println("o1 = " + consequence.getO1());
System.out.println("o2 = " + consequence.getO2());
System.out.println("io2 = " + consequence.getIo2());

The 2 methods to name the process are equal, though, the primary method additionally helps defaulted parameters, in case you utilize that in your process definition

Different options

The earlier instance confirmed the commonest utilization of this jOOQ characteristic together with saved procedures. There’s far more, which I’ll talk about in follow-up weblog posts, quickly, together with:

• Scalar features used embedded in jOOQ SQL statements
• Desk valued features used embedded in jOOQ SQL statements (together with PIPELINED features)
• Cursors returned from saved procedures (each declared as REF CURSOR and undeclared)
• Oracle PL/SQL packages
• Oracle PL/SQL UDTs and their member procedures
• Oracle PL/SQL TABLE, RECORD and associative array varieties
• Microsoft T-SQL desk valued parameters
• Microsoft T-SQL

All of this stuff and extra are supported by jOOQ, so keep tuned for extra.