注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

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

2011-12-15 18:59:37|  分类: ibatis |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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" <result property="name" column="ENAME" />
   <result property="employeeNumber" column="EMPNO" />
   <result property="departmentNumber" column="DEPTNO" />
</resultMap>

<parameterMap 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 scott.example.GetSingleEmpRS(?, ?) }
   ]]>
</procedure>

<parameterMap 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">
   <![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,运行正常!

  评论这张
 
阅读(546)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016