在SQL Server JDBC中使用表值参数 [英] Using table-valued parameters with SQL Server JDBC

问题描述

任何人都可以提供有关如何在SQL Server JDBC中使用表值参数(TVP)的指导吗?我正在使用Microsoft提供的SQL Server驱动程序的6.0版本，并且已经查看了

Could anyone provide some guidance on how to use Table Value Parameters (TVP) with SQL Server JDBC? I am using the 6.0 version of the SQL Server driver provided by Microsoft, and I have reviewed the official documentation as well as the more helpful example

如何通过表值参数从Java到sql server存储过程?

这两个示例都显示了从Connection.prepareStatement调用中获取SQLServerPreparedStatement强制转换的对象以便调用setStructured方法.这样是否会阻止使用标准连接池(如DBCP2)?

Both of the examples show getting a SQLServerPreparedStatement casted object from a Connection.prepareStatement call in order to call the setStructured method. Wouldn't this prevent usage of standard connection pools such as DBCP2?

我注意到另一个Stack Overflow评论中的一个评论，说有可能改为使用stmt.setObject方法:

I noticed a comment from the other Stack Overflow comment saying it might be possible to use the stmt.setObject method instead:

作为强制转换PreparedStatement的替代方法，可以将SQLServerDataTable实例传递给PreparedStatement.setObject(int，Object)方法.这适用于dbo模式中定义的TVP类型. – allenru 7月15日在19:18

As an alternative to casting the PreparedStatement, you can pass the SQLServerDataTable instance to the PreparedStatement.setObject(int,Object) method. This worked for a TVP type that was defined in the dbo schema. – allenru Jul 15 at 19:18

尽管我在尝试此操作时遇到错误，并且dbo模式中的类型是.

Though I got an error when I tried this, and the type is in the dbo schema...

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException:  The Table-Valued Parameter must have a valid type name.

这是我的代码:

// JAVA
private static void tableParameters(DataSource ds)  throws SQLException {
    final String sql = "EXEC dbo.GetAccountsFromTable @accountIds=?";
    final List<Integer> accountIds = generateIntegers(50, 1_000_000);

    try (Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql))
    {
        SQLServerDataTable accounts = new SQLServerDataTable();
        accounts.addColumnMetadata("item", java.sql.Types.INTEGER);
        for (Integer aid : accountIds)
            accounts.addRow(aid.toString());

        stmt.setObject(1, accounts);

        try (ResultSet rs = stmt.executeQuery())
        {
            while (rs.next()) {
                System.out.println(rs.getInt(1));
            }
        }
    }
}

-- TSQL
create type dbo.IntegerTable AS TABLE (item INT);

CREATE PROCEDURE dbo.GetAccountsFromTable(@accountIds dbo.IntegerTable READONLY)
AS
BEGIN
  IF OBJECT_ID('tempdb..#AccountIds') IS NOT NULL
    DROP TABLE #AccountIds

  CREATE TABLE #AccountIds (id INTEGER)

  INSERT INTO #AccountIds
  SELECT * FROM @accountIds

  SELECT * FROM #AccountIds
END

任何帮助或指导表示赞赏.谢谢！

Any help or guidance is appreciated. Thanks!

推荐答案

这是我最终使用的路由. DBCP2具有DelegatingStatement.getInnermostDelegate方法来获取由Microsoft驱动程序创建的PreparedStatement对象.我不是跳过障碍的忠实拥护者-即使添加了错误检查代码也似乎很脆弱. TVP是SqlServer特有的东西，因此使用所需的假设和强制转换可能还不错.

Here's the route that I ended up using. DBCP2 has a DelegatingStatement.getInnermostDelegate method to get the PreparedStatement object created by the Microsoft driver. I'm not a huge fan of the hoops needed to jump through - even if error checking was added the code seems brittle. TVP is a SqlServer specific thing so maybe it's not so bad to use the required assumptions and casts.

private static int testTvp(DataSource ds, List<Integer> accountIds)  throws SQLException {
    final String sql = "EXEC dgTest.GetAccountsFromTvp @accountIds=?";

    try (Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql)) {
        DelegatingPreparedStatement dstmt = (DelegatingPreparedStatement)stmt;
        SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement)dstmt.getInnermostDelegate();

        SQLServerDataTable accounts = new SQLServerDataTable();
        accounts.addColumnMetadata("token", java.sql.Types.INTEGER);
        for (Integer aid : accountIds)
            accounts.addRow(aid);

        pstmt.setStructured(1, "dgTest.IntegerTable", accounts);

        //// NOTE: The below works for JTDS driver, official MS driver said no result sets were returned
        //try (ResultSet rs = pstmt.executeQuery()) {
        //  return sumInts(rs);
        //}

        if (pstmt.execute()) {
            try (ResultSet rs = pstmt.getResultSet()) {
                return sumInts(rs);
            }
        }
        return -1;
    }
}

这篇关于在SQL Server JDBC中使用表值参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！