Android: Closing those database objects

(Or, what the Notepad tutorial never told you.)

If you’ve used Android’s Notepad tutorial (Version 3) , or modeled your own Activity on Notepadv3, then you’ve probably seen log messages that look something like this:

android.database.sqlite.DatabaseObjectNotClosedException: 
    Application did not close the cursor or database object that was opened here
at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:62)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:36)
at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1145)
at android.database.sqlite.SQLiteDatabase.updateWithOnConflict(SQLiteDatabase.java:1671)
at android.database.sqlite.SQLiteDatabase.update(SQLiteDatabase.java:1622)
at com.android.demo.notepad3.NotesDbAdapter.updateNote(NotesDbAdapter.java:186)
at com.android.demo.notepad3.NoteEdit.saveState(NoteEdit.java:106)
at com.android.demo.notepad3.NoteEdit.onPause(NoteEdit.java:87)
at android.app.Activity.performPause(Activity.java:3842)
at android.app.Instrumentation.callActivityOnPause(Instrumentation.java:1190)
at android.app.ActivityThread.performPauseActivity(ActivityThread.java:3335)
at android.app.ActivityThread.performPauseActivity(ActivityThread.java:3305)
at android.app.ActivityThread.handlePauseActivity(ActivityThread.java:3288)
at android.app.ActivityThread.access$2500(ActivityThread.java:125)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2044)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:4627)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
at dalvik.system.NativeStart.main(Native Method)

This is resolved easily enough by overriding the onDestroy method in each of the Activities in your application — or at least, each Activity that uses the database. In the Notepadv3 app, this means overriding onDestroy in both the Notepadv3 and NoteEdit classes. The same definition is suitable for both classes:

    public void onDestroy() {
        super.onDestroy();

        // Replace mDbHelper as needed with your database connection, or
        // whatever wraps your database connection. (See below.)
        mDbHelper.close();
    }

In the Notepadv3 tutorial, the mDbHelper field is an instance of the class NotesDbAdapter, which wraps the database connection. NotesDbAdapter comes from google with the close method already defined, but there’s no code that calls it. With this change, we’re supplying the code that calls close at the right time. (I think.)

In general: If your activity opens a database by calling either SQLiteOpenHelper.getReadableDatabase or SQLiteOpenHelper.getWritableDatabase, then you should rely on SQLiteOpenHelper.close to close that database. Your activity’s onDestroy method must result in a call to the close method for any instances of SQLiteOpenHelper, or classes derived from it, that you’ve created. (In the code above, mDbHelper.close will have that effect.) SQLiteOpenHelper.close will then close any database handle that it created for you.

Android: Using DatabaseUtils.InsertHelper for faster insertions into SQLite database

AndroidOS includes the DatabaseUtils.InsertHelper class for speeding up insertions into an SQLite database. However, very little documentation or examples seem to be available to show how to use this class. I hope this post will help to make InsertHelper a little less mysterious than it apparently has been.

It’s often the case that bulk insertions are performed in the onCreate method of an SQLiteOpenHelper, so I’ll use that context for this example. (This has implications on transaction management, which in turn affects performance, as described below.)

Suppose the onCreate method currently looks something like this:

private class DatabaseHelper extends SQLiteOpenHelper {
    @Override
    public void onCreate(SQLiteDatabase db) {
        ContentValues values = new ContentValues();
        while (moreRowsToInsert) {
            // ... create the data for this row (not shown) ...

            // Add the data for each column
            values.put("Greek", greekData);
            values.put("Ionic", ionicData);
            // ...
            values.put("Roman", romanData);

            // Insert the row into the database.
            db.insert("columnTable", null, values);
        }
    }
    //...
}

Using DatabaseUtils.InsertHelper, this would be re-written as:

import android.database.DatabaseUtils.InsertHelper;
//...
private class DatabaseHelper extends SQLiteOpenHelper {
    @Override
    public void onCreate(SQLiteDatabase db) {
        // Create a single InsertHelper to handle this set of insertions.
        InsertHelper ih = new InsertHelper(db, "columnTable");

        // Get the numeric indexes for each of the columns that we're updating
        final int greekColumn = ih.getColumnIndex("Greek");
        final int ionicColumn = ih.getColumnIndex("Ionic");
        //...
        final int romanColumn = ih.getColumnIndex("Roman");

        try {
            while (moreRowsToInsert) {
                // ... Create the data for this row (not shown) ...

                // Get the InsertHelper ready to insert a single row
                ih.prepareForInsert();

                // Add the data for each column
                ih.bind(greekColumn, greekData);
                ih.bind(ionicColumn, ionicData);
                //...
                ih.bind(romanColumn, romanData);

                // Insert the row into the database.
                ih.execute();
            }
        }
        finally {
            ih.close();  // See comment below from Stefan Anca
        }
    }
    //...
}

