之前的公司是互联网基本上会规避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;