CPS 116 (Fall 2008):
Introduction to Database Systems

Index

• Setting up development environment
• Running Tomcat
• Developing and deploying WARs
• A mini-tutorial of servlet/JSP
• Additional information

Setting up development environment

1. Setting up SSH tunneling. If you have not done so already, follow the instructions for "Advanced SSH setup" in the "Getting Started with cps116" document.
2. Installing Tomcat. Log into cps116 and run the command "/home/dbcourse/bin/setup-tomcat.sh". Log out and log back in again.
3. Copying example source code. Run the command "/home/dbcourse/examples/web-db-beers/setup.sh", which will create a directory named web-db-beers under your home directory on cps116. This directory contains the source code for a simple db-beers web application, which we will use throughout the rest of this document as an example of developing and deploying Java servlet/JSP applications.
4. Setting up example database. Run the command "/home/dbcourse/examples/db-beers/setup.sh", which will set up the database needed for running the db-beers web application.

A note on configuring Tomcat: The script you ran to install Tomcat also carried out some configuration tasks. In particular, the <Resource> element found towards the end of the file ~/apache-tomcat/conf/context.xml configures a JNDI data source that can be used by your web applications. With such a data source, your web applications do not need to worry about any database- or connection-specific details; they simply request a connection from the data source. The application code for dealing with data sources can be found in ~/web-db-beers/src/my/db/BeerDB.java. Additional notes on Tomcat configuration can be found in ~/apache-tomcat/README-cps116.txt.

Running Tomcat

To start Tomcat, run the command "~/apache-tomcat/bin/startup.sh".

To see access the Tomcat web server, point the browser on your work computer to http://localhost:8080/. Assuming that you have followed the advanced SSH setup and Tomcat installation instructions correctly, you should see a welcome page from Tomcat.

To shutdown Tomcat, run the command "~/apache-tomcat/bin/shutdown.sh".

Note: You can only access the Tomcat web site while you remain logged into cps116 using SSH. Also, please remember to shutdown Tomcat before you log off.

Developing and deploying WARs

After starting Tomcat, go into the directory ~/web-db-beers, which is an example of a development directory for a simple db-beers web application. Read the file build.properties in this directory to get a sense of the structure of the development directory. When you start a development directory for your own application, you will want to structure it in the same way.

We will use the build tool named ant to build, deploy, and undeploy our application. Below is a list of the most essential commands (all of them should be issued from the base of the development directory, in our case ~/web-db-beers):

• "ant deploy": Deploy the web application on Tomcat. If deployment succeeds, you can access your web application at the URL http://localhost:8080app.path, where app.path is specified in build.properties file (it should have a leading "/"). For our running example, the URL would be http://localhost:8080/db-beers.
• "ant undeploy": Undeploy the web application on Tomcat. A web application must be undeployed first before it can be re-deployed.
• "ant compile": Compile the Java source files. It is automatically called by "ant dist".
• "ant dist": Package the web application into a WAR (Web Application Archive) file ready to be deployed. It is automatically called by "ant deploy".

A typical development cycle consists of coding/debugging -> ant deploy -> testing on browser -> ant undeploy, and then back to coding/debugging.

To develop your own servlet/JSP application, you may start with a copy of the development directory for db-beers, and make appropriate changes to build.properties, src/, and web/. If you want to use external jar library files, put those under web/WEB-INF/lib/.

A mini-tutorial of servlet/JSP

Servlet basics

The servlet concept is very simple: Instead of serving a static HTML page, the web server executes a piece of Java code to generate the HTML output to serve to the client.

An example servlet is given in ~/web-db-beers/src/my/ViewDrinkerServlet.java. Note that this servlet obtains the database connection object from the current HTTP "session" object. Hence, a single database connection will be shared by all server-generated pages accessed during the same session, which is much more efficient than the approach where each page makes its own connection to access the database.

The shared database connection object is created when the session is created. This task is performed by the event listener in ~/web-db-beers/src/my/listener/SessionListener.java, which listens to the event of a session being created, makes a database connection, and puts it into the session object for use by pages to be accessed during this session.

Most of the database operations are handled by the BeerDB class in ~/web-db-beers/src/my/db/BeerDB.java, which wraps the database connection object. Dealing with the database server directly is a tricky business: Since many JDBC resources (such as ResultSet and Statement) require explicit release for performance reasons, exception handling can be quite tricky. Therefore, we use BeerDB to hide this complexity from the rest of the application. BeerDB.java exercises a wide range of JDBC functionalities, and the code is fairly well documented; make sure you read and understand the comments.

Both the servlet and the listener need to be registered in the Web Deployment Descriptor (~/web-db-beers/web/WEB-INF/web.xml). In this file, we first use a <servlet> element to define the servlet, and then use a <servlet-mapping> element to specify the mapping between a request URL and the servlet. When a mapped URL is requested, the server will generate the response HTML by calling the associated servlet. The web.xml file will be automatically packaged inside the WAR file for deployment. When you develop your application, you will need to modify web.xml to include your own set of servlets.

Why not let the entire web application share a single database connection?

This approach may seem attractive because it would further reduce the overhead of making database connections and preparing statements. It can be accomplished by defining a listener for the "servlet context," which is shared by all servlets in the application. The listener would make the database connection when the context is initialized, put the connection object into the context object, and close the connection when the context is destroyed. There are some issues, however. First, the context has a long life (as long as the deployment duration of your web application). If the application is not used frequently enough, the database connection will likely time out. Therefore, your code needs to recover from timed-out connections. Second, if your web application is popular and there are many concurrent users, a single database connection will become a performance bottleneck. Furthermore, the application server may use many different threads to handle user requests, but not all JDBC drivers are thread-safe. Hence, your code needs to ensure that concurrent uses of the connection object are serialized (which only excerbates the performance bottleneck).

What else can we do to improve performance?

In our sample db-beers web application, we share the database connection within a session. In a real production setting, however, this approach does not scale. Many sessions can overlap in time (because they only time out after a long period of inactivity), so a lot of database connections will be required. A much better approach is connection pooling, which utilizes a cache of database connections shared among sessions. In this case it does not make sense to maintain a database connection for the entire duration of a session. Instead, each page would request a database connection from the pool, and release it back to the pool after using it. Also, explicitly preparing statements in each session would not make sense either; instead, you might want to have the connection pool prepare them for each newly acquired database connection.

JSP basics

JSP can be regarded as a convenient way of writing servlets. In fact, Tomcat implements JSP pages by translating them into servlets. The idea is to embed Java code in HTML using special tags "<% ... %>", which are executed at runtime by the server to produce the final HTML response. (The difference between JSP and JavaScript is that JavaScript is run on the client by the browser.)

JSP files end with suffix .jsp. You will find several JSP examples under ~/web-db-beers/web/. Start with all-drinkers.jsp and go through edit-drinker.jsp and update-drinker.jsp. Comments in these files explain the basics of writing JSPs.

There is no need to register the JSP files in the web deployment descriptor. You just need to put JSP files in the web/ subdirectory, together with regular HTML pages, GIF files, etc. They will be automatically packaged into the WAR file for deployment.

Additional information

• Course notes on using JDBC with DB2 (link)
• JDBC API documentation (link) and Java data source API documentation (link)
• Search for "JDBC programming" in the DB2 Information Center (link)
• Servlet 2.5 API documentation (link) and other information (link)
• JSP 2.1 API documentation (link), specification (link), expression language specification (link), a slightly outdated quick reference card for JSP 2.0 (link), and other information (link)