ibatis调用存储过程(Oracle)示例

 

2010-03-01 15:42
1.运行oracle脚本：
example_pkg.sql：
CREATE OR REPLACE PACKAGE example
AS
   TYPE t_ref_cur IS REF CURSOR;
   PROCEDURE GetSingleEmpRS (p_deptno IN emp.deptno%TYPE, 
           p_recordset1 OUT t_ref_cur);    
   PROCEDURE GetDoubleEmpRS (p_deptno IN emp.deptno%TYPE,
                    p_recordset1 OUT t_ref_cur,
                    p_recordset2 OUT t_ref_cur);
end example;
/

CREATE OR REPLACE PACKAGE BODY example
AS
   PROCEDURE GetSingleEmpRS (p_deptno     IN emp.deptno%TYPE,
                             p_recordset1 OUT t_ref_cur) AS
   BEGIN
       OPEN p_recordset1 FOR
           SELECT ename,
                  empno,
                  deptno
             FROM emp
            WHERE deptno = p_deptno
            ORDER BY ename;
   END GetSingleEmpRS;   

   PROCEDURE GetDoubleEmpRS (p_deptno     IN emp.deptno%TYPE,
                             p_recordset1 OUT t_ref_cur,
                             p_recordset2 OUT t_ref_cur) AS
   BEGIN
       OPEN p_recordset1 FOR
           SELECT ename,
                  empno,
                  deptno
             FROM emp
            WHERE deptno = p_deptno
            ORDER BY ename;
            
       OPEN p_recordset2 FOR
           SELECT ename,
                  empno,
                  deptno
             FROM emp
            WHERE deptno > p_deptno
            ORDER BY ename;
            
   END GetDoubleEmpRS;   
end example;
/

2.实体类：Employee.java
package com.ibatis.common.test;

public class Employee {
String name;
long employeeNumber;
long departmentNumber;

public long getDepartmentNumber() {
   return departmentNumber;
}

public void setDepartmentNumber(long departmentNumber) {
   this.departmentNumber = departmentNumber;
}

public long getEmployeeNumber() {
   return employeeNumber;
}

public void setEmployeeNumber(long employeeNumber) {
   this.employeeNumber = employeeNumber;
}

public String getName() {
   return name;
}

public void setName(String name) {
   this.name = name;
}

public String toString() {
   return "Employee[name=" + name + ",id=" + employeeNumber + ",dept=" + departmentNumber + "]";
}
}

3.配置SqlMapConfig.xml：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
    PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<settings cacheModelsEnabled="false" enhancementEnabled="true" lazyLoadingEnabled="false" maxRequests="32" maxSessions="10" maxTransactions="5" useStatementNamespaces="false" />

<transactionManager type="JDBC">
   <dataSource type="SIMPLE">
    <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver" />
    <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:ORCL" />
    <property name="JDBC.Username" value="scott" />
    <property name="JDBC.Password" value="tiger" />
   </dataSource>
</transactionManager>
<sqlMap resource="com/ibatis/common/test/SqlMap.xml" />
</sqlMapConfig>

4.配置SQLMap.xml：
<?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">
<sqlMap>

<typeAlias alias="Employee" type="com.ibatis.common.test.Employee" />

<resultMap id="employee-map" class="Employee">
   <result property="name" column="ENAME" />
   <result property="employeeNumber" column="EMPNO" />
   <result property="departmentNumber" column="DEPTNO" />
</resultMap>

<parameterMap id="single-rs" class="map">
   <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN" />
   <parameter property="output1" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" />
</parameterMap>

<procedure id="GetSingleEmpRs" parameterMap="single-rs" resultMap="employee-map">
   <![CDATA[
    { call scott.example.GetSingleEmpRS(?, ?) }
   ]]>
</procedure>

<parameterMap id="double-rs" class="map">
   <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN" />
   <parameter property="output1" jdbcType="ORACLECURSOR" javaType="cursor" mode="OUT" resultMap="employee-map" />
   <parameter property="output2" jdbcType="ORACLECURSOR" javaType="cursor" mode="OUT" resultMap="employee-map" />
</parameterMap>

<procedure id="GetDoubleEmpRs" parameterMap="double-rs">
   <![CDATA[
    { call scott.example.GetDoubleEmpRS(?, ?, ?) }
   ]]>
</procedure>
</sqlMap>

5.测试Main方法：
package com.ibatis.common.test;

import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class Main {

public static void main(String arg[]) throws Exception {

   String resource = "com/ibatis/common/test/SqlMapConfig.xml";
   Reader reader = Resources.getResourceAsReader(resource);
   SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

   Map map = new HashMap();
   map.put("in1", new Integer(10));
   List list = sqlMap.queryForList("GetSingleEmpRs", map);

   System.out.println(list);
   System.out.println("--------------------");

   map = new HashMap();
   map.put("in1", new Integer(10));
   sqlMap.queryForObject("GetDoubleEmpRs", map);

   System.out.println(map.get("output1"));
   System.out.println(map.get("output2"));
   System.out.println("--------------------");

}
}

6.OK，运行正常！