原文地址:http://qihang.net/u/fesing/Blog/t-1845
最近项目中遇到通过往存储过程传递数组参数的问题,
浪费了N多个小时,终于有点头绪。
具体的代码就不写上了,因为项目中存储过程的调用方法全部是封装好的(好像现在都这样,都姓3层,嘿嘿)
原理:
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://blog.chinaunix.net/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<Comparable> CancelLog(String sLoginUser, Object[] arrLogID)
{
ArrayList<Comparable> arrList = new ArrayList<Comparable>();
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;
}
}
最近项目中遇到通过往存储过程传递数组参数的问题,
浪费了N多个小时,终于有点头绪。
具体的代码就不写上了,因为项目中存储过程的调用方法全部是封装好的(好像现在都这样,都姓3层,嘿嘿)
原理:
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://blog.chinaunix.net/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<Comparable> CancelLog(String sLoginUser, Object[] arrLogID)
{
ArrayList<Comparable> arrList = new ArrayList<Comparable>();
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;
}
}