Insert data to sqlite database from json parsing

Question

I want to insert data to sqLite database in Android data parsing form json array data.

My code is as follow:

1) DBHelperClass - database creation

public class DueAmountDataBHelper extends SQLiteOpenHelper {

    public DueAmountDataBHelper(Context context) {
        super(context, "abc.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE_PRODUCT_DUE_AMT =
                "create table due_amt_tab(" +
                        "shopId text primary key, " +
                        "shopName text NOT NULL, " +
                        "teluguName text NOT NULL, " +
                        "place text NOT NULL, " +
                        "dueAmount text NOT NULL " +
                        ")";
        db.execSQL(CREATE_TABLE_PRODUCT_DUE_AMT);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public List<DueAmtDBModel> getShopdata() {
        List<DueAmtDBModel> data = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from due_amt_tab", null);
        StringBuffer stringBuffer = new StringBuffer();
        DueAmtDBModel dataModel = null;
        while (cursor.moveToNext()) {
            dataModel = new DueAmtDBModel();
            String shopId, shopName, teluguName, place, dueAmount;

            shopId = cursor.getString(cursor.getColumnIndexOrThrow("shopId"));
            shopName = cursor.getString(cursor.getColumnIndexOrThrow("shopName"));
            teluguName = cursor.getString(cursor.getColumnIndexOrThrow("teluguName"));
            place = cursor.getString(cursor.getColumnIndexOrThrow("place"));
            dueAmount = cursor.getString(cursor.getColumnIndexOrThrow("dueAmount"));

            dataModel.setShopId(shopId);
            dataModel.setShopName(shopName);
            dataModel.setTeluguName(teluguName);
            dataModel.setPlace(place);
            dataModel.setDueAmount(dueAmount);

            stringBuffer.append(dataModel);
            data.add(dataModel);
        }
        return data;
    }
}

to this table i need to insert this json data
APi - http://demo4896782.mockable.io/shops

[
  {
    "shopName": "Hello World.",
    "shopTeluguName": "శరవాన గుడ్డు పంపిణీదారులు",
    "shopAddress": "Bomanahalli",
    "previousDues": 0,
    "shopID": 1
},
{
    "shopName": "Hello World.",
    "shopTeluguName": "శరవాన గుడ్డు పంపిణీదారులు",
    "shopAddress": "Bomanahalli",
    "previousDues": 20,
    "shopID": 2
},
{
    "shopName": "Hello World.",
    "shopTeluguName": "శరవాన గుడ్డు పంపిణీదారులు",
    "shopAddress": "Bomanahalli",
    "previousDues": 400,
    "shopID": 3
}
]

Thank you in advance.

Answer 1

The code below assumes you can parse the json data from the server.

public void insert(JsonObject jsonObject){
    ContentValues values = new ContentValues();
    SQLiteDatabase db = this.getWritableDatabase();
    values.put("shopName", jsonObject.getString('Hello World'));
    values.put("shopTeluguName", jsonObject.getString('shopTeluguName'));
    values.put("shopAddress", jsonObject.getString('shopAddress'));
    values.put("previousDues", jsonObject.getString('previousDues'));
    values.put("shopID", jsonObject.getString('shopID'));

    db.insert("YOUR TABLE NAME", null, values);
}

Now simply iterate over the JSON Array and call this function in the loop

Answer 2

First of all, the table structure should be:

CREATE TABLE IF NOT EXISTS "TABLE_NAME" + "(" + JSON_STRING_KEY + " TEXT ")

Now, make a model class for the json-array you're getting from api.

Now, to insert this json-array in the table, you can do something like this:

 void insertJsonArrayAsStringToTable(ArrayList<Model> modelList) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME, null, null); // delete previous data
    ContentValues contentValues = new ContentValues();
   // array list as a string; or you can directly put the string you got from 
   // onResponse(String response) of your volley StringRequest--if you're using it
    contentValues.put(JSON_STRING_KEY, new Gson().toJsonTree(modelList).toString());  
    db.insert(TABLE_NAME, null, contentValues);
    db.close();
}

Until now, we have the json-array data from the api stored in the table as a string.

Now, we can use a query to retrieve the string from table and again use Gson to convert it into an object( here, an ArrayList of Model):

 public ArrayList<Model> loadData() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery("SELECT * FROM " + TABLE_NAME, null); // now where-clause, so we simply use null as an argument
    res.moveToFirst();
    ArrayList<Model> modelList= null;
    while (!res.isAfterLast()) { // traverse the table
        Type listType = new TypeToken<ArrayList<Model>>() {
        }.getType();
        modelList= new Gson().fromJson(res.getString(res.getColumnIndex(JSON_STRING_KEY)), listType);
        res.moveToNext();
    }
    res.close();
    db.close();
    return modelList;
}

PS: How you manage to store other possible responses coming from api into the db, totally depends on you, whether make separate tables or something like that.