Best way to deserialize Json with nested objects into Android Room Entity with ForeignKeys

Question

I have a Client api. The json response looks like this:

 {
    "clientId": 1,
    "createdAt": null,
    "updatedAt": null,
    "monthlyPaymentAmount": null,
    "person": {
      // Omitted data here
    },
    "paymentType": {
      // Omitted data here
    },
    "deliveryInstructions": null,
    "referralName": null,
    "referralPhoneNumber": null,
    "status": 0,
    "startDate": null,
    "eventDate": null,
  }

So, using the Kotlin data class file from JSON to automatically create data classes from the json response, I've got with the following Client data class which I've turned into a Room @Entity with ForeignKeys:

@Entity(
    tableName = "client",
    foreignKeys = [
        ForeignKey(
            entity = Account::class,
            parentColumns = arrayOf("account_id"),
            childColumns = arrayOf("account_id"),
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Person::class,
            parentColumns = arrayOf("person_id", "account_id"),
            childColumns = arrayOf("person_id", "account_id"),
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = PaymentType::class,
            parentColumns = arrayOf("payment_type_id", "account_id"),
            childColumns = arrayOf("payment_type_id", "account_id"),
        ),
    ],
    indices = [
        Index(value = arrayOf("client_id", "account_id"), unique = true)
    ]
)
data class Client(
    @PrimaryKey
    @ColumnInfo(name = "client_id") val clientId: Int,
    @ColumnInfo(name = "delivery_notes") val deliveryInstructions: String,
    @ColumnInfo(name = "event_date") val eventDate: Date,
    @ColumnInfo(name = "monthly_payment_amount") val monthlyPaymentAmount: Float,
    @ColumnInfo(name = "payment_type_id") val paymentType: Int,
    @ColumnInfo(name = "person_id") val person: Int,
    @ColumnInfo(name = "referral_name") val referralName: String,
    @ColumnInfo(name = "start_date") val startDate: Date,
    @ColumnInfo(name = "status") val status: Int,
    @ColumnInfo(name = "updated_at") val updatedAt: Date,
    @ColumnInfo(name = "synced_at") val syncedAt: Date,
)

There's also PaymentType and Person data classes which I'm omitting, but they do are Room @Entity's as well.

The Room database needs to match the following database structure that has this CREATE TABLE SQL statement:

CREATE TABLE client
(
    client_id              INTEGER NOT NULL,
    account_id             INTEGER NOT NULL,
    updated_at             TEXT    NOT NULL,
    synced_at              TEXT    NOT NULL,
    person_id              INTEGER NOT NULL,
    payment_type_id        INTEGER,
    referral_name          TEXT,
    delivery_notes         TEXT,
    status                 INTEGER DEFAULT 1 NOT NULL,
    monthly_payment_amount REAL,
    start_date             TEXT,
    event_date             TEXT,
    CONSTRAINT client_fk1 FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE CASCADE,
    CONSTRAINT client_fk2 FOREIGN KEY (person_id, account_id) REFERENCES person (person_id, account_id) ON DELETE CASCADE,
    CONSTRAINT client_fk4 FOREIGN KEY (payment_type_id, account_id) REFERENCES payment_type (payment_type_id, account_id),
    CONSTRAINT client_pk PRIMARY KEY (client_id, account_id)
);

So, I've a Converters class to deserialize the json response into Client class as follows:

class Converters {

    @TypeConverter
    fun clientToJson(value: Client?): String? = Gson().toJson(value)

    @TypeConverter
    fun jsonToClient(value: String): Client = Gson().fromJson(value, Client::class.java)

    @TypeConverter
    fun paymentTypeToJson(value: PaymentType?): String? = Gson().toJson(value)

    @TypeConverter
    fun jsonToPaymentType(value: String): PaymentType =
        Gson().fromJson(value, PaymentType::class.java)

    @TypeConverter
    fun objToJsonPerson(value: Person?): String? = Gson().toJson(value)

    @TypeConverter
    fun jsonToObjPerson(value: String): Person = Gson().fromJson(value, Person::class.java)

    // Omitted list of converters here
}

I'm hesitant if the client converter above does correctly creates PaymentType and Person objects automatically (mostly convinced that no). That's why I would like to know what's the proper way to deserialize a json response with nested objects into Room entities with Foreign Keys?

I'm most confused with Foreing Keys though. What will happen when the converter above tries to parse the "person": {} object into the @ColumnInfo(name = "person_id") which is of type Int? Will it know that it is a ForeignKey and will create a Person::class automatically? How's the best/proper way to deserialize nested objects ensuring this relation between the tables is properly done?

Answer 1

Demonstration following on from previous answer

This a demonstration of inserting a single client based upon some slightly modified entities.

Account :-

@Entity
data class Account(
    @PrimaryKey
    val account_id: Long? = null,
    val accountName: String   
)

PaymentType

@Entity( primaryKeys = ["payment_type_id","account_id"])
data class PaymentType(
    @ColumnInfo(name = "payment_type_id")
    val paymentTypeId: Long,
    @ColumnInfo(name = "account_id")
    val accountId: Long,
    val paymentTypeName: String
)

• added accountId (account_id column) to suit Foreign Key constraints in the ClientTable (as per question)
• composite primary key

Person (likewise)

@Entity( primaryKeys = ["person_id","account_id"])
data class Person(
    @ColumnInfo(name = "person_id")
    val personId: Long,
    @ColumnInfo(name = "account_id")
    val accountId: Long,
    val personName: String
)

Client as suggested