As this shows, using InsertHelper is barely more complicated than using SQLiteDatabase.insert. The major differences are that you need to call ih.prepareForInsert() before adding (“binding”) the column data; and you need to obtain each column’s numeric index, which we get by calling ih.getColumnIndex() prior to the loop.

After replacing SQLiteDatabase.insert with DatabaseUtils.InsertHelper, the database insertion speed went from the equivalent of about 95 rows per second to about 525 rows per second. (“Equivalent of”, because the app also spends cycles creating the data to insert. Here, the performance timings are measured using constant data, eliminating that overhead.)

InsertHelper isn’t really doing anything magical here. It’s essentially a wrapper around compiled statements, which you can create yourself using SQLiteDatabase.compileStatement. Most people will probably find InsertHelper easier to use, though.

Other ways to speed up insertions

In addition to that gain, two more changes then brought the insertion speed to well over 900 rows per second. Whether these tricks techniques work for you will depend on your application.

Don’t bind empty columns

In my app, the data for at least 50% of the columns is empty. By skipping the call to ih.bind() when the column data is a null or empty string, I saw a roughly 30% performance boost.

Temporarily disable database thread locking

I’m loading the database during the onCreate method of my app’s SQLiteOpenHelper. During this time, it seems safe to assume that only one thread is accessing the database, so I use SQLiteDatabase.setLockingEnabled() to temporarily (let me emphasize that: temporarily) disable thread locks within the database API. This yielded about a 35% performance gain:

    public void onCreate(SQLiteDatabase db) {
    //...
    try {
        // *Temporarily* (have I emphasized that enough?) disable
        // thread locking in the database. Be sure to re-enable locking 
        // within a finally block.
        db.setLockingEnabled(false);
        // ... load the database ...
    }
    finally {
        db.setLockingEnabled(true);
    }

Transactions and performance

A number of people have cited performance gains through use of explicit transactions in SQLite. However, SQLiteOpenHelper creates a transaction before invoking its callback methods (onCreate, onUpgrade, and onOpen), so explicit transaction control is unnecessary within those methods. (SQLiteOpenHelper will assume that the transaction was successful unless your method throws an exception.)

You would need to manage your own transactions if your insertion code is running outside of one of SQLiteOpenHelper‘s callback methods. The main APIs for this are SQLiteDatabase.beginTransaction, SQLiteDatabase.setTransactionSuccessful, and SQLiteDatabase.endTransaction.

It is possible to nest transactions, but, not surprisingly, this doesn’t seem to help performance. In fact, I saw a very slight performance degradation when using nested transactions (approximately 1%, probably below the accuracy of the measurements.) I also tried periodically closing the current transaction — the first of these being the transaction that was opened by SQLiteOpenHelper — then opening a new one. This didn’t yield much improvement, if any.

Android: Spinners, SimpleAdapter, and (maybe) ViewBinder

The question came up on StackOverflow yesterday: Can a Spinner be configured to use a SimpleAdapter (and if so, how?) The user who asked the question, Chromium, ran into a couple of problems; the last of these was an IllegalStateException after clicking on the Spinner to make a selection.

A bit of searching turned up this issue about using a Spinner in combination with a SimpleAdapter to display a CheckedTextView; it seems that a ViewBinder must be set on the SimpleAdapter for that combination to work. Although it’s not the same problem, it still made me wonder if the SimpleAdapter / Spinner combination might need a ViewBinder for displaying TextView, too.

I wasn’t able to reproduce the IllegalStateException when I ran a sample program on AndroidOS 2.2, but I thought I’d try it on 1.5 in the emulator. Sure enough, I hit the same problem that Chromium reported. Apparently, whatever issue this is has been fixed by 2.2, and perhaps in earlier releases. (I haven’t tried other releases besides 2.2 and 1.5.)

And I also found that ViewBinder once again comes to the rescue, preventing the IllegalStateException from being thrown.

Like some other interfaces in Android (I’m looking at you, CursorToStringConverter. You too, FilterQueryProvider), ViewBinder’s bark is worse than its bite. In other words, the name may be a bit intimidating, but the implementation is a breeze.

Well, it would be a breeze, perhaps, if the documentation were clearer. The only method that you need to implement, setViewValue, receives three parameters: View view, Object data, String textRepresentation. But the documentation doesn’t tell us what these parameters really mean in a given context. I started by creating an empty setViewValue method, and set a breakpoint there. Inside the breakpoint, I verified that view is the TextView for displaying a single row in the Spinner; data is the String value in the Map for this row in the Spinner (“Red”, “Orange”, etc.); and textRepresentation is, well, the text representation of data, which in this case is that same String.

Here’s a sample program, adapted from Chromium’s example, that adds a ViewBinder to a SimpleAdapter. This works on AndroidOS 1.5 and 2.2 (and, presumably, intermediate versions as well.)

package org.oowb.HelloSpinner;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.SimpleAdapter;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemSelectedListener;

/**
 *  Displays a list of colors in a Spinner.
 */
public class HelloSpinner extends Activity {
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        // The key to use for reading the color from the Map
        final String[] from = new String[] { "color" };

        // The type of View to use for displaying the color name.
        // android.R.id.text1 is a standard resource for displaying text.
        final int[] to = new int[] { android.R.id.text1  };

        // Create the List of strings for the spinner to display. Each string
        // is embedded within a Map, using "color" as the key.
        final List<Map<String, String>> data = 
            new ArrayList<Map<String, String>>();
        final String[] colors = getResources().getStringArray(R.array.colors);

        for (int i = 0; i < colors.length; i++) {
            data.add(addData(colors[i]));
        }

        final SimpleAdapter simpleAdapter = 
            new SimpleAdapter(this, data, 
                    android.R.layout.simple_spinner_item, from, to);
        simpleAdapter.setDropDownViewResource(
                android.R.layout.simple_spinner_dropdown_item);

        // Add a ViewBinder to display a color name in a TextView within the
        // Spinner. (This isn't needed in AndroidOS 2.2. In earlier releases,
        // when we're displaying text data within a Spinner, and no ViewBinder
        // is set in the SimpleAdapter, an IllegalStateException is thrown.)
        SimpleAdapter.ViewBinder viewBinder = new SimpleAdapter.ViewBinder() {
            
            public boolean setViewValue(View view, Object data,
                    String textRepresentation) {
                // We configured the SimpleAdapter to create TextViews (see
                // the 'to' array, above), so this cast should be safe:
                TextView textView = (TextView) view;
                textView.setText(textRepresentation);
                return true;
            }
        };
        simpleAdapter.setViewBinder(viewBinder);

        final Spinner spinner = (Spinner) findViewById(R.id.spinner);
        spinner.setAdapter(simpleAdapter);

        // Add an OnItemSelectedListener to display the selected Color
        spinner.setOnItemSelectedListener(new OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> parent, View view,
                    int position, long id) {
                // Get the color name out of the Map
                final Map<String, String> data = 
                    (Map<String, String>) parent.getItemAtPosition(position);
                final String text = "Selected Color:-  " + data.get("color");

                Toast.makeText(parent.getContext(), text, 
                        Toast.LENGTH_LONG).show();
            }

            @Override
            public void onNothingSelected(AdapterView<?> arg0) {
                // Do nothing
            }
        });
    }

    /**
     * Convert the String that's passed in into a Map, with
     * "color" as the key, and the String as the value.
     * @param    colorName  The color to be inserted into a new Map
     * @return   the new Map
     */
    private Map<String, String> addData(String colorName) {
        Map<String, String> map = new HashMap<String, String>();
        map.put("color", colorName);
        return map;
    }
}

