How can I store data with nested object data in Android? [Room]

I have a high dimensional dataset that it provides with a json structure (about 3000 objects, an array of each object and dozens of objects in that array.) I read them with the help of the volley library. I want to save this data. in the database and access it easily. how can I do it

I couldn’t find examples for Room

JSON Data

{
"MyData": [
 {
   "food_id": "1",
   "food_name": "Food 1",
   "food_image": "imageurl",
   "food_kcal": "32",
   "food_url": "url",
   "food_description": "desc",
   "carb_percent": "72",
   "protein_percent": "23",
   "fat_percent": "4",
   "units": [
     {
       "unit": "Unit A",
       "amount": "735.00",
       "calory": "75.757",
       "calcium": "8.580",
       "carbohydrt": "63.363",
       "cholestrl": "63.0",
       "fiber_td": "56.12",
       "iron": "13.0474",
       "lipid_tot": "13.01",
       "potassium": "11.852",
       "protein": "717.1925",
       "sodium": "112.02",
       "vit_a_iu": "110.7692",
       "vit_c": "110.744"
     },
     {
       "unit": "Unit C",
       "amount": "32.00",
       "calory": "23.757",
       "calcium": "53.580",
       "carbohydrt": "39.363",
       "cholestrl": "39.0",
       "fiber_td": "93.12",
       "iron": "93.0474",
       "lipid_tot": "93.01",
       "potassium": "9.852",
       "protein": "72.1925",
       "sodium": "10.0882",
       "vit_a_iu": "80.7692",
       "vit_c": "80.744"
     }
   ]
 },
 {
   "food_id": "2",
   "food_name": "Food 2",
   "food_image": "imageurl",
   "food_kcal": "50",
   "food_url": "url",
   "food_description": "desc",
   "carb_percent": "25",
   "protein_percent": "14",
   "fat_percent": "8",
   "units": [
     {
       "unit": "Unit A",
       "amount": "25.00",
       "calory": "25.757",
       "calcium": "55.580",
       "carbohydrt": "53.363",
       "cholestrl": "53.0",
       "fiber_td": "53.12",
       "iron": "53.0474",
       "lipid_tot": "53.01",
       "potassium": "17.852",
       "protein": "757.1925",
       "sodium": "122.02",
       "vit_a_iu": "10.7692",
       "vit_c": "10.744"
     },
     {
       "unit": "Unit C",
       "amount": "2.00",
       "calory": "2.757",
       "calcium": "5.580",
       "carbohydrt": "3.363",
       "cholestrl": "3.0",
       "fiber_td": "3.12",
       "iron": "3.0474",
       "lipid_tot": "3.01",
       "potassium": "77.852",
       "protein": "77.1925",
       "sodium": "12.02",
       "vit_a_iu": "0.7692",
       "vit_c": "0.744"
     },
     {
       "unit": "Unit G",
       "amount": "1.00",
       "calory": "2.1",
       "calcium": "0.580",
       "carbohydrt": "0.363",
       "cholestrl": "0.0",
       "fiber_td": "0.12",
       "iron": "0.0474",
       "lipid_tot": "0.01",
       "potassium": "5.852",
       "protein": "0.1925",
       "sodium": "1.02",
       "vit_a_iu": "0.7692",
       "vit_c": "0.744"
     }
   ]
 }
]
}

Answer

For SQLite

The first thing that you have to do is determine the schema of the database.

Looking at your data you have a lots of MyData objects, each of which will have 0 or more Unit objects.

Thus you could have a table for the MyData objects and table for the Unit objects. Each Unit will have a parent MyData object. So in addition to the data for each Unit you could have a column that references (maps, associates) the MyData (probably it’s food_id assuming that this uniquely identifies the MyData).

Using a shortened version of MyData (just the food_id, food_name) and a shortened version of Unit (unit, amount and calory) to demonstrate then you could have two classes as:-

class MyData {
    public static final String TABLE_NAME = "_mydata";
    public static final String COLUMN_FOOD_ID = "_food_id";
    public static final String COLUMN_FOOD_NAME = "_food_name";
    long food_id;
    String food_name;
    Unit[] units;

