Screaming Penguin

UPDATE DISCLAIMERThis is one way to do direct DB access with SQLite on Android, but it's not the only way. I have modified the pattern I personally use somewhat, and it's quite a bit different from the time this article was written (this article is old). I plan to update this article at some point when I have time, but for now just keep in mind that this is meant to get you started, but not be a complete/comprehensive/exclusive approach.
Also, after a server crash the formatting, and some images were lost, these items will be fixed as soon as I get a chance, sorry.

Because I often have to revisit this stuff myself, I thought I would write a quick reference tutorial on creating and using a database with an Android application. This isn't terribly well covered in the Android docs, and though many ContentProvider tutorials exist (such as the Unlocking Android code for chapter 5, and the NotePad tutorial included with the SDK), and these help a lot with general database concepts, they are really more complicated than what a basic application needs - a database to store and retrieve stuff. I will walk through the code and tools for an oversimplified example here, with the ultimate goal of inserting and retrieving some data from an database in an Android app, and then examining the database using a shell and the sqlite3 command line tool. The entire code for this example is available here: http://totsp.com/svn/repo/AndroidExamples/trunk/. NOTE This code was updated for part 2, and part 3 of this series, so it no longer exactly matches this example -- it's still a working sample app, it just now does a bit more than the original. First, to get this rolling, we need to create an Android application that HAS a database. We could use any built in application that has a database just to explore it, such as com.android.alarmclock), but we are going to create one here for completeness. After it's setup, the interface for our application will look like the screen shot shown below:
Yeah this project is ugly, and it only has one Activity, but for our purposes here we really aren't trying to create a fancy UI (or anything that is complicated on any level). To create this project we will use the Eclipse IDE. Along with Eclipse, we will also of course need to have the Android SDK with the correct Eclipse ADT Plug-In as a prerequisite too (to get that see the instructions at the aforelinked site). 1. To create a basic Android project, we will simply select File->New->Other->Android->Android Project. On the dialog we will then enter the application name AndroidExamples, and the package name com.totsp.androidexamples, along with some other settings, as shown in the figure below:
The target we choose must be one that we have installed when we setup or configued the Android SDK. For this example we are using 1.6 because it is still the most common Android platform that user's phones are running (we are using the 2.1 SDK, with a 1.6 Target). We will also set the "Min SDK Version" to "4" which is SDK 1.6. This is a confusing part of the Android setup at this point (names are transposed from "sdk" to "api" and such) but the details are documented somewhat. Once we have the default sample project in place, the next step will be to create a helper class that can create the database and encapsulate other SQL details. We will call this class DataHelper. Within this class (at the end) we will include an important inner class that provides a SQLiteOpenHelper. The full code is shown below:

package com.totsp.androidexamples;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

public class DataHelper {

   private static final String DATABASE_NAME = "example.db";
   private static final int DATABASE_VERSION = 1;
   private static final String TABLE_NAME = "table1";

   private Context context;
   private SQLiteDatabase db;

   private SQLiteStatement insertStmt;
   private static final String INSERT = "insert into " 
      + TABLE_NAME + "(name) values (?)";

   public DataHelper(Context context) {
      this.context = context;
      OpenHelper openHelper = new OpenHelper(this.context);
      this.db = openHelper.getWritableDatabase();
      this.insertStmt = this.db.compileStatement(INSERT);
   }

   public long insert(String name) {
      this.insertStmt.bindString(1, name);
      return this.insertStmt.executeInsert();
   }

   public void deleteAll() {
      this.db.delete(TABLE_NAME, null, null);
   }

   public List<String> selectAll() {
      List<String> list = new ArrayList<String>();
      Cursor cursor = this.db.query(TABLE_NAME, new String[] { "name" }, 
        null, null, null, null, "name desc");
      if (cursor.moveToFirst()) {
         do {
            list.add(cursor.getString(0)); 
         } while (cursor.moveToNext());
      }
      if (cursor != null && !cursor.isClosed()) {
         cursor.close();
      }
      return list;
   }

   private static class OpenHelper extends SQLiteOpenHelper {

      OpenHelper(Context context) {
         super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }

      @Override
      public void onCreate(SQLiteDatabase db) {
         db.execSQL("CREATE TABLE " + TABLE_NAME + " 
          (id INTEGER PRIMARY KEY, name TEXT)");
      }

      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         Log.w("Example", "Upgrading database, this will drop tables and recreate.");
         db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
         onCreate(db);
      }
   }
}

This class is simple, as I have said it would be. For example it's using a database with one table and one column, but, it still covers some core Android concepts. We won't go into great detail concerning this class here, it should be mostly understandable from the code, but a few important things to note are:

