With jOOQ – Java, SQL and jOOQ.

jOOQ is especially recognized for its highly effective sort protected, embedded, dynamic SQL capabilities which can be made out there by means of code era. Nonetheless, a secondary use case of code era is to make use of it for saved procedures (presumably completely for saved procedures).

Saved procedures are highly effective methods of transferring advanced information processing logic to the server. This must be carried out extra usually than most purposes are doing it for efficiency causes. See e.g. this text about saving server roundtrips. However it could possibly additionally work as a sensible approach to supply APIs to purchasers and conceal the SQL primarily based particulars (e.g. schema, desk buildings, transaction scripts, and so on.) from purchasers if that’s a helpful factor in an utility / workforce.

In any case, jOOQ will significantly assist you by producing stubs for all features, procedures, packages, UDTs, and so on.

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 put in writing 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, Varieties.INTEGER); // io1
    s.registerOutParameter(3, Varieties.INTEGER); // o1
    s.registerOutParameter(4, Varieties.INTEGER); // o2
    s.registerOutParameter(5, Varieties.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 numerous issues:

• The same old parameter index is error susceptible. Should you’re including another parameter, the indexes shift and that’s exhausting to handle. You can use named parameters, however then you would 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 must know what parameter is of which kind and get this proper

There are lots of 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 somewhat, 2 stubs. A category modelling the decision with parameters, and a comfort methodology that permits for calling the process in a single methodology 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’ll be able to clearly use compelled varieties, similar to with tables, to rewrite the info sort definitions within the jOOQ code generator.

So, one approach 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 so on.
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, most often, jOOQ may even generate a comfort methodology that permits for calling this process in a 1-liner. The generated comfort methodology 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 may name it like this:

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

System.out.println("io1 = " + end result.getIo1());
System.out.println("o1 = " + end result.getO1());
System.out.println("o2 = " + end result.getO2());
System.out.println("io2 = " + end result.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 rather 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.