Android Room [SQLITE_ERROR] SQL error or missing database when using Relation and Junction

user_12945568 0
Viewed 2122

this is my first question here.

I'm building an app using Room database and I tried following this tutorial because I need to implement a many-to-many relation.

However I keep getting the following error as soon as I try to build the app:

error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: OwnerDogCrossRef) private final java.util.List dogs= null;

My entities, with the cross reference data class:

@Entity(tableName = "owner_table")
data class Owner(
    @ColumnInfo(name = "owner_id")
    val id: String,

    @PrimaryKey
    @ColumnInfo(name = "owner_name", index = true)
    val name: String,
    // some other columns

@Entity(tableName = "dog_table")
data class Dog(
    @PrimaryKey
    @ColumnInfo(name = "dog_name")
    val name: String
    // some other columns

@Entity(primaryKeys = ["owner_name", "dog_name"])//, "move_learned_by"])
data class OwnerDogCrossRef(
    val owner_name: String,
    @ColumnInfo(index = true)
    val dog_name: String
    // some other columns

My junction data class:

data class OwnerWithDogs(
    @Embedded val owner: Owner,

    @Relation(
        parentColumn = "owner_name",
        entityColumn = "dog_name",
        associateBy = Junction(OwnerDogCrossRef::class)
    )
    val dogs: List<Dog>
)

My DAO:

@Dao
inteface OwnerDao {
    @Transaction
    @Query("SELECT * FROM owner_table WHERE owner_name = :name")
    fun getOwnerWithDogs(name: String): LiveData<List<OwnerWithDogs>>
}

I also added the OwnerDogCrossRef to my database as below:

@Database(
    entities = [Owner::class, Dog::class, OwnerDogCrossRef::class],
    version = 2,
    exportSchema = false
)
@TypeConverters(Converters::class)
abstract class MainDatabase : RoomDatabase() {
    //some logic
}

Thank you for your help

android-room kotlin android
2 Answers

Go to your Database.kt file and make sure the cross-reference table has been included in the list of entities there. Yours may differ to the example below, but I hope you can see what you may have missed.

@Database(entities = [Owner::class, Dog::class, OwnerDogCrossRef::class], version = 1)
abstract class AppDatabase : RoomDatabase() {

...

}

This is because the Owner, Dog, and OwnerDogCrossRef are all tables that the database needs to know about, whereas OwnerWithDogs is merely going to join the relevant tables in a transaction, as multiple queries will be run, as per documentation. See also here for the database documentation showing how to include the entities for a particular database.

user_22609490 
data class OwnerWithDogs(
    @Embedded val owner: Owner,

    @Relation(
        parentColumn = "owner_name",
        entity = Dog.class,
        entityColumn = "dog_name",
        associateBy = @Junction(
        value=OwnerDogCrossRef.class,
        parentColumn = "owner_name",//variable name in your OwnerDogCrossRef
        entityColumn = "dog_name")
    )
    val dogs: List<Dog>
)

Try this code. For further ideas ,try this link
https://developer.android.com/reference/androidx/room/Junction

user_60595830
Related