Simplifying SQLite Tasks Using AndroidWithoutStupid

SQLite is an extremely robust database with a very light footprint. Because of this, it has easily become the most widely deployed database in the world. SQLite is natively supported on Android and is the default choice for most Android app developers.
Database operations have to be performed very carefully in Android, as mistakes can easily corrupt
your app and cause its VM to be killed. Apart from that, database operations require several lines of code to get things right. In this article, you will learn how to avoid all the hassles by using the AndroidWithoutStupid Java Library. I will also present a few design considerations to make your database operations robust and efficient.

AndroidWithoutStupid Java Library
Like SQLite, AndroidWithoutStupid is public domain software. You can use the code as it is or cut-and-paste whatever is required. I wrote this library because Android apps typically require a lot of apparently unnecessary and distracting fluff. For example, to show a simple message, you need to write the following:

Toast.makeText(
  getApplicationContext(),
“  Hello, World!”,
  Toast.LENGTH_SHORT).show();

With AndroidWithoutStupid, you can use a one-liner MvMessages.showMessage() method
wherever you want, without much ado. Once initialised, the same MvMessages instance can be used to display notifications, menus, prompts and many other things.

// Initialization
MvMessages oink = new MvMessages(getApplicationContext());
...
// Wherever required
oink.showMessage(“Hello, World!”);

Similarly, AndroidWithoutStupid library has other classes to perform a variety of Android-related tasks – all requiring the least number of steps.
For database operations, AndroidWithoutStupid has the MvSQLiteDB class. You don’t have to subclass SQLiteOpenHelper, parse ContentValues objects, or worry about known/unknown exceptions. No, siree! You create an MvSQLiteDB instance and start using SQL statements, similar to how you would do with desktop or Web applications. You don’t even have to bother with opening or closing the database!

Creating a new SQLite database
There are several constructors for the MvSQLiteDB class. To create a new SQLite database, you need to initialise the class with the path for the database file and the table definition statements, as follows:

