Room with complex Json structure

Question

I am pretty new in Room and currently doing one of my projects in which I'm supposed to insert some retrofit GSON data into it. First of all, let me show my JSON below which will give a clear structure.

{
"organization": {
    "id": 0,
    "title": "string",
    "description": "HTML String",
    "style": {
        "navigationBackgroundColor": "#cd1325",
        "navigationTextColor": "#ffffff",
        "topBarLabel": "27July2015abcd",
        "topBarBackgroundColor": "#cd1325",
        "topBarTextColor": "#ffffff",
        "bodyBackgroundColor": "#f5c233",
        "bodyTextColor": "#646363",
        "bannerBackgroundColor": "#ffffff",
        "bannerTextColor": "#000000",
        "buttonBackgroundColor": "#000000",
        "buttonTextColor": "#ffffff",
        "baseTextSize": 0,
        "htmlWrapper": "string"
    }
    "login_options": [{
            "name": "string",
            "title": "EMAIL",
            "description": "string",
            "state": "string",
            "allowed_email_domain": "string",
            "restricted_email_domain": "string"
        }, {
            "name": "string",
            "titl"e: "GOOGLE",
            "description": "string",
            "url": "string",
            "clientId": "string",
            "clientSecret": "string",
            "redirectUri": "string",
            "state": "string",
            "nonce": "string",
            "authorizationEndpointUri": "string",
            "tokenEndpointUri": "string"
        }
    ]
}

}

I am parsing this with retrofit which is working pretty well. Below are the model classes names which has created from https://www.jsonschema2pojo.org/

Ok. Now I have to insert these into the Room database through my repository for which I am facing a lot of difficulties. Can anyone help me with how to create the entities and how to insert these data models into the Room. Still not sure whether to insert the GSON models to Room or to create Entities and put the parsing data to those and then to insert. What I have tried till now.

LoginOptionsTable

@Parcelize
@Entity
public class LoginOptionsTable {

@ColumnInfo
@PrimaryKey(autoGenerate = true)
public long loginOpnId;

@ColumnInfo(name = "login_options_name")
public String name;

@ColumnInfo(name = "login_options_title")
public String title;

@ColumnInfo(name = "login_options_description")
public String description;

@ColumnInfo
public String state;

@ColumnInfo
public String allowedEmailDomain;

@ColumnInfo
public String restrictedEmailDomain;

@ColumnInfo
public String url;

@ColumnInfo
public String clientId;

@ColumnInfo
public String clientSecret;

@ColumnInfo
public String redirectUri;

@ColumnInfo
public String nonce;

@ColumnInfo
public String authorizationEndpointUri;

@ColumnInfo
public String tokenEndpointUri;

public static List<LoginOptionsTable> fromObject(List<LoginOption> 
mOptions){

    List<LoginOptionsTable> groups = new ArrayList<>();

    for(int i=0; i<mOptions.size(); i++){
        LoginOptionsTable st = new LoginOptionsTable();

        st.name = mOptions.get(i).getName();
        st.title = mOptions.get(i).getTitle();
        st.description = mOptions.get(i).getDescription();
        st.state = mOptions.get(i).getState();
        st.allowedEmailDomain = mOptions.get(i).getAllowedEmailDomain();
        st.restrictedEmailDomain = 
        mOptions.get(i).getRestrictedEmailDomain();
        st.url = mOptions.get(i).getUrl();
        st.clientId = mOptions.get(i).getClientId();
        st.clientSecret = mOptions.get(i).getClientSecret();
        st.redirectUri = mOptions.get(i).getRedirectUri();
        st.nonce = mOptions.get(i).getNonce();
        st.authorizationEndpointUri = 
        mOptions.get(i).getAuthorizationEndpointUri();
        st.tokenEndpointUri = mOptions.get(i).getTokenEndpointUri();

        groups.add(st);
    }


    return groups;
}

}

Style Entity:

