Question

sql server存储过程的游标output后 java怎么接受它？oracle返回的rs我会接受

call.setInt(1,paginationBean.getPageIndex());

call.setInt(2,paginationBean.getPageSize());

call.setString(3,paginationBean.getSql());

call.registerOutParameter(4, java.sql.Types.DECIMAL);

call.registerOutParameter(5, OracleTypes.CURSOR);//OracleTypes.CURSOR就是oracle的，那么sql serverl怎么写呢？

call.registerOutParameter(6, java.sql.Types.DECIMAL);

call.executeQuery();

rs=(ResultSet)call.getObject(5);

Answer 1

1.存储过程中我定义了一个游标返回值

@CURSOR_subsidiaryaccounts CURSOR VARYING OUTPUT -- 返回明细账的游标

ALTER PROCEDURE [dbo].[p_ledger_subsidiaryaccounts]
@fyearGe int, --年
@fperiodGe int, --期
@fyearLe int, --年
@fperiodLe int, --期
@fposted int, --是否过账 1,表示包含未过账；0，表示不包含未过账数据
@facctId bigint, --科目ID
@fdetailId bigint, --核算项目ID
@CURSOR_subsidiaryaccounts CURSOR VARYING OUTPUT -- 返回明细账的游标
AS
BEGIN
SET NOCOUNT ON;
/*创建临时表*/

....

--1.对返回的游标进行数据的绑定
SET @CURSOR_subsidiaryaccounts = CURSOR
FORWARD_ONLY STATIC
FOR
SELECT ID,
FDate,
FYear,
FPeriod,
FVoucherId,
FNumber,
FExplanation,
Fdebit,
Fcridt,
Fdc,
Fendbalance
FROM #tmp
Order By ID asc,FYear,Fperiod
-- 2. 打开游标
OPEN @CURSOR_subsidiaryaccounts
END

2.我在JAVA代码中用JDBC模版执行这个存储过程，要注册个游标类型的返回值。
SQL Server的JDBC貌似不支持？？不知道怎么解决呢？java代码如下：

public List<Map> listSubsidiary(final int fyearGe,final int fperiodGe,final int fyearLe,
final int fperiodLe,final Long facctId,final Long fdetailId,final int fposted) {

return (List<Map>) this.jdbcTemplate.execute(new CallableStatementCreator() {

@Override
public CallableStatement createCallableStatement(Connection conn) throws SQLException {
String sql = "{call p_ledger_subsidiaryaccounts(?,?,?,?,?,?,?,?)}";
CallableStatement cs = conn.prepareCall(sql);
cs.setInt(1, fyearGe);
cs.setInt(2, fperiodGe);
cs.setInt(3, fyearLe);
cs.setInt(4, fperiodLe);
cs.setInt(5, fposted);
cs.setLong(6, facctId);
cs.setLong(7, fdetailId);
//cs.registerOutParameter(8, java.sql.Types.OTHER);
cs.registerOutParameter(8, -10);
return cs;
}

},new CallableStatementCallback() {

@Override
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(8);
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
while(rs.next()){
System.out.println(rs.getString("FNumber"));
}
return list;
}
});
}<span></span>

追问

cs.registerOutParameter(8, -10);
这句话你确定能行么？能把sqlserver的游标拿出来么？