How to Populate values from Array into SQLite Database

Question

my App receives user input data via one EditText field and adds to an Array. The App then passes the Array via databasehelper controller to the Helper class using the code

controller.populateStockMaterialDB(InventoryData); 

In my Database helper I have the following code to read the array and populate the database table with 6 values from the Array

public void populateStockMaterialDB(ArrayList<String> inventoryData) {
        SQLiteDatabase db = this.getWritableDatabase();

        for (int i=0; i< inventoryData.size(); i++) {
         ContentValues contentValues = new ContentValues();
            contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_MATERIAL, inventoryData.get(i));
            contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_UOM_STORE, inventoryData.get(i));
            contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_UOM_AVAILABLE, inventoryData.get(i));
            contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_QUANTITY_STORE, inventoryData.get(i));
            contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_QUANTITY_AVAILABLLE, inventoryData.get(i));
            contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_MOQ, inventoryData.get(i));

            this.getWritableDatabase().insertOrThrow(FoodoclockContract.FoodolockEntry.TABLE_NAME7, "", contentValues);
        }
    }

The Array data is populated into the table but not correctly. Instead of having one row with 6 columns populated with data, I have 6 rows with 6 columns populated with data. I would appreciate some help with my code. thank you

Answer 1

You are looping through each item in the array and filling all the columns, so you have an equal number of rows and an equal number columns which would be equal to the length of the array.

What you should do is this, remove the loop and write to each column using the index manually

public void populateStockMaterialDB(ArrayList<String> inventoryData) {
    SQLiteDatabase db = this.getWritableDatabase();

     ContentValues contentValues = new ContentValues();
        contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_MATERIAL, inventoryData.get(1));
        contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_UOM_STORE, inventoryData.get(2));
        contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_UOM_AVAILABLE, inventoryData.get(3));
        contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_QUANTITY_STORE, inventoryData.get(4));
        contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_QUANTITY_AVAILABLLE, inventoryData.get(5));
        contentValues.put(FoodoclockContract.FoodolockEntry.COLUMN_MOQ, inventoryData.get(6));

        this.getWritableDatabase().insertOrThrow(FoodoclockContract.FoodolockEntry.TABLE_NAME7, "", contentValues);
}

that should fix your problem. But a better way to approach this would be to use a model to hold the value for each column in your database and then just map to the corresponding data, just have a class that has string fields for each property you want to save in your database and then use the value each field to populate the db.