Working with Android Databases

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

Consider creating a database adapter, which adds an abstraction layer that encapsulates database interactions. It should provide intuitive, strongly typed methods for adding, removing, and updating items. A database adapter should also handle queries and wrap creating, opening, and closing the database. It’s often also used as a convenient location from which to publish static database constants, including table names, column names, and column indexes.

The following snippet shows the skeleton code for a standard database adapter class. It includes an extension of the SQLiteOpenHelper class, used to simplify opening, creating, and upgrading the database.

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;

    public static final String KEY_ID=”_id”;

    public static final String KEY_NAME=”name”;

    public static final int NAME_COLUMN = 1;

    private static final String DATABASE_CREATE = “create table “ +

    DATABASE_TABLE + “ (“ + KEY_ID +“ integer primary key autoincrement, “ + KEY_NAME +   “text not null);”;

     private SQLiteDatabase db;

    private final Context context;

    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 void close() {

              db.close();

                                 }

public long insertEntry(MyObject _myObject) {

                 ContentValues contentValues = new ContentValues();

                 return db.insert(DATABASE_TABLE, null, contentValues);

                                                                                   }

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) {

                               MyObject objectInstance = new MyObject();

                               return objectInstance;

                                                  }

public int updateEntry(long _rowIndex, MyObject _myObject) {

                                String where = KEY_ID + “=” + _rowIndex;

                                ContentValues contentValues = new ContentValues();

                                return db.update(DATABASE_TABLE, contentValues, where, null);

                                                           }

private static class myDbHelper extends SQLiteOpenHelper {

                    public myDbHelper(Context context, String name,

                    CursorFactory factory, int version) {

                                     super(context, name, factory, version);

                                                                                     }

                  public void onCreate(SQLiteDatabase _db) {

                                   _db.execSQL(DATABASE_CREATE);

                                                                                }

                  public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {

                                       Log.w(“TaskDBAdapter”, “Upgrading from version “ +_oldVersion + “ to “+

_newVersion + “, which will destroy all old data”);

_db.execSQL(“DROP TABLE IF EXISTS “ + DATABASE_TABLE);

onCreate(_db);

}

}

}