ibatis2.x调用oracle存储过程

干嘉悦

一.JAVA对象

public class User implements Serializable {
private static final long serialVersionUID = -6919964218508186044L;  
private int id;  
private String name;  
private Date birthday;
public int getId() {  
return id;  
}  
public void setId(int id) {  
this.id = id;  
}  
public String getName() {  
return name;  
}  
public void setName(String name) {  
this.name = name;  
}  
public Date getBirthday() {  
return birthday;  
}  
public void setBirthday(Date birthday) {  
this.birthday = birthday;  
}  
}

  

二.JAVA对象对应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="testProcedure">
<parameterMap class="java.util.Map" id="procedureMap">
<!-- 设置存储过程输出参数 -->
<parameter property="curr" jdbcType="ORACLECURSOR"  javaType="java.sql.ResultSet" mode="OUT" />
</parameterMap>
<!-- resultClass设置游标返回数据类型 -->
<procedure id="getAllUser" resultClass="com.bijian.User" parameterMap="procedureMap">
{call getAllUser(?)}  
</procedure>
<!-- 表结构  
create table IBATIS_USER  
(  
ID       NUMBER not null,  
NAME     VARCHAR2(20) not null,  
birthday DATE not null  
)
-->
<!-- 存储过程  
create or replace procedure getAllUser(curr_users out sys_refcursor)  
as
begin   
open curr_users for select * from ibatis_user;  
end;
-->
</sqlMap>
 

三.ibatis总配置

<?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>
<properties resource="jdbc.properties"/>
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
maxRequests="32"
maxSessions="10"
maxTransactions="5"
useStatementNamespaces="false"/>
<transactionManager type="JDBC" commitRequired="false">
<dataSource type="DBCP">
<property name="JDBC.Driver" value="${jdbc.driverClassName}"/>
<property name="JDBC.ConnectionURL" value="${jdbc.url}"/>
<property name="JDBC.Username" value="${jdbc.username}"/>
<property name="JDBC.Password" value="${jdbc.password}"/>
<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.PingQuery" value="${jdbc.testsql}"/>
<property name="Pool.PingEnabled" value="false"/>
<property name="Pool.PingConnectionsOlderThan" value="1"/>
<property name="Pool.PingConnectionsNotUsedFor" value="1"/>
<property name="validationQuery" value="${jdbc.testsql}"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/bijian/User.xml"/>
</sqlMapConfig>

  

　总配置对应的属性文件jdbc.properties

#jdbc属性配置信息
#ORACLE
jdbc.url=jdbc:oracle:thin:@192.168.1.100:1521:orcl
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.username=haiya
jdbc.password=kingdee
jdbc.testsql=select 1 from dual

  

四.调用主类

public class Test {
private static SqlMapClient sqlMapper;
static {
Reader reader = null;
try {
reader = Resources.getResourceAsReader("SqlMapConfig.xml");
sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
} catch (Exception e) {
e.printStackTrace();
} finally {
if(reader != null) {
try {
reader.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
reader = null;
}
}
}
}
public static void main(String[] args) {  
try {
User user = null;
List<User> userList = sqlMapper.queryForList("getAllUser");
for(int i=0;i<userList.size();i++) {
user = userList.get(i);
System.out.println("id:" + user.getId());
System.out.println("name:" + user.getName());
System.out.println("birthday:" + user.getBirthday());
System.out.println("");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}