java调用存储过程

创建存储过程的脚本,

使用sqlserver2000 中的pubs 数据库中的 jobs表为例.

createprocedureshowAll
as
select*fromjobs



createprocedureobtainJob_desc
@outputParamvarchar(20)output,
@idint
as
select@outputParam=job_descfromjobswherejob_id=@id


createprocedureobtainReturn
as
declare@retint
select@ret=count(*)fromjobs
return@ret


declare@retint
exec@ret=obtainReturn
print@ret

用来获得连接的函数

publicConnectiongetConnection()...{
Connectioncon=null;
try...{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=pubs","sa","");
}catch(Exceptione)...{
e.printStackTrace();
}
returncon;
}

1,调用得到结果集的存储过程

publicvoidgetResultSet()...{
//获得连接
Connectioncon=this.getConnection();
try...{
//showAll为存储过程名
java.sql.CallableStatementcstm=con.prepareCall("{callshowAll}");
ResultSetrs=cstm.executeQuery();
while(rs.next())...{
//这里写逻辑代码。
System.out.println(rs.getString(1));
}
rs.close();
con.close();
}catch(SQLExceptione)...{
//TODOAuto-generatedcatchblock
e.printStackTrace();
}

}

2,调用带有 输入 ,输出 参数的存储过程。

publicvoidgetOutParameter(intinParam)...{
StringoutParam;
Connectioncon=this.getConnection();

try...{
CallableStatementcstm=con.prepareCall("{callobtainJob_desc(?,?)}");
cstm.registerOutParameter(1,Types.VARCHAR);
cstm.setInt(2,inParam);
cstm.execute();;

//得到输出参数。
StringoutParma=cstm.getString(2);
System.out.println(outParma);
cstm.close();
con.close();

}catch(SQLExceptione)...{
//TODOAuto-generatedcatchblock
e.printStackTrace();
}

}

3,调用带返回值的存储过程。

publicvoidgetReturn()...{
intret;
Connectioncon=this.getConnection();
try...{
CallableStatementcstm=con.prepareCall("{?=callobtainReturn()}");
cstm.registerOutParameter(1,Types.INTEGER);

cstm.execute();
//得到返回值
ret=cstm.getInt(1);

System.out.println(ret);
cstm.close();
con.close();

}catch(SQLExceptione)...{
//TODOAuto-generatedcatchblock
e.printStackTrace();
}

}