Problem while getting multiple cursor includes null(result set)

[nozisim]

Hi guys.
I'm sorry to my English .. I hope you understand .


Environment : Oracle10g, Spring2.0

I have a problem while getting multiple cursor whe using "org.springframework.jdbc.object.StoredProcedu re"


My Code is it below.

package net.bogo.web.isn.board.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import net.bogo.web.common.model.BogoCms;
import oracle.jdbc.OracleTypes;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class ListSqlProc extends StoredProcedure{
	
	private final Log logger = LogFactory.getLog(ListSqlProc.class);
	
	private static final String ListSql = "pckbbsquery.list";
	
	ListSqlProc(DataSource dataSource) {
		
	    super(dataSource, ListSql);
	    
	    //setDataSource(dataSource);
	    setFunction(false);
	    setSql(ListSql);

	    declareParameter(new SqlParameter("insessionid", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("insiteid", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("inmenuid", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("inactionid", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("incondition", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("interminal", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("inosuser", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("inmodule", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("indmluserid", OracleTypes.VARCHAR));
	    declareParameter(new SqlParameter("indmlip", OracleTypes.VARCHAR));
	    
	    declareParameter(new SqlOutParameter("outresult", OracleTypes.VARCHAR));
	    declareParameter(new SqlOutParameter("outrowcount", OracleTypes.INTEGER));
	    declareParameter(new SqlOutParameter("outnotice", OracleTypes.VARCHAR));
	    declareParameter(new SqlOutParameter("outstring", OracleTypes.VARCHAR));
	    declareParameter(new SqlOutParameter("outaction", OracleTypes.VARCHAR));
	    
	    declareParameter(new SqlOutParameter("cursor1", OracleTypes.CURSOR, new FileMapper())); //==> file list cursor

	    declareParameter(new SqlOutParameter("cursor2", OracleTypes.CURSOR, new UserMapper())); //==> shared user cursor

	    declareParameter(new SqlOutParameter("cursor3", OracleTypes.CURSOR, new CommentMapper())); //==> comment cursor
	    compile();
	}
	
	
	 
	Map execute(BogoCms cms) {
		
		
	    Map inParams = new HashMap(10);
	    Map retmap 	= new HashMap();
	    List list1 	= new ArrayList();
	    List list2 	= new ArrayList();
	    List list3 	= new ArrayList();

	    Map results = new HashMap();
	    try {
	    	
		    inParams.put("insessionid", cms.getSessionid());
		    inParams.put("insiteid", cms.getSiteid());
		    inParams.put("inmenuid", cms.getMenuid());
		    inParams.put("inactionid", cms.getActionid());
		    inParams.put("incondition", cms.getCondition());
		    inParams.put("interminal", cms.getTerminal());
		    inParams.put("inosuser", cms.getOsuser());
		    inParams.put("inmodule", cms.getModule());
		    inParams.put("indmluserid", cms.getUserid());

  		    inParams.put("indmlip", cms.getIp());
			
		    results = execute(inParams);
			
		    list1 = (List) results.get("cursor1");
		    list2 = (List) results.get("cursor2");
		    list3 = (List) results.get("cursor3");
			
		} catch (Exception e) {
			logger.info("ListSqlProc => " + e);
		} finally {
			return retmap;
		}
	}
}


public final class ListSqlMapper implements RowMapper{

	public Object mapRow(ResultSet rs, int rownum) throws SQLException {
		// TODO Auto-generated method stub
		
		BogoCms Cms = new BogoCms();
		Cms.setTitle(rs.getString("title"));
		Cms.setKname(rs.getString("userkname"));
		Cms.setLong_dmltime(rs.getString("createtime").substring(0, 10));
		Cms.setKey(rs.getInt("key"));
		Cms.setRank(rs.getInt("rank"));
		Cms.setReadcount(rs.getInt("readcount"));
		
		if(rs.getInt("key") == -1) Cms.setRank_nm("공지");
		else						Cms.setRank_nm(IsNull.NullString(String.valueOf(rs.getInt("rank"))));
		
		Cms.setContentsid(rs.getInt("contentsid"));
		Cms.setDmltime(rs.getString("createtime"));
		Cms.setFilescount(rs.getInt("filescount"));
		
		return Cms;
	}
}

I've declared three cursors.
And excuted then parsed result cursors.


When each cursor is not empty(has one more result set) the whole code works well.

But when one more cursor has no result set the code works not totaly.
(It means..... I can't get any result for the exception even though other cursor may have result sets)

The error does not occurs after null cursor but before "results = execute(inParams);"

I'm in very difficult situation with this problem...

Please give me advices..

If is there any information that I need to write, please tell me.

[neu_gonio]

You may want to use an ORM tool for this so that you don't have to handle the pipelining "manually".

for you case, think ibatis (http://opensource.atlassian.com/conf...ored+procedure)
would do the trick together with some spring declarative transaction
http://static.springframework.org/sp...ansaction.html

[nozisim]

Thanks for help.
Do you mean.. I have to use ORM to solve this problem??
I wander that is there any other solution except using ORM(like ibatis)