    public MyData(long food_id, String food_name, Unit[] units) {
        this.food_id = food_id;
        this.food_name = food_name;
        this.units = units;
    }
}

and

class Unit {
    public static final String TABLE_NAME = "_unit";
    public static final String COLUMN_UNIT = "_unit";
    public static final String COLUMN_AMOUNT = "_amount";
    public static final String COLUMN_CALORY = "_calory";
    public static final String COLUMN_FOOD_ID_PARENT ="parent_food_id";
    String unit;
    double amount;
    double calory;

    public Unit(String unit, double amount, double calory) {
        this.unit = unit;
        this.amount = amount;
        this.calory = calory;
    }
}

Note the constants that will be used for the tables i.e. the schema. These are placed into the classes for brevity, they could be coded elsewhere.

Note the additional column parent_food_id, this will be used to reference the parent food_id.

So the schema will(could) look like

1. Table _mydata which has 2 columns (easy to add more)
   1. food_id (which will be unique (the PRIMARY KEY) and as the value is an integer then INTEGER type if SQLite), and
   2. food_name (as it is a String then TEXT type in SQLite).
2. Table _unit which has 4 columns
   1. _unit (as it is a String then TEXT type in SQLite)
   2. _amount (as it is a floating point type then REAL in SQLite)
   3. _calory (as it is a floating point type then REAL in SQLite)
   4. parent_food_id as it refers to the food_id which is INTEGER then it will be INTEGER.
      1. As it’s what creates the relationship between the 2 tables then it should be a requirement that it does actually reference a food_id. Although not required it is a good idea to enforce the integirty of the reference (referential integrity) for this a Foreign Key constraint could be added.
   • It is always wise to have a primary key but as other MyData objects may have the same value for the units and or the amount and or the calory, it is probably unlikely that the combination of all three will be the same. So the primary key is all 3 columns.

In SQLite terms the SQL’s (one for each table) that will create the above could be :-

