First you cannot have columns that are Lists/Arrays. However, these columns are not actually required. Rather you appear to have a many-many relationship between the CharacterModel and the EpisodeModel.
That is a CharacterModel can have 0-many EpisodeModels and likewise an EpisodeModel can have 0-many CharacterModels.
For such a relationship you typically have a table know by names such as:-
- associative table
- reference table
- intermediate table
- junction table
- mapping table
- ....
Such a table has two primary columns, one for each table that is mapped. Typically these two columns would form the primary key (a composite primary key). As such the mapped tables don't need to store references to the other table. This third table does that.
As such You could have (note some columns/annotations removed for brevity of the demo, obviously you would include these):-
@Entity(tableName = "character_table")
data class CharacterModel(
val created: String?,
//val episode: List<String>?, NOT NEEDED <<<<<
val gender: String?,
val id: Int?,
val image: String?,
/* removed for brevity
@Embedded
val location: Location?,
*/
val name: String?,
/* removed for brevity
@Embedded
val origin: WebStorage.Origin?,
*/
val species: String?,
val status: String?,
val type: String?,
@PrimaryKey
val characterUrl: String
)
and
@Entity(tableName = "episode_table")
data class EpisodeModel(
//@SerializedName("air_date") removed for brevity
val airDate: String?,
//val characters: List<String>?, // NOT NEEDED <<<<<
val created: String?,
val episode: String?,
val id: Int?,
val name: String?,
@PrimaryKey
val episodeUrl: String
)
The new mapping table could be :-
@Entity(
primaryKeys = ["characterUrlMap","episodeUrlMap"],
/* Optional but suggested to enforce referential integrity */
foreignKeys = [
/* Rule that says the value of the characterUrlMap column MUST be a value
that exists in the characterUrl column of one of the rows in the character_table table
*/
ForeignKey(
entity = CharacterModel::class,
parentColumns = ["characterUrl"],
childColumns = ["characterUrlMap"],
/* Option within foreign key but helpful */
onDelete = CASCADE, // if parent is deleted then all children (rows in this table) are deleted rather than orphaned
onUpdate = CASCADE // if characterUrl column is changed then all the children in this table with the parents value are changed
),
ForeignKey(
entity = EpisodeModel::class,
parentColumns = ["episodeUrl"],
childColumns = ["episodeUrlMap"],
onDelete = CASCADE,
onUpdate = CASCADE
)
]
)
data class CharacterEpisodeMap(
val characterUrlMap: String,
@ColumnInfo(index = true)
val episodeUrlMap: String
)
You then need to be able to get the related items either a CharacterModel with all the EpisodeModels that belong to it it and/or an EpisodeModel with all the CharacterModels that belong to it.
To do so you need a POJO (or 2) that can be used to retrieve the data. So you could have:-
data class CharacterModelWithEpisodeModelList(
@Embedded
val characterModel: CharacterModel,
@Relation(
entity = EpisodeModel::class,
parentColumn = "characterUrl",
entityColumn = "episodeUrl",
associateBy = Junction(
value = CharacterEpisodeMap::class,
parentColumn = "characterUrlMap",
entityColumn = "episodeUrlMap"
)
)
val episodeModelList: List<EpisodeModel>
)
and/or :-
data class EpisodeModelWithCharacterModelList(
@Embedded
val episodeModel: EpisodeModel,
@Relation(
entity = CharacterModel::class,
parentColumn = "episodeUrl",
entityColumn = "characterUrl",
associateBy = Junction(
value = CharacterEpisodeMap::class,
parentColumn = "episodeUrlMap",
entityColumn = "characterUrlMap"
)
)
val characterModelList: List<CharacterModel>
)
Demonstration
Using the code above and :-
Dao's such as :-
@Dao
abstract class AllDao {
@Insert(onConflict = IGNORE)
abstract fun insert(characterModel: CharacterModel): Long
@Insert(onConflict = IGNORE)
abstract fun insert(episodeModel: EpisodeModel): Long
@Insert(onConflict = IGNORE)
abstract fun insert(characterEpisodeMap: CharacterEpisodeMap): Long
/* Retrieving the related data */
@Transaction
@Query("SELECT * FROM character_table")
abstract fun getAllCharacterModelsWithEpisodeModelList(): List<CharacterModelWithEpisodeModelList>
@Transaction
@Query("SELECT * FROM episode_table")
abstract fun getAllEpisodeModelsWithCharacterModelList(): List<EpisodeModelWithCharacterModelList>
}
You need an @Database annotated class (with the 3 entities) such as :-
@Database(entities = [CharacterModel::class,EpisodeModel::class,CharacterEpisodeMap::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDao(): AllDao
companion object {
@Volatile
private var instance: TheDatabase? = null
fun getInstance(context: Context): TheDatabase {
if (instance == null) {
instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
.allowMainThreadQueries()
.build()
}
return instance as TheDatabase
}
}
}
Finally to put it altogether an activity MainActivity :-
const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
doSomething()
}
fun doSomething() {
val c1url = "c1url"
dao.insert(CharacterModel("now","x",1,"c1image","Fred","test","ongoing","a type",c1url))
val c2url = "c2url"
dao.insert(CharacterModel("yesterday","x",2,"c1image","Mary","test","finished","a type",c2url))
val c3url = "c3url"
dao.insert(CharacterModel("tomorrow","x",3,"c1image","Jane","test","starting","a type",c3url))
val e1url = "e1url"
dao.insert(EpisodeModel("then","now","1",1,"The First for C1",e1url))
val e2url = "e2url"
dao.insert(EpisodeModel("tomorrow","now","1",2,"The Second for C1",e2url))
val e3url = "e3url"
dao.insert(EpisodeModel("then","now","1",3,"The Third for C1",e3url))
val e4url = "e4url"
dao.insert(EpisodeModel("then","now","1",1,"The First for C2",e4url))
dao.insert(CharacterEpisodeMap(c1url,e1url))
dao.insert(CharacterEpisodeMap(c2url,e1url)) // to show multiple characters in episode
dao.insert(CharacterEpisodeMap(c1url,e2url))
dao.insert(CharacterEpisodeMap(c1url,e3url))
dao.insert(CharacterEpisodeMap(c2url,e4url))
// and so on
for (cwe: CharacterModelWithEpisodeModelList in dao.getAllCharacterModelsWithEpisodeModelList()) {
Log.d(TAG,"Character is ${cwe.characterModel.name} created is ${cwe.characterModel.created} etc.")
for(e: EpisodeModel in cwe.episodeModelList) {
Log.d(TAG,"\tEpisode is ${e.name} etc.")
}
}
for(ewc: EpisodeModelWithCharacterModelList in dao.getAllEpisodeModelsWithCharacterModelList()) {
Log.d(TAG,"Episode is ${ewc.episodeModel.name} etc.")
for(c: CharacterModel in ewc.characterModelList) {
Log.d(TAG,"\tCharacter is ${c.name} etc.")
}
}
}
}
Result
When run the log includes :-
2022-01-12 08:55:46.938 D/DBINFO: Character is Fred created is now etc.
2022-01-12 08:55:46.938 D/DBINFO: Episode is The First for C1 etc.
2022-01-12 08:55:46.938 D/DBINFO: Episode is The Second for C1 etc.
2022-01-12 08:55:46.938 D/DBINFO: Episode is The Third for C1 etc.
2022-01-12 08:55:46.938 D/DBINFO: Character is Mary created is yesterday etc.
2022-01-12 08:55:46.938 D/DBINFO: Episode is The First for C1 etc.
2022-01-12 08:55:46.938 D/DBINFO: Episode is The First for C2 etc.
2022-01-12 08:55:46.938 D/DBINFO: Character is Jane created is tomorrow etc.
2022-01-12 08:55:46.946 D/DBINFO: Episode is The First for C1 etc.
2022-01-12 08:55:46.947 D/DBINFO: Character is Fred etc.
2022-01-12 08:55:46.947 D/DBINFO: Character is Mary etc.
2022-01-12 08:55:46.947 D/DBINFO: Episode is The Second for C1 etc.
2022-01-12 08:55:46.947 D/DBINFO: Character is Fred etc.
2022-01-12 08:55:46.947 D/DBINFO: Episode is The Third for C1 etc.
2022-01-12 08:55:46.947 D/DBINFO: Character is Fred etc.
2022-01-12 08:55:46.947 D/DBINFO: Episode is The First for C2 etc.
2022-01-12 08:55:46.947 D/DBINFO: Character is Mary etc.
