Fastest way to insert values to android sqlite database

Question

I want to create a database, and fill it at the start of application. It will remain same and will never change. There will be like 1000 entries. What is the good way to do this?

I created the table so far, but now i have to fill it with 1000 entries. Is there an UI for it?

public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            db.execSQL("CREATE TABLE "+TABLE_NAME+" ("+
            UID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
            USER_NAME+" TEXT NOT NULL, "+
            USER_TYPE+" TEXT NOT NULL, "+
            USER_CLASS+" TEXT NOT NULL);"


        );

Where do these 1000 entries come from? If you know them before shipping the app, take some tool to build the database (there are GUI tools for sqlite) and load it via SQLiteAssetOpenHelper for example. — zapl
– zapl, Commented Mar 31, 2014 at 13:20

Guillermo Merino · Accepted Answer · 2014-03-31 13:21:53Z

1

Several ways to improve the performance:

As Tsunaze suggests, you should do this in another thread, you can check AsyncTask which is the "default" tool provided in the SDK to do this.

Consider using transactions:

db.beginTransaction();
for(...){
    db.insert(...);
}
db.commit();

In this way you will only write to the database when you finish inserting all the values, speeding up the process.

Do massive inserts:

insert into table
select value1X, value1Y, value1Z
union all
select value2X, value2Y, value2Z
union all
select value3X, value3Y, value3Z
union all
select value4X, value4Y, value4Z

answered Mar 31, 2014 at 13:21

Guillermo Merino

3,2672 gold badges20 silver badges36 bronze badges

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

Comments

Autocrab · Accepted Answer · 2014-03-31 13:12:31Z

0

you can create file "table_inserts.sql" and put it in "raw" folder.

    private void insertValues(SQLiteDatabase db) {
        db.beginTransaction();
        try {
            BufferedReader reader = new BufferedReader(new InputStreamReader(context.getResources().openRawResource(R.raw.table_inserts))));
            String s;
            while ((s = reader.readLine()) != null) {
                db.execSQL(s);
            }
            reader.close();
        } catch (IOException ignored) {
        }
        db.setTransactionSuccessful();
        db.endTransaction();
    }

or your can write all inserts in code.

answered Mar 31, 2014 at 13:12

Autocrab

3,8071 gold badge19 silver badges16 bronze badges

Comments

Tsunaze · Accepted Answer · 2014-03-31 13:13:43Z

You need to do it in a thread, to no block the main UI:

public void addObjects(List<Object> objects, OnQueryListener listener){
listener.begin();
database.beginTransaction();
for(int i = 0; i < objects.size();i++{
// Add
}
database.setTransactionSuccessful();
database.endTransaction();
listener.end();
}

private interface OnQueryListener{
void begin();
void end();
}

And in your main Code do something like this :

Handler h = new Handler();
Thread t = new Thread(run);
t.start();

Runnable run = new Runnable{

void run(){
addObjects(objects, new OnQueryListener{

void begin(){

}
void end(){
handler.post(new Runnable{
void run(){
// On your main UI
}
}
}
}
}
}

It's not something i've tested on real code, just something at the top of my head.

Collectives™ on Stack Overflow

Fastest way to insert values to android sqlite database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related