MySQL Cookbook

16.4 Using Tomcat to Run Web Scripts

16.4.1 Problem

You want to run Java-based programs in a web environment.

16.4.2 Solution

Write programs using JSP notation and execute them using a servlet container.

16.4.3 Discussion

As described in Recipe 16.3, Apache can be used to run Perl, PHP, and Python scripts. For Java, a different approach is needed, because Apache doesn't serve JSP pages. Instead, we'll use Tomcat, a server designed for processing Java in a web environment. Apache and Tomcat are very different servers, but there is a familial relationship—Tomcat is part of the Jakarta Project, which is overseen by the Apache Software Foundation.

This section provides an overview of JSP programming with Tomcat, but makes several assumptions:

• You have a some familiarity with the concepts underlying JavaServer Pages, such as what a servlet container is, what an application context is, and what the basic JSP scripting elements are.

• The Tomcat server has been installed so that you can execute JSP pages, and you know how to start and stop it.

• You are familiar with the Tomcat webapps directory and how a Tomcat application is structured. In particular, you understand the purpose of the WEB-INF directory and the web.xml file.

• You know what a tag library is and how to use one.

I recognize that this is a lot to assume, because the use of JSP and Tomcat in the MySQL world is not so widespread as the use of our other languages with Apache. If you're unfamiliar with JSP or need instructions for installing Tomcat, Appendix B provides the necessary background information.

Once you have Tomcat in place, you should install the following components so that you can work through the JSP examples in this book:

• The mcb sample application located in the tomcat directory of the recipes distribution.

• A MySQL JDBC driver. You may already have one installed for use with the scripts in earlier chapters, but Tomcat needs a copy, too.

• The JSP Standard Tag Library (JSTL), which contains tags for performing database activities, conditional testing, and iterative operations within JSP pages.

I'll discuss how to install these components, provide a brief overview of some of the JSTL tags, and then describe how to write the JSP equivalent of the MySQL show-tables script that was implemented in Recipe 16.3 using Perl, PHP, and Python.

16.4.4 Installing the mcb Application

Web applications for Tomcat typically are packaged as WAR (web archive) files and installed under its webapps directory, which is roughly analogous to Apache's htdocs document root directory. The recipes distribution includes a sample application named mcb that you can use for trying the JSP examples described here. Look in the distribution's tomcat directory, where you will find a file named mcb.war. Copy that file to Tomcat's webapps directory.

Here's an example installation procedure for Unix, assuming that the recipes distribution and Tomcat are located at /u/paul/recipes and /usr/local/jakarta-tomcat. The command to install mcb.war would look like this:

% cp /u/paul/recipes/tomcat/mcb.war /usr/local/jakarta-tomcat/webapps

For Windows, if the relevant directories are D:\recipes and D:\jakarta-tomcat, the command looks like this:

C:\> copy D:\recipes\tomcat\mcb.war D:\jakarta-tomcat\webapps

