11.1 Database on the Go

One of the biggest obstacles of WAP-based mobile commerce is the requirement of uninterrupted wireless network coverage. Today's unreliable and incomplete wireless network infrastructure leaves the anywhere, anytime promise of mobile commerce unfulfilled. Fortunately, the widespread adoption of the smart mobile client technology will change this picture and enable new generations of mobile applications.

A key benefit of smart clients is that they can function in the offline mode when the wireless network connection is temporarily unavailable. The offline mode drastically improves the application availability. In fact, most of today's mobile applications primarily work in the offline mode. Users carry their PDAs or barcode scanners all day. They synchronize data with desktop computers only once or twice a day. The disconnected mobile application architecture is a proven success.

To support offline operations, the mobile client must store application data locally. There are great needs for first-class data management tools on mobile devices. Lightweight relational databases are just the tools we are looking for. Compared with linear data storage facilities (e.g., plain file or the MIDP RMS) that come with J2ME standard profiles, relational databases are much more efficient for complex data. Besides supporting highly available offline applications, mobile databases offer the following benefits.

• They can be used to manage user preferences. Extreme personalization is touted as another major benefit of mobile commerce.

• They can provide performance cache to reduce network round trips. This could drastically improve application performance on slow and long latency wireless networks.

The standard API to access relational databases on the J2SE and J2EE platforms is the Java DataBase Connectivity (JDBC) API. To leverage existing developer skills, most mobile database vendors choose to support subsets of JDBC or JDBC-like APIs on J2ME platforms. For the impatient readers, a sample mobile JDBC application is given in Section 11.11 later this chapter. Now, let's have a look at the JDBC API first.

11.2 Introducing JDBC

The JDBC specification defines a set of standard interfaces. Each database vendor is responsible for supplying implementation classes that know how to talk with their proprietary databases. Those vendor-specific classes are called JDBC drivers and they are transparent to developers. Using JDBC to access a database involves several steps:

11.2.1 A JDBC Example

Now, let's look at the use of the JDBC API through an example. For the sake of simplicity, let's assume that the database table we are dealing with has the following schema (Listing 11.1).

Listing 11.1. Example database table schema

CREATE TABLE PersonRecords(

  USERID INTEGER PRIMARY KEY,

  NAME VARCHAR(254),

  ENTRYTIME TIMESTAMP,

  PICTURE BLOB

};

11.2.2 Obtain a Connection Object

The code for obtaining a Connection object is slightly database-dependent because we have to load the vendor-specific JDBC driver. Using JDBC v3.0 (or the CDC JDBC Optional Package; see Section 11.5), we can use the following code.

VendorDataSource ds = new VendorDataSource();

ds.setServerName("dbserver");

ds.setPortNumber(9980);

Connection conn = ds.getConnection("username", "passwd");

The port number, username, and password are specific to the database setup. Most on-device embedded databases are for single user only. If you use PersonalJava, the driver initialization code is slightly different because it conforms to the JDBC v1.2 specification. The database URL string (DBURI) in the code below is database-specific.

Class.forName("vendor.specific.DriverManager");

Connection conn=DriverManager.getConnection("DBURI",

                             "username","password");

11.2.3 Execute a SQL Statement

Next, we assemble a SQL string and execute it through a Statement object.

String SQLStr = "SELECT * FROM PersonRecords";

Statement stat= conn.createStatement();

ResultSet rs = stat.executeQuery(SQLStr);

If the SQL command is an Update instead of a Select, we should call the Statement.update() method. Method update() returns an integer number that indicates the number of rows that have been updated.

11.2.4 Extract Search Results

We can extract data columns from the ResultSet object returned from the Select query. We can use either the column index or the column name to retrieve data.

// Loop through all returned rows

while (rs.next()) {



  int userID = rs.getInt(1);

  // int userID = rs.getInt("USERID");



  String name = rs.getString(2);

  // String name = rs.getInt("NAME");



  Timestamp entryTime = rs.getTimestamp(3);

  // Timestamp entryTime =

  // rs.getTimestamp("ENTRYTIME");



  InputStream is = rs.getBinaryStream(4);

  // InputStream is = rs.getBinaryStream("PICTURE");

}