To complete the example, here are the layout (main.xml) and values files (arrays.xml and strings.xml):

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:padding="10dip"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content">

    <Spinner 
        android:id="@+id/spinner"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:drawSelectorOnTop="true"
        android:prompt="@string/prompt"
    />
</LinearLayout>
<?xml version="1.0" encoding="UTF-8"?>
<resources>
    <string-array name="colors">
        <item>Red</item>
        <item>Orange</item>
        <item>Yellow</item>
        <item>Green</item>
        <item>Blue</item>
        <item>Purple</item>
        <item>Violet</item>
        <item>Octarine</item>
    </string-array>
</resources>
<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">Color Chooser</string>
    <string name="prompt">Choose a color:</string>
</resources>

Android: Simpler AutoCompleteTextView with SimpleCursorAdapter

Sometimes it seems like nothing brings enlightenment as swiftly as publishing the results of one’s own confusion.

Earlier today, I published a lengthy examination of the work needed to supply an AutoCompleteTextView with data using a CursorAdapter. My intentions were pure, but my code was not. Where I’d seen a need for subclassing, it was because I hadn’t caught on to some of the handler methods provided by the SimpleCursorAdapter class.

Specifically, if we’re using a SimpleCursorAdapter, then:

This allows a significant refactoring of the SelectState class. Instead of the subclassed Adapter class, I’m now using anonymous inner classes to provide the cursor-to-name conversion and the filter query. The inner classes use essentially the same code as the methods that they’ve replaced.

