Android: Bulk Insert, when InsertHelper is depreca

问题:

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.

回答1:

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

回答2:

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.

回答3:

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.

回答4:

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.