java - 如何使用 jTDS 将表值参数传递给存储过程？

Question

我需要使用 JTDS jdbc 驱动程序调用存储过程。

但是这个存储过程需要一个结构类型作为参数。Jtds CallableStatement 没有设置结构的方法，因此我尝试转换为 SQLServerCallableStatement 并设置我想要的结构。我使用的代码如下

            Connection con = ds.getConnection();
            CallableStatement call = con.prepareCall("{call getErrosSKF ?,?,?,?}");
            call.setString("agrupador", agrupador);
            call.setDate(2, new Date(dataInicio.getTimeInMillis()));
            call.setDate(3, new Date(dataFim.getTimeInMillis()));   

            SQLServerDataTable areas = new SQLServerDataTable();
            areas.addColumnMetadata("id",Types.INTEGER);
            for(Integer a : skfIdAreas) {
                areas.addRow(a);
            }
             // generate an exception
            ((SQLServerCallableStatement) call).setStructured(4, "erroSKFTable", areas);

但我无法强制转换，因为它会生成一个异常，表示JtdsCallableStatement 无法转换为 SqlServerCallableStatemen。

我可以使用其他替代方案吗？

Answer 1

对表值参数 (TVP) 的支持是 Microsoft 的 JDBC Driver for SQL Server 的一个相当新的功能，事实上，根据...

System Requirements for the JDBC Driver

... Microsoft 当前可用的 JDBC 驱动程序均无法与 JRE_6 配合使用。您可以尝试从 Microsoft 找到一个古老的 JDBC 驱动程序，但它无论如何都不会直接支持 TVP，所以您最好坚持使用 jTDS。

现在 jTDS 也不直接支持 TVP，但您可以通过以下方式完成任务

1. 创建临时表，
2. 用您的数据填充临时表，然后
3. 使用匿名代码块从临时表填充 TVP，然后调用存储过程。

示例:

给定一个名为 [phonetic] ... 的现有引用表

letter  word
------  -------
A       Alfa
B       Bravo
C       Charlie
...
Y       Yankee
Z       Zulu

...用户定义的表类型...

CREATE TYPE [dbo].[LetterListTableType] AS TABLE(
    [seq] [int] NOT NULL,
    [letter] [varchar](1) NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [seq] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

...以及一个将该表类型作为参数的存储过程...

CREATE PROCEDURE [dbo].[GetPhonetic] 
    @letters dbo.LetterListTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT l.seq, l.letter, p.word 
        FROM phonetic p INNER JOIN @letters l on p.letter = l.letter
        ORDER BY l.seq;
END

...以下 Java 代码...

// 0. create some test data
String[] letters = new String[] { "G", "o", "r", "d" };
//
// 1. create temporary table
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE #tmp (seq INT IDENTITY PRIMARY KEY, letter VARCHAR(1))");
//
// 2. populate temporary table with letters to look up
PreparedStatement ps = conn.prepareStatement("INSERT INTO #tmp (letter) VALUES (?)");
for (String letter : letters) {
    ps.setString(1, letter);
    ps.addBatch();
}
ps.executeBatch();
//
// 3. use anonymous code block to pass table-valued parameter to stored procedure
String sql = 
          "SET NOCOUNT ON; "
        + "DECLARE @tvp dbo.LetterListTableType; "
        + "INSERT INTO @tvp (seq, letter) SELECT seq, letter FROM #tmp; "
        + "EXEC dbo.GetPhonetic @tvp; ";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
    System.out.printf("%s -> %s%n", rs.getString("letter"), rs.getString("word"));
}

...产生

G -> Golf
o -> Oscar
r -> Romeo
d -> Delta