Create object with list of another object

I would like to build an entity from an object with the list of another object. When running the project I get this error: "Cannot figure out how to save this field into database. You can consider adding a type converter for it." Any ideas to resolve this?

My entities:

@Parcelize
@Entity
data class Maintenance(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    var description: String,
    var listParts: List<Parts>
): Parcelable

@Parcelize
@Entity
data class Parts (
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    var serialNumber: Long,
    var description: String,
    var model: String,
) : Parcelable

I tried changing the list type to mutable list, but it didn't work.

Solution

SQLite and therefore Room can only directly store specific types of values:-

null values (NULL),
integer type values (INTEGER),
decimal type values (REAL),
string type values (TEXT) and
bytestream type values (BLOB)

Other values need to be converted to one of the above. Obviously NULL is of no use as the information could not be used to reconstruct the underlying value (your List). INTEGER values would require some complex means of reconstructing the underlying value (List), likewise for decimals. As such you would either need to convert the value to a TEXTual or ByteStream representation of the List in order to store the List.

Hence the suggestion that a TypeConverter is used.

Typically a JSON representation is used, this being a String representation of the underlying object (the List of parts).

However, as Parts is a table in it's own right, then it would seem sensible to not replicate the data stored in that table in another table but to utilise the relationship capabilities of SQLite which is a relational database.

Typical a relationship is reflected by storing a single value that uniquely identifies the related row in another table (you have such values already defined that is the id field, as it MUST be a unique value being the primary key).

It is highly likely that a single Maintenance object could have the same related part as another Maintenance and thus that a single part could be related to many Maintenances. This being a many-many type relationship.

Typically a many-many relationship is afforded by the use of another table that stores the two values, the reference to each of the related rows (the Maintenance and the related part). Such a table has many terms such as an associative table, reference table, mapping table on so on.

Typically the primary key would be the combination of both values (Room requires the definition of a primary key).

As access may be via a part (e.g. get all the maintenances that use a specific part) or via a maintenance (e.g. get all parts used by a maintenance i.e. obviously what you want). As such it may well be more efficient to have an index according to the 2nd value (Room will issue a warning if no such index exists).

As such it is suggested that @Entity wise you amend the Maintenance class to not include the List field and instead introduce a new @Entity annotated class for the relationship table.

So you could have:-

@Entity
data class Maintenance(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    var description: String,
    /*var listParts: List<Parts> defunct id using a many-many relationship*/
)

@Entity
data class Parts (
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    var serialNumber: Long,
    var description: String,
    var model: String,
)
@Entity(
    primaryKeys = ["maintenanceId","partsId"]
)
data class MaintenancePartsRelationship(
    val maintenanceId: Int,
    @ColumnInfo(index = true)
    val partsId: Int
)

for brevity Parcel stuff omitted

To get a Maintenance with it's Parts then you could have a POJO such as:-

data class MaintenanceWithParts(
    @Embedded
    val maintenance: Maintenance,
    @Relation(
        entity = Parts::class, parentColumn = "id", entityColumn = "id",
        associateBy = Junction(MaintenancePartsRelationship::class,"maintenanceId","partsId")
    )
    val partsList: List<Parts>
)

If you ever wanted to get Parts with the Maintenances that use the parts then you could have a POJO such as:-

data class PartsWithMaintenances(
    @Embedded
    val parts: Parts,
    @Relation(
        entity = Maintenance::class, parentColumn = "id", entityColumn = "id",
        associateBy = Junction(MaintenancePartsRelationship::class, parentColumn = "partsId", entityColumn = "maintenanceId")
    )
    val maintenanceList: List<Maintenance>
)

with these POJO's the Junction of the associateBy defines the relationship/referencing/mapping/associative table and the columns that reference the parent/child.
- basically they are a reversal of each other

Working Demo

To demonstrate the above then the following additional code was introduced:-

@Dao
interface AllDAOs {
    @Insert( onConflict = OnConflictStrategy.IGNORE)
    fun insert(maintenance: Maintenance): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(parts: Parts): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(maintenancePartsRelationship: MaintenancePartsRelationship): Long
    @Query("SELECT * FROM maintenance")
    fun getALlMaintenancesWithTheirParts(): List<MaintenanceWithParts>
    @Query("SELECT * FROM parts")
    fun getAllPartsWithTheirMaintenances(): List<PartsWithMaintenances>
}

@Database(entities = [Maintenance::class,Parts::class,MaintenancePartsRelationship::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs
    companion object {
        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
        }
    }
}

