(原创)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());
    }

 

 

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