从java调用表值参数的存储过程

Question

在我的应用程序中，我想执行像SELECT*FROM tbl这样的查询，其中，@list中的col In（@list）可以有变量no of值。我正在使用MS SQL server数据库。当我搜索这个问题时，我找到了这个链接

http://www.sommarskog.se/arrays-in-sql-2008.html

此链接表示使用表值参数。因此，我使用Microsoft SQL Server Management Studio创建了用户定义的数据类型。

创建类型integer_list_tbltype AS TABLE（n int NOT NULL主键）

然后我写了存储过程

CREATE PROCEDURE get_product_names @prodids integer_list_tbltype READONLY AS
   SELECT p.ProductID, p.ProductName
   FROM   Northwind.dbo.Products p
   WHERE  p.ProductID IN (SELECT n FROM @prodids)

然后使用管理工作室只有我执行了这个程序

DECLARE @mylist integer_list_tbltype
INSERT @mylist(n) VALUES(9),(12),(27),(37)
EXEC get_product_names @mylist

它给了我正确的输出。但是我想知道如何从java源代码中调用这个存储过程。我知道如何用常量参数调用简单的存储过程

CallableStatement proc_stmt = null;
proc_stmt = con.prepareCall("{call test(?)}");
proc_stmt.setString(1,someValue);

但是如何在表值参数情况下调用存储过程呢？

Answer 1

典型的答案（逗号分隔或XML）都与SQL注入有关。我需要一个能让我使用事先准备好的声明的答案。所以我想到了这个：

StringBuilder query = new StringBuilder();
query.append(
  "DECLARE @mylist integer_list_tbltype;" +
  "INSERT @mylist(n) VALUES(?)");
for (int i = 0; i < values.size() - 1; ++i) {
  query.append(",(?) ");
}
query.append("; EXEC get_product_names @mylist ");
PreparedStatement preparedStmt = conn.prepareStatement(query.toString());
for (int i = 0; i < values.size(); ++i) {
    preparedStmt.setObject(i + 1, itemLookupValues.get(i));
}

Answer 2

看起来这是JDBC计划中的新增功能，但尚未实施：

http://blogs.msdn.com/b/jdbcteam/archive/2012/04/03/how-would-you-use-table-valued-parameters-tvp.aspx

将参数作为分隔字符串（“9,12,27,37”）传递，然后在SQL Server中创建一个名为“fnSplit”的表值函数，或任何将返回表中整数值的函数（只需搜索“SQL Server split function”，有数百万个）。

Answer 3

这在JDBC驱动程序手册中有记录。在你的情况下，你必须这样做：

try (SQLServerCallableStatement stmt =
    (SQLServerCallableStatement) con.prepareCall("{call test(?)}")) {

    SQLServerDataTable table = new SQLServerDataTable();   
    sourceDataTable.addColumnMetadata("n", java.sql.Types.INTEGER);   

    sourceDataTable.addRow(9);
    sourceDataTable.addRow(12);
    sourceDataTable.addRow(27);
    sourceDataTable.addRow(37);

    stmt.setStructured(1, "dbo.integer_list_tbltype", table);  
}

我最近也在一篇文章中记录了这一点。