Java Web Programming with Eclipse contents
Last modified February 13, 2011 06:38 am

back next

Database-Driven Web Applications

Objectives

References

Video

Overview

This chapter introduces the concept and basic technique of generating Web pages from a database. We illustrate this process by converting the publisher application from a static news feed to a dynamic news feed. Actually, what we generate is not exactly a web page; rather, it is an XML document containing news feed data. In later chapters, we use the same procedures described here to generate HTML documents, which form the basis of web pages.

Recall that the publisher application returns a static RSS document to client applications. In this chapter we replace the static RSS document with a servlet that dynamically generates the RSS news feed from the publisher database created in the MySQL chapter. We do this by directly submitting an SQL select statement to the database from within the NewsFeedServlet that we create. The following diagram illustrates this architecture. Later, we will use a diffirent architecture: one that isolates database interaction away from the servlets.

Servlet-based system with direct access to the database

Servlet-based system with direct access to the database

JDBC Driver Installation

Interaction with relational databases in Java is usually done through a standardized API called Java Database Connectivity (JDBC). For this to work database vendors provide implementations of the JDBC API that operate on their databases. From the perspective of the Java code that uses JDBC, there is no difference between databases because the details of communicating with the databases are hidden within the libraries that implement the API.

Because we are using MySQL, you need to visit the MySQL website and locate and download the recommended MySQL Connector/J package. Unpack the archive that you download and copy the jar file within it into the lib folder under ${TOMCAT_HOME}.

The reason we place the database driver in the lib folder of Tomcat instead of the usual location in WEB-INF/lib is that we will configure Tomcat to create and manage a pool of database connections that all web applications installed with Tomcat will access. For this to work, we need to place the MySQL JDBC driver library in the runtime classpath of Tomcat.

The reason we do not add the MySQL Connector/J jar file to the project's build path is that we do not access any classes or interfaces in this library at build time: only at runetime. Our applictoin only accesses components of the JDBC API that are part of the core Java library. The MySQL Connector/J jar file contains implementations of the service provider's interface of JDBC, which is hidden from our application and needed only at runtime.

Using database connection pools is standard practice because it eliminates costly re-establishment of dropped database connections. Because web applications need to process frequent page requests from multiple clients, the application needs to make frequent connections to the database. Thus, database pooling makes sense in this environment.

Setup Libraries

In this part of the project, we need to add the jar files for the JDOM, ROME and log4j libraries to the publisher application. For this purpose, create a folder called lib under web/WEB-INF and copy the these jar files from the website project into it. (Note: it's easier to perform the copy operation inside Eclipse. If you do the copy operation outside of Eclipse, you need to refresh the navigator view to see the newly added contents.)

Add these jar files to the project build path so that we can use classes from these libraries in our code.

Configure log4j in a manner similar to what you did in the website project. This involves copying the log4j jar file in the lib folder under WEB-INF and adding a log4j.protperies file in the src folder. Make sure the information in log4j.properties is consistent with the needs of the publisher project. In particular, there should be a line that sets the log level to DEBUG for packages that start with publisher, as illustrated in the following.

log4j.logger.publisher=DEBUG

The project also needs a reference to external jar file servlet-api.jar. If this is not already in your project build path, then you should add it to the build path. To do this, right click on the project folder and select Properties. Select Java Build Path and then select the Libraries tab. Click on the Add External Libraries button and navigate to ${TOMCAT_HOME}/lib and then select servlet-api.jar.

The reason we do not add servlet-api.jar to the projects lib folder is that servlet-api.jar is already present in the runtime class path by virtue of being in the Tomcat lib folder. So, we only need to add it to the project's build path.

Create a Dynamic News Feed

In this section, we create a servlet called NewsFeedServlet that generates from the database the XML that comprises a news feed and returns this to connecting clients. We modify the deployment descriptor so that requests for news.rss are handled by the news feed servlet rather than having Tomcat return the static document news.rss that we placed in the web folder.

Start by creating a new class called NewsFeedServlet that extends HttpServlet and is located in the package publisher.web. See the procedure described for creating the HomeServlet in the Java Servlet chapter.

We are going to write logging messages in this servlet. For this purpose, we declare the following member variable.

private Logger logger = Logger.getLogger(this.getClass());

The init method of a servlet is invoked by the servlet container prior to using the servlet for the first time. The init method is therefore used to perform initialization for a servlet. In our case, we need to have the mysql JDBC driver register itself with the JDBC system, so that the JDBC system uses its classes to communicate with the mysql database server. In the following, we do this in the init method of the NewsFeedServlet.

In NewsFeedServlet, override the init(ServletConfig) method that it inherits. (The procedure for overriding inherited methods efficiently in Eclipse is described in the chapter on servlets; see the section in which you create a home servlet.) Use the following code for the implementation of init to allow the MySQL JDBC driver to register itself with the JDBC system.

public void init(ServletConfig config) throws ServletException {
   logger.debug("init()");
   try {
       Class.forName("com.mysql.jdbc.Driver");
   } catch (ClassNotFoundException e) {
       throw new ServletException(e);
   }
}

The method Class.forName in the above code takes the name of a class and returns an instance of Class for it. In our case, this causes the class com.mysql.jdbc.Driver to be loaded, which causes its static initialization code to run. This initialization code registers the class with the JDBC system. Note that the byte code that defines com.mysql.jdbc.Driver is located in the MySQL Connector/J jar file, which we added to the web application's runtime classpath by placing it within WEB-INF/lib.

The following code shows how to connect to the database, generate a news feed XML document from it, and return the document to the client. Add the following code to the news feed servlet.

    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {

        SyndFeed feed = new SyndFeedImpl();
        feed.setFeedType("rss_2.0");
        feed.setTitle("My Local News Feed");
        feed.setLink("http://localhost:8080/publisher/");
        feed.setDescription("This feed was created using ROME.");
        List<SyndEntry> entries = new ArrayList<SyndEntry>();

        try {
            Connection connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost/publisher", "publisher",
                    "publisher");
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement
                    .executeQuery("select * from news_item;");
            while (resultSet.next()) {
                String title = resultSet.getString("title");
                String url = resultSet.getString("url");
                SyndEntry entry = new SyndEntryImpl();
                entry.setTitle(title);
                entry.setLink(url);
                entries.add(entry);
            }
            connection.close();
        } catch (SQLException e) {
            throw new ServletException(e);
        }

        resp.setContentType("text/xml");

        feed.setEntries(entries);
        Writer writer = resp.getWriter();
        SyndFeedOutput output = new SyndFeedOutput();
        try {
            output.output(feed, writer);
        } catch (FeedException e) {
            logger.error("", e);
        }
    }

