Ibatis调用Oracle存储过程返回自定义类型

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参数的结果。