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

Question

This question is a follow up question to this:

When should I close a cursor and db?

I'm learning how to use SQLite for Android and I'm using this tutorial for examples:

http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/

I don't see any consistency as when they close the db or cursor. For example there's this method which closes the db:

public void addContact(Contact contact) 
{
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName()); // Contact Name
values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number

// Inserting Row
db.insert(TABLE_CONTACTS, null, values);
db.close(); // Closing database connection
}

But right after it there's this method which does not close it:

public Contact getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
        KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
        new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
    cursor.moveToFirst();

Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
        cursor.getString(1), cursor.getString(2));
// return contact
return contact;
}

Same thing for closing the cursor.

Why was it correct to close the db after adding a contact but not after getting one? Is it a good practice to always close the db after calling getWritableDatabase() or getReadableDatabase()? Also, if I want to add several items to the DB, is it a good practice to keep the connection to the DB open and insert many items one by one, or should I somehow insert them all togather with a single query?

I have also read that you suppose to call getWritableDatabase() or getReadableDatabase() with an AsyncTask or IntentService, but the methods in the tutorial use it serially. Which is a better practice?

Answer 1

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