Using MySQL for Session BackingStore with Tomcat

19.4.1 Problem

You want to use session storage for Java-based scripts.

19.4.2 Solution

Tomcat handles session management for you. By default, it uses temporary files for backing store, but you can configure it to use MySQL instead by supplying the appropriate JDBC parameters in Tomcat's server.xml configuration file.

19.4.3 Discussion

The Perl and PHP session mechanisms described earlier in this chapter both require applications to indicate explicitly that they want to use MySQL-based session storage. For Perl, a script must state that it wants to use the appropriate Apache::Session module. For PHP 4, the session manager is built into the language, but each application that wants to use the MySQL storage module must register it.

For Java applications that run under Tomcat, a different framework applies. Tomcat itself manages sessions, and if you want to store session information in MySQL, you do so by reconfiguring Tomcat, not your applications. In other words, web-based Java programs are relieved of some of the messy session-related details that must be handled at the application level in other languages. For example, session IDs are handled by the Tomcat server rather than at the application level. Tomcat checks whether cookies are enabled, and uses URL rewriting to encode the session ID in the URL if cookies are unavailable. Application developers need not care which method is used, because the ID is available the same way regardless of how it's transmitted.

To illustrate the independence of applications from the session management method used by Tomcat, this section shows a simple JSP application that uses a session. Then it shows how to reconfigure Tomcat to store session information in MySQL rather than in the default session storewithout requiring any changes at all to the application. First, though, it's necessary to describe the session interface.

19.4.4 The Servlet and JSP Session Interface

Tomcat uses the standard session interface described in the Java Servlet Specification. This interface can be used both by servlets and by JSP pages. Within a servlet, you gain access to the session by importing the javax.servlet.http.HttpSession class and invoking the getSession( ) method of your HttpRequest object:

import javax.servlet.http.*;
HttpSession session = request.getSession ( );

In JSP pages, session support is enabled by default, so it's as though those statements have already been issued by the time the page begins executing. That is, the session is available implicitly through a session variable that's already been set up for you.

The complete session interface is defined in the HttpSession section of the Java Servlet Specification (see Appendix C). Some representative methods of session objects are listed below:

isNew ( )

Returns true or false to indicate whether or not the session has just begun with the current request.

getAttribute (String attrName)

Session contents consist of attributes, which are objects that are bound to names. To access a session attribute, specify its name. The getAttribute( ) method returns the Object bound to the given name, or null if there is no object with that name.

setAttribute (String attrName, Object obj)

Adds the object to the session and binds it to the given name.

removeAttribute (String attrName)

Removes the attribute with the given name from the session.

invalidate ( )

Invalidates the session and any data associated with it. The next request from the client will begin a new session.

19.4.5 A Sample JSP Session Application

The following example shows a JSP page, sess_track.jsp, that maintains a session request counter and a log of the request times. To illustrate the session-related operations more explicitly, this page consists primarily of embedded Java code that uses the HttpSession session interface directly:

<%-- sess_track.jsp - session request counting/timestamping demonstration --%>

<%@ page import="java.util.*" %>
 // get session variables, initializing them if not present

 int count;
 Object obj = session.getAttribute ("count");
 if (obj == null)
 count = 0;
 count = Integer.parseInt (obj.toString ( ));

 ArrayList timestamp = (ArrayList) session.getAttribute ("timestamp");
 if (timestamp == null)
 timestamp = new ArrayList ( );

 // increment counter, add current timestamp to timestamp array

 count = count + 1;
 timestamp.add (new Date ( ));

 if (count < 10) // save updated values in session object
 session.setAttribute ("count", String.valueOf (count));
 session.setAttribute ("timestamp", timestamp);
 else // restart session after 10 requests
 session.removeAttribute ("count");
 session.removeAttribute ("timestamp");

JSP Session Tracker

This session has been active for <%= count %> requests.

