How to save the below json data into two tables(model table and variant table) and get the data according to model

Question

I am facing issue with inserting data in sqlite. I am using room for this. I need to join the table to get the data. below is the json data which I need to insert data

[{
    "ImgPath": "",
    "ModelCode": "Model 1",
    "ModelDescription": "Model 1",
    "ModelID": "1",
    "VarientList": [{
        "MDescription": "APOLLO BLU",
        "MDescriptionLong": "APOLLO BLU",
        "MaterialID": "1",
        "MaterialImgPath": "",
        "Mcode": "APOLOL/CRSP/BLU",
        "ModelColor": ""
    }, {
        "MDescription": "APOLLO BLK",
        "MDescriptionLong": "APOLLO BLK",
        "MaterialID": "2",
        "MaterialImgPath": "",
        "Mcode": "APOLOL/CRSP/BLK",
        "ModelColor": ""
    }]
}, {
    "ImgPath": "",
    "ModelCode": "Model 2",
    "ModelDescription": "Model 2",
    "ModelID": "2",
    "VarientList": [{
        "MDescription": "SACE BROWN",
        "MDescriptionLong": "SACE BROWN",
        "MaterialID": "3",
        "MaterialImgPath": "",
        "Mcode": "SACE/BRN",
        "ModelColor": ""
    }, {
        "MDescription": "SACE BLU",
        "MDescriptionLong": "SACE BLU",
        "MaterialID": "4",
        "MaterialImgPath": "",
        "Mcode": "SACE/BLU",
        "ModelColor": ""
    }]
}]

I have taken this as reference to do this https://android.jlelse.eu/android-architecture-components-room-relationships-bf473510c14a

but I am a bit confused to insert and get the data from join table. What are the attributes will be the foreign keys and how to write the jointable for this.

//This is my Item table constructor

public ItemTable(String modelID, String modelCode, String modelDescription, String imgPath) {
        this.modelID = modelID;
        this.modelCode = modelCode;
        this.modelDescription = modelDescription;
        this.imgPath = imgPath;

    }

//This is my Variant table constructor

public VariantTable(String MDescription, String MDescriptionLong, String MaterialID, String imgPath,, String Mcode,String Mcode) {
        this.MDescription = MDescription;
        this.MDescriptionLong = MDescriptionLong;
        this.MaterialID = MaterialID;
        this.Mcode = Mcode;
    this.ModelColor = ModelColor;

    }

I want to insert model in one table and variants of that model in second table.And I need to show the variant details as per the model. For example, If I have selected Model1 then the variants of the model1 has to come in a drop down. how to show like that, how can i do this?

I am confused to insert data using join table. what will be my join table and how to get the variants of a selected model — Padmaja Rani, Sep 18 '19 at 11:01
I think this will helps you - https://stackoverflow.com/a/47920930/10989990 — Priyanka, Sep 18 '19 at 11:11
for Join you need to keep primary foriegn key relation, i guess in variant table you need to add model ID as well , then you can join both the table using model id — Neha Rathore, Sep 18 '19 at 11:12
@NehaRathore can u please describe more with an example , as I am new to room and this join concept — Padmaja Rani, Sep 18 '19 at 11:24
see my answer, where you can get some fields from model table and also all the variants associated with the same modelID — Priyanka, Sep 18 '19 at 11:52

Priyanka · Answer 1 · 2019-09-18T12:46:05.237

this will help you to get data from DB

@Query("SELECT model.ModelCode, model.ModelID, model.ModelDescription, varient.MDescription as MDescription " +
            "FROM model " +
            "LEFT JOIN varient ON model.ModelID = varient.ModelID")
    List getModelsWithVarient();

EDIT

first of all create model for your json

@Entity
public class MyModel {

    @PrimaryKey
    private String modelID;
    private String imgPath;
    private String modelCode;
    private String modelDescription;

    @Ignore
    private List<VarientList> varientList = null;

    public String getImgPath() {
        return imgPath;
    }

    public void setImgPath(String imgPath) {
        this.imgPath = imgPath;
    }

    public String getModelCode() {
        return modelCode;
    }

    public void setModelCode(String modelCode) {
        this.modelCode = modelCode;
    }

    public String getModelDescription() {
        return modelDescription;
    }

    public void setModelDescription(String modelDescription) {
        this.modelDescription = modelDescription;
    }

    public String getModelID() {
        return modelID;
    }

    public void setModelID(String modelID) {
        this.modelID = modelID;
    }

    public List<VarientList> getVarientList() {
        return varientList;
    }

    public void setVarientList(List<VarientList> varientList) {
        this.varientList = varientList;
    }

}

and one model for Varient

@Entity(foreignKeys = @ForeignKey(entity = MyModel.class, parentColumns = "modelID", childColumns = "id"))
public class VarientList {

    @PrimaryKey(autoGenerate = true)
    int id;

    String modelID;

    private String mDescription;
    private String mDescriptionLong;
    private String materialID;
    private String materialImgPath;
    private String mcode;
    private String modelColor;

    public String getMDescription() {
        return mDescription;
    }

    public void setMDescription(String mDescription) {
        this.mDescription = mDescription;
    }

    public String getMDescriptionLong() {
        return mDescriptionLong;
    }

    public void setMDescriptionLong(String mDescriptionLong) {
        this.mDescriptionLong = mDescriptionLong;
    }

    public String getMaterialID() {
        return materialID;
    }

    public void setMaterialID(String materialID) {
        this.materialID = materialID;
    }

    public String getMaterialImgPath() {
        return materialImgPath;
    }

    public void setMaterialImgPath(String materialImgPath) {
        this.materialImgPath = materialImgPath;
    }

    public String getMcode() {
        return mcode;
    }

    public void setMcode(String mcode) {
        this.mcode = mcode;
    }

    public String getModelColor() {
        return modelColor;
    }

    public void setModelColor(String modelColor) {
        this.modelColor = modelColor;
    }

}

now make one DAO interface to perform all the queries on db

@Dao
public interface MyModelDao {

    @Insert
    void insert(MyModel myModel);

    @Query("SELECT * FROM MyModel WHERE modelID=:modelID")
    List<MyModel> findAllModels(final int modelID);

    @Query("SELECT MyModel.ModelCode, " +
        "MyModel.ModelID, " +
        "MyModel.ModelDescription, " +
        "MyModel.imgPath, " +
        "VarientList.MDescription as MDescription, " +
        "VarientList.mDescriptionLong as mDescriptionLong, " +
        "VarientList.materialID as materialID, " +
        "VarientList.materialImgPath as materialImgPath, " +
        "VarientList.mcode as mcode, " +
        "VarientList.modelColor as modelColor " +
        "FROM model " +
        "LEFT JOIN VarientList ON MyModel.ModelID = VarientList.ModelID")
    List<MyModel> getModelsWithVarientList();
}

now call insert method to insert record and pass the complete model into it.

to get all the varients from the model call getModelsWithVarientList() this will retruns all the models stored in db

for more you can also refer this link - https://stackoverflow.com/questions/44667160/android-room-insert-relation-entities-using-room — Priyanka, Sep 18 '19 at 12:56
thanks for the help will try it and let you know once I done with it. — Padmaja Rani, Sep 18 '19 at 13:11

How to save the below json data into two tables(model table and variant table) and get the data according to model

1 Answers1