求助,JAVA Spring 连接池 调用 Oracle 存储过程的问题?在Spring中配置了c3P0的连接池,在调用Oracle的存储过程时,报错[code]java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.TypeDescriptor.setPhysicalConnectionOf(TypeDescriptor.java:309)
at oracle.sql.TypeDescriptor.(TypeDescriptor.java:105)
at oracle.sql.ArrayDescriptor.(ArrayDescriptor.java:247)
at com.workssys.uen.server.dao.oracle.OracleDeviceInfoDAO$2.createCallableStatement(OracleDeviceInfoDAO.java:67)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java17)
[/code]我的Spring的配置如下:[code]
oracle
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@172.31.0.102:1521:orcl
orca
wks
3
15
100
3
5000
[/code]Oracle中的自定义类型以及存储过程定义如下:[code]create or replace type onems_ids as table of number(19);[/code][code]create or replace procedure P_TestJava(in_ids in onems_ids) is
begin
delete from test_java;
for i in 1..in_ids.count loop
dbms_output.put_line(in_ids(i));
insert into test_java(id) values (in_ids(i));
end loop;
commit;
end P_TestJava;[/code]Java中调用的Code如下:[code]
// 这是主程序,通过Junit调用
public void testMain() {
List list = new ArrayList();
list.add(new Long(5));
list.add(new Long(3));
list.add(new Long(28));
log.info("doDeviceRegistered s-------------------");
doTest(list);
log.info("doDeviceRegistered e-------------------");
}
public List doTest(List ids) {
JdbcDAO jdbcDao = (JdbcDAO) BeanLocator.getBean("jdbcDAO");
final List res = new ArrayList(100);
final String sql = "{call p_testjava(?)}";
jdbcDao.execute(createCreatorForTest(sql,ids),createCallbackForTest(res));
return res;
}
private CallableStatementCreator createCreatorForTest(final String sql,final List arg_ids) {
return new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection conn)
throws SQLException{
ArrayDescriptor desc = new ArrayDescriptor("ONEMS_IDS",conn);
Object[] int_ids = arg_ids.toArray();
ARRAY ids = new ARRAY(desc,conn,int_ids);
OracleCallableStatement cs = (OracleCallableStatement)conn.prepareCall(sql);
cs.setARRAY(1,ids);
return cs;
}
};
}private CallableStatementCallback createCallbackForTest(final List res) {
return new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException{
cs.execute();
return null;
}
};
}[/code]其中 JdbcDAO仅是Spring中的JdbcTemplate的一个空继承的子类。
哪位大侠指点一下,应该如何解决啊?
怎么报造型错误,两个connection不能传递
我记得spring调procedure是用回调方法[code] public int processControl(final String orderid,final String nmMsg,final String status){
int rlt = 0;
SpringStoredProcedure sp = new SpringStoredProcedure(dataSource,"pkg_processcontrol.pro_nextnode");
//×¢²á²ÎÊýÀàÐÍ,ÊäÈë²ÎÊýºÍÊä³ö²ÎÊýͬʱע²á,·ñÔò²»ÄÜÕýÈ·±àÒë´æ´¢¹ý³Ì
sp.setParameter("str_orderid",java.sql.Types.VARCHAR);
sp.setParameter("str_nmmsg",java.sql.Types.VARCHAR);
sp.setParameter("str_status",java.sql.Types.VARCHAR);
sp.setOutParameter("n_errCode", java.sql.Types.INTEGER);
sp.setOutParameter("str_errMsg", java.sql.Types.VARCHAR);
sp.compile();
//´«ÈëÊäÈë²ÎÊýÖµ
Map in = new HashMap();
in.put("str_orderid",orderid);
in.put("str_nmmsg",nmMsg);
in.put("str_status",status);
sp.SetInParam(in);
//Ö´Ðд洢¹ý³Ì
Map m = sp.execute();
rlt = new Integer(m.get("n_errCode").toString()).intValue();
if (rlt<0){
String sqlErrm = m.get("str_errMsg").toString();
System.out.println("ORACLE-ERROR:"+sqlErrm);
}
return rlt;
}[/code]
终于解决了!解决方法很简单,只是以前一直没有向这个方面去想。
看了上面的文章后,也没有什么进展,只是知道在Hibernate中可以用一个新的方法去得到Connection。但是对于c3p0来说,根本不适用。
后来到网上瞎逛,听一个大哥说他在spring配置了多个数据源,分别联Oracle,db2,mysql。
因此得到了启发,其实没必要从c3p0中拿connection。我完全可以配置2个数据源,对于这些特殊的Oracle的存储过程方法,可以直接用Oracle自己的连接池,甚至是非连接池的JDBC连接。只是这些数据源都指向一个数据库而已。