And finally some activity code:-

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

        val p1Id = dao.insert(Parts(id= 0, serialNumber = 100000000001, description = "Parts 1", model = "P1Model"))
        val p2Id = dao.insert(Parts(id= 0, serialNumber = 100000000002, description = "Parts 2", model = "P2Model"))
        val p3Id = dao.insert(Parts(id= 0, serialNumber = 100000000003, description = "Parts 3", model = "P3Model"))

        val m1Id = dao.insert(Maintenance(id = 0, description = "M1"))
        val m2Id = dao.insert(Maintenance(id = 0, description = "M2"))
        val m3Id = dao.insert(Maintenance(id = 0, description = "M3"))
        val p4Id = dao.insert(Parts(id= 0, serialNumber = 100000000004, description = "Parts 4", model = "P4Model"))
        val m4Id = dao.insert(Maintenance(id = 0, description = "M4"))

        dao.insert(MaintenancePartsRelationship(maintenanceId = m1Id.toInt(), partsId = p1Id.toInt()))
        dao.insert(MaintenancePartsRelationship(maintenanceId = m1Id.toInt(), partsId = p3Id.toInt()))


        dao.insert(MaintenancePartsRelationship(maintenanceId = m2Id.toInt(), partsId = p2Id.toInt()))
        dao.insert(MaintenancePartsRelationship(maintenanceId = m2Id.toInt(), partsId = p4Id.toInt()))

        dao.insert(MaintenancePartsRelationship(maintenanceId = m3Id.toInt(), partsId = p4Id.toInt()))
        dao.insert(MaintenancePartsRelationship(maintenanceId = m3Id.toInt(), partsId = p1Id.toInt()))
        dao.insert(MaintenancePartsRelationship(maintenanceId = m3Id.toInt(), partsId = p3Id.toInt()))
        dao.insert(MaintenancePartsRelationship(maintenanceId = m3Id.toInt(), partsId = p2Id.toInt()))

        dao.insert(MaintenancePartsRelationship(maintenanceId = m3Id.toInt(), partsId = p2Id.toInt()))

        for (mwp in dao.getALlMaintenancesWithTheirParts()) {
            val sb = StringBuilder()
            for (p in mwp.partsList) {
                sb.append("\n\tParts ID is ${p.id} Desc is ${p.description} Model is ${p.model} Serial is ${p.serialNumber}")
            }
            Log.d("DBINFO","Maintenance ID is ${mwp.maintenance.id} Desc is ${mwp.maintenance.description}. It has ${mwp.partsList.size} Parts. They are:-$sb")
        }

        for (pwm in dao.getAllPartsWithTheirMaintenances()) {
            val sb = StringBuilder()
            for (m in pwm.maintenanceList) {
                sb.append("\n\tMaintenance ID is ${m.id} Desc is ${m.description}")
            }
            Log.d("DBINFO","Parts ID is ${pwm.parts.id} Desc is ${pwm.parts.description} Model is ${pwm.parts.model} Serial is ${pwm.parts.serialNumber}. It has ${pwm.maintenanceList.size} Maintenances. They are:-$sb")
        }
    }
}

note run on the main thread for brevity
the above first gets an instance of the Database and then the DAO from the database.
Then a number of Parts and Maintenances are added (the order does not matter as at this stage they are independent of each other).
Then MaintenanceWithParts rows are added forming the various relationships
- M1 has parts 1 and 3
- M2 has parts 2 and 4
- M3 has all 4 parts
- M4 has no parts
- the converse is that all parts are used by 2 maintenances
finally all the Maintenances with their parts are extracted and written to the Log. Likewise all the Parts with their maintenances are written to the Log.

Result

When run the log includes:-

2023-12-30 06:34:58.747 D/DBINFO: Maintenance ID is 1 Desc is M1. It has 2 Parts. They are:-
        Parts ID is 1 Desc is Parts 1 Model is P1Model Serial is 100000000001
        Parts ID is 3 Desc is Parts 3 Model is P3Model Serial is 100000000003
2023-12-30 06:34:58.747 D/DBINFO: Maintenance ID is 2 Desc is M2. It has 2 Parts. They are:-
        Parts ID is 2 Desc is Parts 2 Model is P2Model Serial is 100000000002
        Parts ID is 4 Desc is Parts 4 Model is P4Model Serial is 100000000004
2023-12-30 06:34:58.747 D/DBINFO: Maintenance ID is 3 Desc is M3. It has 4 Parts. They are:-
        Parts ID is 1 Desc is Parts 1 Model is P1Model Serial is 100000000001
        Parts ID is 2 Desc is Parts 2 Model is P2Model Serial is 100000000002
        Parts ID is 3 Desc is Parts 3 Model is P3Model Serial is 100000000003
        Parts ID is 4 Desc is Parts 4 Model is P4Model Serial is 100000000004
