Android SQLite leaked

Question

i having problem with sql handler

A SQLiteConnection object for database '/data/data/.../databases/queueManager' was leaked!  Please fix your application to end transactions in progress properly and to close the database when it is no longer needed.

taken from Androidhive tutorial and customized to my use

the table looks like

+ ----------------------------------------------------------- +
: DATABASE_ID : DATABASE_QID : DATABASE_QUEUE : DATABASE_DATE :
+ ----------------------------------------------------------- +

The code

   DBQueue searchDBqid(int id) {
       SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_QID + " = " + id; 

           Cursor cursornum = db.rawQuery(selectQuery, null);
           int dk = cursornum.getCount();
           cursornum.close();

           if (dk >0) {
               Cursor cursor = db.query(TABLE_QUEUE, new String[] { DATABASE_ID,
                       DATABASE_QID, DATABASE_QUEUE, DATABASE_DATE }, DATABASE_QID + "=?",
                       new String[] { String.valueOf(id) }, null, null, null, null);

               if (cursor != null) cursor.moveToFirst();

               DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
                       cursor.getString(1), cursor.getString(2), cursor.getString(3));
               return dbqueue;
           }

       db.close();
       return null;
   }

   DBQueue getDBQueue(int id) {
       SQLiteDatabase db = this.getReadableDatabase();

       Cursor cursor = db.query(TABLE_QUEUE, new String[] { DATABASE_ID,
               DATABASE_QID, DATABASE_QUEUE }, DATABASE_ID + "=?",
               new String[] { String.valueOf(id) }, null, null, null, null);
       if (cursor != null)
           cursor.moveToFirst();

       DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
               cursor.getString(1), cursor.getString(2), cursor.getString(3));
       return dbqueue;
   }


   public String getAllqid() {
       Time today = new Time(Time.getCurrentTimezone());
       today.setToNow();

       String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_DATE + " = '" + today.format("%d %m %Y") + "'";

       SQLiteDatabase db = this.getWritableDatabase();
       Cursor cursor = db.rawQuery(selectQuery, null);

       StringBuilder sb = new StringBuilder();       
       if (cursor.moveToFirst()) {
           do {
               if (sb.length() > 0) sb.append(',');
               sb.append(cursor.getString(1));
           } while (cursor.moveToNext());
       }

       String result = sb.toString();
       return result;
   }
   public void deleteDatedDBQueue() {
        Time today = new Time(Time.getCurrentTimezone());
        today.setToNow();
        String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_DATE + " != '" + today.format("%d %m %Y") + "'"; ;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                db.delete(TABLE_QUEUE, DATABASE_ID + " = ?",
                       new String[] { String.valueOf(Integer.parseInt(cursor.getString(0))) });
            } while (cursor.moveToNext());
        }
        db.close();
    }
   public int getDBQueueCount() {
       String countQuery = "SELECT  * FROM " + TABLE_QUEUE;
       SQLiteDatabase db = this.getReadableDatabase();
       Cursor cursor = db.rawQuery(countQuery, null);
       cursor.close();

       return cursor.getCount();
   }
}

Can someone please tell me how to fix this leak ?

full code: http://ijailbreak.me/databasehandler.txt

Answer 1

Each Cursor should be closed when you're finished with it. The traditional way to do this is:

Cursor cursor = db.query(...);
try {
    // read data from the cursor in here
} finally {
    cursor.close();
}

But now, with try-with-resources, it can be much more concise:

try (Cursor cursor = db.query(...)) {
    // read data from the cursor in here
}

Answer 2

You forget to close your cursors on several occasions, make sure you always close the cursor when your done.

For instance, the second query does not close the cursor, I have TODO'd it for clarity

Also you do not close theSQLiteDatabase once your done in getDBQueue, getAllqid and getDBQueueCount, if you change your design to make your SQLiteOpenHelper a singleton then you won't need to close the SQLiteDatabase and avoid the leak

   DBQueue searchDBqid(int id) {
       SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT  * FROM " + TABLE_QUEUE + " WHERE " + DATABASE_QID + " = " + id; 

           Cursor cursornum = db.rawQuery(selectQuery, null);
           int dk = cursornum.getCount();
           cursornum.close();

           if (dk >0) {

               // TODO: Close this cursor!
               Cursor cursor = db.query(TABLE_QUEUE, new String[] { DATABASE_ID,
                       DATABASE_QID, DATABASE_QUEUE, DATABASE_DATE }, DATABASE_QID + "=?",
                       new String[] { String.valueOf(id) }, null, null, null, null);

               if (cursor != null) cursor.moveToFirst();

               DBQueue dbqueue = new DBQueue(Integer.parseInt(cursor.getString(0)),
                       cursor.getString(1), cursor.getString(2), cursor.getString(3));
               return dbqueue;
           }

       db.close();
       return null;
   }