• CREATE TABLE _mydata(_food_id INTEGER PRIMARY KEY,_food_name TEXT), and
• CREATE TABLE _unit(_unit TEXT,_amount REAL,_calory REAL,parent_food_id INTEGER REFERENCES _mydata(_food_id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(_unit,_amount,_calory))

Handling the Database

With Android and SQLite then the typical way is to utilise a DatabaseHelper that extends the SQLiteOpenHelper. SQLiteOpenHelpr requires 2 overidden methods onCreate and onUpgrae

• onCreate runs when the database is created.
  • It runs once only for the existence of the database which persists i.e. it will only run when the App is first run.
  • this method is typically used to define the entities/components such as tables.
• onUpgrade runs of the database version passed to the SQLiteOpenHelper is greater than the version number extracted from the database itself.

Typically code for accessing the database such as adding, removing, updating, deleting or extracting data.

For the demonstration the Database will will just have methods for inserting data into the tables. Additionally a method that will take a JSON String (equivalent of a file) and populate the tables.

So the DatabaseHelper class could be :-

class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "my_database.db";
    public static final int DATABASE_VERSION = 1;

    private SQLiteDatabase db;
    private static volatile DatabaseHelper instance = null;
    private DatabaseHelper(Context context) {
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
        db = this.getWritableDatabase();
    }

    /* Use a singleton approach */
    public static DatabaseHelper getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseHelper(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "CREATE TABLE IF NOT EXISTS " + MyData.TABLE_NAME + "(" +
                        MyData.COLUMN_FOOD_ID + " INTEGER PRIMARY KEY," +
                        MyData.COLUMN_FOOD_NAME + " TEXT" +
                        ")"
        );

        db.execSQL(
                "CREATE TABLE IF NOT EXISTS " + Unit.TABLE_NAME + "(" +
                        Unit.COLUMN_UNIT + " TEXT," +
                        Unit.COLUMN_AMOUNT + " REAL," +
                        Unit.COLUMN_CALORY + " REAL," +
                        Unit.COLUMN_FOOD_ID_PARENT +
                        /* Foreign Key Constraint to enforce referential integrity*/
                        " INTEGER REFERENCES " + MyData.TABLE_NAME + "(" + MyData.COLUMN_FOOD_ID + ") " +
                        /* These make maintaining referential integrity easier */
                        "ON DELETE CASCADE " +
                        "ON UPDATE CASCADE, " +
                        /* define the primary key */
                        " PRIMARY KEY(" +
                        Unit.COLUMN_UNIT + "," + Unit.COLUMN_AMOUNT + "," + Unit.COLUMN_CALORY +
                        ")" +
                        ")"
        );

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        /* not expecting to increase the database version so leave this to do nothing */
    }

    public long insertMyData(MyData myData) {
        ContentValues cv = new ContentValues();
        cv.put(MyData.COLUMN_FOOD_ID,myData.food_id);
        cv.put(MyData.COLUMN_FOOD_NAME,myData.food_name);
        return db.insert(MyData.TABLE_NAME,null,cv);
    }

    public long insertUnit(Unit unit, long parentId) {
        ContentValues cv = new ContentValues();
        cv.put(Unit.COLUMN_UNIT,unit.unit);
        cv.put(Unit.COLUMN_AMOUNT,unit.amount);
        cv.put(Unit.COLUMN_CALORY,unit.calory);
        cv.put(Unit.COLUMN_FOOD_ID_PARENT,parentId);
        return db.insert(Unit.TABLE_NAME,null,cv);
    }

    public void massInsert(String jsonString) {
        MyData[] extracted = new Gson().fromJson(jsonString,MyData[].class);
        db.beginTransaction();
        for(MyData m: new Gson().fromJson(jsonString,MyData[].class)) {
            long food_id = insertMyData(m);
            if (food_id > 0) {
                for(Unit u: m.units) {
                    insertUnit(u,food_id);
                }
            }
        }
        db.setTransactionSuccessful();
        db.endTransaction();
    }
}

Demonstration

Here’s a demonstration of using the above in an activity, namely MainActivity:-

public class MainActivity extends AppCompatActivity {

    MyData[] TESTDATA = {
            new MyData(
                    1,
                    "Bat",
                    new Unit[]{
                            new Unit("Unit A",15.0000,32.4877372383),
                            new Unit("Unit C",110.0000,238.243404414),
                            new Unit("Unit F",1.0000,2.16584914922)
                    }
                    ),
            new MyData(
                    2,
                    "Another",
                    new Unit[]{
                            new Unit("Unit A",17.0000,3.4877372383),
                            new Unit("Unit C",10.0000,382.243404414),
                            new Unit("Unit F",3.0000,5.16584914922)
                    }
            ),
    };

    DatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        String test = new Gson().toJson(TESTDATA);
        Log.d("APPINFO","TESTDATA as JSON isnt" + test);
        //MyData[] extracted = new Gson().fromJson(test,MyData[].class);

        dbHelper = DatabaseHelper.getInstance(this); // Prepare to use the database
        dbHelper.massInsert(test); // Actually use the database to insert the data

        /* Example of extracting data using a JOIN to combine the MyData and the related Units */
        Cursor csr =  dbHelper.getWritableDatabase().query(
                MyData.TABLE_NAME + " JOIN " + Unit.TABLE_NAME + " ON " + Unit.COLUMN_FOOD_ID_PARENT + "=" + MyData.COLUMN_FOOD_ID,
                null,null,null,null,null,null
                );
        DatabaseUtils.dumpCursor(csr);
        csr.close();

    }
}

Results

When run the log includes :-

