All enterprise bean container-managed persistence (CMP) beans under the Enterprise JavaBeans (EJB) 2.x specification receive a standard EJB exception when an operation fails. Java Database Connectivity (JDBC) applications receive a standard SQL exception if any JDBC operation fails. The product provides special exceptions for its relational resource adapter (RRA), to indicate that the connection currently held is no longer valid.
For a Version 4.0 data source, the ConnectionWaitTimeout object creates an exception that is instantiated from the com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException class.
For Java 2 Connector (J2C) connection factories, the ConnectionWaitTimeout object generates a resource exception of the com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException class.
When the error detection model is configured to exception mapping, later versions of data sources issue an SQL exception of the com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException subclass. When the error detection model is configured to exception checking, later versions of data sources issue an SQL exception of the java.sql.SQLTransientConnectionException class with a chained exception of the com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException class.
"S1000"=;1062=com.ibm.websphere.ce.cm.DuplicateKeyException;"08004"=
com.ibm.websphere.ce.cm.StaleConnectionException
userDefinedErrorMap
can be located in the administrative console by selecting the data
source and configuring the custom properties.The product provides a special subclass of the java.sql.SQLException class for using connection pooling to access a relational database. This com.ibm.websphere.ce.cm.StaleConnectionException subclass exists in both a WebSphere® 4.0 data source and in the most recent version data source that use the relational resource adapter. This class serves to indicate that the connection currently held is no longer valid.
A negative ramification does ensue, however, when the transaction manager closes the connections and returns the connection to the free pool after a transaction ends. An application cannot obtain a connection in one transaction and try to use it in another transaction. If the application tries this connection, a stale connection exception occurs because the connection is already closed.
If you are trying to use an orphaned connection or a connection that is made unavailable by auto connection cleanup, a stale connection exception indicates that the application has attempted to use a connection that is already returned to the connection pool. It does not indicate an actual problem with the connection. However, other cases of a stale connection exception indicate that the connection to the database has gone bad, or stale. Once a connection has gone stale, you cannot recover it, and you must completely close the connection rather than returning it to the pool.
Detecting stale connectionsWhen a connection to the database becomes stale, operations on that connection result in an SQL exception from the JDBC driver. Because an SQL exception is a rather generic exception, it contains state and error code values that you can use to determine the meaning of the exception. However, the meanings of these states and error codes vary depending on the database vendor. The connection pooling run time maintains a mapping of which SQL state and error codes indicate a stale connection exception for each database vendor supported. When the connection pooling run time catches an SQL exception, it checks to see if this SQL exception is considered a stale connection exception for the database server in use.
Recovering from stale connectionsBecause of the differences between error detection models, the application server provides an API that applications can use with either case to identify stale connections. The API is com.ibm.websphere.rsadapter.WSCallHelper.getDataStoreHelper(datasource).isConnectionError(sqlexception).
Applications are not required to explicitly identify a stale connection exception. Applications are already required to catch the java.sql.SQLException, and the stale connection exception or the exception that is raised by the JDBC driver, always inherits data from the java.sql.SQLException. The stale connection exception, which can result from any method that is declared to raise SQLException, is caught automatically in the general catch-block. However, explicitly identifying a stale connection exception makes it possible for an application to recover from bad connections. When application code identifies a stale connection exception, it should take explicit steps to recover, such as retrying the operation under a new transaction and new connection.
These code samples demonstrate how to programmatically address stale connection exceptions for different types of data access clients in different transaction scenarios.
When an application receives a stale connection exception on a database operation, it indicates that the connection currently held is no longer valid. Although it is possible to get an exception for a stale connection on any database operation, the most common time to see a stale connection exception issued is after the first time the connection is retrieved. Because connections are pooled, a database failure is not detected until the operation immediately following its retrieval from the pool, which is the first time communication to the database is attempted. It is only when a failure is detected that the connection is marked stale. The stale connection exception occurs less often if each method that accesses the database gets a new connection from the pool.
Before the application can obtain a new connection for a retry of the operation, roll back the transaction in which the original connection was involved and begin a new transaction. You can break down details on this action into the following two categories:
If an object that explicitly started a bean-managed transaction receives a stale connection exception on a database operation, close the connection and roll back the transaction. At this point, the application developer can decide to begin a new transaction, get a new connection, and retry the operation.
//get a userTransaction
javax.transaction.UserTransaction tran = getSessionContext().getUserTransaction();
//retry indicates whether to retry or not
//numOfRetries states how many retries have
// been attempted
boolean retry = false;
int numOfRetries = 0;
java.sql.Connection conn = null;
java.sql.Statement stmt = null;
do {
try {
//begin a transaction
tran.begin();
//Assumes that a datasource has already been obtained
//from JNDI
conn = ds.getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.execute("INSERT INTO EMPLOYEES VALUES
(0101, 'Bill', 'R', 'Smith')");
tran.commit();
retry = false;
} catch(java.sql.SQLException sqlX)
{
// If the error indicates the connection is stale, then
// rollback and retry the action
if (com.ibm.websphere.rsadapter.WSCallHelper
.getDataStoreHelper(ds)
.isConnectionError(sqlX))
{
try {
tran.rollback();
} catch (java.lang.Exception e) {
//deal with exception
//in most cases, this can be ignored
}
if (numOfRetries < 2) {
retry = true;
numOfRetries++;
} else {
retry = false;
}
}
else
{
//deal with other database exception
retry = false
}
} finally {
//always cleanup JDBC resources
try {
if(stmt != null) stmt.close();
} catch (java.sql.SQLException sqle) {
//usually can ignore
}
try {
if(conn != null) conn.close();
} catch (java.sql.SQLException sqle) {
//usually can ignore
}
}
} while (retry) ;
public class MyEJBClient
{
//... other methods here ...
public void myEJBClientMethod()
{
MyEJB myEJB = myEJBHome.findByPrimaryKey("myEJB");
boolean retry = false;
do
{
try
{
retry = false;
myEJB.insertValue();
}
catch(RetryableConnectionException retryable)
{
retry = true;
}
catch(Exception e) { /* handle some other problem */ }
}
while (retry);
}
} //end MyEJBClient
public class MyEJB implements javax.ejb.EntityBean
{
//... other methods here ...
public void insertValue() throws RetryableConnectionException,
java.rmi.EJBException
{
try
{
conn = ds.getConnection();
stmt = conn.createStatement();
stmt.execute("INSERT INTO my_table VALUES (1)");
}
catch(java.sql.SQLException sqlX)
{
// Find out if the error indicates the connection is stale
if (com.ibm.websphere.rsadapter.WSCallHelper
.getDataStoreHelper(ds)
.isConnectionError(sqlX))
{
getSessionContext().setRollbackOnly();
throw new RetryableConnectionException();
}
else
{
//handle other database problem
}
}
finally
{
//always cleanup JDBC resources
try
{
if(stmt != null) stmt.close();
}
catch (java.sql.SQLException sqle)
{
//usually can ignore
}
try
{
if(conn != null) conn.close();
}
catch (java.sql.SQLException sqle)
{
//usually can ignore
}
}
}
} //end MyEJB
MyEJBClient first gets a MyEJB bean from the home interface, assumed to have been previously retrieved from the Java Naming and Directory Interface (JNDI). It then calls insertValue() on the bean. The method on the bean gets a connection and tries to insert a value into a table. If one of the methods fails with a stale connection exception, it marks the transaction for rollbackOnly (which forces the caller to roll back this transaction) and creates a new retryable connection exception, cleaning up the resources before the exception is thrown. The retryable connection exception is simply an application-defined exception that tells the caller to retry the method. The caller monitors the retryable connection exception and, if it is caught, retries the method. In this example, because the container is beginning and ending the transaction; no transaction management is needed in the client or the server. Of course, the client could start a bean-managed transaction and the behavior would still be the same, provided that the client also committed or rolled back the transaction.
public class MySessionBean extends javax.ejb.SessionBean
{
... other methods here ...
public void mySessionBMTMethod() throws
java.rmi.EJBException
{
javax.transaction.UserTransaction tran =
getSessionContext().getUserTransaction();
boolean retry = false;
do
{
try
{
retry = false;
tran.begin();
// causes ejbLoad() to be invoked
myBMPBean.myMethod();
// causes ejbStore() to be invoked
tran.commit();
}
catch(java.rmi.EJBException re)
{
try
{
tran.rollback();
}
catch(Exception e)
{
//can ignore
}
if (causedByStaleConnection(re))
retry = true;
else
throw re;
}
catch(Exception e)
{
// handle some other problem
}
finally
{
//always cleanup JDBC resources
try
{
if(stmt != null) stmt.close();
}
catch (java.sql.SQLException sqle)
{
//usually can ignore
}
try
{
if(conn != null) conn.close();
}
catch (java.sql.SQLException sqle)
{
//usually can ignore
}
}
}
while (retry);
}
public boolean causedByStaleConnection(java.rmi.EJBException re)
{
// Search the exception chain for errors
// indicating a stale connection
for (Throwable t = re; t != null; t = t.getCause())
if (t instanceof RetryableConnectionException)
return true;
// Not found to be stale
return false;
}
}
public class MyEntityBean extends javax.ejb.EntityBean
{
... other methods here ...
public void ejbStore() throws java.rmi.EJBException
{
try
{
conn = ds.getConnection();
stmt = conn.createStatement();
stmt.execute("UPDATE my_table SET value=1 WHERE
primaryKey=" + myPrimaryKey);
}
catch(java.sql.SQLException sqlX)
{
// Find out if the error indicates the connection is stale
if (com.ibm.websphere.rsadapter.WSCallHelper
.getDataStoreHelper(ds)
.isConnectionError(sqlX))
{
// rollback the tran when method returns
getEntityContext().setRollbackOnly();
throw new java.rmi.EJBException(
"Exception occurred in ejbStore",
new RetryableConnectionException(sqlX));
}
else
{
// handle some other problem
}
}
finally
{
//always cleanup JDBC resources
try
{
if(stmt != null) stmt.close();
}
catch (java.sql.SQLException sqle)
{
//usually can ignore
}
try
{
if(conn != null) conn.close();
}
catch (java.sql.SQLException sqle)
{
//usually can ignore
}
}
}
}
When the local transaction occurs in an enterprise bean running in an unspecified transaction context, the enterprise bean client object, outside of the local transaction containment, could use the method described in the previous bullet to retry the transaction. However, when the local transaction containment takes place as part of a servlet or JSP file, there is no client object available to retry the operation. For this reason, it is recommended to avoid database operations in servlets and JSP files unless they are a part of a user transaction.
You might must set a loopback to access DB2® databases from the application server on a Linux platform.
'71' -SQLCC_ERR_CONN_CLOSED_BY_PARTNER and SQLCODE -XXXX
db2 catalog TCPIP node RHOST remote LHOST server 50000 db2 uncatalog db WAS db2 catalog db WAS as WASAlias at node loop authentication server //If you connect to WASAlias, it is connect through loopback; //If you connect to WAS, it is "normal" connect. db2 catalog db WASAlias as WAS at node RHOST
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.
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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.
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());
}
}
}
The following code sample demonstrates how to roll back transactions and issue exceptions to the bean client in cases of stale connection exceptions.
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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.util.*;
import java.sql.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;
import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException;
import com.ibm.websphere.rsadapter.WSCallHelper;
/*************************************************************************************
* This bean is designed to demonstrate Database Connections in a
* Container Managed Transaction Session Bean. Its transaction attribute *
* should be set to TX_REQUIRED or TX_REQUIRES_NEW. *
**************************************************************************************
*/
public class ShowEmployeesCMTBean implements SessionBean {
private javax.ejb.SessionContext mySessionCtx = null;
final static long serialVersionUID = 3206093459760846163L;
private javax.sql.DataSource ds;
//************************************************************************************
//* ejbActivate calls the getDS method, which does the JNDI lookup for the DataSource.
//* Because the DataSource lookup is in a separate method, we can also invoke it from
//* the getEmployees method in the case where the DataSource field is null.
//************************************************************************************
public void ejbActivate() throws java.rmi.EJBException {
getDS();
}
/**
* ejbCreate method
* @exception javax.ejb.CreateException
* @exception java.rmi.EJBException
*/
public void ejbCreate() throws javax.ejb.CreateException, java.rmi.EJBException {}
/**
* ejbPassivate method
* @exception java.rmi.EJBException
*/
public void ejbPassivate() throws java.rmi.EJBException {}
/**
* ejbRemove method
* @exception java.rmi.EJBException
*/
public void ejbRemove() throws java.rmi.EJBException {}
//************************************************************************************
//* The getEmployees method runs the database query to retrieve the employees.
//* The getDS method is only called if the DataSource variable is null.
//* Because this session bean uses Container Managed Transactions, it cannot retry the
//* transaction on a StaleConnectionException. However, it can throw an exception to
//* its client indicating that the operation is retriable.
//************************************************************************************
public Vector getEmployees() throws ConnectionWaitTimeoutException, SQLException,
RetryableConnectionException
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Vector employeeList = new Vector();
if (ds == null) getDS();
try
{
// 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));
}
}
catch (SQLException sqlX)
{
// Determine if the connection is stale
if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX))
{
// This exception indicates that the connection to the database is no longer valid.
// Roll back the transaction, and throw an exception to the client indicating they
// can retry the transaction if desired.
System.out.println("Connection is stale:" + sqlX.getMessage());
System.out.println("Rolling back transaction and throwing RetryableConnectionException");
mySessionCtx.setRollbackOnly();
throw new RetryableConnectionException(sqlX.toString());
}
// Determine if the connection request timed out.
else 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:" +
sqlX.getMessage());
throw sqlX instanceof ConnectionWaitTimeoutException ?
sqlX :
(ConnectionWaitTimeoutException) sqlX.getCause();
}
else
{
//Throwing a remote exception will automatically roll back the container managed
//transaction
System.out.println("SQL Exception during get connection or process SQL:" +
sqlX.getMessage());
throw sqlX;
}
}
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());
}
}
}
return employeeList;
}
/**
* getSessionContext method
* @return javax.ejb.SessionContext
*/
public javax.ejb.SessionContext getSessionContext() {
return mySessionCtx;
}
//************************************************************************************
//* The getDS method performs the JNDI lookup for the data source.
//* This method is called from ejbActivate, and from getEmployees if the data source
//* object 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");
}
catch (Exception e) {
System.out.println("Naming service exception:" + e.getMessage());
e.printStackTrace();
}
}
/**
* setSessionContext method
* @param ctx javax.ejb.SessionContext
* @exception java.rmi.EJBException
*/
public void setSessionContext(javax.ejb.SessionContext ctx) throws java.rmi.EJBException {
mySessionCtx = ctx;
}
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* This is a Home interface for the Session Bean
*/
public interface ShowEmployeesCMTHome extends javax.ejb.EJBHome {
/**
* create method for a session bean
* @return WebSphereSamples.ConnPool.ShowEmployeesCMT
* @exception javax.ejb.CreateException
* @exception java.rmi.RemoteException
*/
WebSphereSamples.ConnPool.ShowEmployeesCMT create() throws javax.ejb.CreateException,
java.rmi.RemoteException;
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* This is an Enterprise Java Bean Remote Interface
*/
public interface ShowEmployeesCMT extends javax.ejb.EJBObject {
/**
*
* @return java.util.Vector
*/
java.util.Vector getEmployees() throws java.sql.SQLException, java.rmi.RemoteException,
ConnectionWaitTimeoutException, WebSphereSamples.ConnPool.RetryableConnectionException;
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* Exception indicating that the operation can be retried
* Creation date: (4/2/2001 10:48:08 AM)
* @author: Administrator
*/
public class RetryableConnectionException extends Exception {
/**
* RetryableConnectionException constructor.
*/
public RetryableConnectionException() {
super();
}
/**
* RetryableConnectionException constructor.
* @param s java.lang.String
*/
public RetryableConnectionException(String s) {
super(s);
}
}
The following code sample demonstrates your options for addressing stale connection exceptions. You can set different transaction management and connection management parameters, such as the number of operation retries, and the connection timeout interval.
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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.util.*;
import java.sql.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;
import javax.transaction.*;
import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException;
import com.ibm.websphere.rsadapter.WSCallHelper;
/**********************************************************************************
* This bean is designed to demonstrate Database Connections in a *
* Bean-Managed Transaction Session Bean. Its transaction attribute *
* should be set to TX_BEANMANAGED.
**********************************************************************************/
public class ShowEmployeesBMTBean implements SessionBean {
private javax.ejb.SessionContext mySessionCtx = null;
final static long serialVersionUID = 3206093459760846163L;
private javax.sql.DataSource ds;
private javax.transaction.UserTransaction userTran;
//************************************************************************************
//* ejbActivate calls the getDS method, which makes the JNDI lookup for the DataSource
//* Because the DataSource lookup is in a separate method, we can also invoke it from
//* the getEmployees method in the case where the DataSource field is null.
//************************************************************************************
public void ejbActivate() throws java.rmi.EJBException {
getDS();
}
/**
* ejbCreate method
* @exception javax.ejb.CreateException
* @exception java.rmi.EJBException
*/
public void ejbCreate() throws javax.ejb.CreateException, java.rmi.EJBException {}
/**
* ejbPassivate method
* @exception java.rmi.EJBException
*/
public void ejbPassivate() throws java.rmi.EJBException {}
/**
* ejbRemove method
* @exception java.rmi.EJBException
*/
public void ejbRemove() throws java.rmi.EJBException {}
//************************************************************************************
//* The getEmployees method runs the database query to retrieve the employees.
//* The getDS method is only called if the DataSource or userTran variables are null.
//* If a stale connection occurs, the bean retries the transaction 5 times,
//* then throws an EJBException.
//************************************************************************************
public Vector getEmployees() throws EJBException {
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
int retryCount = 5;
// If the Database code processes successfully, we will set error = false
boolean error = true;
if (ds == null || userTran == null) getDS();
do
{
try
{
//try/catch block for UserTransaction work
//Begin the transaction
userTran.begin();
try
{
//try/catch block for database work
//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, as all database operations are successfully completed
error = false;
}
catch (SQLException sqlX)
{
if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX))
{
// This exception indicates that the connection to the database is no longer valid.
// Rollback the transaction, and throw an exception to the client indicating they
// can retry the transaction if desired.
System.out.println("Stale connection:" +
se.getMessage());
userTran.rollback();
if (--retryCount == 0)
{
//If we have already retried the requested number of times, throw an EJBException.
throw new EJBException("Transaction Failure:" + sqlX.toString());
}
else
{
System.out.println("Retrying transaction, retryCount =" +
retryCount);
}
}
else 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 request timed out:" +
sqlX.getMessage());
userTran.rollback();
throw new EJBException("Transaction failure:" + sqlX.getMessage());
}
else
{
// This catch handles all other SQL Exceptions
System.out.println("SQL Exception during get connection or process SQL:" +
sqlX.getMessage());
userTran.rollback();
throw new EJBException("Transaction failure:" + sqlX.getMessage());
}
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());
}
}
}
if (!error) {
//Database work completed successfully, commit the transaction
userTran.commit();
}
//Catch UserTransaction exceptions
}
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());
throw new EJBException("Transaction failure:" + nse.getMessage());
}
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());
throw new EJBException("Transaction failure:" + re.getMessage());
}
catch (SystemException se) {
//Thrown if the transaction manager encounters an unexpected error condition
System.out.println("SystemException in User Transaction:" + se.getMessage());
throw new EJBException("Transaction failure:" + se.getMessage());
}
catch (Exception e) {
//Handle any generic or unexpected Exceptions
System.out.println("Exception in User Transaction:" + e.getMessage());
throw new EJBException("Transaction failure:" + e.getMessage());
}
}
while (error);
return employeeList;
}
/**
* getSessionContext method comment
* @return javax.ejb.SessionContext
*/
public javax.ejb.SessionContext getSessionContext() {
return mySessionCtx;
}
//************************************************************************************
//* The getDS method performs the JNDI lookup for the DataSource.
//* This method is called from ejbActivate, and from getEmployees if the DataSource
//* object 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");
//Create the UserTransaction object
userTran = mySessionCtx.getUserTransaction();
}
catch (Exception e) {
System.out.println("Naming service exception:" + e.getMessage());
e.printStackTrace();
}
}
/**
* setSessionContext method
* @param ctx javax.ejb.SessionContext
* @exception java.rmi.EJBException
*/
public void setSessionContext(javax.ejb.SessionContext ctx) throws java.rmi.EJBException {
mySessionCtx = ctx;
}
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* This is a Home interface for the Session Bean
*/
public interface ShowEmployeesBMTHome extends javax.ejb.EJBHome {
/**
* create method for a session bean
* @return WebSphereSamples.ConnPool.ShowEmployeesBMT
* @exception javax.ejb.CreateException
* @exception java.rmi.RemoteException
*/
WebSphereSamples.ConnPool.ShowEmployeesBMT create() throws javax.ejb.CreateException,
java.rmi.RemoteException;
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* This is an Enterprise Java Bean Remote Interface
*/
public interface ShowEmployeesBMT extends javax.ejb.EJBObject {
/**
*
* @return java.util.Vector
*/
java.util.Vector getEmployees() throws java.rmi.RemoteException, javax.ejb.EJBException;
}
The following code sample demonstrates how to roll back transactions and issue exceptions to the bean client in cases of stale connection exceptions.
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2005,2008
// 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.util.*;
import javax.ejb.*;
import java.sql.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;
import com.ibm.websphere.rsadapter.WSCallHelper;
/**
* This is an Entity Bean class with five BMP fields
* String firstName, String lastName, String middleInit
* String empNo, int edLevel
*/
public class EmployeeBMPBean implements EntityBean {
private javax.ejb.EntityContext entityContext = null;
final static long serialVersionUID = 3206093459760846163L;
private java.lang.String firstName;
private java.lang.String lastName;
private String middleInit;
private javax.sql.DataSource ds;
private java.lang.String empNo;
private int edLevel;
/**
* ejbActivate method
* ejbActivate calls getDS(), which performs the
* JNDI lookup for the datasource.
*/
public void ejbActivate() {
getDS();
}
/**
* ejbCreate method for a BMP entity bean
* @return WebSphereSamples.ConnPool.EmployeeBMPKey
* @param key WebSphereSamples.ConnPool.EmployeeBMPKey
* @exception javax.ejb.CreateException
*/
public WebSphereSamples.ConnPool.EmployeeBMPKey ejbCreate(String empNo,
String firstName, String lastName, String middleInit, int edLevel) throws
javax.ejb.CreateException {
Connection conn = null;
PreparedStatement ps = null;
if (ds == null) getDS();
this.empNo = empNo;
this.firstName = firstName;
this.lastName = lastName;
this.middleInit = middleInit;
this.edLevel = edLevel;
String sql = "insert into Employee (empNo, firstnme, midinit, lastname,
edlevel) values (?,?,?,?,?)";
try {
conn = ds.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, empNo);
ps.setString(2, firstName);
ps.setString(3, middleInit);
ps.setString(4, lastName);
ps.setInt(5, edLevel);
if (ps.executeUpdate() != 1){
System.out.println("ejbCreate Failed to add user.");
throw new CreateException("Failed to add user.");
}
}
catch (SQLException se)
{
if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se))
{
// This exception indicates that the connection to the database is no longer valid.
// Rollback the transaction, and throw an exception to the client indicating they
// can retry the transaction if desired.
System.out.println("Connection is stale:" + se.getMessage());
throw new CreateException(se.getMessage());
}
else
{
System.out.println("SQL Exception during get connection or process SQL:" +
se.getMessage());
throw new CreateException(se.getMessage());
}
}
finally
{
// Always close the connection in a finally statement to ensure proper
// closure in all cases. Closing the connection does not close an
// actual connection, but releases it back to the pool for reuse.
if (ps != null)
{
try
{
ps.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());
}
}
}
return new EmployeeBMPKey(this.empNo);
}
/**
* ejbFindByPrimaryKey method
* @return WebSphereSamples.ConnPool.EmployeeBMPKey
* @param primaryKey WebSphereSamples.ConnPool.EmployeeBMPKey
* @exception javax.ejb.FinderException
*/
public WebSphereSamples.ConnPool.EmployeeBMPKey
ejbFindByPrimaryKey(WebSphereSamples.ConnPool.EmployeeBMPKey primaryKey)
javax.ejb.FinderException {
loadByEmpNo(primaryKey.empNo);
return primaryKey;
}
/**
* ejbLoad method
*/
public void ejbLoad() {
try {
EmployeeBMPKey pk = (EmployeeBMPKey) entityContext.getPrimaryKey();
loadByEmpNo(pk.empNo);
} catch (FinderException fe) {
throw new EJBException("Cannot load Employee state from database.");
}
}
/**
* ejbPassivate method
*/
public void ejbPassivate() {}
/**
* ejbPostCreate method for a BMP entity bean
* @param key WebSphereSamples.ConnPool.EmployeeBMPKey
*/
public void ejbPostCreate(String empNo, String firstName, String lastName, String middleInit,
int edLevel) {}
/**
* ejbRemove method
* @exception javax.ejb.RemoveException
*/
public void ejbRemove() throws javax.ejb.RemoveException
{
if (ds == null)
GetDS();
String sql = "delete from Employee where empNo=?";
Connection con = null;
PreparedStatement ps = null;
try
{
con = ds.getConnection();
ps = con.prepareStatement(sql);
ps.setString(1, empNo);
if (ps.executeUpdate() != 1)
{
throw new EJBException("Cannot remove employee:" + empNo);
}
}
catch (SQLException se)
{
if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se))
{
// This exception indicates that the connection to the database is no longer valid.
// Rollback the transaction, and throw an exception to the client indicating they
// can retry the transaction if desired.
System.out.println("Connection is stale:" + se.getMessage());
throw new EJBException(se.getMessage());
}
else
{
System.out.println("SQL Exception during get connection or process SQL:" +
se.getMessage());
throw new EJBException(se.getMessage());
}
}
finally
{
// Always close the connection in a finally statement to ensure proper
// closure in all cases. Closing the connection does not close an
// actual connection, but releases it back to the pool for reuse.
if (ps != null)
{
try
{
ps.close();
}
catch (Exception e)
{
System.out.println("Close Statement Exception:" + e.getMessage());
}
}
if (con != null)
{
try
{
con.close();
}
catch (Exception e)
{
System.out.println("Close connection exception:" + e.getMessage());
}
}
}
}
/**
* Get the employee's edLevel
* Creation date: (4/20/2001 3:46:22 PM)
* @return int
*/
public int getEdLevel() {
return edLevel;
}
/**
* getEntityContext method
* @return javax.ejb.EntityContext
*/
public javax.ejb.EntityContext getEntityContext() {
return entityContext;
}
/**
* Get the employee's first name
* Creation date: (4/19/2001 1:34:47 PM)
* @return java.lang.String
*/
public java.lang.String getFirstName() {
return firstName;
}
/**
* Get the employee's last name
* Creation date: (4/19/2001 1:35:41 PM)
* @return java.lang.String
*/
public java.lang.String getLastName() {
return lastName;
}
/**
* get the employee's middle initial
* Creation date: (4/19/2001 1:36:15 PM)
* @return char
*/
public String getMiddleInit() {
return middleInit;
}
/**
* Lookup the DataSource from JNDI
* Creation date: (4/19/2001 3:28:15 PM)
*/
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");
}
catch (Exception e) {
System.out.println("Naming service exception:" + e.getMessage());
e.printStackTrace();
}
}
/**
* Load the employee from the database
* Creation date: (4/19/2001 3:44:07 PM)
* @param empNo java.lang.String
*/
private void loadByEmpNo(String empNoKey) throws javax.ejb.FinderException
{
String sql = "select empno, firstnme, midinit, lastname, edLevel from employee where empno = ?";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
if (ds == null) getDS();
try
{
// Get a Connection object conn using the DataSource factory.
conn = ds.getConnection();
// Run DB query using standard JDBC coding.
ps = conn.prepareStatement(sql);
ps.setString(1, empNoKey);
rs = ps.executeQuery();
if (rs.next())
{
empNo= rs.getString(1);
firstName=rs.getString(2);
middleInit=rs.getString(3);
lastName=rs.getString(4);
edLevel=rs.getInt(5);
}
else
{
throw new ObjectNotFoundException("Cannot find employee number" +
empNoKey);
}
}
catch (SQLException se)
{
if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se))
{
// This exception indicates that the connection to the database is no longer valid.
// Roll back the transaction, and throw an exception to the client indicating they
// can retry the transaction if desired.
System.out.println("Connection is stale:" + se.getMessage());
throw new FinderException(se.getMessage());
}
else
{
System.out.println("SQL Exception during get connection or process SQL:" +
se.getMessage());
throw new FinderException(se.getMessage());
}
}
finally
{
// Always close the connection in a finally statement to ensure
// proper closure in all cases. Closing the connection does not
// close an 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 (ps != null)
{
try
{
ps.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());
}
}
}
}
/**
* set the employee's education level
* Creation date: (4/20/2001 3:46:22 PM)
* @param newEdLevel int
*/
public void setEdLevel(int newEdLevel) {
edLevel = newEdLevel;
}
/**
* setEntityContext method
* @param ctx javax.ejb.EntityContext
*/
public void setEntityContext(javax.ejb.EntityContext ctx) {
entityContext = ctx;
}
/**
* set the employee's first name
* Creation date: (4/19/2001 1:34:47 PM)
* @param newFirstName java.lang.String
*/
public void setFirstName(java.lang.String newFirstName) {
firstName = newFirstName;
}
/**
* set the employee's last name
* Creation date: (4/19/2001 1:35:41 PM)
* @param newLastName java.lang.String
*/
public void setLastName(java.lang.String newLastName) {
lastName = newLastName;
}
/**
* set the employee's middle initial
* Creation date: (4/19/2001 1:36:15 PM)
* @param newMiddleInit char
*/
public void setMiddleInit(String newMiddleInit) {
middleInit = newMiddleInit;
}
/**
* unsetEntityContext method
*/
public void unsetEntityContext() {
entityContext = null;
}
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* This is an Enterprise Java Bean Remote Interface
*/
public interface EmployeeBMP extends javax.ejb.EJBObject {
/**
*
* @return int
*/
int getEdLevel() throws java.rmi.RemoteException;
/**
*
* @return java.lang.String
*/
java.lang.String getFirstName() throws java.rmi.RemoteException;
/**
*
* @return java.lang.String
*/
java.lang.String getLastName() throws java.rmi.RemoteException;
/**
*
* @return java.lang.String
*/
java.lang.String getMiddleInit() throws java.rmi.RemoteException;
/**
*
* @return void
* @param newEdLevel int
*/
void setEdLevel(int newEdLevel) throws java.rmi.RemoteException;
/**
*
* @return void
* @param newFirstName java.lang.String
*/
void setFirstName(java.lang.String newFirstName) throws java.rmi.RemoteException;
/**
*
* @return void
* @param newLastName java.lang.String
*/
void setLastName(java.lang.String newLastName) throws java.rmi.RemoteException;
/**
*
* @return void
* @param newMiddleInit java.lang.String
*/
void setMiddleInit(java.lang.String newMiddleInit) throws java.rmi.RemoteException;
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* This is an Enterprise Java Bean Remote Interface
*/
public interface EmployeeBMP extends javax.ejb.EJBObject {
/**
*
* @return int
*/
int getEdLevel() throws java.rmi.RemoteException;
/**
*
* @return java.lang.String
*/
java.lang.String getFirstName() throws java.rmi.RemoteException;
/**
*
* @return java.lang.String
*/
java.lang.String getLastName() throws java.rmi.RemoteException;
/**
*
* @return java.lang.String
*/
java.lang.String getMiddleInit() throws java.rmi.RemoteException;
/**
*
* @return void
* @param newEdLevel int
*/
void setEdLevel(int newEdLevel) throws java.rmi.RemoteException;
/**
*
* @return void
* @param newFirstName java.lang.String
*/
void setFirstName(java.lang.String newFirstName) throws java.rmi.RemoteException;
/**
*
* @return void
* @param newLastName java.lang.String
*/
void setLastName(java.lang.String newLastName) throws java.rmi.RemoteException;
/**
*
* @return void
* @param newMiddleInit java.lang.String
*/
void setMiddleInit(java.lang.String newMiddleInit) throws java.rmi.RemoteException;
}
//===================START_PROLOG======================================
//
// 5630-A23, 5630-A22,
// (C) COPYRIGHT International Business Machines Corp. 2002,2008
// 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;
/**
* This is a Primary Key Class for the Entity Bean
**/
public class EmployeeBMPKey implements java.io.Serializable {
public String empNo;
final static long serialVersionUID = 3206093459760846163L;
/**
* EmployeeBMPKey() constructor
*/
public EmployeeBMPKey() {
}
/**
* EmployeeBMPKey(String key) constructor
*/
public EmployeeBMPKey(String key) {
empNo = key;
}
/**
* equals method
* - user must provide a proper implementation for the equal method. The generated
* method assumes the key is a String object.
*/
public boolean equals (Object o) {
if (o instanceof EmployeeBMPKey)
return empNo.equals(((EmployeeBMPKey)o).empNo);
else
return false;
}
/**
* hashCode method
* - user must provide a proper implementation for the hashCode method. The generated
* method assumes the key is a String object.
*/
public int hashCode () {
return empNo.hashCode();
This code sample demonstrates how you specify the conditions under which the application server issues the ConnectionWaitTimeoutException for a JDBC application.
In all cases in which the ConnectionWaitTimeoutException is caught, there is little that can be done to recover.
public void test1() {
java.sql.Connection conn = null;
java.sql.Statement stmt = null;
java.sql.ResultSet rs = null;
try {
// Look for datasource
java.util.Properties props = new java.util.Properties();
props.put(
javax.naming.Context.INITIAL_CONTEXT_FACTORY,
com.ibm.websphere.naming.WsnInitialContextFactory);
ic = new javax.naming.InitialContext(props);
javax.sql.DataSource ds1 = (javax.sql.DataSource) ic.lookup(jndiString);
// Get Connection.
conn = ds1.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from mytable where this = 54");
}
catch (java.sql.SQLException sqlX) {
if (sqlX instanceof com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException
|| sqlX instanceof java.sql.SQLTransientConnectionException
&& sqlX.getCause() instanceof com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException)
{
//notify the user that the system could not provide a
//connection to the database. This usually happens when the
//connection pool is full and there is no connection
//available for to share.
}
else
{
// handle other database problems.
}
}
finally {
if (rs != null)
try {
rs.close();
}
catch (java.sql.SQLException sqle1) {
}
if (stmt != null)
try {
stmt.close();
}
catch (java.sql.SQLException sqle1) {
}
if (conn != null)
try {
conn.close();
}
catch (java.sql.SQLException sqle1) {
}
}
}
This code sample demonstrates how you specify the conditions under which WebSphere Application Server issues the ConnectionWaitTimeout exception for a JCA application.
In all cases in which the ConnectionWaitTimeout exception is caught, there is little to do for recovery.
The following code fragment shows how to use this exception in Java Platform, Enterprise Edition (Java EE) Connector Architecture (JCA):
/**
* This method does a simple Connection test.
*/
public void testConnection()
throws javax.naming.NamingException, javax.resource.ResourceException,
com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException {
javax.resource.cci.ConnectionFactory factory = null;
javax.resource.cci.Connection conn = null;
javax.resource.cci.ConnectionMetaData metaData = null;
try {
// lookup the connection factory
if (verbose) System.out.println("Look up the connection factory...");
try {
factory =
(javax.resource.cci.ConnectionFactory) (new InitialContext()).lookup("java:comp/env/eis/Sample");
}
catch (javax.naming.NamingException ne) {
// Connection factory cannot be looked up.
throw ne;
}
// Get connection
if (verbose) System.out.println("Get the connection...");
conn = factory.getConnection();
// Get ConnectionMetaData
metaData = conn.getMetaData();
// Print out the metadata Information.
System.out.println("EISProductName" is + metaData.getEISProductName());
}
catch (com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException cwtoe) {
// Connection Wait Timeout
throw cwtoe;
}
catch (javax.resource.ResourceException re) {
// Something wrong with connections.
throw re;
}
finally {
if (conn != null) {
try {
conn.close();
}
catch (javax.resource.ResourceException re) {
}
}
}
}
The application server provides a DataStoreHelper interface for mapping different database SQL error codes to the appropriate exceptions in the application server.
Error mapping is necessary because various database vendors can provide different SQL errors and codes that represent that same issue. For example, the stale connection exception has different codes in different databases. The DB2 SQLCODEs of 1015, 1034, 1036 , and so on indicate that the connection is no longer available because of a temporary database problem. The Oracle SQLCODEs of 28, 3113, 3114, and so on, indicate the same situation.
Mapping these error codes to standard exceptions provides the consistency that makes applications portable across different installations of the application server. The following code segment illustrates how to add two error codes into the error map:public class NewDSHelper extends GenericDataStoreHelper
{
public NewDSHelper(java.util.Properties dataStoreHelperProperties)
{
super(dataStoreHelperProperties);
java.util.Hashtable myErrorMap = null;
myErrorMap = new java.util.Hashtable();
myErrorMap.put(new Integer(-803), myDuplicateKeyException.class);
myErrorMap.put(new Integer(-1015), myStaleConnectionException.class);
myErrorMap.put("S1000", MyTableNotFoundException.class);
setUserDefinedMap(myErrorMap);
...
}
}
A configuration option known as the Error Detection Model controls how the error map is used. At V6 and earlier, Exception Mapping was the only option available for the Error Detection Model. At V7 and later, another option called Exception Checking is also available. Under the Exception Mapping model, the application server consults the error map and replaces exceptions with the corresponding exception type listed in the error map. Under the Exception Checking model, the application server still consults the error map for its own purposes but does not replace exceptions. If you want to continue to use Exception Mapping, you do not need to change anything. Exception Mapping is the default Error Detection Model. If you want to use the Exception Checking Model, see the topic "Changing the Error Detection Model to use the Exception Checking Model" in the related links.
Repetition of certain SQL error messages indicates problems, such as database referential integrity violations, that you can prevent by using the container managed persistence (CMP) sequence grouping feature.
Exceptions resulting from foreign key conflicts due to violations of database referential integrityA database referential integrity (RI) policy prescribes rules for how data is written to and deleted from the database tables to maintain relational consistency. Runtime requirements for managing bean persistence, however, can cause an enterprise JavaBeans (EJB) application to violate RI rules, which can cause database exceptions.
The insert or update value of the FOREIGN KEY table1.name_of_foreign_key_constraint
is not equal to any value of the parent key of the parent table.
orA parent row cannot be deleted because the relationship table1.name_of_foreign_key_constraint
is not equal to any value of the parent key of the parent table.
To prevent these exceptions, you must designate the order in which entity beans update relational database tables by defining sequence groups for the beans.
Exceptions resulting from deadlock caused by optimistic concurrency control schemesAdditionally, sequence grouping can minimize transaction rollback exceptions for entity beans that are configured for optimistic concurrency control. Optimistic concurrency control dictates that database locks be held for minimal amounts of time, so that a maximum number of transactions consistently have access to the data. In such a highly available database, concurrent transactions can attempt to lock the same table row and create deadlock. The resulting exceptions can generate messages similar to the following (which was produced in an environment running DB2):
Unsuccessful execution caused by deadlock or timeout.
Use the sequence grouping feature to order bean persistence so that database deadlock is less likely to occur.