Calling Procedures with Default Parameters utilizing JDBC or jOOQ – Java, SQL and jOOQ.

Utilizing jOOQ’s code generator to name saved procedures is a well-liked cause to make use of jOOQ. For instance, when you’ve a process like the next Oracle PL/SQL process:

CREATE OR REPLACE PROCEDURE p (
  p_i1 IN quantity,
  p_o1 OUT quantity,
  p_i2 IN varchar2,
  p_o2 OUT varchar2
)
IS
BEGIN
  p_o1 := p_i1;
  p_o2 := p_i2;
END;

jOOQ will generate code so that you can name very merely, like this:

// Configuration comprises your JDBC Connection, and different issues
P outcome = Routines.p(configuration, 1, "A");
System.out.println(p.getPO1());
System.out.println(p.getPO2());

This can execute the next, taking good care of binding all IN and OUT parameters for you:

{ name "TEST"."P" (?, ?, ?, ?) }

The output of this system is:

1
A

Now, what in case you’re including DEFAULT values to your process (or operate) signature?

CREATE OR REPLACE PROCEDURE p (
  p_i1 IN quantity := 1,
  p_o1 OUT quantity,
  p_i2 IN varchar2 := 'A',
  p_o2 OUT varchar2
)
IS
BEGIN
  p_o1 := p_i1;
  p_o2 := p_i2;
END;

In your Java code above, there’s no strategy to omit the parameter of the Routines.p() name, however in case you have a look at the generate implementation of Routines.p(), you may see that that is simply comfort for utilizing positional parameter indexes (as we’re used to do in Java). You’ll be able to at all times instantiate the process name instantly, like this – there’s no technical distinction between the 2 methods to name the process:

P p = new P();
p.setPI1(2);
p.setPI2("B");
p.execute(configuration);
System.out.println(p.getPO1());
System.out.println(p.getPO2());

With the above syntax, you may omit any parameter that you understand is defaulted, e.g.:

P p = new P();
p.setPI1(2);
p.execute(configuration);
System.out.println(p.getPO1());
System.out.println(p.getPO2());

Now, as an alternative of the JDBC escape syntax, jOOQ will render an nameless block like this:

start
  "TEST"."P" ("P_I1" => ?, "P_O1" => ?, "P_O2" => ?)
finish;

Word how P_I2 isn’t being handed explicitly to the process name.

The output is:

2
A

This works on any RDBMS that helps default parameters, every with their very own particular syntax to move parameters by title, together with no less than: