sqlite - Easy database access methods in Android

Question

I am currently following a SQLite access tutorial for Android. It has presented to me a sample 'DBAdapter' class, as below:

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

public class DBAdapter {
static final String KEY_ROWID = "_id";
static final String KEY_NAME = "name";
static final String KEY_EMAIL = "email";
static final String TAG = "DBAdapter";

static final String DATABASE_NAME = "MyDB";
static final String DATABASE_TABLE = "contacts";
static final int DATABASE_VERSION = 1;

static final String DATABASE_CREATE =
        "create table contacts (_id integer primary key autoincrement, "
        + "name text not null, email text not null);";

final Context context;
DatabaseHelper DBHelper;
SQLiteDatabase db;

public DBAdapter(Context ctx)
{
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper
{
    DatabaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        try
        {
            db.execSQL(DATABASE_CREATE);
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }
}

//---opens the database---
public DBAdapter open() throws SQLException
{
    db = DBHelper.getWritableDatabase();
    return this;
}

//---closes the database---
public void close()
{
    DBHelper.close();       
}   

//---insert a contact into the database---
public long insertContact(String name, String email)
{
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME,  name);
    initialValues.put(KEY_EMAIL,  email);
    return db.insert(DATABASE_TABLE,  null,  initialValues);
}

//---deletes a particular contact---
public boolean deleteContact(long rowId)
{
    return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;        
}

//---retrieves all the contacts---
public Cursor getAllContacts()
{
    return db.query(DATABASE_TABLE,  new String[] {KEY_ROWID,  KEY_NAME,  KEY_EMAIL}, null, null, null, null, null);
}

//---retrieves a particular contact---
public Cursor getContact(long rowId) throws SQLException
{
    Cursor mCursor =
            db.query(true,  DATABASE_TABLE,  new String[] {KEY_ROWID,  KEY_NAME,  KEY_EMAIL}, KEY_ROWID + "="
                    + rowId, null, null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

//---updates a contact---
public boolean updateContact(long rowId, String name, String email)
{
    ContentValues args = new ContentValues();
    args.put(KEY_NAME,  name);
    args.put(KEY_EMAIL,  email);
    return db.update(DATABASE_TABLE,  args,  KEY_ROWID + "=" + rowId,  null) > 0;
}
}

Now if I wanted to use this class to insert a contact, I would need to write the following:

DBAdapter db = new DBAdapter(this);
db.open();
long id = db.insertContact("name", "email");
db.close();

As I will most definitely need to make lots of calls to access the database, I'd rather this was less verbose. I attempted to make a static class (so I wouldnt have to instantiate it), but couldn't, as the DBAdapter class is expecting to be passed in a context (eg; this).

I'd like to make a class that will allow me perform database operations with a single line of code, eg, dbClass.insertContact("name");, or dbClass.getContacts();. I don't want to instantiate the class, open the connection and close the connection every time - and yet I can't seem to use this with a static function. Could anyone give me any ideas?

Answer 1

This method works well for me.

Create a DataHelper class that opens your database and maintains a reference to the database object and which exposes the database.

public class CustomDataHelper {

private static final int DATABASE_VERSION = 30;
public SQLiteDatabase db = null;

public CustomDataHelper() {

    SQLiteOpenHelper o = new MyOpenHelper(CustomApp.app, "MyData.db");
    this.db = o.getWritableDatabase();

}

    // Rest of standard DataHelper class

private class MyOpenHelper extends SQLiteOpenHelper {

    MyOpenHelper(Context context,String DatabaseName) {
        super(context, DatabaseName, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
              // Standard data code here
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
              // Standard data code here
    }

}

}

Extend your Application class and store your Data object as a static field within it. Modify your Android.manifest to use your custom app class instead of the default one.

public class CustomApp extends Application {

public static CustomDataHelper data; // Access the data class from anywhere

public static CustomApp app; // Access the application context from anywhere

@Override
public void onCreate() {
    super.onCreate();

    app = this;
    data = new CustomDataHelper();
}
}

Any classes that require data access can refer to this object and get a reference to the open database. You can then put all data access code within the class that it relates to.

e.g. In your Contact class you could have a "Save" method that gets the database from the Application class and performs all necessary commands to save the contact details. This keeps all your data access code in the classes that it relates to. i.e All code that modifies contacts is within your contact class.

public class contact {

    public void Save() {

        CustomApp.data.db.execSQL("Your SQL Here");
        // etc

    }

}

As the DataHelper is in a static field, you can access it from anywhere at any time and it already has its own context from the Application Class.

Note the above excludes any error handling for the sake of simplicity.