Relational room database: The class must be either entity or database view

Question

I am trying to understand how to use Room with relational tables. I have created a Job model, that has a list of locations and therefore needs a 1-to-many relation between the Job and Location object. For that, I have created a JobWrapper data class to hold both the Job and the locations. But, when building I get the following error:

The class must be either @Entity or @DatabaseView. - java.util.Collectionerror: Entities and POJOs must have a usable public constructor. You can have an empty constructor or a constructor whose parameters match the fields (by name and type). - java.util.Collection \models\JobWrapper.java:12: error: Cannot find the child entity column parentId in java.util.Collection. Options: private java.util.Collection<models.Location> locations; public final class JobWrapper { ^ Tried the following constructors but they failed to match:
JobWrapper(models.Job,java.util.Collection<models.Location>) -> [param:job -> matched field:job, param:locations -> matched field:unmatched] models\JobWrapper.java:9: error: Cannot find setter for field.

I notice that it at least cannot find the locations table. But, I do not know how to handle the problem. The problem did not appear while reading from the database - it first appeared when I was trying to put data into the database with my JobDAO. I have already spent a day trying to solve it and are therefore searching for a solution or some advise on how to solve it.

Note: I have been following the following guides:

1. https://developer.android.com/training/data-storage/room/relationships#one-to-many
2. https://dev.to/normanaspx/android-room-how-works-one-to-many-relationship-example-5ad0

Here follows some relevant code snippets from my projects:

JobWrapper.kt

data class JobWrapper(
    @Embedded val job: Job,

    @Relation(
        parentColumn = "jobid",
        entityColumn = "parentId"
    ) var locations : Collection<Location>
)

Job

@Entity
data class Job (
    @PrimaryKey
    @NonNull
    var jobid : String,

    @NonNull
    @ColumnInfo(name = "job_status")
    var status : JobStatus,

    @NonNull
    @SerializedName("createdByAuth0Id")
    var creator : String,

    @SerializedName("note")
    var note : String?,

    @NonNull
    var organisationId : String,

    @NonNull
    var type : JobType,

    @SerializedName("atCustomerId")
    @NonNull
    @ColumnInfo(name = "working_at_customer_id")
    var workingAtCustomerId : String,

    @SerializedName("toCustomerId")
    @NonNull
    @ColumnInfo(name = "working_to_customer_id")
    var workingToCustomerId : String,
)

JobStatus.kt

enum class JobStatus {
    CREATED,
    READY,
    IN_PROGRESS,
    FINISHED
}

Location.kt

@Entity
data class Location (
    @PrimaryKey(autoGenerate = true)
    var entityId: Long,

    @NonNull
    var parentId: String,

    @NonNull
    var locationId: String,

    @NonNull
    var type: String
) {
    constructor() : this(0, "", "", "")
}

JobDao.kt

@Dao
interface JobDAO {
    @Transaction
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(job: JobWrapper)

    @Transaction
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertAll(jobs: List<JobWrapper>)

    @Transaction
    @Update
    fun update(job: JobWrapper)

    @Transaction
    @Delete
    fun delete(job: JobWrapper)

    @Transaction
    @Query("DELETE FROM Job")
    fun deleteAll()

    @Transaction
    @Query("SELECT * FROM Job")
    fun getAll(): LiveData<List<JobWrapper>>
}

Answer 1

As Kraigolas has pointed out you can only use JobWrapper directly to extract data you need to insert/delete/update via the actual underlying Entities.

Consider the following

• (unlike Kraigolas's solultion the extended functions are in the JobDao rather than in a repository(swings and roundabouts argument as to which is better))

• note to test I've made some changes for brevity and convenience so the you would have to amend to suit.

JobDao

@Dao
interface JobDAO {

    /* Core/Underlying DAO's */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(job: Job): Long
    @Insert()
    fun insert(location: List<Location>): List<Long>
    @Transaction
    @Delete
    fun delete(location: List<Location>)
    @Delete
    fun delete(job: Job)
    @Update
    fun update(job: Job)
    @Update
    fun update(location: List<Location>)

    @Transaction
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(job: JobWrapper): Long {
        val rv =insert(job.job)
        insert(job.locations)
        return rv
    }

    @Transaction
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertAll(jobs: List<JobWrapper>) {
        for (jw: JobWrapper in jobs) {
            insert(jw)
        }
    }

    @Transaction
    @Update
    fun update(job: JobWrapper) {
        update(job.locations)
        update(job.job)
    }

    /* Delete according to JobWrapper allowing optional deletion of locations */
    @Transaction
    @Delete
    fun delete(job: JobWrapper, deleteChildLocations: Boolean) {
        if (deleteChildLocations) {
            delete(job.locations)
        }
        delete(job.job)
    }

    /* will orphan locations as is */
    /* Note using Foreign Keys in Location (to Job) with ON DELETE CASCADE */
    @Transaction
    @Query("DELETE FROM Job")
    fun deleteAll()

    @Transaction
    @Query("SELECT * FROM Job")
    fun getAll(): List<JobWrapper>

    @Transaction
    @Query("SELECT * FROM job WHERE jobid = :jobid")
    fun getJobWrapperByJobId(jobid: String ): JobWrapper
}

• As can be seen the Core Dao's include Job and Location actions
• The JobWrapper actions invoke the Core actions
• List have been used instead of Collections for my convenience (aka I only dabble with Kotlin)
• Instead of JobType, type has been changed to use String for convenience

As I've tested this the demo/example used follows (obviously JobDao is as above)

The POJO's and Entities used are/were :-

JobWrapper

 data class JobWrapper(
    @Embedded val job: Job,

    @Relation(
        parentColumn = "jobid",
        entityColumn = "parentId",
        entity = Location::class
    ) var locations : List<Location>
)

• List instead of Collection

Job

@Entity
data class Job (
    @PrimaryKey
    @NonNull
    var jobid : String,

    @NonNull
    @ColumnInfo(name = "job_status")
    var status : String,

    @NonNull
    var creator : String,

    var note : String?,

    @NonNull
    var organisationId : String,

    @NonNull
    var type : String,

    @NonNull
    @ColumnInfo(name = "working_at_customer_id")
    var workingAtCustomerId : String,

    @NonNull
    @ColumnInfo(name = "working_to_customer_id")
    var workingToCustomerId : String,
)

• largely the same but String rather than objects for convenience

Location

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Job::class,
            parentColumns = ["jobid"],
            childColumns = ["parentId"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE
        )
    ])
