Android SQLite Db When to Close

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.

When should I close an SQLiteDatabase object?

Your SQLiteOpenHelper instance is static, and therefore global in scope. With that in mind:

Am I doing it right? (probably not)

No.

When do I need to close a SQLiteDatabase object, if not in the onDestroy method?

Never. SQLite is transactional. There is no risk from failing to close the database.

_{Yes, this irks me too, but I have gone through the seven stages of grief over this, and I am on to "acceptance"}

In simpler scenarios, where there is a single component with access to the database, you might close it when that component is destroyed. In your case, your whole app, including background threads, have access to the database. In that case, you simply never close it.

When to close Sqlite database

To get Android's SQLite thread-safety you need to be using the same instance of your SQliteOpenHelper class. Once you do that, closing databases after every use like you're doing is correct, and SQliteOpenHelper takes care of concurrency.

An example via making your helper a singleton:

public class TemporarySqliteDB extends SQLiteOpenHelper {
  private static TemporarySqliteDB instance;

  @synchronized public static getInstance(Context context) {
    if (instance == null) {
      instance = new TemporarySqliteDB(context.getApplicationContext());
    }
    return instance;
  }

  // rest of class here
}

Then later something like

SQLiteDatabase db = TemporarySqliteDB.getInstance().getWriteableDatabase();
try {
  // use db
} finally {
  db.close();
}

What's happening with your current usage pattern is that instead of gainine SQLiteOpenHelper's thread safety you're using SQLite's concurrency safe guards on the sqlite file itself, which try to prevent corrupting the database with concurrent access which could be from separate processes.

How to Open/Close SQLite db in Android Properly

If you're using an instance of a DatabaseHelper class, and after you initialize the DBHelper object, every time you do work in the database you should call the open method before you do work, then create a new cursor, query the database, do work with the information you just stored in the cursor, when you're done close the cursor, then close the database. For example if you wanted to grab every item in a database you would do something like :

...    
DataBaseHelper db = new DataBaseHelper(this);
... 
db.open();
Cursor cursor = db.getAllItems(); 
maxCount = cursor.getCount(); 
Random gen = new Random();
row = gen.nextInt(maxCount); // Generate random between 0 and max
if (cursor.moveToPosition(row)) {
    String myString = cursor.getString(1);  //here I want the second column
    displayString(myString); //private method
}
cursor.close();
db.close(); 

getAllItems is a public method in my DatabaseHelper, it looks like this in case you were wondering

public Cursor getAllItems() {
    return db.query(DATABASE_TABLE, 
        new String[] {
            KEY_ROWID, 
            KEY_NAME
        }, 
        null, 
        null, 
        null, 
        null, 
        null);
}

This is how I access my database and I haven't gotten any of the errors you've got, and it works perfectly.

What is the best practice when closing SQLiteDatabase in Android?

Open and close your db before and after each operation, respectively.

Never leave it open, when not needed.

Your mantra should be: open it, use it, close it.

Repeat for every db operation.

Same goes for Cursors as well.

SQLite When should I close the db and cursor? Should I use asynchronous invocation?

Yes should always close a database connection after using it.

Writable database connections are more vulnerable as some other process may access the unclosed database connection and make changes to the database. which they cannot do through unclosed readable database connection.

Memory Leak should be minimized.
I just answered a similar question yesterday. Here it is :

Memory leak in Java is a situation where some objects are not used by the application any more, but Garbage Collection fails to recognize them as unused and so, does not clean it up.

Every time you create an object, some space in memory is reserved for that object. And the same is for any Database Connection. So, after using the connection, if you dont close it, the GC doesnt get to know that this object will not be used anymore and hence does not delete it. So, it stays there in memory, eating valuable resource while the rest of the program runs. [Hence resource leak].

This is not at all desired. Furthermore, this exposes your program to security issues, as the connection is open, it is vulnerable, and changes can be made to the database. Remember, Even after the Activity closes, if there is a running thread, or an AsyncTask, the database connection remains open as well along with the thread.

Further reading : What is a “Memory leak” in Java?

From StackOverflow : Closing database connection to avoid memory leak

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.

Android Open and Close Database

As with lots of things in computer programming, there is no single "correct proceed" for the timing of closing a SQLite database (via close() on SQLiteOpenHelper if you are using that, or close() on SQLiteDatabase otherwise).

The one fairly hard-and-fast rule is to not use multiple SQLiteDatabase objects for the same database across multiple threads at the same time. That can get you in trouble, unless you are doing your own thread synchronization. If you stick to one SQLiteDatabase object per database, thread synchronization is handled inside the SQLiteDatabase.

Because of the one-database-for-all-threads rule, and because in many cases tracking when threads may or may not want the database can be tricky when you have many different components, some developers take the approach of having a singleton database manager, which lazy-opens the database... and never closes it. For example, a database-backed ContentProvider uses this pattern, as a ContentProvider is never destroyed, so there is no clear time when to close the database.

Personally, I would go that route over the open-use-close approach, particularly if you are using SQLCipher for Android (an encrypted SQLite replacement). However, there is nothing especially wrong with the open-use-close approach if you are sure that only one thread at a time will be working with the database.

If only a single component uses the database, there is nothing wrong with triggering opening the database in onCreate() and closing it in onDestroy(). After all, if that is the only component that needs the database, you do not need the database when the component itself goes away.

Close your db in onDestroy() Comments: "OnDestroy() does not get called always! Use onStop()!" --> So onStart() has to open it.

If onDestroy() does not get called on a component, either you crashed with an unhandled exception in that component, or your process was terminated. The latter scenario is not a problem (again, this is how a ContentProvider works, since a provider is never destroyed). The former scenario should not be a problem, so long as you do not accidentally use that older SQLiteDatabase and some newer SQLiteDatabase at the same time across multiple threads.

The db works efficiently. No need to close it.

It is not a matter of efficiency. SQLite is transactional. Failing to close a database is not a problem, as there are no unflushed buffers or something that you have to worry about.

Open your db once and create a field and an accessor.

That has nothing directly to do with when you close a database.

Close after use.

That is a pattern, and done properly it can work.

open and close database Connection in android?

Use Singleton pattern and access using db=DatabaseHelper.getInstance(context).
It guarantees that only one database helper will exist across the entire application lifecycle.

public class DatabaseHelper extends SQLiteOpenHelper { 

  private static DatabaseHelper sInstance;

  private static final String DATABASE_NAME = "database_name";
  private static final String DATABASE_TABLE = "table_name";
  private static final int DATABASE_VERSION = 1;

  public static synchronized DatabaseHelper getInstance(Context context) {

    // Use the application context, which will ensure that you 
    // don't accidentally leak an Activity's context.
    if (sInstance == null) {
      sInstance = new DatabaseHelper(context.getApplicationContext());
    }
    return sInstance;
  }

  /**
   * Constructor should be private to prevent direct instantiation.
   * make call to static method "getInstance()" instead.
   */
  private DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }
}

And access using :

db=DatabaseHelper.getInstance(this);

And also you can close database connection in catch block if needed.
I hope it help.

---

Related Topics