Oracle自定义类型及存储过程如下:
CREATE OR REPLACE TYPE ARR_OBJECT AS OBJECT( TRADENO VARCHAR2(32), DEAL_TYPE VARCHAR2(2), TURNOVER VARCHAR2(20) );
CREATE OR REPLACE TYPE WEALTH_DEAL_DETAIL_ARRAY IS TABLE OF ARR_OBJECT;
PROCEDURE pro_wealth_deal_detail_array(P_FUND_ACCNO IN VARCHAR2, P_OPEN_DEALNO IN VARCHAR2, P_HOLD_ACCNO IN VARCHAR2, P_MARKET_NO IN VARCHAR2, P_SECURNO IN VARCHAR2, P_TRADE_DATE IN DATE, P_ARR OUT WEALTH_DEAL_DETAIL_ARRAY) IS CURSOR CUR_WEALTH_DEAL_DETAIL IS SELECT TRADENO,DEAL_TYPE,TURNOVER FROM WEALTH_DEAL_DETAIL T WHERE T.FUND_ACCNO LIKE P_FUND_ACCNO AND T.OPEN_DEALNO LIKE P_OPEN_DEALNO AND T.HOLD_ACCNO LIKE P_HOLD_ACCNO AND T.MARKETNO LIKE P_MARKET_NO AND T.SECURNO LIKE P_SECURNO AND T.TRADE_DATE <= TRUNC(P_TRADE_DATE) + 1 AND T.TRADE_DATE >= TRUNC(P_TRADE_DATE) ORDER BY TRADENO ASC;
V_WEALTH_DEAL_DETAIL CUR_WEALTH_DEAL_DETAIL%ROWTYPE; V_IDX INT := 0; BEGIN P_ARR := WEALTH_DEAL_DETAIL_ARRAY(); OPEN CUR_WEALTH_DEAL_DETAIL; LOOP FETCH CUR_WEALTH_DEAL_DETAIL INTO V_WEALTH_DEAL_DETAIL; EXIT WHEN CUR_WEALTH_DEAL_DETAIL%NOTFOUND; P_ARR.EXTEND; P_ARR(V_IDX) := ARR_OBJECT(V_WEALTH_DEAL_DETAIL.TRADENO,V_WEALTH_DEAL_DETAIL.DEAL_TYPE,V_WEALTH_DEAL_DETAIL.TURNOVER); V_IDX := V_IDX + 1; END LOOP; CLOSE CUR_WEALTH_DEAL_DETAIL; END pro_wealth_deal_detail_array; |
Java代码:
1. log4j.properties:
log4j.rootLogger=DEBUG, stdout log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n log4j.logger.java.sql.PreparedStatement=DEBUG log4j.logger.com = DEBUG |
2. sqlmap-config.xml:
<?xml version="1.0" encoding="GB2312"?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <!-- The properties (name=value) in the file specified here can be used placeholders in this config file (e.g. “${driver}”. The file is relative to the classpath and is completely optional. --> <properties resource="com/ibatis/customtype/DB.properties" /> <settings cacheModelsEnabled="true" enhancementEnabled="false" lazyLoadingEnabled="false" maxRequests="3000" maxSessions="3000" maxTransactions="3000" useStatementNamespaces="false"/> <!-- Configure a datasource to use with this SQL Map using SimpleDataSource. Notice the use of the properties from the above resource --> <transactionManager type="JDBC" > <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="${driver}"/> <property name="JDBC.ConnectionURL" value="${url}"/> <property name="JDBC.Username" value="${username}"/> <property name="JDBC.Password" value="${password}"/> </dataSource> </transactionManager>
<sqlMap resource="com/ibatis/customtype/sqlmap-mapping-base.xml"/> </sqlMapConfig>
|
3. DB.properties:
driver = oracle.jdbc.driver.OracleDriver url = jdbc:oracle:thin:@10.25.18.29:1593:test username = scott password = tiger |
4. Sqlmap-mapping-base.xml:
<?xml version="1.0" encoding="GB2312"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="base"> <resultMap id="customArray-result-map" class="com.ibatis.customtype.WealthDealDetailDto"> <result property="tradeno" column="TRADENO" /> <result property="dealType" column="DEAL_TYPE" /> <result property="turnover" column="TURNOVER" /> </resultMap> <parameterMap id="customArray-map" class="java.util.Map"> <!-- 参数列表 --> <parameter property="fundAccno" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN" /> <parameter property="openDealno" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN" /> <parameter property="holdAccno" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN" /> <parameter property="marketno" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN" /> <parameter property="securno" javaType="java.lang.String" jdbcType="VARCHAR2" mode="IN" /> <parameter property="tradeDate" javaType="java.util.Date" jdbcType="DATE" mode="IN" /> <parameter property="result" javaType="java.util.List" jdbcType="ARRAY" mode="OUT" typeName="WEALTH_DEAL_DETAIL_ARRAY" typeHandler="com.ibatis.customtype.ArrayTypeHandler" /> </parameterMap> <procedure id="get-wealth-deal-detail-array" parameterMap="customArray-map"> {CALL test.pro_wealth_deal_detail_array(?,?,?,?,?,?,?)} </procedure> </sqlMap> |
5. DaoSupport.java:
package com.ibatis.customtype;
import java.io.IOException; import java.io.Reader;
import org.apache.log4j.Logger;
import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class DaoSupport { private Logger logger = Logger.getLogger(DaoSupport.class); private SqlMapClient sqlMapClient = null;
public SqlMapClient getSqlMapClient() { return sqlMapClient; }
public DaoSupport() { Reader reader; try { reader = Resources.getResourceAsReader("com/ibatis/customtype/sqlmap-config.xml"); sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader); } catch (IOException e) { logger.error("读取Ibatis配置文件出错!"); logger.error(e); } } }
|
6. WealthDealDetailDto.java:
package com.ibatis.customtype;
public class WealthDealDetailDto { @Override public String toString() { return tradeno + "\t" + dealType + "\t" + turnover; } private String tradeno; private String dealType; private String turnover; public String getTradeno() { return tradeno; } public void setTradeno(String tradeno) { this.tradeno = tradeno; } public String getDealType() { return dealType; } public void setDealType(String dealType) { this.dealType = dealType; } public String getTurnover() { return turnover; } public void setTurnover(String turnover) { this.turnover = turnover; } }
|
7. ArrayTypeHandler.java:
package com.ibatis.customtype;
import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;
import oracle.jdbc.OracleTypes; import oracle.sql.Datum; import oracle.sql.STRUCT;
import com.ibatis.sqlmap.engine.type.TypeHandler;
public class ArrayTypeHandler implements TypeHandler {
public boolean equals(Object arg0, String arg1) { // TODO Auto-generated method stub return false; }
public Object getResult(ResultSet arg0, String arg1) throws SQLException { // TODO Auto-generated method stub return null; }
public Object getResult(ResultSet arg0, int arg1) throws SQLException { // TODO Auto-generated method stub return null; }
public Object getResult(CallableStatement cs, int arg1) throws SQLException { List<WealthDealDetailDto> dtos = new ArrayList<WealthDealDetailDto>(); ResultSet rs = cs.getArray(arg1).getResultSet(); while (rs.next()) { Datum[] data = ((STRUCT)rs.getObject(2)).getOracleAttributes(); WealthDealDetailDto dto = new WealthDealDetailDto(); if (data[0] != null) { dto.setTradeno(new String(data[0].getBytes())); } if (data[1] != null) { dto.setDealType(new String(data[1].getBytes())); } if (data[2] != null) { dto.setTurnover(new String(data[2].getBytes())); } dtos.add(dto); } return dtos; }
public void setParameter(PreparedStatement arg0, int arg1, Object arg2, String arg3) throws SQLException { // TODO Auto-generated method stub
}
public Object valueOf(String arg0) { // TODO Auto-generated method stub return null; }
}
|
8. ArrayTypeDao.java:
package com.ibatis.customtype;
import java.sql.SQLException; import java.util.List; import java.util.Map;
import org.apache.log4j.Logger;
public class ArrayTypeDao extends DaoSupport { private Logger logger = Logger.getLogger(ArrayTypeDao.class);
@SuppressWarnings("unchecked") public List<WealthDealDetailDto> getWealthDealDetailList(Map<Object, Object> paramtersMap) { try { getSqlMapClient().queryForList("get-wealth-deal-detail-array", paramtersMap); List<WealthDealDetailDto> dtos = (List<WealthDealDetailDto>) paramtersMap.get("result"); return dtos; } catch (SQLException e) { logger.error("调用存储过程出错!"); logger.error(e); } return null; } }
|
9. 测试类Test.java:
package com.ibatis.customtype;
import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map;
import org.apache.log4j.Logger;
public class Test { private static Logger logger = Logger.getLogger(Test.class); /** * @param args */ public static void main(String[] args) { ArrayTypeDao atd = new ArrayTypeDao(); Map<Object, Object> paramtersMap = new HashMap<Object, Object>(); paramtersMap.put("fundAccno","%"); paramtersMap.put("openDealno","%"); paramtersMap.put("holdAccno","%"); paramtersMap.put("marketno","%"); paramtersMap.put("securno","%"); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, 2010); c.set(Calendar.MONTH,11); c.set(Calendar.DATE, 24); Date date = c.getTime(); paramtersMap.put("tradeDate",date); List<WealthDealDetailDto> dtos = atd.getWealthDealDetailList(paramtersMap); logger.info("调用存储过程返回数据:" + dtos.size()); if (dtos != null && dtos.size() > 0) { logger.info("Tradeno\tDeal_type\tTurnover"); for (int i=0;i<dtos.size();i++) { WealthDealDetailDto dto = dtos.get(i); logger.info(dto); } } }
}
|
说明:首先,我自定义了一个Object ARR_OBJECT.然后定义了一个类型WEALTH_DEAL_DETAIL_ARRAY,指明是ARR_OBJECT类型。这样就相当于Java中的List<Object>。在存储过程中使用WEALTH_DEAL_DETAIL_ARRAY作为OUT类型参数,根据传入的5个IN类型参数从WEALTH_DEAL_DETAIL表查询符合条件的数据,并放入WEALTH_DEAL_DETAIL_ARRAY中。在Java中,在Ibatis的配置文件中OUT类型参数jdbcType需指定为ARRAY,并且需要指定类型的名字,以及处理该类型的Handler。那么在Handler中呢,因为我们最终需要的是List<WealthDealDetailDto>类型,那么就需要自己从结果集中组装数据,封装成此类型。最后从传入的参数Map中获取sqlmap中配置的property,即得到此OUT参数的结果。