Generate dynamic content with Tomcat and MySQL

Document options Document options requiring JavaScript are not displayed Sample code Rate this page Help us improve this content

Level: Intermediate

Javid Jamae (javidjamae@yahoo.com), Independent Software Consultant, Jamae Consulting
Kulvir Singh Bhogal (kbhogal@us.ibm.com), Senior IT Specialist, IBM

01 Sep 2002

Companies like doubleclick.net have made a lot of money serving banner ads on the Web. The service they provide is great, but why pay for something you can do yourself? In this article, enterprise Java consultants Javid Jamae and Kulvir Bhogal demonstrate how to create rotating banner ads using an all open-source environment: Apache Tomcat, MySQL, and the MM MySQL JDBC driver. First, they'll walk you through the necessary setup in Tomcat and MySQL, and then show you how to install the MM MySQL JDBC driver to allow a Java servlet running in Tomcat to communicate with MySQL.

Banner advertising has been around since the Internet was first transformed from an educational and governmental information store into a centerpoint for international commerce. A rotating banner is an allocated space on a Web page, in which the space is used to display an advertisement -- either randomly or based on some business logic -- every time the Web page is loaded or reloaded. The programs that drive rotating banner ads are fairly simple, but they are important advertising tools. Just like their half-minute radio and television counterparts, these dynamic advertising tools allow a single Web page to display advertisements from various sources and enable different companies to create brand recognition for their products and services while targeting the same audience.

Regardless of how you view them (yes, we all find them annoying at times), Web banners have become a way of life on the Internet. The fact is that the Web constitutes a gargantuan audience of consumers, whose money keeps the wheels of e-business turning. In the short history of Internet marketing, e-business owners have shown they are willing to pay top dollar to place their banner ads on high-traffic sites.

Some companies, such as doubleclick.net, have capitalized on the Web banner phenomenon, acting as an application service provider (ASP) to perform such services as tracking clicks for specific banner advertisements. These ASPs then inform advertisers on how effective their Web advertisement campaigns are.

Of course, ASPs such as doubleclick.net cost money. If you're like us, you don't like spending money when you could get the service for free. Wait a second -- you've probably heard that nothing in this world is free. But don't worry. All this article will cost you is your time. In fact, we'll show you how to tie together an open source (that is, free) environment to set up your own, powerful Web banner tracking system. To pull off this task, our weapons of choice will be Tomcat, MySQL, a single Java servlet, and a few helper classes. Excited? Well then, let's jump into the software installation.

Setting up Tomcat and MySQL

In this section, we will walk through the setup for both Tomcat and MySQL. We'll then show you how to install the driver you need to enable these two applications to communicate.

Setting up Tomcat

Download and install Tomcat. For this article, we used Tomcat 4.1 for Windows, which comes with a nice install package and creates icons and a Start menu folder for you. It also creates a Windows Service to start and stop the Tomcat server. Installation should be very straightforward, but if you have trouble, refer to the Tomcat documentation. Because of the popularity of Tomcat, there is also ample help available on newsgroups and on the Web, some of which we've listed in Resources.

After you install Tomcat, there are a few steps you need to complete to set up our rotating banner Web application. First, we will create a subdirectory called banner under the [installdir]\webapps directory. Under the banner subdirectory, we will then create the standard Web application directory structure:

[installdir]\webapps\banner
[installdir]\webapps\banner\WEB-INF
[installdir]\webapps\banner\WEB-INF\classes
[installdir]\webapps\banner\WEB-INF\lib

Next, we'll add a context to point to our Web application. A context is merely an alias, telling Tomcat where to access our Web application. Our context path will be /banner, and it will point to the banner subdirectory we just created. When a user enters http://localhost:8080/banner, he will be taken to our top-level banner directory under webapps. If he wants to run our BannerServlet, which will exist in the WEB-INF/classes directory, he would use http://localhost:8080/banner/servlet/BannerServlet.

