procedure:
CREATE PROCEDURE proc_a
@idx int output,--注意是output
@d1 datetime,
@d2 datetime
AS
set @idx = 0
Select @idx = fldIDX from tab1
Where time_fld between @d1 and @d2
java src:CallableStatement cstmt =con.prepareCall("{call proc_a(?,?,?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
//注意Timestamp.valueOf(String s)s的格式
//用new 的方法感觉有点麻烦
cstmt.setTimestamp(2,Timestamp.valueOf("2002-05-05 15:00:00"));
cstmt.setTimestamp(3,Timestamp.valueOf("2002-10-05 15:00:00"));
cstmt.execute();
int idx = cstmt.getInt(1);
cstmt.close();
CREATE PROCEDURE proc_a
@idx int output,--注意是output
@d1 datetime,
@d2 datetime
AS
set @idx = 0
Select @idx = fldIDX from tab1
Where time_fld between @d1 and @d2
java src:CallableStatement cstmt =con.prepareCall("{call proc_a(?,?,?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
//注意Timestamp.valueOf(String s)s的格式
//用new 的方法感觉有点麻烦
cstmt.setTimestamp(2,Timestamp.valueOf("2002-05-05 15:00:00"));
cstmt.setTimestamp(3,Timestamp.valueOf("2002-10-05 15:00:00"));
cstmt.execute();
int idx = cstmt.getInt(1);
cstmt.close();
TINYINT->INTEGER
但我觉得如果我要查询的字段不小于30个的话,你的这种方法操作起来比较麻烦,我的那种方法也是参考了别人的,不过是用于一种叫做 PostgreSQL 数据库的,我不知道 SQL Server 2000 是否也支持这种方法。请问还有其它方法没,是针对我前面那种存储过程的,我不想改数据库里的存储过程了,因为要改的存储过程太多了
private static Connection Conn = null;
private static CountAllBean countAllBean = new CountAllBean();
private static ResultSet rs = null;
private static CallableStatement stmt = null; public CountAllBean execute(String startime,String endtime,String s_id)throws ActionException{
try {
Configuration cfg = Configuration.getInstance();
ConnDB db = new ConnDB(s_id); //获取数据库连接
Conn = db.getConnection();
Conn.setAutoCommit(false);
stmt = Conn.prepareCall("{call SumAll(?,?)}");
java.sql.Timestamp time1 = Timestamp.valueOf(startime);
java.sql.Timestamp time2 = Timestamp.valueOf(endtime);
stmt.setTimestamp(2,time1);
stmt.setTimestamp(2,time2);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
String id = rs.getString(1);
String name = rs.getString(2);
countAllBean.set_count(count);
countAllBean.set_money(money);
countAllBean.set_Startime(startime);
countAllBean.set_endtime(endtime);
}else{
return null;
}
return countAllBean;
} catch (Exception ex) {
throw new ActionException(ex);
}finally{
try {
Conn.setAutoCommit(true);
if (stmt!=null) {
stmt.clearParameters() ;
stmt.close();
}
if (rs!=null) rs.close();
if (Conn!=null) Conn.close();
} catch (SQLException ex) {
//
}
}
}
}上面的程序可能写的不是很好,希望高手指点