Some quick notes on Db Connection pooling in Glassfish

To set up a Glassfish connection pooling and JNDI datasource, accessible in your web app, the following things need to be done (tried with Glassfish v2):

Db drivers:

Ensure the relevant JDBC driver is in the Glassfish classpath. I ensured the <glassfish_home>/lib directory contained the latest MySQL J connector. You may need to restart Glassfish (you probably will if you change the classpath).

Configuration:

Connection Pool:

Set up a connection pool in the Glassfish admin interface (under Resources\JDBC\Connection Pools). In my case for MySQL I was happy to use the defaults initially, which includes:

• Datasource classname: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
• Resource Type: javax.sql.Datasource

Give the pool a sensible name.

Once you have set this and the connection details (host, username, password etc) you can use the Ping feature to check the connectivity. If you get errors regarding classes not found, check your classpath / location of the JDBC library jars - possibly restart Glassfish if you have not already.

Once you have a successful ping, you can move on to configuring the JNDI resource:

JNDI resource:

Under the Resources\JDBC\JDBC Resources set up a JNDI resource to the pool you just configured (by name), give the JNDI resource a name of the form:

jdbc/myLovelyDbJNDIResourceName

ensure the resource is enabled!

Deployment descriptors, configuration:

sun-web.xml - Map the container JNDI to a resource reference in sun-web.xml, e.g. something like:

<!-- Map container JNDI to resource reference -->
<resource-ref>
<res-ref-name>myDbResourceName</res-ref-name>
<jndi-name>jdbc/myLovelyDbJNDIResourceName</jndi-name>
</resource-ref>

web.xml - Map the resource as a datasource reference in web.xml

<!-- set up resource reference for the web app -->
<resource-ref>
<res-ref-name>myDbResourceName</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

Application Code:

In you application code, where you get a connection, you can then use something along the lines of:

Connection connection = null;

try
{
Context env = (Context)new InitialContext().lookup("java:comp/env");

DataSource pool = (DataSource)env.lookup("myDbResourceName");

if (pool == null)
{
 throw new RuntimeException("Unknown DataSource");
}

connection = pool.getConnection();
}
catch(NamingException ne)
{
throw new RuntimeException(ne.getMessage());
}

return connection;

Of course, modify the caught, declared to throw exceptions to suit the application design...

References:

This article is a useful reference point, covering similar information in a slightly more verbose form:

http://thestewscope.wordpress.com/2008/01/08/glassfish-v2-ur1-and-mysql-connection-pool/

This forum post also details the deployment descriptor configuration well:
http://forums.java.net/jive/message.jspa?messageID=264629