Insert A Lot Of Data Into Sqlite In Android

currently, I have to insert more than 10 billions data into my android at a time. However, the problem of out of memory will crash the program. The sqlite insert test is quite simp

Solution 1:

A few things:

1. See my answer here for general tips when doing bulk INSERTs.
2. There is no need to have a temporary container for your INSERT statements (in this case, the ArrayList<>). Simply use beginTransaction() and endTransaction() in a try...finally.
3. Utilize pre-compiled statements via SQLiteStatement vs building each INSERT statement as in your example. This is needless thrashing.

Quick and dirty example:

// note: untested code used for illustration!privatebooleanbulkInsertData(SQLiteDatabase db, final String tableName) {
    finalintNUM_ROWS=10000000;

    Randomrandom=newRandom();

    try {
        SQLiteStatementinsStmt= insStmt = db.compileStatement("INSERT INTO " + tableName + " (a, b, c) VALUES (?, ?, ?);");
        db.beginTransaction();
        try {
            for(inti=0; i < NUM_ROWS; i++) {
                insStmt.bindLong(1, i);
                insStmt.bindLong(2, random.nextInt(100000));
                insStmt.bindString(3, String.valueOf(i));
                insStmt.executeInsert();    //  should really check value here!
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();    
        }
    } catch(SQLException se) {
        returnfalse;
    }

    returntrue;
}

Solution 2:

Instead of creating an ArrayList which contains 10000000 number of sql queries (and might be the potential reason for OutOfMemory), you may simply start inserting records in the same loop where you are creating query strings. For example:

privatevoidCreateInsertQueries(SQLiteDatabase mDB)
{
    RandomlocalRandom=newRandom();
    inti=0;
    String query;

    try{
        mDB.beginTransaction();

        while (i < QUERIES_TIMES)
        {
            intj= localRandom.nextInt(100000);
            query = "insert into " + TABLE + " (a,b,c) values (" + i + "," + 
            j + ",'" + String.valueOf(j) + "')";
            ++i;

            mDB.execSQL(query);
        }

        mDB.setTransactionSuccessful();
    }
    catch (SQLException e) { e.printStackTrace(); }
    finally{ mDB.endTransaction(); }
}

Doing so will help you to skip ArrayList which (i think) is the reason for OutOfMemory due to holding so so many records.

And make sure you do use External Memory to host your database file, otherwise not all phones have bigger internal memory.