Insert data into Database - What is the best way to do it

Question

I have to insert anywhere between 50 - 500 contact's information into the database. I have 4 arraylists that contain image, name, number, bool variable respectively. Each row in the data is made up of a combination of all the 4 arraylists along with a SNO. Please refer to the image below. My question is, let's say i have 500 contacts that I retrieve from the User's Contacts list. Is it a good thing that, I have a function that inserts each row at a time into the table and call it 500 times? or is there any other way? A mean idea would be to combine all the arraylists together, pass it to the function and retrieve the data there and repeat the insert statement 500 times. What is better in terms of performance? ``` for(int i =0; i < 500; i++) { dbObj.insert_row(par1, par2, par3, par4, ...); } ``` OR ``` function insert_row(Combined ArrayLists) { for(int i=0; i<500; i++) { db.execSql(//Insert Statement); } } ```

Accepted Answer

I suggest you to create own object that will represent your table where properties of object will be equal to columns in table, e.q.

```
public class Contact {

private String name;
   private String number;
   private String image;
   private boolean conn;

//getters and setters
}
```

Now your approach sounds like "spaghetti code". There is not need to have four ArrayLists and this design is not efficient and correct.

Now, you will have one ArrayList of Contact object with 500 childs.

What is the best way to insert?

For sure wrap your insertion to one `TRANSACTION` that speed up your inserts rapidly and what is the main your dealing with database becomes much more safer and then you don't need to care about possibility of losing database integrity.

Example of transaction(one method from my personal example project):

```
public boolean insertTransaction(int count) throws SQLException {
    boolean result = false;
    try {
        db = openWrite(DataSource.getInstance(mContext));
        ContentValues values = new ContentValues();
        if (db != null) {
            db.beginTransaction();
            for (int i = 0; i < count; i++) {
                values.put(SQLConstants.KEY_TYPE, "type" + i);
                values.put(SQLConstants.KEY_DATE, new Date().toString());
                db.insertOrThrow(SQLConstants.TEST_TABLE_NAME, SQLConstants.KEY_TYPE, values);
                values.clear();
            }
            db.setTransactionSuccessful();
            result = true;
        }
        return result;
    }
    finally {
        if (db != null) {
            db.endTransaction();
        }
        close(db);
    }
}
```

Insert data into Database - What is the best way to do it

Solution

Problem

Related problems