StaleConnectionException

[Cory . Bestgen]

Hello,
I am using websphere 5.1.2, oracle 9i and ibatis 2.0.8. I was using the
following
example from the jpetstore demo as a template for setting up my ibatis
transactions
but crazy things happen when the database has gone down and come back up.

public Order getOrder(int orderId) {
  Order order = null;
  try {
    daoManager.startTransaction();
    order = orderDao.getOrder(orderId);
    for (int i = 0; i < order.getLineItems().size(); i++) {
      LineItem lineItem = (LineItem) order.getLineItems().get(i);
      lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
    }
    daoManager.commitTransaction();
  } finally {
    daoManager.endTransaction();
  }
  return order;
}

Websphere likes to throw a StaleConnectionException when a connection in
the pool has gone
bad. At the websphere site ibm shows how to use a construct something
similar
to the code below that retries if the StaleConnectionException is thrown.

// Set retryCount to the number of times you would like to retry after a
StaleConnectionException
int retryCount = 5;
// If the Database code processes successfully, we will set error = false
boolean error = true;
do {
  try {
    conn = ds.getConnection();
    stmt = conn.createStatement();
    String query = "Select FirstNme, MidInit, LastName from Employee ORDER
BY LastName";
    rs = stmt.executeQuery(query);
    while (rs.next()) { employeeList.addElement(rs.getString(3) + ", " +
rs.getString(1) + " " + rs.getString(2)); }
    //Set error to false to indicate successful completion of the database
work
    error=false;
  } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
    // This exception is thrown if a connection can not be obtained from
the
    // pool within a configurable amount of time.  Frequent occurrences of
    // this exception indicate an incorrectly tuned connection pool
    System.out.println("Connection Wait Timeout Exception during get
connection or process SQL: " + c.getMessage());
    //In general, we do not want to retry after this exception, so set
retry count to 0
    retryCount = 0;
  } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
    // This exception indicates that the connection to the database is no
longer valid.
    // Retry several times to attempt to obtain a valid
    //connection, display an error message if the connection still can not
be obtained.
    System.out.println("Stale Connection Exception during get connection or
process SQL: " + sc.getMessage());
    if (--retryCount == 0) {
      System.out.println("Five stale connection exceptions, displaying
error page.");
    }
  }
} while ( error==true && retryCount > 0 );

And now my question, is there a way to have ibatis automatically retry when
it gets a StaleConnectionException?
Or should each method implement something like below?

public Order getOrder(int orderId) {
  Order order = null;
  int retryCount = 5;
  boolean error = true;
  do {
    try {
      daoManager.startTransaction();
      order = orderDao.getOrder(orderId);
      for (int i = 0; i < order.getLineItems().size(); i++) {
        LineItem lineItem = (LineItem) order.getLineItems().get(i);
        lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
      }
      daoManager.commitTransaction();
      error = false;
    } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
      System.out.println("Connection Wait Timeout Exception during get
connection or process SQL: " + c.getMessage());
      retryCount = 0;
    } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
      System.out.println("Stale Connection Exception during get connection
or process SQL: " + sc.getMessage());
      if (--retryCount == 0) {
        System.out.println("Five stale connection exceptions, displaying
error page.");
      }
    } finally {
      daoManager.endTransaction();
    }
  } while ( error==true && retryCount > 0);
  return order;
}

I am new to websphere and haven't seen a topic like this covered on the
list yet.

thanks


Cory Bestgen
Computer Information Technologist II
Information Technology Division
Office of State Courts Administrator
(573) 522 - 5455
[EMAIL PROTECTED]