High-availability mobile applications

Mobile databases and J2ME tools

1 2 3 Page 2
Page 2 of 3
Vector findNameCardsByKeyword(String name, String company,
        String title, String address1, String address2,
        String phone, String email) {
  Vector NameCards = new Vector();
  String[] keywords = {name, company, title, address1, address2,
                       phone, email};
  try {
    for (int i = 0; i < keywords.length; i++) {
      String criteria = (keywords[i].equals("")) ? "%" :
                        "%" + keywords[i] + "%";
      find.setString(i + 1, criteria);
    }
    ResultSet resultSet = find.executeQuery();
    while (resultSet.next()) {
      NameCard nameCard = new NameCard(resultSet.getInt(1),
            resultSet.getString(2), resultSet.getString(3),
            resultSet.getString(4), resultSet.getString(5),
            resultSet.getString(6),
            resultSet.getString(7), resultSet.getString(8));
      if (!delay)
        loadPicture(nameCard);
      NameCards.addElement(nameCard);
    }
  } catch (SQLException e) {
    e.printStackTrace();
  }
  return NameCards;
}
void addNameCard(NameCard nameCard) {
  nameCard.setID(getNewID());
  try {
    insert.setInt(1, nameCard.getID());
    insert.setString(2, nameCard.getName());
    insert.setString(3, nameCard.getCompany());
    insert.setString(4, nameCard.getTitle());
    insert.setString(5, nameCard.getAddress1());
    insert.setString(6, nameCard.getAddress2());
    insert.setString(7, nameCard.getPhone());
    insert.setString(8, nameCard.getEmail());
    insert.setBytes(9, nameCard.getPicture().getBytes());
    insert.executeUpdate();
  } catch (SQLException e) {
    e.printStackTrace();
  }
}
void updateNameCard(NameCard nameCard) {
  try {
    update.setInt(1, nameCard.getID());
    update.setString(2, nameCard.getName());
    update.setString(3, nameCard.getCompany());
    update.setString(4, nameCard.getTitle());
    update.setString(5, nameCard.getAddress1());
    update.setString(6, nameCard.getAddress2());
    update.setString(7, nameCard.getPhone());
    update.setString(8, nameCard.getEmail());
    update.setBytes(9, nameCard.getPicture().getBytes());
    update.setInt(10, nameCard.getID());
    update.executeUpdate();
  } catch (SQLException e) {
    e.printStackTrace();
  }
}
void deleteNameCard(NameCard nameCard) {
  try {
    delete.setInt(1, nameCard.getID());
    delete.executeUpdate();
  } catch (SQLException e) {
    e.printStackTrace();
  }
}
void loadPicture(NameCard nameCard) {
  try {
    ResultSet resultSet =
            statement.executeQuery(
              "SELECT Picture FROM NameCard WHERE ID = " +
              nameCard.getID());
    resultSet.next();
    Picture picture = new Picture();
    picture.setBytes(resultSet.getBytes(1));
    nameCard.setPicture(picture);
  } catch (SQLException e) {
    e.printStackTrace();
  }
}
private int getNewID() {
  try {
    ResultSet resultSet = statement.executeQuery(
        "SELECT MAX(ID)+1 FROM NameCard");
    if (resultSet.next()) {
      return resultSet.getInt(1);
    } else {
      return 0;
    }
  } catch (Exception e) {
    e.printStackTrace();
  }
  return 0;
}

Synchronize with backend databases

Class DBManager also allows the application developer to synchronize the mobile database with a backend database using PointBase's proprietary UniSync engine. Different vendors use different synchronization engines, but their concepts are similar. The synchronization process follows these steps:

  1. Create corresponding databases and tables on both the backend server and mobile devices.
  2. Create a hub on the synchronization server. The hub contains publications that specify the backend tables (or partial tables) available for synchronization (publish).
  3. Use the hub to create spokes. Spokes are objects on the synchronization server representing mobile devices. Each spoke has an ID. It can subscribe to the publications in the same hub through subscription objects. Using a spoke ID, the mobile device connects to the matching spoke and synchronizes to the subscribed backend tables.
  4. Start the synchronization server. This basically involves executing the com.pointbase.me.sync.Server class's main() method. The server class is available in the PointBase distribution package. There are several ways to run the server in different environments. Please refer to PointBase documentation for more details and example scripts. By default, the server listens at port 8124.
  5. Initiate the synchronization process using a spoke ID and spoke stub classes residing on the mobile devices.

Figure 3 illustrates the UniSync synchronization server architecture.

Figure 3. The architecture of UniSync server

Class ResetServer in Listing 3 demonstrates how to create hubs and spokes on the UniSync server:

Listing 3. Set up the synchronization server

