如何在从JSONArray向SqLite插入/更新10000行时防止应用程序崩溃

Question

我正在尝试将所有数据从Mysql数据库重新加载到SqLite数据库。我有10000行，试图用一个命令更新Sqlite。

我可以使用这些函数更新任何我的sqlite数据，但是当它有很多行时，应用程序崩溃

Android数据库游标窗口分配异常：2048 kb的游标窗口分配失败打开游标=608（#此进程打开的游标=608）

在阅读一些文章之前，我对ContentValues做了这项工作，我发现使用SQLiteStatement可以大大提高插入/更新速度。

我是这样做的：

KeyTypePair包括以下列名称和类型，12列：

public static final String[][] KEYS_TYPES = {
        new String[] { "id_field", "int"},
        new String[] { "text", "string" }
        ................. and so on..................
}

DBDatabase.java

private SQLiteDatabase db;

这从mysql获取10000行，并尝试插入/更新到sqlite

public void addAll(JSONArray ARRAY, String TABLE, String IDFIELD, String[][] KEYS_TYPES) {
    long startTime = System.currentTimeMillis();
    SQLiteDatabase db = this.getWritableDatabase();

    String sqlPrepared = sqlPrepared(KEYS_TYPES);

    if(doesTableExist(db, TABLE)) {
        try {
            for (int i = 0; i < ARRAY.length(); i++) {
                JSONObject ROW = ARRAY.getJSONObject(i);

                int id = getId(ROW, IDFIELD);

                String where = " WHERE " + IDFIELD + "=" + id;

                add(ROW, TABLE, IDFIELD, id, sqlPrepared, where, KEYS_TYPES);
            }
            long difference = System.currentTimeMillis() - startTime;
            Log.e("add all timing", new Long(difference).toString());
        } catch (JSONException e) {
            voids.debugMsg(context, "SQLite addAll ERROR: " + e.toString());
        }
    } else {
        voids.debugMsg(context, "TABLE " + TABLE + " NOT EXIST, LETS TRY AGAIN..");
        createTable(db, TABLE);
        addAll(ARRAY, TABLE, IDFIELD, KEYS_TYPES);
    }
}

这将完成实际的插入

public void add(JSONObject object, String TABLE, String ID, int id, String sqlPrepared, String where, String[][] keyTypePair) {
    db.beginTransaction();
    String sql = "";
    if(dataExist(TABLE, ID, id)) {
        sql = "UPDATE " + TABLE + " SET " + sqlPrepared + where;
    } else {
        sql = "INSERT INTO " + TABLE + " SET " + sqlPrepared;
    }

    try {
        SQLiteStatement stmt = db.compileStatement(sql);
        stmt = bindValues(keyTypePair, stmt, object);
        stmt.execute();
    } finally {

    }
    db.setTransactionSuccessful();
    db.endTransaction();
}

这将创建用于更新和插入的部分准备字符串

public String sqlPrepared(String[][] keyTypePair) {
    String columnString = "";
    for (int i = 0; i < keyTypePair.length; i++) {
        String[] pair = keyTypePair[i];
        String KEY = pair[0];
        if(keyTypePair.length!=i+1) {
            columnString += KEY + "=?, ";
        } else {
            columnString += KEY + "=?";
        }

    }
    columnString += "";
    return columnString;
}

这只是为了从JSONObject获取id

private int getId(JSONObject OBJECT, String ID) {
    int id = 0;
    try {
        id = OBJECT.getInt(ID);
    } catch (JSONException e) {
        Log.e("DBDatabase ERROR: ", "ERROR WHILE TRYING TO ADD TO SQLite: "+e.toString());
    }
    return id;
}

这将检查SqLite数据库中是否存在行

public boolean dataExist(String TABLE, String IDFIELD, int ID) {
    boolean exist = false;
    String[] columns = {IDFIELD};
    Cursor cursor =
            db.query(TABLE,
                    columns, // b. column names
                    IDFIELD + " = ?", // c. selections
                    new String[]{String.valueOf(ID)}, // d. selections args
                    null, // e. group by
                    null, // f. having
                    null, // g. order by
                    null); // h. limit
    if (cursor.getCount()>0) {
        exist = true;
    }
    return exist;
}

以下是当应用程序崩溃时我对logcat的看法：

08-24 17:02:06.137    9522-9522/.DBService E/Zygote﹕ MountEmulatedStorage()
08-24 17:02:06.137    9522-9522/.DBService E/Zygote﹕ v2
08-24 17:02:06.167    9522-9522/.DBService E/SELinux﹕ [DEBUG] get_category: variable seinfo: default sensitivity: NULL, cateogry: NULL
08-24 17:02:13.687    9522-9522/.DBService E/CursorWindow﹕ Could not allocate CursorWindow '/data/data/my.app/databases/myDB' of size 2097152 due to error -12.
08-24 17:02:13.697    9522-9522/.DBService E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: .DBService, PID: 9522
android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed. # Open Cursors=608 (# cursors opened by this proc=608)
        at android.database.CursorWindow.<init>(CursorWindow.java:108)
        at android.database.AbstractWindowedCursor.clearOrCreateWindow(AbstractWindowedCursor.java:198)
        at android.database.sqlite.SQLiteCursor.clearOrCreateWindow(SQLiteCursor.java:301)
        at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:139)
        at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
        at my.app.MyDatabase.dataExist(MyDatabase.java:569)
        at my.app.MyDatabase.add(MyDatabase.java:392)
        at my.app.MyDatabase.addAll(MyDatabase.java:407)
        at my.app.MyReceiver$1.onSuccess(MyReceiver.java:37)
        at my.app.SocketManager$1.onPostExecute(SocketManager.java:121)
        at my.app.SocketManager$1.onPostExecute(SocketManager.java:58)
        at android.os.AsyncTask.finish(AsyncTask.java:632)
        at android.os.AsyncTask.access$600(AsyncTask.java:177)
        at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:145)
        at android.app.ActivityThread.main(ActivityThread.java:5832)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1399)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1194)

Answer 1

在你的dataExist（）中，你需要在返回之前关闭光标。

public boolean dataExist(String TABLE, String IDFIELD, int ID) {
    boolean exist = false;
    String[] columns = {IDFIELD};
    Cursor cursor =
            db.query(TABLE,
                    columns, // b. column names
                    IDFIELD + " = ?", // c. selections
                    new String[]{String.valueOf(ID)}, // d. selections args
                    null, // e. group by
                    null, // f. having
                    null, // g. order by
                    null); // h. limit
    if (cursor.getCount()>0) {
        exist = true;
    }
    cursor.close();
    return exist;
}