2022-01-08 07:32:14.666  D/APPINFO: TESTDATA as JSON is
        [{"food_id":1,"food_name":"Bat","units":[{"amount":15.0,"calory":32.4877372383,"unit":"Unit A"},{"amount":110.0,"calory":238.243404414,"unit":"Unit C"},{"amount":1.0,"calory":2.16584914922,"unit":"Unit F"}]},{"food_id":2,"food_name":"Another","units":[{"amount":17.0,"calory":3.4877372383,"unit":"Unit A"},{"amount":10.0,"calory":382.243404414,"unit":"Unit C"},{"amount":3.0,"calory":5.16584914922,"unit":"Unit F"}]}]

and

2022-01-08 07:32:14.698  I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f302fce
2022-01-08 07:32:14.699  I/System.out: 0 {
2022-01-08 07:32:14.699  I/System.out:    _food_id=1
2022-01-08 07:32:14.699  I/System.out:    _food_name=Bat
2022-01-08 07:32:14.699  I/System.out:    _unit=Unit A
2022-01-08 07:32:14.699  I/System.out:    _amount=15
2022-01-08 07:32:14.699  I/System.out:    _calory=32.4877
2022-01-08 07:32:14.699  I/System.out:    parent_food_id=1
2022-01-08 07:32:14.699  I/System.out: }
2022-01-08 07:32:14.699  I/System.out: 1 {
2022-01-08 07:32:14.699  I/System.out:    _food_id=1
2022-01-08 07:32:14.699  I/System.out:    _food_name=Bat
2022-01-08 07:32:14.700  I/System.out:    _unit=Unit C
2022-01-08 07:32:14.701  I/System.out:    _amount=110
2022-01-08 07:32:14.701  I/System.out:    _calory=238.243
2022-01-08 07:32:14.701  I/System.out:    parent_food_id=1
2022-01-08 07:32:14.701  I/System.out: }
2022-01-08 07:32:14.701  I/System.out: 2 {
2022-01-08 07:32:14.701  I/System.out:    _food_id=1
2022-01-08 07:32:14.701  I/System.out:    _food_name=Bat
2022-01-08 07:32:14.701  I/System.out:    _unit=Unit F
2022-01-08 07:32:14.701  I/System.out:    _amount=1
2022-01-08 07:32:14.701  I/System.out:    _calory=2.16585
2022-01-08 07:32:14.701  I/System.out:    parent_food_id=1
2022-01-08 07:32:14.701  I/System.out: }
2022-01-08 07:32:14.702  I/System.out: 3 {
2022-01-08 07:32:14.702  I/System.out:    _food_id=2
2022-01-08 07:32:14.702  I/System.out:    _food_name=Another
2022-01-08 07:32:14.702  I/System.out:    _unit=Unit A
2022-01-08 07:32:14.702  I/System.out:    _amount=17
2022-01-08 07:32:14.702  I/System.out:    _calory=3.48774
2022-01-08 07:32:14.702  I/System.out:    parent_food_id=2
2022-01-08 07:32:14.702  I/System.out: }
2022-01-08 07:32:14.702  I/System.out: 4 {
2022-01-08 07:32:14.702  I/System.out:    _food_id=2
2022-01-08 07:32:14.703  I/System.out:    _food_name=Another
2022-01-08 07:32:14.703  I/System.out:    _unit=Unit C
2022-01-08 07:32:14.703  I/System.out:    _amount=10
2022-01-08 07:32:14.703  I/System.out:    _calory=382.243
2022-01-08 07:32:14.703  I/System.out:    parent_food_id=2
2022-01-08 07:32:14.703  I/System.out: }
2022-01-08 07:32:14.703  I/System.out: 5 {
2022-01-08 07:32:14.703  I/System.out:    _food_id=2
2022-01-08 07:32:14.703  I/System.out:    _food_name=Another
2022-01-08 07:32:14.703  I/System.out:    _unit=Unit F
2022-01-08 07:32:14.703  I/System.out:    _amount=3
2022-01-08 07:32:14.704  I/System.out:    _calory=5.16585
2022-01-08 07:32:14.704  I/System.out:    parent_food_id=2
2022-01-08 07:32:14.704  I/System.out: }
2022-01-08 07:32:14.704  I/System.out: <<<<<

Using Android Studio’s App Inspection the database looks like :-

and