- Introduction
- Running Tomcat 5
- Tomcat 5 Resources
- Installing MySQL
- Creating the Database Connection Pool
- Running Tomcat 4
- Tomcat 4 Resources
- Conclusion
This chapter is from the book
This chapter is from the book
This chapter is from the book
Creating the Database Connection Pool
Now let's introduce Tomcat to your database. We do so by creating a database connection pool that is actually a JNDI resource. JNDI is a J2EE technology that I talk more about in Chapter 21, "Administering JNDI Resources." Essentially, JNDI is a directory-like listing of resources maintained by a provider and available for lookup and access by a client. In our case, Tomcat serves as the provider, and our application (just the JSP, in this case) is the client. JNDI works by assigning resources names that the client can use to retrieve a handle to the resource itself.
When you create a JNDI-accessible database connection pool in Tomcat, you must define it in $CATALINA_HOME/conf/server.xml. As we'll see in Chapter 21, there are various places where you are allowed to define your pool. You can define it at the top level, thereby making it available to all hosts and host applications; at the virtual host level; and at the application or context level. The key determining factor here is scope: who needs your pool? Because we are just getting started, we'll keep your definition at the application level, which means we must now edit the application definition file, $CATALINA_HOME/conf/Catalina/localhost/unleashed.xml. The revised file appears in Listing 3.4.
Listing 3.4 Revised unleashed.xml
<Context path="/unleashed" docBase="unleashed" debug="0"> <Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_unleashed_" suffix=".log" timestamp="false"/> <Resource name="jdbc/unleashed" auth="Container" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/unleashed"> <parameter><name>username</name><value>root</value></parameter> <parameter><name>password</name><value></value></parameter> <parameter><name>driverClassName</name> <value>org.gjt.mm.mysql.Driver</value></parameter> <parameter><name>url</name> <value>jdbc:mysql://localhost/unleashed</value></parameter> </ResourceParams> </Context>
This code is a typical example of a JNDI resource definition. First, you need to define the resource itself. The fact that the type attribute is javax.sql.DataSource signals to Tomcat that it is a database connection pool. The name can be anything you want, but you typically use the prefix jdbc/ for connection pools. Also, it helps to use the database name as the rest of the name because it is clear at a glance what database this resource points to.
After the resource definition comes various parameters that apply to the resource. Obviously, each resource type has its own applicable parameters, some required and some not. For database pools, you need at least the four shown here: database user ID, database password, driver class name, and the JDBC URL that points to the database itself. Tomcat uses all four parameters to create connections to your database. The driver name is important because Tomcat will be looking in its classpath for that object. If you put the MySQL JDBC jar file in $CATALINA_HOME/common/lib, you're guaranteed that Tomcat will find it. Often, one of the biggest problems people have in setting up connection pools is in not having the JDBC jar file for their databases, having it but not in the right place (so it is not in Tomcat's classpath), or having the driver name wrong. Typically, the JDBC documentation specific to your database server will tell you what driver class name to use, as well as the format for the URL, another place where errors commonly occur. If you look at your URL, you'll notice that the syntax is basically host and database. You could also add a port number after the host, if you had your MySQL server running on something other than the default of 3306.
Also, remember to change the username and password parameters if you changed the MySQL defaults. You'll notice my password parameter is empty; that's because, again, the root user has no password by default. More parameters for database polls control their operation—minimum, maximum, rules for orphaned connections, and so on. You'll deal with all of them later, in Chapter 21.
Creating the JSP
We're two steps away from seeing whether everything works. First, we need to create a basic JSP page as shown in Listing 3.5. The logic of this page is to do a JNDI lookup on the connection pool and acquire a JDBC connection, which we can then use to retrieve the rows from the table. If you are not familiar with JDBC, just cut and paste away. Call this page resources.jsp and put it in the unleashed directory in your deployment directory.
Listing 3.5 resources.jsp
<html>
<%@ page language="java"
import="javax.sql.*,javax.naming.*,java.sql.*" session="false"%>
<head>
<style type="text/css">
<!--
a { text-decoration: none }
body { font-family: verdana, helvetica, sans serif; font-size: 10pt; }
-->
</style>
</head>
<body>
<center>
<h3>This is a test of a Tomcat-managed connection pool</h3>
</center>
<%
try {
String jdbcname = "jdbc/unleashed";
String cmd = "select title, url from TomcatResources order by title";
Context ctx = new InitialContext();
Context envCtx = (Context)ctx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup(jdbcname);
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(cmd);
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
out.println("<table border\"1\">");
out.println("<tr bgcolor=\"lightGrey\">");
for (int i = 1; i <= cols; i++) {
out.println("<td align=\"center\" width=\"200\"><b>" +
rsmd.getColumnName(i) + "</b></td>");
}
out.println("</tr>");
while (rs.next()) {
out.println("<tr>");
for (int i = 1; i <= cols; i++) {
out.println("<td align=\"left\">" +
rs.getString(i) + "</td>");
}
out.println("</tr>");
}
out.println("</table>");
conn.close();
conn.close();
} catch(Exception e) {
out.println("Error: " + e);
e.printStackTrace();
}
%>
</body>
</html>
One thing that is different between this JSP page and the index.jsp created earlier is that I had to add some imports to the page declaration. I imported javax.naming.* for the JNDI classes, javax.sql.* for the JNDI data source class, and java.sql.* for the JDBC classes. Another thing to note is that rather than intermingle HTML and Java code, I just created a single code block and used the println method on the out object to spit out the necessary HTML. The out object is one provided by default to JSP pages, and it stands for the ServletOutputStream object, with which the JSP writes data to the client. Sometimes, it is easier than mixing raw HTML with the Java code, although you do have to escape double-quotes, which can be a real pain.
The basic logic in this page is to retrieve a handle, which is called an InitialContext, to the JNDI naming environment. Note that we actually do two lookups: one to get the overall JNDI context, named as java:comp/env, and one to get the particular resource, which we named before as jdbc/unleashed. Remember I said that a JNDI lookup actually returns a handle to the resource. In this case, that handle is a javax.sql.DataSource object, from which we can then retrieve the connection that is wrapped inside. Then, we can do all your regular JDBC calls. You'll notice that I wrote a rather generic section of code that prints column names and headers based on the metadata returned by the result set. It is a useful way that you can use with any kind of select statement because it creates the table dynamically based on what is returned. If you want to see whether that is true, you can just replace the SQL statement I have with something that goes against one of your own tables. (You might also have to change the resource definition to point to another database, in this case.)
Testing
If you haven't already done so, you need to start or restart Tomcat. Then, you can browse to http://localhost:8080/unleashed/resources.jsp and see the results shown in Figure 3.4.
)
Figure 3.4 Testing resources.jsp.
If you don't get this page, you have a couple of things to check. First, make sure you do have resources.jsp in the right directory. Second, and most important, you could see an error such as that shown in Figure 3.5.
This error is probably the most common error with Tomcat connection pools: the driver indicated in your resource definition in server.xml cannot be found. Note that my JSP spits out any exceptions to the browser, but not all code is so helpful. Many times, you'll have to look in the log files or the Tomcat console window for this error. You'll typically see a big stack trace, but somewhere buried should be this message. Then, you'll know that the JDBC jar file for your database is in the wrong place or missing. Correct the problem, restart Tomcat, and you'll be fine.
)
Figure 3.5 Error page in resources.jsp.