android sqlitelog,Android sqlite log reports and db.close()

问题

The log reports that the database or cursor was not closed. I basically have an activity with a custom surfaceview and uses a handler to report back to the activity. When I receive the msg I show an alertdialog and also update the database.

private Handler handler = new Handler() {

public void handleMessage(Message msg) {

switch(msg.what) {

case 1:

dh.open();

dh.updateEvent("id", "name", "someone");

dh.close();

successAlert.show();

break;

case 2:

failAlert.show();

break;

}

}

};

Previously I did not have "dh.close()" and thats when the log reported database/cursor not closed. But ever since I added that in, it takes a really long time to complete. Once I get the message, the system seems to hang. Am I doing something wrong or does it usually take this long. I have also tried using a try block with a finally to close the db.

EDIT:

public class DatabaseHelper {

private Database dbHelper;

private SQLiteDatabase db;

private Context context;

public DatabaseHelper(Context context) {

this.context = context;

//database = new Database(context);

}

public void open() {

dbHelper = new Database(context);

db = dbHelper.getWritableDatabase();

}

public void close() {

dbHelper.close();

}

public void updateEvent(int id, String name, int other) {

ContentValues cv = new ContentValues();

cv.put("id", id);

cv.put("name", name);

cv.put("other", other);

db.update("stateTable", cv, "id=" + id, null);

}

public boolean checkState(int id) {

db = dbHelper.getReadableDatabase();

Cursor cursor = db.query("stateTable", null, null, null, null, null, null);

cursor.moveToPosition(id - 1);

int i = cursor.getInt(2);

android.util.Log.d("semajhan", ": " + i);

if (i == 1) {

return true;

} else {

return false;

}

}

}

Extended SQLiteOpenHelper:

public class Database extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "events.db";

private static final int DATABASE_VERSION = 1;

private static final String TABLE_NAME = "stateTable";

private static final String ID = "id";

private static final String NAME = "name";

private static final String OTHER = "other";

private static final String DATABASE_CREATE = "CREATE TABLE stateTable (id INT, name TEXT, other INT)";

private static final String DATABASE_UPGRADE = "DROP TABLE IF EXISTS table";

public Database(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

// TODO Auto-generated constructor stub

}

@Override

public void onCreate(SQLiteDatabase db) {

// TODO Auto-generated method stub

db.execSQL(DATABASE_CREATE);

// added initial values

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// TODO Auto-generated method stub

db.execSQL(DATABASE_UPGRADE);

onCreate(db);

}

}

01-11 13:57:41.239: ERROR/ActivityManager(61): ANR in com.semajhan.soodles (com.semajhan.soodles/.Level1)

01-11 13:57:41.239: ERROR/ActivityManager(61): Reason: keyDispatchingTimedOut

01-11 13:57:41.239: ERROR/ActivityManager(61): Load: 1.64 / 0.56 / 0.26

01-11 13:57:41.239: ERROR/ActivityManager(61): CPU usage from 35716ms to -1ms ago:

01-11 13:57:41.239: ERROR/ActivityManager(61): 44% 862/com.semajhan.soodles: 37% user + 7.2% kernel / faults: 853 minor

01-11 13:57:41.239: ERROR/ActivityManager(61): 29% 61/system_server: 27% user + 1.9% kernel / faults: 142 minor

01-11 13:57:41.239: ERROR/ActivityManager(61): 0.2% 731/com.android.quicksearchbox: 0% user + 0.2% kernel / faults: 30 minor

01-11 13:57:41.239: ERROR/ActivityManager(61): 0.2% 707/com.android.launcher: 0.2% user + 0% kernel / faults: 30 minor

01-11 13:57:41.239: ERROR/ActivityManager(61): 0.2% 801/com.svox.pico: 0.1% user + 0.1% kernel / faults: 363 minor

01-11 13:57:41.239: ERROR/ActivityManager(61): 0% 117/com.android.systemui: 0% user + 0% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 0% 41/adbd: 0% user + 0% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 99% TOTAL: 86% user + 13% kernel + 0% irq

01-11 13:57:41.239: ERROR/ActivityManager(61): CPU usage from 1969ms to 2620ms later:

