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 relationshipTomcat 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:
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:
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: ecipes and D:jakarta-tomcat, the command looks like this:
C:> copy D: ecipes omcatmcb.war D:jakarta-tomcatwebapps
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 privatethat 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.
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:
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-tomcatcommonlib
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:
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.
% jar xf jakarta-taglibs-standard.zip
http://java.sun.com/jstl/core /WEB-INF/c.tld http://java.sun.com/jstl/sql /WEB-INF/sql.tld
Each entry contains a element that specifies the symbolic name by which mcb JSP pages will refer to the corresponding TLD file, and a 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.)
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.
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 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:
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:
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
element of the output page, do this:
...
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.
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:
1 is not equal to 0
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:
x is empty y is not empty
This is another conditional tag, but it allows multiple conditions to be tested. Include a tag for each condition that you want to test explicitly, and a tag if there is a "default" case:
Please choose an item Please choose only one item Thank you for choosing an item
This tag acts as an iterator, allowing you to loop over a set of values. The following example uses a tag to loop through a set of rows in the result set from a query (represented here by the rs variable):
id = , name =
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:
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:
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 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.
To issue a statement such as UPDATE, DELETE, or INSERT that doesn't return rows, use a 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:
DELETE FROM profile WHERE id > 100 Number of rows deleted:
To process queries that return a result set, use . As with , the text of the query is given in the tag body, and the dataSource attribute indicates the data source. The tag also takes a var attribute that names the variable you want to associate with the result set:
SELECT id, name FROM profile ORDER BY id
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.
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 tags in the body of the query-issuing tag. The data value can be specified either in the body of the tag or in its value attribute:
DELETE FROM profile WHERE id > ? 100 SELECT id, name FROM profile WHERE cats = ? AND color = ?
The contents of a result set returned by 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:
To use column numbers instead, do this:
You can also use 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:
id = , name =
Iterate through rs.rowsByIndex to access column values by number:
id = , name =
The row count is available as rs.rowCount:
Number of rows selected:
Names of the columns in the result set are available using rs.columnNames:
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" %>
Tables in cookbook Database
Tables in cookbook database:
SHOW TABLES
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.
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References