Is it OK to use static "database helper" class?

Question

I have some Android projects and most of them are connected with SQLite databases. I'm interested is it a good programming practice (or a bad habbit) to use some static class like "DatabaseHelper.class" in which I would have all static method related for database manipulation. For example

public static int getId(Context context, String name) {
    dbInit(context);

    Cursor result = db.rawQuery("SELECT some_id FROM table WHERE some_name = '" + name + "'", null);
    result.moveToFirst();
    int id = result.getInt(result.getColumnIndex("some_id"));
    result.close();

    return id;
}

where dbInit(context) (which is used in all my static methods for database manipluation) is

private static void dbInit(Context context) {
    if (db == null) {
        db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
    }
}

Then when I need something I can easily call those method(s) with for example

int id = DatabaseHelper.getId(this, "Abc");

EDIT: Do I have to use dbClose on every connection or leave it open per-activity and close per-activity? So do I have change that upper code to something like this?

    ...
    dbClose();

    return id;
}

private static void dbClose() {
    if (db != null) {
        db.close();
    }
}

Answer 1

I would suggest you get into the habit of getting a database connection every time you need one, and releasing it back when you are done with it. The usual name for such a facility is a "database connection pool".

This moves the connection logic out of your actual code and into the pool, and allow you to do many things later when you need them. One simple thing, could be that the pool logs how long time a connection object was used, so you can get information about the usage of the database.

Your initial pool can be very simple if you only need a single connection.

Answer 2

I would definitely have your database related code in a separate class, but would really recommend against using a static class or Singleton. It might look good at first because of the convenience, but unfortunately it tightly couples your classes, hides their dependencies, and also makes unit testing harder.

The drawbacks section in wikipedia gives you a small overview of why you might want to explore other techniques. You can also head over here or over there where they give concrete examples of a class that uses a database access singleton, and how using dependency injection instead can solve some of the issues I mentioned.

As a first step, I would recommend using a normal class that you instantiate in your constructor, for ex:

public class MyActivity extends Activity {    
  private DBAccess dbAccess;
  public MyActivity() {
     dbAccess = new DBAccess(this);
  }    
}

As a second step, you might want to investigate frameworks like RoboGuice to break the hard dependency. You code would look something like:

public class MyActivity extends Activity {    
  @Inject private DBAccess dbAccess;
  public MyActivity() {
  }    
}

Let us know if you want more details!