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

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

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

CodePudding user response：

Will it know that it is a ForeignKey and will create a Person::class automatically?

Absolutely not.

@ForeignKey defines a ForeignKey constraint i.e. a rule that says that the column(s) to which the constraint is applied must be a value in the referenced column(s) in the referenced table. The referenced (Parent) tables have to exist and be populated accordingly with values that match the rule.

Type converters are used to convert an unhandled type (not a type this is integer (e.g. Int Long Byte etc), String, decimal number (Float, Double etc) or ByteArray) into a handled type.

As an example your :-

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

would convert a single column such as

client: Client

from a Client To a JSON String and store that string in the client column. It is not going to split the client into individual values and place them into individual columns.

So with your retrieved JSON String you extract Client objects with embedded Person and Payment Type.

You can only successfully insert the Client if all the Foreign Keys can be met.

So you should probably check that the account_id exists in the account table. The check that the person_id,account_id exists in the person_table and so on before inserting the Client otherwise the insert will fail.

If the checks fail to identify the rows then you either have to abort or insert the appropriate rows into the tables.

Assuming that your source data is referentially correct. Then you should first extract the highest level parent (Account I believe) inserting them. You can then extract the next level (Person and Payment Type) and insert them and then finally insert the Client. This way the Foreign Keys should exist.

Al alternative would be to turn off Foreign key support and load the data and then turn on Foreign Key support back on. However, if the data is not referentially correct you may encounter Foreign Key constraint conflicts.

e.g. db.openHelper.writableDatabase.setForeignKeyConstraintsEnabled(false)

I've got with the following Client data class which I've turned into a Room @Entity with ForeignKeys:

based upon the JSON you will have issues with the null values unless you ensure that the values are changed to appropriate values.

e.g. "updatedAt": null, associated with @ColumnInfo(name = "updated_at") val updatedAt: Date, unless the TypeConverter returns a non-null value will fail.

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

It does not e.g. you have:-

payment_type_id INTEGER, but @ColumnInfo(name = "payment_type_id") val paymentType: Int, The former does not have the NOT NULL constraint, the latter has an implicit NOT NULL (val paymentType: Int? does not have the implicit NOT NULL)
- repeated for a number of columns
status INTEGER DEFAULT 1 NOT NULL, but @ColumnInfo(name = "status") val status: Int, the latter does not have the default value using defaultValue = "1" in the @ColumnInfo annotation would apply it.
- However, you cannot use the convenience @Insert annotated function as it will ALWAYS supply a value. To have the default value apply you would have to use @Query("INSERT INTO (csv_of_the_columns_that_are_not_to_have_a_default_value_applied) VALUES ....
CONSTRAINT client_pk PRIMARY KEY (client_id, account_id) but @PrimaryKey @ColumnInfo(name = "client_id") val clientId: Int,. Only the client ID is the Primary Key. You do have indices = [Index(value = arrayOf("client_id", "account_id"), unique = true)]. However, you should instead have primaryKeys = arrayOf("client_id", "account_id")

Additional

Having had a closer look. I believe that your issue is not with type converters nor at present with Foreign keys but with what is an attempt to fit the square peg into the round hole.

Without delving into trying to ignore fields from a JSON perspective a solution to what I believe is the core issue is that the you cannot just fit the Client with Person and Payment objects Embedded into the Client that you want to store.

So first consider this alternative class for the Entity renamed ClientTable :-

@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: 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 paymentTypeid: Int,
    @ColumnInfo(name = "person_id") val personid: 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,

    /* Not required but may be useful BUT will not be columns in the table */
    @Ignore
    val person: Person,
    @Ignore
    val paymentType: PaymentType
)

The only changes are the two additional BUT @Ignore annotated vals, for the Person and for the PaymentType. The @Ignore results in them not being included as a column in the table. They are there just for demonstration (you might have problems with them being null when extracting the data from the database).

Note that for testing the PaymentType is :-

@Entity
data class PaymentType(
    @PrimaryKey
    val paymentTypeId: Long? = null,
    val paymentTypeName: String
)

and the Person is :-

@Entity
data class Person(
    @PrimaryKey
    val personId: Long,
    val personName: String
)

so the // Omitted data here does not cause issues.

Instead of your JSON the following JSON has been used (however it is built on the fly) :-

{"clientId":1,"deliveryInstructions":"x","eventDate":"Jan 21, 2022 10:57:59 AM","monthlyPaymentAmount":111.11,"paymentType":{"paymentTypeId":20,"paymentTypeName":"Credit Card"},"person":{"personId":10,"personName":"Bert"},"referralName":"Fred","startDate":"Jan 21, 2022 10:57:59 AM","status":1,"syncedAt":"Jan 21, 2022 10:57:59 AM","updatedAt":"Jan 21, 2022 10:57:59 AM"}

A simple not Type Converter json extractor to mimic the API has been added:-

class JsonApiExample {
    
     fun testExtractJsonFromString(json: String): Client {
          return Gson().fromJson(json,Client::class.java)
     }
}

Now to the other peg The Client with the embedded Person/PaymentType and WITHOUT the personId and paymentTypeId that are not fields in the JSON:-

data class Client(
    val clientId: Int,
    val deliveryInstructions: String,
    val eventDate: Date,
    val monthlyPaymentAmount: Float,
    val referralName: String,
    val startDate: Date,
    val status: Int,
    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!!.toInt(),
            this.person.personId.toInt(),
            this.referralName,
            this.startDate,
            this.status,
            this.updatedAt,
            this.syncedAt,
            this.person,
            this.paymentType
        )
    }
}

As you can see the important bit is the getClientAsClientTable funtion. This will generate and return a ClientTable Object and effectively make the square peg round to fit.

So testing it, as far as creating a ClientTable that could be inserted (Foreign Keys permitting, which would not be the case due to there being no account_id column in the ClientTable nora field in the Client class) consider :-

class MainActivity : AppCompatActivity() {
    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,"Bert"), paymentType = PaymentType(20,"Credit Card"))

        /* Convert the Client to JSON mimicing the API and write it to the log to allow inspection */
        val jsonClientX = Gson().toJson(clientx)
        Log.d("TESTIT",jsonClientX)

        /* Extract the Client from the JSON */
        val clientxrevisited = JsonApiExample().testExtractJsonFromString(jsonClientX)
        /* Extract the ClientTable from the Client */
        val ClientTable = clientxrevisited.getClientAsClientTable()
        /* Allow a Break point to be placed so the results can be inspected*/
        if (1 == 1) {
            Log.d("TESTIT","Testing")
        }
    }
}

When run with a BreakPoint :-

The ticks are the columns
The highlighted are the embedded objects from which you would be able to insert or ignore into the respective tables.
- note that the fields may well be different just id and name were chosen just to demonstrate.

So to RECAP two classes; one for the JSON extract/import, and the other for the Entity/Table with a means of turning one into the other.

A single class may be possible if you can ascertain how to ignore/map JSON fields e.g. perhaps this How to add and ignore a field for json response