Channels ▼
RSS

.NET

Using SQLite on Android


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: using getReadableDatabase() 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:

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, the onUpgrade() 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:

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 the onUpgrade method is called. One approach is to do the following:

@Override
public 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 the onUpgrade() 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.


Gaddo F. Benedetti is presently an independent analyst-engineer who consults for various European clients in the telecom and Internet industries.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video