Introduction

Ajax has rapidly become the latest darling of Web application developers. Web sites like Google Maps, Yahoo email, and other highly interactive Web sites utilize Ajax to make a rich interactive experience through a Web browser.

Ajax is not a spec, a framework, nor an API. It is a set of existing technologies consisting of JavaScript, XML, the Document Object Model (DOM), Cascading Style Sheets (CSS), and asynchronous HTTP requests to a Web server or servlet container. What Ajax allows you to accomplish with your Web applications is additional processing, manipulation, and formatting of data on the client, in this case the Web browser. The server, which can reside on the same host as the client, consists of a Web server or servlet container and a data store. The data could reside in a flat file or in a database. In the sample application discussed in this article, My Address Book, the data resides in a Derby database.

Some of the features of Derby demonstrated in this application are as follows:

• A peek at the upcoming XML support available in the Cloudscape 10.2 release
• Create and call a stored procedure, using a Java Database Connectivity (JDBC) Callable statement
• Use an EmbeddedDataSource to create connections
• Store image files as BLOBS in the database

This article discusses how to build a simple Address Book Web application using Cloudscape and Ajax. Some basic familiarity with databases and Ajax technologies is recommended to get the most from this article. To become familiar with Cloudscape and Ajax refer to the Resources section at the end of the article.

Back to top

Embedded applications

The word "embedded" in the context of an application implies components providing services or functionality to the application without the end user being responsible for managing, or even being aware of, the underlying sub-systems. The application "just works." A more general definition of the word "embedded", found in the Wiktionary, is "To be part of, and firmly, or securely surrounded; lodged solidly into."

The sample application embeds the database engine, Derby, and the servlet container, Jetty, within the application. Both Derby and Jetty are 100 percent Java implementations and are started by the application without the user having to start or stop additional processes or applications.

The Derby database engine and JDBC driver are contained in a single jar file, derby.jar. The Jetty Web server is also packaged in a single jar file, org.mortbay.jetty.jar. However, some additional jar files are required to use Jetty as a servlet container and to fulfill the logging requirements of Jetty.

The next section explains the architecture of the application and makes clear how an embedded solution is appropriate in certain Web application use cases.

Back to top

My Address Book - Architecture

The My Address Book application is a simple Web application with the following functionality:

• Add contact information for individuals
• Modify (edit) a contact
• Delete a contact
• Upload a photo of a contact
• Sort the list of contacts by first name, last name, or show only those contacts that have a photo stored in the database

A Model View Controller (MVC) approach is used in the design of the Web application. The view is provided by the HTML pages that utilize JavaScript and CSS. The controller is provided by a servlet that runs in the embedded Jetty Web server, and the Derby database acts as the back end and represents the model layer for the application.

The architecture is a little different than some MVC applications -- it also utilizes an applet and the model and controller are running in the same Java virtual machine (JVM) and on the same host.

Figure 1 shows the overall design of the application, and the next section goes into further detail.

Back to top

Huh? A browser hosting a JVM that runs a Web server and a database?

The numbers next to the arrows outline the flow of the application and the sequence involved in booting the Web server and the database engine.

1. An HTML page is requested from the browser that resides on the file system.
2. The startapplet.html page is opened in the browser, which starts an applet running inside the JVM in the Firefox browser. The applet, which now has a complete Java environment at its beck and call, starts up and configures the embedded Jetty Web server.
3. When the Start Cloudscape Ajax Demo button is pressed, a request is made to the Web server to return the next page, firstpage.html.
4. firstpage.html is served to the browser through an HTTP response.
5. When a username and password is submitted in the form on firstpage.html, a request is sent back to Jetty for processing.
6. Jetty creates an instance of the application class, DerbyDatabase, which boots the embedded Derby engine, creates the database (only once), and creates two tables and loads some rows.
7. The database selects a row from the CONTACTS table and passes back this value to the Web server.
8. The Web server serves the result to the client in the form of an HTML page, AddressBook.html, utilizing JavaScript and CSS in the process.

If you take a step back from the image for a moment, you'll understand why the word "embedded" is emphasized so much in the previous section. A single JVM is running inside the Firefox browser. This JVM allows the applet to start an embedded Java Web server that then boots an embedded Java database engine, Derby, all running in this single JVM.

Why would you want to do this? That question will be answered in greater detail, but for now here are some simple reasons:

• Embedded applications are easier to manage than traditional client/server applications.
• Asynchronous requests for data from the client (one of the neat things about Ajax) to the server require the HTTP protocol. Therefore, to make an asynchronous request for data (which in this case resides in the database) requires an HTTP server.

Back to top

Components of the application

The sample application uses the following software components and technologies:

• Derby database engine and JDBC driver
  • The data repository on the "server" side
• Jetty Web server and servlet container
  • Serves HTML pages and acts as a controller through a servlet for database requests
• Firefox browser and Java runtime environment (JRE)
  • Mozilla Firefox version 1.5.x utilizes the Java 1.5.x JRE to launch the JVM
• JavaScript
  • Used to make asynchronous requests to the Web server to retrieve the data through a controller servlet. Manipulates the returned data through the DOMParser. Also used for sorting data already on the client.
• CSS
  • Formats the content of the HTML pages

Back to top

Software requirements

The software described in this section is available for download at no cost and must be installed prior to running and installing the sample Web application.

• Mozilla Firefox 1.5.0.x or later, refer to the Resources section.
• Sun JRE, Version 1.5.0.x or later, refer to the Resources section.
• Windows operating system (the application is not compatible with Linux at this time)

Verify the JRE with Firefox:

• After downloading and installing Firefox and the JRE, test the installation of the JRE in the browser by visiting JAVA SOFTWARE for Your Computer.
• The output from verifying a correct installation using the latest version of the JRE at the time this article was written looks like this:

  We detected your Java environment as follows; Description Your Environment Java Runtime Vendor: Sun Microsystems Inc. Java Runtime Version 1.5.0_06

  
  
• This verification must succeed in order for the sample application to work. If it does not, and you downloaded and installed the correct version of the JRE, but the version of the JRE reported in the validation test is not 1.5.x or later, you may need to configure which JRE is used when running an applet in a browser.

  To do this, open the control panel and launch the Java icon. Select the Java tab, and click the View button in the Java Applet Runtime Settings section. Check to make sure the location is actually valid. In this example, one of the JREs has been uninstalled, but there is still a location entry that is invalid. Since you may have multiple JREs installed, you need to make sure the browser reports the correct JRE is used prior to installing the application.

• The Java control panel JRE applet settings used in this example look like this when the JRE validation test reports the JRE being used as version 1.5.0_06:

The sample application zip file:

• Download the Cloudscape_Ajax_Demo.zip file to your file system (refer to the Downloads section). All HTML, JavaScript, CSS, and Java source files are included as well as the Derby database engine and Jetty Web server class files.

Back to top

Install the application

1. Install the required software as listed above.
   
   
2. Confirm that the version of the JRE used in Firefox is the correct one as noted in the Software requirements section, "Verify the JRE with Firefox."
   
   
3. Unzip the file, Cloudscape_Ajax_Demo.zip. This creates a directory called Cloudscape_Ajax_Demo that contains the following subdirectories and files:
   • src
     • Java source files
   • licenses
     • License files for each library included in the application
   • cloudscape_ajax_webapp.zip
4. Unzip the file, cloudscape_ajax_webapp.zip, to the Mozilla Firefox home directory. Since the Jetty Web server is started from this directory, this is considered the "home" directory to serve the HTML pages from. On the machine used in this example, the full path to the directory where the Firefox executable resides is C:\projects\Ajax\Mozilla Firefox. Once unzipped, there will be a webapps/Ajax directory under your Mozilla Firefox home directory. This directory contains the applet jar file cloudscape_demo.jar (including all dependent libraries), JavaScript, CSS, and HTML files for the application.

   The applet jar file, cloudscape_demo.jar, contains these libraries:

   • derby.jar -- A 10.2.x snapshot version of the Derby database engine and embedded JDBC driver that provides XML capabilities. Derby snapshots are unsupported.
   • org.mortbay.jetty.jar -- The Jetty HTTP server and servlet container.
   • javax.servlet.jar -- The servlet API classes.
   • commons-logging.jar -- Logging classes required for the Jetty Web server.
   • xercesImpl.jar -- The Xerces parser -- required by Derby for XML support.
   • cos.jar -- An O'Reilly library for uploading files to a Web server used in the application (refer to the license directory for the acknowledgements and distribution restrictions on using this jar file.)
   • cloudscape.ajax package -- The application classes including the applet, a controller servlet, and classes to access the Derby database through the EmbeddedDataSource as well as an XML helper class. (The source for all of these classes is in the src subdirectory of the main zip file.)
   The classes contained in the above jar files were extracted and placed in a single jar file, cloudscape_demo.jar, because the initial application class needs access to some of the Jetty and logging classes contained in the above libraries. Instead of listing all jar files in the applet tag which would require signing each jar file, I extracted the classes from the other jar files and created a single self-signed jar file.

Back to top

Exploring the application

With the setup and prerequisites complete, start up the application and examine what goes on behind the scenes as you step through it. To help in understanding where each item discussed fits into the big picture, the steps in the architecture diagram from Figure 1 are referred to.

Launch the 1.5.0 Firefox browser, and open the startapplet.html file in the webapps/Ajax directory under the Firefox install directory. In this example, the full path to this file is C:\projects\Ajax\Mozilla Firefox\webapps\Ajax\startapplet.html.

If you have unzipped the cloudscape_ajax_webapp.zip file to the correct location, the first thing you should see when bringing up startapplet.html is the security window shown in Figure 3. Accept the digital signature from Susan Cline for the StartJetty application (the applet class) to proceed. To avoid this security warning when bringing up startapplet.html in the future, click the Always trust content from this publisher check box and select Run.

Note about the Security Warning: The application needs to read and write files on the client (in this case the client and server are the same host) and is started by an applet. The security model associated with applets normally does not allow this. By signing an applet, access to read and write files is permitted. This application makes use of a signed jar file to enable this.

Once you select Run in the security window, the status bar on Firefox should say Applet StartJetty started. This first page is an HTML file containing the applet that starts the embedded Jetty Web server.

The Applet

startapplet.html contains the HTML APPLET tag to launch an applet. The APPLET tag, shown in Listing 1, also requires the name of the Java class containing the applet and the jar file required by the applet be listed. From the Firefox browser menu select View and then Page Source to see the HTML source of this page.

The init method, which all Java classes extending the Applet class must override, and the startJetty method are shown in Listing 2, from the cloudscape.ajax.StartJetty Java source file. If you downloaded and unzipped the zip file, you can find this file in the Cloudscape_Ajax_Demo/src/cloudscape/ajax directory.

 
<APPLET code="cloudscape.ajax.StartJetty.class" codebase="."
width="1" height="1" name="StartJetty" 
archive="cloudscape_demo.jar" alt=""></APPLET>

 
public void init() {
  System.out.println("StartJetty: init() was called");
  setBackground(Color.white);
  startJetty();
}

private int startJetty() {
  int startStatus = 0;

  try {
    // Create the server
    server = new HttpServer();

    // Create a port listener
    SocketListener listener = new SocketListener();

    listener.setHost("localhost");
    listener.setPort(HTTP_SERVER_PORT);
    listener.setMinThreads(5);
    listener.setMaxThreads(250);
    server.addListener(listener);

    // Create a context 
    HttpContext context = new HttpContext();
    context.setContextPath("/Ajax/*");
    server.addContext(context);

    // Create a servlet container
    ServletHandler servlets = new ServletHandler();
    context.addHandler(servlets);

    // Map a servlet onto the container
    servlets.addServlet("ControlServlet", "/ControlServlet/*",
					"cloudscape.ajax.ControlServlet");

    // Serve static content from the context
    String home = System.getProperty("jetty.home", ".");
    context.setResourceBase(home + "/webapps/Ajax/");
    context.addHandler(new ResourceHandler());

    // Start the http server
    server.start();
    startStatus = STARTED_OK;
			
    } catch (java.net.BindException addressUsedExcept) {
     System.out.println("Jetty server has already been started on port"+HTTP_SERVER_PORT);
      startStatus = STARTED_ALREADY;
    } catch (org.mortbay.util.MultiException multiServerExcept) {
     System.out.println("Jetty server has already been started on port"+HTTP_SERVER_PORT);
    startStatus = STARTED_ALREADY;
    } catch (Exception e) {
      e.printStackTrace();
      startStatus = NOT_STARTED;
  }
  return startStatus;
}

The startJetty method sets the host and port for the HTTP server, sets up a servlet handler, creates a context for the application, registers the servlet for the application, and finally starts the Web server. The init method of the applet, which is called when the applet is started, calls the startJetty method.

These are steps 1 and 2 in Figure 1.

From the startapplet.html page, click the Start Cloudscape Ajax Demo button. This fetches the next page, firstpage.html, from Jetty (steps 3 and 4 in Figure 1), prompting for a username and password.

At this point you can either log in with the username of cloudscape and password of ajax, or create a new user.

Clicking either the Login to Address Book or Create New User button from firstpage.html does the following (steps 5 through 8 in Figure 1):

• Sends the username and password off to a servlet residing on the Jetty Web server. The servlet creates an instance of an application class that boots the Derby database engine and creates a Derby database. Two tables are created -- a USERS table and a CONTACTS table. A sample row is inserted in both tables. Subsequent invocations of the application boot the Derby engine, but do not recreate the database.
• Next, the user is authenticated if the username already exists, or if it is a new username, a row is inserted in the USERS table. The next page that appears is the main page of the application, AddressBook.html.

The servlet

Look at the init method of the servlet class, ControlServlet.java, that is also available as part of the download.

The method that is called when a servlet is loaded for the first time by the servlet container is the init method. This method creates an application class, DerbyDatabase and calls its initialize method. As described above, this method boots the database engine and creates the database and tables if not already created.

 
public void init() throws ServletException {
  derbyDB = new DerbyDatabase();
  derbyDB.initialize();
}

