This chapter describes how to use the local SQLite database with an ADF Mobile application.
This chapter includes the following sections:
SQLite is a relational database management system (RDBMS) specifically designed for embedded applications.
SQLite has the following characteristics:
It is ACID-compliant: like other traditional database systems, it has the properties of atomicity, consistency, isolation, and durability.
It is lightweight: the entire database consists of a small C library designed to be embedded directly within an application.
It is portable: the database is self-contained in a single file that is binary-compatible across a diverse range of computer architectures and operating systems
For more information, see 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
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 Documentation section of the SQLite site at http://www.sqlite.org/docs.html.
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 site at http://www.sqlite.org/lockingv3.html.
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 site 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 site at http://www.sqlite.org/lang_createtable.html.
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 site at http://www.sqlite.org/datatype3.html.
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 site at http://www.sqlite.org/different.html.
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.
A typical SQLite usage requires you to know the following:
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".
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.
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
scriptStream = 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.
Commit statements are ignored when encountered. Each statement is committed as it is read from the SQL script.
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).
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);