+

Search Tips   |   Advanced Search

Example: Handling servlet JDBC connection exceptions


The following code sample demonstrates how to set transaction management and connection management properties, such as operation retries, to address stale connection exceptions within a servlet JDBC transaction.

This example code performs the following actions:

//===================START_PROLOG======================================

//

//   5630-A23, 5630-A22, 
//   (C) COPYRIGHT International Business Machines Corp. 2002,2008

//   All Rights Reserved

//   Licensed Materials - Property of IBM

//   disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

//

//   IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING

//   ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR

//   PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR

//   CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF

//   USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR

//   OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE

//   OR PERFORMANCE OF THIS SOFTWARE.

//

//===================END_PROLOG========================================
 package WebSphereSamples.ConnPool;
 import java.io.*;
 import javax.servlet.*;
 import javax.servlet.http.*;
 import java.util.*;

// Import JDBC packages and naming service packages. import java.sql.*;
 import javax.sql.*;
 import javax.naming.*;
 import javax.transaction.*;
 import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException;
 import com.ibm.websphere.rsadapter.WSCallHelper;
 public class EmployeeListTran extends HttpServlet {
    private static DataSource ds   = null;   
    private UserTransaction ut = null;
    private static String title    = "Employee List";    


// ****************************************************************

// * Initialize servlet when it is first loaded.                  *

// * Get information from the properties file, and look up the    *

// * DataSource object from JNDI to improve performance of the    *

// * the servlet's service methods.                               *

// ****************************************************************
    public void init(ServletConfig config)
    throws ServletException
    {
        super.init(config);
        getDS();
    }


// ****************************************************************

// * Perform the JNDI lookup for the DataSource and               *

// * User Transaction objects.                                    *           

// * This method is invoked from init(), and from the service     *

// * method of the DataSource is null                             *

// ****************************************************************
    private void getDS() {
        try {
            Hashtable parms = new Hashtable();
            parms.put(Context.INITIAL_CONTEXT_FACTORY, 
 "com.ibm.websphere.naming.WsnInitialContextFactory");
            InitialContext ctx = new InitialContext(parms);
            
// Perform a naming service lookup to get the DataSource object.
            ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB");
            ut = (UserTransaction) ctx.lookup("java:comp/UserTransaction");
        } catch (Exception e) {
            System.out.println("Naming service exception: " + e.getMessage());
            e.printStackTrace();
        }
    }


// ****************************************************************

// * Respond to user GET request                                  *

// ****************************************************************
    public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
    {
        Connection conn  = null;
        Statement stmt = null;
        ResultSet rs = null;
        Vector employeeList = new Vector();                                                         
        
// Set retryCount to the number of times you would like to retry after a
        
// stale connection exception
        int retryCount = 5;      
        
// If the Database code processes successfully, we will set error = false
        boolean error = true;
        do
        {
            try
            {          
                
//Start a new Transaction
                ut.begin();
                
// Get a Connection object conn using the DataSource factory.                               
                conn = ds.getConnection();
                
// Run DB query using standard JDBC coding.
                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 (SQLException sqlX)
            {
              
// Determine if the connection request timed out.
              
// This code works regardless of which error detection
              
// model is used. If exception mapping is enabled, then
              
// we need to look for ConnectionWaitTimeoutException.
              
// If exception checking is enabled, then look for
              
// SQLTransientConnectionException with a chained
              
// ConnectionWaitTimeoutException.

              if ( sqlX instanceof ConnectionWaitTimeoutException
                || sqlX instanceof SQLTransientConnectionException
                   && sqlX.getCause() instanceof ConnectionWaitTimeoutException)
              {
                
// 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
                
//and roll back the transaction
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    
//Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only! " + se.getMessage());                
                } 
                catch (IllegalStateException ise)
                {
                    
//Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only! " + ise.getMessage());                    
                } 
                catch (SystemException sye)
                {
                    
//Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only! " + sye.getMessage());
                }
                retryCount=0;   
              }
              else if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX))
              {
                
// This exception indicates that the connection to the database is no longer valid.
                
//Roll back the transaction, then 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("Connection is stale: " + sc.getMessage());
                
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    
//Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only! " + se.getMessage());                
                } 
                catch (IllegalStateException ise)
                {
                    
//Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only! " + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    
//Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only! " + sye.getMessage());
                }
                if (--retryCount == 0)
                {
                    System.out.println("Five stale connection exceptions, displaying error page.");            
                }
              } 
              else
              {
                System.out.println("SQL Exception during get connection or process SQL: " + sq.getMessage());       

                
//In general, we do not want to retry after this exception, so set retry count to 0
                
//and rollback the transaction
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    
//Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only! " + se.getMessage());                
                } 
                catch (IllegalStateException ise)
                {
                    
//Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only! " + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    
//Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only! " + sye.getMessage());
                }
                retryCount=0;
              } 
            }
            catch (NotSupportedException nse)
            {
                
//Thrown by UserTransaction begin method if the thread is already associated with a
                
//transaction and the Transaction Manager implementation does not support nested  
                
//transactions.
                System.out.println("NotSupportedException on User Transaction begin: " + nse.getMessage());
            } 
            catch (SystemException se)
            {
                
//Thrown if the transaction manager encounters an unexpected error condition
                System.out.println("SystemException in User Transaction: " +se.getMessage());
            } 
            catch (Exception e)
            {
                System.out.println("Exception in get connection or process SQL: " + e.getMessage());                
                
//In general, we do not want to retry after this exception, so set retry count to 5
                
//and roll back the transaction
                try
                {
                    ut.setRollbackOnly();
                } 
                catch (SecurityException se)
                {
                    
//Thrown to indicate that the thread is not allowed to roll back the transaction.
                    System.out.println("Security Exception setting rollback only! " + se.getMessage());
                } 
                catch (IllegalStateException ise)
                {
                    
//Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception setting rollback only! " + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    
//Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception setting rollback only! " + sye.getMessage());
                }
                retryCount=0;
            } 
            finally
            {
                
// Always close the connection in a finally statement to ensure proper
                
// closure in all cases. Closing the connection does not close and 
                
// actual connection, but releases it back to the pool for reuse.

                if (rs != null)
                {
                   try
                   {
                      rs.close();
                   } 
                   catch (Exception e)
                   {
                      System.out.println("Close Resultset Exception: " + e.getMessage());
                   }
                }
                if (stmt != null)
                {
                   try
                   {
                      stmt.close();
                   } 
                   catch (Exception e)
                   {
                      System.out.println("Close Statement Exception: " + e.getMessage());
                   }
                }
                if (conn != null)
                {
                   try
                   {
                      conn.close();
                   } 
                   catch (Exception e)
                   {
                      System.out.println("Close connection exception: " + e.getMessage());
                   }
                }
                try
                {
                    ut.commit();
                } 
                catch (RollbackException re)
                {
                    
//Thrown to indicate that the transaction has been rolled back rather than committed.
                    System.out.println("User Transaction Rolled back! " + re.getMessage());                    
                } 
                catch (SecurityException se)
                {
                    
//Thrown to indicate that the thread is not allowed to commit the transaction.
                    System.out.println("Security Exception thrown on transaction commit: " + se.getMessage());
                } 
                catch (IllegalStateException ise)
                {
                    
//Thrown if the current thread is not associated with a transaction.
                    System.out.println("Illegal State Exception thrown on transaction commit: " + ise.getMessage());
                } 
                catch (SystemException sye)
                {
                    
//Thrown if the transaction manager encounters an unexpected error condition
                    System.out.println("System Exception thrown on transaction commit: " + sye.getMessage());
                } 
                catch (Exception e)
                {
                    System.out.println("Exception thrown on transaction commit: " + e.getMessage());
                }
            }
        }
        while ( error==true && retryCount > 0 );        

        
