ibatis 调用oracle存储过程 返回cursor

view plaincopy to clipboardprint?
CREATE TABLE "USER_BASIC_INFO"  
  ( "USER_ID" NUMBER(10,0) NOT NULL ENABLE,  
"USER_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,  
"REAL_NAME" VARCHAR2(50 BYTE),  
"PASSWORD" VARCHAR2(32 BYTE) NOT NULL ENABLE,  
"EMAIL" VARCHAR2(50 BYTE) NOT NULL ENABLE,  
"USER_TYPE" VARCHAR2(50 BYTE),  
"USER_STATE" VARCHAR2(50 BYTE),  
"LOGIN_COUNT" NUMBER(8,0) DEFAULT 0,  
"QUESTION" VARCHAR2(200 BYTE),  
"ANSWER" VARCHAR2(200 BYTE),  
"ACCOUNT" VARCHAR2(50 BYTE) DEFAULT -1,  
"BRANCH_ID" VARCHAR2(50 BYTE) DEFAULT -1,  
"CREATE_DATE" DATE DEFAULT sysdate,  
"MODIFY_DATE" DATE DEFAULT sysdate,  
"LAST_VISIT" DATE DEFAULT sysdate,  
"USER_LEVEL" VARCHAR2(50 BYTE),  
"EXPERIENCE_TIME" NUMBER(4,0) DEFAULT 0,  
"CUSTID" NUMBER(20,0),  
CONSTRAINT "PK_USER_BASIC_INFO" PRIMARY KEY ("USER_ID") 
  ) 

view plaincopy to clipboardprint?
CREATE TABLE "USER_EXTEND_INFO"  
  ( "USER_ID" NUMBER(10,0) NOT NULL ENABLE,  
"SEX" VARCHAR2(50 BYTE),  
"BIRTHDAY" DATE,  
"RACE" VARCHAR2(50 BYTE),  
"PROVINCE" VARCHAR2(50 BYTE),  
"CITY" VARCHAR2(50 BYTE),  
"CARD_TYPE" VARCHAR2(50 BYTE),  
"CARD_ID" VARCHAR2(50 BYTE),  
"TELEPHONE" VARCHAR2(50 BYTE),  
"MOBILEPHONE" VARCHAR2(50 BYTE),  
"FAX" VARCHAR2(50 BYTE),  
"ADDRESS" VARCHAR2(100 BYTE),  
"POST_CODE" VARCHAR2(50 BYTE),  
"DEGREE" VARCHAR2(50 BYTE),  
"VOCATION" VARCHAR2(50 BYTE),  
"SOURCE" VARCHAR2(100 BYTE),  
CONSTRAINT "PK_USER_EXTEND_INFO" PRIMARY KEY ("USER_ID") 
  ) 

view plaincopy to clipboardprint?
create or replace PACKAGE Types 
AS 
    TYPE ref_cursor IS REF CURSOR; 
END; 