manager=SyncManager.getInstance(caturl,catdriver,catuser,catpassword);
String dsname;
dsname=SyncDataSource.DEFAULT;
String hubname="Hub";
Hub hub=manager.createHub(hubname);
Publication pub;
String pubname;
SpokeConfig spoke;
Subscription sub;
String subname="SubNameCard";
String tablename="NAMECARD";
String[] tables=new String[]{tablename};
// Publish the complete name-card table
pubname="PubNameCard";
pub=hub.newPublication(pubname,dsname,tables);
hub.publish(pub);
// Create two spokes and subscribe to this publication
for(int i=1;i<=2;i++) {
  String name="Spoke"+i;
  spoke=hub.createSpokeConfig(name);
  spoke.savePassword("pass"+i);
  sub=spoke.newSubscription(subname,SyncDataSource.DEFAULT,pubname);
  spoke.subscribe(sub);
}
// Publish the name-card table without the picture column
pubname="PubNameCardNoPicture";
pub=hub.newPublication(pubname,dsname,tables);
SyncTable table=pub.getSyncTable(tablename);
table.dropSyncColumns(new String[]{"PICTURE"});
hub.publish(pub);
            
// Create two spokes and subscribe to this publication
for(int i=3;i<=4;i++) {
  String name="Spoke"+i;
  spoke=hub.createSpokeConfig(name);
  spoke.savePassword("pass"+i);
  sub=spoke.newSubscription(subname,SyncDataSource.DEFAULT,pubname);
  spoke.subscribe(sub);
}
manager.close();

The following code snippet from DBManager demonstrates how to obtain the spoke stub and process the synchronization on the device side. The comments embedded in the code illustrate the differences between the application's synchronized and standalone versions:

Listing 4. Data access via the synchronization server

