+

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
//   All Rights Reserved
//   Licensed Materials - Property of IBM
//   US Government Users Restricted Rights - Use, duplication or
//   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.  Note the lack
// of an IBM Extensions package import.  This is no longer required. import java.sql.*; import javax.sql.*; import javax.naming.*; import javax.transaction.*;
 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
// StaleConnectionException         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 (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
//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 (com.ibm.websphere.ce.cm.StaleConnectionException sc) {

// This exception indicates that the connection to the database is no longer valid.
//Rollback 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("Stale Connection Exception during get connection or process SQL: " + 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.");            
     }   } 
   catch (SQLException sq) {
     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 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;   } 
   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 Reference

Administrative console buttons
Administrative console page features

 

Reference topic