Unable to prevent SQLiteConnection object leakage

Question

In my Android app, I have some Database transaction using SQLite but I am getting SQLiteConnection object leakage despite trying many ways to prevent the leak. I have almost tried each and every thing in the internet like closing the db, closing the cursor, or ending the transaction. Below is the warning in android studio.

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

Sometimes by closing the db or closing the cursor I used to get error - Attempt to reopen an already-closed object ..... I am not pro in Android, I am trying to learn by my own self , so could you help me. I have posted the codes below:

• Inside the DBHelper class

    public class DBHelper extends SQLiteOpenHelper {
  
      ...
        public static DBHelper getInstance(Context ctx) {
  
        if (mInstance == null) {
          mInstance = new DBHelper(ctx.getApplicationContext());
         }
         return mInstance;
       }
  
       private DBHelper(Context context) {
         super(context, DB_NAME, null, 1);
         this.context = context;
         DB_PATH = context.getDatabasePath(DB_NAME).getPath();
      }
  
     ...
  
     private Cursor getData(String Query) {
        String myPath = DB_PATH + DB_NAME;
        Cursor c = null;
       try {
            db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
            c = db.rawQuery(Query, null);
          } catch (Exception e) {
            e.printStackTrace();
           }
        return c;
       }
  
     private void dml(String Query) {
        String myPath = DB_PATH + DB_NAME;
        if (db == null)
          db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
        try {
           db.execSQL(Query);
        } catch (Exception e) {
           e.printStackTrace();
        }
      }
  
  
  
  
    //Methods to perform different db transaction
  
    public void addToSubject(ItemSubject itemSubject) {
     if (checkSuggested(itemSubject.getId())) {
       dml("delete from " + TABLE_SUBJECT + " where id = '" + itemSubject.getId() + "'");
     }
     String insert = "insert into TABLE_SUBJECT .....";
     dml(insert);
   }
  
   public void cleartable_subject() {
     String delete = "delete from " + TABLE_SUBJECT;
     dml(delete);
   }
  
  public long subject_size() {
     if (db == null) {
       db = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
     }
     try {
       long count = DatabaseUtils.queryNumEntries(db, TABLE_SUBJECT);
       return count;
     } catch (Exception e) {
       e.printStackTrace();
       return 0;
    }
   }
  
   private Boolean checkSubject(String id) {
     String select = "select * from " + TABLE_SUBJECT + " where id = '" + id + "'";
     Cursor cursor = getData(select);
     return cursor != null && cursor.getCount() > 0;
   }
  
   public ArrayList<ItemSubject> loadDataSubject() {
      ArrayList<ItemSubject> arrayList = new ArrayList<>();
      String select = "select * from " + TABLE_SUBJECT;
      Cursor cursor = getData(select);
      if (cursor != null && cursor.getCount() > 0) {
       cursor.moveToFirst();
       for (int i = 0; i < cursor.getCount(); i++) {
           String id = cursor.getString(cursor.getColumnIndex(TAG_ID));
           String course = cursor.getString(cursor.getColumnIndex(TAG_COURSE_NAME));
           ....
           ItemSubject objItem = new ItemSubject(id, courseId,...);
           arrayList.add(objItem);
           cursor.moveToNext();
       }
       cursor.close();
      }
      return arrayList;
     }
  
     //There are more similar methods for other tables
  

I am accessing these methods inside fragments and activities like

     dbHelper = DBHelper.getInstance(getActivity());

     if ((dbHelper.subject_size() >= 1){
      dbHelper.cleartable_subject();
      for (int i = 0; i < arrayListSubject.size(); i++) {
                dbHelper.addToSubject(arrayListSubject.get(i));
            }
            arrayListSubject = dbHelper.loadDataSubject();
     }

Sorry for the long section of codes, but I thought everything will be necessary. Could you please help me?

Criner

Answer 1

You say you looked into many questions online, but you definitely didn't look into this or this. It literally cropped up 5 minutes into searching.

Anyways, if I were you, to resolve this issue, I would include a finally clause when closing the connection. This means that you would be declaring your database helper as a static instance variable and use the Abstract Factory pattern to guarantee the singleton property.

Your warning is happening because you are not ensuring that only one DatabaseHelper will ever exist at any given time. If the mInstance object has not been initialized, one will be created. If one has already been created then it will simply be returned.

Here is the code:

public ArrayList<ItemSubject> loadDataSubject() {
    ArrayList<ItemSubject> arrayList = new ArrayList<>();
    String select = "select * from " + TABLE_SUBJECT;
    Cursor cursor = getData(select);
    
    
    if (cursor != null && cursor.getCount() > 0) {
    try {
        cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                String id = cursor.getString(cursor.getColumnIndex(TAG_ID));
                String course = cursor.getString(cursor.getColumnIndex(TAG_COURSE_NAME));
         ....
                ItemSubject objItem = new ItemSubject(id, courseId,...);
                arrayList.add(objItem);
                cursor.moveToNext();
           }
       finally {
           if (cursor != null)
           cursor.close();
       }
   }
   return arrayList;
}    

But since you say that my only contribution to your question (which had already been answered in other posts) is that of refining your lack of elementary English, then I am not that sure if you can accept this as an adequate answer.

Hulking

First of all thanks a lot for your answer. I had already looked at those post that you had given but it was creating some exceptions (like trying to open an already closed database or something of that type). – Criner

Also as you said - 'Your warning is happening because you are not ensuring that only one DatabaseHelper will ever exist at any given time. If the mInstance object has not been initialized, one will be created. If one has already been created then it will simply be returned.' I am already using my DatabaseHelper using mInstance object which ensures only one object. Please have a look at the code part of my question carefully. And finally i would like to say that if your answer solves my problem i will definitely accept your answer as solution and upvote it as well – Criner

looking at this post from an official Android engineer at Google, groups.google.com/g/android-developers/c/NwDRpHUXt0U/m/… you don't need to close the connection as it will get closed as part of the kernel cleaning up the process's resources when the process is killed. – Hulking

I had solved my problem. Earlier I had tried to close the cursor as you said inside the finally block, but it was still not working. The problem was the method getData() which was returning cursor. I just removed the getData method and initialised cursor separately in each method and called the cursor.close inside finally so now it is solved. – Criner