|
比如SQL Server的一個存儲過程：
- create procedure proc_test
- @q_type int,
- @value int,
- @count int output
- as
- begin
- update mytable set value = @value where type = @q_type
- set @count = @@rowcount
- select * from mytable where type = @q_type
- end
- go
這個存儲過程，既有輸出參數(shù)，又有返回結(jié)果集，而用java調(diào)用他，兩者都要取到，則代碼如下：
- Connection conn = MyConnectionPool.getConnection();
- CallableStatement cstmt = conn.prepareCall("{call proc_test(?,?,?)}");
- cstmt.setInt(1, type);
- cstmt.setInt(2, value);
- cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
- ResultSet rs = cstmt.executeQuery();
- while(rs.next()) {
- doSomeThingToResultSet(rs);
- }
- doSomeThingToOutParameter(cstmt.getInt(3));
- rs.close();
- cstmt.close();
- conn.close();
其中的關鍵在于哪兒呢？
-
必須用cstmt.executeQuery()來取得結(jié)果集，用cstmt.execute()然后getResultSet()是取不到的，而executeQuery()能保證先執(zhí)行update再select；
-
獲得輸出參數(shù)的cstmt.getInt(3)必須在處理完結(jié)果集的所有內(nèi)容后再執(zhí)行，如果把上述代碼改成如下：
........
doSomeThingToOutParameter(cstmt.getInt(3));
while(rs.next()) {
doSomeThingToResultSet(rs);
}
........
后果就是，在rs.next()的時候，會拋出異常：java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
|
