On the problem of manipulating DDL after using prepared statement cache in WebLogic

A few days ago, a customer asked me such a question, they configured the prepared statement cache in WebLogic, and they have operations in the application of DDL, such as ALTER TABLE or something, when the use of cached prepared Statement, Oracle End throws SqlException: breach of protocol. In fact, this problem is already described in the WebLogic document, as follows:

http://e-docs.bea.com/wls/docs81/ConsoleHelp/jdbc_connection_pools.html#1107805

Probably means: This relies on the database, needs to look at the database end how to deal with such prepared statement. Initially I think that as long as the WebLogic end of the manual cleaning off the entire cache can be (WebLogic in prepared statement abnormal, will take the initiative wrapper connection on the corresponding prepared statement Cache cleared, the next time the call will be rebuilt prepared statement, so manual cleanup cache is completely redundant), but the actual results are not as imagined. Even if we clear out prepared statement cache and recreate a prepared statement, the problem is not solved. Why? What to do? After several related tests, the conclusion is that this behavior relies on the physical connection of DB, rather than on individual prepared statement, there are 2 ways to do this after this problem:

1: The client handles prepared statement throws the exception, catch to the exception, need to physical connection take out close off. It is recommended that the customer take out a logical connection from data source, while physical connection has been in connection pool. If the simple close off logical connection, to get a logical connection again, WebLogic can not guarantee that the return connection used a different physical connection. There will be a detailed solution later.

2: Wait, about a minute or so, can be normal operation.

First look at why?

Well, we can test it with the following code: After the test program is run, change the structure of the backend test table with SQL Plus, such as ALTER TABLE test Add (key1 varchar (10))

1 package test.jdbc;
2
3 Import Oracle.jdbc.OracleDriver;
4 Import Java.sql.DriverManager;
5 Import java.sql.Connection;
6 Import java.sql.PreparedStatement;
7 Import Java.sql.ResultSet;
8
9 public class Oracledrivertest {
10
One public static void main (String args[])
12 {
A try
14 {
Oracledriver Driver = (oracledriver) Class.
forname ("Oracle.jdbc.OracleDriver"). newinstance ();
Drivermanager.registerdriver (driver);
A String url= "Jdbc:oracle:thin: @localhost: 1521:coffeedb";
Connection conn = drivermanager.getconnection (URL, "system", "coffee");
PreparedStatement pstmt = conn.preparestatement ("SELECT * from Test");
for (int loop=0; loop<10; loop++)
22 {
Try
24 {
System.out.println (Pstmt.tostring ());
ResultSet rs = Pstmt.executequery ();
while (Rs.next ())
28 {
The String val = rs.getstring (1);
System.out.println (Val);
31}
Rs.close ();
Thread.CurrentThread (). Sleep (5000);
}catch (java.sql.SQLException se)
35 {
//thread.currentthread (). Sleep (10000);
Panax Notoginseng se.printstacktrace ();
System.out.println ("Get exception, remake prepared statement in loop:" + loop);
39/*
* If we just remake a prepared statement, SQLException'll be thrown still
Slove Such issue, we have to remake a physical connection. To do the test, we
The * can comment the next line at a to-what would happen and then we activate
* It, to the what would happen this time.
44 */
//conn = drivermanager.getconnection (URL, "system", "coffee");
pstmt = Conn.preparestatement ("SELECT * from Test");
Continue;
48}
49}
Pstmt.close ();
Wuyi Conn.close ();
52
}catch (Exception e)
54 {
Try
56 {
//thread.currentthread (). Sleep (10000);
System.out.println ("Catch exception in Main ()");
E.printstacktrace ();
}catch (Exception E1)
61 {
E1.printstacktrace ();
63}
64}
65}
66}
67