Inserting multiple entries into SQLite database android

Question

I am having a problem inserting multiple entries into an Android SQLite database. I insert like so with db.execQuery():

String insertString = "INSERT INTO coordinates 
    SELECT 0 AS _id, 5 AS associated_route_id, 38.88945 AS latidude, -77.034821 AS longitude 
    UNION SELECT 1, 5, 38.889671, -77.034912 
    UNION SELECT 2, 5, 38.890041, -77.035316"
database.execSQL(insertString);

I pull later with the same db like so:

String[] columns = new String[] { "latitude", "longitude" };
Cursor cursor = db.query("coordinates", columns,
                "associated_route_id=5", null, null, null,
                null);

cursor.moveToFirst();
if (cursor.getCount() == 0)
// No rows in cursor

I got this to work using db.insert(table, null, contentValues), but replaced the insert to make things faster.

The problem is that the cursor is empty which makes it seem like the insert is not working. Why doesn't it work?

Can you post your insert code? Are you using sql script or inserting with content value? — wangyif2
– wangyif2, Commented Apr 23, 2013 at 17:37

Johnny Z · Accepted Answer · 2013-04-23 19:41:07Z

10

Answering my own question.

I used a different approach to insert the entries. I used Trinimon's suggestion to use db.compileStatement, but what increased insertion time the most was adding:

db.startTransaction();
//insert lots of stuff...
database.setTransactionSuccessful();
database.endTransaction();

Inserting 500 entries decreased from 45 seconds to 300 milliseconds.

answered Apr 23, 2013 at 19:41

Johnny Z

15.7k5 gold badges31 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trinimon Over a year ago

Thought about proposing this, but hadn't assumed that it would be such a performance booster. Usually you can use executeBatch() with JDBC what is also a quite nice feature (viralpatel.net/blogs/batch-insert-in-java-jdbc).

mittelmania Over a year ago

Thanks, that answer really helped me! It's worth noting that Trinimon's example for syntax that could be compiled is not valid, the correct example should be: String sql = "INSERT INTO coordiantes VALUES(?, ?, ?, ?)"

Trinimon · Accepted Answer · 2013-04-23 17:48:05Z

2

The fastest way to insert records is to use prepared statements with bind variables, because the statement doesn't need to be compiled all the time, e.g:

String sql = "INSERT INTO coordinates (?, ?, ?, ?)";
SQLiteStatement statement = db.compileStatement(sql);

// loop through records or arrays and assign values
for (...) { 
    long id = ...
    long associated_route_id = ...
    double latidude = ...
    double longitude = ...

    statement.bindLong  (1, id);
    statement.bindLong  (2, associated_route_id);
    statement.bindDouble(3, latidude);
    statement.bindDouble(4, longitude);

    statement .execute();
}

Hope this helps ... Cheers!

edited Apr 23, 2013 at 17:48

answered Apr 23, 2013 at 17:41

Trinimon

14k9 gold badges46 silver badges61 bronze badges

2 Comments

Johnny Z Over a year ago

Oops, copy/paste error. I took out some fields to simplify the problem. I updated the question. I will try and implement this insert method to see if it works. Thank you.

Trinimon Over a year ago

Ok, I removed my hint from here 'cause it's no longer valid.

Collectives™ on Stack Overflow

Inserting multiple entries into SQLite database android

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related