If you are trying to find the best way to work with SQLite connection on Android, asking yourself things like “Should I keep one single connetion opened or open and close everytime that I use the database?” “If I use a single connection, how and when to close? Do I need to close it?”, keep in mind that a sqlite connection is just a simple file handling. Yes… if you are opening a connection, you are actually getting a file reference to read and write on it. Could I become things lighter? So, you can keep a single connection for all app lifecycle without worry, when the app shuts down, it’ll let go of the file reference. In my opnion, the database connection should be held by a singleton. Using a DI Framework like Dagger would sound great!
What about exceptions like these below? Can a singleton database connection make this exception happen?
E/Database(234): Leak found
E/Database(234): Caused by: java.lang.IllegalStateException: SQLiteDatabase created and never closed
or
ERROR/Database(10982): close() was never explicitly called on database ‘/data/data/com.psyhclo/databases/calls.db’
ERROR/Database(10982): android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
This exceptions is thrown when you have opened more SQLiteDatabase instances than you have closed. It’s not just about you never close a database connection, it’s about open a second connection without close the first one. Actually, a database connection never closed in a singleton pattern can avoid this kind of exception and not be the cause of it. It is easy open a connection and forget to close, what a Singleton can avoid.
Tássio Auad 