• it includes an implementation of SQLiteOpenHelper as an inner class
• it demonstrates two different ways of interacting with the database in code, with a SQLiteStatement for inserts (which has the advantage of being pre-compiled, versus regular, but easier SQLiteDatabase.query() methods you probably also want to be familiar with), and directly by querying for selects
• it shows a useful pattern (though again, oversimplified here) of exposing data persistence/retrieval methods on the helper

To use this class from the default Main.java class that we let the Android Eclipse Plug-In generate, we will modify it a bit to create an instance of our DataHelper and then use it to create and retrieve data as seen below.(NOTE: In the real world you might do this once per application, say by using the oft overlooked Android Application class, where you could create the DataHelper once, and then expose the reference to other classes, rather than in Activity.onCreate()):

package com.totsp.androidexamples;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;

import java.util.List;

public class Main extends Activity {
    
   private TextView output;
   
   private DataHelper dh;
   
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        this.output = (TextView) this.findViewById(R.id.out_text);
        
        this.dh = new DataHelper(this);
        this.dh.deleteAll();
        this.dh.insert("Porky Pig");
        this.dh.insert("Foghorn Leghorn");
        this.dh.insert("Yosemite Sam");        
        List<String> names = this.dh.selectAll();
        StringBuilder sb = new StringBuilder();
        sb.append("Names in database:\n");
        for (String name : names) {
           sb.append(name + "\n");
        }
        
        Log.d("EXAMPLE", "names size - " + names.size());
        
        this.output.setText(sb.toString());
        
    }
}

For this class to work, we also need to change the main.xml layout file it relies on. We need to include one additional TextView for the output, as seen below:

<?xml version="1.0" encoding="utf-8"?>
<ScrollView 
  xmlns:android="http://schemas.android.com/apk/res/android"
      android:layout_width="fill_parent"
      android:layout_height="wrap_content">
  <LinearLayout xmlns:android=
     "http://schemas.android.com/apk/res/android"
      android:orientation="vertical" 
      android:layout_width="fill_parent"
      android:layout_height="fill_parent">
    <TextView android:layout_width="fill_parent"
       android:layout_height="wrap_content"
       android:text="@string/hello" />
    <TextView android:id="@+id/out_text"
       android:layout_width="fill_parent"
       android:layout_height="wrap_content"
       android:text="" />
  </LinearLayout>
</ScrollView>

With that, we have an application that we should be able to launch and run in the emulator, and it should look like the first screen shot we saw above - a basic black screen with white text and a few names as output. The names come from the database, which now exists, and now we can move on to using sqlite3. Android uses SQLite as it's built in embedded database. If you need to store local application data, rather than going to simpler mechanisms like the file system, or more complicated means such as the network, you use the database. To examine the database we can login using the shell provided by the Android Debug Bridge. To use this we need the "tools" folder of the SDK on our path (see the SDK documentation if you need more information about that). If we start the app in Eclipse (Run As -> Android Application), and leave it running, we should then be able to login with the command:

ccollins@crotalus:~$ adb -e shell
# 

The -e option tells ADB to use the emulator, rather than a possible connected device (and returns an error if more than one emulator is running). The "#" is the command prompt, we are logged in.

Once logged in we can browse around with the "ls" command. The directory we are interested in is /data/data/com.totsp.androidexamples/databases (each application has a directory at the path /data/data/PACKAGE_NAME). We can change to that directory with the "cd" command. Once there we can use the command line SQLite tool, sqlite3, to examine our database, as follows:

# sqlite3 example.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select * from sqlite_master;
table|android_metadata|android_metadata|3|CREATE TABLE android_metadata (locale TEXT)
table|table1|table1|4|CREATE TABLE table1 (id INTEGER PRIMARY KEY, name TEXT)
sqlite> 

We login with "sqlite3 [database_name]" and then we can run basic SQL commands that are supported by SQLite (see the documentation there for full details). One handy table is the sqlite_master that we can see shows all the other tables inside our database.

Some other interesting commands are:

sqlite> .schema
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE table1 (id INTEGER PRIMARY KEY, name TEXT);
sqlite> .tables
android_metadata  table1          
sqlite> select * from table1;
1|Porky Pig
2|Foghorn Leghorn
3|Yosemite Sam
sqlite> 

We can use .help from within the sqlite3 shell (it has it's own shell, apart from the emulator shell we are logged in to), to see a full list of commands. Commands that start with "." are built in, and perform a function - such as .schema and .tables that we see above. SQL commands themselves can also be run directly by typing them in -- something we also see above (typed commands are run as soon as a ; is encountered and you press enter).

With that, we have the basics. We have an application that creates a database and stores and retrieves data using it, and we have done a bit of exploring with the SQLite tools in ADB. In the future I hope to expand on this article and add some more involved tables, and further examples such as Android unit testing (another not so well documented, but extremely useful, part of the platform).