Building a Database Driven Web Site Using JBuilder and InterBase

by Robert Schieck - MER Systems Inc., Borland Developers Conference 1999.


Most web sites consist of static html pages that require you to modify each web page file in order to change the page. As web sites increase in size, not only does the management of their content becomes more complex and difficult, but a change in the look and feel of the web site becomes almost impossible.

For example, Borland had approximately 14,000 static pages on their web site when they changed their name from Borland to Inprise. They did a global search and replace on Borland for Inprise on their web site. The problems created were enormous.

The purpose of this presentation is to show you how to create a database driven web site so that you can easily change the look and feel of your web site.



With all of the messages from the news groups at indexed on our web site, we have more than 1,000,000 pages on line. We needed a new method of administering our web site. We used the following specifications to create our web site:

  1. The web site had to be searchable from the search engines (no frames, ?s,.ddl, exe, asp, jsp).
  2. We wanted the appearance of frames without the use of frames.
  3. We wanted to be able to change any web page without requiring access to our web server.
  4. We wanted to be able to change the look and feel of the site easily.
  5. We wanted to be able to add new pages by adding data to a database.

Web Page Basics

If you look at the Inprise or Borland or InterBase sites, you will find that they all have basically the same layout.

If you look at most sites, there might be 10,000 pages but only 10 or so different topmenus, sidemenus, and footers. If we stored all of our web pages in a database, then we could store the topmenus, sidemenus, and footers in separate tables from the bodies, and join them when the page is sent out. This means that all of the pages could share one topmenu, sidemenu and footer. By changing these, you can quickly change the look and feel of your web site.

Why Java Servlets

Our site was originally built running IIS and Delphi ISAPI DLLs. It worked quite nicely, but did have some difficulties. The main problem was portability. The ISAPI DLLs limited us to an NT platform only. With more than 50% of the world's servers running Apache, and the big web servers running on UNIX, we needed a different environment on which to build our technology so that it would be portable between the server hardware platforms and web servers themselves.

We could have done our web site in perl, but we wanted a newer technology. We settled on Java Servlets.

As for portability, we converted our ISAPI DLL implementation to Java on the NT platform. We then moved it to Linux where we had to add one line of code to get it to work properly. The version from Linux was moved back to NT and ran unchanged, so, from a portability perspective it was great.

Why InterBase

With 1,000,000 pages plus online on our web site and 1,500 to 2,000 users a day hitting it, InterBase is easy to install, easy to use, easy to maintain. The main reason we use InterBase is that we can automate all of the database administration function (backups) in scripts, set them up as a cron job or an "AT" scheduler on NT, and forget about the DBA functions and InterBase.

Why Suns JavaWebServer

Virtually all web servers on the market today can run Java Servlets, either as they are delivered from the vendor or with a third party product.

We chose Sun's JavaWebServer to build on, because it is like buying an Hewlett Packard (HP) printer. Every printer manufacturer tries to emulate an HP to be compatible, so I just buy HP printers knowing that emulation will be perfect. Sun sets the standards for Servlets, so by using Sun's JavaWebServer, I know that it will meet the proper specification as it is the reference server for the servlets.

With more than 50% of the web servers in the world being Apache and the Apache Jserv project past beta, we will try to move our site from Sun's JavaWebServer to Apache.

As for portability, we just created a web site for a customer in the US. We created it on NT using Sun's JavaWebServer 1.1.3. We then moved it to Novell Netware 4.11 , the NetScape Server for Novell Netware and the Servlet Gateway for Novell Netware. The web site ran unchanged on a Novell server. Now that is portability!

How it Works

We are going to create a Servlet called Initially the way the Servlet will be referenced from someone's browser is via a URL such as:


When the server receives a URL like the one above, the server calls the GetForm Servlet and makes available to the Servlet any variables passed in from the browser as well as part of the URL ("/mypage.html") that it is servicing. It expects the Servlet to return a complete web page.

Our Servlet needs to run a query on our database using "/mypage.html" to determine the record we want , to build a complete web page out of it, and to send it back to the server.

The Database

The InterBase database consists of one main table called webforms. It contains all the bodies of the WebPages, as well as the keys for the other tables. The supporting tables are called topmenu, sidemenu, footer and header.

To get a complete page from the database for the web page "/mypage.html', we run the following SQL :


To get a complete page back, we have to add together the fields in the correct order:


However, there are a couple of other fields that area also added in. The final addition looks like:


The reason for TOPHDR_INFO is to allow the metatag info to be stored in a separate table. The HEADDATA is also used for metatags. It is located in the same table as the body, so you can put items in here,such as the title for your page, description, key words etc. that are page specific.

Technical Info

Database Connections

Connecting to a database, no matter whose database it is, is expensive. So in high performance web servers, you cannot connectto the server for every page that is requested. Connection pooling is used to minimize the number of connections to the servers, to allow their use among many Servlets, and to keep the connections open while the server is up.

Being firm believers in not "reinventing the wheel", we use a connection broker that is freely available on the web from the

JavaExchange has not only a connection broker, but a global connection broker that allows connections to be shared among many Servlets.


