虽然光标有一些限制,但可以处理数百万行。
限制是如果一行包含的数据超过了游标窗口(1M(早期版本)或2M)所能容纳的数据量。通常,这只会发生在非常大的项目,如图像或视频。
具有3000000行的示例
下面是一个示例应用程序,可以处理300万行的插入和提取(albiet相当耗时)。
1.DBDone.java
用于确定非UI线程何时完成的接口
-
(如果未运行主UI线程,则可能会由于应用程序未重新注册(ANR)而导致崩溃)
:-
public interface DBDone {
void dbDone();
}
2.DBHelper.java
一个数据库助手,具有一些基本方法,允许添加和检索数据(不允许选择WAL或日记模式,后者在Android 9之前是默认模式)。
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
public static final int DBVERSION = 1;
public static final String TBL_TABLENAME = "table_name";
public static final String COL_SOMECOLUMN1 = "some_column_1";
public static final String COL_SOMECOLUMN2 = "some_column_2";
public static final String crt_tablename_sql = "CREATE TABLE IF NOT EXISTS " + TBL_TABLENAME + "(" +
COL_SOMECOLUMN1 + " TEXT, " +
COL_SOMECOLUMN2 + " TEXT" +
")";
private static boolean mWALMode = false;
SQLiteDatabase mDB;
public DBHelper(Context context, boolean wal_mode) {
super(context, DBNAME, null, DBVERSION);
mWALMode = wal_mode;
mDB = this.getWritableDatabase();
}
@Override
public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);
if (mWALMode) {
db.enableWriteAheadLogging();
} else {
db.disableWriteAheadLogging();
}
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(crt_tablename_sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
public long insert(String c1_value, String c2_value) {
String nullcolumnhack = null;
ContentValues cv = new ContentValues();
if ((c1_value == null && c2_value == null)) {
nullcolumnhack = COL_SOMECOLUMN1;
}
if (c1_value != null) {
cv.put(COL_SOMECOLUMN1,c1_value);
}
if (c2_value != null) {
cv.put(COL_SOMECOLUMN2,c2_value);
}
return mDB.insert(TBL_TABLENAME,nullcolumnhack,cv);
}
public long insertJustColumn1(String c1_value) {
return this.insert(c1_value, null);
}
public long insertJustColumn2(String c2_value) {
return this.insert(null,c2_value);
}
public Cursor getAllFromTableName() {
return this.getAll(TBL_TABLENAME);
}
public Cursor getAll(String table) {
return mDB.query(table,null,null,null,null,null,null);
}
}
-
main活动实例化DatabaseHelper(mDBHlpr),注意这将创建数据库和基础表,因为构造函数通过获取调用来强制创建
getWritableDatabase
-
然后调用dbDone方法,当mStage设置为0时,该方法将在新线程中清空表。
-
当表被清空时,调用dbDone时,mStage将为1,因此添加数据(如果不存在数据,则不应该添加,因为表已被清空)。
-
当数据被插入时,将调用dbDone,由于mStage现在为2,在提取光标后,将记录一些信息,并将显示所有行。遍历游标中的所有行,并计算两列都为null的行数。
-
-
两列都为null的行数将被计数(只是为了对光标进行处理)。随机插入空值时,数字会有所不同。
public class MainActivity extends AppCompatActivity implements DBDone {
DBHelper mDBHlpr;
int mStage = 0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DBHelper(this,false);
dbDone();
}
// Add some data but not in the UI Thread
private void addData() {
new Thread(new Runnable() {
@Override
public void run() {
addSomeData(3000000);
dbDone(); // All done so notify Main Thread
}
}
).start();
}
public void dbDone() {
switch (mStage) {
case 0:
emptyTable();
break;
case 1:
addData();
break;
case 2:
logSomeInfo();
break;
}
mStage++;
}
/**
* Add some rows (if none exist) with random data
* @param rows_to_add number of rows to add
*/
private void addSomeData(int rows_to_add) {
Log.d("ADDSOMEDATA","The addSomeData method hass been invoked (will run in a non UI thread)");
SQLiteDatabase db = mDBHlpr.getWritableDatabase();
if(DatabaseUtils.queryNumEntries(db,DBHelper.TBL_TABLENAME) > 0) return;
// Random data that can be added to the first column
String[] potential_data1 = new String[]{null,"complete","started","stage1","stage2","stage3","stage4","stage5"};
// Random data that can be added to the second column
String[] potential_data2 = new String[]{null,"something else","another","different","unusual","normal"};
Random r = new Random();
db.beginTransaction();
for (int i=0; i < rows_to_add; i++) {
mDBHlpr.insert(
potential_data1[(r.nextInt(potential_data1.length))],
potential_data2[(r.nextInt(potential_data2.length))]
);
}
db.setTransactionSuccessful();
db.endTransaction();
}
/**
* Log some basic info from the Cursor always traversinf the entire cursor
*/
private void logSomeInfo() {
Log.d("LOGSOMEINFO","The logSomeInfo method has been invoked.");
Cursor csr = mDBHlpr.getAllFromTableName();
StringBuilder sb = new StringBuilder("Rows in Cursor = " + String.valueOf(csr.getCount()));
int both_null_column_count = 0;
while (csr.moveToNext()) {
if (csr.getString(csr.getColumnIndex(DBHelper.COL_SOMECOLUMN1)) == null && csr.getString(csr.getColumnIndex(DBHelper.COL_SOMECOLUMN2)) == null) {
both_null_column_count++;
}
}
sb.append("\n\t Number of rows where both columns are null is ").append(String.valueOf(both_null_column_count));
Log.d("LOGSOMEINFO",sb.toString());
}
/**
* Empty the table
*/
private void emptyTable() {
Log.d("EMPTYTABLE","The emptyTable method has been invoked (will run in a non UI thread)");
new Thread(new Runnable() {
@Override
public void run() {
mDBHlpr.getWritableDatabase().delete(DBHelper.TBL_TABLENAME,null,null);
dbDone();
}
}).start();
}
}
日志可能包含CursorWindow完整消息,但这些消息会被处理(因为有问题的行将包含在下一个CursorWindow中),例如:-
2018-12-30 10:19:10.862 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 404 bytes, free space 204 bytes, window size 2097152 bytes
2018-12-30 10:19:11.856 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 24 bytes, free space 11 bytes, window size 2097152 bytes
2018-12-30 10:19:12.377 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 7 bytes, free space 4 bytes, window size 2097152 bytes
2018-12-30 10:19:12.902 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 8 bytes, free space 1 bytes, window size 2097152 bytes
2018-12-30 10:19:13.433 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 24 bytes, free space 1 bytes, window size 2097152 bytes
2018-12-30 10:19:13.971 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 24 bytes, free space 21 bytes, window size 2097152 bytes
2018-12-30 10:19:14.505 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 24 bytes, free space 18 bytes, window size 2097152 bytes
2018-12-30 10:19:15.045 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 404 bytes, free space 187 bytes, window size 2097152 bytes
2018-12-30 10:19:15.598 2799-2817/so53958115.so53958115 W/CursorWindow: Window is full: requested allocation 7 bytes, free space 0 bytes, window size 2097152 bytes
...........
时间:-
日志包括:-
2018-12-30 10:17:04.610 2799-2799/? D/EMPTYTABLE: The emptyTable method has been invoked (will run in a non UI thread)
2018-12-30 10:17:04.615 2799-2817/? D/ADDSOMEDATA: The addSomeData method hass been invoked (will run in a non UI thread)
2018-12-30 10:19:10.506 2799-2817/so53958115.so53958115 D/LOGSOMEINFO: The logSomeInfo method has been invoked.
2018-12-30 10:20:17.803 2799-2817/so53958115.so53958115 D/LOGSOMEINFO: Rows in Cursor = 3000000
Number of rows where both columns are null is 62604
所以它采取了:-
-2分6秒添加3000000行。
-1分7.5秒提取并遍历光标(通常不会提取这么多行)
但最重要的是,游标已经处理了3000000行。在本例中,您还可以看到游标窗口是2M(2097152字节)。
结论
问题不太可能是1000行对于光标来说太大,尽管可能是某些行(如果存储图像/视频/长文本)的最大值超过了光标可以处理的大小。
问题更可能是由于其他原因造成的,这些原因只能通过日志中的堆栈跟踪来确定。
因此,如果没有堆栈跟踪或更全面的信息,就不可能提供特定的答案。