2023-12-30 06:34:58.747 D/DBINFO: Maintenance ID is 4 Desc is M4. It has 0 Parts. They are:-


2023-12-30 06:34:58.750 D/DBINFO: Parts ID is 1 Desc is Parts 1 Model is P1Model Serial is 100000000001. It has 2 Maintenances. They are:-
        Maintenance ID is 1 Desc is M1
        Maintenance ID is 3 Desc is M3
2023-12-30 06:34:58.750 D/DBINFO: Parts ID is 2 Desc is Parts 2 Model is P2Model Serial is 100000000002. It has 2 Maintenances. They are:-
        Maintenance ID is 2 Desc is M2
        Maintenance ID is 3 Desc is M3
2023-12-30 06:34:58.750 D/DBINFO: Parts ID is 3 Desc is Parts 3 Model is P3Model Serial is 100000000003. It has 2 Maintenances. They are:-
        Maintenance ID is 1 Desc is M1
        Maintenance ID is 3 Desc is M3
2023-12-30 06:34:58.750 D/DBINFO: Parts ID is 4 Desc is Parts 4 Model is P4Model Serial is 100000000004. It has 2 Maintenances. They are:-
        Maintenance ID is 2 Desc is M2
        Maintenance ID is 3 Desc is M3

Additional (Referential Integrity)

Although the above works, there is no enforcement of what is termed as Referential Integrirty. You could for example include the following:-

 dao.insert(MaintenancePartsRelationship(9999,6666))

Which would result in a row in the reference table that refers to a non-existent Maintenance and also a non-existent Parts, which could result in issues but at the minimum is a waste. The references do not have the integrity that they relate to anything.

Often you would want to not allow this. SQLite caters for not allowing this waste by the way of Foreign Key constraints. Room caters for this by allowing Foreign Keys to be specified.

You may wish to consider amending the above (The MaintenancePartsRelationship table) to not allow such discrepancies by including ForeignKey definitions.

Thus you could consider using:-

