-3

I am using below query to insert or replace "number" and "time" in "Contacts" table. Is there any way I can insert or replace multiple record in SQLite for Android?

"Insert or replace into Contacts (_id, number, status) values ((select _id from Contacts where number = '123456'), '123456','sent');"
user934820
  • 1,162
  • 3
  • 17
  • 48
  • http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database – Andrew Fielden Aug 12 '15 at 10:20
  • @Andrew Fielden, it is for Sqlite not MySql – tafia Aug 12 '15 at 10:24
  • @Andrew Fielden, I had already gone through the question but unable to adjust it properly with insert and replace. If you please can give an example of it with insert and replace? – user934820 Aug 12 '15 at 10:27

3 Answers3

1

You can improve speed for Multiple/Batch Database Inserting or Replacing operation using concept of transaction and compileStatement so your query will be compiled only once.

For Example:

    db.beginTransaction();
    try {
         String sql = "Insert or Replace into Items (itemNumber, description,unitOfMeasure, weight) values(?,?,?,?)";
         ArrayList<ItemMaster> itemsList = // Retrieve your items list here
         for(int i=0;i<itemsList.size();i++)
         {

             SQLiteStatement insert = db.compileStatement(sql);
             insert.bindString(1, item.getItemNumber());
             insert.bindString(2, item.getItemDescription1());
             insert.bindString(3, item.getSellingUOM());
             insert.bindDouble(4, item.getWeight());
             insert.execute();
         }
         db.setTransactionSuccessful();
    } finally {
      db.endTransaction();
    }
Durgesh Patel
  • 1,035
  • 8
  • 15
  • Thanks for your code Patel, but it is a very slow process if you dealing with 100 records for example. I am looking for any quick approach. – user934820 Aug 12 '15 at 10:28
  • No, I am sure it is not slow. I have done demo to insert 100 records using traditional insert v/s bulk insertion approach. traditional insert takes 1672 ms and bulk insertion takes only 92 ms. look at this http://www.techrepublic.com/blog/software-engineer/turbocharge-your-sqlite-inserts-on-android/ – Durgesh Patel Aug 12 '15 at 10:31
1

Try this code for insert multiple entries

public void insertIntoTable(ArrayList<YourModelClass> alist) {

        SQLiteDatabase db = this.getWritableDatabase();

        String sql = "insert into tableName (colomname1,colomnname2) values(?,?)";
        db.beginTransaction();
        SQLiteStatement stmt = db.compileStatement(sql);
        for (int i = 0; i < alist.size(); i++) {
            stmt.bindString(1, alist.get(i).getMethod1());
            stmt.bindString(2, alist.get(i).getMethod2());
            stmt.execute();
            stmt.clearBindings();
        }
        db.setTransactionSuccessful();
        db.endTransaction();

        db.close();
    }

Also Make a Model class to specify your variable names. eg:

class ModelClass {

String var1,var2;

public void setVar1(String var1)
{
this.var1=var1;
}

public String getVar1()
{
return var1;
}
public void setVar2(String var2)
{
this.var2=var2;
}

public String getVar2()
{
return var2;
}
}
userDroid
  • 198
  • 9
0

Not sure I understand the query, but it looks like you can just update instead of insert or replace (the select will return null if number is not found)

Update Contacts set status = 'sent' where number in (...)
tafia
  • 1,512
  • 9
  • 18