最近开发一个应用，需要调用一个入参为List的存储过程。
存储过程为: proc_test(p1 OUT Number, p2 IN Number, p3 IN TAB_CUSTOMER);
这个List入参是一个在oracle中自定义的类型的表，如下：
CREATE OR REPLACE TYPE TAB_CUSTOMER AS TABLE OF TYP_CUSTOMER;
CREATE OR REPLACE TYPE TYP_CUSTOMER AS OBJECT
( ID VARCHAR2(20), NAME VARCHAR2(20), GENDER NUMBER, AGE NUMBER,BIRTHDAY DATE
);问题一：如何传List对象给oracle存储过程
一 开始我使用jpa的@Procedure，结果一直报错：参数个数或参数类型错误。我在项目中
定义了一个实体类Customer，和Oracle中的类型TYP_CUSTOMER字段相同。传了一个
List<Customer>给存储过程。java.util.List无法转换成存储过程需要的oracle.sql.ARRAY对象。
后来咨询了又相关经验的同事，问题解决，代码如下：
【以下代码解决如何传oracle.sql.ARRAY给存储过程的问题】
import java.util.List;
import java.sql.Connection;import oracle.sql.ARRAY;import oracle.sql.ArrayDescriptor;import oracle.sql.STRUCT;import oracle.sql.StructDescriptor;import oracle.sql.DATE;/*
以下方法返回一个oracle.sql.ARRAY对象，该对象与oracle中自定 的对象的表映射。 @param oracleType oracle中自定义的类 @param oracleTable oracle中自定义的类的表 @list 本地封装的数据列表 @return ARRAY 一个oracle.sql.ARRAY对象，该对象可与oracle中自定 的对象的表映射。*/private ARRAY getOracleArray(Connection con, String oracleType, String oracleTable, List<Customer> list) throws Exception { ARRAY array = null; ArrayDescriptor desc = ArrayDescriptor.createDescriptor(oracleTable, con); STRUCT[] structs = new STRUCT[list.size()];if (list != null && list.size() > 0) {
StructDescriptor structdesc = new StructDescriptor(oracleType, con); for (int i = 0, len = list.size(); i < len; i++) { Object[] result = { list.get(i).getId(), list.get(i).getName(), list.get(i).getGender(), list.get(i).getAge(), new DATE(new java.sql.Date(list.get(i).getBirthday().getTime()))}; structs[i] = new STRUCT(structdesc, con, result); } array = new ARRAY(desc, con, structs); } else { array = new ARRAY(desc, con, structs); } return array; }import java.util.Date;
class Customer { int id; String name; String gender; int age; Date birthday; /* 省略getter/setter */}问题二：只能发起连接池的最大连接数量的请求
问题代码如下：
@Autowired
private HikariDataSource hikariDataSource;
public void save(List<Customer> customers, int p2) {
Connection conn = null; try { conn = getConnection();PreparedStatement pstmt = conn.prepareStatement("call PKG_TEST.PROC_TEST(?,?,?)");
ARRAY p3 = getOracleArray(conn, TYP_CUSTOMER, TAB_CUSTOMER, customers);int p1 = 0;
pstmt.setInt(1, p1); pstmt.setInt(2, p2); pstmt.setArray(3, p3);pstmt.execute();
pstmt.close(); } catch (SQLException e) { throw new PersistException(e); } catch (Exception e) { throw new PersistException(e); } finally { if(conn != null) try{ conn.close(); }catch(Exception e){ throw new PersistException(e); } } } private Connection getConnection() { Connection conn = null; try { conn = hikariDataSource.getConnection(); DatabaseMetaData metaData = conn.getMetaData(); conn = metaData.getConnection(); } catch (SQLException e) { throw new PersistException(e); }return conn;
}class PersistException extends RuntimeException{ /*省略*/}
我这里注入了HikariDataSource，因为Hikari的Connection无法直接cast为
Oracle的Connection，所以做了以上转换。
看起来，一次访问数据库结束后，数据库连接没有释放。
【表查询也有同样的问题】
解决问题的代码如下：
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;import org.hibernate.engine.spi.SessionImplementor;
@PersistenceContext
private EntityManager entityManager;
public void save(SaleRetrainingReport report) {
try { Connection conn = entityManager.unwrap(SessionImplementor.class).connection(); CallableStatement stmt = conn.prepareCall("call PKG_TEST.PROC_TEST(?,?,?)"); ARRAY p3 = getOracleArray(toOracleConnection(conn), TYP_CUSTOMER, TAB_CUSTOMER, customers);int p1 = 0;
stmt.setInt(1, p1); stmt.setInt(2, p2); stmt.setArray(3, p3);stmt.execute();
stmt.close(http://www.my516.com); } catch (SQLException e) { throw new PersistException(e); } catch (Exception e) { throw new PersistException(e); } }private Connection toOracleConnection(Connection connection) {
Connection conn = null; try { DatabaseMetaData metaData = connection.getMetaData(); conn = metaData.getConnection(); } catch (SQLException e) { throw new PersistException(e); }return conn;
}
---------------------