如何用ibatis 调用一个带返回光标的存储过程?

Question

<?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="User_cn">
    <cacheModel id="cmccResult" type="LRU" readOnly="true" serialize="false">
      <flushInterval hours="24"/>
    </cacheModel>
    <resultMap id="getResult" class="com.sensky.stat.entity.ResultDx">
      <result property="dayTime"       column="day_time"        jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="connetNum"     column="connectionnum"         jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="time_user"     column="time_users"       jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="bao_user"      column="bao_users"        jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="send_user"     column="send_users"       jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="time_newuser"  column="time_newusers"    jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="bao_newuser"   column="bao_newusers"     jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="send_newuser"  column="send_newusers"    jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="timeLength"    column="time_total_mins"      jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="avgTime"       column="time_avg_min"         jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="time_dillnums"     column="tabs_time"   jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="bao_dillnums"      column="tabs_bao"    jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="send_dillnums"     column="tabs_send"   jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="time_fee"     column="fee_time"   jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="bao_fee"      column="fee_bao"    jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="send_fee"     column="fee_send"   jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="allMoney"     column="fee_total"   jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="ARPUTime"     column="arpu_time"   jdbcType="VARCHAR2"     javaType="java.lang.String"/>
      <result property="ARPUSend"     column="arpu_send"   jdbcType="VARCHAR2"     javaType="java.lang.String"/>
    </resultMap>
    <parameterMap id="single-rs" class="map" >
      <!--parameter property="i_flag"          jdbcType="int"      javaType="java.lang.Integer" mode="IN"/-->
      <!--parameter property="i_province"      jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/-->
      <parameter property="i_starttime"     jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/>
      <parameter property="i_endtime"       jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/>
      <!--parameter property="i_starthour"     jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/-->
      <!--parameter property="i_startmin"      jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/-->
      <!--parameter property="i_endhour"       jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/-->
      <!--parameter property="i_endmin"        jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/-->
      <!--parameter property="i_code"          jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/-->
      <parameter property="output1"         jdbcType="ORACLECURSOR" mode="OUT" javaType="java.lang.Object"/>
    </parameterMap>
    <procedure id="GetSingleEmpRs" parameterMap="single-rs"  resultMap="getResult" >
         <![CDATA[  { call newstat.sp_fee_usercall_dx(?,?,?) }  ]]>
    </procedure>
</sqlMap>
对应的存储过程
CREATE OR REPLACE
PACKAGE BODY "NEWSTAT" AS
PROCEDURE sp_fee_usercall_dx(i_starttime in varchar2,i_endtime in varchar2, o_cursor out t_cursor)
is
begin
/****************************************************************************************************
*
*  电信日报
*  包月-按时-点送
*  查询参数 caller = i_code stime = 2005-03-01 etime = 2005-05-29
****************************************************************************************************/
open o_cursor for
select  day_time ,
        connectionnum,
        time_users,
        bao_users,
        send_users,
                                '1' as time_newusers,
                                '2' as bao_newusers,
                                '3' as send_newusers,
        时长 as time_total_mins,
        平均时长 as time_avg_min,
        time_dillnums as tabs_time,
        bao_dillnums as tabs_bao,
        send_dillnums as tabs_send,
        time_fee as fee_time,
        bao_fee as fee_bao,
        send_fee as fee_send,
        nvl(time_fee,0)+nvl(bao_fee,0)+nvl(send_fee,0) as fee_total,
        arpu_time,
        trunc(send_fee/replace2(send_users),2) as arpu_send
from
(select to_char(a.t_stime,'yyyy-mm-dd') as day_time ,
       b.connectionnum as connectionnum,
       count(distinct t_callerid) as time_users,
       count(distinct b_caller) as bao_users,
       count(distinct s_caller) as send_users,
       '*' as time_newuser,
       '*' as bao_newuser,
       '*' as send_newuser,
       sum(a.t_longmin) as 时长,
       trunc(avg(a.t_longmin),2) as 平均时长,
       count(a.t_callerid) as time_dillnums,
       count(a.b_caller) as bao_dillnums,
       count(a.s_caller) as send_dillnums,
       sum(a.t_feemoney) as time_fee,
       count(distinct b_caller)*30 as bao_fee,
       count(s_caller)*2 as send_fee,
       trunc(avg(a.t_feemoney),2) as arpu_time,
       '2' as arpu_send
    from tbl_bao_time_send_dx a inner join  tbl_operationinfo_dx  b on a.serviceid = b.serviceid --and (a.t_calledid = b.connectionnum or a.s_called is not null)
    where  trunc(a.t_stime) >= to_date(i_starttime,'yyyy-mm-dd') and trunc(a.t_stime) <= to_date(i_endtime,'yyyy-mm-dd')
    group by to_char(a.t_stime,'yyyy-mm-dd'),b.connectionnum);
       return;       
end sp_fee_usercall_dx;
END;
调用出错：

Answer 1

