Android- java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed

Question

I was reading through stackoverflow about this question and I still haven't found a solution. Sometimes my application face this problem. this is my error log,

12-31 10:58:28.025: E/AndroidRuntime(16322): FATAL EXCEPTION: Timer-0
12-31 10:58:28.025: E/AndroidRuntime(16322): java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:1031)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:746)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:400)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:905)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:834)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:197)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:237)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at com.eyepax.horseapp.data.dbadapters.ContactGroupDbAdapter.getContactGroups(ContactGroupDbAdapter.java:138)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at com.eyepax.horseapp.data.DataManager.getGroups(DataManager.java:1548)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at com.eyepax.horseapp.sync.ContactSync.contactGroupSync(ContactSync.java:43)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at com.eyepax.horseapp.service.SynchingService.callSync(SynchingService.java:77)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at com.eyepax.horseapp.service.SynchingService.access$0(SynchingService.java:70)
12-31 10:58:28.025: E/AndroidRuntime(16322):    at com.eyepax.horseapp.service.SynchingService$1.run(SynchingService.java:57)

This is the method which crashed in ContacGroupDbAdapter class,

/**
 * Returns ContactGroup.
 * 
 * @return ArrayList<ContactGroup>
 */
public ArrayList<ContactGroup> getContactGroups() {

    this.open();
    Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME, null);
    ArrayList<ContactGroup> list = null;

    if (cursor != null && cursor.moveToFirst()) {
        list = new ArrayList<ContactGroup>();

        do {
            try {
                ContactGroup contactGroup = new ContactGroup();
                contactGroup.setGroupId(cursor.getString(cursor.getColumnIndex(GROUP_ID)));
                contactGroup.setGroupName(cursor.getString(cursor.getColumnIndex(GROUP_NAME)));
                contactGroup.setIsDeleted(cursor.getInt(cursor.getColumnIndex(GROUP_IS_DELETED)));
                contactGroup.setUpdatedDate(cursor.getString(cursor.getColumnIndex(GROUP_UPDATE_DATE)));
                contactGroup.setIsSync(cursor.getInt(cursor.getColumnIndex(GROUP_IS_SYNC)));

                list.add(contactGroup);
            } catch (Exception e) {

            }
        } while (cursor.moveToNext());

    }

    if (cursor != null)
        cursor.close();
    return list;
}

And that it crashed in the line cursor.moveToFirst() with this error. please help me to resolve this issue.

i used separate BaseDbAdapter class to close db,

public class BaseDbAdapter {

/**
 * Application context.
 */
protected Context context;

/**
 * Database.
 */
protected SQLiteDatabase database;

/**
 * Database helper class.
 */
protected SQLiteDataBaseHelper dbHelper;

/**
 * StringBuilder
 */
protected StringBuilder stringBuilder;

public static final String CREATE_TABLE_PRE_TEXT = "create table if not exists ";
/**
 * Returns StringBuilder object.
 * 
 * @return
 */
protected StringBuilder getStringBuilder() {
    if (stringBuilder == null) {
        stringBuilder = new StringBuilder();
    }

    return stringBuilder;
}

/**
 * Opens a connection to the database.
 * 
 * @throws SQLException
 */
public synchronized void open() throws SQLException {
    dbHelper = SQLiteDataBaseHelper.getInstance(context);
    database = dbHelper.getWritableDatabase();
}

/**
 * Closes the connection.
 */
public synchronized void close() {
    database.close();
}

public static String getDeleteTableStatement(String tableName) {
    return "DELETE TABLE " + tableName;
}

}

I used SQLiteDataBaseHelper class and its getInstance method changed as synchronised.
as this answer but still issue not fixed.

 /**
 * Get an instance of SQLiteDataBaseHelper.
 * 
 * @param context
 * @return SQLiteDataBaseHelper
 */
public static synchronized SQLiteDataBaseHelper getInstance(Context context) {

    // if(dbName == null) {
    // dbName = DataManager.getSQLiteDatabaseName(context);
    // }

    if (instance == null) {
        instance = new SQLiteDataBaseHelper(context);

    }

    return instance;
}

Answer 1

The problem I think is with synchronization.When multiple threads access there is misconception about the state of your database.

You should avoid making database an instance variable and instead make it method local in your helper class (preferably the one that extends SQLiteOpenHelper). An example of getting the database and working on it is shown here:

public void addContact(Contact contact) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName()); // Contact Name
    values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number

    // Inserting Row
    db.insert(TABLE_CONTACTS, null, values);
    db.close(); // Closing database connection
}

Please check this tutorial: Android SQLite Database Tutorial

Here is a proposed example of how you may do it cleanly:

public class DatabaseHelper extends SQLiteOpenHelper {

    ......

    // no need to synchronize
    public Cursor getBookList(){
        SQLiteDatabase db = getReadableDatabase(); // or getWritableDatabase()
        Cursor c = db.query(BookEntry.TABLE_NAME, null, null, null, null, null, BookEntry.COLUMN_NAME + " ASC");
        return c;
    }

    // no need to override close method, because that one is very well coded already
    // SQLiteOpenHelper will close any opened database, with its service close method.
}

public class SomeAdapter{


    // no need to synchronize, as all DatabaseHelper instances are method local
    public int getBookCount(){
        DatabaseHelper dbHelper = new DatabaseHelper(context);
        Cursor c = dbHelper.getBookList();
        int rowCount = c.getCount();
        c.close();  // close the cursor once used.
        dbHelper.close(); // ask the helper to close any open database.
        return rowCount;

    }

}

Please check the SQLiteOpenHelper.java here: SQLiteOpenHelper.java Then you will know how database(mDatabase) is maintained by methods such as getReadableDatabase(),getWritableDatabase() and close().

Answer 2

    public ArrayList<ContactGroup> getContactGroups() {
       ArrayList<ContactGroup> list = null;
        try {
                // TODO Auto-generated method stub
                SQLiteDatabase database = this.getWritableDatabase();
        Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME, null);
         if (cursor != null && cursor.moveToFirst()) {
            list = new ArrayList<ContactGroup>();


                try { 
                     while (cursor.moveToNext())
                    {
                    ContactGroup contactGroup = new ContactGroup();
                    contactGroup.setGroupId(cursor.getString(cursor.getColumnIndex(GROUP_ID)));
                    contactGroup.setGroupName(cursor.getString(cursor.getColumnIndex(GROUP_NAME)));
                    contactGroup.setIsDeleted(cursor.getInt(cursor.getColumnIndex(GROUP_IS_DELETED)));
                    contactGroup.setUpdatedDate(cursor.getString(cursor.getColumnIndex(GROUP_UPDATE_DATE)));
                    contactGroup.setIsSync(cursor.getInt(cursor.getColumnIndex(GROUP_IS_SYNC)));

                    list.add(contactGroup);
                       }
                } catch (Exception e) {

                } 


        } 

        if (cursor != null)
    {
            cursor.close();
        database.close();
    }
    } catch (Exception e) {

                }

        return list;
    } 

try this effort