11.3 Portable and Efficient Code Using PreparedStatement
Although easy to use, the Statement object is not suited for large volume queries and updates over several different databases.
11.3.1 Problems with the Statement Interface
Using the Statement interface, developers have to hardcode all SQL statements. There are several serious disadvantages to this approach.
We have to manually escape all special characters and null values. String presentations of data types like DateTime and TimeStamp are difficult to remember. Also, it is very difficult to hardcode binary content, such as a picture in a blob field, into a SQL text string.
Since different databases have slightly different escaping and formatting schemes, those hardcoded SQL strings are not portable.
Every time we pass the SQL string to the Statement object, it has to be parsed to an internal data format. It is inefficient if the same statement is reused many times.
The PreparedStatement interface is designed to solve the above problems.
 
11.3.2 Use of the PreparedStatement Interface
A PreparedStatement object is instantiated with a parameterized SQL template.
String SQLTemplate =

  "INSERT INTO PersonRecords " +

  "(USERID, NAME, ENTRYTIME, PICTURE) " +

  "VALUES (?, ?, ?, ?)";

PreparedStatement pstmt = conn.prepareStatement(SQLTemplate);


The template is parsed only once and can be efficiently reused. Those ? marks are template parameters that can be specified dynamically at runtime. We use methods setXXXX, where the XXXX indicates supported JDBC types, to assign values to those parameters. For example, we can do
int userID;

String name;

Timestamp entryTime;

byte [] picture;

// For every userID, populate the name, entryTime and picture variables

  pstmt.setInt(1, userID);

  pstmt.setString(2, name);

  pstmt.setTimestamp(3, entryTime);

  pstmt.set(4, picture);

  pstmt.executeUpdate();



// End


All the database-specific formatting and escaping will be automatically taken care of by the JDBC driver. We should almost always 

use the PreparedStatement class instead of the Statement class unless doing a simple statement only once.

 
11.4 Access Stored Procedures Using CallableStatement 
Due to the device and network constrains, mobile database applications need to minimize the operational overhead and fully leverage the database's native optimization. The database stored procedures and the JDBC CallableStatement interface are very handy tools.
11.4.1 What Is a Stored Procedure?
A stored procedure is a named group of SQL statements and flow control logic that have been previously created and stored in the database. Stored procedures 

accept input parameters (IN parameters) just like any other types of remote procedure calls. Stored procedures can also return results by updating the values of some parameters that have been passed to it (OUT parameters). Of course, stored procedures containing query statements also return normal row sets. Stored procedures have the following advantages:

    
More robust: Stored procedures modularize database access functionalities, which promotes code reuse and reduces the chance for human errors.
    
Bandwidth friendly: Calling the stored procedure by its name certainly uses less bandwidth than passing the entire sequence of SQL statements.
    
More secure: A database can be configured to prohibit generic SQL operations but allow only certain stored procedure calls from certain users. This allows fine-tuned access control and helps to ensure data integrity.
    

    
High performance: Since a group of SQL statements and their controlling logic are stored together, the database can optimize the execution flow globally. However, most mobile databases on devices do not have this level of sophistication.
    

11.4.2 Use of the CallableStatement Interface
Using JDBC, we can access stored procedures using the CallableStatement interface, which extends the PreparedStatement interface. We have to first define stored 

procedures in the database through a database-specific step. The following SQL script defines a stored procedure, addPerson, in an Oracle database. The procedure takes in four parameters: pUSERID, pNAME, pENTRYTIME, and pPICTURE. It adds a new row to the PersonRecords table and returns the total number of rows in the OUT parameter pTOTAL.
CREATE PROCEDURE addPerson (

  pUSERID IN INTEGER,

  pNAME IN VARCHAR(254),

  pENTRYTIME IN TIMESTAMP,

  pPICTURE IN BLOB,

  pTOTAL OUT INTEGER

)

AS

BEGIN

  INSERT INTO PersonRecords

  (USERID, NAME, ENTRYTIME, PICTURE)

  VALUES (pUSERID, pNAME, pENTRYTIME, pPICTURE);



  SELECT COUNT(*) FROM PersonRecords;



  COMMIT;

END


