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?

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.