java调用存储过程 参数,使用java中的表值参数调用存储过程

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

This link says to use table-valued parameter. So I created user-defined data type using Microsoft SQL Server Management Studio.

CREATE TYPE integer_list_tbltype AS TABLE (n int NOT NULL PRIMARY KEY)

Then I wrote stored procedure

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)

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

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?

解决方案

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.

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'

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.

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'");