@Parcelize
@Entity
public class StyleTable {

@ColumnInfo
@PrimaryKey(autoGenerate = true)
public long styleId;

@ColumnInfo
public String navigationBackgroundColor;
@ColumnInfo
public String navigationTextColor;
@ColumnInfo
public String topBarLabel;
@ColumnInfo
public String topBarBackgroundColor;
@ColumnInfo
public String topBarTextColor;
@ColumnInfo
public String bodyBackgroundColor;
@ColumnInfo
public String bodyTextColor;
@ColumnInfo
public String bannerBackgroundColor;
@ColumnInfo
public String bannerTextColor;
@ColumnInfo
public String buttonBackgroundColor;
@ColumnInfo
public String buttonTextColor;
@ColumnInfo
public Integer baseTextSize;
@ColumnInfo
public String htmlWrapper;


public static StyleTable fromObject(Style mStyle) {
    StyleTable st = new StyleTable();

    st.navigationBackgroundColor = mStyle.getNavigationBackgroundColor();
    st.navigationTextColor = mStyle.getNavigationTextColor();
    st.topBarLabel = mStyle.getTopBarLabel();
    st.topBarBackgroundColor = mStyle.getTopBarBackgroundColor();
    st.topBarTextColor = mStyle.getTopBarTextColor();
    st.bannerBackgroundColor = mStyle.getBodyBackgroundColor();
    st.bannerTextColor = mStyle.getBannerTextColor();
    st.buttonBackgroundColor = mStyle.getButtonBackgroundColor();
    st.buttonTextColor = mStyle.getButtonTextColor();
    st.baseTextSize = mStyle.getBaseTextSize();
    st.htmlWrapper = mStyle.getHtmlWrapper();

    return st;
}

}

OrgEntity:

@Parcelize
@Entity(foreignKeys = {@ForeignKey(entity = StyleTable.class, parentColumns = 
"styleId", childColumns = "stId"),
    @ForeignKey(entity = LoginOptionsTable.class, parentColumns = 
"loginOptionId", childColumns = "loginOpnId")
})
public class OrgTable {

@ColumnInfo
@PrimaryKey
public long id;

@ColumnInfo
public String title;

@ColumnInfo
public String description;

@ColumnInfo
public long stId;

//TODo make it for mutiple table
@ColumnInfo
public Long loginOptionsId;

@Ignore
public StyleTable style;

@Ignore
public List<LoginOptionsTable> loginOptions = null;

public static OrgTable fromObject(Organization organization){
    OrgTable org = new OrgTable();
    org.id = organization.getId();
    org.title = organization.getTitle();
    org.description = organization.getDescription();
    StyleTable st = StyleTable.fromObject(organization.getStyle());
    org.style = st;
    //make the relation through Id
    org.stId = st.styleId;

    List<LoginOptionsTable> lo = 
   LoginOptionsTable.fromObject(organization.getLoginOptions());
    org.loginOptions = lo;
    //make the relation through Id
    org.loginOptionsId = lo.get(0).loginOpnId;


return org;
}

}

DAO

@Dao
public interface OrgDAO {

@Query("SELECT * FROM OrgTable")
OrgTable getOrganization();

@Insert
void insertOrg(OrgTable org);

}

I have tried to create these, but not able to understand how to keep the relation between these and insert/get the saved data.

Answer 1

I have tried to create these, but not able to understand how to keep the relation between these and insert/get the saved data.

You can do this 2 ways either with:

• a POJO with all three tables @Embedded (only suitable for 1 Org -> 1 Login and 1 Style)

• with a POJO with the OrgTable @Embedded and with the LoginOptionsTable and StyleTable with @Relation annotations

An example of the first (all three tables @Embedded) being :-

class OrgWithLoginAndStyle {

    /* Note use Query that
        JOINS the Orgtable with the Login table
        and JOINS the Orgtable with the Style table
     */

    @Embedded
    OrgTable orgTable;
    @Embedded
    LoginOptionsTable loginOptionsTable;
    @Embedded
    StyleTable styleTable;

}

An example of a Dao that utilises this is:-

@Query("SELECT * FROM OrgTable " +
        "JOIN StyleTable ON StyleTable.styleId = OrgTable.stId " +
        "JOIN LoginOptionsTable ON LoginOptionsTable.loginOpnId = OrgTable.loginOptionsId")
List<OrgWithLoginAndStyle> getOrganizationLoginAndStyle();

• This is more flexible query wise as all columns are available for WHERE clauses etc but the query is more complicated.

