Android: Bulk Insert, when InsertHelper is deprecated

Question

There is plenty answers and tutorials using InsertHelper to do fast bulk insert in SQLiteDatabase.
But InsertHelper is deprecated as of API 17.

What is now the fastest method to bulk insert large sets of data in Android SQLite ?

So far my greatest concern is that SQLiteStatement is not very comfortable to work with, where InsertHelper had binding columns and binding values, which was kind of trivial.

pawelzieba · Accepted Answer · 2013-02-12T19:15:14.543

26

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

edited Feb 12 '13 at 19:15

answered Jan 15 '13 at 19:00

pawelzieba

16,082
3
46
72

this is great, but `INSERT_QUERY` must be plain-text written query with content-values placeholders `?`, this is quite uncomfortable for large datasets, isn't it ? – Marek Sebera Jan 15 '13 at 19:03
That's true, maybe https://developer.android.com/reference/android/database/sqlite/SQLiteQueryBuilder.html could help? – pawelzieba Jan 16 '13 at 10:02
Add some example code to your answer, I think this might be the solution, I'll accept you after. – Marek Sebera Jan 16 '13 at 10:55
thanks for the code... note that the binding index is 1-based.... you might want to correct that. – Matthieu Feb 06 '13 at 19:11
Is `statement.execute()` any better than using `db.insert()` directly? Seems like a few extra lines of code for not much benefit. – nucleartide Apr 04 '14 at 19:50
SQLiteStatement requires me to know about column indexes though, so I'm back to writing code for every specific table again (despite "createInsert" helping me with column names). InsertHelper could figure it out from ContentValues. – Victor Basso Jun 06 '15 at 23:08

score 1 · Answer 2 · answered Jan 15 '13 at 18:29

1

I recommend you take a look at google IO app especially the provider, this is how google developers do this, so you can be sure that this is the proper way.

answered Jan 15 '13 at 18:29

Nickolaus

4,785
4
38
60

1

Since the [provider](https://github.com/google/iosched/blob/master/android/src/main/java/com/google/samples/apps/iosched/provider/ScheduleProvider.java) (new URL) didn't even implement bulkInsert, I don't think they were concentrating on speed. – Jade Sep 03 '14 at 21:25

score 1 · Answer 3 · answered Jan 16 '13 at 17:12

I was facing the same issue and could not find how SQLiteStatement could easily replace DatabaseUtils.InsertHelper as you need to build the SQL query by hand.

I ended up using SQLiteDatabase.insertOrThrow which can easily replace existing code. I only had to add sometimes a null column hack to handle cases where I insert empty ContentValues.

Yes the statement is not compiled to be reused later but building a INSERT query by hand and binding all the parameters is too much pain. I would be interested to know how much this impact performance for large bulk insert data set (InsertHelper is only deprecated) in case you modify your code.

score 1 · Answer 4 · answered Apr 04 '13 at 13:18

1

I just made a backup of the default class keeping only the code related to InsertHelper, check out this gist.

I'm pretty sure this was deprecated to clean up the SDK utils classes. What's left with my class is only using non deprecated stuff.

answered Apr 04 '13 at 13:18

Romain Piel

11,017
15
71
106

Android: Bulk Insert, when InsertHelper is deprecated

4 Answers4

Linked