SQL SERVER 存储过程返回游标类型 JDBC相应的游标类型怎么办?

luomansi 发布于 2013/01/06 21:35
阅读 3K+
收藏 0
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;
 }
 });
 }
加载中
0
luomansi
luomansi

引用来自“付乐”的答案

CallableStatement cstmt  = con.prepareCall("{?=call pro_fristCount(?)}");
			cstmt.registerOutParameter(1, Types.REAL);
			cstmt.setInt(2, 10);
			ResultSet rs = cstmt.executeQuery();
			while(rs.next()){
				System.out.println("-------- "+rs.getString("deptId"));
			}
你确定  这个sql是这样写的?上面是原生jdbc调用方式,很久没写了。试试这样可以么。

1.把定义存储过程改为如下:
ALTER PROCEDURE [dbo].[p_ledger_subsidiaryaccounts] 
	@fyearGe int,       --年
	@fperiodGe int,     --期
	@fyearLe int,       --年
	@fperiodLe int,     --期
	@fposted int,     --是否过账 1,表示包含未过账;0,表示不包含未过账数据
	@facctId bigint,  --科目ID
	@fdetailId bigint--, --核算项目ID
        --这个游标参数去掉相应的返回采用Select语句就可以了
	--@CURSOR_subsidiaryaccounts CURSOR VARYING OUTPUT -- 返回明细账的游标
AS
BEGIN 	
	SET NOCOUNT ON;
	/*创建临时表*/
        .....
        .....
  --1.对返回的游标进行数据的绑定 , 把下面注释的代码去掉用Select语句直接返回结果集就好了
	 /* 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 */
	SELECT ID,  
            FDate,
		    FYear,
		    FPeriod,
			FVoucherId,
			FNumber,
			FExplanation,
			Fdebit,
			Fcridt,
			Fdc,
			Fendbalance
         FROM #tmp
         Order By ID asc,FYear,Fperiod 
    truncate table #tmp
    drop table #tmp 
END
2.相应的java调用改为下:(本人采用的是SpringJdbc模版)
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);
 return cs;
 }
 
 },new CallableStatementCallback() { 
 
 @Override
 public Object doInCallableStatement(CallableStatement cs) 
 throws SQLException, DataAccessException {
 ResultSet rs = cs.executeQuery();
 List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); 
 while(rs.next()){
 Map map = new HashMap<String, Object>();
 map.put("ID", rs.getLong("ID"));
 map.put("FDate", rs.getString("FDate"));
 map.put("FYear", rs.getInt("FYear"));
 map.put("FPeriod", rs.getInt("FPeriod"));
 map.put("FVoucherId", rs.getLong("FVoucherId"));
 map.put("FNumber", rs.getString("FNumber"));
 map.put("FExplanation", rs.getString("FExplanation"));
 map.put("Fdebit", rs.getDouble("Fdebit"));
 map.put("Fcridt", rs.getDouble("Fcridt"));
 map.put("Fdc", rs.getString("Fdc"));
 map.put("Fendbalance", rs.getDouble("Fendbalance"));
 list.add(map);
 }
 return list;
 }
 });
 }
把相应的代码改成上面就可以了,谢谢你哦!
红薯
红薯
记得设置最佳答案
0
付乐
付乐

CallableStatement cstmt  = con.prepareCall("{?=call pro_fristCount(?)}");
			cstmt.registerOutParameter(1, Types.REAL);
			cstmt.setInt(2, 10);
			ResultSet rs = cstmt.executeQuery();
			while(rs.next()){
				System.out.println("-------- "+rs.getString("deptId"));
			}
你确定  这个sql是这样写的?上面是原生jdbc调用方式,很久没写了。试试这样可以么。

luomansi
luomansi
@CURSOR_subsidiaryaccounts CURSOR VARYING OUTPUT -- 返回明细账的游标 这是我定义存储过程用于返回结果集的游标 VARYING OUTPUT 类型 我用的是SpringJDBC模版调用存储过程,我要在JAVA程序中接收游标类型需要在参数部分注册一个返回值类型。
0
付乐
付乐

引用来自“luomansi”的答案

引用来自“付乐”的答案

CallableStatement cstmt  = con.prepareCall("{?=call pro_fristCount(?)}");
			cstmt.registerOutParameter(1, Types.REAL);
			cstmt.setInt(2, 10);
			ResultSet rs = cstmt.executeQuery();
			while(rs.next()){
				System.out.println("-------- "+rs.getString("deptId"));
			}
你确定  这个sql是这样写的?上面是原生jdbc调用方式,很久没写了。试试这样可以么。

1.把定义存储过程改为如下:
ALTER PROCEDURE [dbo].[p_ledger_subsidiaryaccounts] 
	@fyearGe int,       --年
	@fperiodGe int,     --期
	@fyearLe int,       --年
	@fperiodLe int,     --期
	@fposted int,     --是否过账 1,表示包含未过账;0,表示不包含未过账数据
	@facctId bigint,  --科目ID
	@fdetailId bigint--, --核算项目ID
        --这个游标参数去掉相应的返回采用Select语句就可以了
	--@CURSOR_subsidiaryaccounts CURSOR VARYING OUTPUT -- 返回明细账的游标
AS
BEGIN 	
	SET NOCOUNT ON;
	/*创建临时表*/
        .....
        .....
  --1.对返回的游标进行数据的绑定 , 把下面注释的代码去掉用Select语句直接返回结果集就好了
	 /* 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 */
	SELECT ID,  
            FDate,
		    FYear,
		    FPeriod,
			FVoucherId,
			FNumber,
			FExplanation,
			Fdebit,
			Fcridt,
			Fdc,
			Fendbalance
         FROM #tmp
         Order By ID asc,FYear,Fperiod 
    truncate table #tmp
    drop table #tmp 
END
2.相应的java调用改为下:(本人采用的是SpringJdbc模版)
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);
 return cs;
 }
 
 },new CallableStatementCallback() { 
 
 @Override
 public Object doInCallableStatement(CallableStatement cs) 
 throws SQLException, DataAccessException {
 ResultSet rs = cs.executeQuery();
 List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); 
 while(rs.next()){
 Map map = new HashMap<String, Object>();
 map.put("ID", rs.getLong("ID"));
 map.put("FDate", rs.getString("FDate"));
 map.put("FYear", rs.getInt("FYear"));
 map.put("FPeriod", rs.getInt("FPeriod"));
 map.put("FVoucherId", rs.getLong("FVoucherId"));
 map.put("FNumber", rs.getString("FNumber"));
 map.put("FExplanation", rs.getString("FExplanation"));
 map.put("Fdebit", rs.getDouble("Fdebit"));
 map.put("Fcridt", rs.getDouble("Fcridt"));
 map.put("Fdc", rs.getString("Fdc"));
 map.put("Fendbalance", rs.getDouble("Fendbalance"));
 list.add(map);
 }
 return list;
 }
 });
 }
把相应的代码改成上面就可以了,谢谢你哦!
嗯 确实  记得曾今也遇到这个问题  直接select 就是直接返回结果集了。在oracle中就用你最开始的那种方式开启游标返回的吧。
返回顶部
顶部