[ad_1]
jOOQ is especially recognized for its highly effective kind protected, embedded, dynamic SQL capabilities which are made out there via code technology. Nonetheless, a secondary use case of code technology is to make use of it for saved procedures (probably solely for saved procedures).
Saved procedures are highly effective methods of shifting advanced knowledge processing logic to the server. This needs to be performed extra usually than most functions are doing it for efficiency causes. See e.g. this text about saving server roundtrips. However it could additionally work as a sensible method to provide APIs to purchasers and conceal the SQL based mostly particulars (e.g. schema, desk constructions, transaction scripts, and many others.) from purchasers if that’s a helpful factor in an utility / crew.
In any case, jOOQ will vastly provide help to by producing stubs for all features, procedures, packages, UDTs, and many others.
An instance JDBC process name
A easy instance process in Oracle can 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 makes use of IN, OUT, and IN OUT parameters. When calling this process with JDBC, we’d have to put in writing one thing like this:
strive (CallableStatement s = c.prepareCall(
"{ name my_proc(?, ?, ?, ?, ?, ?) }"
)) {
// Set all enter 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 sorts
s.registerOutParameter(2, Sorts.INTEGER); // io1
s.registerOutParameter(3, Sorts.INTEGER); // o1
s.registerOutParameter(4, Sorts.INTEGER); // o2
s.registerOutParameter(5, Sorts.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 method suffers from numerous issues:
- The standard parameter index is error inclined. In the event you’re including yet one more parameter, the indexes shift and that’s onerous to handle. You would use named parameters, however then you would nonetheless have typos, and never all JDBC drivers assist this. All of them assist listed parameters, although.
- There’s no apparent distinction between
IN,IN OUT, andOUTparameters within the API. You must know which parameter has which mode. The JDBC API doesn’t provide help to right here. - You additionally must know what parameter is of which sort and get this proper
There are lots of different caveats and particulars, however these are a very powerful ones.
Utilizing jOOQ generated code
jOOQ’s code generator simply generates a stub for this process. Or reasonably, 2 stubs. A category modelling the decision with parameters, and a comfort methodology that permits for calling the process in a single methodology name. That is what it appears like:
// Generated code
public class MyProc extends AbstractRoutine<java.lang.Void> {
// [...]
non-public static closing lengthy serialVersionUID = 1L;
public void setI1(Quantity worth) {
setNumber(I1, worth);
}
public void setIo1(Quantity worth) {
setNumber(IO1, worth);
}
public void setIo2(Quantity worth) {
setNumber(IO2, worth);
}
public void setI2(Quantity worth) {
setNumber(I2, worth);
}
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 makes use of Quantity for enter values and BigDecimal for output values to bind to the NUMBER kind. Different RDBMS assist INTEGER sorts, in case that’s extra what your code makes use of. You’ll be able to clearly use pressured sorts, identical to with tables, to rewrite the info kind definitions within the jOOQ code generator.
So, one method to name the process is now:
MyProc name = new MyProc();
name.setI1(1);
name.setIo1(2);
name.setIo2(5);
name.setI2(6);
// Use the standard jOOQ configuration, e.g. the one configured by
// Spring Boot, and many others.
name.execute(configuration);
System.out.println("io1 = " + name.getIo1());
System.out.println("o1 = " + name.getO1());
System.out.println("o2 = " + name.getO2());
System.out.println("io2 = " + name.getIo2());
That’s already fairly easy and permits for dynamic calls to procedures. Now, typically, jOOQ may also generate a comfort methodology that permits for calling this process in a 1-liner. The generated comfort methodology appears like this:
public class Routines {
// [...]
public static MyProc myProc(
Configuration configuration
, Quantity i1
, Quantity io1
, Quantity io2
, Quantity 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 enter parameters for you, so you possibly can name it like this:
MyProc end result = Routines.myProc(configuration, 1, 2, 5, 6);
System.out.println("io1 = " + end result.getIo1());
System.out.println("o1 = " + end result.getO1());
System.out.println("o2 = " + end result.getO2());
System.out.println("io2 = " + end result.getIo2());
The 2 methods to name the process are equal, though, the primary method additionally helps defaulted parameters, in case you utilize that in your process definition
Different options
The earlier instance confirmed the commonest utilization of this jOOQ function together with saved procedures. There’s way more, which I’ll talk about in follow-up weblog posts, quickly, together with:
All of this stuff and extra are supported by jOOQ, so keep tuned for extra.
Coming from JPublisher
Within the previous days, Oracle customers could have used JPublisher to bind to their saved procedures. You may be delighted to know that you just received’t miss a lot whenever you migrate to jOOQ! Give it a strive.
[ad_2]