The re-written SelectState follows. The full example can be downloaded as a ZIP file.

(See the earlier post for a description of the sample application’s functionality.)

package org.oowb.AutoCompleteExample;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AutoCompleteTextView;
import android.widget.Button;
import android.widget.FilterQueryProvider;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.SimpleCursorAdapter.CursorToStringConverter;

/**
 * A simple Android Activity to demonstrate:
 * 
 * 1) How to use an AutoCompleteTextView with a SimpleCursorAdapter
 * 
 * 2) How to access the cursor row for the user's choice, to obtain additional
 * data from that row when an item is selected.
 * 
 * @author Dan Breslau
 * 
 */
public class SelectState extends Activity {

    final static int[] to = new int[] { android.R.id.text1 };
    final static String[] from = new String[] { "state" };

    private TextView mStateCapitalView;
    private AutoCompleteTextView mStateNameView;
    private AutoCompleteDbAdapter mDbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        mDbHelper = new AutoCompleteDbAdapter(this);
        setContentView(R.layout.selectstate);
        Button confirmButton = (Button) findViewById(R.id.confirm);
        confirmButton.setOnClickListener(new View.OnClickListener() {
            public void onClick(View view) {
                setResult(RESULT_OK);
                finish();
            }
        });

        mStateCapitalView = (TextView) findViewById(R.id.state_capital);
        mStateNameView = (AutoCompleteTextView) findViewById(R.id.state_name);

        // Create a SimpleCursorAdapter for the State Name field.
        SimpleCursorAdapter adapter = 
            new SimpleCursorAdapter(this, 
                    android.R.layout.simple_dropdown_item_1line, null,
                    from, to);
        mStateNameView.setAdapter(adapter);

        // Set an OnItemClickListener, to update dependent fields when
        // a choice is made in the AutoCompleteTextView.
        mStateNameView.setOnItemClickListener(new OnItemClickListener() {
            public void onItemClick(AdapterView<?> listView, View view,
                        int position, long id) {
                // Get the cursor, positioned to the corresponding row in the
                // result set
                Cursor cursor = (Cursor) listView.getItemAtPosition(position);

                // Get the state's capital from this row in the database.
                String capital = 
                    cursor.getString(cursor.getColumnIndexOrThrow("capital"));

                // Update the parent class's TextView
                mStateCapitalView.setText(capital);
            }
        });

        // Set the CursorToStringConverter, to provide the labels for the
        // choices to be displayed in the AutoCompleteTextView.
        adapter.setCursorToStringConverter(new CursorToStringConverter() {
            public String convertToString(android.database.Cursor cursor) {
                // Get the label for this row out of the "state" column
                final int columnIndex = cursor.getColumnIndexOrThrow("state");
                final String str = cursor.getString(columnIndex);
                return str;
            }
        });

        // Set the FilterQueryProvider, to run queries for choices
        // that match the specified input.
        adapter.setFilterQueryProvider(new FilterQueryProvider() {
            public Cursor runQuery(CharSequence constraint) {
                // Search for states whose names begin with the specified letters.
                Cursor cursor = mDbHelper.getMatchingStates(
                        (constraint != null ? constraint.toString() : null));
                return cursor;
            }
        });
    }
}