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 motive to make use of jOOQ. For instance, when you’ve got 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 consequence = 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 if you happen to’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 option to omit the parameter of the Routines.p() name, however if you happen to take 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 possibly can at all times instantiate the process name immediately, 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 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 a substitute of the JDBC escape syntax, jOOQ will render an nameless block like this:

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

Notice 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 a minimum of:

• Db2
• Informix
• Oracle
• PostgreSQL
• SQL Server