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

on

|

views

and

comments

[ad_1]

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

For instance, you could write:

CREATE TYPE u_number_table AS TABLE (column_value INTEGER);

CREATE FUNCTION f_cross_multiply (
  @numbers u_number_table READONLY
)
RETURNS @end result TABLE (
  i1 INTEGER,
  i2 INTEGER,
  product INTEGER
)
AS
BEGIN
  INSERT INTO @end result
  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 end result 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 vital. Desk-valued parameters could 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 doable to observe the table-valued parameters tutorials and use a com.microsoft.sqlserver.jdbc.SQLServerDataTable, however if you happen to’re utilizing jOOQ and its code generator, each the user-defined sort and the operate can have generated Java code so that you can name simply:

Listing<Integer> l = Listing.of(1, 2, 3);
Consequence<FCrossMultiplyRecord> end result = ctx
    .selectFrom(fCrossMultiply(new UNumberTableRecord(
        l.stream().map(UNumberTableElementTypeRecord::new).toList()
    )))
    .fetch();

You possibly can think about extra complicated 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 methodology that fashions an embedded name to a table-valued operate (standalone calls are additionally doable)
  • UNumberTableRecord is a report representing the user-defined sort u_number_table, which could be handed as a desk valued parameter
  • UNumberTableElementTypeRecord is an artificial report sort for a single row of a u_number_table (extra complicated sorts with a number of attributes are doable, too!)

Printing this end result 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 end result rows like this:

end result.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!

[ad_2]

Supply hyperlink

Share this
Tags

Must-read

Google Presents 3 Suggestions For Checking Technical web optimization Points

Google printed a video providing three ideas for utilizing search console to establish technical points that may be inflicting indexing or rating issues. Three...

A easy snapshot reveals how computational pictures can shock and alarm us

Whereas Tessa Coates was making an attempt on wedding ceremony clothes final month, she posted a seemingly easy snapshot of herself on Instagram...

Recent articles

More like this

LEAVE A REPLY

Please enter your comment!
Please enter your name here