ibatis与oracle的存储过程的调用_JAVA_编程开发_程序员俱乐部

前提：已经搭建好了spring+struts+ibatis 框架，并且已经配置好了数据源

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.配置SQLMap.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Employee">
	<typeAlias alias="Employee" type="org.test.pro.domain.Employee" />
	<resultMap id="employee-map" class="Employee">
	   <result property="name" column="ENAME" />
	   <result property="employeeNumber" column="EMPNO" />
	   <result property="departmentNumber" column="DEPTNO" />
	</resultMap>

	<parameterMap class="map" id="single-rs">
		<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 example.GetSingleEmpRS(?,?)}
		]]>
	</procedure>
	<parameterMap class="map" id="double-rs">
		<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" resultMap="employee-map">
		<![CDATA[
			{ call example.GetDoubleEmpRS(?,?,?)}
		]]>
	</procedure>
</sqlMap>

4.EmployeeDAOImpl类：

package org.test.pro.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import org.test.pro.dao.EmployeeDAO;


public class EmployeeDAOImpl extends SqlMapClientDaoSupport implements EmployeeDAO{

	public List testEmployee(Map<String, Object> map) {
		// TODO Auto-generated method stub
		return getSqlMapClientTemplate()
		.queryForList("Employee.GetSingleEmpRs", map);
	}

	public Object testDoubleEmployee(Map<String, Object> map) {
		// TODO Auto-generated method stub
		return getSqlMapClientTemplate().queryForObject("Employee.GetDoubleEmpRs",map);
	}
}

5.action测试方法（方法部分）：

public String testEmployee(){
		Map map = new HashMap();
		map.put("in1", new Integer(10));
		List list = userService.testEmployee(map);
		System.out.println(list);
		System.out.println("--------------------");
		 map = new HashMap();
		 map.put("in1", new Integer(10));
		 userService.testDoubleEmployee(map);

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

6.OK，运行正常！