带有ResultSet、返回值和输出参数的JDBC SQL Server存储过程

Question

我正在将应用程序从Jython转换为已编译的Java。该应用程序使用大量SQL Server存储过程来执行CRUD操作。所有过程都定义了一个指示状态的返回值，以及一些用于向应用程序提供反馈的输出参数。大多数过程还返回一个结果集。我正在努力研究如何检索返回值、结果集和输出参数。

我通常使用C#，所以JDBC的细微差别对我来说是新的。我一直在测试其中一个过程，该过程对数据库进行插入，然后对插入的对象进行选择。

下面是一个简化的示例过程，仅用于说明。实际程序比这更复杂。

CREATE PROCEDURE [dbo].[sp_Thing_Add]
(
@Name NVARCHAR(50),
@Description NVARCHAR(100),
@ResultMessage NVARCHAR(200) = N'' OUTPUT
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @Result INT = -1
DECLARE @ResultMessage = 'Procedure incomplete'

BEGIN TRY
INSERT INTO Things (Name, Description) VALUES (@Name, @Description)
SELECT * FROM Things WHERE ThingID = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
SELECT @Result = CASE WHEN ERROR_NUMBER() <> 0 THEN ERROR_NUMBER() ELSE 1 END,
@ResultMessage = ERROR_MESSAGE()
GOTO EXIT_SUB
END CATCH
SUCCESS:
SET @Result = 0
SET @ResultMessage = N'Procedure completed successfully'
RETURN @Result
EXIT_SUB:
IF @Result <> 0
BEGIN
-- Do some error handling stuff
END
RETURN @Result

我可以使用以下代码成功地检索ResultSet。

var conn = myConnectionProvider.getConnection();
String sql = "{? = call dbo.sp_Thing_Add(?, ?, ?)}"
call = conn.prepareCall(sql);
call.registerOutParameter(1, TYPES.Integer); // Return value
call.setString("Name", thing.getName());
call.setString("Description", thing.getDescription());
call.registerOutParameter("ResultMessage", TYPES.NVARCHAR);
ResultSet rs = call.executeQuery();
// Try to get the return value. This appears to close the ResultSet and prevents data retrieval.
//int returnValue = call.getInt(1);
// Normally there'd be a check here to make sure things executed properly, 
// and if necessary the output parameter(s) may also be leveraged
if (rs.next()) {
thing.setId(rs.getLong("ThingID"));
// Other stuff actually happens here too...
}

如果我尝试使用注释掉的行检索返回值，我会得到一个错误，说明ResultSet已关闭。

com.microsoft.sqlserver.jdbc.SQLServerException：结果集已关闭。

我已经阅读了文档，并了解了如何执行返回值、输出参数和结果集。但我如何利用这三个因素呢？

Answer 1

给定存储过程中的处理顺序(插入、选择，然后填充结果参数(，在使用CallableStatement.getXXX检索返回值之前，您需要处理结果集。

Answer 2

输出在从executeQuery()检索的ResultSet rs中。

你可能想使用这样的excute方法：

call.execute();
String returnValue = call.getString("ResultMessage");

您还希望正确地映射到输出类型。

Answer 3

执行查询后，您的连接就关闭了。基本上mysqljdbc连接隐式地扩展到AutoCloseable。由于您的结果只是过程中的实体，请通过索引0获取值，并进行适当的索引越界异常处理。