// Data definition list
ArrayList<String> oDDList = new ArrayList<String>();
oDDList.add(“CREATE TABLE countries ("“ +
  "“ country_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,”" +
“  " country_name TEXT UNIQUE NOT NULL, “" +
“  " country_continent TEXT NOT NULL);"”);
oDB = new MvSQLiteDB(“"/sdcard/countries.sq3”", oDDList);
// Requires write permission in app manifest XML
if (oDB.mbIsAvailable) {
  oink.showMessage("“DB success"”);
} else {
  oink.showMessage(“"DB failure”");
}

If the database file or the specified tables already exist, the constructor will not fail. Without a worry, you can just move along to the next steps. Please note that, with SQLite, you do not need to create an AUTOINCREMENT column. The built-in ROWID column might work just as well.

Using an existing SQLite database
What if you need to use a SQLite database file that already exists? Just specify the path in the
constructor and forget the table definition statements.

oDB = new MvSQLiteDB(“/mnt/sdcard/countries.sq3”);

What if you are paranoid and need to check whether the tables are actually there? Well, then check the sql_master table that SQLite automatically creates to store your database schema.

oDB = new MvSQLiteDB(“/sdcard/countries.sq3”);
MvException oCheckTable =
 oDB.getSingleResultForSelectSQL(
“     " SELECT COUNT(name)" “ +
“     " FROM sqlite_master"“ +
“     " WHERE TYPE=’table’ AND name In (‘COUNTRIES’);”",
       true); // returns a number
if (oCheckTable.mbSuccess) {
  if ((Long) oCheckTable.moResult == 1) { // number of tables
     oink.showMessage(“Yes, countries table is present”);
} else {
  oink.showMessage(“No, countries table is not present”);
}
}

AndroidWithoutStupid uses its MvException class to provide a universal object-type return variable
(MvException.moResult) and any exception (MvException.mException) encountered along the way. This helps in avoiding unnecessary nesting and indentation with multiple try-catch blocks. You can rely on simpler C-style true-or-false evaluations and not worry about exceptions. This makes the code compact and easier to read. Of course, you need to fix exceptions during development.
The exceptions are usually caused by mistakes in the SQL statements or by logical errors related to database constraints.

Adding records
AndroidWithoutStupid doesn’t mess with ContentValues objects. It is SQL statements all the way.

oDB = new MvSQLiteDB(“/sdcard/countries.sq3”);

MvException oAddRecord;
oAddRecord = oDB.executeSQL(
“  "INSERT INTO COUNTRIES (COUNTRY_NAME, COUNTRY_CONTINENT)“ +
“  "VALUES (‘India’, ‘Asia’);”);
if (oAddRecord.mbSuccess) {
  oink.showMessage(“India added”);
} else {
  oink.showMessage(“India was not added\n” +
                   oAddRecord.mException.getMessage());
}

Like most methods in MvSQLiteDB, the executeSQL() method automatically opens and closes the database. If you are adding several records one after another, if you choose not to auto-close the DB, remember to call the closeDB() method at the end. Else, your app will be killed.

oDB = new MvSQLiteDB("“/sdcard/countries.sq3"”);
oDB.executeSQL(
 "“INSERT INTO countries (country_name, country_continent) “ +
“ "VALUES (‘USA’, ‘North America’);"”,
false);
oDB.executeSQL(
 "“INSERT INTO countries (country_name, country_continent)"“ +
“ "VALUES (‘UK’, ‘Europe’);"”,
 false);
oDB.executeSQL(
“ "INSERT INTO countries (country_name, country_continent) “" +
“ "VALUES (‘Russia’, ‘Europe’);”",
  false);
oDB.executeSQL(
“"INSERT INTO countries (country_name, country_continent) “" +
“"VALUES (‘Egypt’, ‘Africa’);”",
false);
// do not forget this line if false is used with executeSQL()
oDB.closeDB();

Deleting and updating records
The executeSQL() method can also be used for any other SQL statements that do not return results, such as those that delete and update records.

MvException oDelete, oUpdate;
oDB = new MvSQLiteDB(“/sdcard/countries.sq3"”);

oDelete = oDB.executeSQL(
“    "DELETE FROM countries “" +
“    "WHERE country_name=’UK’;”");
if !oDelete.mbSuccess) { oink.showMessage("“Delete failed"”); }

oUpdate = oDB.executeSQL(
“UPDATE countries “ +
“ SET country_name=’United States of America (USA)’” +
“ WHERE country_name=’USA’;”);
if (!oUpdate.mbSuccess) { oink.showMessage(“Update failed”); }

Querying the database
When you need to query the database for a single result (1 row x 1 column), you can use the getSingleResultForSelectSQL() method, as follows:

MvException oQuery;
oDB = new MvSQLiteDB(“/sdcard/countries.sq3”);
oQuery = oDB.getSingleResultForSelectSQL(
      "“SELECT country_continent “ +
“      "FROM countries” +
“      "WHERE country_name=’India’”);
if (oQuery.mbSuccess) {
      oink.showMessage(“India is in “ + oQuery.moResult);
}
oQuery = oDB.getSingleResultForSelectSQL(
“       "SELECT COUNT(country_name) “ +
“       "FROM countries” +
“       "WHERE country_continent=’Europe’”,
       true); // it returns a number
if (oQuery.mbSuccess) {
oink.showMessage(oQuery.moResult + “countries from Europe”);
}

When performing queries that return several records, you need to use a cursor. After extracting the data from the cursor, you need to close the cursor and the database.

Cursor oCursor;
MvException oGetCursor;
oDB = new MvSQLiteDB(“"/sdcard/countries.sq3"”);
ArrayList<String> oCountryList = new ArrayList<String>();

oGetCursor = oDB.getCursorForSelectSQL(
 "“SELECT country_name FROM countries ORDER BY country_name”");
if (oGetCursor.mbSuccess) {
 oCursor = (Cursor) oGetCursor.moResult;
 if (oCursor.getCount() > 0) {
     for (int i = 0; i < oCursor.getCount(); i++) {
        oCursor.moveToNext();
        oCountryList.add(oCursor.getString(0));
        MvMessages.logMessage(“Found “ + oCursor.getString(0));
}
}
   oCursor.close();
}
oDB.closeDB();
// Code to use the ArrayList follows
// ...

Design tips
While AndroidWithoutStupid makes database operations seem simple, it is not enough to make your Android app robust and efficient. Here is why:

• File operations, network operations, database operations and other long-running operations should not be performed in your activity (screen) classes. This may make your app appear like it has become unresponsive and Android’s cleanup operations will find and kill it. Using threads and asynchronous tasks are not scalable solutions.
• Android kills and haphazardly recreates your activity when the layout needs to change, such as when the user rotates the screen. This may cause your database-tied fields to be left with no data or with the wrong data.

To solve the first problem, you need to move your database operations to a Service class or, even better, to an IntentService class (an on-demand version of Service). Your activity sends data requests and commands to the IntentService using an intent, and the IntentService returns data using broadcast intents. To receive the broadcast data, your activity uses a broadcast receiver and a receiver filter.

For the second problem, save the results of your database operations to the bundle object passed to the onSaveInstanceState() method and try to restore it from the bundle object passed to the onCreate() method. The amount of data that can be saved to the onSaveInstanceState() method is limited and the saving process itself cannot take too long. If your activity holds lots of data, then store only the index (position) of the current record. When the screen rotates, query the data again and automatically skip to the record, the index of which you had already saved. In the onCreate() method, you need to check that the bundle and the values in it are not null. If the saved values are not available in the bundle (it happens when the device is low on memory), then you may need to query the database again.

References
[1] http://sqlite.org/ “SQLite.org”
[2] https://github.com/vsubhash/AndroidWithoutStupid “AndroidWithoutStupid Java Library on GitHub”
[3] http://www.vsubhash.com/article.asp?id=131&info=AndroidWithoutStupid_Java_Library “Using AndroidWithoutStupid Java Library”