JDBC Connection pools in Sun Application Server

Here is a little problem which lasted 2 hours. The question is, how does one use JDBC Connection pools managed by the Sun Application Server? If you look in the administration guide, it won't tell you anything. Ask a bunch of experts on Sun forums, and they tell you nothing. This little entry should resolve your problems.

There are two main things to consider when setting up the connection pools.
1. JDBC Driver - Your DB of choice comes with a JDBC driver. Your Sun application server does not. So your first order of business is to add your JDBC driver to the Sun Application Server classpath. The surest way to do this is by going through the web admin console and adding the full path to the JDBC driver JAR file in one of the classpath boxes. Go to Application Server -> JVM Settings -> Path settings. I added the MySQL JDBC driver to Classpath suffix. Now you can set up connection pools and JDBC resource. This is the straight-forward stuff which IS documented. Then when you ping the connection pool, you should get Ping Success instead of class not found.

2. JNDI Name - If you use Netbeans, It might set up the JNDI name incorrectly which is the cause of the 2 hour search for answers. Netbeans smartly uses "java:comp/env/<JDBC Resource Name>" when what really worked was simply "<JDBC Resource Name>". Thus the code to get the datasource should be

javax.naming.Context c = new javax.naming.InitialContext();
return (javax.sql.DataSource) c.lookup("<JDBC Resource Name>");

After all that, don't forget to restart the Application Server. Now you can thank me for saving you 2 hours.