Room框架使用SQLite时因图片过大导致应用崩溃求助

问题描述 #

使用Room库开发基于SQLite的Android应用时，首次添加数据后跳转至包含RecyclerView的Fragment，应用1秒后崩溃。仅部分小图片能正常存入数据库，大图片会触发崩溃。

错误日志 #

E/SQLiteQuery: exception: Row too big to fit into CursorWindow requiredPos=0, totalRows=1; query: SELECT * FROM vet_product ORDER BY id ASC
E/AndroidRuntime: FATAL EXCEPTION: arch_disk_io_0
    Process: com.dimon.vetdatabasemobile, PID: 26700
    java.lang.RuntimeException: Exception while computing database live data.
        at androidx.room.RoomTrackingLiveData$1.run(RoomTrackingLiveData.java:92)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at java.lang.Thread.run(Thread.java:923)
     Caused by: android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=1
        at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:1001)
        at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:838)
        at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
        at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:153)
        at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:140)
        at com.dimon.vetdatabasemobile.db.dao_s.ProductDao_Impl$5.call(ProductDao_Impl.java:172)
        at com.dimon.vetdatabasemobile.db.dao_s.ProductDao_Impl$5.call(ProductDao_Impl.java:163)
        at androidx.room.RoomTrackingLiveData$1.run(RoomTrackingLiveData.java:90)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167) 
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641) 
        at java.lang.Thread.run(Thread.java:923) 

相关代码 #

实体类 #

@Parcelize
@Entity(tableName = "vet_product")
data class Product(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    val productName: String,
    val productPrice: Double,
    val productImage: Bitmap
) : Parcelable


@Parcelize
@Entity(tableName = "cart")
data class CartModel(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val productName: String,
    val productPrice: Double
) : Parcelable

Dao层 #

@Dao
interface ProductDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(product: Product)

    @Delete
    fun delete(product: Product)

    @Update
    fun update(product: Product)

    @Query("DELETE FROM vet_product")
    fun deleteAll()

    @Query("SELECT * FROM vet_product ORDER BY id ASC")
    fun getAllProducts(): LiveData<List<Product>>

}


@Dao
interface CartDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertToCart(cartModel: CartModel)

    @Query("DELETE FROM cart")
    fun deleteAllFromCart()

    @Query("SELECT * FROM cart ORDER BY id ASC")
    fun getCartModels(): LiveData<List<CartModel>>

}

仓库层 #

class ProductRepository(private val productDao: ProductDao) {

    val getAllProduct: LiveData<List<Product>> = productDao.getAllProducts()

    fun addProduct(product: Product) {
        productDao.insert(product)
    }

    fun updateProduct(product: Product) {
        productDao.update(product)
    }

    fun deleteProduct(product: Product) {
        productDao.delete(product)
    }

    fun deleteAllProducts() {
        productDao.deleteAll()
    }

}


class CartRepository(private val cartDao: CartDao) {

    val getAllCartModels: LiveData<List<CartModel>> = cartDao.getCartModels()

    fun addToCart(cartModel: CartModel) {
        cartDao.insertToCart(cartModel)
    }

    fun deleteAllFromCart() {
        cartDao.deleteAllFromCart()
    }

}

ViewModel层 #

class ProductViewModel(application: Application) : AndroidViewModel(application) {

    val getAllProducts: LiveData<List<Product>>
    private val repository: ProductRepository

    init {
        val productDao = VetDatabase.getInstance(application).productDao()

        repository = ProductRepository(productDao)
        getAllProducts = repository.getAllProduct
    }

    fun addProduct(product: Product) {
        viewModelScope.launch(Dispatchers.IO) {
            repository.addProduct(product)
        }
    }

    fun updateProduct(product: Product) {
        viewModelScope.launch(Dispatchers.IO) {
            repository.updateProduct(product)
        }
    }

    fun deleteProduct(product: Product) {
        viewModelScope.launch(Dispatchers.IO) {
            repository.deleteProduct(product)
        }
    }

    fun deleteAllProducts() {
        viewModelScope.launch(Dispatchers.IO) {
            repository.deleteAllProducts()
        }
    }

}


class CartViewModel(application: Application) : AndroidViewModel(application) {

    val getCartModels: LiveData<List<CartModel>>
    private val repository: CartRepository

