ibatis调用oracle函数示例

 

利用空余时间写的一个简单例子,数据库是oracle10g.

 

配置文件:

 OracleSqlMapConfig.xml

 <?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

	<!-- Configure a built-in transaction manager.  If you're using an 
		app server, you probably want to use its transaction manager 
		and a managed datasource -->
	<transactionManager type="JDBC" commitRequired="false">
		<dataSource type="SIMPLE">
			<property name="JDBC.Driver"
				value="oracle.jdbc.driver.OracleDriver" />
			<property name="JDBC.ConnectionURL"
				value="jdbc:oracle:thin:@localhost:1521:ORCL" />
			<property name="JDBC.Username" value="scott" />
			<property name="JDBC.Password" value="tiger" />
			<property name="Pool.MaximumActiveConnections" value="10" />
			<property name="Pool.MaximumIdleConnections" value="5" />
			<property name="Pool.MaximumCheckoutTime" value="120000" />
			<property name="Pool.TimeToWait" value="500" />
			<property name="Pool.PingEnabled" value="false" />
			<property name="Pool.PingConnectionsOlderThan" value="1" />
			<property name="Pool.PingConnectionsNotUsedFor" value="1" />
		</dataSource>
	</transactionManager>

	<sqlMap resource="com/Emp.xml" />


</sqlMapConfig>

 

  Emp.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="Emp">

	<!-- 取别名 -->
	<typeAlias alias="emp" type="com.Emp" />

	<resultMap id="emp-map" class="emp">
		<result property="ename" column="ENAME" />
	</resultMap>

	<parameterMap id="test" class="java.util.HashMap">
		<parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="emp-map" />
		<parameter property="param1" jdbcType="VARCHAR"	javaType="java.lang.String" mode="IN" />
	</parameterMap>

	<procedure id="testCur" parameterMap="test">
		{? = call selectemp(?)} 
	</procedure>
			
</sqlMap>

  数据库脚本:

create or replace function scott.selectemp(
		param1 in varchar2 		
)return sys_refcursor
is
outcursor sys_refcursor;
		begin 
		      open outcursor for select ename from scott.emp where emp.ename like param1 ;
		      return outcursor;
		end;

 pojo类:

package com;

public class Emp {

	private int empno;

	private String ename;

	private String job;

	private float sal;

	private float comm;

	public float getComm() {
		return comm;
	}

	public void setComm(float comm) {
		this.comm = comm;
	}

	public int getEmpno() {
		return empno;
	}

	public void setEmpno(int empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public float getSal() {
		return sal;
	}

	public void setSal(float sal) {
		this.sal = sal;
	}

}

 

 

 测试代码: 

Reader reader = Resources.getResourceAsReader("com/OracleSqlMapConfig.xml");
SqlMapClient client = SqlMapClientBuilder.buildSqlMapClient(reader);
reader.close();
Map<String, Object> p = new HashMap<String, Object>();
p.put("param1", "%S%");
client.queryForObject("testCur", p);
List list = (List) p.get("result");
for (int i = 0; i < list.size(); i++) {
 Emp emp = (Emp) list.get(i);
 System.out.println(emp.getEname());
}