Android: Inserting batch data (one time) using SQLite

Question

below is the code, currently i am using, but i am looking a way to speed up my insert and i have 100+ rows inserted on very first time... any thoughts?

public class SQLiteAdapter {

private SQLiteHelper sqLiteHelper;
private SQLiteDatabase sqLiteDatabase;
private Context context;

public SQLiteAdapter(Context c){
    context = c;
}

   public long insert(String empId, String name......)
   {
      ContentValues contentValues = new ContentValues();
      contentValues.put(KEY_EMP_ID, empId);
      .....................
      .....................
      .....................
      return sqLiteDatabase.insert(KEY_TABLE, null, contentValues); 
   }


   public void InsertReciters()
   {
     //currently, this is how i am doing, how can i speed up inserting the rows in to db?
    //i have 100+ rows ..........

     this.insert("ac123", ................);
     this.insert("ac133", ................);
     this.insert("ac143", ................);
     this.insert("ac153", ................);
     this.insert("ac163", ................);
     .................
     ................. 
   }  
}

You can use pre-populated database: http://stackoverflow.com/a/9109728/265167 — Yaqub Ahmad, Mar 27 '12 at 06:44
prepared statements and transactions: http://stackoverflow.com/a/29797229/3681880 — Suragch, Apr 27 '15 at 08:15

boiledwater · Answer 1 · 2012-03-27T04:12:43.700

Suggest you insert huge data by transaction.

See below code format:

List<String[]> hugeData=new ArrayList<String[]>();
try{
   sqLiteDatabase.beginTransaction();
    //insert huge data
    //get pre-compiled SQLiteStatement object
    SQLiteStatement statement=sqLiteDatabase.compileStatement("insert into tablename(..) value (?,?)")   
    for(String[] row:hugeData){
      statement.bindString(1,row[0]);
      //......
      statement.execute();
    }
   sqLiteDatabase.setTransactionSuccessful();
}finally{
  sqLiteDatabase..endTransaction();
}

---------------------edited------------------

public class SQLiteAdapter {

private SQLiteHelper sqLiteHelper;
private SQLiteDatabase sqLiteDatabase;
private Context context;
List<String[]> insertData=null;

public SQLiteAdapter(Context c){
    context = c;
}
 public SQLiteAdapter(Context c,List<String[]> _insertData){
    this(c);
    insertData=_insertData;
}
   public long insert(String empId, String name......)
   {
      ContentValues contentValues = new ContentValues();
      contentValues.put(KEY_EMP_ID, empId);
      .....................
      .....................
      .....................
      return sqLiteDatabase.insert(KEY_TABLE, null, contentValues); 
   }


   public void InsertReciters()
   {
     //currently, this is how i am doing, how can i speed up inserting the rows in to db?
    //i have 100+ rows ..........

     this.insert("ac123", ................);
     this.insert("ac133", ................);
     this.insert("ac143", ................);
     this.insert("ac153", ................);
     this.insert("ac163", ................);
     .................
     ................. 
   }  

  public void InsertBatchData()
   {
    try{
   sqLiteDatabase.beginTransaction();
    //insert huge data
    //get pre-compiled SQLiteStatement object
    SQLiteStatement statement=sqLiteDatabase.compileStatement("insert into tablename(..) value (?,?)")   
    for(String[] row:hugeData){
      statement.bindString(1,row[0]);
      //......
      statement.execute();
    }
   sqLiteDatabase.setTransactionSuccessful();
   }finally{
    sqLiteDatabase..endTransaction();
   }
   }
}

i dont understand the `....compileStatement("insert....` i have like 100+ rows ... — Nick Kahn, Mar 27 '12 at 03:30
That return pre-compiled SQLiteStatement object. This object can then be used to efficiently execute this statement multiple times. — boiledwater, Mar 27 '12 at 03:37
okay so i am confused in the `value` what do you want me to defined enter the values for the row and then in the for loop statement? can you use my code to demonstrate... may be i am bit confused with the way you used your insert statement. — Nick Kahn, Mar 27 '12 at 03:52
First, if you want to insert huge data, you must have the huge data object that will insert database. May be you can use List type object to store huge data. Your can loop List object to insert data. — boiledwater, Mar 27 '12 at 04:08

Android: Inserting batch data (one time) using SQLite

1 Answers1