ibatisとoracleのストレージ・プロシージャの呼び出し
前提:spring+struts+ibatisフレームワークが構築され、データソースが構成されています.
1.oracleスクリプトを実行するには:
example_pkg.sql:
ストレージプロセスの詳細な説明については、ネット上にはすでに多くの資料がありますが、ここではあまり言いません.
2.エンティティークラス:Employee.java
3.SQLMapを構成する.xml:
4.EmployeeDAOImplクラス:
5.action試験方法(方法部分):
6.OK、正常に動作!
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、正常に動作!