Working With Sqlite Databases

It's good practice to create a helper class to simplify your database interactions.

The following section shows you how to create a database adapter class for your database. This abstraction layer encapsulates your database interactions. It will provide intuitive, strongly typed methods for adding, removing, and updating items. A database adapter should also handle queries and expose methods for creating, opening, and closing the database.

Prepared for ASHLEE KABAT, email: [email protected] Order number: 56760408 This PDF is for the purchaser's personal use in accordance with the Wrox Terms of Service and under US copyright as stated on this book's copyright page. If you did not purchase this copy, please visit to purchase your own copy.

It can also be used as a convenient location to publish static database constants, including table and column names.

Listing 7-1 shows the skeleton code for a standard database adapter class. It includes an extension of the SQLiteOpenHelper class (discussed in more detail in the following section), used to simplify opening, creating, and upgrading the database.

LISTING 7-1: Skeleton code for a standard database adapter implementation Available for download on import android.content.Context; import android.database.*;

import android.database.sqlite.*;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.util.Log;

public class MyDBAdapter {

private static final String DATABASE_NAME = "myDatabase.db"; private static final String DATABASE_TABLE = "mainTable"; private static final int DATABASE_VERSION = 1;

// The index (key) column name for use in where clauses. public static final String KEY_ID="_id";

// The name and column index of each column in your database, public static final String KEY_NAME="name"; public static final int NAME_COLUMN = 1;

// TODO: Create public field for each column in your table.

// SQL Statement to create a new database.

private static final String DATABASE_CREATE = "create table " + DATABASE_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " + KEY_NAME + " text not null);";

// Variable to hold the database instance private SQLiteDatabase db;

// Context of the application using the database. private final Context context; // Database open/upgrade helper private myDbHelper dbHelper;

public MyDBAdapter(Context _context) { context = _context;

dbHelper = new myDbHelper(context, DATABASE_NAME, null, DATABASE_VERSION);

public MyDBAdapter open() throws SQLException { db = dbHelper.getWritableDatabase(); return this;

public int insertEntry(MyObject _myObject) {

// TODO: Create a new ContentValues to represent my row // and insert it into the database, return index;

public boolean removeEntry(long _rowIndex) {

return db.delete(DATABASE_TABLE, KEY_ID + "=" + _rowIndex, null) > 0;

public Cursor getAllEntries () {

return db.query(DATABASE_TABLE, new String[] {KEY_ID, KEY_NAME}, null, null, null, null, null);

public MyObject getEntry(long _rowIndex) {

// TODO: Return a cursor to a row from the database and // use the values to populate an instance of MyObject return objectInstance;

public boolean updateEntry(long _rowIndex, MyObject _myObject) { // TODO: Create a new ContentValues based on the new object // and use it to update a row in the database. return true;

private static class myDbHelper extends SQLiteOpenHelper {

public myDbHelper(Context context, String name,

CursorFactory factory, int version) { super(context, name, factory, version);

// Called when no database exists in disk and the helper class needs

©Override public void onCreate(SQLiteDatabase _db) { _db.execSQL(DATABASE_CREATE);

// Called when there is a database version mismatch meaning that the version // of the database on disk needs to be upgraded to the current version. ©Override public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) { // Log the version upgrade.

Log.w("TaskDBAdapter", "Upgrading from version " +

_newVersion + ", which will destroy all old data");


LISTING 7-1 (continued)

// Upgrade the existing database to conform to the new version. Multiple // previous versions can be handled by comparing _oldVersion and _newVersion // values.

// The simplest case is to drop the old table and create a new one. _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE); // Create a new one. onCreate(_db);

Mobile Apps Made Easy

Mobile Apps Made Easy

Quick start guide to skyrocket your offline and online business success with mobile apps. If you know anything about mobile devices, you’ve probably heard that famous phrase coined by one of the mobile device’s most prolific creators proclaiming that there’s an app for pretty much everything.

Get My Free Training Guide

Post a comment