Setting up a connection pool with Tomcat

I made so many attempts to make database connection pooling work with Netbeans and Tomcat, and little did I know that the solution is just in the Netbeans help documentation. Look up the topic "Setting up a Connection Pool on the Tomcat Web Server". In any case, here are the steps:

Assuming you already have a web application project open,

1. Go to the Tomcat administration page.

• In Netbeans, go to the Runtime Window.
• Expand Servers
• Right-click on Apache Tomcat and Select Start
  
• Expand Apache Tomcat
• Expand Web Applications
• Right-click on /admin
• Select "Open in browser"
• Enter username and password.(SEE NOTE BELOW!)
  

NOTE: Ironically, this is the most difficult part in setting up connection pooling. I had to spend a lot of time trying to find out what the default username and password is for the Tomcat Admin. So follow the instructions here carefully!

• right click on the Tomcat server instance in the Runtime window.
• select Properties
• note the value in the Catalina Base field. Mine is C:\Documents and Settings\Gavin\.netbeans\5.5\apache-tomcat-5.5.17_base.
• Go to this directory, and you will see a directory in it named "conf".
• Under the conf directory, open tomcat-users.xml.
• Look for the user whose role is "admin". That's the user you are looking for. Mine has the username "ide".
  

2. Add a JDBC Database Resource.

• Once logged in, click Data Sources
• Choose Create New Data Source Actions drop down in the right.
• Type values such as the following to define your data source. My settings look like this. Change yours accordingly.
• JNDI Name: jdbc/grocerificDB
• Data Source URL: jdbc:mysql://localhost:3306/grocerific
• JDBC Driver Class: com.mysql.jdbc.Driver
• User name: root
• Password: secret
  

3. Make sure the JDBC driver is placed in the common/lib directory of Tomcat's base directory.
NOTE: The driver has to be placed there and not under your application's WEB-INF/lib directory. I really don't know why. Netbeans' bundled Tomcat server is in
<Netbeans folder>\enterprise3\apache-tomcat-5.5.17\common\lib.

4. In your application, edit WEB-INF/web.xml. Add the following lines:

<resource-ref>
<description>Grocerific DataSource</description>
<res-ref-name>jdbc/grocerificDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

5. Replace jdbc/grocerificDB with the name you registered in Tomcat.

6. In your web app's META-INF directory, open context.xml, and it should look like this:

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/DataSourceWeb">
<ResourceLink global="jdbc/grocerificDB" name="jdbc/grocerificDB" type="javax.sql.DataSource"/>
</Context>

7. Replace jdbc/grocerificDB with the name you registered in Tomcat, and replace /DataSourceWeb with your application's name.

8. To reference your DataSource from a servlet,

Context ctx = new InitialContext();
Context envCtx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup("jdbc/grocerificDB");
Connection conn = ds.getConnection();

That's it! let me know if you have any questions. I just discovered a super easy way to do all of this using Netbeans wizards. I'll post it when I have the time.

It's amazing how Netbeans has progressed. I still wonder why so many people still use Eclipse when Netbeans seems to be so much better.

Visit Active Learning if you want to attend my training courses on Java and J2EE.