// Prepare and return HTML response, prevent dynamic content from being cached
        
// on browsers.
        res.setContentType("text/html");
        res.setHeader("Pragma", "no-cache");
        res.setHeader("Cache-Control", "no-cache");
        res.setDateHeader("Expires", 0);
        try
        {
            ServletOutputStream out = res.getOutputStream();
            out.println("<HTML>");
            out.println("<HEAD><TITLE>" + title + "</TITLE></HEAD>");
            out.println("<BODY>");                  
            if (error==true)
            {
                out.println("<H1>There was an error processing this request.</H1>" +
                  "Please try the request again, or contact " +
                  " the <a href='mailto:sysadmin@my.com'>System Administrator</a>");
            } 
            else if (employeeList.isEmpty())
            {
               out.println("<H1>Employee List is Empty</H1>");
            } 
            else
            {
               out.println("<H1>Employee List </H1>");
               for (int i = 0; i < employeeList.size(); i++)
               {
                  out.println(employeeList.elementAt(i) + "<BR>"); 
               }
            }
            out.println("</BODY></HTML>");
            out.close();
        } 
        catch (IOException e)
        {
           System.out.println("HTML response exception: " + e.getMessage());
        }
    }
}




 

Related


Administrative console buttons
Administrative console page features

 

Related information


Change the error detection model to use the Exception Checking Model