使用OraclePreparedStatement通过Tomcat 8.5.9从java 8写入oracle 11.2数据库？ [英] Write to oracle 11.2 database from java 8 via Tomcat 8.5.9 using OraclePreparedStatement?

问题描述

使用带有Tomcat 8.5.9的Java 8编写Oracle 11.2数据库时遇到问题。实际上，以下代码适用于写入存储过程，但直接写入数据库时​​出错。

I'm having trouble writing to an Oracle 11.2 database using Java 8 with Tomcat 8.5.9. Actually, the following code works fine for writing to a stored procedure, but I get an error when writing to the database directly.

Context initCtx = new InitialContext(); 
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/myPool");
conn = ds.getConnection(); 

// The following works fine:   
cs = conn.prepareCall( "{call my_proc (?,?)}" );
cs.setString(1, var1);
cs.registerOutParameter(2, Types.VARCHAR); 
cs.execute();
out_var2 = cs.getString(2);

// The following results in a ClassCastException error:
sql ="INSERT INTO MY_TABLE1 (my_value1, my_value2) VALUES (?,?)";
ps = (OraclePreparedStatement) conn.prepareStatement(sql);

// The following results in the same error, but is an example of using Oracle extensions for setXXX():
sql="INSERT INTO MY_TABLE2 (my_value3, my_value4) VALUES (?,?)";
ps = (OraclePreparedStatement) conn.prepareStatement(sql);       
for (ii=0; ii<var100.length; ii++) {
     ps.setBinaryFloat(3,  my_value3[ii]);
     ps.setBinaryDouble(4, my_value4[ii]);
     ps.addBatch();
}
ps.executeBatch();

错误是： java.lang.ClassCastException：org.apache.tomcat .dbcp.dbcp2.DelegatingPreparedStatement无法强制转换为oracle.jdbc.OraclePreparedStatement 。

我最近从GlassFish切换到Tomcat。之前为Glassfish工作的代码是：

I recently switched from GlassFish to Tomcat. The previous code that was working for Glassfish was:

OracleDataSource ods = ds.unwrap(OracleDataSource.class);
OracleConnection conn = (OracleConnection) ods.getConnection();
conn = ds.getConnection();
sql_a ="INSERT INTO MY_TABLE (my_value1, my_value2) VALUES (?,?)";
ps_a = (OraclePreparedStatement) conn.prepareStatement(sql_a);

但是它给出了错误， java.lang.NullPointerException 使用Tomcat。

but it gives the error, java.lang.NullPointerException with Tomcat.

我使用以下链接作为指南配置了我的tomcat文件：

I've configured my tomcat files using the following link as a guide:

https://tomcat.apache.org/tomcat -8.5-doc / jndi-datasource-examples-howto.html

特别是上的部分Oracle 8i，9i和& 10g （上下文配置和web.xml）。

specifically the section on Oracle 8i, 9i, & 10g (context configuration and web.xml).

当我直接写入数据库时​​，我知道如何消除Tomcat错误，同时也允许上面的代码在写入存储过程时继续工作？

Any idea how I can get eliminate the Tomcat error when I write to the database directly, while also allowing the above code to continue working when writing to a stored procedure?

推荐答案

这可能不是最好的答案，但是通过反复试验，我发现我只需要在下面添加一行代码来解开与OracleConnection的连接，一切正常。

This may not be the best answer, but through trial and error, I found that I just need to add one line of code below to unwrap the connection to an OracleConnection, and everything works fine.

...
Connection tconn=null;
OracleConnection conn=null;
Context initCtx = new InitialContext(); 
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/myPool");
tconn = ds.getConnection();
// the following line is needed to unwrap to OracleConnection
conn= tconn.unwrap(OracleConnection.class);
tconn.close();
...

我确信有另一种（或许更好的）配置方式Tomcat用于OracleConnection，但我不知道该怎么做。

I'm sure there's an alternative (perhaps better) way to configure Tomcat for an OracleConnection, but I'm not sure how to do it.

这篇关于使用OraclePreparedStatement通过Tomcat 8.5.9从java 8写入oracle 11.2数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！