An example of the second (with @Relation annotations for the LoginOptionsTable and the StyleTable) :-

class OrganizationWithLoginOptionsAndWithStyles {

    @Embedded
    OrgTable orgTable;
    @Relation(entity = LoginOptionsTable.class,parentColumn = "loginOptionsId",entityColumn = "loginOpnId")
    List<LoginOptionsTable> loginOptionsTables;
    @Relation(entity = StyleTable.class,parentColumn = "stId",entityColumn = "styleId")
    List<StyleTable> styleTables;
}

• This is perhaps simpler to code. However, it is less efficient as the Styles and LoginOptions are retrieved independently for each each OrgTable. As such the query is simpler as you only need to get the OrgTable(s). Room does the work of building related objects. The query is limited to WHERE etc on only the OrgTable columns (if you JOINed the other tables such clauses would possibly not have the desired result (probably OK in your case as there is only 1 LoginOptions and 1 Style per OrgTable object))

An example of a Dao that utilises this is:-

@Transaction
@Query("SELECT * FROM OrgTable")
List<OrganizationWithLoginOptionsAndWithStyles> getOrganizationsLoginsAndStyles();

• Note that @Transaction is advised due to (I believe) underlying queries being run to get the LoginOptionsTable objects and the StyleTable objects.

Note Regarding your getOrganization query (see comment)

@Query("SELECT * FROM OrgTable")
//OrgTable getOrganization(); /* <<<<<<<<<< WRONG should be a List */
List<OrgTable> getOrganizations();

And your insertOrg

@Insert
//void insertOrg(OrgTable org)
long insertOrg(OrgTable org); /* <<<<<<<<<< might as well allow the id of the inserted row to be obtained */

Ok. Now I have to insert these into the Room database through my repository for which I am facing a lot of difficulties.

And I believe that your OrgTable FK definitions should be as per :-

@Entity(foreignKeys = {@ForeignKey(entity = StyleTable.class, parentColumns =
        "styleId", childColumns = "stId"),
        @ForeignKey(entity = LoginOptionsTable.class, parentColumns =
                "loginOpnId", childColumns = "loginOptionsId")
})

• i.e the LoginOptions child and parent are the wrong way round and also that the parent should be loginOptionsId rather than loginOptionId (not s after Option).

Basic test

Using the following code as above except making the OrgDao class :-

@Dao
public interface OrgDAO {

    @Query("SELECT * FROM OrgTable")
    //OrgTable getOrganization(); /* <<<<<<<<<< WRONG should be a List */
    List<OrgTable> getOrganizations();

    @Query("SELECT * FROM OrgTable " +
            "JOIN StyleTable ON StyleTable.styleId = OrgTable.stId " +
            "JOIN LoginOptionsTable ON LoginOptionsTable.loginOpnId = OrgTable.loginOptionsId")
    List<OrgWithLoginAndStyle> getOrganizationLoginAndStyle();

    @Transaction
    @Query("SELECT * FROM OrgTable")
    List<OrganizationWithLoginOptionsAndWithStyles> getOrganizationsLoginsAndStyles();

    @Insert
    //void insertOrg(OrgTable org)
    long insertOrg(OrgTable org); /* <<<<<<<<<< might as well allow the id of the inserted row to be obtained */

    @Insert
    long insertStyle(StyleTable styleTable);

    @Insert
    long insertLoginOptions(LoginOptionsTable loginOptionsTable);
}

And using :-

@Database(entities = {OrgTable.class,StyleTable.class,LoginOptionsTable.class},version = 1) abstract class OrgLoginStyleDatabase extends RoomDatabase { abstract OrgDAO getOrgDao(); }

And with an activity (note that some of your code was commented out namely the use of @Parcelize and the @Ignored Concstructors for the Entities) :-

public class MainActivity extends AppCompatActivity {

    OrgLoginStyleDatabase db;
    OrgDAO dao;
    public static final String TAG = "OLSINFO";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = Room.databaseBuilder(this,OrgLoginStyleDatabase.class,"orgloginoptionsstyle.db")
                .allowMainThreadQueries()
                .build();
        dao = db.getOrgDao();

