/* * Class: ConnectionHATest.java * Author: John Sheaffer, Oracle Corporation, 17 Aug 2005 * * This sample program gives an example of how to code a JDBC application so * that it can tolerate a connection failure during a transaction. An * application in a Highly Available system should be tolerant of connection * failures. It can tolerate them with just a little additional logic that * catches the SQLException thrown when the TCP socket is broken, followed by * simply re-issuing the transaction that was in progress. * * The code below shows that this connection failure tolerance can be as simple * as a single while loop, however there are many more creative ways to do it * also. The method connect() can be a method of a static object, and therefore * reused by all the JDBC code in an application. * * To run this sample program in a JRE, you will need to add the Oracle JDBC * driver file to the JRE's ../lib/ext directory. For example, on my laptop I * copy the file: * E:\oracle\product\10.1.0\db_1\jdbc\lib\classes12.jar * to the location: * C:\Program Files\Java\jre1.5.0_01\lib\ext\ * Then just modify the String jdbc_url below to point at your database, * compile, and run. * * Enjoy! * */ import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.SQLException; import java.sql.DriverManager; import java.util.Date; import oracle.jdbc.OracleDriver; public class ConnectionHATest { // JDBC-thin driver URL static String jdbc_url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=grigio)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=champagne)(PORT=1521))(LOAD_BALANCE = ON))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=wines)))"; // JDBC-OCI (a.k.a JDBC-Thick) driver URL...uses the tnsnames.ora file // static String jdbc_url = "jdbc:oracle:oci:@wines"; static String username = "demo"; static String password = "demo"; public static void main(String[] args) { ConnectionHATest connectionTest = new ConnectionHATest(); Statement u_stmt = null; Statement stmt = null; Statement stmt2 = null; // Make the initial connection Connection conn = connectionTest.connect(10); // If conn == null, something is wrong with the database from the get go if (conn == null) { System.err.println("Unable to initialize the database connection. " + "Please check if the database is available, and " + "check the JDBC connection URL for errors."); System.exit(1); } // This while statement encloses the transaction that would need to be // re-issued if the session is lost. BTW, there are much more elegant and // creative ways to do this...but this gives you the idea. boolean committed = false; while (!committed) { // Perform a simple transaction. try { u_stmt = conn.createStatement(); stmt = conn.createStatement(); stmt2 = conn.createStatement(); u_stmt.executeUpdate("INSERT INTO ORDERS_TABLE VALUES (sysdate, 10)"); ResultSet rs = stmt.executeQuery("Select count(*) from ORDERS_TABLE"); ResultSet rs2 = stmt2.executeQuery("Select instance_name from v$instance"); // Print out some results if (rs.next() && rs2.next()) { System.out.print((new Date()).toString() + ": " + "[" + rs2.getString(1) + "] "); System.out.println("ORDERS_TABLE = " + rs.getLong(1) + " rows"); } conn.commit(); u_stmt.close(); stmt.close(); stmt2.close(); // Set complete = true to move on // However for this demonstration, we want this to loop forever // committed = true; // Sleep for one second before moving on so we can actually read the output try { Thread.sleep(1000); } catch (InterruptedException e) {} } // End of the simple transaction // If the connection is severed, reconnect to restart the work catch (SQLException ex) { // Show us the exception that was caught System.err.println((new Date()).toString() + ": " + ex.toString()); // In here you can evaluate the SQLException for different causes // and then respond accordingly...but we're just going to reconnect // and re-issue to keep it simple. // Reconnect, trying up to 10 times conn = connectionTest.connect(10); // You probably want the .connect() method to fail after N tries // so that you can do something if the database is not coming // back up any time soon, such as if the DB was brought down // for maintenance and someone forgot to stop the application server, // or if the application was started before the database server. // If the code reaches this point and conn is still null, then all the // reconnect attempts failed. if (conn == null) { // In here, do something to close down, since the database // doesn't seem to be coming back up. System.err.println("All reconnection attempts failed"); System.exit(1); } } // end catch } // end while (!committed) // Main is exiting, so clean up nicely try { conn.close(); } catch (SQLException ex) { System.out.println("SQLExeption during stmt and conn closure"); } } // method main public Connection connect(int numRetries) { System.out.println((new Date()).toString() + ": " + "Connecting..."); long delay = 5000; // 5.0 seconds Connection conn = null; try { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); } catch (SQLException ex) { System.out.println("SQLException registering driver"); } // Try to connect numRetries times for(int i=0; i < numRetries; i++) { try { conn = DriverManager.getConnection(jdbc_url, username, password); if (conn != null) { break; } else { throw new SQLException(); } } catch (SQLException ex) { System.out.println((new Date()).toString() + ": " + "Connect failed"); System.err.println(ex.toString()); try { Thread.sleep(delay); } catch (InterruptedException e) {} } } if (conn != null) { System.out.println((new Date()).toString() + ": " +"<>"); } return conn; } // method connect }