WebSphere Studio v5 & Connection Pooling

[Tania Johnson]

WebSphere Studio v5 allows an application to be bound to a specific datasource. This allows for the use of connection pooling and is supposed to save the overhead of making a connection to the database since you draw your connections from a pool. I've attempted to make use of this, but can't find any good examples to verify that I'm doing this correctly. I haven't seen any performance improvements using the connection pooling. Here's what I've done.
1. Opened the server configuration file and added the datasource information to the file. This part works correctly.
2. Created a pool.java class:
package xxxx1;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
/**
* This class returns a connection from a JNDI data source.
*
* @author: xxxx Information Technology Division
* @author Tania Johnson
* @version: 1.0 09/15/2003
*/
public class Pool
{
private static Connection poolConnection;
/**
* Method Pool create a new connection pool
*/
public Pool( )
{
// for testing purposes only
// System.err.println( "DBUtil instance created." );
}
/**
* Method enableConnection
* @return Connection New connection from the pool
*/
public static Connection enableConnection( )
{
try
{
Context ctx = ( Context ) new InitialContext( );
poolConnection = ( ( DataSource ) ctx.lookup( "jdbc/xxxx2" ) ).getConnection( );
// for testing purposes only
System.err.println( "DBUtil instance created." );
}
catch( Exception e )
{
e.printStackTrace( System.err );
}
return poolConnection;
}

/**
* Method closeConnection
* Closes the Connection releasing it back tot he pool
*/
public static void releaseConnection( ) throws java.sql.SQLException
{
try
{
if( poolConnection != null && !poolConnection.isClosed( ) )
{
if( !poolConnection.getAutoCommit( ) )
{
poolConnection.rollback( );
}
poolConnection.close( );
}
System.err.println( "DBUtil instance destroyed." );
System.err.println( );
}
catch( SQLException sqle )
{
// for testing purposes
// System.out.println( "Error connection..." );
}
finally
{
poolConnection = null;
}
}
}
3. In another class the connection pool is used like this:
public void main( )
{
try
{
dbOwner = DatabaseOwnerBean.getPidsDatabaseOwner( );
dbConnect( );
switch( getActionCode( ) )
{
case 's' : // search Account
searchAccount( );
break;
case 'a' : // insert Account
insertAccount( );
break;
case 'd' : // delete Account
deleteAccount( );
break;
case 'u' : // update Account
updateAccount( );
break;
case 'r' : // reset page values
resetValues( );
break;
default : // default
setActionCode( ' ' );
setMessage( "" );
messageString.setMessageCode(0);
break;
}
setActionCode(' ');
dbDisconnect( );
}
catch( Exception e )
{
/* set the message for Print Screen - 24 */
messageString.setMessage( 24 );
setMessage( messageString.getMessage( ) + "
" + e ); }
}
}
/**
* Open a connection to the database
*/
public void dbConnect( ) throws Exception
{
currentConnection = Pool.enableConnection( );
}
/**
* Release the connection to the database
*/
public void dbDisconnect( ) throws SQLException
{
if( currentStatement != null )
{
currentStatement.close( );
}
if( rs != null )
{
rs.close( );
}
connectionPooling.releaseConnection( );
}
So, my questions are these:
1. Is this the right way to use the connection pooling?
2. Is there a better way to use connection pooling?
3. When setting up the datasource in the server config file, do you have to enter a default username and password?

[Jaime Garcia]

Maybe this helps. This are Tips for using connection pooling from IBM:
Obtain and close connection in the same method (you can have a connection pool class, but when you call it to get the connection in a method, close it in the same method.
If you opened it, close it (ResultSet, statement, etc).
For servlets, obtain DataSource in the init() method (DataSource, not the connection).

Worst practices
Do not close connections in a finalize() method
Do not declare connections as static objects
In servlets, do not declare Connection objects as instance variables
In CMP beans, do not manage data access
the page:

4.2.4.2.1.2: Tips for using connection pooling
explain this points.
And this one explain how to use a datasorce:
4.2.4.2.1.1: Creating datasources with the WebSphere connection pooling API
Hope this helps