data class Location (
    @PrimaryKey(autoGenerate = true)
    var entityId: Long,

    @NonNull
    var parentId: String,

    @NonNull
    var locationId: String,

    @NonNull
    var type: String
) {
    constructor() : this(0, "", "", "")
}

• Foreign Key added for referential integrity and also CASCADE deletes (UPDATE CASCADE not really required unless you change a jobid, other updates aren't cascaded (and wouldn't need to be))

@Database used JobDatabase

@Database(entities = [Location::class,Job::class],version = 1)
abstract class JobDatabase: RoomDatabase() {
    abstract fun getJobDao(): JobDAO
    
    companion object {
        var instance: JobDatabase? = null
        fun getInstance(context: Context): JobDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context, JobDatabase::class.java, "job.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as JobDatabase
        }
    }
}

• allowMainThreadQueries used to allow testing on main thread

The test/demo activity MainActivity

class MainActivity : AppCompatActivity() {

    lateinit var db: JobDatabase
    lateinit var dao: JobDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = JobDatabase.getInstance(this)
        dao = db.getJobDao()

        var jobId: String = "Job1"
        var jw = JobWrapper(
            Job(
                jobId,
                "x",
                "Fred",
                "Note for Job1",
                "Org1",
                "A","Cust1",
                "Cust1"),
            listOf(
                Location(0,jobId,"loc1","J"),
                Location(0,jobId,"Loc2","K"),
                Location(0,jobId,"Loc3","L")
            )
        )
        dao.insert(jw)
        dao.insertAll(createJobWrapperList(10))
        dao.delete(dao.getJobWrapperByJobId("job6"),true)
        var jobWrapper = dao.getJobWrapperByJobId("job7")
        jobWrapper.job.creator = "update creator"
        for (l in jobWrapper.locations) {
            if (l.type == "M") l.type= "UPDATED"
        }
        dao.update(jobWrapper)
    }

    fun createJobWrapperList(numberToCreate: Int): List<JobWrapper> {
        val l = mutableListOf<JobWrapper>()
        for(i in 1..numberToCreate) {
            var jid = "job$i"
            l.add(
                JobWrapper(
                Job(jid,"X","Creator$i","Note for $jid","org$jid","T","custA","custT"),
                    arrayListOf(
                        Location(0,jid,"loc_$jid.1","L"),
                        Location(0,jid,"loc_$jid.2","M"),
                        Location(0,jid,"loc_$jid.3","N")
                    )
                )
            )
        }
        return l.toList()
    }
}

This :-

1. gets DB instance and the dao.
2. adds a job and it's location via a single JobWrapper
3. adds x (10) jobs and 3 locations per Job via a List of JobWrappers generated via the createJobWrapperList function. 4.deletes the a JobWrapper obtained via getJobWrapperByJobId including deleting the underlying locations (true) using delete for the JobWrapper associated with the jobid "job6".
4. obtains the JobWrapper associated with "job7" changes the creator and changes the locations that have a type of "M" to "UPDATED" (just the one) and then uses the update(JobWrapper) to apply the updates.

WARNING

Inserting using a JobWrapper, as it has REPLACE conflict strategy will result in additional locations if it replaces as the entityId will always be generated.

Result

Runinng the above results in :-

Job Table :-

• As can be seen job6 has been deleted (11 rows added 10 left) and job7's creator has been updated.

Location Table :-

• As can be seen no job6 locations (was 33 locations (11 * 3) now 30) and the location that was type M has been updated according to the JobWrapper passed.

You asked :-

How do I ensure the relationship to the right parent (job) when inserting the childs (locations)?

I think the above demonstrates how.

Answer 2

JobWrapper does not have an associated table with it. Note that it is nice for pulling from your database because it will grab from the Location table and the Job table, but it makes no sense to insert into your database with a JobWrapper because there is no associated table for it.

Instead, you need to insert Jobs, and Locations separately. The database can query for them together because they are related by jobid and parentid, so you don't have to worry about them losing their relationship to each other, and you can still query for your JobWrapper.

If given my above explanation you still think that you should be able to insert a JobWrapper, then you might create a repository with a method like:

suspend fun insertWrapper(wrapper : JobWrapper){
    dao.insertJob(wrapper.job)
    dao.insertLocations(wrapper.locations)
}

Where insertJob inserts a single Job, and insertLocations inserts a list of Location.