data class Client(
    val clientId: Long,
    val deliveryInstructions: String,
    val eventDate: Date,
    val monthlyPaymentAmount: Float,
    val referralName: String,
    val startDate: Date,
    val status: Long,
    val updatedAt: Date,
    val syncedAt: Date,
    val person: Person,
    val paymentType: PaymentType
) {
    fun getClientAsClientTable(): ClientTable {
        return ClientTable(
            this.clientId,
            this.deliveryInstructions,
            this.eventDate,
            this.monthlyPaymentAmount,
            this.paymentType.paymentTypeId,
            this.person.personId,
            this.referralName,
            this.startDate,
            this.status,
            this.updatedAt,
            this.syncedAt
        )
    }
}

• ideally id's should be Long rather than Int as they have the potential to overflow an Int. So Long's have been used.

ClientTable formally (Client) :-

@Entity(
    tableName = "client",
    foreignKeys = [
        ForeignKey(
            entity = Account::class,
            parentColumns = arrayOf("account_id"),
            childColumns = arrayOf("account_id"),
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Person::class,
            parentColumns = arrayOf("person_id", "account_id"),
            childColumns = arrayOf("person_id", "account_id"),
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = PaymentType::class,
            parentColumns = arrayOf("payment_type_id", "account_id"),
            childColumns = arrayOf("payment_type_id", "account_id"),
        ),
    ],
    indices = [
        Index(value = arrayOf("client_id", "account_id"), unique = true)
    ]
)
data class ClientTable(
    @PrimaryKey
    @ColumnInfo(name = "client_id") val clientId: Long,
    @ColumnInfo(name = "delivery_notes") val deliveryInstructions: String,
    @ColumnInfo(name = "event_date") val eventDate: Date,
    @ColumnInfo(name = "monthly_payment_amount") val monthlyPaymentAmount: Float,
    @ColumnInfo(name = "payment_type_id") val paymentTypeid: Long,
    @ColumnInfo(name = "person_id") val personid: Long,
    @ColumnInfo(name = "referral_name") val referralName: String,
    @ColumnInfo(name = "start_date") val startDate: Date,
    @ColumnInfo(name = "status") val status: Long,
    @ColumnInfo(name = "updated_at") val updatedAt: Date,
    @ColumnInfo(name = "synced_at") val syncedAt: Date,
    @ColumnInfo(name = "account_id") var accountId: Long = 1 //????? ADDED
)

• NOTE the addition of the accountId

Converters

class Converters {

    @TypeConverter
    fun dateToLong(date: Date): Long {
        return date.time / 1000 // divided by 1000 to strip milliseconds as easier to handle dates
    }

    @TypeConverter
    fun dateFromLong(dateAsLong: Long): Date {
        return Date(dateAsLong * 1000) // reapply milliseconds
    }
}

AllDao (as it implies all of them together) :-

@Dao
abstract class AllDao {
    @Insert(onConflict = IGNORE)
    abstract fun insert(account: Account): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(paymentType: PaymentType): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(person: Person): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(clientTable: ClientTable): Long

    @Query("SELECT count(*) >= 1 FROM account WHERE account_id=:accountId")
    abstract fun doesAccountExistByAccountId(accountId: Long): Boolean
    @Query("SELECT count(*) >= 1 FROM paymenttype WHERE account_id=:accountId AND payment_type_id=:paymentTypeId")
    abstract fun doesPaymentTypeExistByAccountIdPaymentTypeId(accountId: Long, paymentTypeId: Long): Boolean
    @Query("SELECT count(*) >= 1 FROM person WHERE account_id=:accountId AND person_id=:personId")
    abstract fun doesPersonExistByAccountIdPersonId(accountId: Long, personId: Long): Boolean

    @Query("")
    @Transaction
    fun insertFromAPIJson(json: String): Long {
        var rv: Long = -1
        val client = Gson().fromJson(json,Client::class.java)
        val clientTable = client.getClientAsClientTable()
        insert(Account(client.person.accountId,"NO NAME"))
        val accountExits = doesAccountExistByAccountId(client.person.accountId)
        insert(PaymentType(client.paymentType.paymentTypeId,client.paymentType.accountId,client.paymentType.paymentTypeName))
        val paymentTypeExists = doesPaymentTypeExistByAccountIdPaymentTypeId(client.paymentType.accountId,client.paymentType.paymentTypeId)
        insert(Person(client.person.personId, client.person.accountId, client.person.personName))
        val personExists = doesPersonExistByAccountIdPersonId(client.person.accountId,client.person.personId)
        if (accountExits && paymentTypeExists && personExists) {
            clientTable.accountId = client.person.accountId
            rv = insert(clientTable)
        }
        return rv
    }
}

• Obviously note the insertFromAPIJson function
• Also note abstract class rather than an interface
• Note the improvised account name (something you will have to determine how to name)

TheDatabase the abstract class annotated with @Database including a basic getInstance function :-

@TypeConverters(Converters::class)
@Database(entities = [Account::class,ClientTable::class,PaymentType::class,Person::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    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
        }
    }
}

Finally adding the single client from the JSON (again the client is built and the JSON extracted to mimic the API). MainActivity :-

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

        /* Create a Client ready to be converted to JSON */
        val clientx = Client(
            clientId = 1,
            deliveryInstructions = "x",
            eventDate = Date(),
            monthlyPaymentAmount = 111.11F, referralName = "Fred", startDate = Date(), status = 1, updatedAt = Date(), syncedAt = Date(),
            Person(10,1,"Bert"), paymentType = PaymentType(20,1,"Credit Card"))

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()
        dao.insertFromAPIJson(Gson().toJson(clientx))

    }
}

The Result

Using App Inspection :-

The Account has been added :-

The PaymentType has been added :-

The Person has been added :-

and the Client :-

Run again

As expected the data remains the same due to onConflict IGNORE