java 调用oracle 管道表函数

需求：用户导入100万白名单，如果该导入的白名单存在于数据库中，就不执行白名单导入功能。所以要对该导入白名单与数据库进行比对，将不再白名单中的数据传入。

单次10000个白名单批量插入前，将其过滤，单次操作session 开启时间不会太长，数据压力不大，session系统数量可以保证。

该过滤功能逻辑很简单，下面贴出管道表函数在java中的灵活运用
1）构建一个rowtype 这个row type 与java中传入的数组中单条数据结构保持一致
create or replace type MdnUser_row_type as object
(
mdn VARCHAR(15),
mdnOwner VARCHAR(20),
whiteid VARCHAR(20),
batchid VARCHAR(20),
status VARCHAR(20),
addTIME VARCHAR(20)
)
2）定义自定义数组table 类型
CREATE OR REPLACE TYPE MDNUSER_TABLE_TYPE AS TABLE OF MdnUser_row_type

3）构建管道表函数
create or replace function fn_to_table(MdnUser_TABLE IN MdnUser_TABLE_TYPE )
return MdnUser_TABLE_TYPE PIPELINED
as
MdnUser_row MdnUser_row_type ;
BEGIN
for i in 1..MdnUser_TABLE.count LOOP
MdnUser_row := MdnUser_TABLE(i);
PIPE ROW (MdnUser_row);
END LOOP ;

RETURN ;
end;

4）组装sql
String tblName = "NM_NET_USER_" +spid;
StringBuffer sb = new StringBuffer("");
sb.append("SELECT TEMPTBL.MDN ,TEMPTBL.MDNOWNER ,TEMPTBL.WHITEID, ");
sb.append("TEMPTBL.BATCHID,TEMPTBL.STATUS,TEMPTBL.ADDTIME ");
sb.append("FROM TABLE(FN_TO_TABLE(?)) TEMPTBL WHERE NOT EXISTS ");
sb.append("(SELECT U.MDN FROM "+tblName+" U WHERE U.MDN = TEMPTBL.MDN)") ;
return sb.toString();


5）数据访问部分代码
conn = conn.getMetaData().getConnection();
ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("MDNUSER_TABLE_TYPE", conn);
oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn,userArray);
pstmt = conn.prepareStatement(sqlQuery);
pstmt.setArray(1, array);
rs = pstmt.executeQuery();

6) 说明

TABLE(FN_TO_TABLE(?)) 管道表函数可以在sql中直接调用
conn = conn.getMetaData().getConnection();
hibernate session 获得的connection 无法直接向 oracle.jdbc.OracleConnection转型 ，要特出处理
MDNUSER_TABLE_TYPE
一定要大写 保持与数据库中type一致
oracle.sql.ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn,userArray);
将java 数组转换为oracle 数组