当前页面: 开发资料首页 → J2SE 专题 → java调用postgre数据库游标的问题
java调用postgre数据库游标的问题
摘要: java调用postgre数据库游标的问题
困扰好久了,请各位达人帮帮我吧!!
数据库函数,返回一个游标,如下:
DECLARE
ref refcursor;
BEGIN
open ref for select a.id,a.firstname,a.lastname,b.sumworkmin,c.othermin from employee a
left join
(
select employeeid,sum(workmin) as sumworkmin from workreport
where workdate>=$1 and
workdate<($1+(interval '1 month'))
group by employeeid
) as b
on a.id = b.employeeid
left join
(
select employeeid,sum(workmin) as othermin from workreport
where (worktype=0 or worktype=9 or projectid='00000') and
workdate>=$1 and
workdate<($1+(interval '1 month'))
group by employeeid
) as c
on a.id = c.employeeid ;
return ref;
END;
-------------------------------------------------------------
java代码如下:
DBOperator db = null;
Connection conn = null;
ResultSet rs = null;
CallableStatement cs = null;
try {
db = new DBOperator();
conn = db.getConnection();
String sql = "{? = call select_workeff(to_date(?,'YYYY/MM/DD')) }";
cs = conn.prepareCall(sql);
cs.registerOutParameter(1, Types.OTHER);
cs.setString(2, date);
cs.execute();
System.out.println("cs === " + cs);
rs = (ResultSet) cs.getObject(1);
while (rs.next()) {
String userName = rs.getString("firstname") + " "
+ rs.getString("lastname");
.........................
}
}
代码没有贴全,帮我看一下问题出在什么地方啊?愁死我了!!
忘了说了,是postgres数据库
没有人帮我吗
"{? = call select_workeff(to_date(?,'YYYY/MM/DD')) }";
可以这样用吗?
函数呀?
怎么可以调用函数呢,要调用存储过程。