SQL procedure
Let’s check some function/procedure examples
Simple function
Add addFn = new Add(); (1)
addFn.set__1(5); (2)
addFn.set__2(10);
jooqx.routine(addFn).onSuccess(output -> { (3)
assert 15 == output;
}).onFailure(Throwable::printStackTrace); (4)
With out-parameters
Dup dup = new Dup(); (1)
// Routine has 1 in-parameter and 2 out-parameters
dup.set__1(5); (2)
jooqx.routineResult(dup).onSuccess(rr -> {
Record record = rr.getRecord(); (3)
Field<?>[] outputFields = rr.getOutputFields(); (4)
assert Objects.equals(Arrays.toString(outputFields), "[\"f1\", \"f2\"]");
assert record.fields().length == 2;
assert 5 == (Integer) record.get(outputFields[0]);
assert Objects.equals(record.get(outputFields[1]), "5 is text");
assert Objects.equals(record.toString(), """
+----+----------+
| f1|f2 |
+----+----------+
| *5|*5 is text|
+----+----------+
"""); (5)
}).onFailure(Throwable::printStackTrace); (6)
With output is result set
jooqx.fetchMany(dsl -> dsl.selectFrom(Tables.FIND_AUTHORS.call("Sc"))).onSuccess(rows -> {
assert Objects.equals(rows.toString(), """
[+----+---------------------+-------+
| id|name |country|
+----+---------------------+-------+
| *2|*F. Scott. Fitzgerald|*USA |
+----+---------------------+-------+
, +----+----------------+-------+
| id|name |country|
+----+----------------+-------+
| *4|*Scott Hanselman|*USA |
+----+----------------+-------+
]
""");
FindAuthorsRecord rec1 = rows.get(0);
assert rec1.getId() == 2;
assert Objects.equals(rec1.getName(), "F. Scott. Fitzgerald");
}).onFailure(Throwable::printStackTrace);