Correct use of Sqlite on Android (SQLiteOpenHelper, connections, main thread, etc...) [closed]

Question

I'm searching info about SQLite on Android, and read some chapters of some books on Android Programming, but I don't know how to really use SQLite on Android correctly.

I'm developing an app that has Activities, Services and BroadcastReceivers, and all of them must read and write data in the database. So I have some questions:

• Where is the best place to create the SQLiteOpenHelper instance? I read about this, and it seems that the best way is to have only SQLiteOpenHelper for all the app.
• When, and where, do I need to get SQLiteDatabase object by calling dbHelper.getReadableDatabase() (or getWritableDatabase)? I need to do it on each query, closing it after each query?
• I read that I should never do database operations in the main thread, so I'm creating an Async task for each database operation I do in an Activity, is this the best way?

Answer 1

Most of the question is rather subjective. But let me take a stab at it

Where is the best place to create the SQLiteOpenHelper instance? I readed about this, and seems that the best way is to have only SQLiteOpenHelper for all the app.

You can create a Class in your project that extends SQLiteOpenHelper. This is helpful when you would be accessing the same Database in multiple Activities in your application.

When, and where, do I need to get SQLiteDatabase object by calling dbHelper.getReadableDatabase() (or getWritableDatabase)? I need to do it on each query, closing it after each query?

This would be done in the Class that extends SQLiteOpenHelper. For example: (this is an excerpt from a Vogella article provided at the end of this post)

public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
}

And in an Activity that will use the Class:

CommentsDataSource datasource = new CommentsDataSource(this);
datasource.open();

And a Cursor and the Database connection made from the Activity should be closed when you are done with fetching, modifying, adding, etc with the Database.

The code above will make sense when you have read the webpage linked later.

I readed that I should never do database operations in the main thread, so I'm creating an Async task for each database operation I do in an Activity, this is the best way?

Ideally, yes. I would personally recommend using an Asynctask especially when you would be dealing with large record sets.

Finally, I would suggest that you read up on this tutorial by Lars Vogella: http://www.vogella.com/articles/AndroidSQLite/article.html. It will address most of your concerns and queries. Good luck. :-)