Do I need to call getWritableDatabase() everytime I manipulate data

Question

I have a newbie question about sqlite databases in android:

Do I really need to retrieve a writeable database everytime I manipulate data?

So can I write a DAO like this:

class Dao {

        private final SQLiteDatabase database;

        public Dao(SQLiteOpenHelper databaseHelper){

             database = databaseHelper.getWritableDatabase();
    }

    public void insert(...){

       ContentValues cv = new ContentValues(4);
       database.insertOrThrow(TABLE, null, cv);
       ...
    }


    public void update(...){
        ContentValues cv = new ContentValues(4);
        database.update(....);
    }
}

or do I must write my dao like this:

class Dao {

        private final SQLiteOpenHelper databaseHelper;

        public Dao(SQLiteOpenHelper databaseHelper){

             this.databaseHelper = databaseHelper
    }

    public void insert(...){

       SQLiteDatabase database = databaseHelper.getWritableDatabase();
       ContentValues cv = new ContentValues(4);
       database.insertOrThrow(TABLE, null, cv);
       ...
    }


    public void update(...){
        SQLiteDatabase database = databaseHelper.getWritableDatabase();
        ContentValues cv = new ContentValues(4);
        database.update(....);
    }
}

If the second approach is the correct one: do I also need to close the database after every operation:

public void update(...){
    SQLiteDatabase database = databaseHelper.getWritableDatabase();
    ContentValues cv = new ContentValues(4);
    database.update(....);
    database.close();
}

I guess its the same for databaseHelper.getReadableDatabase() right?

score 11 · Accepted Answer · edited May 23 '17 at 12:00

11

You should close every cursor you open. But the database instance doesn't need to be closed after each use.

I usually fetch a writable database in onCreate() and store the resulting SQLiteDatabase as a member variable for each activity, and never explicitly close that instance. (But again, I do close every cursor as soon as I'm finished processing their results.)

See Managing SQLite Connections in Android for a little more discussion on the subject.

edited May 23 '17 at 12:00

Community

1
1

answered Sep 03 '13 at 15:04

Jon Adams

24,464
18
82
120

2

I do not want to do that in every Activity. So I would subclass the application and implement there something like a singleton, to ensure that the Dao (more specific the SQLiteOpenHelper) gets instantiated only once – sockeqwe Sep 03 '13 at 15:31
@sockeqwe: You _could_ put it in your Application as a static, but that's generally considered bad practice. It will also make testing activities more difficult. But if you don't care about good practices or formal code testing, then yeah, that could work. Just make sure none of your code ever closes the connection; otherwise it won't be obvious why some activities fail getting a connection intermittently. (Which is one of the reasons why it's bad practice.) – Jon Adams Sep 09 '13 at 14:29

Do I need to call getWritableDatabase() everytime I manipulate data

1 Answers1

Linked