When you organize imports, select the following classes from the alternative presented.

The NewsFeedServlet uses JDBC classes (from packages java.sql and javax.sql) to retrieve the news items from the database. It uses ROME classes (from package com.sun.syndication.feed.synd) to construct the RSS document in memory and it uses the SyndFeedOutput class (from package com.sun.syndication.feed.synd) to write the document into the output stream provided by the response object.

In the doGet method of the news feed servlet, we start by constructing a instance of SyndFeed. This object represents the XML document that we return to clients. We set the feed type, title, link and description for the overall news feed. We then use a while loop to construct the individual entries that comripse the news feed.

Prior to entering the while loop, we issue an SQL select command to retrieve from the database all of the news item data. Recall that a select command results in a table of rows and columns. In Java, this tabular result of a select command is represented as an instance of the ResultSet class. It is returned by the executeQuery method of a Statement object.

The ResultSet contains an internal row pointer, which is initially set to one before the first row. We call the next method of the ResultSet to advance the pointer to the next row. If a next row exists, next returns true, otherwise, next returns false. Thus, we use the value returned by next as the loop condition.

For each row in the ResultSet, we construct a news feed entry, and add this to the SyndFeed object that we created prior to entering the loop. The following shows the XML fragment that we build in this loop.

<rss>
   <channel>
      <title></title>
      <item>
         <title></title>
         <link></link>
      </item>
      <item>
            :
            :
   </channel>
</rss>

Finally, we use the SyndFeedOutput class from the ROME library and the OutputStream from the HttpServletResponse object to send the RSS document to the client (the website application in our example). Note that we need to specify the content-type of the object we return to the client, which we do by invoking the setContentType method of the response object with the string text/xml. We don't need to specify a content-type when generating html from JSPs because the JSPs will automatically set the content-type to text/html.

Modify the web.xml file, so that requests matching /news.rss are sent to NewsFeedServlet. To do this, insert the contents of the following listing into the web.xml file.

web.xml

<?xml version="1.0"?>
<web-app
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
   <servlet>
      <servlet-name>news-feed</servlet-name>
      <servlet-class>publisher.web.NewsFeedServlet</servlet-class>
   </servlet>
   <servlet-mapping>
      <servlet-name>news-feed</servlet-name>
      <url-pattern>/news.rss</url-pattern>
   </servlet-mapping>
</web-app>

Restore JSP File

If you modified the home.jsp as part of the exercise 2 in the News Feed Application chapter, you will need to restore the jsp file to its original state, otherwise you will get a null pointer excepetion.

Test

Restart the publisher application and verify that the news feed servlet of the publisher application is working correctly by going to http://localhost:8080/publisher/news.rss. You should be able to view the XML document that the servlet generates.

After verifying that the news feed servlet is working correctly, go to http://localhost:8080/website/home and verify that the home servlet is retrieving your news feed and processes it correctly. The result is shown in the following screen shot.

News feed home page

News feed home page

Exercises

(1) Add news

Add the following news item to the news_item table in the publisher database and verify that the change appears in the home page of the website application.

insert into news_item (id, title, url) values (3, 'ABC News', 'http://www.abcnews.com/');

(2) PubDate field

Add the publication date (pubDate) field to your database and news feed. Modify both the website application and the publisher application to include this new information. Note that the SyndEntry class contains a pubDate field that you can set.

Hint: use the datetime data type in MySQL.

back next

Copyright 2007-2009 David Turner and Jinseok Chae. All rights reserved.