The requests occurred at these times:

  • <% for (int i = 0; i < timestamp.size ( ); i++) out.println ("
  • " + timestamp.get (i) + "
  • "); %>

Invoke sess_track.jsp a few times from your browser to see how the display changes.

The session.setAttribute( ) method used in sess_track.jsp places information into the session so that it can be found by later invocations of the script. But session attributes also can be shared with other scripts. To see this, make a copy of sess_track.jsp and invoke the copy from your browser. You'll see that it accesses the same session information as sess_track.jsp.

Some of the session related operations shown in sess_track.jsp can be done using tags from JSTL, which provides a sessionScope variable for getting at the implicit JSP session object:

<%-- sess_track2.jsp - session request counting/timestamping demonstration --%>

<%@ page import="java.util.*" %>
<%@ taglib uri="" prefix="c" %>


 ArrayList timestamp = (ArrayList) session.getAttribute ("timestamp");
 if (timestamp == null)
 timestamp = new ArrayList ( );
 // add current timestamp to timestamp array, store result in session
 timestamp.add (new Date ( ));
 session.setAttribute ("timestamp", timestamp);
%>JSP Session Tracker 2

This session has been active for requests.

The requests occurred at these times:


<%-- has session limit of 10 requests been reached? --%>

19.4.6 Telling Tomcat to Save Session Records in MySQL

The Tomcat documentation pertaining to session management may be found at:

Tomcat has its own default session storage mechanism (temporary files). To override the default and save sessions in MySQL via JDBC instead, use the following procedure:

  1. Create a table to hold session records.
  2. Make sure that Tomcat has access to the proper JDBC driver.
  3. Modify Tomcat's server.xml configuration file to specify use of a persistent session manager for the relevant application context or contexts.

None of these steps involve modifying the sample session application in any way, which is a reflection of how Tomcat implements session support above the application level. Create the Tomcat session table

Tomcat stores several types of information into the session table:

  • The session ID. By default, these are 32-character MD5 values.
  • The session data. This is a serialized string.
  • Whether or not the session is valid, as a single byte.
  • The maximum inactivity time allowed, as a 32-bit integer measured in seconds.
  • The last access time, as a 64-bit integer.

These specifications are satisfied by the following table, which you should create before proceeding to the next section:

CREATE TABLE tomcat_session
 id CHAR(32) NOT NULL,
 data BLOB,
 valid_session CHAR(1) NOT NULL,
 max_inactive INT NOT NULL,
 last_access BIGINT NOT NULL,
); Place the JDBC driver where Tomcat can find It

Because Tomcat itself manages sessions, it must be able to access the JDBC driver used to store sessions in a database. It's common to store drivers in the lib directory of the Tomcat tree so that all applications have access to them. But for a driver to be accessible to Tomcat as well, it should go in the common/lib directory. (Thus, if you have the MySQL Connector/J driver installed in lib, move it to common/lib.) After a restart, Tomcat will be able to use it. For more information, see Recipe 16.4. Modify the Tomcat Configuration File

To tell Tomcat to use the tomcat_session table, it's necessary to modify the server.xml file in Tomcat's conf directory. Do this by placing a element in the body of the element of each application context that should use MySQL-based session storage. (If a context has no such element, create one.) For the mcb application context, the element can be created like this:


The element attributes specify general session-related options. Within the element body, the element attributes provide the specifics pertaining to the JDBC driver. The following discussion focuses on the attributes shown in the example, but there are others that you can use. See the Tomcat session-management documentation for more information.

The attributes shown in the example have the following meanings:


Indicates the Java class that implements persistent session storage. It must be org.apache.catalina.session.PersistentManager.


Indicates the logging detail level. A value of zero disables debug output; higher numbers generate more output.


Allows application sessions to survive server restarts. Should be true if you want Tomcat to save current sessions when it shuts down (and reload them when it starts up).


Indicates the number of seconds before inactive sessions are eligible for being saved to MySQL. A value of -1 means "never."


Indicates how many seconds a session can be idle before becoming eligible to be swapped (saved to MySQL and passivated out of server memory). A value of -1 means "never."


Indicates how many seconds a session can be idle before it should be swapped. A value of -1 means "never." If this feature is enabled, the value should be greater than minIdleSwap and maxIdleBackup.

Within the body of the element, the element indicates how to connect to the database server, which database and table to use for storing session records, and the names of the columns in the table:


The name of a class that implements the org.apache.catalina.Store interface. For JDBC-based storage managers, the value of this attribute must be org.apache.catalina.session.JDBCStore.


The class name for the JDBC driver. For the MySQL Connector/J driver, the attribute value should be com.mysql.jdbc.Driver.


Indicates how to connect to the database server. The following URL connects to the MySQL server on the local host, using a username and password of cbuser and cbpass:


However, server.xml entries are written in XML, so the & character that separates the user and password connection parameters must be written as the & entity, like so:


When Tomcat reads the server.xml file, the file parser converts & back to &, which is what gets passed to the JDBC driver.


Names the table in which to store session records. For our example, this is the tomcat_session table described earlier.

The remaining attributes in the example indicate the column names in the session table. These attributes are sessionIdCol, sessionDataCol, sessionValidCol, sessionMaxInactiveCol, sessionLastAccessedCol which correspond in the obvious way to the columns contained in the tomcat_session table.

After you modify the server.xml file, restart Tomcat. Then invoke the sess_track.jsp or sess_track2.jsp scripts a few times to initiate a session. Each should behave the same way as before you reconfigured Tomcat. After a period of inactivity equal to the element maxIdleBackup attribute value, you should see a session record appear in the tomcat_session table. If you watch the MySQL query log, you should also see sessions being saved to MySQL when you shut down Tomcat.

Changing server.xml is a global change, somewhat similar to changing session.save_handler in PHP's php.ini file. However, unlike PHP, where modifying the global initialization file affects other developers on the same host in such a way that they may have to change their session-based scripts, modifying Tomcat's configuration to use JDBC-based backing store for session management is completely invisible to servlets and JSP pages. Thus, you can make the change without worrying that other developers who use the same Tomcat server will accuse you of acting toward them with premeditated malice.

19.4.7 Session Expiration in Tomcat

Session persistence is 60 minutes by default. To provide an explicit duration for a session manager, add a maxInactiveInterval to the appropriate element in the server's conf/server.xml file. To provide a duration that is specific to a particular application context, add a element to the context's WEB-INF/web.xml file. For example, to use a value of 30 minutes, specify it like this:


If you modify either server.xml or web.xml, restart Tomcat.

19.4.8 Session Tracking in Tomcat

Although your JSP pages need do nothing to have Tomcat set up sessions or to use JDBC for session storage, they may need to take a small step to make sure that sessions move from request to request properly. This is necessary if you generate pages that contain hyperlinks to other pages that participate in the same session.

Tomcat automatically generates a session identifier and tracks the session using cookies if it receives a cookie from the client that contains the session ID. If the client has cookies disabled, Tomcat tracks the session by rewriting URLs to include the session ID. You need not determine which method Tomcat is using, but you should take care to ensure proper propagation of the session ID in case it is being passed by URL rewriting. This means that if you create a page that includes a link to another page that is part of the session, you should not just list the path to the page like this:

To go to the next page,
<a href="nextpage.jsp">click here</a>.

This link doesn't contain the session ID. If Tomcat does happen to be tracking the session using URL rewriting, you'll lose the ID when the user selects the link. Instead, pass the link to encodeURL( ) to allow Tomcat to add the session ID to the URL as necessary:

To go to the next page,
<a href="<%= response.encodeURL (">">click here</a>.

If Tomcat is tracking the session with cookies, encodeURL( ) returns the URL unchanged. However, if Tomcat is tracking the session by means of URL rewriting, encodeURL( ) adds the session ID to the page path automatically, so that it looks something like this:


You should generate URLs using encodeURL( ) like this for links in any tag that takes the user to a page in the current session. This includes ,

, and tags, and possibly even tags, if for some reason those tags invoke a script that generates images on a session-specific basis.

It's probably best to develop the habit of using encodeURL( ) as a matter of routine when writing URLs for session-based applications. Even if you think everyone who uses the application will have cookies enabled, your assumption may prove incorrect some day.

The ) method has a name similar to encodeURL( ), but it's different. It performs conversion of special characters to %xx notation to make them safe for use in URLs.

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

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: