People love Ajax applications so much that they are willing to use them instead of their desktop equivalents. The only problem occurs when they fail to have network access. This is when an offline feature is necessary. Apache Derby is a great option for enabling offline access to Ajax-powered applications. Learn how to use Apache Derby as a local data store that can be used to take your Ajax application offline.

Share:

Michael Galpin (mike.sr@gmail.com), Software architect, eBay

Michael Galpin's photoMichael Galpin has been developing Java software professionally since 1998. He currently works for eBay. He holds a degree in mathematics from the California Institute of Technology.



23 September 2008

Also available in Japanese

Prerequisites and system requirements

This article uses Apache Derby as a client-side database. Derby can be downloaded separately, but is also bundled with Java™ 6 and is called Java DB. In this article, we will use Derby V10.4.1.3 with Java 5 and 6. We will leverage Java applets to enable Derby on the browser and access the applet using JavaScript. Thus, familiarity with Java applets and JavaScript is highly recommended. Derby lets us use normal JDBC and SQL, so familiarity with those is assumed (see Resources).


Applets with Derby

Apache Derby is an embedded database that can be used by any Java application. It is such a useful tool that it is bundled with Java Platform, Standard Edition (Java SE) V6. There are countless uses of an embedded database, but many people are not aware of some of the client-side functionality that is possible with Derby. We will explore some of these possibilities by building a simple address book application. We will start with a Java applet that will make use of Apache Derby and eventually move this to an Ajax-based application that uses Derby as a cache.

Data access

In an article about a database technology, it makes sense to start with the database code. First, let's define a simple table schema to store our contacts in. This is shown below.

Figure 1. The Contact table
The Contact table

You could imagine a much more complicated schema for contacts, with things like multiple phone numbers, addresses, etc. This, however, will do just fine for our application. Of course, we will have a Java class corresponding to the contact table. In this case, we will follow the Active Record pattern and wrap a database row with a class capable of all database operations. Its code is shown below.

Listing 1. The Contact class
public class Contact {
    
    private Integer id;
    private String firstName;
    private String lastName;
    private String email;
    
    public static List<Contact> getContacts(String clause){
        if (clause == null)
            clause = "";
        String sql = SELECT_SQL + clause;
        Connection conn = DbManager.getConnection();
        List<Contact> contacts = new ArrayList<Contact>();
        try {
            ResultSet cursor = conn.createStatement().executeQuery(sql);
            while (cursor.next()){
                Contact c = new Contact();
                c.setId(cursor.getInt(1));
                c.setFirstName(cursor.getString(2));
                c.setLastName(cursor.getString(3));
                c.setEmail(cursor.getString(4));
                contacts.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return contacts;
    }
    
    public static List<Contact> getAllContacts(){
        return Contact.getContacts(null);
    }
    
    public static Contact getContact(String clause){
        List<Contact> results = Contact.getContacts(clause);
        if (results == null || results.size() != 1){
            return null;
        }
        else return results.get(0);    
    }
    
    public void save(){
        if (id == null)
            insert();
        else
            update();
    }
    
    public void delete(){
        Connection conn = DbManager.getConnection();
        String sql = "delete from Contact where id=?";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
            System.out.println("Deleted contact id="+id);
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    private void insert() {
        Connection conn = DbManager.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(INSERT_SQL, 
                    PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, firstName);
            ps.setString(2, lastName);
            ps.setString(3, email);
            ps.executeUpdate();
            ResultSet autoRs = ps.getGeneratedKeys();
            if (autoRs.next()){
                id = autoRs.getInt(1);
            }
            System.out.println("Contact saved new id = " + id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    private void update(){
        Connection conn = DbManager.getConnection();
        try{
            PreparedStatement ps = conn.prepareStatement(UPDATE_SQL);
            ps.setString(1, firstName);
            ps.setString(2, lastName);
            ps.setString(3, email);
            ps.setInt(4, id);
            ps.executeUpdate();
            System.out.println("Contact updated with id="+id);
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

This class does a lot, but everything is pretty straightforward. It has fields corresponding to the database columns and the usual accessors (getters and setters) for each field. It has methods for doing all of the CReate Update Delete (CRUD) operations and the SQL queries to go with them. The methods for querying the contacts are static methods, so you can do things like Contact.getAllContacts(), for example. The operations for save and delete are instance methods, so you call them on an individual contact. The queries are not shown here, as they are standard SQL. The class also has the usual getters and setters, but they are not shown for the sake of brevity (see Downloads for the full code listings). This class forms the basis of client-side storage with Derby. At first, we will use it a as part of an applet UI, but later the applet will just be used to make it available to JavaScript. Notice that for each method, we make a call to a utility class, DbManager, to get a connection.

Listing 2. The DbManager class
package org.developerworks.addressbook;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbManager {
    static{
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } 
    }
    
    public static Connection getConnection(){
        try {
            Connection conn = DriverManager.getConnection("jdbc:derby:contacts;
create=true");
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

This is where all of the Derby-specific code lives. OK — so it is actually not very specific to Derby. We are using an embedded database, but we get to treat it like it is just another database being accessed through JDBC. We use the EmbeddedDriver for our driver class in the static initializer of the class. Also for the getConnection method, we add the extra parameter create=true to tell Derby to create the database if it does not already exist. Notice there is no need for user names or passwords as you might usually see with JDBC — one benefit of using an embedded database. You saw all of the data-access code. It looks a lot like database code you would see in any application; the database just happens to be an embedded Derby database. You could imagine creating a different model for your application so you could store data specialized to your application, but on the client side. Let's look at a simple application that leverages the data-access code shown in Listing 2.

Applet UI

We will start by using a very simple applet that will make use of our data-access code.

Listing 3. Applet UI code
public class AddressBookApplet extends JApplet {
    private static final long serialVersionUID = 1L;    
    private static final String[] columns = { "First Name", "Last Name", "Email", "Id"};
    
    public AddressBookApplet() {
        this.setLayout(new GridLayout(1,0));
        JPanel panel = buildUi();
        this.add(panel);
    }

    public Contact addContact(String firstName, String lastName, String email) {
        Contact c = new Contact();
        c.setFirstName(firstName);
        c.setLastName(lastName);
        c.setEmail(email);
        c.save();
        return c;
    }
    
    public void deleteContact(Integer id){
        Contact c= new Contact();
        c.setId(id);
        c.delete();
    }

    public Object[][] loadContacts() {
        List<Contact> book = Contact.getAllContacts();
        Object[][] contacts = new Object[book.size()][4];
        int cnt = 0;
        for (Contact contact : book){
            contacts[cnt++] = contact.toArray();
        }
        return contacts;
    }

    private JPanel buildUi() {
        JPanel panel = new JPanel();
        panel.setLayout(new BoxLayout(panel, BoxLayout.Y_AXIS));
        
        final DefaultTableModel dataModel = createDataModel();
        final JTable table = createTable(dataModel);
        
        //Lots of Swing/UI Code omitted for brevity    
      }
    
    private JTable createTable(final DefaultTableModel dataModel) {
        final JTable table = new JTable(dataModel);
        table.setPreferredScrollableViewportSize(new Dimension(500, 70));
        table.setFillsViewportHeight(true);
        return table;
    }

    private DefaultTableModel createDataModel() {
        Object[][] contacts = loadContacts();
        final DefaultTableModel dataModel = new DefaultTableModel(contacts, columns);
        return dataModel;
    }
}

Most of this code is typical Swing code for building a UI. All of the UI code is done in private methods at the bottom of the class. The buildUi method coordinates the Swing component creation, but most of those are omitted for brevity. More interesting are the three public methods (besides the constructor): addContact, deleteContact, and loadContacts. Each of these is essentially a wrapper around the data-access code we developed earlier. We don't really want to use an applet for the UI of our final application, but it provides an easy way to test out the code. If you are using Eclipse, you can simply right-click on the Applet class and select Run As > Java Applet.

Figure 2. Run as Java applet in Eclipse
Run as Java applet in Eclipse

Eclipse does not do any real magic here, it simply uses the JDK's Applet Viewer. If you are not using Eclipse, you can invoke this on the command line. Either way, you should get something that looks like Figure 3.

Figure 3. Running applet using Applet Viewer
Running applet using Applet Viewer

Add and remove contacts to test your application. This is an easy way to develop and test client-side database code that will later be accessed using JavaScript. The applet UI is almost a fancy-looking unit test. We could almost take this and add it to a Web page, but not quite. First, there are some security considerations we need to deal with.

Security

We will sign the JARs used in our applet. If you are following along with the plan here, there is one thing that might seem too good to be true. Derby gives us a persistent database embedded on our client (everything is being stored on the client). It is kind of like HTTP cookies, but those are notoriously limited to 4 KB per domain. What are the limits of a Derby database on the client? The answer is either a lot (as in practically none) or a little (as in zero).

By default, an applet cannot access the local file system, so Derby cannot store any data on the client. So is using Derby a pipe dream? Fortunately, it is not. The key is that you must digitally sign your applet. A signed applet gets access to the local file system so Derby can persist data if it comes from a signed applet. All we need to do is sign the applet.

Listing 4. Signing the applet
$ keytool -genkey -alias sigs -keystore sigstore -keypass password -storepass password
What is your first and last name?
  [Unknown]:  Michael
What is the name of your organizational unit?
  [Unknown]:  developerWorks
What is the name of your organization?
  [Unknown]:  IBM
What is the name of your City or Locality?
  [Unknown]:  San Jose
What is the name of your State or Province?
  [Unknown]:  CA
What is the two-letter country code for this unit?
  [Unknown]:  US
Is CN=Michael, OU=developerWorks, O=IBM, L=San Jose, ST=CA, C=US correct?
  [no]:  yes

$ jarsigner -keystore sigstore -storepass password -keypass password -signedjar 
addrbook.jar derby.jar sigs

Warning: The signer certificate will expire within six months.

As you can see, we use two JDK tools to sign the applet (technically, the JARs that contain the applet). First, we use keytool to create a key store for holding a generated encryption key. This is the same tool you will use for tasks like creating an SSL certificate. Once we have a key, we use that key in conjunction with the jarsigner tool to sign the JARs. Notice that we included the Derby JAR, as well as the addrbook JAR that contains our custom code. Finally, this is an example of a self-signed applet. This is fine for development, but usually not for any user-facing code. In that case, you will want a key/certificate from a trusted provider, like VeriSign. The point of all this is that because we want to store data on the client, we need to go through these extra steps to comply with the Java language's client-side security model. With these security considerations in mind and a working applet, we are now ready to use the applet from JavaScript.


Applets with JavaScript

It is possible to use an applet for everything in a Web application, but it is more common to use standard HTML and JavaScript for the UI of Web applications. We can still do this and use an applet as a way to access an embedded Derby database. We just need to do some integration between JavaScript and an applet.

Integrating with an applet

Creating code to communicate between JavaScript and a Java applet may sound tricky, or like some new technology. It is neither. This kind of integration has been going on since the days of Netscape Navigator, and the techniques used there will still work. First, let's start at looking at the HTML code for just embedding our applet in a page.

Listing 5. Applet-embed code
<applet alt="Address Book Applet" name="addrBookApplet" 
    code="org.developerworks.addressbook.AddressBookApplet"
    width="400" height="200" archive="addrbook.jar, derby.jar">
</applet>

This is pretty standard applet-embed code. It uses the <applet> tag, which is deprecated. It is still supported across browsers. It is the only tag that can be used with Microsoft® and Mozilla browsers. So if you do not want to use it, you need to use JavaScript to browser sniff (determine what kind of browser the user is using). If it is Internet Explorer, you will use an <object> tag. Otherwise, use an <embed> tag with nested <param> tags for things like the height, width, and archive attributes. Also, if you use the <object> tag, you will also need a scriptable parameter to allow JavaScript to call the Java methods of the applet. If you use the <applet> tag, this is not needed.

Another attribute you should be aware of is MAYSCRIPT. This is used to grant the applet permission to execute JavaScript on the page. This can be very useful, but it is not needed in this case. We will use JavaScript to access the applet (the JavaScript will invoke Java methods of our applet). However, the applet will not call JavaScript, so we do not need the MAYSCRIPT attribute. So how do we call those Java methods from JavaScript?

Listing 6. Calling Java from JavaScript
function saveContact(firstName, lastName, email){
    var applet = document.addrBookApplet;
    var newContact = applet.addContact(firstName, lastName, email);
    addContactToUi(newContact);
}

The first thing we do in the saveContact function is get a handle on the applet. We do this by using the name of the applet (the name attribute in Listing 5). From there, we call the addContact method directly, and it returns a new Contact object in the code in Listing 6. We pass this off to another JavaScript function to update the UI with the new contact. There is nothing more needed. It is that simple. We can use the applet just for persistence and JavaScript for everything else.

Using a headless applet

We can now make our applet headless (just a code library with no UI to it). To do that, we simply remove all of the UI from Listing 3 and just keep those public methods at the top. We need to tweak our UI-embed code slightly.

Listing 7. Headless UI HTML
<applet alt="Headless Applet" name="headlessApplet"
    code="org.developerworks.addressbook.HeadlesApplet"
    width="1" height="1" archive="addrbook.jar, derby.jar">
</applet>

The only thing of interest here is that we made the width and height both equal to 1. This will essentially make the applet invisible on the page. As a result, the end users will have no idea there is an applet on the page at all. It will be an invisible helper. Of course, you also need to tweak the JavaScript if you change the name of the applet, but that is the only other thing you might need to change. We are ready to use this client-side persistence tool to further enhance our Web applications.


Creating an Ajax cache

Applets are capable of doing a lot of the same things you do with Ajax, and as we have seen, a lot more. In this case, we are only interested in the "a lot more" part. Applets can communicate with server, but we will stick with Ajax for that. It is the ability to use Derby as an embedded client-side database that enables us to do things that we could not do with Ajax alone. More to the point: We can use Derby as a powerful client-side cache for data from our servers.

Using Derby as a cache

Here is the idea: We will keep our contacts on the server, and all of our operations like add and delete will be Ajax calls. However, we will keep the same information on the client in Derby and use it as a cache. Thus, we can load all of our contacts from Derby.

Listing 8. Loading contacts from the Derby cache
function init(){
    var book = document.headlessApplet.loadContacts();
    var i = 0;
    var contact = {};
    for (i=0;i<book.length;i++){
        contact = {firstName:book[i][0], lastName:book[i][1], 
                    email:book[i][2], id:book[i][3]};
        addContactToUi(contact);
    }
}

The function loads the contacts from the applet, then iterates over it adding each contact to the UI. This is code that we call when the page first loads. Normally, we would get the contacts from the server and suffer some latency waiting on the response from the server. With our Derby cache, there is no latency. However, we do need to make sure that we keep our contacts in sync between the server and the Derby cache.

Keeping in sync

Our cache is only useful if it is accurate, so we need to make sure that it is in sync with our server. The simplest way to do this is to send an asynchronous update to the server and while that is processing, update the cache and the UI of our application.

Listing 9. Adding a contact
function addContact(){
    var contact = {};
    var firstName = document.getElementById("firstName").value;
    var lastName = document.getElementById("lastName").value;
    var email = document.getElementById("email").value;
    var req = getXhr(); // get the browser specific XMLHttpRequest object
    var params = "firstName="+username + ",lastName="+lastName+",email="+email;
    req.open("POST", "/contact/create", true);
    req.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    req.setRequestHeader("Content-length", params.length);
    req.setRequestHeader("Connection", "close");
    req.onreadystatechange = handleResponse;    
    req.send(params);    
    // update cache
    saveContact(firstName, lastName, email);
}

Most of this code is typical Ajax code, using the XMLHttpRequest object. We simply send the XMLHttpRequest, as usual. This will be asynchronous, so the req.send() call returns immediately. We can then call the saveContact() function from Listing 6. Notice that we registered a handler (by setting the req.onreadystatechange property). With this approach, the code needs to handle the case where the call to the server fails. This could happen if a server was temporarily down or if the user was having network problems. You could get quite sophisticated here and queue up the updates so they can be re-tried later when the server or network are back up. Alternatively, we could move the saveContact call to the handler. The UI will not be quite as responsive, but that way, we only update it and the cache when there is a successful update to the server.


Summary

We have seen how easy it is to use Derby as an embedded database. We have seen how this can be combined into a Java applet that will persist data on to the client. There are some security factors we must consider here — that only signed applets can write data to the client's file system — but we took a look at the process and tools for signing our applets. With that in mind, we can easily access an applet from JavaScript and turn Derby into a cache for an Ajax-enabled Web application. This allows data to be accessed even if a server is temporarily unavailable. Thus, Derby can be a key part of any kind of "offline" strategy, making data available even when the server is not.


Download

DescriptionNameSize
Sample codeos-ad-offline-ajax-AddressBook.zip8KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Open source on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source
ArticleID=339303
ArticleTitle=Offline Ajax with Apache Derby
publish-date=09232008