        StyleTable s1 = new StyleTable();
        s1.bannerBackgroundColor = "X";
        s1.bannerTextColor = "X";
        s1.baseTextSize = 20;
        s1.bodyBackgroundColor = "X";
        s1.bodyTextColor = "X";
        s1.buttonBackgroundColor = "X";
        s1.buttonTextColor = "X";
        s1.htmlWrapper = "X";
        s1.navigationBackgroundColor = "X";
        s1.navigationTextColor = "X";
        s1.styleId = 100;
        s1.topBarBackgroundColor = "X";
        s1.topBarLabel = "X";
        s1.topBarTextColor = "X";
        long s1Id = dao.insertStyle(s1);
        LoginOptionsTable l1 = new LoginOptionsTable();
        l1.allowedEmailDomain = "Y";
        l1.authorizationEndpointUri = "Y";
        l1.clientId = "Y";
        l1.clientSecret = "Y";
        l1.description = "Y";
        l1.loginOpnId = 1000;
        l1.name = "Y";
        l1.nonce = "Y";
        l1.redirectUri = "Y";
        l1.restrictedEmailDomain = "Y";
        l1.state = "Y";
        l1.title = "Y";
        l1.url = "Y";
        long l1Id = dao.insertLoginOptions(l1);
        OrgTable o1 = new OrgTable();
        o1.description = "Z";
        o1.id = 10000;
        o1.loginOptionsId = l1Id;
        o1.stId = s1Id;
        dao.insertOrg(o1);
        List<OrgTable> orgTableList = dao.getOrganizations();
        for(OrgTable o: orgTableList) {
            logOrgTable(o,"FROM getOrganizations -> ");
        }
        List<OrganizationWithLoginOptionsAndWithStyles> organizationsLoginsAndStylesList = dao.getOrganizationsLoginsAndStyles();
        for(OrganizationWithLoginOptionsAndWithStyles owloaws: organizationsLoginsAndStylesList) {
            logOrgTable(owloaws.orgTable,"FROM (@Relations) getOrganizationsLoginsAndStyles -> ");
            for(LoginOptionsTable lot: owloaws.loginOptionsTables) {
                logLoginOptionsTable(lot,"\t");
            }
            for(StyleTable s: owloaws.styleTables) {
                logStyleTable(s,"\t");
            }
        }
        List<OrgWithLoginAndStyle> owlas = dao.getOrganizationLoginAndStyle();
        for(OrgWithLoginAndStyle o: owlas) {
            logOrgTable(o.orgTable,"FROM (@Embeddeds) getOrganizationLoginAndStyle -> ");
            logLoginOptionsTable(o.loginOptionsTable,"\t");
            logStyleTable(o.styleTable,"\t");
        }
    }

    private void logOrgTable(OrgTable o,String preamble) {
        Log.d(TAG,preamble + "OrgTable Description = " + o.description + " ID = " + o.id);
    }
    private void logStyleTable(StyleTable s, String preamble) {
        Log.d(TAG,preamble + "StyleTable Description = " + s.topBarTextColor + " ID =" + s.styleId);
    }
    private void logLoginOptionsTable(LoginOptionsTable l, String preamble) {
        Log.d(TAG,preamble + "LoginOptionsTable Description = " + l.description + " ID = " + l.loginOpnId);
    }
}

Result

When run (will only run the once as hard coded ID's have been used) the result output to the log was :-

2021-04-12 21:51:50.981 D/OLSINFO: FROM getOrganizations -> OrgTable Description = Z ID = 10000

2021-04-12 21:51:50.987 D/OLSINFO: FROM (@Relations) getOrganizationsLoginsAndStyles -> OrgTable Description = Z ID = 10000
2021-04-12 21:51:50.987 D/OLSINFO:  LoginOptionsTable Description = Y ID = 1000
2021-04-12 21:51:50.987 D/OLSINFO:  StyleTable Description = X ID =100

2021-04-12 21:51:50.989 D/OLSINFO: FROM (@Embeddeds) getOrganizationLoginAndStyle -> OrgTable Description = Z ID = 10000
2021-04-12 21:51:50.989 D/OLSINFO:  LoginOptionsTable Description = Y ID = 1000
2021-04-12 21:51:50.989 D/OLSINFO:  StyleTable Description = X ID =100

• All three objects were inserted successfully and all three queries worked as expected.