With jOOQ – Java, SQL and jOOQ.

jOOQ is principally identified for its highly effective kind secure, embedded, dynamic SQL capabilities which might be made accessible by means of code era. Nevertheless, 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 transferring complicated information processing logic to the server. This ought to be carried out extra typically than most functions are doing it for efficiency causes. See e.g. this text about saving server roundtrips. However it may possibly additionally work as a sensible technique to provide APIs to shoppers and conceal the SQL based mostly particulars (e.g. schema, desk buildings, transaction scripts, and so forth.) from shoppers if that’s a helpful factor in an software / group.

In any case, jOOQ will drastically enable you to by producing stubs for all features, procedures, packages, UDTs, and so forth.

An instance JDBC process name

A easy instance process in Oracle can 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 strategy suffers from numerous issues:

• The standard parameter index is error susceptible. For those who’re including another parameter, the indexes shift and that’s exhausting to handle. You would use named parameters, however then you could possibly 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 must know which parameter has which mode. The JDBC API doesn’t enable you to right here.
• You additionally should know what parameter is of which sort and get this proper

There are a lot of different caveats and particulars, however these are an important ones.

Utilizing jOOQ generated code

jOOQ’s code generator simply generates a stub for this process. Or reasonably, 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 like:

// Generated code
public class MyProc extends AbstractRoutine {

    // [...]
    non-public static remaining 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 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 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 standard jOOQ configuration, e.g. the one configured by
// Spring Boot, and so forth.
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, generally, jOOQ may even generate a comfort methodology that permits for calling this process in a 1-liner. The generated comfort methodology appears 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 outcome = Routines.myProc(configuration, 1, 2, 5, 6);

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

The 2 methods to name the process are equal, though, the primary strategy 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:

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

Coming from JPublisher

Within the previous days, Oracle customers might have used JPublisher to bind to their saved procedures. You’ll be delighted to know that you just gained’t miss a lot whenever you migrate to jOOQ! Give it a strive.