有进要通过jdbc调用存储过程,来说一下怎样来实现?
在我见过的有两种情况:
1)返回一个结果集(ResultSet)。
2)返回一个特定的值。
下面来详细的说明。
1)返回一个结果集(ResultSet),这种类似通常的处理结果集
如果事先就有一个类似如下的procedure
CREATE
PROCEDURE
getShipQuantity @jsid
int
AS
SELECT
jf_js_id,
SUM
(jf_ship_quantity)
AS
shipqty
FROM
tjobsheet_finish f
WHERE
(jf_js_id
=
@jsid)
GROUP
BY
jf_js_id
那么我们将通过如下的代码来调用
String sql
=
"
{ call getShipQuantity(?) }
"
;
Connection con
=
getSession().connection();//通过hibernate得到的连接
ResultSet rs
=
null
;
BigDecimal shipQuantity
=
new
BigDecimal(
0
);
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
try
{
CallableStatement cs = con.prepareCall(sql);
cs.setInt(1,jsoId);//设置输入参数
rs = cs.executeQuery();//返回结果集
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if(rs.next())
{
shipQuantity = new BigDecimal(rs.getDouble(2));
}
logger.debug("shipQuantity --------------------- "+shipQuantity);
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
}
catch
(Exception e)
{
logger.debug(e);
}
2)返回一个特定的值。也就是说,在procedure的定义中已经用output输出参数了。请看下面的proceduer
create
procedure
getSingleWgt @@singleWgt numeric(
8
,
3
) output,@jsnum
varchar
(
11
)
=
'
0000-0480
'
as
declare
@stwgt numeric(
8
,
3
)
select
@stwgt
=
sum
(b.stwgt)
from
js
as
a
inner
join
jsactdtl
as
b
on
a.jsnum
=
b.jsnum
where
a.completion
=
1
and
b.stflag
=
22
and
a.jsnum
=
@jsnum
select
@@singleWgt
=
(@stwgt
/
orderedqty)
from
js
where
jsnum
=
@jsnum
那么我们将通过如下的代码来调用
String sql
=
"
{ call getSingleWgt(?,?) }
"
;
Connection con
=
getSession().connection();
//
得到connection
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
try
{
CallableStatement cs = con.prepareCall(sql);//通过它来执行sql
cs.registerOutParameter(1,java.sql.Types.FLOAT);//注册输出参数
cs.setString(2,shipment.getJsnum());//指出输入参数
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if(cs.execute())
{//执行
float output = cs.getFloat(1);//返回值
}
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
}
catch
(Exception e)
{
logger.debug(e);
}
在我见过的有两种情况:
1)返回一个结果集(ResultSet)。
2)返回一个特定的值。
下面来详细的说明。
1)返回一个结果集(ResultSet),这种类似通常的处理结果集
如果事先就有一个类似如下的procedure
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](/Images/dot.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](/Images/dot.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockEnd.gif](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](/Images/dot.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedBlockEnd.gif](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
2)返回一个特定的值。也就是说,在procedure的定义中已经用output输出参数了。请看下面的proceduer
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
那么我们将通过如下的代码来调用
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](/Images/dot.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](/Images/OutliningIndicators/ContractedSubBlock.gif)
![dot.gif](/Images/dot.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockEnd.gif](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](/Images/OutliningIndicators/ContractedBlock.gif)
![dot.gif](/Images/dot.gif)
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedBlockEnd.gif](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![None.gif](/Images/OutliningIndicators/None.gif)