// Import proprietary classes for sync
import com.pointbase.me.jdbc.*;
class DBManager {
  // In addition to JDBC connection variables,
  // we also need to define variables for sync
  // ... ...
  private Spoke spoke;
  private String spokename;
  private int spoke_id;
  private int spoke_range_start,spoke_range_end;
  final static int ROWS_PER_SPOKE=1<<16;
  private String syncurl;
  private String syncpassword;
  private DBManager() {
    
    // Get DB connection parameters
    // ... ...
    
    // Get sync parameters
    syncurl =
      properties.getProperty("syncurl", "http://localhost:8124");
    String spokeid =
      properties.getProperty("spokeid", "1");
    spokename =
      properties.getProperty("spoke", "Spoke"+spokeid);
    syncpassword =
      properties.getProperty("syncpassword", "pass"+spokeid);
    url =
      properties.getProperty("url",
          "jdbc:pointbase:micro:pbdemo"+spokeid);
    connect();
  }
  // The complete connect method using synchronization server
  private void connect() {
    try {
      System.out.println("Connecting to the database...");
      Class.forName(driver);
      // If the database doesn't exist, create a new database
      connection = DriverManager.getConnection(url, user, password);
      statement = connection.createStatement();
      // Check sync metadata and create tables
      loadMeta();
      // Create prepared statements
      createStatement();
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    }
  }
  // The complete newID method using the sync server
  private int getNewID() {
    try {
      ResultSet rs = statement.executeQuery(
            "SELECT MAX(ID)+1 FROM NameCard WHERE "+
            "ID>="+spoke_range_start+" AND ID<"+spoke_range_end);
      rs.next();
      int id=rs.getInt(1);
      if(rs.wasNull()) {
        return spoke_range_start;
      } else {
        return id;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return 0;
  }
  // Create table and load metadata from the sync hub
  void loadMeta() {
    try {
      SyncManager manager=SyncManager.getInstance(connection);
      spoke=manager.getSpoke(spokename);
      if(spoke==null) {
        System.out.println(
            "Loading MetaData from url "+syncurl+
            " for spoke "+spokename+
            " using password "+syncpassword);
        spoke=manager.createSpoke(spokename);
        spoke.savePassword(syncpassword);
        spoke.saveHubURL(syncurl);
        spoke.loadConfig();
        spoke.getSnapshot();
      }
      spoke_id = spoke.getSpokeId();
      System.out.println("SpokeID is "+spoke_id);
      spoke_range_start = ROWS_PER_SPOKE * spoke_id;
      spoke_range_end =  spoke_range_start + ROWS_PER_SPOKE - 1;
    } catch (SyncException e) {
      e.printStackTrace();
    }
  }
  
  // Synchronize spoke databases (mobile databases) with the hub
  // and backend databases
  void sync() {
    try {
      spoke.sync();
    } catch (SyncException e) {
      e.printStackTrace();
    }
  }
  
  // Other data access methods are the same as the non-synced version.
}

J2ME mobile database choices

Of course, PointBase is not the only choice for J2ME mobile database products. In the rest of this article, I compare and discuss several leading competitors. Before I do that, let me first overview the product landscape.

JDBC databases for high-end devices

For high-end devices that run PersonalJava or the Personal Profile, the database can be accessed via the JDBC APIs. The JDBC database is the easiest type of database to program. All vendors have at least one product in this category. The biggest challenge here is how to balance features with footprint. We want the smallest and fastest database that supports the exact set of features the application requires.

Lightweight databases for MIDP devices

CLDC (Connected Limited Device Configuration)/MIDP does not support the JDBC interface. Two common approaches support complex data management on MIDP devices:

  • Implement extremely lightweight relational databases and JDBC-like access APIs over RMS. This approach is resource expensive but proves the best in terms of developer productivity and feature support.
  • Directly extend the RMS class and implement simple row (de)serialization, data access, indexing/searching, and synchronization methods in the extended class. The extended class is a thin layer over RMS. It can mimic a relational table's behavior.

I discuss both approaches in the product review sections.

Synchronization servers

As you have seen, synchronization is a key feature for mobile databases. Most database vendors have their own proprietary synchronization servers. Those servers support many add-on features to optimize the synchronization process in the mobile environment. Important features of synchronization servers include the following:

  • Smart conflict resolution
  • Bandwidth reduction
  • End-to-end encryption
  • Performance tuning for backend engines
  • Asynchronous and scalable updates

In the next several sections, I examine and compare products from several leading vendors.

HSQL Database Engine

The open source HSQL Database Engine is based on Thomas Mueller's Hypersonic SQL Project. It is completely written in Java and is one of the most widely used embedded databases. It is included in many J2EE application servers. On mobile devices, HSQL runs on the PersonalJava and FP/PP platforms. HSQL is free for all. You can freely redistribute it with your applications, which proves handy for mobile applications.

HSQL provides a JDBC driver that supports 95 percent of the JDBC interface and all JDBC 1 data types. It supports transactions, foreign keys, and even Java stored procedures. Tables in HSQL can reside in memory or be persisted to disk files. HSQL has less than a 160-KB memory footprint. It also distributes a database management console for PersonalJava devices (tested on Sharp Zaurus).

However, HSQL lacks some of the advanced performance and security features commonly found in commercial mobile databases. More importantly, HSQL does not offer any synchronization solution. It also lacks a solution for MIDP devices.

PointBase Micro

PointBase is one of the leading vendors for pure Java embedded databases. The PointBase Micro database runs on both FP/PP/PersonalJava and MIDP platforms.

On the FP/PP/PersonalJava platforms, the PointBase Micro database supports most JDBC and SQL features with a footprint of merely 91 KB. It also supports advanced features such as database encryption. It lacks support for stored procedures and the CallableStatement interface. Since MIDP does not support JDBC, PointBase offers its own lightweight JDBC-like APIs for MIDP. PointBase Micro database's MIDP version provides a database browser console MIDlet.

PointBase Micro databases can easily synchronize with server-side PointBase Embedded databases and Oracle databases through the UniSync synchronization server.

Sybase iAnywhere Solutions

iAnywhere's SQL Anywhere Studio has a large market share in lightweight databases for laptop computers. It also has a strong presence in the mobile database market for pervasive devices. A key innovation behind Anywhere SQL Studio is its custom database generator. It lets the customer, not the database vendor, decide how to balance the database footprint against its supported features.

In the Anywhere SQL Studio, the user can specify the SQL statements she will call in the application. The studio will then generate a custom database with the user's choice of the underlying persistence mechanism and transactional features. The generated database is a pure Java class that comes with a set of APIs the user application can call. Data synchronization APIs using iAnywhere's MobiLink synchronization server are also built into the custom database.

In additional custom database generators, iAnywhere also offers large-footprint, general-purpose mobile databases with many security, optimization, and usability features, including support for Java stored procedures. Those databases run natively on popular mobile platforms to achieve better performance. Sybase iAnywhere SQL Studio does not yet support autogeneration of MIDP databases.

IBM DB2 Everyplace

IBM DB2 Everyplace is IBM's mobile database offering. For IBM customers, DB2 Everyplace integrates well with other IBM enterprise components (e.g., DB2 Universal Database and WebSphere MQ Everyplace) and IBM development tools (e.g., IBM WebSphere Studio Device Developer). DB2 Everyplace runs natively on many platforms including Palm OS, Symbian OS, Pocket PC, QNX Software Systems, and embedded Linux. DB2 Everyplace supports encrypted data fields and table storage optimization features.

On the MIDP platform, DB2 Everyplace has a product called FastRecordStore. It emulates an indexable and searchable relational table on top of MIDP RMS record stores.

DB2 Everyplace databases and FastRecordStores synchronize with backend databases via the IBM synchronization engine. DB2 Everyplace also comes with a tool called Mobile Application Builder that allows developers to visually build DB2 Everyplace applications.

Oracle9i Lite

Oracle9i Lite is Oracle's mobile database product. It runs on Palm OS, Pocket PC, Symbian OS, and Win32 platforms. The Win32 edition is intended to run on laptop computers and supports JDBC, multiuser mode, and Java stored procedures. Oracle9i Lite's Pocket PC and Symbian OS editions support JDBC. The Palm OS edition only supports Oracle's proprietary native OKAPI (Object Kernel API) and ODBC (Open Database Connectivity). The Oracle9i Lite suite includes a mobile development kit that automatically generates and packages mobile database applications from user custom requirements. Currently, it only generates native client applications.

1 2 3 Page 2
Page 2 of 3