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.

enter image description here

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

First of all, replace your 4 ArrayLists by 1 ArrayList of a custom class type. You can build an `INSERT` query to insert many rows: http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql — Sotirios Delimanolis, Apr 12 '13 at 18:09

Simon Dorociak · Accepted Answer · 2013-04-12T18:24:35.217

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

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

Thanks a lot for your input. Will accept your answer when the waiting period is done.. — Vamsi Challa, Apr 12 '13 at 18:17

codester · Answer 2 · 2013-04-12T18:29:32.573

Converting 4 arrays into one object array makes the code better. but you can create these objects without doing it like this.

Prepare the sql statement with bind variables (? or :vars), then execute the statement multiple times in a loop by setting the bind variables for each row.

String sql = "insert into..... values (?,?,?,?)";
// Get connection etc
PreparedStatement stmt = conn.prepareStatement(sql);
for(int i =0; i < 500; i++)
{
   stmt.setString(1, name.get(i));
   stmt.setNumber(2, number.get(i));
   ...

   stmt.executeUpdate();
}

score 0 · Answer 3 · answered Apr 12 '13 at 18:16

0

If you are going to insert 500 records into database you should use transaction

database.beginTransaction();
try {
     // perform inserts
     database.setTransactionSuccessful();
finally {
     database.endTranasction();
}

answered Apr 12 '13 at 18:16

Mike

819
1
8
14

What is the scope of the transaction? Should all inserts fail if a single one does? – Sotirios Delimanolis Apr 12 '13 at 18:19
This is how the transaction works, if one insert failed all inserts will be canceled. Or everything is performed successfully or nothing. – Mike Apr 12 '13 at 18:21
I know that, I'm asking you if that is the correct logic. How are the inserts related? I would think each one is independent. – Sotirios Delimanolis Apr 12 '13 at 18:22
Usage of transaction will guarantee data consistency what is important. – Mike Apr 12 '13 at 18:34
What I'm getting at is the transaction is unnecessary here. He is inserting 500 different rows. If one insert doesn't go through (duplicate key, other violated constraint, etc.) it shouldn't affect the other inserts. But with this setup, you will rollback and cancel all inserts if one fails. Plus, the insert in this case is already ACID (he hasn't shown any evidence that an insert will affect anything else) – Sotirios Delimanolis Apr 12 '13 at 18:36

score 0 · Answer 4 · edited May 23 '17 at 12:31

As mentioned before create Your own class to represent one row or use ContentValues class SQlite doesn't provide possibility to insert many rows in one query like in MySQL but there is some way around it You can read here.

If You decide to use method described in this link it is better if You create a function to generate this query and run it just once. Otherwise As others mentioned already you may use transaction to improve a performance of many inserts.

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

4 Answers4