10 Using the Local Database

10.1.1 Differences Between SQLite and Other Relational Databases

SQLite is designed for use as an embedded database system, one typically used by a single user and often linked directly into the application. Enterprise databases, on the other hand, are designed for high concurrency in a distributed client-server environment. Because of these differences, there is a number of limitations compared to Oracle databases. Some of the most important differences are:

• Concurrency

• SQL Support and Interpretation

• Data Types

• Foreign Keys

• Database Transactions

• Authentication

For more information, see the following:

• Documentation section of the SQLite website at http://www.sqlite.org/docs.html

• "Limits In SQLite" available from the Documentation section of the SQLite website at http://www.sqlite.org/limits.html

10.2.1 How to Connect to the Database

Connecting to the SQLite database is somewhat different from opening a connection to an Oracle database. That said, once you have acquired the initial connection, you can use most of the same JDBC APIs and SQL syntax to query and modify the database.

You use the java.sql.Connection object associated with your application to connect to the SQLite database. When creating the connection, ensure that every SQLite JDBC URL begins with the text jdbc:sqlite:.

Example 10-1 shows how to open a connection to an unencrypted database.

java.sql.Connection connection = new SQLite.JDBCDataSource
             ("jdbc:sqlite:/path/to/database").getConnection();

Example 10-2 shows how to open a connection to an encrypted database.

java.sql.Connection connection = new SQLite.JDBCDataSource
             ("jdbc:sqlite:/path/to/database").getConnection(null,"password");

In the preceding example, the first parameter of the getConnection method is the user name, but since SQLite does not support user-based security, this value is ignored.

10.2.2 How to Use SQL Script to Initialize the Database

Typically, you can use an SQL script to initialize the database when the application starts. Example 10-3 shows the SQL initialization script that demonstrates some of the supported SQL syntax (described in Section 10.1.1.2, "SQL Support and Interpretation") through its use of the DROP TABLE, CREATE TABLE, and INSERT commands and the NUMBER and VARCHAR2 data types.

DROP TABLE IF EXISTS PERSONS;

CREATE TABLE PERSONS
(
PERSON_ID NUMBER(15) NOT NULL,
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
EMAIL VARCHAR2(25) NOT NULL
);

INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 100, 'David', 'King', 'steven@king.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 101, 'Neena', 'Kochhar', 'neena@kochhar.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 102, 'Lex', 'De Haan', 'lex@dehaan.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 103, 'Alexander', 'Hunold', 'alexander@hunold.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 104, 'Bruce', 'Ernst', 'bruce@ernst.net');

To use the SQL script, add it to the ApplicationController project of your MAF application as a resource. Suppose a sample script has been saved as initialize.sql in the META-INF directory. Example 10-4 shows the code that you should add to parse the SQL script and execute the statements.

private static void initializeDatabaseFromScript() throws Exception {
   InputStream scriptStream = null;
   Connection conn = null;
   try {
      // ApplicationDirectory returns the private read-write sandbox area
      // of the mobile device's file system that this application can access.
      // This is where the database is created
      String docRoot = AdfmfJavaUtilities.getDirectoryPathRoot
                                (AdfmfJavaUtilities.ApplicationDirectory);
      String dbName = docRoot + "/sample.db";

      // Verify whether or not the database exists.
      // If it does, then it has already been initialized 
      // and no furher actions are required
      File dbFile = new File(dbName);
      if (dbFile.exists())
         return;

      // If the database does not exist, a new database is automatically 
      // created when the SQLite JDBC connection is created
      conn = new SQLite.JDBCDataSource("jdbc:sqlite:" + docRoot +
                                       "/sample.db").getConnection();

      // To improve performance, the statements are executed 
      // one at a time in the context of a single transaction
      conn.setAutoCommit(false);

      // Since the SQL script has been packaged as a resource within
      // the application, the getResourceAsStream method is used 
      scriptStream = Thread.currentThread().getContextClassLoader().
                            getResourceAsStream("META-INF/initialize.sql");
      BufferedReader scriptReader = new BufferedReader
                                        (new InputStreamReader(scriptStream));
      String nextLine;
      StringBuffer nextStatement = new StringBuffer();

      // The while loop iterates over all the lines in the SQL script,
      // assembling them into valid SQL statements and executing them as
      // a terminating semicolon is encountered
      Statement stmt = conn.createStatement();
      while ((nextLine = scriptReader.readLine()) != null) {
         // Skipping blank lines, comments, and COMMIT statements
         if (nextLine.startsWith("REM") ||
             nextLine.startsWith("COMMIT") || 
             nextLine.length() < 1)
            continue;
         nextStatement.append(nextLine);
         if (nextLine.endsWith(";")) {
            stmt.execute(nextStatement.toString());
            nextStatement = new StringBuffer();
         }
      }
      conn.commit();
   }
   finally {
      if (conn != null)
         conn.close();
   }
}

You invoke the database initialization code (see Example 10-4) from the start method of the LifeCycleListenerImpl, as Example 10-5 shows.

public void start() {
   try {
      initializeDatabaseFromScript();
   }
   catch (Exception e) {
      Trace.log(Utility.FrameworkLogger,
                Level.SEVERE, 
                LifeCycleListenerImpl.class, 
                "start", 
                e);
   }
}

10.2.3 How to Initialize the Database on a Desktop

