How to insert multiple rows in a SQL Database

I’m developing a Recipes Book and I’ve a problem saving multiple Ingredients for the same Recipe in the database. I can add, pressing a button, multiples Linear Layout with inside an ingredient EditText and a quantity EditText. So the for cycle evaluates each Layout, it takes the currents ingredient and quantity values saving them in the newIngredient instance (of the Ingredient.class). Then it inserts the instance in the database and finally, it adds the instance to my “ingredients” ArrayList and closes the DB. With the debug, I found out that all this works only for the first iteration of for cycle.

for (int d=0; d<countIngredients; d++) {
        View childViewIng = parentIngredientLayout.getChildAt(d);
        EditText childTextViewI = childViewIng.findViewById(R.id.ingredientsField);
        EditText childTextViewQ = childViewIng.findViewById(R.id.quantityField);
        childIngredient = childTextViewI.getText().toString();
        childQuantity = Integer.parseInt(childTextViewQ.getText().toString());
        newIngredient = new Ingredient(childIngredient, childQuantity);
        dbHelper.insertIngredient(newIngredient);
        ingredients.add(newIngredient);
        dbHelper.close();
    }

Answer

In your code you are closing the database inside the for loop.

For this reason, your code will execute the first iteration, then close the database and so the next iterations will fail due to a closed db connection.

You should move your dbHeloper.close(); call outside the loop.

In addiction, you can move your variable outside the for loop, for a better memory usage. In shorts:

Step 1: close database after the loop cycle

for (int d=0; d < countIngredients; d++) {
    View childViewIng = parentIngredientLayout.getChildAt(d);
    EditText childTextViewI = childViewIng.findViewById(R.id.ingredientsField);
    EditText childTextViewQ = childViewIng.findViewById(R.id.quantityField);
    childIngredient = childTextViewI.getText().toString();
    childQuantity = Integer.parseInt(childTextViewQ.getText().toString());
    newIngredient = new Ingredient(childIngredient, childQuantity);
    dbHelper.insertIngredient(newIngredient);
    ingredients.add(newIngredient);
}
//move close method here, outside loop
dbHelper.close();

Step 2: optimize variables

//move variables here, or wherever you want
View childViewIng = null;
EditText childTextViewI = null;
EditText childTextViewQ = null;

for (int d=0; d < countIngredients; d++) {
    //in this way you will create only 1 object, and reuse it every time
    childViewIng = parentIngredientLayout.getChildAt(d);
    childTextViewI = childViewIng.findViewById(R.id.ingredientsField);
    childTextViewQ = childViewIng.findViewById(R.id.quantityField);
    childIngredient = childTextViewI.getText().toString();
    childQuantity = Integer.parseInt(childTextViewQ.getText().toString());
    newIngredient = new Ingredient(childIngredient, childQuantity);
    dbHelper.insertIngredient(newIngredient);
    ingredients.add(newIngredient);
}
dbHelper.close();

Hope this helps!