Then, we use a parameterized procedure call template to instantiate a CallableStatement instance. The ? marks indicate parameters. An IN parameter is set by the setXXXX method inherited from the PreparedStatement interface. An OUT parameter must be registered so that we can retrieve its value after the call. Any parameter can be both IN and OUT. Finally, the procedure call is executed via the CallableStatement.executeUpdate() method.
int userID;

String name;

Timestamp entryTime;

byte [] picture;



// Init the CallableStatement



CallableStatement cstmt =

    conn.prepareCall("{call addPerson(?, ?, ?, ?, ?)}");



// Set IN parameters

cstmt.setInt(1, userID);

cstmt.setString(2, name);

cstmt.setTimestamp(3, entryTime);

cstmt.set(4, picture);

// Register the OUT parameter

cstmt.registerOutParameter(5, java.sql.Types.INTEGER);



// Execute the procedure call

cstmt.executeUpdate();



// If the procedure call returns a ResultSet,

// you can process it here



// Get the OUT parameter

int totalcount = cstmt.getInt(5);

11.5 The JDBC Optional Package for the CDC
Due to the resource limits of mobile devices, we can support only part of the JDBC API on J2ME. The CDC JDBC optional package is a rich subset of JDBC v3.0. In particular, the following standard JDBC features are not supported.

    
Connection pools.
    
The ParameterMetaData interface.
    
Setting parameters by name in the CallableStatement interface.
    
SQL 99 types (the Struct, Array, Ref, SQLData, SQLInput, and SQLOutput interfaces).
    
Custom type mapping (the setTypeMap() and getTypeMap() methods).

The J2ME subset of JDBC is supported by most mobile database vendors. In addition to the standard interfaces, vendors provide performance and productivity features

to distinguish themselves from competitors. In the next several sections, we look at mobile database solutions from leading vendors.
11.6 HSQL Database Engine
The Open Source HSQL Database Engine project is based on Thomas Mueller's Hypersonic SQL database project. It is completely written in Java and is one of the

most widely used Open Source embedded databases. It is included in many J2EE application servers. On mobile devices, HSQL runs on the PersonalJava and CDC/FP platforms.HSQL (v1.7) 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 does all of this with a memory footprint of

less than 160KB. HSQL also distributes a database management console for PersonalJava devices.



As an Open Source project, HSQL is free for all. You can freely distribute it with your application—this is very handy for mobile applications. Unlike many other Open Source projects, the documentation for HSQL is remarkably good. Commercial support, however, is not available.HSQL does have shortcomings too. It does not support the entire 

SQL or JDBC specification; it does not provide database encryption; most important of all, it does not come with a synchronization engine. Database synchronization is essential for enterprise applications (see Chapter 13), and all other commercial mobile databases

discussed in this chapter support backend synchronization.



11.7.1 Use an UltraLite Custom Database


The following step-by-step instructions illustrate how to build and use an UltraLite custom database for our mobile applications.
 
 

    

    
Build a reference database (from a SQL script or through the administration panel GUI) on the server side. This database has the same schema as our mobile deployment database.
    
    

    
Add all required SQL statements into the reference database through the GUI. Those SQL statements should cover all the database operations that we use in the application. We should also assign each of those statements a name. For example, we can assign a name INSERT_PRODUCT to the following statement.
    
INSERT INTO Product (prod_id, price, prod_name) VALUES (?, ?, ?)


    
    

    
Run the UltraLite Analyzer command-line tool to generate the custom database in a supported implementation platform. We can generate C/C++ native implementations for Palm OS, Windows CE, Symbian OS, and VxWorks devices. Or, we can generate a crossplatform Java implementation (PersonalJava). In this chapter, we focus on Java databases.
    
    

    
The UltraLite Analyzer generates two Java source code files. One file (SampleDB.java) contains the custom database implementation. The other file (ISampleSQL.java) contains interfaces to the predefined SQL statements in step 2.
    
    

    
We can obtain a JDBC connection to this custom database using the following code snippet. By default, an iAnywhere UltraLite database is transient: it is erased when the application closes it. The top two lines of code specify that the SampleDB database needs to persist as a file.
    
java.util.Properties p = new java.util.Properties();

p.put("persist", "file");

SampleDB db = new SampleDB(p);