01-11 13:57:41.239: ERROR/ActivityManager(61): 54% 61/system_server: 48% user + 6% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 40% 69/SurfaceFlinger: 40% user + 0% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 10% 92/InputDispatcher: 7.5% user + 3% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 1.5% 62/HeapWorker: 1.5% user + 0% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 44% 862/com.semajhan.soodles: 32% user + 12% kernel / faults: 2 minor

01-11 13:57:41.239: ERROR/ActivityManager(61): 24% 874/Thread-13: 24% user + 0% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 23% 862/studios.soodles: 4.6% user + 18% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 1.5% 867/Compiler: 0% user + 1.5% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 0.8% 731/com.android.quicksearchbox: 0% user + 0.8% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 0.8% 732/HeapWorker: 0% user + 0.8% kernel

01-11 13:57:41.239: ERROR/ActivityManager(61): 100% TOTAL: 76% user + 23% kernel

回答1:

The first call to getReadableDatabase or getWritableDatabase of the SQLiteOpenHelper instance takes a really long time to complete. You shouldn't create a new Database object (your SQLiteOpenHelper instance) each time that you need to query the database. Try using the same Database instance within DatabaseHelper.

When using SQLiteOpenHelper, you don't want to close the SQLiteDatabase object for an SQLiteOpenHelper because it is shared; i.e. getWritableDatabase always returns the same SQLiteDatabase object.

Notice that your checkState method leaks a cursor. To help prevent cursor leaks, I always use a try-finally after obtaining a cursor. For example:

db = dbHelper.getReadableDatabase();

Cursor cursor = db.query("stateTable", null, null, null, null, null, null);

try {

cursor.moveToPosition(id - 1);

int i = cursor.getInt(2);

android.util.Log.d("semajhan", ": " + i);

if (i == 1) {

return true;

} else {

return false;

}

} finally {

cursor.close();

}

回答2:

I wonder if the underlying problem regarding the cursor not being closed is the same as in this answer: Activities need to have an onDestroy method that closes the DatabaseHelper. (All I can tell right now is that you don't show an onDestroy method, but this is clearly just a portion of your code.) I also blogged about this problem here.

You also wrote that "But ever since I added [dh.close()], it takes a really long time to complete." Are you doing a lot of writes to the database? Maybe you need to use transactions to flush those writes periodically. If you're only reading from the database, then I have no idea why the close call would take a long time to complete. But without seeing more of your code, these are only guesses.

EDIT: ccheneson's advice about calling db.close() before calling dbHelper.getReadableDatabase() is worth following. However, you need to check that db isn't null. This changes the checkState to:

public boolean checkState(int id) {

if (db != null) {

db.close();

}

db = dbHelper.getReadableDatabase();

// ... etc ....

If that turns out to be the real problem, please accept ccheneson's answer, not mine.

(Above edit was incorrect; I should have known better! See below instead.)

EDIT 1: I'm not sure why you are re-opening the database at all in this method. Assuming that your DatabaseHelper.open method is invoked, you have a readable and writeable database handle. checkState doesn't need to re-open it to perform reads.

EDIT 2: However, SQLiteOpenHelper.getReadableDatabase will almost always return the same database handle that was returned from SQLiteOpenHelper.getWriteableDatabase. You do not need to close that database handle explicitly; the SQLiteOpenHelper will close it for you. You do need to be sure that SQLiteOpenHelper.close is invoked when your activity is destroyed (as I wrote above.)

回答3:

public void close() {

dbHelper.close(); // to change to db.close(); ?

}

I think here you would want to close the database handle db instead of the dbHelper

Also in you checkState method:

public boolean checkState(int id) {

db = dbHelper.getReadableDatabase();

Cursor cursor = db.query("stateTable", null, null, null, null, null, null);

cursor.moveToPosition(id - 1);

int i = cursor.getInt(2);

android.util.Log.d("semajhan", ": " + i);

if (i == 1) {

return true;

} else {

return false;

}

}

You would want to close the cursor like:

int i = cursor.getInt(2);

cursor.close();

android.util.Log.d("semajhan", ": " + i);

来源：https://stackoverflow.com/questions/4660363/android-sqlite-log-reports-and-db-close