Android store nested JSON Objects from Api call to Sqlite

Question

How do I store Object that have other Objects as Instance Variable in an android SQLite database.

Basically, I have my JSON response from Rest Api call to look like:

{
  "announcements": [
    {
      "courseId": "196014605914",
      "id": "269865109791",
      "text": "Ignore the previous link. Join the already started CHM 112",
      "state": "PUBLISHED",
      "alternateLink": "https://classroom.google.com/c/MTk2MDE0NjA1OTE0/p/MjY5ODY1MTA5Nzkx",
      "creationTime": "2021-02-08T09:18:05.420Z",
      "updateTime": "2021-02-08T09:18:05.415Z",
      "creatorUserId": "101562079220031604157"
    },
    {
      "courseId": "196014605914",
      "id": "246419265642",
      "text": "Note2",
      "materials": [
        {
          "driveFile": {
            "driveFile": {
              "id": "1CjL0RV7PdcIrrRWii1ApUown3YoyEKTx",
              "title": "HYDROCARBONS2-C-C, C=C AND ALKYNES.pdf",
              "alternateLink": "https://drive.google.com/open?id=1CjL0RV7PdcIrrRWii1ApUown3YoyEKTx",
              "thumbnailUrl": "https://drive.google.com/thumbnail?id=1CjL0RV7PdcIrrRWii1ApUown3YoyEKTx&sz=s200"
            },
            "shareMode": "VIEW"
          }
        }
      ],
 "state": "PUBLISHED",
      "alternateLink": "https://classroom.google.com/c/MTk2MDE0NjA1OTE0/p/MjQ2NDE5MjY1NjQy",
      "creationTime": "2021-01-25T10:41:45.094Z",
      "updateTime": "2021-01-25T10:41:44.260Z",
      "creatorUserId": "101562079220031604157"
    },
  ],
  "nextPageToken": "GkUSQxJBCLHA5Pr4LhI4Cg5iDAi2pfD_BRDAoOKzAQoOYgwItqXw_wUQgMCNtwEKCgiAgICg29jhsFoKCgiAgIDgwu223Hk"
}

This is part of the Announcement Object but some have others don't have a Material object as seen above. The problem is not all Announcements have Material object and more so a single Announcement object can have upto 20 material object.

My data model looks like below:

public class CourseDetailsItem{
    private final String message;
    private final Long time;
    private final String author;
    private final List<Material> materials;

//... Appropriate getters and setters

//Material.java
public class Material{
    private final String label;
    private final String link;

//... Appropriate getters and setters

What is the best way to store the Object for offline usage?

In my DbHelper class I'm confused

SQLiteDatabase db = this.getWritableDatabase();
        String CREATE_COURSE_TABLE = "CREATE TABLE IF NOT EXISTS _"+courseId+" ("+ ID +" INTEGER PRIMARY KEY," + MESSAGE + " TEXT,"+ TIME + " INTEGER," + AUTHOR + " TEXT," + LABEL + " TEXT," + LINK + " TEXT"+")";
        db.execSQL(CREATE_COURSE_TABLE);
        ContentValues values = new ContentValues();
        values.put(MESSAGE, courseDetailsItem.getMessage());
        values.put(TIME, courseDetailsItem.getTime());
        values.put(AUTHOR, courseDetailsItem.getAuthor());
        //values.put(); HERE HOW DO I STORE MATERIAL OBJECT
        //Considering the fact that Material Object could be upto Twenty for each  of my entry

Anyone please...

And please no negative energy here, I just couldn't get my brain about a solution in here

Answer 1

The best - or most intuitive - way to do so is to also reflect their relationship in the relational SQLite database.

This means you'd have an Announcement table and a Material table.

In the Material table you'll need to add a 'foreign key' with the primary id of the Announcement it refers to. This way you'll be able to store 0...n Materials for an Announcement. Your Announcement model/ table doesn't need to be changed and can stay as is.

Note: As far as I know SQLite doesn't support real 'foreign key' definitions. This isn't too bad, since it's only about the concept of the 'pointer' from one table to another based on the primary key. In a real, full-fledged database it would evaluate it and ensure the data integrity based on it. This strictness is not always needed or wanted and therefore not supported in SQLite - to make it more lightweight. Also you can ensure the same data integrity with properly written code.

Then you'd have two tables looking like the following:

Announcement (courseId, id, text, ...)

Material (announcementId, id, title, alternateLink, ...)

Edit: This is also called a OneToMany relationship. When you look for 'modelling a OneToMany relationship in relational database' you will find a lot of resources explaining it in more detail.

When storing an Announcement you'll also 'simply' store the Material objects in their table. Here you can evaluate if you want to maintain a strong data integrity and want to store all Announcement and Material objects in one single transaction or if it doesn't need to be so strict and you'll store it in different transactions.

There are several possibilities:

• A single Announcement object is stored along with all it's Materials in one transaction
• All Announcement objects are stored along with all Materials in one transaction
• All Announcement objects are stored in one transaction, then all Material objects are stored in one transaction

The first option is to logically prefer and will provide the best user experience.

When loading the Announcements from the database you can decide whether you want to load the list of Materials eagerly or lazy. The query should look like:

SELECT * FROM Material WHERE announcementId = this.id.

It'll return you the list of Materials associated with the individual Announcement.