我现在写了个存储过程:
CREATE PROCEDURE spWWWClear
@YearMonth varchar(8) --费用年月 AS
declare @Ynum int,@Wnum intif isnull(@YearMonth,'')=''
set @YearMonth=cast(year(getdate())*100+month(getdate()) as varchar(8))select isnull(clTotal1,0)+isnull(clTotal2,0)+isnull(clTotal3,0) as Total,isnull(clTotal1,0) clTotal1,isnull(clTotal2,0) clTotal2,isnull(clTotal3,0) clTotal3 into #temp1 from ClearRate where clYearMonth=cast(@YearMonth as int) and clPayed=0 select isnull(clTotal1,0)+isnull(clTotal2,0)+isnull(clTotal3,0) as Total,isnull(clTotal1,0) clTotal1,isnull(clTotal2,0) clTotal2,isnull(clTotal3,0) clTotal3 into #temp2 from ClearRate where clYearMonth=cast(@YearMonth as int) and clPayed=1 select @Ynum=count(*)from #temp1
select @Wnum=count(*)from #temp2select '未收' as 状态,@Ynum 户数,sum(clTotal1) 费用一,sum(clTotal2) as 费用二,sum(clTotal3) as 其他费,sum(Total) as 总金额 from #temp1
union all
select '已收' as 状态, @Wnum 户数,sum(clTotal1) 费用一,sum(clTotal2) as 费用二,sum(clTotal3)as 其他费,sum(Total) as 总金额 from #temp2
GO
我想在jsp中调用这个存储过程,该怎么调用呢?想在jsp中显示下面的内容:
状态 户数 费用一 费用二 其他费 总金额
未收 5190 527930.2000 731029.0000 .0000 1258959.2000
已收 1413 96211.2000 149832.0000 .0000 246043.2000
CREATE PROCEDURE spWWWClear
@YearMonth varchar(8) --费用年月 AS
declare @Ynum int,@Wnum intif isnull(@YearMonth,'')=''
set @YearMonth=cast(year(getdate())*100+month(getdate()) as varchar(8))select isnull(clTotal1,0)+isnull(clTotal2,0)+isnull(clTotal3,0) as Total,isnull(clTotal1,0) clTotal1,isnull(clTotal2,0) clTotal2,isnull(clTotal3,0) clTotal3 into #temp1 from ClearRate where clYearMonth=cast(@YearMonth as int) and clPayed=0 select isnull(clTotal1,0)+isnull(clTotal2,0)+isnull(clTotal3,0) as Total,isnull(clTotal1,0) clTotal1,isnull(clTotal2,0) clTotal2,isnull(clTotal3,0) clTotal3 into #temp2 from ClearRate where clYearMonth=cast(@YearMonth as int) and clPayed=1 select @Ynum=count(*)from #temp1
select @Wnum=count(*)from #temp2select '未收' as 状态,@Ynum 户数,sum(clTotal1) 费用一,sum(clTotal2) as 费用二,sum(clTotal3) as 其他费,sum(Total) as 总金额 from #temp1
union all
select '已收' as 状态, @Wnum 户数,sum(clTotal1) 费用一,sum(clTotal2) as 费用二,sum(clTotal3)as 其他费,sum(Total) as 总金额 from #temp2
GO
我想在jsp中调用这个存储过程,该怎么调用呢?想在jsp中显示下面的内容:
状态 户数 费用一 费用二 其他费 总金额
未收 5190 527930.2000 731029.0000 .0000 1258959.2000
已收 1413 96211.2000 149832.0000 .0000 246043.2000
Connection conn = DatabaseConnection.getConnection(); // 获得数据库连接
CallableStatement proc =conn.prepareCall("{(?,?,?,?,?,?)=call spNewbumf(?)}");//调用存储过程
proc.registerOutParameter(1,Types.INTEGER);//传出参数
proc.registerOutParameter(2,Types.INTEGER);
proc.registerOutParameter(3,Types.INTEGER);
proc.registerOutParameter(4,Types.INTEGER);
proc.registerOutParameter(5,Types.INTEGER);
proc.registerOutParameter(6,Types.INTEGER);
proc.setString(7,YearMonth);
//传入参数
proc.execute();//执行然后取到返回值了,又怎样在jsp页面上显示呢?另外取返回值的时候,上边的1,2,3......表示的是什么啊,是我的表里的字段?还是存储过程里的?我调用的时候对吗?
cmt.setString(1,YearMonth);//输入
ResultSet rs= cmt.executeQuery();
String str= cmt.getString(0);//获取输出值
rs.getString("费用二");你把 存储过程 拿到 数据库去执行一下 就知道 返回什么样的了
你说的那1,2,3,4,5,6 那几个就是代表你的输出参数,你在过程里面定义了6个
你在查询分析器里面执行
exec spWWWClear 日期 看看显示结果是什么样的? 如果是
状态 户数 费用一 费用二 其他费 总金额
未收 5190 527930.2000 731029.0000 .0000 1258959.2000
已收 1413 96211.2000 149832.0000 .0000 246043.2000 这样的记录?
那就不用输出参数,用结果集来接收,然后显示。
public ResultSet getData(String queryString) {
ResultSet rs = null;
try {
cst = conn.prepareCall(queryString); cst.executeQuery();
return rs = cst.getResultSet();
} catch (Exception ex) {
System.out.print("调用存储过程时发生错误!:" + ex.toString());
return rs;
}
}
public ResultSet getDataByPage(String your_date) {
ResultSet rs = null;
try {
Temp();
jdbc.connect();
rs = jdbc.getData("{call spWWWClear(" + your_date + ")}");
return rs;
} catch (Exception ex) {
System.out.print("费用结算错误" + ex.toString());
return rs;
}
}
然后用RS来显示你需要显示的列。
在 查询分析器里显示结果就是这样的,但是在jsp中怎么显示啊。
List list = new ArrayList();
用while(rs.next){
Module m = new Module();
m.setMm( rs.getString("名称"));
list.add(m);
}
页面
<%
List list = request.getSession().getAttribute("list");
for(Iterator it=list.iterator();it.hasNext();){
Module m = (Module)it.next;
%>
${m.mm}
<%
}
%><c:foreach item="><c:foreach>
CallableStatement proc =conn.prepareCall("{(?,?,?,?,?,?)=call spNewbumf(?)}");//调用存储过程
proc.registerOutParameter(1,Types.INTEGER);//传出参数
proc.registerOutParameter(2,Types.INTEGER);
proc.registerOutParameter(3,Types.INTEGER);
proc.registerOutParameter(4,Types.INTEGER);
proc.registerOutParameter(5,Types.INTEGER);
proc.registerOutParameter(6,Types.INTEGER);
proc.setString(7,YearMonth);
//传入参数
proc.execute();//执行
1、2、3、4、5、6、7代表conn.prepareCall("{(?,?,?,?,?,?)=call spNewbumf(?)}");里面的第几个?
想在jsp中显示的话首先你要将得到的结果集封装成一个个对应的对象成为一个list,然后放在request中,在jsp 中可以通过struts标签或el表达式得到。
例如:
String sql = "select * from user";
Connection conn = DatabaseConnection.getConnection(); // 获得数据库连接
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
以上没问题吧,如果是调用存储过程
String sql = "spWWWClear 200812";
Connection conn = DatabaseConnection.getConnection(); // 获得数据库连接
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();这样就OK了