Inserting data into a database - what is the best way to do this

Question

I need to insert anywhere between 50 - 500 contact information into the database. I have 4 arraylists that contain image, name, number, bool variable respectively.

Each row of data consists of a combination of all 4 arraists along with SNO. See image below.

My question is: let's say I have 500 contacts that I extract from the user's contact list. Good thing I have a function that inserts each row at a time into a table and calls it 500 times? or is there another way? The average idea would be to bring all the arraists together, pass their functions on and get the data there and repeat the insert statement 500 times.

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

Answer 1

Paste data into a database - what is the best way to do it

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

 public class Contact { private String name; private String number; private String image; private boolean conn; //getters and setters } 

Now your approach sounds like a "spaghetti code." There is no need to have four ArrayLists, and this design is inefficient and correct.

You will now have one ArrayList object to contact with 500 children.

What is the best way to insert?

Surely wrap your insert in one TRANSACTION , which will speed up your insertions quickly and most importantly, your database business becomes much more secure, and then you do not need to worry about the possibility of losing the integrity of the database.

Transaction example (one of my personal projects):

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

Answer 2

Converting 4 arrays into one array of objects makes the code better. but you can create these objects without doing so.

Prepare the sql statement with the bind variables (? Or: vars), then execute the statement several times in the loop, 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(); } 

Answer 3

If you are going to insert 500 records into the database, you must use a transaction

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

Answer 4

As already mentioned, create your own class to represent a single row or use the ContentValues ​​class. SQlite does not provide the ability to insert many rows into a single query, as in MySQL, but there is some way. You can read it here.

If you decide to use the method described in this link, it is better if you create a function to create this request and run it only once. Otherwise, as already mentioned, you can use a transaction to improve the performance of many inserts.