Best practices with Sqlite Android

Question

I'm developing an app with sqlite, but I don't know when I should open and close my database.

Is it good practice maintain the database open while app is running?
Should I open and close the database just when I'm reading/writing there?
Does it matter?

score 1 · Answer 1 · answered Mar 03 '13 at 15:16

1

Yes, it matters. In any application not just mobile it is always best practice to keep the database connection open for only as long as you need it to perform an operation on said database. That means that you should close the connection as soon as possible. Connections are a limited resource and it is important to not tie them up.

answered Mar 03 '13 at 15:16

Stan R.

15,757
4
50
58

First thanks for your answer. But,if a write and a read from database takes some time,why i should that? :\ An app,should be must fast as possible.. – user1866731 Mar 03 '13 at 15:22
I'm not sure if "opening/closing" the connection is going to make your application that much slower. Also please have a look at http://stackoverflow.com/questions/4842112/when-to-close-db-connection-on-android-every-time-after-your-operation-finished – Stan R. Mar 03 '13 at 15:23
From android API "Like getWritableDatabase(), this method may take a long time to return, so you should not call it from the application main thread" – user1866731 Mar 03 '13 at 15:29

score 1 · Accepted Answer · answered Mar 03 '13 at 15:17

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.

I disagree with keeping the connection open the whole time, there is no viable reason to do so. — Stan R., Mar 03 '13 at 16:07
@StanR. I said that that is what I would personally do. You can make arguments both for and against it. I'm not saying its the only method you should follow. — Raghav Sood, Mar 03 '13 at 16:09

Best practices with Sqlite Android

2 Answers2