java中用jdbc调用SQLServer带返回结果集存储过程的问题

最新推荐文章于 2024-06-20 09:43:59 发布

最新推荐文章于 2024-06-20 09:43:59 发布 · 522 阅读

文章标签：

自学java中用jdbc调用SQLServer带返回结果集存储过程但是却发生“The executeQuery method must return a result set”错误
存储过程如下：
create procedure exchangeMoney2(
@account_id_in bigint,--转入钱的账户
@account_id_out bigint,--转出钱的账户
@exchange_money float--转账金额
)as
begin
--创建临时表#account
create table #account
(account_id bigint null,account_name varchar(80) null,
account_money money null,add_time datetime null)
--将转账前的2个账户信息插入到临时表#account中
insert into #account
select * from account
where account_id=@account_id_out or account_id=@account_id_in
--更新转出账户的余额
update account set account_money=account_money-@exchange_money
where account_id=@account_id_out
--更新转入账户的余额
update account set account_money=account_money+@exchange_money
where account_id=@account_id_in
--将转账后的2个账户的账户信息插入到临时表#account中
insert into #account
select * from account
where account_id=@account_id_out or account_id=@account_id_in
--返回临时表#account中的数据
select * from #account
--删除临时表#account中的内容
truncate table #account
--删除临时表#account
drop table #account
end

jsp页面如下：
<%@ page language="java" import="java.util.*" pageEncoding="GB18030"%>
<%@page import="java.sql.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>调用存储过程</title>
</head>
<body>
<%
Class.forName("net.sourceforge.jtds.jdbc.Driver");
String url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=webcounter";
Connection conn = DriverManager.getConnection(url,"sa","sa");
String callExchangeMoneyStr = "{?=call exchangeMoney(1,2,100)}";
CallableStatement procedure = conn.prepareCall(callExchangeMoneyStr);
procedure.registerOutParameter(1,Types.OTHER);
ResultSet rs = procedure.executeQuery();
%>
<table border="1" cellpadding="0" cellspacing="0" align="center" width="400">
<tr>
<td colspan="4" align="center">转账操作</td>
</tr>
<tr>
<td>账号</td><td>户名</td>
<td>余额</td><td>账户生成时间</td>
</tr>
<tr><td colspan="4" align="center">转账前的账户信息</td></tr>
<%
int i=0;
while(rs.next()){
i++;
if(i==3){
%>
<tr><td colspan="4" align="center">转账后的账户信息</td></tr>
<%} %>
<tr>
<td><%=rs.getLong("account_id") %></td>
<td><%=rs.getString("account_name") %></td>
<td><%=rs.getFloat("account_money") %></td>
<td><%=rs.getDate("add_time") %></td>
</tr>
<%} %>
</table>
</body>
</html>