Blog Archive

Android SQLite bulk insert and update example

How to efficiently insert or update a lot of data in Android SQLite database

Database transactions are slow and in situations where there is need for thousands of records have to be inserted, inserting each record takes a lot of time and valuable resources. In such scenarios, batch insert or updates can speed up the process.

Here is simple SQLite Item database to display batch insert(or update)

    public static final String KEY_ROWID = "_id";
    public static final String KEY_ITEM = "itemNumber";
    public static final String KEY_DESC = "description";
    public static final String KEY_UOM = "unitOfMeasure";
    public static final String KEY_WGHT = "weight";

private static final String DATABASE_CREATE =
        "create table Items (_id integer PRIMARY KEY autoincrement, "
        + "itemNumber text not null, description text not null, unitOfMeasure text, weight numeric, UNIQUE (itemNumber));";

Example for a single Item Insert

public long createItem(String itemNumber, String description) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_ITEM, itemNumber);
        initialValues.put(KEY_DESC, description);

        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

Example for a single Item Update

public boolean updateItem(long rowId, String itemNumber, String description) {
        ContentValues args = new ContentValues();
        args.put(KEY_ITEM, itemNumber);
        args.put(KEY_DESC, description);

        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }

SQLite provides methods in SQLiteDatabase class can be used to make all the insert calls in the same batch in a single transaction. Start a transaction by calling the beginTransaction() method. Perform the database operations and then call the setTransactionSuccessful() to commit the transaction. Once the transaction is complete call the endTransaction() function.
  • beginTransaction();
  • compileStatement(String sql)
  • setTransactionSuccessful();
  • endTransaction();

public void beginTransaction ()

Begins a transaction in EXCLUSIVE mode.

Transactions can be nested. When the outer transaction is ended all of the work done in that transaction and all of the nested transactions will be committed or rolled back. The changes will be rolled back if any transaction is ended without being marked as clean (by calling setTransactionSuccessful). Otherwise they will be committed.

Here is the standard idiom for transactions:

db.beginTransaction();
try {
...
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}


public SQLiteStatement compileStatement (String sql)

Compiles an SQL statement into a reusable pre-compiled statement object. Faster when used over and over.

Sample code doing bulk insert after parsing an XML file

public ItemMaster loadBulkData(String content){
        
        ItemMaster lastItem = null;
        
        try {

            
            Log.w(TAG, "Start");
            /** Handling XML */
            SAXParserFactory spf = SAXParserFactory.newInstance();
            SAXParser sp = spf.newSAXParser();
            XMLReader xr = sp.getXMLReader();

            ItemXMLHandler myXMLHandler = new ItemXMLHandler();
            xr.setContentHandler(myXMLHandler);
            InputSource inStream = new InputSource();
            Log.w(TAG, "Parse1");
            inStream.setCharacterStream(new StringReader(content));
            Log.w(TAG, "Parse2");
            xr.parse(inStream);
            Log.w(TAG, "Parse3");
            
            mDb.beginTransaction();
            String sql = "Insert or Replace into Items (itemNumber, description,unitOfMeasure, weight) values(?,?,?,?)";
            SQLiteStatement insert = mDb.compileStatement(sql);
            ArrayList<ItemMaster> itemsList = myXMLHandler.getItemsList();
            for(int i=0;i<itemsList.size();i++){
                ItemMaster item = itemsList.get(i);
                if(i == (numberOfRows-1)){
                    lastItem = item;
                }
                insert.bindString(1, item.getItemNumber());
                insert.bindString(2, item.getItemDescription1());
                insert.bindString(3, item.getSellingUOM());
                insert.bindDouble(4, item.getWeight());
                insert.execute();
            }
            mDb.setTransactionSuccessful();
            Log.w(TAG, "Done");
        }
        catch (Exception e) {
            Log.w("XML:",e );
        }
        finally {
            mDb.endTransaction();
        }       
        
        return lastItem;

    }

Source code for ItemMaster.java

package com.mysample.ordering.util;

public class ItemMaster {
   
    String itemNumber = null;
    String itemDescription1 = null;
    String sellingUOM = null;
    double weight = 0;
    public String getItemNumber() {
        return itemNumber;
    }
    public void setItemNumber(String itemNumber) {
        this.itemNumber = itemNumber;
    }
    public String getItemDescription1() {
        return itemDescription1;
    }
    public void setItemDescription1(String itemDescription1) {
        this.itemDescription1 = itemDescription1;
    }
    public String getSellingUOM() {
        return sellingUOM;
    }
    public void setSellingUOM(String sellingUOM) {
        this.sellingUOM = sellingUOM;
    }
    public double getWeight() {
        return weight;
    }
    public void setWeight(double weight) {
        this.weight = weight;
    }
    

}