Android comes with SQLite (version 3.5.9+), which has been available on the platform since release 1.5 of the OS (the “Cupcake” release). For readers unfamiliar with SQLite, it is a self-contained, server-less, transactional SQL database. While it has its limitations, SQLite serves as a powerful tool in the Android developer’s arsenal.
What I’ll principally cover here is one way to use a SQLite database in Android, concentrating on its management; specifically, creation and update, rather than all the runtime operations.
Managing SQLite
To begin with, we can manage SQLite using a class that extends SQLiteOpenHelper, which comes with a constructor and two required methods; onCreate and onUpgrade.
Naturally, the first of these is executed when the constructor is instantiated; it is here that, via the superclass, we provide four important pieces of data:
- Context. This is the context of the application. It can be useful to set this in the constructor and store it locally for later use in other methods.
- Database name. This is the filename (as a
String) of the physical database file being accessed. - Cursor factory. The cursor factory in use, if any.
- Database Version. This is the version of your database (as an integer), which I’ll discuss in more detail later. Your initial version should be 1.
For our example, we put these four pieces together and get the following:
|
1
2
3
4
5
6
7
8
9
10
11
|
class DB extends SQLiteOpenHelper { final static int DB_VERSION = 1;final static String DB_NAME = "mydb.s3db"; Context context; public DB(Context context) { super(context, DB_NAME, null, DB_VERSION); // Store the context for later use this.context = context; } |
The constructor does two things. First, it checks whether the database exists and, if not, will call the onCreate method. Second, if the database does exist, it will check whether the existing database version number differs from the one implemented in the constructor, so as to determine if the database has been updated. If it has, the onUpgrade method will be called.
Additionally, as we now know that the onCreate method is called only when the database does not exist, it can be used as a handy way to determine if you’re dealing with a first run of the application following installation. As such, you can use this method to call any other methods that you need executed only on the first run, such as EULA dialogs.
Let’s look at the database itself. For the purposes of this article, I’m just going to use a very simple employee database with a SQL creation script as follows:
|
1
2
3
4
5
6
7
|
CREATE TABLE employees ( name TEXT NOT NULL, ext TEXT NOT NULL, mob TEXT NOT NULL, age INTEGER NOT NULL DEFAULT '0'); |
We can easily construct this by hard-coding and executing the creation SQL, line by line, in our code as follows:
|
1
2
3
4
5
6
|
@Overridepublic void onCreate(SQLiteDatabase database) { database.execSQL(
"CREATE TABLE employees ( _id INTEGER PRIMARY KEY " + "AUTOINCREMENT, name TEXT NOT NULL, ext TEXT NOT NULL, " + "mob TEXT NOT NULL, age INTEGER NOT NULL DEFAULT '0')");} |
As you can see, this can get quite unwieldy once the database reaches a certain size and complexity, so the ideal solution would be to bundle a SQL creation script as an asset file. To use this approach, you need to write a method that takes in a SQL script from the assets directory and parses it, executing it line by line:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
@Overridepublic void onCreate(SQLiteDatabase database) {executeSQLScript(database, "create.sql");}private void executeSQLScript(SQLiteDatabase database,
String dbname) {ByteArrayOutputStream outputStream = new
ByteArrayOutputStream(); byte buf[] = new byte[1024]; int len; AssetManager assetManager = context.getAssets(); InputStream inputStream = null; try{ inputStream = assetManager.open(dbname); while ((len = inputStream.read(buf)) != -1) { outputStream.write(buf, 0, len); } outputStream.close(); inputStream.close(); String[] createScript =
outputStream.toString().split(";"); for (int i = 0; i < createScript.length; i++) { String sqlStatement = createScript[i].trim(); // TODO You may want to parse out comments here if (sqlStatement.length() > 0) { database.execSQL(sqlStatement + ";"); } } } catch (IOException e){ // TODO Handle Script Failed to Load } catch (SQLException e) { // TODO Handle Script Failed to Execute }} |
While this is a more complex approach than simply executing each SQL statement for very simple databases, it quickly pays dividends once the database becomes more complex or you need to pre-populate it. You’ll also see that I abstracted the creation into a separate method called executeSQLScript so that it can be reused in other situations.
Interacting with the Database
Now that the database is created, I want to be able to interact with it. Here’s a brief rundown:
The first step is to open the database and there are two ways to do this: usinggetReadableDatabase() and getWritableDatabase(). The former is faster and uses fewer resources. It should be used for anything that does not require writing or changing the database. The latter call is better suited to INSERTs, UPDATEs, and the like.
In Android, recordsets are returned from queries as Cursor objects. To carry out a query, use either the query() or rawQuery() method. For example, the following two calls return exactly the same data:
|
1
2
3
4
|
DB db = new DB(this);SQLiteDatabase qdb = db.getReadableDatabase();Cursor recordset1 =
qdb.query("mytable", null, null, null, null, null, null);Cursor recordset2 = qdb.rawQuery("SELECT * FROM mytable", null); |
The first call uses a bewildering number of parameters. They are the table name, a string array of the column names, the WHERE clause, the selection arguments, the GROUP BY clause, the HAVING clause, and the ORDER BY clause, respectively. You’ll note that setting many of these as null has the effect of their being treated as wildcards or omitted altogether.
Most of these parameters are fairly straightforward to anyone familiar with SQL. The selection arguments, however, require a little more explanation because they form a string array that interacts with the WHERE parameter, systematically replacing any “?” characters in the clause with a value from the array.
With the rawQuery() approach, there are only two parameters: the first is the SQL query, and the second is the selection argument — akin to those used in the query method. Selection arguments may be preferable to use with complex queries, such as those that use JOINs.
Similarly, INSERT, UPDATE, DELETE, and a range of other common operations are handled with methods similar to query(); or, as with rawQuery(), they can be executed as raw SQL code using execSQL().
Database Upgrades
Returning to management of the database, let’s look at the tricky scenario of database upgrades. Over time, an app will likely change. New functionality may be added, or it may be better optimized. These changes, in turn, may lead to a requirement to upgrade the database schema and change the value for DB_VERSION in the updated application code to reflect this.
One potential problem is that replacing our database with a new version will end up invalidating the previous version and lead to the loss of data that was present in existing user installations. A second problem is that, once our application has reached a point whereby more than two versions have been released, we cannot presume that the user has been diligently upgrading all along, so a simple upgrade from one version to the next may no longer work.
To deal with this, we already know that if we introduce a new database version, theonUpgrade() method will be called. So ideally, we can use our SQL script parser method and execute one or more update scripts.
Let’s look at what we intend to change in Version 2 of our database in the example:
- Normalize the phone number data (extension, mobile) into a separate “numbers” table, which includes a numeric field to denote the type of phone number.
- Add a salary field to the employee table.
Using the Version 1 schema as a starting point, this can be handled by writing a SQL script that updates the schema and then populates it with the data from the older version:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
CREATE TABLE numbers ( _id INTEGER PRIMARY KEY AUTOINCREMENT, employid INTEGER NOT NULL, number TEXT NOT NULL, ntype INTEGER NOT NULL DEFAULT '0');CREATE INDEX employid ON numbers(employid);INSERT INTO numbers (employid, number, ntype) SELECT _id, ext, 0
FROM employees;INSERT INTO numbers (employid, number, ntype) SELECT _id, mob, 1
FROM employees;CREATE TABLE temp ( _id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, salary INTEGER NOT NULL DEFAULT '0');INSERT INTO temp (_id, name) SELECT _id, name FROM employees;DROP TABLE employees;ALTER TABLE temp RENAME TO employees; |
Naturally, the more complex the changes in your database schema, the more complex the script you’ll need to write to handle this. SQLite has more limited support for SQL than many databases, so sometimes you’ll need to devise workarounds for these limitations. For example, in the aforementioned update script, I had to employ a temporary table as a workaround for the lack of DROP COLUMN functionality.
Now that I have the SQL upgrade script, I need to handle how it is executed when theonUpgrade method is called. One approach is to do the following:
|
1
2
3
4
5
6
7
8
9
10
11
|
@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if (newVersion > oldVersion) { switch (oldVersion) { case 1: executeSQLScript(database, "update_v2.sql"); case 2: executeSQLScript(database, "update_v3.sql"); }}} |
There are two things to note in this code. The first is that I check to see whether the new database version is greater than the old one. I wrote the code this way because theonUpgrade() method will be called any time these two versions are different, leading to a situation in which version downgrades are also possible. Our code does not envisage this ever occurring, but an else clause and accompanying code could be added to handle this.
The second thing is that there are no break statements between the cases in our switch. This is because each script simply updates from one version to the next, meaning that an upgrade from Version 1 to 3 will first run the upgrade script from Version 1 to 2, then continue on to run the upgrade script from Version 2 to 3. If the database is already at Version 2, it will simply skip the first script and only run the upgrade script from Version 2 to 3.
Thus, each time you upgrade the database, you will only need to replace the create script with one that reflects the new schema (for new installs), and an update script (that handles only an update from the previous version) to handle all possible upgrades. Meanwhile, in our Java code, we need to update the value for DB_VERSION and, naturally, any operations that may be affected by the new database schema.
Conclusion
SQLite can be a very useful means to store and manage data in a persistent manner on Android devices. However, as with any database, care needs to be taken to administer it correctly, particularly with regard to version changes.
Using the script parser solution and saving this part of the application logic as a series of SQL scripts is an efficient and simple management technique to avoid having to write complex methods to the core application to handle each upgrade, thus allowing you to concentrate on the application’s business logic instead.
Thanks 🙂 Keep Coding 🙂
Solution's point... 
You must be logged in to post a comment.