Android SQLite same cursor for multiple tables:

Question

In my Android app, I have the following SQLite Interface Class

package com.songs.lookup;

import java.util.ArrayList;
import java.util.List;

import com.songs.MainActivity2;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class CacheDB {

    public CacheDB(Context context){
        this.dbHelper = new CacheDBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    private Cursor songCursor;
    private Cursor tuneCursor;
    private Cursor personCursor;
    private Context context;
    private CacheDBHelper dbHelper;
    private SQLiteDatabase db;
    private static final String DATABASE_NAME = "SONGS";
       private static final int DATABASE_VERSION = 1;
       private static final String SONG_TABLE_NAME = "songs";
       private static final String TUNE_TABLE_NAME = "tunes";
       private static final String PERSON_TABLE_NAME = "persons";
       private static final String COLUMN_NAME = "name";
        String selectsongQuery = "SELECT  * FROM " + SONG_TABLE_NAME;
        String selecttuneQuery = "SELECT  * FROM " + TUNE_TABLE_NAME;
        String selectpersonQuery = "SELECT  * FROM " + PERSON_TABLE_NAME;



       private static final String SONG_TABLE_CREATE =
                "CREATE TABLE " + SONG_TABLE_NAME + " (" +
                COLUMN_NAME + " TEXT);";

       private static final String TUNE_TABLE_CREATE =
                        "CREATE TABLE " + TUNE_TABLE_NAME + " (" +
                        COLUMN_NAME + " TEXT);";

       private static final String PERSON_TABLE_CREATE =
                        "CREATE TABLE " + PERSON_TABLE_NAME + " (" +
                        COLUMN_NAME + " TEXT);";

   class CacheDBHelper extends SQLiteOpenHelper{ 
     SQLiteDatabase readDb = null;
     SQLiteDatabase writeDb = null;
   public CacheDBHelper(Context context, String name, CursorFactory factory,
            int version) {
       super(context, DATABASE_NAME, null, DATABASE_VERSION);
       System.out.println("After the cachedbhelper");

    }

        @Override
        public void onCreate(SQLiteDatabase db) {
            System.out.println("Here inside the oncreate of cacheDBHelper");
            db.execSQL(SONG_TABLE_CREATE);
            db.execSQL(TUNE_TABLE_CREATE);
            db.execSQL(PERSON_TABLE_CREATE);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }

        public SQLiteDatabase getReadDb()
           {
            if(readDb == null)
              readDb = this.getReadableDatabase();
            else; 
            return  readDb;
           }
        public SQLiteDatabase getWriteDb()
           {
            if(writeDb == null)
              writeDb = this.getReadableDatabase();
            else;
            return  writeDb;
           }
   }

   @SuppressLint("NewApi")
public void performOperation(String Operation, String table, ArrayList<String> array1)
   {
       SQLiteDatabase db = dbHelper.getWriteDb();

       String INSERT = "insert into "   
                + table + " (" + COLUMN_NAME + ") values (?)";

       String DELETE = "delete from " + table; 

       String FETCH = "select DISTINCT(" + COLUMN_NAME + "from " + table + ")";

       db.beginTransaction();

       SQLiteStatement dbStmt = db.compileStatement(Operation.equals("INSERT") ? INSERT : DELETE);

       if(Operation.equals("INSERT"))
       {  
            int aSize = array1.size();



                for (int i = 0; i < aSize; i++) {
                    dbStmt.bindString(1, array1.get(i));
                    dbStmt.executeInsert();
            }
       }

       if(Operation.equals("DELETE"))
       {
           dbStmt.executeUpdateDelete();
       }


       db.setTransactionSuccessful();
       db.endTransaction();


       try {
            db.close();
            dbHelper.close();
           } catch (Exception e) {
            e.printStackTrace();
           }
        }

   public List<String> fetchData(String table)
   {
       List<String> result = new ArrayList<String>();
       SQLiteDatabase db = this.dbHelper.getReadDb(); 
       result = this.fetchDatafromDB(table, db);

       dbHelper.close();
       return result;
   }


   public List<String> fetchDatafromDB(String table, SQLiteDatabase db) {
        List<String> list = new ArrayList<String>(); 
        String selectQuery = "SELECT  * FROM " + table;    
     System.out.println("The cursor plac eeee");

       if(table == "song")
       {
         songCursor = db.rawQuery(selectQuery, null);
         list = parseCursor(songCursor);
         songCursor.close();
       }
       else if(table == "tune")
       {
           tuneCursor = db.rawQuery(selectQuery, null);
         list = parseCursor(tuneCursor);
         tuneCursor.close();
       }
       else
       {
           personCursor = db.rawQuery(selectQuery, null);
         list = parseCursor(personCursor);
         personCursor.close();
       }
         db.close();
         return list;
}

  public List<String> parseCursor(Cursor cursor)
  {
      List<String> list = new ArrayList<String>();
        if (cursor.moveToFirst()) {
            do {
                list.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
            cursor.close();
        return list; 
  }

}

In another class where I am looking up the data from the DB I am calling the fetchData method consecutively for all the three tables. The first table operations are working correctly. However for the second table, I am getting the following error:

04-24 06:27:50.706: E/AndroidRuntime(2354): FATAL EXCEPTION: main
04-24 06:27:50.706: E/AndroidRuntime(2354): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.songs/com.songs.MainActivity2}: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/com.songs/databases/songS
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.ActivityThread.access$600(ActivityThread.java:130)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.os.Handler.dispatchMessage(Handler.java:99)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.os.Looper.loop(Looper.java:137)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.ActivityThread.main(ActivityThread.java:4745)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at java.lang.reflect.Method.invokeNative(Native Method)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at java.lang.reflect.Method.invoke(Method.java:511)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at dalvik.system.NativeStart.main(Native Method)
04-24 06:27:50.706: E/AndroidRuntime(2354): Caused by: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/com.songs/databases/songS
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1310)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at com.songs.lookup.CacheDB.fetchDatafromDB(CacheDB.java:181)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at com.songs.lookup.CacheDB.fetchData(CacheDB.java:155)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at com.songs.lookup.LookUpData.getData(LookUpData.java:38)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at com.songs.MainActivity2.onCreate(MainActivity2.java:66)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.Activity.performCreate(Activity.java:5008)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
04-24 06:27:50.706: E/AndroidRuntime(2354):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
04-24 06:27:50.706: E/AndroidRuntime(2354):     ... 11 more

There have been previous questions which are similiar but I have not seen any such question which create or fetch from multiple tables.

Essentially the line where it is failing as

       personCursor = db.rawQuery(selectQuery, null);

Before I was having a single cursor and initialized it to null, and then thought I needed separate cursors which could be the issue but it does not seem to be the case.

Femi · Accepted Answer · 2013-04-26 03:51:23Z

1

You're calling db.close() and dbHelper.close() in multiple places (performOperation,fetchDataFromDB, fetchData). Once you call close you can no longer query the database without creating a brand new DbHelper. That's why you're getting the exception, which tells you that the database is closed. Remove your close calls and you should be fine: there should be an explicit close method that you call in your Activity's onDestroy.

answered Apr 26, 2013 at 3:51

Femi

64.8k8 gold badges120 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

H P Over a year ago

I tried the fixes you mentioned and I am not getting the stack trace any more. I removed all the close statements and it works fine now. But now when I switch off the device and turn it on without having the server running, I get the error at the following lines: The next line after dbStmt.executeInsert(); and the error is Caused by: java.lang.NullPointerExceptionat android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:224) at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:188)

Femi Over a year ago

Not quite sure without a full stack trace, but either you're probably not calling close() like you should be (you should call close() from your activity's onDestroy) or you're passing an invalid context into the helper, like an Activity that is no longer valid.

H P Over a year ago

For some reason I am not getting the null pointer exception but when I do a fetch of the data immediately after inserting it, I get size of the data as 0. Do I have to make my CacheDB class or the CacheDBHelper static? What is the correct way to reuse the CacheDBHelper? Should I create a new one every time ?

DGN · Accepted Answer · 2013-04-26 04:52:07Z

0

Please check whether you have mistyped the table names... The table is created with names "tunes". In fetchdatafromDB(), you are checking with the name "tune". Change it to "tunes" and change the other names also.

  if(table == "songs")
   {
     songCursor = db.rawQuery(selectQuery, null);
     list = parseCursor(songCursor);
     songCursor.close();
   }

Hope it helps.

answered Apr 26, 2013 at 4:52

DGN

7023 silver badges12 bronze badges

1 Comment

H P Over a year ago

There are three different tables. Not sure what you're refering to.

Collectives™ on Stack Overflow

Android SQLite same cursor for multiple tables:

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related