Null Pointer Exception when calling getReadableDatabase()

Question

I fairly new to the whole SQLite android implementation and I am clueless on what to do to get rid of this error. Essentially what I am trying to do with my database is save user data (9 Array lists with a maximum of 20 elements in each of them). I have an app where users can plot points on a map and make a "Road Trip". They then have a choice to save that road trip. I am saving each road trip as a table and grabbing the information needed.

Here is my code. I am trying to call the method public ArrayList<String> getTableNames() to populate a spinner and it is failing on the first line in that method.

package com.example.roadtripplanner;

import java.util.ArrayList;

import com.google.android.gms.maps.model.LatLng;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 2;

    // Database Name
    private static final String DATABASE_NAME = "rtpDatabase";

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

    //Strings to create the first table.
    public static final String TABLE_COMMENTS = "comments";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_COMMENT = "comment";
    private static final String DATABASE_CREATE = "create table "
        + TABLE_COMMENTS + "(" + COLUMN_ID
        + " integer primary key autoincrement, " + COLUMN_COMMENT
        + " text not null);";
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        db.close();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
        onCreate(db);
    }

    public void addTable(String statment) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(statment);
        db.close();

    }

    public void addTableValues(String tableName, ArrayList<LatLng> latLngList,         ArrayList<String> stringList1, ArrayList<String> stringList2){
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("latLng", latLngList.get(0).latitude + latLngList.get(0).longitude);
        values.put("string1", stringList1.get(0));
        values.put("string2", stringList2.get(0));

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

    public String getTableValues(String tableName){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cur = db.query(tableName, new String[] {"latLng", "string1",
            "string2"}, null, null, null, null, null);
        String foo = "";
        cur.moveToFirst(); // move your cursor to first row
        // Loop through the cursor
        while (cur.isAfterLast() == false) {
            foo = "0: " + cur.getString(0) + "1: " + cur.getString(1) + "2: " + cur.getString(2);
            System.out.println("0: " + cur.getString(0)); // will fetch you the data
            System.out.println("1: " + cur.getString(1));
            System.out.println("2: " + cur.getString(2));
            cur.moveToNext();
        }

        cur.close();
        return foo;
    }

    public ArrayList<String> getTableNames(){
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<String> tableNames = new ArrayList<String>();
        Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
        if (c.moveToFirst())
        {
            c.moveToNext(); //Skipping first result which is not a table name.
            while ( !c.isAfterLast() ){
                tableNames.add( c.getString( c.getColumnIndex("name")) );
               c.moveToNext();
            }
        }
        c.close();
        return tableNames;
    }

    public void dropTables(){
        SQLiteDatabase db = this.getWritableDatabase();

        for(int i=0; i < getTableNames().size(); i++){
            db.execSQL("DROP TABLE IF EXISTS '" + getTableNames().get(i) + "'");
            db.close();
        }
    }

    //Checks to see if a table already exists
    boolean doesTableExist(String tableName)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        if (tableName == null || db == null || !db.isOpen())
        {
            return false;
        }
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?", new String[] {"table", tableName});
        if (!cursor.moveToFirst())
        {
            return false;
        }
        int count = cursor.getInt(0);
        cursor.close();
        return count > 0;
    }

}

And I'm calling public ArrayList<String> getTableNames() in another class here...

DatabaseHandler db = new DatabaseHandler(this);

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.startpage);
    mContext = this;

    if(db.getTableNames().size() == 0){
        trips_options.add("No Road Trips Saved");
    } else {
        for(int i=0; i < db.getTableNames().size(); i++){
            String temp = db.getTableNames().get(i);
            //Removing underscores for the time being to make the titles look better for the user.
            //They will be put back when a user selected a trip.
            temp = temp.replaceAll("_", " ");
            trips_options.add(db.getTableNames().get(i));
        }
    }

    tripsAdapter = new ArrayAdapter<String>(StartPageActivity.this, android.R.layout.simple_spinner_item,trips_options);
    tripsSpinner = (Spinner)findViewById(R.id.spinner_trips);
    tripsSpinner.setAdapter(tripsAdapter);
    addListenerOnButton();
}

LogCat....

07-20 16:28:33.693: E/AndroidRuntime(11397): FATAL EXCEPTION: main
07-20 16:28:33.693: E/AndroidRuntime(11397): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.roadtripplanner/com.example.roadtripplanner.StartPageActivity}: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/com.example.roadtripplanner/databases/rtpDatabase
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2100)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2132)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.ActivityThread.access$600(ActivityThread.java:139)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1231)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.os.Handler.dispatchMessage(Handler.java:99)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.os.Looper.loop(Looper.java:137)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.ActivityThread.main(ActivityThread.java:5021)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at java.lang.reflect.Method.invokeNative(Native Method)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at java.lang.reflect.Method.invoke(Method.java:511)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:789)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:556)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at dalvik.system.NativeStart.main(Native Method)
07-20 16:28:33.693: E/AndroidRuntime(11397): Caused by: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/com.example.roadtripplanner/databases/rtpDatabase
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.database.sqlite.SQLiteDatabase.endTransaction(SQLiteDatabase.java:520)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:263)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at com.example.roadtripplanner.DatabaseHandler.getTableNames(DatabaseHandler.java:85)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at com.example.roadtripplanner.StartPageActivity.onCreate(StartPageActivity.java:52)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.Activity.performCreate(Activity.java:5058)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
07-20 16:28:33.693: E/AndroidRuntime(11397):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2064)
07-20 16:28:33.693: E/AndroidRuntime(11397):    ... 11 more

Answer 1

Get rid of most of your close() methods that you are calling on SQLiteDatabase. Notably, do not call close() on the database passed to you in onCreate() of SQLiteOpenHelper, and do not keep opening and closing the database.

SQLiteOpenHelper holds onto the database you retrieve with getReadableDatabase()/getWritableDatabase(), and the point is for you to reuse that opened SQLiteDatabase object, particularly as you do work across multiple threads.