After copying the mcb.war file to the webapps directory, restart Tomcat. As distributed, Tomcat is configured by default to look for WAR files under webapps when it starts up and automatically unpack any that have not already been unpacked. This means that copying mcb.war to the webapps directory and restarting Tomcat should be enough to unpack the mcb application. When Tomcat finishes its startup sequence, look under webapps and you should see a new mcb directory under which are all the files contained in mcb.war. (If Tomcat doesn't unpack mcb.war automatically, see the sidebar Unpacking a WAR File Manually.) If you like, have a look around in the mcb directory at this point. It should contain several files that clients can request using a browser. There should also be a WEB-INF subdirectory, which is used for information that is private—that is, available for use by scripts in the mcb directory, but not directly accessible by clients.

Next, verify that Tomcat can serve pages from the mcb application context by requesting some of them from your browser. The following URLs request in turn a static HTML page, a servlet, and a simple JSP page:

http://tomcat.snake.net:8080/mcb/test.html

http://tomcat.snake.net:8080/mcb/servlet/SimpleServlet

http://tomcat.snake.net:8080/mcb/simple.jsp

Adjust the hostname and port number in the URLs appropriately for your installation.

Unpacking a WAR File Manually WAR files are actually ZIP-format archives that can be unpacked using jar, WinZip, or any other tool that understands ZIP files. However, when unpacking a WAR file manually, you'll need to create its top-level directory first. The following sequence of steps shows one way to do this, using the jar utility to unpack a WAR file named mcb.war that is assumed to be located in Tomcat's webapps directory. For Unix, change location to the webapps directory, then issue the following commands: % mkdir mcb % cd mcb % jar xf ../mcb.war For Windows, the commands are only slightly different: C:\> mkdir mcb C:\> cd mcb C:\> jar xf ..\mcb.war Unpacking the WAR file in the webapps directory creates a new application context, so you'll need to restart Tomcat before it notices the new application.

16.4.5 Installing the JDBC Driver

The JSP pages in the mcb application need a JDBC driver for connecting to the cookbook database. The following instructions describe how to install the MySQL Connector/J driver; the installation procedure for other drivers should be similar.

To install MySQL Connector/J for use by Tomcat applications, place a copy of it in Tomcat's directory tree. Assuming that the driver is packaged as a JAR file (as is the case for MySQL Connector/J), there are three likely places under the Tomcat root directory where you can install it, depending on how visible you want the driver to be:

• To make the driver available only to the mcb application, place it in the mcb/WEB-INF/lib directory under Tomcat's webapps directory.

• To make the driver available to all Tomcat applications but not to Tomcat itself, place it in the lib directory under the Tomcat root.

• To make the driver available both to applications and to Tomcat, place it in the common/lib directory under the Tomcat root.

I recommend installing a copy of the driver in the common/lib directory. That gives it the most global visibility (it will be accessible both by Tomcat and by applications), and you'll need to install it only once. If you enable the driver only for the mcb application by placing a copy in mcb/WEB-INF/lib, but then develop other applications that use MySQL, you'll need to either copy the driver into those applications or move it to a more global location.

Making the driver more globally accessible also is useful if you think it likely that at some point you'll elect to use JDBC-based session management or realm authentication. Those activities are handled by Tomcat itself above the application level, so Tomcat needs access to the driver to carry them out.

Here's an example installation procedure for Unix, assuming that the MySQL Connector/J driver and Tomcat are located at /src/Java/mysql-connector-java-bin.jar and /usr/local/jakarta-tomcat. The command to install the driver would look like this:

% cp /src/Java/mysql-connector-java-bin.jar /usr/local/jakarta-tomcat/common/lib

For Windows, if the components are installed at D:\mysql-connector-java-bin.jar and D:\jakarta-tomcat, the command looks like this:

C:\> copy D:\mysql-connector-java-bin.jar D:\jakarta-tomcat\common\lib

After installing the driver, restart Tomcat and then request the following mcb application page to verify that Tomcat can find the JDBC driver properly:

http://tomcat.snake.net:8080/mcb/jdbc_test.jsp

You may need to edit jdbc_test.jsp first to change the connection parameters.

16.4.6 Installing the JSTL Distribution

Most of the scripts that are part of the mcb sample application use JSTL, so it's necessary to install it or those scripts won't work. To install a tag library into an application context, copy the library's files into the proper locations under the application's WEB-INF directory. Generally, this means installing at least one JAR file and a tag library descriptor (TLD) file, and adding some tag library information to the application's web.xml file. JSTL actually consists of several tag sets, so there are there are several JAR files and TLD files. The following instructions describe how to install JSTL for use with the mcb application:

• Make sure that the mcb.war file has been unpacked to create the mcb application directory hierarchy under the Tomcat webapps directory. (See Recipe 16.4.416.4.4.") This is necessary because the JSTL files must be installed under the mcb/WEB-INF directory, which will not exist until mcb.war has been unpacked.

• Get the JSTL distribution from the Jakarta Project web site. Go to the Jakarta Taglibs project page, which is accessible at this URL:

  http://jakarta.apache.org/taglibs/

  Follow the Standard Taglib link to get to the JSTL information page; the latter has a Downloads section from which you can get the binary JSTL distribution.

• Unpack the JSTL distribution into some convenient location, preferably outside of the Tomcat hierarchy. The commands to do this are similar to those used to unpack Tomcat itself (see Recipe ). For example, to unpack a ZIP format distribution, use the following command, adjusting the filename as necessary:

  % jar xf jakarta-taglibs-standard.zip

• Unpacking the distribution will create a directory containing several files. Copy the JAR files (jstl.jar, standard.jar, and so forth) to the mcb/WEB-INF/lib directory. These files contain the class libraries that implement the JSTL tag actions. Copy the tag library descriptor files (c.tld, sql.tld, and so forth) to the mcb/WEB-INF directory. These files define the interface for the actions implemented by the classes in the JAR files.

• The mcb/WEB-INF directory contains a file named web.xml that is the web application deployment descriptor file (a fancy name for "configuration file"). Modify web.xml to add <taglib> entries for each of the JSTL TLD files. The entries will look something like this:

  <taglib>
      <taglib-uri>http://java.sun.com/jstl/core</taglib-uri>
      <taglib-location>/WEB-INF/c.tld</taglib-location>
  </taglib>
  <taglib>
      <taglib-uri>http://java.sun.com/jstl/sql</taglib-uri>
      <taglib-location>/WEB-INF/sql.tld</taglib-location>
  </taglib>

  Each <taglib> entry contains a <taglib-uri> element that specifies the symbolic name by which mcb JSP pages will refer to the corresponding TLD file, and a <taglib-location> element that indicates the location of the TLD file under the mcb application directory. (You'll find that web.xml as distributed already contains these entries. However, you should take a look at them to make sure they match the filenames of the TLD files that you just installed in the previous step.)

• The mcb/WEB-INF directory also contains a file named jstl-mcb-setup.inc. This file is not part of JSTL itself, but it contains a JSTL <sql:setDataSource> tag that is used by many of the mcb JSP pages to set up a data source for connecting to the cookbook database. The file looks like this:

  <sql:setDataSource var="conn"
      driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/cookbook"
      user="cbuser" password="cbpass" />

  Edit the driver, url, user, and password tag attributes as necessary to change the connection parameters to those that you use for accessing the cookbook database. Do not change the var attribute.

• The JSTL distribution also includes WAR files containing documentation and examples (standard-doc.war and standard-examples.war). If you wish to install these, copy them into Tomcat's webapps directory. (You probably should install the documentation so that you can access it locally from your own server. It's useful to install the examples as well, because they provide helpful demonstrations showing how to use JSTL tags in JSP pages.)

• Restart Tomcat so that it notices the changes you've just made to the mcb application and so that it unpacks the WAR files containing the JSTL documentation and examples. If Tomcat doesn't unpack WAR files for you automatically, see the sidebar Unpacking a WAR File Manually.

After installing JSTL, restart Tomcat and request the following mcb application page to verify that Tomcat can find the JSTL tags properly:

http://tomcat.snake.net:8080/mcb/jstl_test.jsp

16.4.7 Writing JSP Pages with JSTL

This section discusses the syntax for some of the JSTL tags used most frequently by mcb JSP pages. The descriptions are very brief, and many of these tags have additional attributes that allow them to be used in ways other than those shown here. For more information, consult the JSTL specification (see Appendix C).

A JSP page that uses JSTL must include a taglib directive for each tag set that the page uses. Examples in this book use the core and database tags, identified by the following taglib directives:

<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>

The uri values should match the symbolic values that are listed in the web.xml <taglib> entries (see Recipe 16.4.6"). The prefix values indicate the initial string used in tag names to identify tags as part of a given tag library.

JSTL tags are written in XML format, using a special syntax for tag attributes to include expressions. Within tag attributes, text is interpreted literally unless enclosed within ${...}, in which case it is interpreted as an expression to be evaluated.

The following tags are part of the JSTL core tag set:

<c:out>

This tag evaluates its value attribute and is replaced by the result. One common use for this tag is to provide content for the output page. The following tag would produce the value 3:

<c:out value="${1+2}" />

<c:set>

This tag assigns a value to a variable. For example, to assign a string to a variable named title and then use the variable later in the <title> element of the output page, do this:

<c:set var="title" value="JSTL Example Page" />

<html>
<head>
<title><c:out value="${title}" /></title>
</head>
...

This example illustrates a principle that is generally true for JSTL tags: To specify a variable into which a value is to be stored, name it without using ${...} notation. To refer to that variable's value later, use it within ${...} so that it is interpreted as an expression to be evaluated.

<c:if>

This tag evaluates the conditional test given in its test attribute. If the test result is true, the tag body is evaluated and becomes the tag's output; if the result is false, the body is ignored:

<c:if test="${1 != 0}">
1 is not equal to 0
</c:if>

The comparison operators are ==, !=, <, >, <=, and >=. The alternative operators eq, ne, lt, gt, le, and ge make it easier to avoid using special HTML characters in expressions. Arithmetic operators are +, -, *, / (or div), and % (or mod). Logical operators are && (and), || (or), and ! (not). The special empty operator is true if a value is empty or null:

<c:set var="x" value="" />
<c:if test="${empty x}">
x is empty
</c:if>
<c:set var="y" value="hello" />
<c:if test="${!empty y}">
y is not empty
</c:if>

<c:choose>

This is another conditional tag, but it allows multiple conditions to be tested. Include a <c:when> tag for each condition that you want to test explicitly, and a <c:otherwise> tag if there is a "default" case:

<c:choose>
    <c:when test="${count == 0}">
        Please choose an item
    </c:when>
    <c:when test="${count > 1}">
        Please choose only one item
    </c:when>
    <c:otherwise>
        Thank you for choosing an item
    </c:otherwise>
</c:choose>

<c:forEach>

This tag acts as an iterator, allowing you to loop over a set of values. The following example uses a <c:forEach> tag to loop through a set of rows in the result set from a query (represented here by the rs variable):

<c:forEach var="row" items="${rs.rows}">
    id = <c:out value="${row.id}" />,
    name = <c:out value="${row.name}" />
    <br />
</c:forEach>

Each iteration of the loop assigns the current row to the variable row. Assuming that the query result includes columns named id and name, the column values are accessible as row.id and row.name.

The JSTL database tags are used to issue queries and access their results:

<sql:setDataSource>

This tag sets up connection parameters to be used when JSTL contacts the database server. For example, to specify parameters for using the MySQL Connector/J driver to access the cookbook database, the tag looks like this:

<sql:setDataSource var="conn"
    driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/cookbook"
    user="cbuser" password="cbpass" />

The driver, url, user, and password attributes specify the connection parameters, and the var attribute names the variable to associate with the connection. By convention, JSP pages in this book use the variable conn, so tags occurring later in the page that require a data source can refer to the connection using the expression ${conn}.

To avoid listing connection parameters in each JSP page that uses MySQL, a <sql:setDataSource> tag for connecting to the cookbook database is placed in the include file WEB-INF/jstl-mcb-setup.inc. A JSP page can access the file as follows to set up the connection:

<%@ include file="/WEB-INF/jstl-mcb-setup.inc" %>

To change the connection parameters used by the mcb pages, just edit jstl-mcb-setup.inc.

<sql:update>

To issue a statement such as UPDATE, DELETE, or INSERT that doesn't return rows, use a <sql:update> tag. A dataSource tag attribute indicates the data source, the affected-rows count resulting from the statement is returned in the variable named by the var attribute, and the statement itself should be specified in the tag body:

<sql:update var="count" dataSource="${conn}">
    DELETE FROM profile WHERE id > 100
</sql:update>
Number of rows deleted: <c:out value="${count}" />

<sql:query>

To process queries that return a result set, use <sql:query>. As with <sql:update>, the text of the query is given in the tag body, and the dataSource attribute indicates the data source. The <sql:query> tag also takes a var attribute that names the variable you want to associate with the result set:

<sql:query var="rs" dataSource="${conn}">
    SELECT id, name FROM profile ORDER BY id
</sql:query>

The mcb JSP pages use rs as the name of the result set variable. Strategies for accessing the contents of a result set are outlined below.

<sql:param>

You can write data values literally into a query string, but JSTL also allows the use of placeholders, which is helpful for values that contain characters that are special in SQL statements. Use a ? character for each placeholder in the query string, and provide values to be bound to the placeholders using <sql:param> tags in the body of the query-issuing tag. The data value can be specified either in the body of the <sql:param> tag or in its value attribute:

<sql:update var="count" dataSource="${conn}">
    DELETE FROM profile WHERE id > ?
    <sql:param>100</sql:param>
</sql:update>

<sql:query var="rs" dataSource="${conn}">
    SELECT id, name FROM profile WHERE cats = ? AND color = ?
    <sql:param value="1" />
    <sql:param value="green" />
</sql:query>

The contents of a result set returned by <sql:query> are accessible several ways. Assuming that a result set is available through a variable rs, row i of the result can be accessed either as rs.rows[i] or as rs.rowsByIndex[i], where row number values begin at 0. The first form produces a row with columns that can be accessed by name. The second form produces a row with columns that can be accessed by column number (beginning with 0). For example, if a result set has columns named id and name, you can access the values for row three using column names like this:

<c:out value="${rs.rows[2].id}" />
<c:out value="${rs.rows[2].name}" />

To use column numbers instead, do this:

<c:out value="${rs.rowsByIndex[2][0]}" />
<c:out value="${rs.rowsByIndex[2][1]}" />

You can also use <c:forEach> as an iterator to loop through the rows in a result set. Iterate through rs.rows if you want to access column values by name:

<c:forEach var="row" items="${rs.rows}">
    id = <c:out value="${row.id}" />,
    name = <c:out value="${row.name}" />
    <br />
</c:forEach>

Iterate through rs.rowsByIndex to access column values by number:

<c:forEach var="row" items="${rs.rowsByIndex}">
    id = <c:out value="${row[0]}" />,
    name = <c:out value="${row[1]}" />
    <br />
</c:forEach>

The row count is available as rs.rowCount:

Number of rows selected: <c:out value="${rs.rowCount}" />

Names of the columns in the result set are available using rs.columnNames:

<c:forEach var="name" items="${rs.columnNames}">
    <c:out value="${name}" />
    <br />
</c:forEach>

16.4.8 Writing a MySQL Script using JSP and JSTL

Recipe 16.3 shows how to write Perl, PHP, and Python versions of a script to display the names of the tables in the cookbook database. With the JSTL tags, we can write a JSP page that provides that information as follows:

<%-- show_tables.jsp - Issue SHOW TABLES query, display results --%>

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>
<%@ include file="/WEB-INF/jstl-mcb-setup.inc" %>

<html>
<head>
<title>Tables in cookbook Database</title>
</head>
<body bgcolor="white">

<p>Tables in cookbook database:</p>

<sql:query var="rs" dataSource="${conn}">
    SHOW TABLES
</sql:query>

<c:forEach var="row" items="${rs.rowsByIndex}">
    <c:out value="${row[0]}" /><br />
</c:forEach>

</body>
</html>

The taglib directives identify which tag libraries the script needs, and the include directive pulls in the code that sets up a data source for accessing the cookbook database. The rest of the script generates the page content.

This script should be installed in the mcb subdirectory of your Tomcat server's webapps directory, and you can invoke it as follows:

http://tomcat.snake.net:8080/mcb/show_tables.jsp

Like the PHP script shown in Recipe 16.3, the JSP script does not produce any Content-Type: header explicitly. The JSP engine produces a default header with a content type of text/html automatically.