@Entity(
    primaryKeys = ["maintenanceId","partsId"],
    foreignKeys = [
        ForeignKey(
            entity = Maintenance::class,
            parentColumns = ["id"],
            childColumns = ["maintenanceId"],
            /* Optional but helps maintain referential Integrity */
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Parts::class,
            parentColumns = ["id"],
            childColumns = ["partsId"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)

This then says that:

the maintenanceId value MUST be an existing id value in the Maintenance table, and
the partsId value, MUST be an existing id value in the Parts table.

The onDelete and onUpdate, when using CASCADE automatically maintains referential integrity in that:-

for onDelete if the parent is deleted then the children that reference that parent are automatically deleted.
for onUpdate if the referenced value (the id) is changed then that change is made to the children.
1. this is typically a rarer situation.

Hence, the optional aspect of onDelete and onUpdate.

an issue is that if referential integrity is compromised then an exception will occur and that would be introduce coding complexities to handle. e.g. if the insert above were attempted then the exception would be something like

2023-12-30 07:21:15.449 2921-2921/a.a.so77733410kotlinroomstoringlist E/AndroidRuntime: FATAL EXCEPTION: main
Process: a.a.so77733410kotlinroomstoringlist, PID: 2921
java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so77733410kotlinroomstoringlist/a.a.so77733410kotlinroomstoringlist.MainActivity}: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)

However, handling the above may be preferential to encountering a perhaps more obscure exception due to references being made to non-existent parents.

Using Type Converters

Consider the following that demonstrates using Type Converters:-

@Entity
data class OriginalMaintenance(
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    var description: String,
    var listParts: PartsList
)

data class PartsList(
    var partsList: List<Parts>
)

class TheTypeConverters {
    @TypeConverter
    fun convertFromPartsListToJSOnString(lp: PartsList): String = Gson().toJson(lp)
    @TypeConverter
    fun convertFromJSONStringToPartsList(js: String): PartsList = Gson().fromJson(js,PartsList::class.java)
}

OriginalMaintenance to reflect the original Maintenance (but also keep the suggested)
PartsList added to suit the com.google.code.gson library (added to the project).
TheTypeConverters to convert the list of parts to a JSON string when storing data and to convert the stored String to the list of parts when retrieving data.

To inform Room where the TypeConverters can be found then the following is added (in this case at the @Database level (fullest scope)):-

@TypeConverters(TheTypeConverters::class)

The entities parameter of the @Database annotation is changed to include the new OriginalMaintenance class:-

@Database(entities = [Maintenance::class,Parts::class,MaintenancePartsRelationship::class,OriginalMaintenance::class], version = 1, exportSchema = false)

The AllDAOs interface has the following added:-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(originalMaintenance: OriginalMaintenance): Long
@Query("SELECT * FROM originalmaintenance")
fun getAllOriginalMaintenance(): List<OriginalMaintenance>

Finally the activity code has the following added:-

    val pl1 = PartsList(
        listOf(
            Parts(0,100000000001, description = "Parts 1", model = "P1Model"),
            Parts(0, serialNumber = 100000000003, description = "Parts 3", model = "P3Model")
        )
    )
    val pl2 = PartsList(
        listOf(
            Parts(id=0, serialNumber = 100000000002, description = "Parts 2", model = "P2Model"),
            Parts(id=0, serialNumber = 100000000004, description = "Parts 4", model = "P4Model")
        )
    )
    val pl3 = PartsList(
        listOf(
            Parts(0,100000000001, description = "Parts 1", model = "P1Model"),
            Parts(id=0, serialNumber = 100000000002, description = "Parts 2", model = "P2Model"),
            Parts(0, serialNumber = 100000000003, description = "Parts 3", model = "P3Model"),
            Parts(id=0, serialNumber = 100000000004, description = "Parts 4", model = "P4Model")
        )
    )

    dao.insert(OriginalMaintenance(0,"OM1",pl1))
    dao.insert(OriginalMaintenance(0,"OM2",pl2))
    dao.insert( OriginalMaintenance(0,"OM3",pl3))
    dao.insert(OriginalMaintenance(0,"OM4", PartsList(listOf())))

    for (om in dao.getAllOriginalMaintenance()) {
        val sb = StringBuilder()
        for (p in om.listParts.partsList) {
            sb.append("\n\tParts ID is ${p.id} Desc is ${p.description} Model is ${p.model} Serial is ${p.serialNumber}")
        }
        Log.d("DBINFO","Maintenance ID is ${om.id} Desc is ${om.description}. It has ${om.listParts.partsList.size} Parts. They are:-$sb")
    }

This code will add the equivalent of the initial demo and then extract the data and output it to the log, as per

2023-12-30 08:22:11.060 D/DBINFO: Maintenance ID is 1 Desc is OM1. It has 2 Parts. They are:-
        Parts ID is 0 Desc is Parts 1 Model is P1Model Serial is 100000000001
        Parts ID is 0 Desc is Parts 3 Model is P3Model Serial is 100000000003
2023-12-30 08:22:11.060 D/DBINFO: Maintenance ID is 2 Desc is OM2. It has 2 Parts. They are:-
        Parts ID is 0 Desc is Parts 2 Model is P2Model Serial is 100000000002
        Parts ID is 0 Desc is Parts 4 Model is P4Model Serial is 100000000004
2023-12-30 08:22:11.061 D/DBINFO: Maintenance ID is 3 Desc is OM3. It has 4 Parts. They are:-
        Parts ID is 0 Desc is Parts 1 Model is P1Model Serial is 100000000001
        Parts ID is 0 Desc is Parts 2 Model is P2Model Serial is 100000000002
        Parts ID is 0 Desc is Parts 3 Model is P3Model Serial is 100000000003
        Parts ID is 0 Desc is Parts 4 Model is P4Model Serial is 100000000004
2023-12-30 08:22:11.061 D/DBINFO: Maintenance ID is 4 Desc is OM4. It has 0 Parts. They are:-

All looks fine. However the underlying data tells a different story.

First, when adding the OM's the PartsList may or may not consist of Parts that exist in the Parts table. Each such list is stored as whatever it is, irrespective of it existing in the Parts table.

Using App Inspection to look at the database (that contains data for both techniques) then.

Comparing the Maintenance table versus the OriginalMaintenance table .

and then

as is easily seen the latter contains far more data in the partslList column and that the data is bloated with indentifiers and separators and that a part, if used more than once is repeated.

The Parts table effectively stores exactly the same data but is, in comparison:-

of course if using the original and if parts were added, this data would exist even though it is then of little use and could very well lead to confusion.

Of course using the relational aspect and the many-many table has to be considered this table:-

Quite clearly using relations is more space efficient. That is even without considering that numbers take up much less storage. If this factor is considered then the reference table being comprised of integer values will consume far less storage than the equivalent JSON strings. (an Integer will be stored in a maximum of 8 bytes, a string value will consume at the minimum 1 byte per character).
Eventually there is also a likelihood that other issues will be encountered. e.g.
- how would you do the equivalent of ascertaining the Maintenances that use a part (easy as is shown with relationships)
- how would you change a part's value? (just update the part in the parts table using relationships)