java调用sql server存储过程
为了便于测试，先创建一个简单的表：CREATE TABLE test
(
id INT,
name VARCHAR(10)
)
INSERT INTO test VALUES(1,'safsdf');
INSERT INTO test VALUES(2,'aaaaa');
INSERT INTO test VALUES(3,'bbbbb');
一.存储过程返回单个结果集
存储过程1如下所示:
IF OBJECT_ID ('dbo.pTestProc1') IS NOT NULL
DROP PROCEDURE dbo.pTestProc1
GO
CREATE PROCEDURE pTestProc1
(
@id INT,
@name VARCHAR(20) OUTPUT
)
AS
BEGIN
SELECT @name = name FROM test WHERE id = @id
END
存储过程2如下所示:
IF OBJECT_ID ('dbo.pTestProc2') IS NOT NULL
DROP PROCEDURE dbo.pTestProc2
GO
CREATE PROCEDURE pTestProc2
AS
BEGIN
DECLARE @sql VARCHAR(100)
SET @sql = 'SELECT id, name FROM test '
EXEC(@sql)
END
调用存储过程１,2的代码如下({call dbo.pTestProc1或2})：
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.pTestProc1}");
while (rs.next()) {
System.out.println(rs.getString("id") + ":" + rs.getString("name"));
}
输出结果如下：
1:safsdf
2:aaaaa
3:bbbbb
二.使用带有输入参数的存储过程
存储过程3如下所示:
IF OBJECT_ID ('dbo.pTestProc3') IS NOT NULL
DROP PROCEDURE dbo.pTestProc3
GO
CREATE PROCEDURE pTestProc3
(
@id INT
)
AS
BEGIN
SELECT id, nameFROM test WHERE id = @id
END
调用代码如下：
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("{call pTestProc3(1)}");
while (rs.next()) {
System.out.println(rs.getString("id") + ":" + rs.getString("name"));
}
或
PreparedStatement pstmt = conn.prepareStatement("{call pTestProc3(?)}");
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("id") + ":" + rs.getString("name"));
}
或
CallableStatement cstmt = conn.prepareCall("{call dbo.pTestProc3(?)}");
cstmt.setInt(1, 1);
ResultSet rs = cstmt.executeQuery();
while (rs.next())
System.out.println(rs.getString("id") + ":" + rs.getString("name"));
输出结果都是:
1:safsdf
三.使用带有输出参数的存储过程
存储过程4如下所示:
IF OBJECT_ID ('dbo.pTestProc4') IS NOT NULL
DROP PROCEDURE dbo.pTestProc4
GO
CREATE PROCEDURE pTestProc4
(
@id INT,
@name VARCHAR(20) OUTPUT
)
AS
BEGIN
SELECT @name = name FROM test WHERE id = @id
END
调用代码如下:
CallableStatement cstmt = conn.prepareCall("{ call dbo.pTestProc4(?,?)}");
//设置IN参数
cstmt.setInt(1,1);
//注册OUT参数
cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
//执行存储过程
cstmt.execute();
//获取参数值
System.out.println( cstmt.getString(2) );
输出结果：safsdf
参考文章：http://www.qqread.com/java/2008/04/w407397.html
页:
[1]