14

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?
Sergio Viudes
  • 2,714
  • 5
  • 26
  • 44
  • Thanks for your negative votes ¬¬ – Sergio Viudes Apr 04 '13 at 10:48
  • 2
    best place to create SQLiteOpenHelper instance is in `ContentProvider`'s `onCreate` method... best place for using `getWritableDatabase()` is in `delete`/`insert`/`update` methods of `ContentProvider` and `getReadableDatabase()` in `query` ... you don't need to close those databases ... for avoid of using database operation on UI you can use `Loaders` with CP ... i know that writing CP is pain in the a** but with well written CP you avoid many problems (such as accessing database from multiple theread) – Selvin Apr 04 '13 at 10:50
  • Thanks Selvin. I would like to use SQLite without using a ContentProvider, because I don't need to share any data with other apps. I readed that I don't need a content provider in my app: http://touchlabblog.tumblr.com/post/32793099735/training-sqlite#_=_. There is no way to use SQLite correctly without using a ContentProvider? – Sergio Viudes Apr 04 '13 at 10:55
  • 1
    but why you don't wanna use CP ... for such cases (multiple application component (Activity, Ser, BR)) using CP has another added value ... if you fx insert something in service your Activity with fx ListView will get informed about this and reload items ... anyway you can try to put db access methods in custom Application class – Selvin Apr 04 '13 at 11:00
  • Ok Selvin, I'll take a look at CP. If using it will make my app working correctly, I'll use it. Thanks :) – Sergio Viudes Apr 04 '13 at 11:06

2 Answers2

7

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. :-)

Siddharth Lele
  • 27,623
  • 15
  • 98
  • 151
0

1.Where is the best place to create the SQLiteOpenHelper instance?

SQLiteOpenHelper helps you to connect with the tables you defined using it.So you can define all of your tables in one SQLiteOpenHelper class.

2.When, and where, do I need to get SQLiteDatabase object by calling dbHelper.getReadableDatabase() (or getWritableDatabase)?

Whenever you want to write data in your defined tables you will need to call getWritableDatabase and for reading from them you will need getReadableDatabase

You can make CRUD(Create, Retrieve, Update, Delete) functions for these purposes.

3.Never do database operations in the main thread!

I'm not sure about this one.

well here's a link for you to start on with SQLite- http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/

Rahul
  • 47
  • 8
  • Thanks for your answer. About SQLiteOpenHelper, I know what it is, in fact, I did it, and my app is working with my SQLite database, but I needed to create more than one SQLiteOpenHelper because using only one to access database from different threads cased my app to fail. I readed that I should create only 1 (http://touchlabblog.tumblr.com/post/32793099735/training-sqlite#_=_), so I don't need exactly how to handle it. About getReadableDatabase/getWritableDatabase, I'm doing like your tutorial: open connection -> query -> close connection. – Sergio Viudes Apr 04 '13 at 11:02