With jOOQ – Java, SQL and jOOQ.

jOOQ is principally recognized for its highly effective kind protected, embedded, dynamic SQL capabilities which might be made out there by means of code technology. Nonetheless, a secondary use case of code technology is to make use of it for saved procedures (presumably solely for saved procedures).

Saved procedures are highly effective methods of transferring complicated knowledge processing logic to the server. This must be carried out extra usually than most functions are doing it for efficiency causes. See e.g. this text about saving server roundtrips. However it may well additionally work as a sensible technique to supply APIs to purchasers and conceal the SQL based mostly particulars (e.g. schema, desk constructions, transaction scripts, and many others.) from purchasers if that’s a helpful factor in an software / staff.

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

An instance process

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 jot down one thing like this:

attempt (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 sorts
    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 strategy suffers from varied issues:

• The same old parameter index is error inclined. If you happen to’re including another parameter, the indexes shift and that’s arduous to handle. You may use named parameters, however then you would nonetheless have typos, and never all JDBC drivers help this. All of them help 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 aid you right here.
• You additionally should know what parameter is of which sort 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 fairly, 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 seems like:

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

    // [...]
    personal 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 kind. Different RDBMS help INTEGER sorts, in case that’s extra what your code makes use of. You’ll be able to clearly use pressured sorts, similar to with tables, to rewrite the info kind definitions within the jOOQ code generator.

So, one technique 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, usually, jOOQ will even generate a comfort methodology that permits for calling this process in a 1-liner. The generated comfort methodology seems 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’ll be able to 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 strategy additionally helps defaulted parameters, in case you employ 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 sorts
• Microsoft T-SQL desk valued parameters
• Microsoft T-SQL

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