Android SQLite error opening database

Question

I'm using a SQLiteOpenHelper and my class was working completely fine, but I did have an error in one of my methods where I did not close the database after getting the getCount() from a cursor. Now, everytime I start the app, I get the error:

    java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase:

I'm trying to close that connection but I'm unable to perform any methods with that class because I always get this error.

I actually recreated an entire new Android app and copied my source files over one by one, and I still received the same error.

In my MainActivity.java:

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    BillDatabaseHelper db = new BillDatabaseHelper(this);
    db.recreateDatabases(); // this is the line that causes the error

    viewPagerFragment = new ViewPagerFragment();
    transaction = manager.beginTransaction();
    transaction.add(R.id.placeholderMain, viewPagerFragment);
    transaction.commit();
}

In my DatabaseHelper.java:

public void recreateDatabases() {
    SQLiteDatabase db = this.getWritableDatabase();
    onCreate(db);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRANSACTIONS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_BILL_INFO);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_REMINDERS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_BILL_DATE_INFO);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PAYDATES);

    String strCreateTransactionsTableSQL =
            "CREATE TABLE " + TABLE_TRANSACTIONS + "("
            + KEY_TRAN_ID + " INTEGER PRIMARY KEY,"
            + KEY_TRAN_DESC + " TEXT,"
            + KEY_TRAN_AMOUNT + " REAL,"
            + KEY_TRAN_PURCHASE_DATE + " TEXT);";

    String strCreateBillInfoTableSQL =
            "CREATE TABLE " + TABLE_BILL_INFO + "("
            + KEY_BILL_ID + " INTEGER PRIMARY KEY,"
            + KEY_BILL_NAME + " TEXT,"
            + KEY_BILL_BALANCE + " FLOAT,"
            + KEY_BILL_DUE_DATE + " TEXT,"
            + KEY_BILL_MINIMUM + " FLOAT,"
            + KEY_BILL_CURRENT + " FLOAT);";

    String strCreateRemindersTableSQL =
            "CREATE TABLE " + TABLE_REMINDERS + "("
            + KEY_REM_ID + " INTEGER PRIMARY KEY,"
            + KEY_REM_NAME + " TEXT,"
            + KEY_REM_DESC + " TEXT,"
            + KEY_REM_LOCATION + " TEXT,"
            + KEY_REM_START + " TEXT);";

    String strCreateBillDateTableSQL =
            "CREATE TABLE " + TABLE_BILL_DATE_INFO + "("
            + KEY_BILLDATE_PK + " INTEGER PRIMARY KEY,"
            + KEY_BILLDATE_TOBILL_ID + " INTEGER,"
            + KEY_BILLDATE_BILLID + " INTEGER,"
            + KEY_BILLDATE_PAYDATE + " TEXT,"
            + KEY_BILLDATE_BILLDATE + " TEXT,"
            + KEY_BILLDATE_OWEDTODAY + " CHARACTER(1),"
            + KEY_BILLDATE_BALANCE + " FLOAT,"
            + KEY_BILLDATE_CURRENT + " FLOAT,"
            + "FOREIGN KEY(" + KEY_BILLDATE_PK + ") REFERENCES " + TABLE_BILL_INFO + "(" + KEY_BILL_ID + "));";

    String strCreatePaydatesSQL =
            "CREATE TABLE " + TABLE_PAYDATES + "("
            + KEY_PAYDATE_ID + " INTEGER PRIMARY KEY,"
            + KEY_PAYDATE_DATE + " TEXT);";

    db.execSQL(strCreateTransactionsTableSQL);
    db.execSQL(strCreateBillInfoTableSQL);
    db.execSQL(strCreateRemindersTableSQL);
    db.execSQL(strCreateBillDateTableSQL);
    db.execSQL(strCreatePaydatesSQL);
}

Where should I be closing this connection? Any help would be greatly appreciated.

Answer 1

You do not have to close a database connection, see:

Best place to close database connection

You can leave a database open like this:

public class YourDatabase extends SQLiteOpenHelper {

    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "yourdatabase.db";
    private SQLiteDatabase database;

    public YourDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // call this function once
    public void open() throws SQLException {
        database = this.getWritableDatabase();
    }

    private void create(SQLiteDatabase database) {
        database.execSQL("CREATE TABLE contacts (TEXT `name`, TEXT `number`)");
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        create(database);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
    }
}

Just call open() once, and you can then use the database attribute to write or read from the database, like this database.rawQuery(selectQuery, null);