ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brice Ruth <bdr...@gmail.com>
Subject Re: StaleConnectionException
Date Mon, 14 Feb 2005 20:27:33 GMT
Does Websphere have a mechanism for validating connections, much like
Tomcat's DataSource? We simply supply a validation query that either
gets executed *every* time a DataSource is provided to a client -OR-
after a certain timeout (idle time).

This helped us when our iSeries was bounced regularly Sunday mornings.

Brice


On Mon, 14 Feb 2005 14:16:16 -0600, Cory.Bestgen@courts.mo.gov
<Cory.Bestgen@courts.mo.gov> wrote:
> 
> 
> 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
> cory.bestgen@courts.mo.gov
> 
>

Mime
View raw message