When to Close Db Connection on Android? Every Time After Your Operation Finished or After Your App Exit

When to close db connection on android? Every time after your operation finished or after your app exit

I don't know of any performance penalties in frequent closing/opening of the database (regardless of its size). I think the answer to this question also depends on what type of application is accessing the database.

Do you "re-query" the database a lot?

Then it seems rectified to keep it open.

Do you fetch different data each time you fetch something?

Again, it seems reasonable to leave it open (as you won't gain in caching the data instead).

Are there any other applications accessing the same database?

If there is a risk for concurrency or blocking issues, it might be wise to close the database after finished reading/writing from/to it.

Generally I would say that you might gain more in caching data than in leaving the database open (contra closing it) when optimizing for performance.

Should I open() and close() my SQL database constantly or leave it open?

I tried to leave a connection open once -- I used it to stuff a repeater or something -- can't remember now.

Later in the program, I had another need to use the connection -- I think I had it so that when a user clicked a parent item in the repeater, a detail div would pop-up with more information for that item. This generated an error -- something to the effect of 'cannot open() on an open connection'.

I think the error might have been avoidable another way (like checking to see if the connection I was trying to open was already open), but as I thought about it, I realized I'd have to make that a standard practice throughout my app, and that seemed like too much work, so I just made it a standard practice to always close my connections after each use.

Connections stay in a connection pool -- I'm no whiz on that -- but if was curious about performance, I guess I'd keep that in mind, in terms of what it costs to open a connection multiple times -- whatever your situation is requiring anyway.

Another thought is that your DB admin may be able to force-close all open connections, or the db may close for some other reason. If you're not the dba, you might give a thought to risk/benefit of depending on something that you don't have long-term control over like keeping the connection open.

Android SQLite DB When to Close

i would keep it open the whole time, and close it in some lifecycle method such as onStop or onDestroy. that way, you can easily check if the database is already in use by calling isDbLockedByCurrentThread or isDbLockedByOtherThreads on the single SQLiteDatabase object every time before you use it. this will prevent multiple manipulations to the database and save your application from a potential crash

so in your singleton, you might have a method like this to get your single SQLiteOpenHelper object:

private SQLiteDatabase db;
private MyDBOpenHelper mySingletonHelperField;
public MyDBOpenHelper getDbHelper() {
    db = mySingletonHelperField.getDatabase();//returns the already created database object in my MyDBOpenHelper class(which extends `SQLiteOpenHelper`)
    while(db.isDbLockedByCurrentThread() || db.isDbLockedByOtherThreads()) {
        //db is locked, keep looping
    }
    return mySingletonHelperField;
}

so whenever you want to use your open helper object, call this getter method(make sure it's threaded)

another method in your singleton may be(called EVERY TIME before you try to call the getter above):

public void setDbHelper(MyDBOpenHelper mySingletonHelperField) {
    if(null == this.mySingletonHelperField) {
        this.mySingletonHelperField = mySingletonHelperField;
        this.mySingletonHelperField.setDb(this.mySingletonHelperField.getWritableDatabase());//creates and sets the database object in the MyDBOpenHelper class
    }
}

you may want to close the database in the singleton as well:

public void finalize() throws Throwable {
    if(null != mySingletonHelperField)
        mySingletonHelperField.close();
    if(null != db)
        db.close();
    super.finalize();
}

if the users of your application have the ability to create many database interactions very quickly, you should use something like i have demonstrated above. but if there is minimal database interactions, i wouldn't worry about it, and just create and close the database every time.

Best practices with Sqlite Android

Is it good practice maintain the database open while app is running?

It is a personal preference.

I would personally keep it open the whole time, and close it in some lifecycle method such as onStop() or onDestroy(). That way, you can easily check if the database is already in use by calling isDbLockedByCurrentThread() or isDbLockedByOtherThreads() on the single SQLiteDatabase object every time before you use it. This will prevent multiple manipulations to the database and save your application from a potential crash, which is possible if you accidentally have two open instances in the case where you open/close per read/write.

Keep in mind that your application may be killed while you have an open database connection.

Also, you should use transactions to reduce the write operations to the disk at a time.

Does it matter?

A database is just another file on the disk at the end of the day. If you try to write to it from multiple threads/instances at the same time, you will run into problems.

Is it good idea to close db in Insert method of db helper?

The exception is not related to the code but there is a problem:

this.db = db;

You're storing a reference to SQLiteDatabase you didn't obtain yourself with e.g. getReadableDatabase() or getWritableDatabase().

protected void drop(String table){
    //TODO Produces a damn error!
    db.execSQL("DROP TABLE IF EXISTS "+table);
}

Here you are using the stored reference which points to a database connection that is likely already closed. Use getWritableDatabase() here to get the db reference. Also, remove the SQLiteDatabase member variable. You don't need it anyway.

Internally SQLiteDatabase connections are reference counted. If the database is already open, opening it again just increments the reference count of the existing connection. Calling close() will decrement the reference counter and once it reaches exactly zero, the database is closed.

The database reference passed to onCreate() is opened and closed by the Android database framework.

Do I need to open and close the connection every time the schema changes?

I think what you're doing should be OK - but if you alter a table, then you better be sure to close and then re-open the database handle.

---

Related Topics