Building a Database Driven Web Site Using JBuilder and InterBase

Specifications

With all of the messages from the news groups at forums.borland.com 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!

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 :

SELECT BODY, HEADDATA, TOPHDR_INFO, TOPMENU_INFO, FOOTER_INFO,SIDEMENU_INFO
FROM WEBFORMS, TOPHDR, TOPMENU, FOOTER, SIDEMENU
WHERE
WEBFORMS.TOPHDR_KEY=TOPHDR.TOPHDR_KEY AND
WEBFORMS.TOPMENU_KEY=TOPMENU.TOPMENU_KEY AND
WEBFORMS.FOOTER_KEY=FOOTER.FOOTER_KEY AND
WEBFORMS.SIDEMENU_KEY=SIDEMENU.SIDEMENU_KEY AND
WEBFORMS.FORMNAME='/MYPAGE.HTML';

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

Full html page=TOPMENU_INFO + SIDEMENU_INFO + BODY + FOOTER_INFO

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

Full html page=TOPHDR_INFO + HEADDATA + TOPMENU_INFO + SIDEMENU_INFO + BODY + FOOTER_INFO

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.

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 www.javaexchange.com

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

InterClient

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:

jdbc:interbase://localhost/c:/conference.gdb

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

jdbc:interbase://www.somehost.com/c:/conference.gdb

This connection string says to use the InterClient server on "www.somehost.com" 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 www.somehost.com:

jdbc:interbase://www.somehost.com/www.anotherhost:c:/conference.gdb

This connection string says to use the InterClient server on "www.somehost.com" 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 www.anotherhost.com;

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 java.io.*;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 {
super.init(config);
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 {
   response.setContentType("text/html");
   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
rslt = stmt.executeQuery("SELECT BODY, HEADDATA, TOPHDR_INFO, TOPMENU_INFO,FOOTER_INFO, "
+ "SIDEMENU_INFO FROM WEBFORMS, TOPHDR, TOPMENU, FOOTER, SIDEMENU "
+ " WHERE WEBFORMS.TOPHDR_KEY = TOPHDR.TOPHDR_KEY"
+ " AND WEBFORMS.TOPMENU_KEY = TOPMENU.TOPMENU_KEY "
+ " AND WEBFORMS.FOOTER_KEY = FOOTER.FOOTER_KEY "
+ " AND WEBFORMS.SIDEMENU_KEY = SIDEMENU.SIDEMENU_KEY "
+ " AND WEBFORMS.FORMNAME = '" + formName + "'");
//check to see if there was something returned by fetching the first row
if (!rslt.next()) {
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("TOPHDR_INFO")));
sbout.append(noNull(rslt.getString("HEADDATA")));
sbout.append(noNull(rslt.getString("TOPMENU_INFO")));
sbout.append(noNull(rslt.getString("SIDEMENU_INFO")));
sbout.append(noNull(rslt.getString("BODY")));
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) {
sbout.appen
d(e.toString());
e.printStackTrace();   }
finally {
// set the content length so the browsers won't get snooty
response.setContentLength(sbout.length());
PrintWriter out = new PrintWriter (response.getOutputStream());
// send the page back
out.print(sbout.toString());
out.close();   } }

Compiling

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:jbuilder3javalibclasses.zip;.;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) {
formName="";
}
else {

to:

String formName=request.getPathInfo();

if (formName==null) {
formName="/INDEX.HTML";
}
else {

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