Skip Headers
Oracle® Fusion Middleware Mobile Developer's Guide for Oracle Application Development Framework
11g Release 2 (11.1.2.4.0)

Part Number E24475-03
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

11 Using the Local Database

This chapter describes how to use the local SQLite database with an ADF Mobile application.

This chapter includes the following sections:

11.1 Introduction to the Local SQLite Database Usage

SQLite is a relational database management system (RDBMS) specifically designed for embedded applications.

SQLite has the following characteristics:

For more information, see the SQLite website at http://www.sqlite.org.

For a sample usage of the local SQLite database, see an ADF Mobile sample application called HR whose data is based on the default HR schema that is provided with all Oracle databases. The data is stored in a local SQLite database and persisted between each startup. The HR application is located in the PublicSamples.zip file within the jdev_install/jdeveloper/jdev/extensions/oracle.adf.mobile/Samples directory on your development computer

11.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:

For more information, see the following:

11.1.1.1 Concurrency

At any given time, a single instance of the SQLite database may have either a single read-write connection or multiple read-only connections.

Due to its coarse-grained locking mechanism, SQLite does not support multiple read-write connections to the same database instance. For more information, see "File Locking And Concurrency In SQLite Version 3" available from the Documentation section of the SQLite website at http://www.sqlite.org/lockingv3.html.

11.1.1.2 SQL Support and Interpretation

Although SQLite complies with the SQL92 standard, there are a few unsupported constructs, including the following:

  • RIGHT OUTER JOIN

  • FULL OUTER JOIN

  • GRANT

  • REVOKE

For more information, see "SQL Features That SQLite Does Not Implement" available from the Documentation section of the SQLite website at http://www.sqlite.org/omitted.html.

For information on how SQLite interprets SQL, see 'SQL As Understood by SQLite" available from the Documentation section of the SQLite website at http://www.sqlite.org/lang_createtable.html.

11.1.1.3 Data Types

While most database systems are strongly typed, SQLite is dynamically typed and therefore any value can be stored in any column, regardless of its declared type. SQLite does not return an error if, for instance, a string value is mistakenly stored in a numeric column. For more information, see "Datatypes In SQLite Version 3" available from the Documentation section of the SQLite website at http://www.sqlite.org/datatype3.html.

11.1.1.4 Foreign Keys

SQLite supports foreign keys. It parses and enforces foreign key constraints. For more information, see the SQLite Foreign Key Support available from the Documentation section of the SQLite site at http://www.sqlite.org/foreignkeys.html.

11.1.1.5 Database Transactions

Although SQLite is ACID-compliant and hence supports transactions, there are some fundamental differences between its transaction support and Oracle's:

  • Nested transactions: SQLite does not support nested transactions. Only a single transaction may be active at any given time.

  • Commit: SQLite permits either multiple read-only connections or a single read-write connection to any given database. Therefore, if you have multiple connections to the same database, only the first connection that attempts to modify the database can succeed.

  • Rollback: SQLite does not permit a transaction to be rolled back until all open ResultSets have been closed first.

For more information, see "Distinctive Features of SQLite" available from the Documentation section of the SQLite website at http://www.sqlite.org/different.html.

11.1.1.6 Authentication

SQLite does not support any form of role-based or user-based authentication. By default, anyone can access all of the data in the file. However, ADF Mobile provides encryption routines that you can use to secure the data and prevent access by users without the valid set of credentials.

11.2 Using the Local SQLite Database

ADF Mobile contains an encrypted SQLite 3.7.9 database.

A typical SQLite usage requires you to know the following:

11.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 11-1 shows how to open a connection to an unencrypted database.

Example 11-1 Connecting to Unencrypted Database

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

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

Example 11-2 Connecting to 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.

Note:

SQLite does not display any error messages if you open an encrypted database with an incorrect password. Likewise, you are not alerted if you mistakenly open an unencrypted database with a password. Instead, when you attempt to read or modify the data, an SQLException is thrown with the message "Error: file is encrypted or is not a database".

11.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 11-3 shows the SQL initialization script that demonstrates some of the supported SQL syntax (described in Section 11.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.

Example 11-3 SQL Initialization Script

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 ADF Mobile application as a resource. Suppose a sample script has been saved as initialize.sql in the META-INF directory. Example 11-4 shows the code that you should add to parse the SQL script and execute the statements.

Example 11-4 Initializing Database from SQL Script

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();
   }
}

Note:

In Example 11-4, the error handling was omitted for simplicity.

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

Example 11-5 Invoking Database Initialization Code

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

11.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 ADF Mobile application as a resource. Suppose a database has been saved as sample.db in the META-INF directory. Example 11-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.

Example 11-6 Initializing Database on Desktop

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();
   }
}

Note:

In Example 11-6, the error handling was omitted for simplicity.

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

Example 11-7 Invoking Database Initialization Code

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

11.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.

11.2.5 Limitations of the ADF Mobile's SQLite JDBC Driver

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

  • 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).

11.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.

Note:

The VACUUM can take a significant amount of time when run on large databases (approximately 0.5 seconds per megabyte on the Linux computer on which SQLite is developed). In addition, it can use up to twice as much temporary disk space as the original file while it is running.

Typically, the VACUUM command should be run from a properly registered LifeCycleListener implementation (see Section 5.7, "About Lifecycle Event Listeners.").

11.2.7 How to Encrypt and Decrypt the Database

ADF Mobile allows you to provide the SQLite database with an initial or subsequent encryption. To do so, you have to establish the database connection (see Section 11.2.1, "How to Connect to the Database"), and then use the following utility method to encrypt the database with a new key:

AdfmfJavaUtilities.encryptDatabase(connection, "newPassword");

Caution:

If you open a database incorrectly (for example, use an invalid password to open an encrypted database), and then encrypt it again, neither the old correct password, the invalid password, nor the new password can unlock the database resulting in the irretrievable loss of data.

In addition to encrypting, you can permanently decrypt the database. To do so, open the encrypted database with the correct password, and then use the following method:

AdfmfJavaUtilities.decryptDatabase(connection);