To add the /banner context, we first need to edit the server.xml file in the Tomcat conf directory. Go nearly to the bottom of the file where you will see several context tags. There should be one for /admin and one for /examples. Add the following context tag:

<!-- BannerAd Context -->
<Context path="/banner" docBase="banner" 
debug="0" reloadable="true" crossContext="true"/>

After you've added the context tag, restart Tomcat so that the changes to the server.xml file take effect (in our case, we just restarted the Windows Service that Tomcat installed).

Setting up MySQL

MySQL is a powerful database for the price you pay for it (nothing), and many companies use MySQL to handle their data. The number is growing daily as companies with low budgets enter the Web market. The open source community has greeted MySQL with open arms. The documentation about this powerful database is abundant, and there are both Linux and Windows versions.

Download and install MySQL with the "Typical" setup option (for this article, we'll assume you are working with the WinNT version of MySQL). After you've completed the installation, you will notice one of the annoying aspects of MySQL: it doesn't place anything in your Start menu. You will need to go to the directory where you installed the database (c:\mysql\, by default), then go to the bin directory, where you will find the executables to run MySQL.

First, double-click the winmysqladmin.exe file. The first time you open this file, you will be asked to enter a username and password. Next, right-click on the street light icon that will appear in your taskbar. Go to WinNT and choose "Start the service" to keep MySQL running in the background. Finally, double-click the "mysql.exe" icon to start "MySQL Monitor," where you will work with MySQL.

Getting MySQL and Tomcat to work together

Getting MySQL and Tomcat to communicate with one another can be difficult. With the JDBC API, however, we'll be able to use SQL to talk to a MySQL database from our Java classes with relative ease.

We'll use the MM MySQL JDBC driver, an open source driver, to facilitate communication between MySQL and Tomcat. (At the time of this writing, 2.0.14 is the latest version.)

Unfortunately, setting up this driver is a little tricky. First, download the appropriate JAR file for the driver from here. We downloaded the file called mm.mysql-2.0.14-you-must-unjar-me.jar. Next, unjar (or unzip) the file to a temporary directory. Finally, copy the file that contains the driver from the unzipped directory structure into your WEBAPPS/BANNER/WEB-INF/lib directory and restart Tomcat. In the version of the driver we downloaded, the file is called mm.mysql-2.0.14-bin.jar.

We could have used the JDBC/ODBC bridge driver to communicate with MySQL, but we've assumed that a native driver would provide more of a performance advantage (though we haven't run any benchmarks to prove our assumption). For this application, it probably wouldn't make much of a difference in performance, but we decided to demonstrate how to use the native JDBC driver so you wouldn't have to figure it out when you are designing a larger application.

Back to top

The rotating banner application

Now that we have installed all of our software, we'll go over what our application is capable of and how we have architected and developed it.

There are essentially two actions you can perform using our banner servlet. First, you can use it to view a random banner image on a Web page, which occurs every time a Web page is loaded that contains the banner ad. Second, you can click on the banner image, which will forward you to the link corresponding to the image that was loaded.

In terms of HTML, the code looks similar to this:

<a href="...Link For Random Image...">
<img src="...Random Image..."/>
</a>

If we want to load a random image, our image tag obviously can't point to a static image file, so we will direct it to run a servlet, which we'll call BannerServlet. We will use an HTTP GET method parameter to direct our servlet to give us an image. So our image tag will look like this:

<img src="http://localhost:8080/banner/servlet/BannerServlet?type=image"/>

This tag calls our servlet and passes in the parameter key-value pair type=image. The servlet's service() method interprets this request and returns a random image to the browser. Of course, the servlet must somehow remember which image it sent to the client so it knows where to go when the client clicks on it. We'll store the metadata related to the image that we sent on the client's session so that when the user clicks the image, the metadata from his session will load, redirecting him to the appropriate URL.

Our link tag will look almost the same as our image tag:

