(原创)Ibatis2调用数据库存储过程的相关示例

第一种方式，传入一个参数(非自定义java类型)，返回单个值：

 

oracle代码：

   

CREATE OR REPLACE PROCEDURE testPro(bidObjectId  NUMBER,projectId OUT NUMBER)
AS
BEGIN
       SELECT bo.project_id INTO projectId FROM bm_t_bid_object bo WHERE bo.bid_object_id = bidObjectId;
  END;

  

Ibatis配置：

     <parameterMap id="projectIdProc" class="java.util.Map">
        <parameter property="bidObjectId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
        <parameter property="result" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
    </parameterMap>

 

<procedure id="getProjectIdProc" parameterMap="projectIdProc">
        { call testPro(?,?) }
</procedure>

 

Java调用代码：

     public Long getProjectIdProc(Map map) throws Exception {
        this.getBaseDao().getSqlMapClientTemplate().queryForObject("getProjectIdProc",map);
        Long pId = Long.valueOf(map.get("result").toString());       
        return pId;
    }

 

Junit测试代码：基于org.springframework.test.AbstractDependencyInjectionSpringContextTests测试

      public void testGetProjectIdProc() {
        HashMap parameters = new HashMap();
        parameters.put("bidObjectId","900000000000000003");
        Object o = null;
        try {
            o = projectMgr.getProjectIdProc(parameters);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(o);
    }

 

第二种方式： 传入单个参数，返回游标结果集

 

oracle代码：

 

CREATE OR REPLACE PACKAGE myPage
AS
  TYPE myrctype IS REF CURSOR;
  PROCEDURE testListPro(projectId NUMBER,return_cursor OUT myrctype);
END;

 

CREATE OR REPLACE PACKAGE BODY myPage

AS
  PROCEDURE testListPro(projectId NUMBER,return_cursor OUT myrctype)
    IS
    sqlStr VARCHAR(200);
    BEGIN
      sqlStr := 'SELECT * FROM bm_t_bid_object bo WHERE bo.project_id =:p_id';
      OPEN return_cursor FOR sqlStr USING projectId;
    END testListPro;
END myPage;

 

Ibatis配置：

 

<resultMap id="bidObjectBasic" class="bidObject">
        <result property="id" column="BID_OBJECT_ID"/>
        <result property="objectNo" column="OBJECT_NO"/>
        <result property="objectName" column="OBJECT_NAME"/>
    </resultMap>

 

    <parameterMap id="bidObjectsProc" class="java.util.Map">
        <parameter property="projectId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
        <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="bidObjectBasic"/>
    </parameterMap>

 

    <procedure id="getBidObjectsProc" parameterMap="bidObjectsProc">
        { call MYPAGE.testListPro(?,?) }
    </procedure>

 

Java调用代码：

 

public List getBidObjectsProc(Map map) throws Exception {
        this.getBaseDao().getSqlMapClientTemplate().queryForList("getBidObjectsProc",map);
        List list = (ArrayList) map.get("result");
        return list;
    }

 

Junit测试代码:

public void testGetBidObjectsProc() {
        HashMap parameters = new HashMap();
        parameters.put("projectId","120000000000000004");
        List list = null;
        try {
            list = projectMgr.getBidObjectsProc(parameters);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("list.size() = " + list.size());
    }

 

 

第三种方式，传入一个List<Project> projects参数，返回游标

 

oracle代码：

 

CREATE OR REPLACE TYPE OPROJECT IS OBJECT
(
       pid NUMBER(18),
       pname NVARCHAR2(200),
       pno NVARCHAR2(100)
)

 

CREATE OR REPLACE TYPE MY_OPROJECT_TYPE AS TABLE OF OPROJECT

 

CREATE OR REPLACE PACKAGE mypageforList
AS
  TYPE myrctype IS REF CURSOR;
  PROCEDURE GETBIDOBJECTSBYPROJECTS(MY_OPROJECT IN MY_OPROJECT_TYPE,return_cursor OUT myrctype);
END mypageforList;

 

CREATE OR REPLACE PACKAGE BODY MYPAGEFORLIST AS
  PROCEDURE GETBIDOBJECTSBYPROJECTS(MY_OPROJECT   IN MY_OPROJECT_TYPE,
                                    RETURN_CURSOR OUT MYRCTYPE) IS
    PROJECTID_STRS VARCHAR2(200) := '';
    sqlstr VARCHAR2(200);
  BEGIN   
    FOR I IN 1 .. MY_OPROJECT.COUNT() LOOP
      PROJECTID_STRS := CONCAT(PROJECTID_STRS,CONCAT(to_char(MY_OPROJECT(I).PID),','));      
    END LOOP; 
        PROJECTID_STRS := SUBSTR(PROJECTID_STRS,1,(LENGTH(PROJECTID_STRS)-1));
     sqlstr := 'SELECT * FROM BM_T_BID_OBJECT BO
       WHERE BO.PROJECT_ID IN ('||PROJECTID_STRS||')';
    --OPEN return_cursor FOR SELECT * FROM bm_t_bid_object bo WHERE bo.project_id = MY_OPROJECT(2).pid;
    OPEN RETURN_CURSOR FOR sqlstr;
  END GETBIDOBJECTSBYPROJECTS;
END MYPAGEFORLIST;

 

Ibatis配置：

 

<parameterMap id="bidObjectsByProjectListProc" class="java.util.Map">
        <parameter property="projects" jdbcType="ARRAY" javaType="java.util.List" mode="IN" typeHandler="ProjectListHandler"/>
        <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="bidObjectBasic"/>
    </parameterMap>

    <procedure id="getBidObjectsByProjectListProc" parameterMap="bidObjectsByProjectListProc">
        { call mypageforList.GETBIDOBJECTSBYPROJECTS(?,?) }
    </procedure>

 

java代码：

 

public class ProjectListHandler implements TypeHandlerCallback {
    @Override
    public void setParameter(ParameterSetter parameterSetter, Object o) throws SQLException {
        Connection conn = null;
        try {
            if (null != o) {
                List<Project> list = (ArrayList<Project>) o;
                System.out.println("传入的java参数list长度为： = " + list.size());
                Class c = Class.forName("oracle.jdbc.OracleDriver");
                conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "powerbid_test", "powerbid_test");

                System.out.println("conn = " + conn);
                ARRAY array = getArray(conn, "OPROJECT", "MY_OPROJECT_TYPE", list);
                parameterSetter.setArray(array);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != conn) {
                conn.close();
            }
        }
    }

    @Override
    public Object getResult(ResultGetter resultGetter) throws SQLException {
       //todo 如果返回的数据库类型要转换成某种特定的java类型，实现这个方法

        return null;
    }

    @Override
    public Object valueOf(String s) {
        return null;
    }

    private ARRAY getArray(Connection con, String OracleObj,
                           String Oraclelist, List listData) throws Exception {
        ARRAY array = null;

        if (listData != null && listData.size() > 0) {
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, con);

            StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
            STRUCT[] structs = new STRUCT[listData.size()];

            for (int i = 0; i < listData.size(); i++) {
                Object[] result = {((Project) listData.get(i)).getId().toString(),
                        ((Project) listData.get(i)).getProjectName(),
                        ((Project) listData.get(i)).getProjectNo()};

                structs[i] = new STRUCT(structdesc, con, result);
            }

            array = new ARRAY(desc, con, structs);
        }
        return array;
    }
}

 

