Bulk Insertion on Android Device

Bulk Insertion on Android device

I don't believe there is any feasible way to accomplish #3 or #4 on your list.

Of the other solutions you list two that have the datafile contain direct SQL, and the other has the data in a non-SQL format.

All three would work just fine, but the latter suggestion of grabbing the data from a formatted file and building the SQL yourself seems the cleanest. If true batch update capability is added at a later date your datafile is still usable, or at least easily processable into a usable form. Also, creation of the datafile is more straightforward and less error prone. Finally, having the "raw" data would allow import into other data-store formats.

In any case, you should (as you mentioned) wrap the groups of inserts into transactions to avoid the per-row transaction journal creation.

how to bulk insert in sqlite in android

Use a transaction to insert all the rows -- NOT one row per transaction.

SQLiteDatabase db = ...
db.beginTransaction();
try {
    // do ALL your inserts here
    db.setTransactionSuccessful()
} finally {
    db.endTransaction();
}

EDIT

public void add_cities(List<Cities> list) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues values = new ContentValues();
        for (Cities city : list) {
            values.put(CityId, city.getCityid());
            values.put(CityName, city.getCityName());
            db.insert(TABLE_CITY, null, values);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

ALL inserts, ONE transaction.

How to use bulkInsert() function in android?

This is bulkInsert using ContentProvider.

public int bulkInsert(Uri uri, ContentValues[] values){
    int numInserted = 0;
    String table;

    int uriType = sURIMatcher.match(uri);

    switch (uriType) {
    case PEOPLE:
        table = TABLE_PEOPLE;
        break;
    }
    SQLiteDatabase sqlDB = database.getWritableDatabase();
    sqlDB.beginTransaction();
    try {
        for (ContentValues cv : values) {
            long newID = sqlDB.insertOrThrow(table, null, cv);
            if (newID <= 0) {
                throw new SQLException("Failed to insert row into " + uri);
            }
        }
        sqlDB.setTransactionSuccessful();
        getContext().getContentResolver().notifyChange(uri, null);
        numInserted = values.length;
    } finally {         
        sqlDB.endTransaction();
    }
    return numInserted;
}

Call it only once when you will have more ContentValues in ContentValues[] values array.

Android: Bulk Insert, when InsertHelper is deprecated

SQLiteStatement has also binding methods, it extends SQLiteProgram.

Just run it in transaction:

    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    final SQLiteStatement statement = db.compileStatement(INSERT_QUERY);
    db.beginTransaction();
    try {
        for(MyBean bean : list){
            statement.clearBindings();
            statement.bindString(1, bean.getName());
            // rest of bindings
            statement.execute(); //or executeInsert() if id is needed
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }

---

EDIT

I can't find nice solution in SQLiteQueryBuilder but it's as simple as:

final static String INSERT_QUERY = createInsert(DbSchema.TABLE_NAME, new String[]{DbSchema.NAME, DbSchema.TITLE, DbSchema.PHONE});

static public String createInsert(final String tableName, final String[] columnNames) {
    if (tableName == null || columnNames == null || columnNames.length == 0) {
        throw new IllegalArgumentException();
    }
    final StringBuilder s = new StringBuilder();
    s.append("INSERT INTO ").append(tableName).append(" (");
    for (String column : columnNames) {
        s.append(column).append(" ,");
    }
    int length = s.length();
    s.delete(length - 2, length);
    s.append(") VALUES( ");
    for (int i = 0; i < columnNames.length; i++) {
        s.append(" ? ,");
    }
    length = s.length();
    s.delete(length - 2, length);
    s.append(")");
    return s.toString();
}

How to insert bulk data in android sqlite database using ormlite efficiently

I'm trying to insert 100000 records in android sqlite database at a time... On average they take 140 seconds and take 60-65% CPU which is not ok in my opinion.

Unfortunately I don't have an easy answer for you. You may have to do this sort of insert directly using raw SQL to achieve faster performance on the limited Android CPU. Once you have the data inserted then you can turn to ORMLite to query or manipulate the data faster.

Android: Add thousands of contacts in phonebook in bulk

Ok, so the good thing about your code is that you apply your ops in batches.
The not-optimal thing about your code is that your batches are very small, 4 ops each.

You can instead gather bigger batches (i would recommend around 500 a batch, but you can play with the number.

Here's some untested code:

private static final int BATCH_SIZE = 500;

private void addThousandContacts() {
    ArrayList<ContentProviderOperation> ops = new ArrayList<>();
    for (int i = 0; i < 1000; i++) {
        addSingleContact(ops);

        if (ops.size() >= BATCH_SIZE) {
            try {
                contentResolver.applyBatch(ContactsContract.AUTHORITY, ops);
                ops.clear(); // remove all applied operations and start a new batch
            } catch (RemoteException e) {
                e.printStackTrace();
            } catch (OperationApplicationException e) {
                e.printStackTrace();
            }
        }
    }
}

private void addSingleContact(ArrayList<ContentProviderOperation> ops) {
    int rawInsertIndex = ops.size();

    // Adding insert operation to operations list
    // to insert a new raw contact in the table ContactsContract.RawContacts
    ops.add(ContentProviderOperation.newInsert(ContactsContract.RawContacts.CONTENT_URI)
            .withValue(ContactsContract.RawContacts.ACCOUNT_TYPE, null)
            .withValue(ContactsContract.RawContacts.ACCOUNT_NAME, null)
            .build());

    // Adding insert operation to operations list
    // to insert display name in the table ContactsContract.Data
    ops.add(ContentProviderOperation.newInsert(ContactsContract.Data.CONTENT_URI)
            .withValueBackReference(ContactsContract.Data.RAW_CONTACT_ID, rawInsertIndex) // tells the system the index of the operation that contains the current RawContactId
            .withValue(ContactsContract.Data.MIMETYPE, ContactsContract.CommonDataKinds.StructuredName.CONTENT_ITEM_TYPE)
            .withValue(ContactsContract.CommonDataKinds.StructuredName.DISPLAY_NAME, contact.getContactName())
            .build());

    ... // add more operations email, phone, etc.
}

---

Related Topics