You want to configure a javax.sql.DataSource for use in a servlet with the Tomcat web container.
Create a resource element in Tomcat's server.xml file and an associated resource-ref element in the web.xml deployment descriptor.
Tomcat makes it easy to set up a connection pool so that servlets and JSPs can efficiently share database connections. In web sites that have many simultaneous users, a connection pool improves efficiency by sharing existing database connections, rather than creating a new connection and tearing it down every time an application has to use the database.
Another benefit of configuring a connection pool is that you can change the database system that a servlet or JSP is using without touching the Java code, because the database resource is configured outside of the servlet or JSP.
Here are the steps for configuring a DataSource with Tomcat:
Example 21-2 shows the Resource and a ResourceParams elements in server.xml . This example describes a DataSource that connects with an Oracle 8 i database.
Example 21-2. The resource element in server.xml
<Resource name="jdbc/oracle-8i-athletes" scope= "Shareable" type="javax.sql.DataSource" auth= "Container" description="Home Oracle 8i Personal Edition"/> <ResourceParams name="jdbc/oracle-8i-athletes"> <parameter> <name>driverClassName</name> <value>oracle.jdbc.driver.OracleDriver</value> </parameter> <parameter> <name>url</name> <value>jdbc:oracle:thin:@192.168.0.2:1521:ORCL</value> </parameter> <parameter> <name>username</name> <value>scott</value> </parameter> <parameter> <name>password</name> <value>tiger</value> </parameter> </ResourceParams>
Create a Resource and ResourceParams element for each database that your application uses. Example 21-3 shows the resource-ref element associated with the Resource specified by Example 21-2.
Example 21-3. A resource-ref element specifies a DataSource in web.xml
<!-- top of web.xml file --> <resource-ref> <res-ref-name>jdbc/oracle-8i-athletes</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> <!-- rest of web.xml file -->
The JNDI path to this DataSource , which you use in a JNDI lookup (see the next recipe), is jdbc/oracle-8i-athletes .
The JDBC specification: http://java.sun.com/products/jdbc/download.html; Recipe 21.3 on using a DataSource in a servlet with Tomcat; Recipe 21.4-Recipe 21.6 on configuring and using DataSources with servlets and JSPs on WebLogic; Recipe 21.7 and Recipe 21.8 on calling stored procedures from servlets and JSPs; Recipe 21.9 on converting a java.sql.ResultSet object to a javax.servlet.jsp.jstl.sql Result ; Recipe 21.10 and Recipe 21.11 on using transactions in servlets and JSPs; Recipe 21.12 on finding out information about a ResultSet .