前幾天有客戶問我這麼個問題，他們在weblogic中配置了prepared statement cache, 而他們應用中有操作DDL的地方，比如alter table什麼的，這時候如果使用cached prepared statement的話，Oracle端會拋出SQLException: 違反協議。其實這個問題，weblogic 文檔中已經有描述，如下：
http://e-docs.bea.com/wls/docs81/ConsoleHelp/jdbc_connection_pools.html#1107805
大概意思是：這個依賴數據庫，需要看數據庫端怎麼處理這樣的prepared statement. 最初我認為只要在weblogic 端手工清理掉整個cache就可以了（weblogic在prepared statement 出現異常的時候，會主動將wrapper connection上對應的prepared statement cache清掉，下次調用的時候會重建prepared statement，所以手工清理cache是完全多余的），但實際結果並不如想象的那樣。即使我們clear掉prepared statement cache, 重新創建一個prepared statement的話，問題同樣得不到解決。 為什麼？ 怎麼辦？作了幾個相關的測試後，結論是：這個行為依賴於DB的physical connection, 而不是單個的prepared statement，出現這樣的問題後，能做的有如下2種方式：
1：客戶端處理prepared statement拋出的異常， catch到異常後，需要將physical connection拿出來close掉。之所以建議這樣，客戶從data source中拿出的是個logical connection，而physical connection一直在connection pool。如果簡單的close掉logical connection, 重新去拿一個logical connection的話，weblogic無法保證返回的connection用了不同的physical connection。後面會有詳細的解決辦法。
2：等待，大約一分鐘左右，可以正常操作。
首先看看為什麼？
好了，我們可以用用下面的代碼測試一下：在測試程序run起來以後， 通過sql plus去改變後端test table的結構，比如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
11 public static void main(String args[])
12 {
13 try
14 {
15 OracleDriver driver = (OracleDriver)Class.
16 forName("oracle.jdbc.OracleDriver").newInstance();
17 DriverManager.registerDriver(driver);
18 String url="jdbc:oracle:thin:@localhost:1521:coffeedb";
19 Connection conn = DriverManager.getConnection(url, "system", "coffee");
20 PreparedStatement pstmt = conn.prepareStatement("select * from Test");
21 for(int loop=0; loop<10; loop++)
22 {
23 try
24 {
25 System.out.println(pstmt.toString());
26 ResultSet rs = pstmt.executeQuery();
27 while(rs.next())
28 {
29 String val = rs.getString(1);
30 System.out.println(val);
31 }
32 rs.close();
33 Thread.currentThread().sleep(5000);
34 }catch(java.sql.SQLException se)
35 {
36 //Thread.currentThread().sleep(10000);
37 se.printStackTrace();
38 System.out.println("get exception, remake prepared statement in loop: " + loop);
39 /*
40 * if we just remake a prepared statement, SQLException will be thrown still, to
41 * slove such issue, we have to remake a physical connection. To do the test, we
42 * can comment the next line at first to see what will happen and then we activate
43 * it, to see what will happen this time.
44 */
45 //conn = DriverManager.getConnection(url, "system", "coffee");
46 pstmt = conn.prepareStatement("select * from Test");
47 continue;
48 }
49 }
50 pstmt.close();
51 conn.close();
52
53 }catch(Exception e)
54 {
55 try
56 {
57 //Thread.currentThread().sleep(10000);
58 System.out.println("catch exception in main()");
59 e.printStackTrace();
60 }catch(Exception e1)
61 {
62 e1.printStackTrace();
63 }
64 }
65 }
66 }
67
如代碼中的注釋說的一樣，單純的重建prepared statement是沒用的，需要重建physical connection. 這個代碼中connection沒有通過weblogic, 直接從driver manager拿connection, 問題一樣能夠復現，跟weblogic沒關系了吧。
好了，知道為什麼了，但怎麼辦呢？ physical connection是weblogic在connection pool中維護的，我們怎麼去控制它們啊？看文檔， weblogic的jdbc programming提到了具體的操作方法，鏈接如下：
http://e-docs.bea.com/wls/docs81/jdbc/thirdparty.html#1108224
1 java.sql.Connection vendorConn = ((WLConnection)conn).getVendorConnection();
2 // do not close vendorConn
3 // You could also cast the vendorConn object //to a vendor interface, such as:
4 // oracle.jdbc.OracleConnection vendorConn = (OracleConnection)
文檔中不建議我們自己去關閉vendor connection，而是由connection pool自己去管理，connection pool通過Remove Infected Connections Enabled來控制physical connection如何還池，
Applies only to physical database connections.
When set to true, the physical connection is not returned to the connection pool after the application closes the logical connection. Instead, the physical connection is closed and recreated.
Note: It is recommended that you set this flag to true as such connections are not managed by WebLogic Server.
When set to false, if you close the logical connection, the physical connection is returned to the connection pool. If you use this setting, ensure that the database connection is suitable for reuse by other applications.
This parameter is applicable only if the application gets a connection from the connection pool and then calls the getVendorConnection() method on that object. The getVendorConnection() method returns a vendor specific connection to the caller of the method which might leave the connection pool in an inconsistent state. As a result, WebLogic Server might remove it from the pool assuming it is an infected connection.
Enabling this attribute will have an impact on performance as it will essentially disable the pooling of connections. This is because connections will be removed from the pool and replaced with new connections.
因為我們這個問題必須關閉physical connection, 所以采用默認配置就可以了。你也許還會有疑問，physical connection被關閉了，如果我反復搞這麼幾次，connection不就被關完了？其他應用怎麼辦？不用擔心，有connection pool呢，在getVendorConnection()被調用的時候， connection會檢查Remove Infected Connections Enabled，如果為true，即這個logical connection對應的physical connection不會被重用，它會schedule一個創建physical connection的動作，以補充那個拋棄我們的phisical connection。最後關閉連接的時候，logical connection會被廢棄，physical connection被關閉。
而我在測試中，嘗試自己去關閉vendor connection，如下：
1 private void initializeConnection() throws SQLException
2 {
3 //this test should be run in local jvm, as oracle.jdbc.driver.T4CConnection
4 //is not a serializable object.
5 //java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
6 this.conn = this.retriver.getJBDCConnection(dsName);
7 this.wlConn = (WLConnection)this.conn;
8 this.oraConn = (OracleConnection)this.wlConn.getVendorConnection();
9 System.out.println(this.conn.toString());
10 System.out.println(this.oraConn.toString());
11 }
12
13 private void pstmtTest()
14 {
15 try
16 {
17 PreparedStatement pstmt = this.conn.prepareStatement("select * from Test");
18 System.out.println(pstmt.toString());
19 ResultSet rs = pstmt.executeQuery();
20 while(rs.next())
21 {
22 String val = rs.getString(1);
23 System.out.println(val);
24 }
25 rs.close();
26 pstmt.close();
27 this.oraConn.close();
28 this.conn.close();
29 }catch(Exception e)
30 {
31 try
32 {
33 this.oraConn.close();
34 this.conn.close();
35 }catch(Exception e1)
36 {
37 e1.printStackTrace();
38 }
39 e.printStackTrace();
40 }
41 }
測試也沒什麼問題，應該是oracle connection在關閉connection時先去檢查connection 狀態，如果已經關閉，則忽略這個動作，否則weblogic在關閉physical connection的時候應該收到SQLException。雖然這麼做沒什麼問題，但還是建議大家按照文檔上的說明，不要主動關閉phisical connection, 而讓connection pool自己去管理。
總結一下：要處理這個問題，在catch到preparedStatement.execute***()拋出的 SQLException時候，將從data source中get出來的connection cast成WLConnection, 然後調用getVendorConnection()即可，不要做其他任何處理。但有一個限制，這個方法必須在server端執行， 因為Vendor Connection不是個serializable對象，不能被拿到remote JVM上去用。
時間有限，只做了Oracle的測試，沒有做其他DB的測試，比如DB2, SQLServer，畢竟這個問題依賴於數據庫，不保證這一做法是用於其它所有數據庫。
