Save complex JSON response in SQLite with Room-CodePudding

I'm trying to implement caching of a JSON API response with Room.
The response I get in JSON follows this data class structure:

@Serializable
data class ApiDataResponse(
    val success: Boolean,
    val message: String? = null,
    val albums: List<AlbumResponse> = emptyList()
)

@Serializable
data class AlbumResponse(
    val id: String,
    val title: String,
    val createdBy: String,
    val enabled: Boolean,
    val keywords: List<String>,
    val pics: List<PicResponse>
)

@Serializable
data class PicResponse(
    val picUrl: String,
    val emojis: List<String>
)

Notes:

@Serializable is from kotlinx.serialization library to parse the JSON response.
These response data classes are only used inside my datasource layer, the view layer doesn't care about an ApiDataResponse and only knows a "pure" version of AlbumResponse called Album and a "pure" version of PicResponse called Pic (by "pure" I mean a data class without external library annotations).

So to implement this cache with Room I could discard the ApiDataResponse and save only the contents of AlbumResponse (and consequently PicResponse), having new data classes for Room entities following this idea:

@Entity(tableName = "albums")
data class AlbumEntity(
    @PrimaryKey(autoGenerate = false)
    val id: String,
    val title: String,
    val createdBy: String,
    val enabled: Boolean,
    val keywords: List<String>, // obstacle here
    val pics: List<PicEntity> // obstacle here
)

// obstacle here
// @Entity
data class PicEntity(
    val picUrl: String,
    val emojis: List<String>
)

I already know how to save simple data in Room, with the simplest JSON I was able to do this task, the problem is that in this more complex scenario I have no idea how to achieve this goal. So I wish someone could guide me in this situation.

CodePudding user response：

I believe the issue is with columns as lists.

What you could do is add the following classes so the Lists are embedded within a class:-

data class StringList(
    val stringList: List<String>
)
data class PicEntityList(
    val picEntityList: List<PicEntity>
)

and then change AlbumEntity to use the above instead of the Lists, as per:-

@Entity(tableName = "albums")
data class AlbumEntity(
    @PrimaryKey(autoGenerate = false)
    val id: String,
    val title: String,
    val createdBy: String,
    val enabled: Boolean,
    //val keywords: List<String>, // obstacle here
    val keywords: StringList, /// now not an obstacle
    //val pics: List<PicEntity> // obstacle here
    val emojis: PicEntityList// now not an obstacle
)

To be able to store the "complex" (single object) you need to convert this so some TypeConverters e.g.

class RoomTypeConverters{
    @TypeConverter
    fun convertStringListToJSON(stringList: StringList): String = Gson().toJson(stringList)
    @TypeConverter
    fun convertJSONToStringList(json: String): StringList = Gson().fromJson(json,StringList::class.java)
    @TypeConverter
    fun convertPicEntityListToJSON(picEntityList: PicEntityList): String = Gson().toJson(picEntityList)
    @TypeConverter
    fun convertJSONToPicEntityList(json: String): PicEntityList = Gson().fromJson(json,PicEntityList::class.java)
}

note this utilises the dependency com.google.code.gson

You then need to have the @TypeConverters annotation to cover the appropriate scope (at the @Database level is the most scope). Note the plural rather than singular, they are different.

To demonstrate the above works, First some functions in an interface annotated with @Dao :-

@Dao
interface AlbumDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(albumEntity: AlbumEntity): Long
    @Query("SELECT * FROM albums")
    fun getAllAlbums(): List<AlbumEntity>
}

Second an @Database annotated class (note the @TypeConverters annotation) :-

@TypeConverters(RoomTypeConverters::class)
@Database(entities = [AlbumEntity::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAlbumDao(): AlbumDao

    companion object {
        @Volatile
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"album.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Third some activity code to actually do something (insert some Albums and then extract them writing the extracted data to the Log) :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AlbumDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAlbumDao()

        dao.insert(AlbumEntity(
            "Album001", "The First Album","Fred",false,
            StringList(listOf("The","First","Album")),
            PicEntityList(
                listOf(
                    PicEntity("PE001", listOf("emoji1","emoji2","emoji3")),
                    PicEntity("PE002",listOf("emoji10")),
                    PicEntity("PE003", listOf("emoji20","emoji21"))
                ))
        ))
        dao.insert(AlbumEntity(
            "Album002","This is the Second Album","Mary", true,
            StringList(listOf("keya","keyb","keyc","keyd","keye")),
            PicEntityList(
                listOf(
                    PicEntity("PE011", listOf("emoji30","emoji31")),
                    PicEntity("PE012", listOf("emoji1","emoji10","emoji20","emoji30"))
            ))
        ))
        for (a in dao.getAllAlbums()) {
            logAlbum(a)
        }
    }

    fun logAlbum(albumEntity: AlbumEntity) {
        val keywords = StringBuilder()
        for(s in albumEntity.keywords.stringList) {
            keywords.append("\n\t$s")
        }
        val pelog = StringBuilder()
        for (pe in albumEntity.emojis.picEntityList) {
            pelog.append("\n\tURL is ${pe.picUrl}")
            for (emoji in pe.emojis) {
                pelog.append("\n\t\tEmoji is ${emoji}")
            }
        }
        Log.d(
            "ALBUMINFO",
            "Album id is ${albumEntity.id} "  
                    "Title is ${albumEntity.title} "  
                    "CreateBy ${albumEntity.createdBy} "  
                    "Enabled=${albumEntity.enabled}. "  
                    "It has ${albumEntity.keywords.stringList.size} keywords. "  
                    "They are $keywords\n. "  
                    "It has ${albumEntity.emojis.picEntityList.size} emojis. "  
                    "They are ${pelog}"
        )
    }
}

Run on the main thread for convenience and brevity

When run then the log contains:-

D/ALBUMINFO: Album id is Album001 Title is The First Album CreateBy Fred Enabled=false. It has 3 keywords. They are 
        The
        First
        Album
    . It has 3 emojis. They are 
        URL is PE001
            Emoji is emoji1
            Emoji is emoji2
            Emoji is emoji3
        URL is PE002
            Emoji is emoji10
        URL is PE003
            Emoji is emoji20
            Emoji is emoji21
            
            
D/ALBUMINFO: Album id is Album002 Title is This is the Second Album CreateBy Mary Enabled=true. It has 5 keywords. They are 
        keya
        keyb
        keyc
        keyd
        keye
    . It has 2 emojis. They are 
        URL is PE011
            Emoji is emoji30
            Emoji is emoji31
        URL is PE012
            Emoji is emoji1
            Emoji is emoji10
            Emoji is emoji20
            Emoji is emoji30

i.e. the 2 albums have been extracted along with the appropriate embedded lists.

The Albums table itself (via App Inspection) consists of :-

An Alternative, and from a Database perspective, better approach, instead of embedding lists as a single value (String), would have the lists as related tables (with a one-many or a many-many relationship).