Connection conn = db.connect();


    
    

    
We use common JDBC APIs to access the database. We should only use SQL commands predefined in the interface file (ISampleSQL.java) to ensure that the application does not reach any functionalities that are not built into this custom database. For example, the following code snippet can be used to add a product entry.
    
PreparedStatement pstmt = conn.prepareStatement (INSERT_PRODUCT);

pstmt.setInt(1, 1);

pstmt.setInt(2, 400);

pstmt.setString(3, "Drywall");

    

 
    
11.8 IBM DB2 Everyplace
DB2e is the lightweight device version of the popular DB2 database. In this book, we cover DB2e v8.1. It runs natively on many platforms, including the Palm OS, Symbian OS, PocketPC, QNX, and embedded Linux. DB2e comes with a tool called Mobile Application Builder that allows developers to visually build DB2e applications.

Note
The Palm OS is not CDC-compatible and does not have standard JDBC support. But the IBM WebSphere Micro Environment (formerly known as J9) for Palm OS provides a JDBC extension. DB2e's JDBC driver works only on IBM's Palm OS Java runtime.
DB2e does not support stored procedures on devices. Its JDBC driver does not support the CallableStatement interface. DB2e supports only a subset of SQL types: INT, VARCHAR, BLOB, DECIMAL, CHAR, SMALLINT, DATE, TIME, and TIMESTAMP. The SQL statements supported by DB2e are listed in Table 11.2. It does not support advanced SQL features such as table JOINs. DB2e supports encrypted data fields and table storage optimization features. We can use the following code snippet to connect a local DB2e database.
Class.forName("com.ibm.db2e.jdbc.DB2eDriver");

Connection conn = DriverManager.getConnection("jdbc:db2e:mydb");



// SQL queries and updates etc.

// ... ...


    
In addition to the JDBC interface, DB2e also supports C/C++ native interfaces through the Call-Level Interface (CLI). DB2e is part of IBM's mobile strategy, and 

    it plays well with other IBM mobile middleware tools such as the MQe, SMF, and WebSphere Studio Device Developer (see "Resources").
    
         

    
 
    
 11.9 Oracle9i Lite
    
The Oracle9i Lite is Oracle's mobile database product. It runs on Palm OS, PocketPC, Symbian OS, and Win32 platforms. The Win32 edition is intended to run on 

    laptop computers and supports JDBC, multiuser mode, and Java stored procedures. The PocketPC and Symbian OS editions of 9i Lite support JDBC. The Palm OS edition 

    supports only Oracle's proprietary native OKAPI and ODBC.The Oracle9i Lite suite includes a Mobile Development kit that automatically generates and packages mobile database applications 

    from user custom requirements. Currently, it generates only native client applications.
    
 11.10 PointBase Micro Edition
    
