Multiple input or replacement query in Android SQLite

Question

I use the query below to insert or replace the “number” and “time” in the “Contacts” table. Is there a way to insert or replace multiple records in SQLite for Android?

"Insert or replace into Contacts (_id, number, status) values ((select _id from Contacts where number = '123456'), '123456','sent');" 

Answer 1

You can improve speed for a multiple / batch database. Insert or replace an operation using the concept of transaction and compileStatement so that your request is compiled only once.

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

Answer 2

Try this code to insert multiple records.

 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 create a model class to specify 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; } } 

Answer 3

Not sure if I understand the request, but it looks like you can just update instead of inserting or replacing (the choice will return null if the number is not found)

 Update Contacts set status = 'sent' where number in (...)