public List getBidObjectsByProjectListProc(Map map) throws Exception {
        this.getBaseDao().getSqlMapClientTemplate().queryForList("getBidObjectsByProjectListProc",map);
        List list = (ArrayList) map.get("result");
        return list;

}

 

 

Junit测试代码：

 

public void testGetBidObjectsByProjectListProc() {
        List<Project> listinput = new ArrayList<Project>();
        Project p1 = new Project(900000000000000007l);
        Project p2 = new Project(900000000000000001l);
        Project p3 = new Project(900000000000000005l);
        listinput.add(p1);
        listinput.add(p2);
        listinput.add(p3);
        HashMap parameters = new HashMap();
        parameters.put("projects", listinput);
        List list = null;
        try {
            list = projectMgr.getBidObjectsByProjectListProc(parameters);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("list.size() = " + list.size());
    }

 

 

今天项目中遇到才去研究，现贴如此，希望对有同样需求的同行有所帮助。

评论

4 楼 wuchunjiesp 2013-09-17  

总算解决了，THANK楼主，空值问题是TYPE里面参数类型定义问题，必须是NVARCHAR2才能解析出String类型的参数，哦也！很重要的

3 楼 wuchunjiesp 2013-09-16  

存储里面是这样的

FOR I IN 1..P_GOODSDIM_INSERT_LIST.COUNT() LOOP
               INSERT INTO AP_AWBGOODSDIM_T(
                     CAD_DIMID,
                     CAD_AWBID,
                     CAD_GOODSLENGTH,
                     CAD_GOODSWIDTH,
                     CAD_GOODSHEIGHT,
                     CAD_GOODSNUM
              )VALUES(
                     SYS_GUID(),
                     V_AWBID,
                     10,
                     10,
                     10,
                     TO_NUMBER(P_GOODSDIM_INSERT_LIST(I).cadDimId||10)
              );
              /*INSERT INTO AP_AWBGOODSDIM_T(
                     CAD_DIMID,
                     CAD_AWBID,
                     CAD_GOODSLENGTH,
                     CAD_GOODSWIDTH,
                     CAD_GOODSHEIGHT,
                     CAD_GOODSNUM
              )VALUES(
                     SYS_GUID(),
                     V_AWBID,
                     TO_NUMBER(P_GOODSDIM_INSERT_LIST(I).cadGoodsLength),
                     TO_NUMBER(P_GOODSDIM_INSERT_LIST(I).cadGoodsWidth),
                     TO_NUMBER(P_GOODSDIM_INSERT_LIST(I).cadGoodsHeight),
                     TO_NUMBER(P_GOODSDIM_INSERT_LIST(I).cadGoodsNum)
              );*/
              --dbms_output.put_line(P_GOODSDIM_INSERT_LIST(I).CAD_GOODSLENGTH);
          END LOOP;

2 楼 wuchunjiesp 2013-09-16  

请教一下：为什么我和你一样配置，到存储里面获取不到这个类型的值呢？值全是空的。。。跟版本有关系吗？
public void setParameter(ParameterSetter parametersetter, Object obj)
throws SQLException {
Connection conn = null;
try {
if (null != obj) {
List<ParamInVO> list = (ArrayList<ParamInVO>) obj;
Class c = Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.4.129.36)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.4.129.38)(PORT=1521))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))",
"apt_user", "apt_user");

