Hey there! As someone who's fumbled through early Android SQLite struggles, I totally get how frustrating this is—especially when you're just starting out. Let's walk through the most common reasons this happens and how to fix them:

Common Causes & Fixes #

1. Unclosed Database Connections #

It's super easy to forget to close your SQLite connections after using them, and over time (especially when the app is in the background), unclosed connections can lead to resource leaks or locked databases.

• Fix: Always make sure to call close() on your SQLiteDatabase or SQLiteOpenHelper instance when you're done with it. For example, in your Activity/Fragment's onDestroy() method:

  override fun onDestroy() {
      super.onDestroy()
      myDbHelper.close() // Replace with your helper instance
  }
  

• Even better: Use try-with-resources if you're working directly with SQLiteDatabase to auto-close connections:

  val db = myDbHelper.writableDatabase
  db.use { // 'use' is a Kotlin extension that auto-closes the database
      // Perform your database operations here
  }
  

2. Incorrect SQLiteOpenHelper Instance Management #

If you're creating a new SQLiteOpenHelper every time you need to access the database (instead of reusing a single instance), you can end up with conflicting connections that cause issues when the app wakes up from the background.

• Fix: Implement a singleton pattern for your SQLiteOpenHelper to ensure only one instance exists across your app. Here's a quick example:

  class MyDbHelper private constructor(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
  
      companion object {
          @Volatile
          private var INSTANCE: MyDbHelper? = null
  
          fun getInstance(context: Context): MyDbHelper {
              return INSTANCE ?: synchronized(this) {
                  val instance = MyDbHelper(context.applicationContext)
                  INSTANCE = instance
                  instance
              }
          }
      }
  
      // ... onCreate() and onUpgrade() implementations
  }
  

  Always get the helper via MyDbHelper.getInstance(context) instead of creating a new one.

3. Corrupted Database File #

If your app crashes unexpectedly (or gets killed by the system while writing to the database), the SQLite file can become corrupted. When you reopen the app, the database can't be read properly.

• Fix: Add a check for database integrity when opening the database. You can run the PRAGMA integrity_check command to verify, and if it fails, delete the old database and recreate it:

  override fun onOpen(db: SQLiteDatabase) {
      super.onOpen(db)
      val cursor = db.rawQuery("PRAGMA integrity_check", null)
      if (cursor.moveToFirst()) {
          val result = cursor.getString(0)
          if (result != "ok") {
              // Database is corrupted—delete and recreate
              context.deleteDatabase(DATABASE_NAME)
              onCreate(db)
          }
      }
      cursor.close()
  }
  

4. Unhandled Background Thread Operations #

If you have background threads running database operations when the app goes to the background, those threads might not be properly cancelled. This can leave the database in a locked state, causing issues when you reopen the app.

• Fix: Use proper thread management (like Kotlin Coroutines with viewModelScope or lifecycleScope) to ensure background tasks are cancelled when the app is in the background. For example:

  lifecycleScope.launch {
      try {
          // Perform database operations
      } finally {
          // Clean up if needed
      }
  }
  

  The lifecycleScope will automatically cancel the coroutine when the Activity/Fragment is destroyed.

A Pro Tip for Small Apps #

If you're finding raw SQLite too cumbersome, consider using Room—Google's official SQLite wrapper. It handles connection management, thread safety, and even provides compile-time checks for your SQL queries. For a small app, it's way less error-prone than dealing with raw SQLite, and the learning curve is gentle for beginners.

内容的提问来源于stack exchange，提问作者Daniel Santos