java oracle传参数类型吗,java实现oracle传递array数组类型的参数

java实现oracle传递array数组类型的参数

原理：

1.一维数组

A.单纯的一维数组的话，直接建立一个table类型就可以

TYPE TYPE_VARCHAR AS TABLE OF VARCHAR2(200);

2.多维数组

A.多维数组就要稍加修改了，如二维数组

TYPE TYPE_VARCHAR AS TABLE OF VARCHAR2(200);

TYPE TYPE_TABLE AS TABLE OF TYPE_VARCHAR;

3.java代码的实现

A。首先是connection的问题，connection必须是OracleConnection(java.sql包下的)

B。必须给Array添加一种数据定义(oracle.sql.ArrayDescriptor)

C。ARRAY必须是java.sql.Array

D。好了，开始定义ARRAY

arrDesc = ArrayDescriptor.createDescriptor("TYPE_TABLE", getNativeConnection(conn));

ARRAY arr = new ARRAY(arrDesc, getNativeConnection(conn), new Object[][]{{"test1","test2"},{"testA","testB"}});

从网上搜到为数不多的代码(仅供参考)

下面代码来自ChinaUnix博客(http://doc.docsou.com/u2/63586/showart_703439.html)

-----------------------------------------------------------------------------------

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

import org.apache.tomcat.dbcp.dbcp.DelegatingConnection;

public class BussinessLog {

public static ArrayList CancelLog(String sLoginUser, Object[] arrLogID)

{

ArrayList arrList = new ArrayList();

Connection conn = null;

CallableStatement callStmt = null;

String sql = null;

ArrayDescriptor arrDesc = null;

try

{

conn = DbConnectionManager.getConnection();

sql = "{call P_CanceltLog(?,?,?,?)}";

callStmt = conn.prepareCall(sql);

arrDesc = ArrayDescriptor.createDescriptor("NUMBERTABLE", getNativeConnection(conn));

ARRAY arr = new ARRAY(arrDesc, getNativeConnection(conn), arrLogID);

callStmt.setString(1, sLoginUser);

callStmt.setObject(2, arr, Types.ARRAY);

callStmt.registerOutParameter(3, Types.VARCHAR);

callStmt.registerOutParameter(4, Types.INTEGER);

callStmt.execute();

arrList.add(callStmt.getInt(4));

arrList.add(callStmt.getString(3));

return arrList;

} catch (Exception e) {

System.out.println(e.toString());

} finally {

DbAction.clear(conn, callStmt);

}

return arrList;

}

public static Connection getNativeConnection(Connection con) throws SQLException {

if (con instanceof DelegatingConnection) {

Connection nativeCon = ((DelegatingConnection) con).getInnermostDelegate();

return (nativeCon != null ? nativeCon : con.getMetaData().getConnection());

}

return con;

}

}