Use jOOQ’s code generator to simply go table-valued parameters

Microsoft T-SQL helps a language function referred to as table-valued parameter (TVP), which is a parameter of a desk kind that may be handed to a saved process or operate.

For instance, it’s possible you’ll write:

CREATE TYPE u_number_table AS TABLE (column_value INTEGER);

CREATE FUNCTION f_cross_multiply (
  @numbers u_number_table READONLY
)
RETURNS @outcome TABLE (
  i1 INTEGER,
  i2 INTEGER,
  product INTEGER
)
AS
BEGIN
  INSERT INTO @outcome
  SELECT
    n1.column_value, 
    n2.column_value, 
    n1.column_value * n2.column_value
  FROM @numbers n1
  CROSS JOIN @numbers n2

  RETURN
END

This operate takes a table-valued parameter (TVP), and produces a outcome set containing the cross product of the parameter desk with itself. The operate occurs to be a table-valued operate, however this isn’t strictly crucial. Desk-valued parameters may be handed to any operate or process.

In native T-SQL, the above operate can be utilized as follows:

DECLARE @t u_number_table;
INSERT INTO @t VALUES (1), (2), (3);
SELECT * FROM f_cross_multiply(@t);

Producing the next output:

|i1 |i2 |product|
|---|---|-------|
|1  |1  |1      |
|2  |1  |2      |
|3  |1  |3      |
|1  |2  |2      |
|2  |2  |4      |
|3  |2  |6      |
|1  |3  |3      |
|2  |3  |6      |
|3  |3  |9      |

Calling the operate from Java

Utilizing native JDBC, it’s attainable to observe the table-valued parameters tutorials and use a com.microsoft.sqlserver.jdbc.SQLServerDataTable, however should you’re utilizing jOOQ and its code generator, each the user-defined kind and the operate may have generated Java code so that you can name simply:

Listing l = Listing.of(1, 2, 3);
Consequence outcome = ctx
    .selectFrom(fCrossMultiply(new UNumberTableRecord(
        l.stream().map(UNumberTableElementTypeRecord::new).toList()
    )))
    .fetch();

You possibly can think about extra advanced queries the place the table-valued operate is used e.g. in a CROSS APPLY operator.

There are a number of generated objects right here:

• FCrossMultiplyRecord is a TableRecord containing the rows produced by the f_cross_multiply operate.
• Routines.fCrossMultiply is a static-imported technique that fashions an embedded name to a table-valued operate (standalone calls are additionally attainable)
• UNumberTableRecord is a document representing the user-defined kind u_number_table, which may be handed as a desk valued parameter
• UNumberTableElementTypeRecord is an artificial document kind for a single row of a u_number_table (extra advanced sorts with a number of attributes are attainable, too!)

Printing this outcome yields:

+----+----+-------+
|  i1|  i2|product|
+----+----+-------+
|   1|   1|      1|
|   2|   1|      2|
|   3|   1|      3|
|   1|   2|      2|
|   2|   2|      4|
|   3|   2|      6|
|   1|   3|      3|
|   2|   3|      6|
|   3|   3|      9|
+----+----+-------+

Alternatively, simply use the generated code to entry the outcome rows like this:

outcome.forEach(r -> {
    System.out.println(
        r.getI1() + " * " + r.getI2() + " = " + r.getProduct()
    );
});

To get:

1 * 1 = 1
2 * 1 = 2
3 * 1 = 3
1 * 2 = 2
2 * 2 = 4
3 * 2 = 6
1 * 3 = 3
2 * 3 = 6
3 * 3 = 9

Simply join jOOQ’s code generator to your SQL Server database, and begin calling your capabilities accepting table-valued parameters with ease!