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 have 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 outcome = Routines.p(configuration, 1, "A");
System.out.println(p.getPO1());
System.out.println(p.getPO2());

This can execute the next, caring for binding all IN and OUT parameters for you:

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

The output of this system is:

1
A

Now, what for those who’re including DEFAULT values to your process (or perform) 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 for those who have a look at the generate implementation of Routines.p(), you’ll be able to 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 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’ll be able to omit any parameter that you realize 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;

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 go parameters by title, together with at the very least:

• Db2
• Informix
• Oracle
• PostgreSQL
• SQL Server