The connections between our Servlets and InterBase are made via a JDBC driver from InterBase called InterClient. InterClient is a type 3 driver which means that our Servlet will talk to a middle tier InterClient server that in turn talks to InterBase. The connection string looks like:


This connection string says to use the InterClient server on "LocalHost" and to make an Interbase local connection to `"c:/conference.gdb":


This connection string says to use the InterClient server on "" and to make an InterBase local connection to "c:/conference.gdb". This means that you will get your data from a database "c:/conference.gdb" located on


This connection string says to use the InterClient server on "" and to make an InterBase connection to "www.anotherhost:c:/conference.gdb". This means that you will get your data from a database "c:/conference.gdb" located on;

It sometimes gets a little confusing, but just remember that your Servlet will be talking to an InterClient Server which in turn has to make a full connection to an InterBase server.

The Code

An empty core of a java Servlet looks like:

import javax.servlet.*;
import javax.servlet.http.*;
import*;import java.util.*;
import java.sql.*;
import com.javaexchange.dbConnectionBroker.*;
public class GetForm extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);  }
public void doGet(
HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");  }}

The imports are somewhat like "includes" in "C" and "uses" clauses in Delphi. The init is like a constructor and is run only once. It allows you to set up the environment for your Servlet. The doGet method is the routine that is called when the form is returned from a browser.

Our actual init function looks like:

private JDBCGlobalBroker globalBroker;
// A global (all Servlets) connection pool
public void init(ServletConfig config) throws ServletException {
try {
// create a globalconnection broker to handle the database connections.
globalBroker = new JDBCGlobalBroker(
new File("C:\JavaWebServer1.2\servlets\Conference.cfg"));   }
catch (IOException e)
{ e.printStackTrace();
throw new ServletException("Error in GetForm.init()" + e.toString()); } }

In our init method, we do one thing, and that is to setup the connection pooling for the variable globalBroker.

The doGet Method contains the following code:

public void doGet(
HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
   StringBuffer sbout = new StringBuffer(1000);
// create a new Stringbuffer sbout to hold the page we are going to return
//* the 1000 is an arbitrary initial length. The buffer will grow as needed.
Connection conn = null;
// create a new connection variable to hold our reference to a connection from
// the connection broker
try {
// extract the URL that called this Servlet and put it in formName
// if http://localhost/servlet/GetForm/mypage.html goes in
// then formName will = '/mypage.html'
String formName = request.getPathInfo();
if (formName == null) {
      formName = "";     }
else {
// uppercase the form name so that our forms are case insensitive
formName = formName.toUpperCase();     }
// Show form name on the web server console
System.out.println("Getform: FORM NAME=" + formName);
// get connection from broker and save the reference to it in conn
conn = globalBroker.getConnection();
try {
// create stmt to put the SQL statement in and
// rslt to hold the results
Statement stmt = null;
ResultSet rslt = null;
try {
stmt = conn.createStatement();
//assign the SQL to the statement and execute it
+ " AND WEBFORMS.FORMNAME = '" + formName + "'");
//check to see if there was something returned by fetching the first row
if (! {
throw new SQLException("Requested form not found: " + formName);         }
// Add all the returned fields together into the sbout buffer and
// remember a null + something = null so check for nulls
sbout.append(noNull(rslt.getString("FOOTER_INFO")));       }
finally {
//close result, then statement
if (rslt != null) rslt.close();
if (stmt != null) stmt.close();       }     }
finally {
// give the connection back to the broker
if (conn != null) globalBroker.freeConnection(conn);     }   }
catch (Exception e) {
e.printStackTrace();   }
finally {
// set the content length so the browsers won't get snooty
PrintWriter out = new PrintWriter (response.getOutputStream());
// send the page back
out.close();   } }


compiling the source code requires the Java Servlet SDK which is available from Sun.

The command line that we used to compile GetForm was:

c:jbuilder3javabinjavac -classpath;.;c:jsdk2.0libjsdk.jar *.java

As we run it from the /JavaWebSever1.1.3/servlets directory, it cheats a little since it will compile every java file in that directory.

The Servlet Alias

Sun's JavaWebServer has the ability to create an alias. We want everyone who connects to our site to go through our GetForm Servlet. After you have GetForm running properly, then run the JavaWebServer administration facility. Click on the "Web Service" and then "Servlet Aliases". There is an entry in "Alias" column that is just a "/" and in the other column it has "pagecompile". Click on the pagecompile, then click the button modify. Change the pagecompile to GetForm. Click the save button and you are almost done. You will need to restart the server in order to get the changes to take effect.

Since all of your web users will now go through your GetForm Servlet, you may want to modify the code a little. I suggest that you modify:

String formName=request.getPathInfo();

if (formName==null) {
else {


String formName=request.getPathInfo();

if (formName==null) {
else {

so that users who only put your web server name into their browser will automatically get sent to main page, index.html.


Well, it wasn't that hard to do. It took about 130 or so lines of code to make a data driven web site. Now, by changing one menu in one spot, you can affect your whole web site. By modifying the code presented here, when a specific page is requested, you could run a query on the server and return its contents or do almost anything else. By looking for variables sent in by the browser, you can update your database. The door is now open; it is up to you to step through and create your Database Driven Dynamic Web Site.