Spring访问传入数组参数的Oracle存储过程

　　在JAVA程序中访问具有数组入参的Oracle存储过程，必须通过java.sql.Array来实现。通过查看java.sql.Array的源代码
　　

package java.sql;
/**
* The mapping in the Java programming language for the SQL type
* <code>ARRAY</code>.
* By default, an <code>Array</code> value is a transaction-duration
* reference to an SQL <code>ARRAY</code> value.  By default, an <code>Array</code>
* object is implemented using an SQL LOCATOR(array) internally, which
* means that an <code>Array</code> object contains a logical pointer
* to the data in the SQL <code>ARRAY</code> value rather
* than containing the <code>ARRAY</code> value's data.
* <p>
* The <code>Array</code> interface provides methods for bringing an SQL
* <code>ARRAY</code> value's data to the client as either an array or a
* <code>ResultSet</code> object.
* If the elements of the SQL <code>ARRAY</code>
* are a UDT, they may be custom mapped.  To create a custom mapping,
* a programmer must do two things:
* <ul>
* <li>create a class that implements the {@link SQLData}
* interface for the UDT to be custom mapped.
* <li>make an entry in a type map that contains
*   <ul>
*   <li>the fully-qualified SQL type name of the UDT
*   <li>the <code>Class</code> object for the class implementing
*       <code>SQLData</code>
*   </ul>
* </ul>
* <p>
* When a type map with an entry for
* the base type is supplied to the methods <code>getArray</code>
* and <code>getResultSet</code>, the mapping
* it contains will be used to map the elements of the <code>ARRAY</code> value.
* If no type map is supplied, which would typically be the case,
* the connection's type map is used by default.
* If the connection's type map or a type map supplied to a method has no entry
* for the base type, the elements are mapped according to the standard mapping.
* <p>
* @since 1.2
*/
public interface Array {
...
}
　　
　　可以明显看到，Array的实现时通过SQL LOCATOR。通过JDBC调用时，是使用指向数组的指针来实现，而不是复制一份拷贝。类似的类型还有BLOB，CLOB等大对象处理，在调用时，仅维护一个引用。维护指向数据库实际类型的指针，比较类似的用法是java.sql.ResultSet。为了获取到实际的指针引用，应用程序需要维护一个数据库的物理连接。
　　
　　在实际应用中，尤其是基于Spring的应用中，优先采用数据库连接池来操作数据库，而不是JDBC只连。Spring的参考配置：

   <bean id="devDataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:thin:@10.240.14.10:1521:p09tr</value>
</property>
<property name="username">
<value>test</value>
</property>
<property name="password">
<value>test</value>
</property>
<property name="defaultAutoCommit">
<value>false</value>
</property>
<property name="maxActive">
<value>5</value>
</property>
<property name="accessToUnderlyingConnectionAllowed">
<value>true</value>
</property>
</bean>
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>mm_datasource</value>
</property>
<property name="jndiEnvironment">
<props>
<prop key="java.naming.provider.url">
t3://10.240.14.1:7100
</prop>
<prop key="java.naming.factory.initial">
weblogic.jndi.WLInitialContextFactory
</prop>
</props>
</property>
<property name="defaultObject" ref="devDataSource" />
</bean>
<bean id="sessionFactory"
class="com.fenet.insurance.core.spring.hibernate3.annotation.ScanAnnotationSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="annotatedScanPackages">
<list>
<value>com.fenet.insurance.mm.study.hibernate.id.entity</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.Oracle9Dialect
</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.jdbc.batch_size">5</prop>
</props>
</property>
</bean>
<bean id="oracleTypeHandler"
class="ccom.fenet.insurance.mm.common.oracle.OracleTypeHandlerImpl">
<property name="dataSource">
<ref local="dataSource" />
</property>
</bean>
　　
　　优先通过JNDI获取连接池，如果没有取到则，默认用dbcp的连接池。通过DataSource访问数据库，用到的连接是逻辑连接，而非实际连接数据库的物理连接，仅仅是物理的连接的一个Wrapper。在本文中讨论，在Spring应用中调用具有Array参数的Oracle存储过程，在创建Array时需要用到实际的物理连接。
　　
　　假设，我们使用的Oracle表和数组类型为

create table study_array_nick_tab
(
name varchar2(200)
);
create or replace type study_array_nick_list is VARRAY(1000) of varchar2(200);

　　
　　被调用的Oracle存储过程为

create or replace procedure study_array_nick(in_array in study_array_nick_list) is
v_i number;
begin
for v_i in 1 .. in_array.count loop
insert into study_array_nick_tab(name) values(in_array(v_i));
end loop;
commit;
exception when others then
rollback;
raise_application_error('20999','测试错误');
end study_array_nick;

　　
　　在实际生成Array时，

public static <D> ARRAY createARRAY(DataSource dataSource,
String arrayname, D[] objects) {
Assert.notNull(dataSource, "创建Array失败，传入参数dataSource为空.");
Assert.hasText(arrayname, "创建Array失败，传入参数arrayname为空.");
Assert.notEmpty(objects, "创建Array失败，传入参数objects为空.");
Connection poolConn = null;
Connection vendorConn = null;
try {
poolConn = dataSource.getConnection();
if(poolConn instanceof DelegatingConnection) {
vendorConn = (OracleConnection) ((PoolableConnection) ((DelegatingConnection) poolConn).getDelegate()).getDelegate();
} else {
vendorConn = ((WLConnection) poolConn).getVendorConnection();}
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
arrayname, vendorConn);
return new ARRAY(arrayDescriptor, vendorConn, objects);
} catch (SQLException e) {
throw new BusinessException("创建Array失败.", e);
} finally {
vendorConn = null;
try {
if (poolConn != null && !poolConn.isClosed()) {
poolConn.close();
}
} catch (SQLException e) {
throw new BusinessException("创建Array,关闭连接失败.", e);
}
}
}
　　使用完连接后，尽量只关闭池逻辑连接，对于物理连接不应该关闭，而是交给池去管理。得到Array后，就可以调用包含此Array参数的存储过程。

                                DataSource ds = bean.getDataSource();
JdbcTemplate jdbc = new JdbcTemplate(ds);
//PlatformTransactionManager tm = new DataSourceTransactionManager(ds);
//TransactionStatus status = tm.getTransaction(null);
jdbc.execute(new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con)
throws SQLException {
return con.prepareCall("{call study_array_nick(?)}");
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
ARRAY oa = bean.createARRAY("STUDY_ARRAY_NICK_LIST",
new String[] { "666", "7777", "7775" });
Assert.notNull(oa);
cs.setArray(1, oa);
cs.execute();
return null;
}
});
//tm.commit(status);
　　
　　在实际使用中发生了很多问题，首先对于不同连接池，获取物理连接的方式不一致。另外，在非Weblogic环境下使用Weblogic连接池来生成Oracle的ARRAY时，调用getVendorConnection时会出现序列化错误。经查实后，发现Weblogic中的JDBC操作的序列化，会由容器通过特别的类来中转（比如说SerailConnection）。