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 eg 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 (eg 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 procedure
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;
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
OUTparameters 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 modeling 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 typesjust 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
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:
- Scalar functions used embedded in jOOQ SQL statements
- Table valued functions used embedded in jOOQ SQL statements (including
- Cursors returned from stored procedures (both declared as
REF CURSORand undeclared)
- Oracle PL/SQL packages
- Oracle PL/SQL UDTs and their member procedures
- Oracle PL/SQL
RECORDand associative array types
- Microsoft T-SQL table valued parameters
- Microsoft T-SQL
All of these things and more are supported by jOOQ, so stay tuned for more.