How to insert multiple rows in a SQL Database

Question

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();
    }

try moving dbHelper.close(); outside for loop

Firdous nath
– Firdous nath

2019-03-27 10:58:00 +00:00
Commented Mar 27, 2019 at 10:58 — Firdous nath
– Firdous nath, Commented Mar 27, 2019 at 10:58
Do you get any database already closed exception?

Nicola Gallazzi
– Nicola Gallazzi

2019-03-27 11:01:19 +00:00
Commented Mar 27, 2019 at 11:01 — Nicola Gallazzi
– Nicola Gallazzi, Commented Mar 27, 2019 at 11:01

Pier Giorgio Misley · Accepted Answer · 2019-03-27 11:07:45Z

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!

Collectives™ on Stack Overflow

How to insert multiple rows in a SQL Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related