Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Efficient batch SQL query execution on Android, for upgrade database

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();
    }
like image 719
Pentium10 Avatar asked May 20 '10 17:05

Pentium10


People also ask

What are the several important methods that can be used in SQLite database for Android?

Important Methods in SQLite DatabaseThis method will return the number of rows in the cursor. This method returns a Boolean value when our cursor is closed. This method returns the total number of columns present in our table. This method will return the name of the column when we passed the index of our column in it.


2 Answers

How do I insert these efficiently?

Use transaction:

    db.beginTransaction();
    try {
        for(;;) {
            db.execSQL(...);
            }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
like image 197
yanchenko Avatar answered Sep 19 '22 01:09

yanchenko


I think what you have is the only way to load those 1000 records, now, as far as deploying that DB with your apk file, check out this post:

http://www.helloandroid.com/tutorials/how-have-default-database

like image 30
Ricardo Villamil Avatar answered Sep 22 '22 01:09

Ricardo Villamil