Using Java Servlets with Database Connectivity

The persistent nature of Java servlets makes them ideal for database/web technology. Mr. McDonald takes a look at using servlets with PostgreSQL and JDBC.
Configuring and Testing Apache/JServ

Listing 1.

A number of tags need to be added to your httpd.conf file. This is situated in /etc/httpd/conf in my only slightly modified Red Hat 5.1 machine. Listing 1 is an excerpt from my file. All tags in Listing 1 are covered in the JServ documentation, and the JAVA_COMPILER is really only applicable to systems which have a JIT installed. Notice that all the relevant classpaths are included: there is no need for Swing and other such visual classes to be present. I also set the LD_LIBRARY_PATH to the Java shared objects. If your Java code relies on shared libraries not located in the directories listed in your /etc/ file, then you must add these directories to LD_LIBRARY_PATH. Additionally the JServ error log (ServletErrorLog) is an important configuration item. It is in this file that all JServ-related errors and exceptions are dumped. Keep an eye on the regular httpd ErrorLog file, too. During testing, I run the command tail -f on the JServ error log file.

Your current Apache daemon must now be restarted. How you do this will vary depending on your distribution, but for Red Hat systems simply use the SysV scripts to do the magic:

/etc/rc.d/init.d/httpd stop
/etc/rc.d/init.d/httpd start

In general, it should be possible to merely kill all instances of the httpd daemon and then rerun the new executable:

killall httpd
Now let's get a little servlet to test the current functionality of the JServ-enabled Apache. The Java servlet in Listing 2 illustrates the benefits and simplicity of the servlet model. This code keeps a counter (hits) that is reset to zero when the servlet is initialized. Every time the servlet is invoked by Apache, the counter is displayed and incremented. Notice that no effort is made to keep the servlet and its data persistent.

Listing 2.

Every servlet must provide a service method which gets called whenever Apache needs to serve the URL The two arguments passed, HttpServletRequest and HttpSerletResponse, encapsulate the HTTP connection. Information on HTTP-specific data such as cookies can be manipulated with these two objects. The method init is called only once when the class is first loaded (or reloaded) by Apache. This initialization method can be used to set up long-lived and potentially costly resources. From this simple example, it can be seen that the class, once loaded by Apache, remains persistent until it is reloaded. The hits variable is initialized once (in the init method), and is then incremented each time service is called. JServ will reload the class if it has been modified since it was first loaded. Sometimes it is necessary to reload the class if a change is made to the property file; in this case, touch the class file. This happens quite transparently.

PostgreSQL and JDBC

Servlets are most often mentioned in the same context as database connectivity, and rightly so. Servlets and their persistent nature make for ideal database/web integration technology. My first forays into this led me quickly to the conclusion that CGI scripts were too slow for anything but large-scale processing with databases—where the initial setup of the database connection matched (or even exceeded) the actual database operations. Since I was familiar with PostgreSQL which came with my Red Hat 5.1 installation, I decided to use that RDBMS for experimentation. I also had some experience with developing Java database client software. The Java Database Connectivity (JDBC) classes in the JDK (Java Development Kit) are easy to use and somewhat portable between databases. The JDBC classes are comprised of database-independent code (the java.sql.* classes, part of the JDK) and database-specific code. The JDBC classes for PostgreSQL are part of the later distributions and are necessary for this application.

Listing 3.

All this can be clearly demonstrated by a small JDBC example. In order for this to be a useful example, it is necessary to have a working PostgreSQL installation. Listing 3 is a rather dense example that illustrates a number of JDBC issues. First, the setup of the PostgreSQL JDBC driver (loginJdbc) and the login to the database (loginUrl, loginUser and loginPasswd). The variable loginUrl is often tricky to get right (especially when learning). The last part, in this case “dbname”, is the name of the database to which the connection is being made. The latter two constants (loginUser and loginPasswd) need to be changed to reflect your database environment. Setting the permissions to the database involves executing the command createuser (as user postgres) and then granting the needed permissions to the user. Once the connection is established, the SQL SELECT statement is executed and the result set is captured by the variable rs. Meta-data for this result set (rsmd) is then used to determine the number of columns returned and to display the column names. The result set is then iterated through, printing each row as strings. This example is very simple-minded and is intended only for SQL SELECT statements because of the result set. Be prepared for exceptions; e.g., an exception is thrown if no columns are returned.