Storing image data in Room database with or without using (typeAffinity = ColumnInfo.BLOB)

user_4323977 0
Viewed 561

I know it's not the best practice to store an image in DB directly and we should store a path instead. But in my case this is a must.

I am able to store list of images perfectly fine defined as:

@ColumnInfo(name = "picture")
var picture: ByteArray? = null

I came across solutions that suggests using (typeAffinity = ColumnInfo.BLOB). So I changed my column to:

@ColumnInfo(name = "picture", typeAffinity = ColumnInfo.BLOB)
var picture: ByteArray? = null

I haven't noticed anything significant in performance. I wonder what are the possible advantages of using typeAffinity or disadvantages of not using it?

It maybe worth mentioning my images are always under 1 megabytes.

android-room android-sqlite sqlite android
1 Answers

There is no real advantage/disadvantage, certainly not at run time perhaps marginally at compile time.

That is all that using typeAffinity=? does is override the typeAffinity being determined by the type of the field/column of the variable.

As you have var picture: ByteArray this would be resolved to a column type of BLOB anyway.

If you wished you could compile with both and see the resultant SQL used by looking at the generated java.

Perhaps consider the following Entity that uses both:-

@Entity(tableName = "user")
data class UserEntity(

    @PrimaryKey
    val userid: Long = 0,
    var picture1: ByteArray? = null,
    @ColumnInfo(typeAffinity = ColumnInfo.BLOB)
    var pitcure2: ByteArray? = null
)

In the generated Java (use Android view as highlihted) then in the @Database class (UserDatabase in the example) suffixed by _Impl (so UserDatabase_Impl in the example) the following is a screen shot of the generated Java :-

enter image description here

  • Android highlighted indicates where to select the Android view.
  • The highlight in the code explorer shows the respective code (UserDatabase_Impl) in the expanded java (generated) directory
  • The createAllTables method is the method used to create the table(s)
    • room_master_table is a room specific table used for verification of an existing table with the schema to detect if there are differences.

The code (SQL) generated for the creation of the table is :-

_db.execSQL("CREATE TABLE IF NOT EXISTS `user` (`userid` INTEGER NOT NULL, `picture1` BLOB, `pitcure2` BLOB, PRIMARY KEY(`userid`))");
  • i.e. the definition of the columns picture1 and picture2 are identical bar the column names.
  • NOTE please heed the WARNING in regards to not changing the generated code.
user_47445140
Related