Add a lot of data to a SQLite database on Android using ORM

Question

I have an SQLite database with some tables containing several thousands of rows. I use GreenDAO to manage this database (but it could be another ORM).

What is the best way to add a lot of data in my database ?

1 - Using the conventional way of the ORM with thousands of lines like this one ? (GreenDAO in this example)

daoSession.getDefinitionDao().insert(new Definition(null,4L,"ail"));

This could be messy and get code too large errors on the way.

2 - Use an already populated database in the asset folder ?

Copy this already populated database from the asset folder to the data folder of the app using a method similar to this one :

 /**
 * Copy existing database file in system
 */
public void copyDataBase() {

    int length;
    byte[] buffer = new byte[1024];
    String databasePath = DB_PATH + DB_NAME;

    try {
        InputStream databaseInputFile = this.context.getAssets().open(DB_NAME+".sqlite");
        OutputStream databaseOutputFile = new FileOutputStream(databasePath);

        while ((length = databaseInputFile.read(buffer)) > 0) {
            databaseOutputFile.write(buffer, 0, length);
            databaseOutputFile.flush();
        }
        databaseInputFile.close();
        databaseOutputFile.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

3 - Use raw queries got from a file containing a pure SQL script with all my inserts ?

SQLiteDatabase.execSQL(MY_RAW_SQL_INSERT_QUERY)

4 - Any other method ?

Pratik Tank · Accepted Answer · 2017-09-08 10:17:31Z

1

i am inserting thousands of record in few second using sqlite use below code.

THIS IS CREATE TABLE QUERY :

private static final String CREATE_MY_TABLE = "CREATE TABLE IF NOT EXISTS " + MY_TABLE_NAME
    + " (" + UID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
    + LICENSE_NUMBER + " VARCHAR(255), "
    + MODEL_NUMBER + " VARCHAR(255), "
    + " UNIQUE (" + LICENSE_NUMBER + "));”;

NOTE: in above query LICENSE_NUMBER is UNIQUE, the query automatically manage insert/update you don't need to worry about that.

here i am reading json formatted file from sdcard and insert it into sqlite:

public boolean insertUpdateBulk() {

    boolean flag = false;

    SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
    try {

        File sdCardDirectory = AppUtils.getDataFileDirectoryPath(mContextDatabaseAdapter);
        String jsonPanelData = loadJSONFromSDCard(sdCardDirectory, MYFILENAME.json);

        if (jsonPanelData != null) {
            JSONArray ja = new JSONArray(jsonPanelData);
            L.e("=== DB === START");
            sqLiteDatabase.beginTransaction();

            String sql = "Insert or Replace into " + DatabaseHelper.MY_TABLE_NAME + " (" +
                    LICENSE_NUMBER +
                    ", " + MODEL_NUMBER + ") values(?,?)";

            SQLiteStatement insert = sqLiteDatabase.compileStatement(sql);

            for (int i = 0; i < ja.length(); i++) {
                JSONObject jsl = ja.getJSONObject(i);

                insert.bindString(INDEX_OF_LICENSE_NUMBER, jsl.getString("LICENSE_NUMBER"));
                insert.bindString(INDEX_OF_MODEL_NUMBER, jsl.getString("MODEL_NUMBER"));

                insert.execute();
            }
            sqLiteDatabase.setTransactionSuccessful();
            flag = true;
            L.e("=== DB === DONE");
        }
    } catch (Exception e) {
        L.e("=== DB === Error " + e);
    } finally {
        sqLiteDatabase.endTransaction();
    }
    return flag;
}

answered Sep 8, 2017 at 10:17

Pratik Tank

2,2521 gold badge21 silver badges30 bronze badges

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

3 Comments

Laurent Over a year ago

Good idea. I didn't think about this approach, import data from JSON.

Pratik Tank Over a year ago

@Laurent please accept my answer it helps other too.

Laurent Over a year ago

Just waiting if there are other propositions. For sure I'll accept it if consider it's the one that suit the best my needs in 2 or 3 days. Thank you again ;)

Collectives™ on Stack Overflow

Add a lot of data to a SQLite database on Android using ORM

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related