    init {
        val cartDao = VetDatabase.getInstance(application).cartDao()

        repository = CartRepository(cartDao)
        getCartModels = repository.getAllCartModels
    }

    fun addToCart(cartModel: CartModel) {
        viewModelScope.launch(Dispatchers.IO) {
            repository.addToCart(cartModel)
        }
    }

    fun deleteAllFromCart() {
        viewModelScope.launch(Dispatchers.IO) {
            repository.deleteAllFromCart()
        }
    }

}

数据库类 #

@Database(entities = [Product::class, CartModel::class], version = 5, exportSchema = false)
@TypeConverters(Converters::class)
abstract class VetDatabase : RoomDatabase() {

    abstract fun productDao(): ProductDao
    abstract fun cartDao(): CartDao

    companion object {
        @Volatile
        private var INSTANCE: VetDatabase? = null

        fun getInstance(context: Context): VetDatabase {
            val tempInstance = INSTANCE
            if (tempInstance != null) {
                return tempInstance
            }

            synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    VetDatabase::class.java,
                    "vet_db"
                )
                    .fallbackToDestructiveMigration()
                    .allowMainThreadQueries()
                    .build()

                INSTANCE = instance
                return instance
            }
        }
    }

}

问题根源 #

SQLite的CursorWindow默认大小限制为约2MB，当直接将大Bitmap存储为BLOB时，单条数据的总大小会超过这个限制，导致查询时无法将数据加载到CursorWindow，触发SQLiteBlobTooBigException，进而导致LiveData计算异常引发崩溃。

解决方案 #

方案一：存储图片文件路径（推荐） #

不直接存储Bitmap，改为存储图片在本地的文件路径，这是处理大图片的最优方案。

1. 修改Product实体类
   将productImage字段替换为存储路径的String类型：

   @Parcelize
   @Entity(tableName = "vet_product")
   data class Product(
       @PrimaryKey(autoGenerate = true) val id: Int = 0,
       val productName: String,
       val productPrice: Double,
       val productImagePath: String // 替换原Bitmap字段
   ) : Parcelable
   

2. 添加图片本地存储方法
   选择图片后，将Bitmap保存到应用内部存储，返回文件路径：

   fun saveImageToInternalStorage(context: Context, bitmap: Bitmap, fileName: String): String {
       context.openFileOutput(fileName, Context.MODE_PRIVATE).use { fos ->
           // 可根据需求调整压缩格式和质量，比如JPEG压缩率设为80
           bitmap.compress(Bitmap.CompressFormat.PNG, 100, fos)
       }
       return context.filesDir.absolutePath + File.separator + fileName
   }
   

3. 添加图片加载方法
   从数据库读取路径后，加载对应的Bitmap：

   fun loadImageFromPath(path: String): Bitmap? {
       return BitmapFactory.decodeFile(path)
   }
   

4. 更新相关代码
   所有涉及productImage的Dao、Repository、ViewModel代码均需改为使用productImagePath，同时移除原有的Bitmap类型转换器（Converters）。

方案二：压缩Bitmap后存储（临时应急方案） #

如果必须存储Bitmap，可在插入数据库前对Bitmap进行压缩，降低其大小：

fun compressBitmap(bitmap: Bitmap, maxSize: Int): Bitmap {
    var compressedBitmap = bitmap
    var quality = 100
    while (getBitmapSize(compressedBitmap) > maxSize && quality > 10) {
        quality -= 10
        val outputStream = ByteArrayOutputStream()
        compressedBitmap.compress(Bitmap.CompressFormat.JPEG, quality, outputStream)
        val byteArray = outputStream.toByteArray()
        compressedBitmap = BitmapFactory.decodeByteArray(byteArray, 0, byteArray.size)
    }
    return compressedBitmap
}

// 获取Bitmap字节大小
fun getBitmapSize(bitmap: Bitmap): Int {
    return bitmap.byteCount
}

使用时，在调用addProduct前先压缩Bitmap：

val compressedBitmap = compressBitmap(originalBitmap, 1024 * 1024) // 压缩到1MB以内
viewModel.addProduct(Product(0, name, price, compressedBitmap))

注意：此方案仅适合小图片，大图片即使压缩后仍可能超过CursorWindow限制，且多次压缩会导致图片质量下降。

内容的提问来源于stack exchange，提问作者Code Watermelon