CompSci 316 (Fall 2013):
Introduction to Database Systems

Index

• Notation
• Setting up
• Developing and deploying WARs
• A mini-tutorial of servlet/JSP
• Additional information

Notation

In the following, HOSTNAME refers to the fully qualified host name of your machine (e.g., dbcourse-junyang.cloudapp.net). USER refers to your login name on this machine (e.g., azureuser).

Setting up

To install Tomcat 6 on your machine and to set up a development environment, run the command "/opt/dbcourse/install/tomcat6/setup.sh". This command will also create the directory ~/jsp-db-beers/ (which is under your home directory), with an example Java servlet/JSP application called db-beers. We will use this application throughout the rest of this document as an example of developing and deploying Java servlet/JSP applications.

Once installed, Tomcat can be found at the URL http://HOSTNAME:8080/. You can access the web-based Tomcat manager interface at http://HOSTNAME:8080/manager/html. It requires the user name USER and the password that you picked for Tomcat during installation. If you ever forget this password, you can find it in the file ~/jsp-db-beers/build.properties.

In /etc/tomcat6/context.xml (which require sudo to read because of permissions), the <Resource> element found towards the end of the file 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 ~/jsp-db-beers/src/my/db/BeerDB.java.

This application works with the beer drinkers' database, which has already been set up for you. If you ever need to "reset" the database content (after you modified it, for example), just run "/opt/dbcourse/examples/db-beers/setup.sh".

Developing and deploying WARs

Go into the directory ~/jsp-db-beers/. Read the file build.properties in this directory to get a sense of the structure of the development directory. If you start a development directory for your own application, you probably 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 when you are at the base of the development directory, in our case ~/jsp-db-beers/):

• "ant deploy": Deploy the web application on Tomcat. If deployment succeeds, you can access your web application at the URL http://HOSTNAME:8080app.path, where app.path is specified in build.properties file (it has a leading "/"). For our running example, the URL would be http://HOSTNAME: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. For debugging, you will find the detailed Tomcat logs in /var/log/tomcat6/ useful.

To develop your own servlet/JSP application, you can start with another 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 ~/jsp-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 ~/jsp-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 ~/jsp-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 (~/jsp-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 ~/jsp-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 JDBC (link)
• JDBC API documentation (link) and Java data source API documentation (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)