ibatis调用oracle函数示例

最新推荐文章于 2021-04-16 09:13:03 发布

renjunfu

最新推荐文章于 2021-04-16 09:13:03 发布

阅读量247

点赞数

CC 4.0 BY-SA版权

分类专栏： ibatis 文章标签： iBATIS Oracle JDBC SQL SQL Server

本文链接：https://blog.youkuaiyun.com/renjunfu/article/details/83436394

ibatis 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个使用IBatis框架连接Oracle数据库并执行存储过程返回游标的Java示例。该示例展示了如何配置数据库连接、定义SQL映射文件及对应的POJO类，并通过测试代码查询员工数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

利用空余时间写的一个简单例子,数据库是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());
}