package com.sensky.stat.controller;import org.ncc.core.TeamBeans;
import java.util.List;
import com.sensky.stat.service.TestService;
import com.sensky.stat.entity.ResultDx;
import java.util.Iterator;
import java.util.HashMap;public class test {
    public test() {
        try {          TestService  userDAO = (TestService) TeamBeans.getBean("testService");
          HashMap param=new HashMap();
//          param.put("i_flag",new Integer("1"));
//          param.put("i_province","");
          param.put("i_starttime","2006-07-28");
          param.put("i_endtime","2006-07-30");
//          param.put("i_starthour","00");
//          param.put("i_startmin","00");
//          param.put("i_endhour","23");
//          param.put("i_endmin","59");
//          param.put("i_code","10");
//          param.put("key_no","7");
//          param.put("opertor","=");
//          param.put("groupBy","addr");
//          param.put("showCity","show");
          System.out.println("开始:"+new java.util.Date());
          List list=  userDAO.getJdbcBaseDAO().getListResult("GetSingleEmpRs",param);
          //Object ob=  userDAO.getJdbcBaseDAO().getObject("getConnectionNum_All",param);
          //System.out.println("分次结束:"+new java.util.Date());
         // List list2  = userDAO.getUserListCU(param);
          System.out.println("结束:"+list.size()+new java.util.Date());
         // list.addAll(list2);
          Iterator ilist=list.iterator();
          System.out.println("$sysdate---------i_code-------company------zhujiao-------zongji------hesuan----danwei-----shiji$");
          System.out.println(" A1      A2     a3      a4     a5      a6       a7      a8         a9");
          while(ilist.hasNext()){
          ResultDx cu=(ResultDx) ilist.next();
          System.out.print(": "+cu.getDayTime());
          System.out.print(": "+cu.getConnetNum());
          System.out.print(": "+cu.getTime_user());
          System.out.print(": "+cu.getBao_user());
          System.out.print(": "+cu.getSend_user());
          System.out.print(": "+cu.getTime_newuser());
          System.out.print(": "+cu.getBao_newuser());
          System.out.println(": "+cu.getSend_newuser());
          }
//          System.out.println("总数"+list.size());
        } catch (Exception ex) {ex.printStackTrace();
        }    }    public static void main(String[] args) {
        test test = new test();    }
}

Answer 2

错误：
初始化线程池 create .....1
初始化线程池 create .....2
初始化线程池 create .....3
初始化线程池 create .....4
初始化线程池 create .....5
初始化线程池 create .....6
初始化线程池 create .....7
初始化线程池 create .....8
初始化线程池 create .....9
初始化线程池 create .....10
开始:Wed Aug 02 15:29:05 CST 2006
15:29:05,124  INFO JdbcTransactionObjectSupport:62 - JDBC 3.0 Savepoint class is available
15:29:08,218  INFO XmlBeanDefinitionReader:329 - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
15:29:08,498  INFO SQLErrorCodesFactory:126 - SQLErrorCodes loaded: [DB2, HSQL, MS-SQL, MySQL, Oracle, Informix, PostgreSQL, Sybase]
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];   
--- The error occurred in xmlconfig/proce.xml.  
--- The error occurred while applying a parameter map.  
--- Check the User_cn.single-rs.  
--- Check the results (failed to retrieve results).  
--- Cause: java.lang.NullPointerException; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in xmlconfig/proce.xml.  
--- The error occurred while applying a parameter map.  
--- Check the User_cn.single-rs.  
--- Check the results (failed to retrieve results).  
--- Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in xmlconfig/proce.xml.  
--- The error occurred while applying a parameter map.  
--- Check the User_cn.single-rs.  
--- Check the results (failed to retrieve results).  
--- Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:228)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:165)
at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:187)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:226)
at com.sensky.stat.ibatisdao.UserDAO.getListResult(UserDAO.java:30)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:266)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:100)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
at $Proxy0.getListResult(Unknown Source)
at com.sensky.stat.controller.test.<init>(test.java:30)
at com.sensky.stat.controller.test.main(test.java:57)
Caused by: java.lang.NullPointerException
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:375)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:291)
at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
... 22 moreCaused by: 
java.lang.NullPointerException
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:375)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:291)
at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:228)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:165)
at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:187)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:226)
at com.sensky.stat.ibatisdao.UserDAO.getListResult(UserDAO.java:30)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:266)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:100)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
at $Proxy0.getListResult(Unknown Source)
at com.sensky.stat.controller.test.<init>(test.java:30)
at com.sensky.stat.controller.test.main(test.java:57)

Answer 3

你写的太多了，我给你一个简单点的例子：
<parameterMap id="swapParameters" class="map">
<parameter property="nf"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="yf"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="zbr"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="zbrq"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>   
</parameterMap>
          
<procedure id="p_m_ccplwqjxm" parameterMap="swapParameters"> 
{ call p_m_ccplwqjxm(?,?,?,?)}
</procedure>

Answer 4

谢谢楼上的兄弟，你这个例子太简单了，我需要的是一个调用返回结果集的存储过程。返回orcale 的光标集，就是多记录的结果集。

Answer 5

还有，存储过程是没有问题的。通过jdbc可以调用

Answer 6

希望和朋友讨论 我的 MSN:  [email protected] 我叫：简易