Efficient batch SQL query execution on Android, for upgrade database

android, sqlite, upgrade

Solution

How do I insert these efficiently?

Use transaction:

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

Problem

As we Android developers know, the SQLiteDatabase `execSQL` method can execute only one statement. The doc says: Execute a single SQL statement that is not a query. For example, `CREATE TABLE`, `DELETE`, `INSERT`, etc. Multiple statements separated by `;`s are not supported. I have to load in a batch of records, 1000 and counting. How do I insert these efficiently? And what's the easiest way to deliver these SQLs with your apk? I mention, there is already a system database and I will run this on the `onUpdate` event. I have this code so far: ``` List<String[]> li = new ArrayList<String[]>(); li.add(new String[] { "-1", "Stop", "0" }); li.add(new String[] { "0", "Start", "0" }); /* the rest of the assign */ try { for (String[] elem : li) { getDb().execSQL( "INSERT INTO " + TABLENAME + " (" + _ID + "," + NAME + "," + PARSE_ORDER + ") VALUES (?,?,?)", elem); } } catch (Exception e) { e.printStackTrace(); } ```

Original source