view plaincopy to clipboardprint?
create or replace PROCEDURE QUERY_USER_BASIC_INFO 
( P_USER_NAME IN USER_BASIC_INFO.USER_NAME % TYPE 
, P_REAL_NAME IN USER_BASIC_INFO.REAL_NAME % TYPE 
, P_USER_TYPE IN USER_BASIC_INFO.USER_TYPE % TYPE 
, P_BRANCH_ID IN USER_BASIC_INFO.BRANCH_ID % TYPE 
, P_BEGIN_DATE IN DATE 
, P_END_DATE IN DATE 
, P_BEGIN IN NUMBER 
, P_END IN NUMBER 
, P_RESULT OUT NUMBER 
, P_CURSOR OUT TYPES.ref_cursor 
) AS 
v_sql varchar2(1000); 
v_sqlCount varchar2(1000); 
BEGIN 
        v_sql:='SELECT * FROM ( 
            SELECT 
                row_.*, 
                ROWNUM rownum_ 
            FROM ( 
                SELECT e.*,f.SEX, 
          f.BIRTHDAY, 
          f.RACE, 
          f.PROVINCE, 
          f.CITY, 
          f.CARD_TYPE, 
          f.CARD_ID, 
          f.TELEPHONE, 
          f.MOBILEPHONE, 
          f.FAX, 
          f.ADDRESS, 
          f.POST_CODE, 
          f.DEGREE, 
          f.VOCATION, 
          f.SOURCE,g.key_name FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id 
          left join dictionary g on e.user_type=g.key_code and g.parent_id=11 
        where 1=1'; 
        IF P_USER_NAME IS NOT NULL THEN 
            v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || ''''; 
        ELSE IF P_REAL_NAME IS NOT NULL THEN 
            v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || ''''; 
        ELSE IF P_USER_TYPE IS NOT NULL THEN 
            v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || ''''; 
        ELSE IF P_BRANCH_ID IS NOT NULL THEN 
            v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || ''''; 
        ELSE IF P_BEGIN_DATE IS NOT NULL THEN 
            v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE; 
        END IF; 
        END IF; 
        END IF; 
        END IF; 
        END IF; 
            v_sql := v_sql || ') row_ WHERE ROWNUM <= ' || P_END || ') WHERE rownum_ >' || P_BEGIN; 
        --DBMS_OUTPUT.PUT_LINE(v_sql); 
        open P_CURSOR for v_sql; 
 
        v_sqlCount := 'SELECT count(1) FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id'; 
        IF P_USER_NAME IS NOT NULL THEN 
            v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || ''''; 
        ELSE IF P_REAL_NAME IS NOT NULL THEN 
            v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || ''''; 
        ELSE IF P_USER_TYPE IS NOT NULL THEN 
            v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || ''''; 
        ELSE IF P_BRANCH_ID IS NOT NULL THEN 
            v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || ''''; 
        ELSE IF P_BEGIN_DATE IS NOT NULL THEN 
            v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE; 
        END IF; 
        END IF; 
        END IF; 
        END IF; 
        END IF; 
        EXECUTE IMMEDIATE v_sqlCount into P_RESULT; 
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN 
            P_RESULT := 9006; 
        WHEN OTHERS THEN 
            P_RESULT := 9005; 
END QUERY_USER_BASIC_INFO; 

view plaincopy to clipboardprint?
<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> 
         
<!-- iBatis SQL Map 文件 --> 
<sqlMap namespace="com.cssweb.idm.user.pojo.UserBasicInfo"> 
    <typeAlias alias="userBasicInfo" type="com.cssweb.idm.user.pojo.UserBasicInfo"/> 
         <resultMap id="detailMap" class="userBasicInfo"> 
        <result property="userId" column="USER_ID" jdbcType="Long"/> 
        <result property="userName" column="USER_NAME" jdbcType="String" nullValue=""/> 
        <result property="realName" column="REAL_NAME" jdbcType="String" nullValue=""/> 
        <result property="password" column="PASSWORD" jdbcType="String" nullValue=""/> 
        <result property="email" column="EMAIL" jdbcType="String" nullValue=""/> 
        <result property="userType" column="USER_TYPE" jdbcType="String" nullValue=""/> 
        <result property="userState" column="USER_STATE" jdbcType="String" nullValue=""/> 
        <result property="loginCount" column="LOGIN_COUNT" jdbcType="java.lang.Integer" nullValue="0"/> 
        <result property="question" column="QUESTION" jdbcType="String" nullValue=""/> 
        <result property="answer" column="ANSWER" jdbcType="String" nullValue=""/> 
        <result property="account" column="ACCOUNT" jdbcType="java.lang.Integer" nullValue="0"/> 
        <result property="branchId" column="BRANCH_ID" jdbcType="String" nullValue=""/> 
        <result property="createDate" column="CREATE_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/> 
        <result property="modifyDate" column="MODIFY_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/> 
        <result property="lastVisit" column="LAST_VISIT" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/> 
        <result property="userLevel" column="USER_LEVEL" jdbcType="String" nullValue=""/> 
        <result property="experienceTime" column="EXPERIENCE_TIME" jdbcType="java.lang.Integer" nullValue="0"/> 
        <result property="custId" column="CUSTID" jdbcType="Long" nullValue="0"/> 
        <result property="sex" column="SEX" jdbcType="String" nullValue=""/> 
        <result property="birthday" column="BIRTHDAY" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/> 
        <result property="race" column="RACE" jdbcType="String" nullValue=""/> 
        <result property="province" column="PROVINCE" jdbcType="String" nullValue=""/> 
        <result property="city" column="CITY" jdbcType="String" nullValue=""/> 
        <result property="cardType" column="CARD_TYPE" jdbcType="String" nullValue=""/> 
        <result property="cardId" column="CARD_ID" jdbcType="String" nullValue=""/> 
        <result property="telephone" column="TELEPHONE" jdbcType="String" nullValue=""/> 
        <result property="mobilePhone" column="MOBILEPHONE" jdbcType="String" nullValue=""/> 
        <result property="fax" column="FAX" jdbcType="String" nullValue=""/> 
        <result property="address" column="ADDRESS" jdbcType="String" nullValue=""/> 
        <result property="postCode" column="POST_CODE" jdbcType="String" nullValue=""/> 
        <result property="degree" column="DEGREE" jdbcType="String" nullValue=""/> 
        <result property="vocation" column="VOCATION" jdbcType="String" nullValue=""/> 
        <result property="source" column="SOURCE" jdbcType="String" nullValue=""/> 
        <result property="typeName" column="KEY_NAME" jdbcType="String" nullValue=""/> 
     </resultMap> 
    <parameterMap id="procQueryMap" class="java.util.HashMap" >    
        <parameter property="P_USER_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/> 
        <parameter property="P_REAL_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/> 
        <parameter property="P_USER_TYPE" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/> 
        <parameter property="P_BRANCH_ID" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/> 
        <parameter property="P_BEGIN_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/> 
        <parameter property="P_END_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/> 
        <parameter property="P_BEGIN" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/> 
        <parameter property="P_END" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/> 
        <parameter property="P_RESULT" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/> 
        <parameter property="P_CURSOR" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/> 
    </parameterMap> 
    <procedure id="queryUserBasicInfoProc" parameterMap="procQueryMap" resultMap="detailMap">    
        {call QUERY_USER_BASIC_INFO (?,?,?,?,?,?,?,?,?,?)}    
    </procedure>     
</sqlMap> 

view plaincopy to clipboardprint?
/**
* <p>this method is used by people get user list.
* @param map
* @return List
* @throws DataAccessException
*/ 
@SuppressWarnings("unchecked") 
public List<UserBasicInfo> getList(Map<String, Object> map)  
        throws DataAccessException { 
    return this.getSqlMapClientTemplate().queryForList(statementName +  ".queryUserBasicInfoProc", map); 
}