A partial listing of the initialize method for the DerbyDatabase class shows how to create an EmbeddedDataSource, create the database using the setCreateDatabase method, specify the database name, and create a connection to the database.

 
public boolean initialize() {
  Connection conn = null;
  boolean success = true;
  if (isInitialized) {
    return success;
  }

  if (ds == null) {
    ds = new EmbeddedDataSource();
    ds.setCreateDatabase("create");
    ds.setDatabaseName("DerbyContacts");
    Statement stmt = null;
    try {
      conn = ds.getConnection();
    } catch (SQLWarning e) {
       ...

Now, where were we? You just logged into the application and verified your username and password, or created a new username and password and inserted it into the Derby database. Successful in this endeavor, the main page of the application, AddressBook.html, should appear as below.

From this page, a variety of tasks can be performed, such as adding, editing or removing a contact, or sorting the list of contacts. First, add another contact. The image below shows what the page looks like after the Add button is clicked and the text fields are filled out to add a new contact.

Now click the Add Contact button. If all of the fields have been filled out, a JavaScript pop-up box asks if you would like to upload a picture of this contact. If any of the fields do not have a value in them, a JavaScript function alerts you to this fact and prompts you to complete all fields prior to submitting the page. When the Add Contact button is clicked a lot goes on behind the scenes.

JavaScript

When clicked, the Add Contact button utilizes JavaScript's event handling capabilities by specifying the insertContact function to be called. The first thing this function does is ensure that all fields of the form have been filled out. If they have, a variable is created and constructed to contain all the values included in the form. Next, another JavaScript function is called, makeDerbyRequest that has this variable as one of its parameters. The makeDerbyRequest function is essentially a control point for making an asynchronous request to the servlet running in the Jetty Web server.

Listing 5 shows the html for the Add Contact button and Listing 6 shows some of the code for the insertContact and makeDerbyRequest functions in JavaScript.

<INPUT type="button" name="add_button" value="Add Contact"
onclick="insertContact()" class="buttons">

var request = false;
   
try {
  request = new XMLHttpRequest();
} catch (trymicrosoft) {
  try {
    request = new ActiveXObject("Msxml2.XMLHTTP");
  } catch (othermicrosoft) {
    try {
      request = new ActiveXObject("Microsoft.XMLHTTP");
    } 
    catch (failed) {
     request = false;
    }  
  }
}

function insertContact() {
  var isFormValid = validateForm("add_contact_form");
  if (!isFormValid) {
    showErrors("add_contact_form");
  }	
  else {
    var parameters = "&firstname=" + document.add_contact_form.firstname.value ...
    ...
    makeDerbyRequest("insert", parameters);
  }

  return isFormValid;
}

function makeDerbyRequest(databaseAction, parameters, selectedOption) {
  var url = "/Ajax/ControlServlet/?querytype=";
  if (databaseAction == "show") {
    url = url + "show";
    request.open("GET", url, true);
    request.onreadystatechange = updateContactList;
    request.send(null);
  }
  else if (databaseAction == "login") {
    url= url + "login" + parameters;
    request.open("GET", url, true);
    request.onreadystatechange = verifyLogin;
    request.send(null);
  }
  else if (databaseAction=="insert") {
   url = url + "insert"+ parameters; 
   request.open("GET", url, true);
   request.onreadystatechange = showInsertResults;
   request.send(null);
  }
    ...
    ...

The makeDerbyRequest function is called with a databaseAction parameter of insert and a list of name or value pairs that represent all of the contacts attributes, like first name and last name. The request variable in the makeDerbyRequest function is initialized and represents an XMLHttpRequest object. The XMLHttpRequest object is an extension to the Web browser DOM used in Ajax to asynchronously receive XML or text from an HTTP server. The callback function for the XMLHttpRequest objects' onreadystatechange property, showInsertResults, is examined later.

Servlet requests and database access

Take a look at what happens on the server side once the XMLHttpRequest object sends off the request to the Jetty Web server at the URL of http://localhost:8095/Ajax/ControlServlet/?querytype=insert, plus the name value pairs that correspond to the form values when you added your new contact.

The doGet method of the ControlServlet class shown below first parses the incoming parameters and then if the querytype parameter is set to insert or update, creates a helper class, ContactXMLBeanthat is essentially a JavaBean containing the contact's information. Finally, the ContactXMLBean object is passed to the DerbyDatabase objects insertContact method.

public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

  String queryType = "";
  String user = "";
  String password = "";
  String newuser = "";
  queryType = request.getParameter("querytype");
  ...

  String returnXML = "";

  ...

  if (queryType != null) {
    if (queryType.equals("show")) {
      returnXML = derbyDB.getContacts();
    } 
    else if (queryType.equals("insert") || queryType.equals("update")) {
      String firstname = request.getParameter("firstname");
      String lastname = request.getParameter("lastname");
      String email = request.getParameter("email");
      String phone1 = request.getParameter("phone1");
      String address = request.getParameter("address");
      String city = request.getParameter("city");
      String state = request.getParameter("state");
      String zip_code = request.getParameter("zip_code");
      String phone2 = request.getParameter("phone2");
      String bday = request.getParameter("bday");

      ContactXMLBean myContact = new ContactXMLBean(address, bday, city, \
        email, firstname, lastname, phone1, phone2, state, zip_code);

      if (queryType.equals("insert")) {
        returnXML = derbyDB.insertContact(myContact);
      }
      ...

      response.setContentType("text/plain");
      PrintWriter out = response.getWriter();
      out.write(returnXML);
      out.flush();
      out.close();
}

Before you examine the DerbyDatabase insertContact method, you need to look at the CREATE TABLE Data Definition Language (DDL) statement for the CONTACTS table and the stored procedure that was created when you first booted up the Derby engine in the initialize method of the DerbyDatabase class.

The CONTACTS table uses an XML data type that is unsupported in the current version of Cloudscape, but will be supported in the 10.2 release. The operations used in this demonstration consist of reading in XML data and retrieving it.

Note: The official 10.2 version of Cloudscape and Derby is not currently available. However, an alpha snapshot version can be downloaded from the Apache Derby Web site. The Derby database classes contained in this demo are an alpha version of 10.2 and should not be used in a production environment.

To use Derby or Cloudscape to build your own applications, download the current release, Version 10.1, from either IBM Cloudscape or Apache Derby, listed in the Resources section.

CREATE TABLE APP.CONTACTS(ID INT CONSTRAINT CONTACT_PK PRIMARY KEY, XML_COL XML)

Using stored procedures in Derby is a three step process: Creating a public class with at least one static method in it, executing some SQL to register the stored procedure, and calling it. The DerbyProcedures class satisfies the first requirement. Things to note about this method are the use of the default connection and setting the autocommit mode to false. By default it is set to true. This procedure gets the maximum value of the ID column currently in the CONTACTS table. It then increments this value and inserts it into the new row in two places -- in the ID column and as part of the XML column XML_COL. The reason for storing the ID in the XML column becomes apparent a little bit later when you examine what is returned to the client when a select of the CONTACTS table is made and the contents are sent to the client and parsed through JavaScript.

The XMLPARSE SQL function is one of the new XML operators used to insert XML into a column that is coming in the 10.2 release of Cloudscape.

public class DerbyProcedures {
  public static void insertContact(int[] rowNum, String myXMLContact)
	throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:default:connection");
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    int currentValue = 0;

    ResultSet rs = stmt.executeQuery("select max(id) from APP.CONTACTS");

    while (rs.next()) {
      currentValue = rs.getInt(1);
    }
    rs.close();

    currentValue++;
    stmt.close();
    String sql = "insert into APP.CONTACTS (id, xml_col) values ("
      + currentValue + ", xmlparse(document '<contact><id>"
      + currentValue + "</id>" + myXMLContact + "</contact>' preserve whitespace))";
    stmt = conn.createStatement();
    int numRows = stmt.executeUpdate(sql);
    if (numRows > 0) {
      rowNum[0] = currentValue;
    } else {
      rowNum[0] = 0;
    }
    stmt.close();
    conn.commit();
    conn.close();
  }
}

The next step in creating the stored procedure is to issue the CREATE PROCEDURE DDL to register the procedure in the database. One of the differences between SQL functions and stored procedures in Derby is that functions cannot modify data. Also, stored procedures support IN, OUT, and INOUT parameters. The procedure takes an IN parameter that is the new contact represented as a Java String. The OUT parameter returns the row number just inserted into the database.

create procedure InsertXMLContact(OUT rowNum integer, IN contact VARCHAR(500)) 
parameter style java language java external name
'cloudscape.ajax.DerbyProcedures.insertContact'

Now you can call the stored procedure. This method in the DerbyDatabase class performs this step using the JDBC CallableStatement class and the CALL SQL syntax. The integer returned from this method corresponds to the value of the id column just inserted in the table.

public String insertContact(ContactXMLBean myContact) {
  // the id of the row just inserted
  int idNum = 0;

  try {
    Connection conn = ds.getConnection();
    CallableStatement cstmt = conn.prepareCall("call InsertXMLContact(?,?)");
    cstmt.setString(2, myContact.toXMLString());
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.execute();
    idNum = cstmt.getInt(1);
    cstmt.close();
    conn.close();
  } 
  catch (SQLException sqlExcept) {
    sqlExcept.printStackTrace();
  }
  if (idNum > 0) {
    return String.valueOf(idNum);
  } else {
    return String.valueOf(0);
  }
}

The last step that occurs after we pushed the Add Contact button is to process the result sent back from the servlet to the client. Since I've shown a lot of stuff since we pushed the Add Contact button on the Web page, I'll summarize what we've seen.

The JavaScript onclick() event handler called the JavaScript function, insertContact(). This function verified that all fields contained values prior to further processing. If they did contain values then the JavaScript function, makeDerbyRequest was called. This function sent off a request to the servlet residing on the Jetty Web server containing the fields in the form. The showInsertResults callback function was registered with the XMLHttpRequest objects onreadystatechange property.

On the server side the servlet parsed the parameters and sent off a request to the Derby database to insert the contact into the table. The row was inserted into a table which utilized the xml datatype and a stored procedure was invoked using the JDBC Callable interface. After inserting the contact into the table, the database returned the value of the id column of the row just inserted.

Now we can see the callback function on the client, showInsertResults.

function showInsertResults() {
  if (request.readyState == 4) {
    if (request.status == 200 ) {
     var response = request.responseText;
       if (response != "0") {
         confirmAddPhoto(response);						
       }
       else {
         alert("There was a problem inserting the contact.");
       }
     }
     else if (request.status == 404) {
      alert("Request URL does not exist");
     }
     else {
      alert("Error inserting data, please try again.");
     }	
   }
}

This function shows a fairly standard way of processing an asynchronous request; checking the readyState and status to output the returned value from the Web server only when the response is complete and has a status of "OK", or 200.

Whew! We've added a contact to our Address Book application and seen some of the work done behind the scenes using JavaScript and the XMLHttpRequest object. Now let's upload a photo of my friend Martin in response to the popup that appeared after I added him.

The next page, AddPhoto.html is a standard file input field that lets you browse to a file on the file system. After browsing to an image file click the Upload to Cloudscape button. This does exactly what it says - it uploads the image to the Derby database.

The SQL DDL for the CONTACT_PHOTO table is shown below.

CREATE TABLE APP.CONTACT_PHOTO
(id int constraint CONTACT_FK references APP.CONTACTS(id),
 fname varchar(30), 
 lname varchar(30), 
 filename varchar(30), 
 picture BLOB(150000), 
 filesize int)
);

The insertPhoto method in the DerbyDatabase application class uses a PreparedStatment and inserts the photo file as a BLOB into the database. The snippet below has been formatted for readability with a line break, but the actual source does not include the '\' character.

public int insertPhoto(int id, String fname, String lname, String filename,
		File photoFile) {
  int numRows = 0;
  String sql = "insert into APP.CONTACT_PHOTO (id, fname, lname,
  filename, picture, filesize) \
    values (?,?,?,?,?,?)";

  Connection conn;
  try {
    conn = ds.getConnection();
    PreparedStatement prepStmt = conn.prepareStatement(sql);
    prepStmt.setInt(1, id);
    prepStmt.setString(2, fname);
    prepStmt.setString(3, lname);
    prepStmt.setString(4, filename);

    InputStream fileIn = new FileInputStream(photoFile);
    prepStmt.setBinaryStream(5, fileIn, (int) photoFile.length());
    prepStmt.setInt(6,(int) photoFile.length());
    numRows = prepStmt.executeUpdate();
    fileIn.close();
    prepStmt.close();
    conn.close();
  } catch (SQLException sqlExcept) {
    SQLExceptionPrint(sqlExcept);
  } catch (Exception e) {
    e.printStackTrace();
  }
  return numRows;
}

The id for the CONTACT_PHOTO table was originally passed on the client side through the response from the server to the JavaScript function, confirmAddPhoto function. This function then passed it on to the next page, AddPhoto.html that was eventually processed by the servlet that then called the insertPhoto method shown above.

Back to top

Huh? A browser hosting a JVM that runs a Web server and a database? Part II

Earlier, a few reasons were listed for why an embedded architecture for a Web application serves a purpose in certain use cases. A few additional reasons why this is desirable are:

• Ability to store and access information in a Derby database through a Web application without network connectivity.
• Web browsers are a familiar user interface for novice users.
• Mobile users, for instance a sales force, could add and update contact information without Internet connectivity.
• A future extension for an application of this kind would be to synchronize with a remote server when connectivity becomes available.

Back to top

Exploring the application - Part II

In the Exploring the application section the focus was mainly on the communication between the client and server and the overall architecture. For Part II, you will look at how the client processes the results retrieved from the database when the Show All button is pressed and whenever the main page of the application is accessed. Also, you will briefly be shown some of the JavaScript used to sort the list by first name or last name.

To see what happens when the contacts are returned from the database, you need to briefly look back at how they were inserted into the database. Whenever an insert is made into the CONTACTS table the value representing the contact itself -- the name value pairs, such as first name or last name -- needs to be converted into the Derby XML data type prior to being inserted. The helper class ContactXMLBean has a utility method, toXMLString that wraps the bean in XML elements. Refer to Listing 11 to see the toXMLString method being invoked during the parameter setting for the CallableStatement, when you inserted your new contact.

public String toXMLString() {
  return "<firstname>" + firstname + "</firstname><lastname>" 
  + lastname + "</lastname><email>" + email + "</email><phone1>" 
  + phone1 + "</phone1><address>" + address + "</address><city>" 
  + city + "</city><state>" + state + "</state><zip_code>" 
  + zipcode + "</zip_code><phone2>" + phone2 + "</phone2><bday>" + bday + "</bday>";
}

The method on the server side, the JDBC call to the database that selects all contacts from the table, is shown in Listing 16.

public String getContacts() {
  StringBuffer sbuf = new StringBuffer(2000);
  try {
    Connection conn = ds.getConnection("APP", "APP");
    Statement stmt = conn.createStatement();
    sbuf.append("<Results>");
    ResultSet rs = stmt.executeQuery("SELECT XMLSERIALIZE(XML_COL AS \
     VARCHAR(500)) from APP.CONTACTS");
    while (rs.next()) {
      sbuf.append(rs.getString(1));
    }
    sbuf.append("</Results>");
    rs.close();
    stmt.close();
    conn.close();
  } catch (SQLException sqlExcept) {
    sqlExcept.printStackTrace();
  }
  return sbuf.toString();
}

The XMLSerialize SQL function is one of the new functions that will be available in the 10.2 release of Cloudscape. It is not currently supported. Notice that the XML is being cast to a VARCHAR(500). Since VARCHAR is a supported data type in Cloudscape 10.1, if you would like to modify this application to work with the current release of Cloudscape, all you really need to do is define the APP.CONTACTS table with the XML_COL as type VARCHAR(500), continue to use the helper ContactXMLBean class' toXMLString method, and change the select to be the following:

SELECT XML_COL from APP.CONTACTS;

If only one row is in the APP.CONTACTS table the select output would appear as shown in Figure 17.

<firstname>Susan</firstname>
<lastname>Cline</lastname>
<email>susanc@mycorp.com</email>
<phone1>510 589-8888</phone1>
<address>300 East 2nd Street</address>
<city>Oakland</city>
<state>California</state>
<zip_code>98654</zip_code>
<phone2>415 703-6345</phone2>
<bday>July 15</bday>

The getContacts method returns more than just a result set in the form of a string, though. See how it adds a <Results> root tag around the entire result set? This root element is required to make a well-formed XML document, which is a requirement when parsing XML. This leads to the next topic of parsing the returned results, the contacts, on the client to display them.

Parsing and displaying the contacts

Every time the Show All button is clicked, or the AddressBook.html page is loaded, the contacts are retrieved from the database asynchronously, parsed, and then displayed on the page.

To invoke a JavaScript function when the page is loaded, an event handler for the onload event needs to be specified. The HTML BODY tag taken from AddressBook.html shown below associates the OnPageLoad function with the onload event, and is called whenever the page is loaded.

<BODY onLoad="OnPageLoad(queryStringText)" id="thebody">

The OnPageLoad function calls the makeDerbyRequest function with a parameter of show. You've looked at the makeDerbyRequest function before. The interesting part of Listing 18 is the callback function, updateContactList.

function makeDerbyRequest(databaseAction, parameters, selectedOption) {
  var url = "/Ajax/ControlServlet/?querytype="
  if (databaseAction == "show") {
    url = url + "show";
    request.open("GET", url, true);
    request.onreadystatechange = updateContactList;
    request.send(null);
  }
  ...
 function updateContactList() {
   if (request.readyState == 4) {
     if (request.status == 200 ) {
     // the servlet is returning a string, so I need to 
     // use responseText vs responseXML
     var response = request.responseText;
     var parser = new DOMParser();
     // this turns the doc into an xml doc so it can be parsed.
     var doc = parser.parseFromString(response, "text/xml");

     // The xml returned from the server is of the format:
     //<Results><contact><firstname>Susan</firstname><lastname>Cline</lastname>
     //<email>susan@yahoo.net</email>
     //<phone1>510 547 -8888</phone1><address>1569 Balboa</address><city>Oaktown</city>
     //<state>California</state><zip_code>94618</zip_code><phone2>510 774-6345</phone2>
     //<bday>July 15</bday></contact></Results>
    				
     var Results = doc.getElementsByTagName("Results")[0];
     if(!Results.getElementsByTagName("contact")[0]) {
       alert("No Contacts found - please add one.");
       return;
     }
    		
     // remove existing rows from the select list and populate only with the 
     // new ones retrieved
     var theList = document.getElementById("select_contacts");
     theList.length = 0;
    		
     // a collection of all contact elements wrapped by the <Results> tag
     var allContacts = Results.getElementsByTagName("contact");
     var contactOutput = "";
     var columns;
     var option;
     var txt;
     var optionValueAttribute;
     var optionValue;
     var optionId = "";

     // iterate through the contacts found
     for (var j = 0; j < allContacts.length; j++) {
      // columns represents all of the children of the contact
      columns = allContacts[j].childNodes;
      for (var i = 0; i < columns.length; i++) {
        if (columns[i].firstChild) {
          // extract the unique integer value but don't include it 
          // as part of the output string
          if (i == 0) {
          // this is the value generated from Derby as the id value
            optionValue = columns[0].firstChild.nodeValue;
          }
          else {
            // concatenate all the rest of the attributes
	    contactOutput = contactOutput + " " + columns[i].firstChild.nodeValue;
	  }
	}
      }
      // add a new option element to the select list
      option = theList.appendChild(document.createElement('OPTION'));
      // assign the unique integer for each contact as the value of the select option
      option.value = optionValue;
      // create a text node that contains the attributes of the contact
      txt = document.createTextNode(contactOutput);
      // append the text to the <option>
      option.appendChild(txt);
      contactOutput = "";
    }
  }
  else if (request.status == 404) {
    alert("Request URL does not exist");
  }
  else {
    alert("Error fetching data, please try again.");
  }	
 }	
}

The first thing to notice about the udpateContactList function is the use of the Mozilla-specific object, the DOMParser. It is used to parse either XML or text on the client -- in this case to parse the string returned from the server. The parseFromString function allows you to parse the string into a DOM tree.

Next, the document returned by the DOM tree is parsed using the getElementsByTagName to look for the Results tag, and then it looks for the first contact tag. If it does not find at least one contact element, an alert is given and the function returns with no further processing.

If a contact is found, then the HTML select list where the contacts are displayed on the AddressBook.html page is cleared by setting the length of the select list to zero.

The next section of code, the for loop, first retrieves all the childNodes of the first contact, when the variable j is initialized to zero. Then all of these childNodes are iterated through with the first node, the id node, being set to the variable optionValue. The optionValue variable is used as the unique identifier for each HTML select list option so that individual options, representing a single contact, can be manipulated on the client at a later time -- or if a database update needs to be made. If the node being processed in the inner for loop is not the firstChild, then each value of the node is concatenated with the previous values in the contactOutput variable.

Jumping outside the inner for loop to the outer for loop shows how to add a child node, an OPTION node, to the select list and then assign the value of the option with the value contained in the optionValue variable as mentioned above. Next, the contactOutput variable is assigned to a text node that is appended to the OPTION node and then re-initialized to an empty string for use in the next iteration of the outer for loop.

All rows returned from a select of the database are processed this way, which allows for the dynamic creation of the HTML select list every time the Show All button is clicked or the page is loaded.

Sorting the contacts on the client

You just looked at the JavaScript to retrieve the contacts and display them in the select list. Now look at how you can sort and manipulate them once they are on the client. More contacts have been added to make this a little bit more interesting. Figure 6 shows all of the contacts in My Address Book in the order they were added. Only about two or three have photos associated with them.

To see the list of contacts sorted by first name, click the Sort by Firstname button.

Sorting by last name works too.

The JavaScript function used for sorting by first name or last name is the sortSelect function. This function takes two parameters -- the first is the HTML select list to sort, and the second is the comparison function.

function sortSelect (select, compareFunction) {
  var options = new Array (select.options.length);
  for (var i = 0; i < options.length; i++) {
    options[i] = 
    new Option (select.options[i].text, select.options[i].value,
                select.options[i].defaultSelected, select.options[i].selected);
  }
  options.sort(compareFunction);
  select.options.length = 0;
  for (var i = 0; i < options.length; i++) {
    select.options[i] = options[i];
  }
}

This function creates a new JavaScript Array object that is the size of the select list passed in as the first parameter. All of the options from the select list are copied to the new Array by creating a new Option object from each of the old options. The Array's sort method is used with the optional function parameter used as the algorithm to sort the Array. Then, the select list is cleared and the select list options are set to the values contained in the sorted array. The function used to sort the array is the compareFunction that was passed into the function when it was called.

In the case when the Sort by Lastname button is clicked, the call to the sortSelect function looks like the following:

<INPUT type="button" id="lastname_sort_button" name="lastname_sort_button" 
value="Sort by Lastname" onclick="sortSelect(document.select_form.contacts,sortLastName)" 
class="buttons">

The sort function used for the Array object is sortLastName, which is shown in Listing 20.

function sortLastName(option1, option2) {			
  var lastName1 = extractLastName(option1).toUpperCase();
  var lastName2 = extractLastName(option2).toUpperCase();
			
  return lastName1 < lastName2 ? -1 :
  lastName1 > lastName2 ? 1 : 0;

}

This function first parses the second word from the option (the first word being the first name), changes it to upper case, and then compares the two strings and returns a -1, 1, or 0.

Back to top

Summary

This article has shown how to use an embedded database engine and Web server in an Ajax-enabled Web application where the client and server reside on the same host.

Using JavaScript and the XMLHttpRequest object, contacts from the My Address Book application were retrieved from a Derby database through a servlet running on the Jetty Web server.

Embedding the database and Web server in a Web application offers the benefit of a familiar user interface to access persistent data with the possibility of extending the application to sync with a remote server when connectivity exists.

For more complex applications, the architecture of this application would need to be modified -- but as a simple demonstration of using Ajax with Derby it serves as a foundation for understanding the ease of embedding Derby in Web applications.

Back to top

Acknowledgements

The idea for this application is based upon a demo created by Francois Orsini, presented at ApacheCon in December of 2005. Francois is an Apache Derby contributor. His application also embeds Derby in a Firefox browser, but does not make use of an embedded Web server. To download Francois' demo, refer to the Resources section.

Back to top

Download

Information about download methods

Resources

Learn

• Cloudscape: "Cloudscape Version 10: A technical overview" (developerWorks, August 2004): Find an excellent starting point to learn about the impressive capabilities of the Cloudscape database and its architecture.
  
  
• Cloudscape: The Cloudscape FAQ, "A compendium of Cloudscape frequently asked questions" (developerWorks, August 2004): Provides great information for new users of Cloudscape as well as more experienced users.
  
  
• Cloudscape information center: Find all of the Cloudscape documentation online.
  
  
• Ajax: "Mastering Ajax, Part 1: Introduction to Ajax" (developerWorks, December 2005): Read the excellent series by Brett McLaughlin on Ajax to learn the basics of Ajax and discover the meaning of terms such as the XMLHttpRequest object.
  
  
• Jetty Web Server: Learn more about this 100 percent Java HTTP server and servlet container at jetty.mortbay.org.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Mozilla Firefox: Download Mozilla Firefox version 1.5.x or higher.
  
  
• Java Runtime Environment: Download Java Runtime Environment Version 5, update 6 or later.
  
  
• Cloudscape 10.1: Download the currently supported release of Cloudscape Version 10.1.
  
  
• Apache Derby: Download the source or binary version of Derby. Official releases, snapshot jars, and nightly builds are available.
  
  
• Francois Orsini's demo: This demo also embeds Derby in the Web browser, and the idea for this article and sample application was derived from Francois' demo.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Embedded applications
• My Address Book - Architecture
• Huh? A browser hosting a JVM that runs a Web server and a database?
• Components of the application
• Software requirements
• Install the application
• Exploring the application
• Huh? A browser hosting a JVM that runs a Web server and a database? Part II
• Exploring the application - Part II
• Summary
• Acknowledgements
• Download
• Resources
• About the author
• Comments

Next steps from IBM

Create rich Internet applications using WebSphere Portal Express Web 2.0 technologies.

---

• Try: WebSphere Portal Express
• Article: Get started using Ajax and WebSphere Portal Express
• Tutorial: Quickly build a an Ajax-enabled WebSphere Portal Express Web application
• Demo: Architecture, design, and construction using the IBM Rational Software Delivery Platform: AJAX
• Buy: WebSphere Portal Express

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers