The Best Way to Call Stored Procedures from Java: With jOOQ

jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures).

Stored procedures are powerful ways of moving complex data processing logic to the server. This should be done more often than most applications are doing it for performance reasons. See e.g. this article about saving server roundtrips. But it can also work as a practical way to offer APIs to clients and hide the SQL based details (e.g. schema, table structures, transaction scripts, etc.) from clients if that’s a useful thing in an application / team.

In any case, jOOQ will greatly help you by generating stubs for all functions, procedures, packages, UDTs, etc.

An example JDBC procedure call

A simple example procedure in Oracle would 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 uses IN, OUT, and IN OUT parameters. When calling this procedure with JDBC, we’d have to write something like this:

try (CallableStatement s = c.prepareCall(
    "{ call my_proc(?, ?, ?, ?, ?, ?) }"
)) {

    // Set all input 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 types
    s.registerOutParameter(2, Types.INTEGER); // io1
    s.registerOutParameter(3, Types.INTEGER); // o1
    s.registerOutParameter(4, Types.INTEGER); // o2
    s.registerOutParameter(5, Types.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 approach suffers from various problems:

• The usual parameter index is error prone. If you’re adding one more parameter, the indexes shift and that’s hard to manage. You could use named parameters, but then you could still have typos, and not all JDBC drivers support this. They all support indexed parameters, though.
• There’s no obvious distinction between IN, IN OUT, and OUT parameters in the API. You have to know which parameter has which mode. The JDBC API doesn’t help you here.
• You also have to know what parameter is of which type and get this right

There are many other caveats and details, but these are the most important ones.

Using jOOQ generated code

jOOQ’s code generator just generates a stub for this procedure. Or rather, 2 stubs. A class modelling the call with parameters, and a convenience method that allows for calling the procedure in a single method call. This is what it looks like:

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

    // [...]
    private static final long serialVersionUID = 1L;

    public void setI1(Number value) {
        setNumber(I1, value);
    }

    public void setIo1(Number value) {
        setNumber(IO1, value);
    }

    public void setIo2(Number value) {
        setNumber(IO2, value);
    }

    public void setI2(Number value) {
        setNumber(I2, value);
    }

    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 uses Number for input values and BigDecimal for output values to bind to the NUMBER type. Other RDBMS support INTEGER types, in case that’s more what your code uses. You can obviously use forced types, just like with tables, to rewrite the data type definitions in the jOOQ code generator.

So, one way to call the procedure is now:

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

// Use the usual jOOQ configuration, e.g. the one configured by
// Spring Boot, etc.
call.execute(configuration);

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

That’s already quite simple and allows for dynamic calls to procedures. Now, in most cases, jOOQ will also generate a convenience method that allows for calling this procedure in a 1-liner. The generated convenience method looks like this:

public class Routines {
    // [...]

    public static MyProc myProc(
          Configuration configuration
        , Number i1
        , Number io1
        , Number io2
        , Number 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 input parameters for you, so you can call it like this:

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

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

The two ways to call the procedure are equivalent, although, the first approach also supports defaulted parameters, in case you use that in your procedure definition

Other features

The previous example showed the most common usage of this jOOQ feature along with stored procedures. There’s much more, which I will discuss in follow-up blog posts, soon, including:

• Defaulted parameters
• Scalar functions used embedded in jOOQ SQL statements
• Table valued functions used embedded in jOOQ SQL statements (including PIPELINED functions)
• Cursors returned from stored procedures (both 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 types
• Oracle AQ calls and their message UDTs
• Microsoft T-SQL table valued parameters

All of these things and more are supported by jOOQ, so stay tuned for more.

Coming from JPublisher

In the old days, Oracle users may have used JPublisher to bind to their stored procedures. You will be delighted to know that you won’t miss much when you migrate to jOOQ! Give it a try.