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

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


已标记关键词 清除标记
<p style="" align="left"><span lang="EN-US" style=""><span style="color: #000000;">CREATE OR REPLACE TYPE EMP_SALARY_REC AS OBJECT (<br> EMP_ID NUMBER(5),<br> EMP_NAME VARCHAR2(255),<br> START_DATE DATE,<br> SALARY NUMBER <br>);<br>/<br><br>CREATE OR REPLACE Type EMP_SALARY_TAB AS TABLE OF EMP_SALARY_REC;<br>/<br><br>next we will create a small package with one single test procedure<br><br>CREATE OR REPLACE PACKAGE EMP_SALARY_PKG IS<br> PROCEDURE GET_EMP_SALARIES(i_array IN EMP_SALARY_TAB,o_array OUT<br>EMP_SALARY_TAB);<br>End EMP_SALARY_PKG;<br>/<br></span><br>用ibatis就不能配置调用吗?</span></p><br/><strong>问题补充</strong><br/><div class="quote_title">niwenbin 写道</div><div class="quote_div">SQL Map通过<procedure>元素支持存储过程。下面的例子说明如何使用具有输出参数的存储过程。 <br /><parameterMap id="swapParameters" class="map" > <br /><parameter property="email1" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/> <br /><parameter property="email2" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/> <br /></parameterMap> <br /><procedure id="swapEmailAddresses" parameterMap="swapParameters" > <br />{call swap_email_address (?, ?)} <br /></procedure> <br />调用上面的存储过程将同时互换两个字段(数据库表)和参数对象(Map)中的两个email <br />地址。如果参数的mode属性设为INOUT或OUT,则参数对象的值被修改。否则保持不变。</div> <br /> <br /> <br />参数是自定义的type类型(EMP_SALARY_TAB): <br />CREATE OR REPLACE TYPE EMP_SALARY_REC AS OBJECT ( <br />EMP_ID NUMBER(5), <br />EMP_NAME VARCHAR2(255), <br />START_DATE DATE, <br />SALARY NUMBER <br />); <br />CREATE OR REPLACE Type EMP_SALARY_TAB AS TABLE OF EMP_SALARY_REC; <br /><br/><strong>问题补充</strong><br/><div class="quote_title">threejin520 写道</div><div class="quote_div">Ibatis访问存储过程的配置 zz  2009-10-30 16:35:48|  分类: Java |  标签: |字号大 <br />中 <br />小 订阅 <br /> <br />最近项目里经常需要配置用Ibatis访问存储过程(或函数),多次摸索,发现配置的难点是在于如何配置输入输出参数。现以访问Oracle存储过程(或函数)举例说明。 <br /> <br />一般基本的配置要点如下: <br /> <br />(1)、访问存储过程(或函数)一般以procedure标签来定义statement。 <br /> <br />(2)、输入输出的参数需要用parameterMap标签单独定义。 <br /> <br />(3)、区分输入输出参数用parameter标签的mode属性来定义,分别为”OUT”和”IN”。 <br /> <br />按照是否有返回值,是否使用游标分类说明如下: <br /> <br />第一类,没有返回结果,有输出参数以out标识的过程访问配置样式: <br /> <br /><parameterMap id=”functionPramsMap” class=”map”> <br /> <br /><parameter property=”p1″ jdbcType=”VARCHAR” javaType=”string” mode=”IN”/> <br /> <br /><parameter property=”p2″ jdbcType=”INTEGER” javaType=”int” mode=”IN”/> <br /> <br /><parameter property=”p3″ jdbcType=”INTEGER” javaType=”int” mode=”OUT” /> <br /> <br /></parameterMap> <br /> <br /><procedure id=”statementId” parameterMap=”functionPramsMap”> <br /> <br />{call packageName.functionName(?,?,?)} <br /> <br /></procedure> <br /> <br />第二类,有返回结果,有输出参数以out标识的过程访问配置样式: <br /> <br /><parameterMap id=”functionParamsMap” class=”map”> <br /> <br /><parameter property=”p0″ jdbcType=” INTEGER” javaType=”string” mode=”OUT”/> <br /> <br /><parameter property=”p1″ jdbcType=”VARCHAR” javaType=”string” mode=”IN”/> <br /> <br /><parameter property=”p2″ jdbcType=”INTEGER” javaType=”int” mode=”IN”/> <br /> <br /><parameter property=”p3″ jdbcType=”INTEGER” javaType=”int” mode=”OUT” /> <br /> <br /></parameterMap> <br /> <br /><procedure id=”statementId” parameterMap=” functionParamsMap”> <br /> <br />{?=call packageName.functionName(?,?,?)} <br /> <br /></procedure> <br /> <br />这里参数p0标识调用过程的返回结果。 <br /> <br />第三类,有使用游标的的过程访问配置样式: <br /> <br /><resultMap id=”resultMap” class=”package.ClassA”> <br /> <br /><result property=”property0″ column=”field0″ /> <br /> <br /><result property=”property1″ column=”field2″ /> <br /> <br /><result property=”property2″ column=”field3″ /> <br /> <br /></resultMap> <br /> <br /><parameterMap id=”functionParamsMap” class=”map”> <br /> <br /><parameter property=”p0″ jdbcType=” INTEGER” javaType=”string” mode=”OUT”/> <br /> <br /><parameter property=”p1″ jdbcType=”VARCHAR” javaType=”string” mode=”IN”/> <br /> <br /><parameter property=”p2″ jdbcType=”INTEGER” javaType=”int” mode=”IN”/> <br /> <br /><parameter property=”p3″ jdbcType=”ORACLECURSOR” <br /> <br />javaType=”java.sql.ResultSet” mode=”OUT” resultMap=”resultMap” /> <br /> <br /></parameterMap> <br /> <br /><procedure id=”statementId” parameterMap=”functionPramsMap”> <br /> <br />{?=call packageName.functionName(?,?,?)} <br /> <br /></procedure> <br /> <br />这里,关键注意的是p3参数,由于使用的是oracle数据库驱动,所以用jdbcType=”ORACLECURSOR”表示oracle游标,同时由于是游标,所以javaType=”java.sql.ResultSet”。最后必须要给参数p3配置好resultMap属性,以说明游标返回的结果集映射方式,不配置此属性,在代码里来访问结果集是不行的。此时,在Java代码里调用的样式如下: <br /> <br />HashMap<String, Object> params = new HashMap<String, Object>(); <br /> <br />params.put(”p1″, p1); <br /> <br />params.put(”p2″, p2); <br /> <br />sqlMapClient.queryForObject(”statementId”, params); <br /> <br />List<ClassA> list = (List<ClassA>) params.get(”p3″); <br /> <br />if (list != null && list.size() > 0) { <br /> <br />return userList.get(0); <br /> <br />} <br /> <br />return null; <br /> <br />第四类,有复杂的参数类型的存储过程: <br /> <br />(1)、对于BLOB或者CLOB,在POJO里的属性都定义为byte[]类型,iBatis配置里跟普通类型参数一样处理; <br /> <br />(2)、实现TypeHandler来自定义类型处理器用户处理复杂的字段; <br /> <br />(3)、对于数据库自定义的数据类型,首先要定义实现了java.sql.SQLData的类Class TypeA。实现getSQLTypeName方法,返回值为数据库的自定义类型名。配置文件里parameter标签的属性如:jdbcType=”TypeB” JavaType=”TypeA”,同时还要写TypeHandlerCallback用于处理jdbcType=”TypeB”的类型。 <br /></div> <br />我现在需要第四类的例子,如何构建ARRAY
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页