<a href="http://localhost:8080/banner/servlet/BannerServlet?type=link">
<img src="http://localhost:8080/banner/servlet/BannerServlet?type=image"/>
</a>

When the servlet is called with the type=link key-value pair, the servlet grabs the banner's metadata and reroutes the user to the appropriate URL.

Code and CLASSPATH setup

To use the code provided with this article, you must first unzip the zip file (in Resources) and compile the .java files using the command line javac compiler or your favorite IDE. To compile the code, set your CLASSPATH with these two JAR files:

• mm.mySQL-2.0.14-bin.jar (or whatever version you get from the MM MySQL Web site)
  
  
• servlet.jar (if it doesn't come packaged with the JDK you are using)

Copy the compiled .class files into the [tomcat_installdir]\webapps\banner\WEB-INF\classes directory we created earlier. The image files and the .htm file we provided as an example must go in the [tomcat_installdir]\webapps\banner directory.

Database setup

The database portion of our application is merely used to persist metadata about each banner in our system. In other words, we are not actually going to store the image files in our database, but rather a reference to each image file. In our database, we will use seven columns to describe each banner ad.

The descriptions in Table 1 show you what each record will contain. We will actually only use five out of the seven database columns in the application. CustomerName and NumberOfClicksPurchased aren't used in our version, but we put them in as placeholders for expandability. You could very easily extend our application and use it as a real-world business application where customers would pay for a certain number of clicks per banner.

Table 1. Database fields

Of course, in a real-world environment, you would have more than one site banner. Depending on how much your banner "sponsor" paid you in comparison to other sponsors, you might want his banner to be shown less or more. The BannerWeight fields will be used to implement this functionality. We have implemented a very simple weight system where the percent chance for each banner to be displayed is:

(BannerWeight / Sum of all BannerWeights) * 100

Translating what was just stated in SQL, you would issue the following statements using the MySQL Monitor:

mysql> create database BANNER;

To connect to the database, you enter:

mysql> use BANNER;

Next, we create our table:

mysql> create table ADS
(IMAGEFILE VARCHAR(50) NOT NULL, 
URL VARCHAR(50) NOT NULL,
CUSTOMERNAME VARCHAR(50),
NUMBEROFCLICKSPURCHASED INT(4),
NUMBEROFCLICKSREMAINING INT(4) NOT NULL,
NUMBEROFIMPRESSIONS INT(4) NOT NULL,
BANNERWEIGHT INT(4) NOT NULL);

A "describe" of the ADS table would look like Figure 1.

You will need to populate the database with some sample values so that you can make sure what you are building is sound. Included in the project zip file are example banners (in GIF format) you can use to get an idea of how things should work. Of course, for the Web banner URL, you will need to state the location where you decide to house the banner files. You can follow the SQL syntax below to "register" your banners into the database:

mysql> insert into ADS values('/sitea.gif','http://www.cnn.com',
  'John Doe',100,100,0,10);

Use this syntax to insert the records shown in Table 2 into the database.

Table 2. Database records

Note that the Web banner URLs are located on the local host for testing purposes only. In a production environment, your URL would point to the actual location of the GIF file. This URL could be virtually any location on the Internet.

Now that we have a database, we need to use the data we just put in it. We will do this with a Java servlet. The code for the Java servlet that will "power" our endeavor is described below. You might want to take some time to read through the code of BannerServlet.java in the project zip file. If you feel overwhelmed, don't worry; we'll spend some time explaining how the code works.

Back to top

Banner architecture

The architecture for our banner ad servlet is very simple. We will use four classes:

• A general purpose Logger class that will write log messages to a text file.
  
  
• A servlet called BannerServlet that will be called every time a banner image is displayed (that is, every time the page loads) and every time a banner image is clicked. This servlet is the heart of our application.
  
  
• A general purpose DBHandler class that our BannerServlet will use to communicate with our MySQL database.
  
  
• A Banner class, which we will use to create objects that contain all the metadata we have for each banner in the database.

The BannerServlet class and the Banner class are specific to our application. They are fairly straightforward and you can easily expand them to add more complex features.

The great part about the DBHandler and Logger classes are that you can reuse them in practically any application you write in which you need to communicate with a database or write to a log file.

We will go into a more detailed discussion about all four of these classes so you can understand how the servlet works and how it uses DBHandler to communicate with our MySQL database.

Logger class

The Logger class is very simple. It has a single field for the File object we are logging to. You can pass a reference to a single Logger object to several classes and have them all write to the same log file. The Logger class allows you to do several things. You can:

• Create a logger
• Add a divider to the log file (a string of "------"'s)
• Add a log entry by passing in the name of the calling method and the log message
• Add a default message for the start of a method
• Add a default message for the end of a method
• Delete the log file
• Return the File object used by the logger

We will use a Logger object in both the DBHandler class and the BannerServlet.

DBHandler class

DBHandler is a very versatile class and can be used to interface with just about any database through JDBC. It requires a string with the name for the JDBC/ODBC driver that we are using to connect to our database, a string with the name of the database for which we set up the DSN, and a Logger parameter. The Logger parameter tells DBHandler where to print output messages while working its magic. The constructor for DBHandler opens a connection to the database. When you are done using DBHandler, you must close it using the close() method.

After you create a DBHandler object, you must create a query to execute. Use the setQueryString() method to pass in a string with your query in it, which can be in the form of a PreparedStatement class.

A PreparedStatement is a great feature of JDBC. It allows you to define a query string, using question mark characters in place of variable criteria in the query. You can then use setter methods on the PreparedStatement class to set the values of the unknown elements in the query. Fortunately, the DBHandler class will take care of all of this for us. We just set the query we want and call one of the DBHandler methods, as shown below:

public Banner getBannerByName(String name) {
        ...
        ...
        dbHandler.setQueryString("SELECT * FROM ADS WHERE NAME=?");
        ResultSet rs = dbHandler.lookup(name);
        dbHandler.close();
        ...
        ...
}

You can perform SELECT queries using the lookup() method, UPDATE queries using the executeUpdate() method, and INSERT queries using the insert() method. There is also an execute() method that takes no parameters and executes any query that doesn't have any PrepareStatement arguments.

Banner class

The Banner class is simply a bunch of setter and getter methods that directly correspond to the column values in the ADS database table.

BannerServlet class

The BannerServlet is the heart of our application. We will break down the different parts of this class for you. By browsing through the code, you will become more familiar with how the DBHandler class is used to connect to the database.

Fields
The BannerServlet uses five fields:

• String _databaseUrl: The name of the database that we will access (jdbc:odbc:\\localhost\BANNER).
  
  
• String _driverName: The name of the driver that we will use to talk to the database. As described above, we will use the MM MySQL JDBC driver. The name of the driver is org.gjt.mm.mysql.Driver.
  
  
• Logger _logger: The name of the Logger class we will use to log all events that occur in our application.
  
  
• HashMap _banners: A HashMap of all the Banner objects. This HashMap will be populated in the init() method of our servlet. Each row in our database table will translate to a single Banner object that is stored in our HashMap. We'll elaborate on this in a moment.
  
  
• int _totalWeight: The sum of all the Banner weights. This value is also set in the init() method; we'll discuss this shortly.

init()
The init(ServletConfig) method of any servlet is called when the servlet is first loaded by the container. The container in our case is Tomcat. Tomcat generates and passes in a ServletConfig object that contains both default configuration information that the container sets and custom configuration information that the developer (you) can set up in the config files for the servlet. For our purposes, we will not need to pass in any configuration information, but you may want to expand the servlet at some point and use this functionality.

The first thing we do in init() after calling super.init() is initialize our HashMap variable _banners and set our _totalWeight to 0. Then, we connect to the database and get all the rows from our ADS table in the form of a ResultSet. We iterate through our ResultSet using a for loop, construct a Banner object out of each row, then add the Banner object into the HashMap using the index of our for-loop as the hash value. (We could have just as easily used a Vector or some other Collection class to accomplish the same thing.)

We now have a HashMap of all of our Banners in memory. If we update our database, we can just call the init() method to reload our HashMap again. We will use this in our increaseImpressions() and decreaseClicksRemaining() methods.

service()
The service() method is defined in the HttpServlet class that our BannerServlet extends and can process any request, whether it be a GET or POST method. Our implementation of the service() method has two core parts. The first part handles what the servlet does when the Web page sends an image request, and the second part handles a link request.

Finally, we look at the type parameter sent in from the client. If the value of type was image, we get a random Banner object from the database, add the Banner object to the user's session, increase the number of impressions for the given banner, and route the user to the image referenced in the image field of the Banner object.

If the value of type was link, we get the Banner object off of the session, decrease the clicks remaining on the banner, and redirect the user to the link specified in the URL field of the Banner object.

Other methods
getRandomBanner(), increaseImpressions(), and decreaseClicksRemaining() are all helper methods called from the service method. getRandomBanner() uses a simple algorithm to select a random banner from our _banners HashMap. Both increaseImpressions() and decreaseClicksRemaining() use DBHandler to connect to the database and update information for the given Banner. At the end of these two methods, we call the init() method to reload the updated Banner information into our HashMap.

Back to top

Conclusion

We have created an application that demonstrates how Apache Tomcat and MySQL can communicate with one another, and have given you a very useful and reusable tool that handles most of the database work for you. There are several things you could do to expand this application that are outside the scope of this article. As we described above, you could expand the application into a system in which you charge customers for a certain number of clicks or even a certain number of impressions. The CustomerName field in the database could easily be replaced by some other field that would be a primary key in another table containing all of your customer information.

You could expand the DBHandler class to handle database connection pooling. In addition, you could externalize the queries you want to use for your application into a text, properties, or XML file, allowing you to expand or change query definitions without having to write more code.

Back to top

Download

	Information about download methods

Resources

• Download the source code used in this article.
  
  
  
  
• The Jakarta Project is Tomcat's parent project and hosts many other fabulous open source projects that you can use.
  
  
  
  
• The MySQL Web page has links on how to download and use mySQL.
  
  
  
  
• Download the MM MySql JDBC Driver used in this article from Sourceforge.
  
  
  
  
• The JDBC API documentation is a great resource for anything involving JDBC.
  
  
  
  
• Learn more about JDBC with these free developerWorks tutorials: Managing database connections with JDBC (November 2001) and Advanced database operations with JDBC (November 2001).
  
  
  
  
• Another good JDBC resource is the JDBC 2.0 Fundamentals short course from the Java Developer Connection.
  
  
  
  
• The Tomcat Book was founded in April 2001 to come up with an open-source guide to installing, configuring, and deploying the Tomcat JSP/Servlet server and applications for it.
  
  
  
  
• If you have specific questions about Tomcat, The JGuru Tomcat FAQ is an interactive FAQ where you can post your questions.
  
  
  
  
• In "Filtering tricks for your Tomcat" (developerWorks, June 2001), Sing Li shows you how to make productive use of filters in your projects.
  
  
  
  
• JavaServer Pages (JSP) technology is typically used for building HTML pages with dynamic content. But you can use this technology to generate dynamic content in other formats as well, including XML. Marshall Lamb shows you how in "Generate dynamic XML with JavaServer Pages technology" (developerWorks, December 2000).
  
  
  
  
• Find more resources on the developerWorks Java technology zone.
  

About the authors

		Javid Jamae is an independent software consultant who specializes in enterprise application and software methodology consulting. You can contact Javid at javidjamae@yahoo.com.

		Kulvir Singh Bhogal works as an IBM Software Services for WebSphere consultant, devising and implementing J2EE solutions at customer sites across the United States.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Back to top