ibatis2.0使用procedure

之前的公司是互联网基本上会规避procedure这种东西,有的话也会是比较简单的实现.到了新公司全部使用procedure(原因不详,个人觉得是拍脑袋决定).ibatis2 2.1.7.597版本中的dtd有bug,会直接导致无法识别parameterMap标签下子标签resultMap！而是用比较高的版本会有jdbcType="ORACLECURSOR"无法支持的情况！后来发现使用2.3.4.726版本问题解决!
开发过程中引起的异常:
Check the output parameters (retrieval of output parameters failed)等类似异常，这个异常是把上面jdbcType="ORACLECURSOR"改为jdbcType="CURSOR"导致。主要的问题点1.dtd文件的不支持 2.对oracle cursor的支持bug!!
相关的资源:
http://www.alisdn.com/wordpress/?p=1362
http://www.apachebookstore.com/confluence/oss/display/IBATIS/How+do+I+call+a+stored+procedure?decorator=printable
相关配置如下:

		<dependency>
		  <groupId>org.apache.ibatis</groupId>
		  <artifactId>ibatis-sqlmap</artifactId>
		  <version>2.3.4.726</version>
		</dependency>

		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>

		<!-- Logging -->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.9</version>
		</dependency>

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc14</artifactId>
			<version>10.2.0.4.0</version>
		</dependency>

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <properties resource ="db.properties"/>
    <transactionManager type ="JDBC">
        <dataSource type="DBCP">
            <property  name = "JDBC.Driver"  value ="${driver}"/>
            <property  name = "JDBC.ConnectionURL"  value ="${url}" />
            <property  name = "JDBC.Username"  value = "${user}" />
            <property  name = "JDBC.Password"  value = "${password}" />
            <property  name = "Pool.MaximumWait"  value ="30000" />
		    <property  name = "Pool.MaximumActiveConnections" value = "15" />
		    <property  name = "Pool.MaximumIdleConnections" value = "15" />
        </dataSource>
    </transactionManager>
    <sqlMap resource="com/ssc/gce/biz/bean/RecDataSqlMapper.xml" />
</sqlMapConfig>

 

<?xml version="1.0" encoding="UTF-8"?>  

<!DOCTYPE sqlMap
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="RecData">

	<typeAlias alias="BaseRecData" type="com.ssc.gce.biz.bean.BaseRecData" />

	<resultMap id="rec_result" class="BaseRecData">
	   <result property="guid" column="guid"/>
	   <result property="paymenttype" column="paymenttype"/>
	   <result property="amount" column="amount"/>
	   <result property="payOrReceive" column="payorreceive"/> 
	   <result property="effectiveDate" column="effectivedate"/>
	   <result property="valueDate" column="valueDate"/>
	   <result property="tradeId" column="tradeid"/>
	   <result property="assetId" column="assetid"/>
	   <result property="assetDescription" column="assetdescription" />
	   <result property="sourceId" column="sourceid"/>
	   <result property="postDate" column="postdate"/>
	   <result property="transactioncode" column="transactioncode"/>
	   <result property="classCode" column="classcode"/>
	   <result property="fundNumber" column="fund_number"/>
	   <result property="broker" column="broker"/>
	   <result property="account" column="account"/>
   </resultMap>
   
	<parameterMap id="rec_data_query" class="java.util.HashMap"> 
         <parameter property="guid" jdbcType="VARCHAR" mode="IN" />
		 <parameter property="sourceId" jdbcType="VARCHAR" mode="IN" />
		 <parameter property="fundNumber" jdbcType="VARCHAR" mode="IN" />
		 <parameter property="settlementDate" jdbcType="DATE" mode="IN" />
		 <parameter property="paymentType" jdbcType="VARCHAR" mode="IN" />
		 <parameter property="amount" jdbcType="DOUBLE" mode="IN" />
		 <!--out result  -->
		 <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"  resultMap="rec_result" />
    </parameterMap>
   
    <procedure id="RecDataDao.getRecData" parameterMap="rec_data_query" >
   		{call gc_getdata_pkg.getdata_uc1_prc(?,?,?,?,?,?,?)}
    </procedure>
   
</sqlMap>

 

public class IbatisRecDataDao extends MyIbatisClient implements RecDataDao {
	
	private static Logger logger = Logger.getLogger(IbatisRecDataDao.class);

	public List<BaseRecData> getRecData(RecDataQuery query) {
		try {

			Map<String, Object> queryMap = new HashMap<String, Object>();
			
			queryMap.put("guid",query.getGuid());
			queryMap.put("sourceId",query.getSourceId());
			queryMap.put("fundNumber",query.getFundNumber());
			queryMap.put("settlementDate",query.getSettlementDate());
			queryMap.put("paymentType",query.getPaymentType());
			queryMap.put("amount",query.getAmount());
//			queryMap.put("result",query.getResult());
//			sqlClient.queryForList("RecDataDao.getRecData", queryMap);
			sqlClient.queryForObject("RecDataDao.getRecData", queryMap);  
			return (List<BaseRecData>)queryMap.get("result");
		} catch (Exception e) {
			logger.error("IbatisRecDataDao.getRecData",e);
		}
		return null;
	}

}

 

create or replace
PACKAGE         gc_getdata_pkg
AS
  TYPE rec_Data IS RECORD
  (
     guid                     fpml_cash.guid%TYPE,
     paymenttype              paymenttoaccount.paymenttype%TYPE,
     amount                   paymenttoaccount.amount%TYPE,
     payorreceive             paymenttoaccount.payorreceive%TYPE,
     effectivedate            tradedetails.effectivedate%TYPE,
     valueDate                tradedetails.settlementdate%TYPE,
     tradeid                  versionedtransactionid.transactionid%TYPE,
     assetid                  tradedetails.assetid%TYPE,
     assetdescription         tradedetails.assetdescription%TYPE,
     sourceid                 fpml_cash.sourceid%TYPE, 
     postdate                 tradedetails.tradedate%TYPE,
     transactioncode          tradedetails.transactiontype%TYPE,
     classcode                VARCHAR2(100),
     fund_number              party.partyid%TYPE,
     broker                   party.partyid%TYPE,
     account                  party.partyid%TYPE
  );
  
  TYPE refcur IS REF CURSOR RETURN rec_Data; 

  /*****************************************************************************************
  name: getdata_uc1_prc
  parameters:     pn_guid             IN   NUMBER,
                  pn_sourceid         IN   NUMBER,
                  pn_partyid          IN   NUMBER,
                  pd_settlementdate   IN   DATE,
                  pv_paymenttype      IN   VARCHAR2,
                  pn_amount           IN   NUMBER,
                  cur_data            OUT  refcur
  create date: 2011-05-24
  creater: ×××
  desc: This procedure is to get the data via the input conditions
        The input parametes can be generated together with any condition
        For instance: call getdate_uc1_prc(2870112, null, null, null, null, null, curOut);
                   or call getdate_uc1_prc(null, 1, 'PC1E', null, null, null, curOut);
  *****************************************************************************************/
  PROCEDURE getdata_uc1_prc
  (
    pv_guid             IN   VARCHAR2,
    pv_sourceid         IN   VARCHAR2,
    pv_fund_number      IN   VARCHAR2,
    pd_settlementdate   IN   DATE,
    pv_paymenttype      IN   VARCHAR2,
    pn_amount           IN   NUMBER,
    
    cur_data            OUT  refcur
  );

  PROCEDURE getTestData_prc
  (
    pv_startNum       IN   VARCHAR2,
    pv_endNum         IN   VARCHAR2,
    cur_data          OUT  refcur
  );

  PROCEDURE getTestData_prc
  (
    cur_data          OUT  refcur
  );
  
END gc_getdata_pkg;