jOOQ Will Support SQL Server’s Table-Valued Parameters
Table-valued parameters can lend a hand when it comes to bulk processing. They're great for SQL Server, and jOOQ will soon begin supporting them, too.
Join the DZone community and get the full member experience.
Join For FreeSQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance:
CREATE TYPE numbers AS TABLE (i INTEGER);
CREATE FUNCTION cross_multiply (
@numbers numbers READONLY
)
RETURNS @result TABLE (
i1 INTEGER,
i2 INTEGER,
product INTEGER
)
AS
BEGIN
INSERT INTO @result
SELECT n1.i, n2.i, n1.i * n2.i
FROM @numbers n1
CROSS JOIN @numbers n2
RETURN
END
The above function creates a cross product of a table with itself and multiplies each possible combination. So, when calling this with the following table argument:
DECLARE @arg NUMBERS;
INSERT INTO @arg VALUES (1),(2),(3),(4);
SELECT * FROM cross_multiply(@arg);
i1i2product
-----------------------
111
212
313
414
122
224
326
428
133
236
339
4312
144
248
3412
4416
Easy, eh?
Now let's call the above from Java, with jOOQ.
No problem with jOOQ 3.10. Don’t worry about the boring JDBC data type binding details, as the jOOQ code generator has you covered. As always, all routines are generated classes/methods, and this time, the TABLE
type is also a generated type. Let the code speak for itself. Instead of this SQL statement:
DECLARE @arg NUMBERS;
INSERT INTO @arg VALUES (1),(2),(3),(4);
SELECT * FROM cross_multiply(@arg);
Numbers numbers = new NumbersRecord(
new NumbersElementTypeRecord(1),
new NumbersElementTypeRecord(2),
new NumbersElementTypeRecord(3),
new NumbersElementTypeRecord(4)
);
// Standalone function call:
Result<CrossMultiplyRecord> r1 =
crossMultiply(configuration, numbers);
// Embedded table-valued function call, with predicate
Result<CrossMultiplyRecord> r2 =
DSL.using(configuration)
.selectFrom(crossMultiply(numbers))
.where(F_CROSS_MULTIPLY.PRODUCT.gt(5))
.fetch();
System.out.println(r1);
System.out.println(r2);
+----+----+-------+
| i1| i2|product|
+----+----+-------+
| 1| 1| 1|
| 2| 1| 2|
| 3| 1| 3|
| 4| 1| 4|
| 1| 2| 2|
| 2| 2| 4|
| 3| 2| 6|
| 4| 2| 8|
| 1| 3| 3|
| 2| 3| 6|
| 3| 3| 9|
| 4| 3| 12|
| 1| 4| 4|
| 2| 4| 8|
| 3| 4| 12|
| 4| 4| 16|
+----+----+-------+
+----+----+-------+
| i1| i2|product|
+----+----+-------+
| 3| 2| 6|
| 4| 2| 8|
| 2| 3| 6|
| 3| 3| 9|
| 4| 3| 12|
| 2| 4| 8|
| 3| 4| 12|
| 4| 4| 16|
+----+----+-------+
Not only does jOOQ understand table-valued parameters, since jOOQ 3.5, we have also supported table-valued functions, which can be used like any ordinary table:
Result<CrossMultiplyRecord> r2 =
DSL.using(configuration)
.selectFrom(crossMultiply(numbers))
.where(F_CROSS_MULTIPLY.PRODUCT.gt(5))
.fetch();
CrossMultiplyRecord
elements (if you’re not using any projection), and you can form predicates on table columns (i.e. function return values), you can join the table, etc.
Excellent! Let’s start using table-valued parameters!
Published at DZone with permission of Lukas Eder. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments