从Java使用表值参数调用存储过程 [英] Call stored procedure with table-valued parameter from java

问题描述

在我的应用程序中，我想执行类似 SELECT * FROM tbl WHERE col IN(@list)的查询，其中，@ list可以具有可变的无值值.我正在使用MS SQL Server数据库.当我用谷歌搜索这个问题时，我发现了这个链接

In my application I want to execute query like SELECT * FROM tbl WHERE col IN (@list) where,@list can have variable no of values. I am using MS SQL server database. When I google this problem then I found this link

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

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

This link says to use table-valued parameter. So I created user-defined data type using 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)

然后仅使用Management Studio，我执行了此过程

and then using management studio only I executed this procedure

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

，它给了我正确的输出.但是我想知道如何从Java源代码调用此存储过程.我知道如何用固定数量的参数调用简单的存储过程

and it is giving me correct output. But I am wondering how to call this stored procedure from java source code. I know how to call simple stored procedure with constant number of argument

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

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

but how to call stored procedure in table-value parameter case?

推荐答案

搜索了一段时间后，我找到了这个问题的答案.特别是当您使用IN子句并且操作数都不是变量时，可以使用逗号分隔的值作为IN子句中的输入.

After searching for a while I found answer of this problem.Specially when you use IN clause and no of operands are variable then you can use comma-delimited values as an input in IN clause.

这是一个示例，该示例将使用动态SQL检索存储过程，该存储过程将在提供的邮政编码中检索给定律师类型的所有律师.

Here's the example how the stored procedure that will retrieve all lawyers of the given lawyer type in the provided ZIP code will look like using dynamic SQL.

CREATE PROCEDURE [dbo].[GetLawyers] ( @ZIP CHAR(5), @LawyerTypeIDs VARCHAR(100) )
AS

DECLARE @SQL     VARCHAR(2000)

SET @SQL = 'SELECT * FROM [dbo].[Lawyers]
            WHERE [ZIP] = ' + @ZIP + ' AND
                  [LawyerTypeID] IN (' + @LawyerTypeIDs + ')'
EXECUTE (@SQL)

GO

要执行存储过程，将用户输入的邮政编码和所选律师键入的值传递为逗号分隔的值:

To execute the stored procedure passing the ZIP code entered by the user and the selected lawyer types in a comma separated value:

EXECUTE [dbo].[GetLawyers] '12345', '1,4'

所以结论是您不需要使用TVP.无论您使用哪种语言[Java，PHP]，都只需将参数作为逗号分隔的字符串传递给存储过程，即可正常工作.

So conclusion is you do not need to use TVP. Whichever language[Java, PHP] you use just pass parameters as comma-delimited string to stored procedure and it will work perfect.

因此，在JAVA中，您可以调用上述存储过程:-

So in JAVA you can call above stored procedure:-

proc_stmt = con.prepareCall("{call GetLawyers(?,?)}");
proc_stmt.setString(1,"12345");
proc_stmt.setString(2,"'1,4'");

这篇关于从Java使用表值参数调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！