Because SQLite databases are self-contained and binary-compatible across platforms, you can use the same database file on iOS, Android, Windows, Linux, and Mac OS platforms. In complex cases, you can initialize the database on a desktop using third-party tools (such as MesaSQLite, SQLiteManager, and SQLite Database Browser), and then package the resulting file as a resource in your application.

To use the database, add it to the ApplicationController project of your MAF application as a resource. Suppose a database has been saved as sample.db in the META-INF directory. Example 10-6 shows the code that you should add to copy the database from your application to the mobile device's file system to enable access to the database.

private static void initializeDatabase() throws Exception {
   InputStream sourceStream = null;
   FileOutputStream destinationStream = null;
   try {
      // ApplicationDirectory returns the private read-write sandbox area
      // of the mobile device's file system that this application can access.
      // This is where the database is created
      String docRoot = AdfmfJavaUtilities.getDirectoryPathRoot
                                (AdfmfJavaUtilities.ApplicationDirectory);
      String dbName = docRoot + "/sample.db";

      // Verify whether or not the database exists.
      // If it does, then it has already been initialized 
      // and no furher actions are required
      File dbFile = new File(dbName);
      if (dbFile.exists())
         return;

      // Since the database has been packaged as a resource within
      // the application, the getResourceAsStream method is used 
      sourceStream = Thread.currentThread().getContextClassLoader().
                            getResourceAsStream("META-INF/sample.db");
      destinationStream = new FileOutputStream(dbName);
      byte[] buffer = new byte[1000];
      int bytesRead;
      while ((bytesRead = sourceStream.read(buffer)) != -1) {
         destinationStream.write(buffer, 0, bytesRead);
      }
   }
   finally {
      if (sourceStream != null)
         sourceStream.close();
      if (destinationStream != null)
         destinationStream.close();
   }
}

You invoke the database initialization code (see Example 10-6) from the start method of the LifeCycleListenerImpl, as Example 10-7 shows.

public void start() {
   try {
      initializeDatabase();
   }
   catch (Exception e) {
      Trace.log(Utility.FrameworkLogger,
                Level.SEVERE, 
                LifeCycleListenerImpl.class, 
                "start", 
                e);
   }
}

10.2.4 What You May Need to Know About Commit Handling

Commit statements are ignored when encountered. Each statement is committed as it is read from the SQL script. This auto-commit functionality is provided by the SQLite database by default. To improve your application's performance, you can disable the auto-commit to allow a regular execution of commit statements by using the Connection's setAutoCommit(false) method.

10.2.5 Limitations of the MAF's SQLite JDBC Driver

The following methods from the java.sql package have limited or no support in MAF:

• The getByte method of the ResultSet is not supported. If used, this method will throw an SQLException when executed.

• The execute method of the Statement always returns true (as opposed to returning true only for statements that return a ResultSet).

10.2.6 How to Use the VACUUM Command

When records are deleted from an SQLite database, its size does not change. This leads to fragmentation and, ultimately, results in degraded performance. You can avoid this by periodically running the VACUUM command.

Typically, the VACUUM command should be run from a properly registered LifeCycleListener implementation (see Section 4.7, "Using Lifecycle Listeners in a Mobile Application").

10.2.7 How to Encrypt and Decrypt the Database

MAF allows you to provide the SQLite database with an initial or subsequent encryption through the use of various APIs. Some of these APIs enable you to specify your own password for encrypting the database. Others are used when you prefer MAF to generate and, optionally, manage the password.

To encrypt the database with your own password:

1. Establish the database connection (see Section 10.2.1, "How to Connect to the Database").

2. Use the following utility method to encrypt the database with a new key:

   AdfmfJavaUtilities.encryptDatabase(connection, "newPassword");
   

To permanently decrypt the database encrypted with your own password:

1. Open the encrypted database with the correct password.

2. Use the following utility method:

   AdfmfJavaUtilities.decryptDatabase(connection);
   

To encrypt the database using the MAF-generated password:

1. Generate a password using the following method:

   GeneratedPassword.setPassword("databasePasswordID", "initialSeedValue");
   

   This method requires both a unique identifier and an initial seed value to aid the cryptographic functions in generating a strong password.

2. Retrieve the created password using the previously-specified ID as follows:

   char[] password = GeneratedPassword.getPassword("databasePasswordID");
   

3. Establish the database connection (see Section 10.2.1, "How to Connect to the Database").

4. Encrypt the database as follows:

   AdfmfJavaUtilities.encryptDatabase(connection, new String(password));
   

To decrypt the database and delete the MAF-generated password:

1. Obtain the correct password as follows:

   char[] password = GeneratedPassword.getPassword("databasePasswordID");
   

2. Establish the database connection and decrypt the database as follows:

   java.sql.Connection connection =
                       SQLite.JDBCDataSource("jdbc:sqlite:/path/to/database").
                       getConnection(null, new String(password));
   

3. Optionally, delete the generated password using the following method:

   GeneratedPassword.clearPassword("databasePasswordID");
   

10.2.8 What You May Need to Know About the StockTracker Sample Application

The StockTracker sample application uses a custom SQLite database file that is packaged within this application. The database file contains a table with four records which include information on four stocks. When the application is activated, it reads data from the table and displays the four stocks. The information about the stocks can be subject to CRUD operations: the stocks can be created, reordered, updated, and deleted through the user interface. All the CRUD operations, including reordering of stocks, are updated in the SQLite database.