Geek asks and answers

Paste a lot of data into sqlite in android

Currently, I have to insert over 10 billion data into my android. However, a low memory problem will cause the program to crash. The insert test in sqlite is pretty simple. Just using the for loop to create the sql insert and wrqpped "begin" and "commit" commands.

    private ArrayList<String> sqlInsertCmd = new ArrayList<String>();
    int QUERIES_TIMES = 10000000;
    private void CreateInsertQueries()
    {
        Random localRandom = new Random();
        int i = 0;
        while (i < QUERIES_TIMES)
        {
            int j = localRandom.nextInt(100000);
            sqlInsertCmd.add("insert into " + TABLE + " (a,b,c) values (" + i + "," + 
            j + ",'" + String.valueOf(j) + "')");
            ++i;
        }
    }

    Then..

    mDB.beginTransaction();
    for (int i=0; i<this.QUERIES_TIMES; i++)
    {
        mDB.execSQL(sqlInsertCmd.get(i));
    }
    mDB.setTransactionSuccessful();
    mDB.endTransaction();

Any ideas to avoid running out of memory?

Thanks to everyone, but the code above is just a simple example. In my program it is more complicated. I have to store something in a container (e.g. hashMap) and dynamically build the sql statement. Can I create 10 services and each service processes 1/10 jobs?

+3
source share
2 answers

Few things:

:

// note: untested code used for illustration!
private boolean bulkInsertData(SQLiteDatabase db, final String tableName) {
    final int NUM_ROWS = 10000000;

    Random random = new Random();

    try {
        SQLiteStatement insStmt = insStmt = db.compileStatement("INSERT INTO " + tableName + " (a, b, c) VALUES (?, ?, ?);");
        db.beginTransaction();
        try {
            for(int i = 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) {
        return false;
    }

    return true;
}
+3

ArrayList, 10000000 sql ( OutOfMemory), , . :

private void CreateInsertQueries(SQLiteDatabase mDB)
{
    Random localRandom = new Random();
    int i = 0;
    String query;

    try{
        mDB.beginTransaction();

        while (i < QUERIES_TIMES)
        {
            int j = 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(); }
}

ArrayList, ( ) OutOfMemory - .

, , .

0

More articles:


All Articles