PointBase (v4.6) is a pure Java embedded database product. It runs on any mobile device that supports J2ME (see Chapter 12 for PointBase Micro's MIDP support). On CDC and PersonalJava platforms, PointBase Micro is just a JAR file that the user application can link to. It is very easy to deploy—you can simply embed it in your application. The footprint of PointBase Micro for the CDC and PersonalJava is 91 KB. The PointBase Micro database does not support stored procedures or the CallableStatement interface. In the next section, we look at an example application supplied by PointBase.
    
 
    
11.11 Example Application: Contact Manager
    
The example is an advanced mobile contact manager application provided by PointBase. The application itself is very simple: It mainly duplicates features commonly found in advanced address books. For example, it allows the user to store contact name, address, and phone numbers with pictures; provides intuitive browsing and searching interfaces; and synchronizes with backend database servers. Figure 11.3 demonstrates the application in action on a PocketPC device running Insignia's Jeode PersonalJava VM.
    
The clientside application contains a set of AWT UI classes that conforms to the PersonalJava specification. Behind those UI drivers, there is a database access layer and a generic on-device JDBC database layer. Now, we focus on the code in the data access layer, which is contained in a single class: DBManager.
    
Class DBManager is a singleton class that provides a single point of entry to the database from the application. The singleton pattern avoids threading complexities involved with embedded databases. The code snippet below is the constructor and initialization method of DBManager. It makes a connection to the database, loads the table schema, populates the table with sample data, and creates SQL statement templates (PreparedStatement) for later use. As we can see, everything here is standard JDBC stuff. For enterprise Java developers, the DBManager class (Listing 11.2) should be easy to understand.
    
Listing 11.2. The DBManager class (Part 1) in Contact Manager
    
class DBManager {

  // DBManager is a Singleton class

  private static DBManager instance;

  private String driver;

  private String url;

  private String user;

  private String password;

  private boolean delay;

  private Connection connection;

  private Statement statement;

  private PreparedStatement insert;

  private PreparedStatement find;

  private PreparedStatement delete;

  private PreparedStatement update;

  private PreparedStatement all;



  static DBManager getInstance() {

    if (instance == null) {

      instance = new DBManager();

    }

    return instance;

  }



  private DBManager() {

    // get parameters from runtime properties

    // This allows us to switch to different JDBC

    // DBs without changing the application code.

    Properties properties = ContactManager.getProperties();

    driver =

      properties.getProperty("driver",

          "com.pointbase.me.jdbc.jdbcDriver");

    url =

      properties.getProperty("url",

          "jdbc:pointbase:micro:pbdemo");

    user =

      properties.getProperty("user", "PBPUBLIC");

    password =

      properties.getProperty("password", "PBPUBLIC");

    delay =

      properties.getProperty("delayread","true").equals("true");

    connect();

  }



private void connect() {

  try {

    // Load the driver class

    Class.forName(driver);



    // If the database doesn't exist,

    // create a new database.

    connection = DriverManager.getConnection(url, user, password);



    // Create template statement objects

    statement = connection.createStatement();

    createStatement();



    // If the database is newly created,

    // load the schema

    boolean newdb=initDatabase();

    // Load sample data for the new tables

    if(newdb) {

       SampleDataCreator.insert(connection);

    }



  } catch (Exception e) {

    e.printStackTrace();

    System.exit(1);

  }

}



void disconnect() {

  try {

    connection.commit();

    statement.close();

    insert.close();

    find.close();

    delete.close();

    update.close();

    all.close();

    connection.close();

    System.exit(0);

  } catch (Exception e) {

    e.printStackTrace();

    System.exit(1);

  }

}



// Create the table and load the schema

private boolean initDatabase() {

  try {

    String sql = "CREATE TABLE NameCard " +

        "(ID INT PRIMARY KEY, Name VARCHAR(254), " +

        "Company VARCHAR(254), Title VARCHAR(254), " +

        "Address1 VARCHAR(254), Address2 VARCHAR(254), " +

        "Phone VARCHAR(254), Email VARCHAR(254), "+

        "Picture Binary(1000000))";

    // if the table already exists,

    // this will throw an exception

    statement.executeUpdate(sql);

    // this means the database already exists

    return true;

  } catch (SQLException e) {

    // ignore the error - the table already

    // exists, which is good

    // so we don't need to add demo data later on

    return false;

  }

}



// create statement templates

private void createStatement() {

  try {

    insert = connection.prepareStatement(

      "INSERT INTO NameCard (ID, Name, Company, Title, " +

      "Address1, Address2, Phone, Email, Picture) " +

      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

    find = connection.prepareStatement(

      "SELECT * FROM NameCard WHERE (Name LIKE ?) "+

      "AND (Company LIKE ?) AND (Title LIKE ?) "+

      "AND ((Address1 LIKE ?) OR (Address2 LIKE ?)) "+

      "AND (Phone LIKE ?) AND (Email LIKE ?)");

    delete = connection.prepareStatement(

      "DELETE FROM NameCard WHERE ID = ?");

    update = connection.prepareStatement(

      "UPDATE NameCard SET ID=?, Name=?, Company=?, " +

      "Title=?, Address1=?, Address2=?, Phone=?, " +

      "Email=?, Picture=? WHERE ID = ?");

    all = connection.prepareStatement(

      "SELECT ID, Name, Company, Title, Address1, " +

      "Address2, Phone, Email FROM NameCard");

  } catch (SQLException e) {

    e.printStackTrace();

    }

  }



  // Other methods

}


    
Other methods in the DBManager provide access to the database via simple JDBC API calls. The following code snippet (Listing 11.3) demonstrates methods to search and manipulate name card records. These methods make heavy use of the SQL templates we defined earlier.
    
 Listing 11.3. The DBManager class (Part 2) in Contact Manager
    
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;

}