ARRAY array = getArray(conn, "GOODSDIM_INFO_TYPE",
"GOODSDIM_INFO_TYPE_TABLE", list);
System.out.println(array);
parametersetter.setArray(array);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != conn) {
conn.close();
}
}

}
----------//ARRAY
private ARRAY getArray(Connection con, String OracleObj, String Oraclelist,
List listData) throws Exception {
ARRAY array = null;

if (listData != null && listData.size() > 0) {
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,con);

StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
STRUCT[] structs = new STRUCT[listData.size()];

for (int i = 0; i < listData.size(); i++) {
Object[] result = {
((ParamInVO) listData.get(i)).getCadDimId(),
((ParamInVO) listData.get(i)).getCadAwbId(),
((ParamInVO) listData.get(i)).getCadGoodsLength(),
((ParamInVO) listData.get(i)).getCadGoodsWidth(),
((ParamInVO) listData.get(i)).getCadGoodsHeight(),
((ParamInVO) listData.get(i)).getCadGoodsNum()};

structs[i] = new STRUCT(structdesc, con, result);
}

array = new ARRAY(desc, con, structs);
}
return array;
}
------//java里面赋值
List<ParamInVO> listinput = new ArrayList<ParamInVO>();
//包装信息
List<Map<String,String>> listInsert = (List<Map<String,String>>) paramMap.get("listInsert");
for(Map<String,String> packageDtl : listInsert){
String cadGoodsnum = packageDtl.get("cadGoodsnum");
String cadGoodslength = packageDtl.get("cadGoodslength");
String cadGoodswidth = packageDtl.get("cadGoodswidth");
String cadGoodsheight = packageDtl.get("cadGoodsheight");
if((!"".equals(cadGoodsnum)) && (!"".equals(cadGoodslength)) && (!"".equals(cadGoodswidth)) && (!"".equals(cadGoodsheight))){
ParamInVO paramInVO = new ParamInVO();
//interLeaveManageDAO.doInsertInterPackage(packageDtl);
paramInVO.setCadDimId("1");
paramInVO.setCadAwbId("1");
paramInVO.setCadGoodsNum(cadGoodsnum);
paramInVO.setCadGoodsLength(cadGoodslength);
paramInVO.setCadGoodsWidth(cadGoodswidth);
paramInVO.setCadGoodsHeight(cadGoodsheight);
listinput.add(paramInVO);
}
}
paramMap.put("insertGoodsDim", listinput);
-----//
<parameter property="insertGoodsDim" jdbcType="ARRAY" javaType="java.util.List" typeHandler="com.neusoft.apt.common.filter.ArrayTypeHandlerIn" mode="IN" />

1 楼 sbpya 2010-03-11  

以上第三种方式中，ProjectListHandler配置给spring托管，在ibatis配置文件
直接引用spring配置的bean的id好像会报错，请各位指教

<bean id="projectListHandler" class="com.freshpower.bid.bm.plugin.ProjectListHandler" scope="singleton">
        <property name="basePDao">
            <ref bean="baseDao"/>
        </property>
    </bean>

<parameterMap id="bidObjectsByProjectListProc" class="java.util.Map">
        <parameter property="projects" jdbcType="ARRAY" javaType="java.util.List" mode="IN" typeHandler="projectListHandler"/>
        <parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="bidObjectBasic"/>
    </parameterMap>

这样配置，红色部分报错，无法引用spring配置的bean,请问这里要如何修正呢？
这样做的主要目标只是想，在ProjectListHandler类中注入baseDao，通过basePDao.getConnection()可以直接拿到spring托管的连接，不用再Class.forName了

不要告诉我在ProjectListHandler可调用cxf.getBean("baseDao"),我个人分析，是不是projectListHandler还需要实现spring中某个接口才能作为一个spring的typeHander呢,类似调用org.springframework.orm.ibatis.support.ClobStringTypeHandler一样，请指教。