Iterate cursor inside for loop SQLite android

Question

I am trying to build a chating app. I need to execute a SQLite query that returns all the groups the user is a member of. Then using those groups, for each one I need the some data.

Here are both methods:

public List<Contact_class> getAllGroupsFromUser() {
    List<String> grupos = new ArrayList<>();
    String user = ClassCOM.getUserLogged(context);
    SQLiteDatabase db = this.getReadableDatabase();
    String select_query = "SELECT gr."+KEY_GROUP+" FROM "+TABLE_GROUPS+" gr JOIN "+TABLE_USER_GROUP_REL+" rel ON rel."+KEY_ID_GROUP+" = gr."+KEY_ID_GROUP+" JOIN "+ TABLE_USERS +" user ON user."+KEY_ID_USER+" = rel."+KEY_ID_USER+" WHERE user." + KEY_USER + " = '"+user+"'";
    String grupo;

    Cursor cursor = db.rawQuery(select_query, null);
    if (cursor.moveToFirst()) {
        do {
            grupo = cursor.getString(0);
            grupos.add(grupo);
        } while (cursor.moveToNext());
    }
    cursor.close();
    db.close();

    return getGroupLastMessage(grupos);
}


private List<Contacto_class> getGroupLastMessage(List<String> grupos){
    List<Contacto_class> lista = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();
    String fecha, mensaje, usuario;

    for(String gr : grupos){
        String select_query = "SELECT "+KEY_DATE+", "+KEY_USER+", "+KEY_MENSAJE+" FROM "+TABLE_MESSAGES+" WHERE "+KEY_ID_GROUP+"='"+getGroupId(gr)+"' ORDER BY "+KEY_DATE +" DESC LIMIT 1";

        Cursor cursor = db.rawQuery(select_query, null);
        if (cursor.moveToFirst()) {
            fecha = cursor.getString(0);
            usuario = cursor.getString(1);
            mensaje = cursor.getString(2);
            Contacto_class contacto_class = new Contacto_class(gr, usuario, fecha, mensaje);
            lista.add(contacto_class);
        }
        cursor.close();
    }

    db.close();
    return lista;
}

When I run the code, I get this error:

E/AndroidRuntime: FATAL EXCEPTION: main
Process: es.monlau.smartschool, PID: 5719
java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/user/0/es.monlau.smartschool/databases/Smartschool
    at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257)
    at es.monlau.smartschool.handler.DBHandler.getGroupLastMessage(DBHandler.java:327)
    at es.monlau.smartschool.handler.DBHandler.getAllGroupsFromUser(DBHandler.java:315)
    at es.monlau.smartschool.Mensajeria.showContactList(Mensajeria.java:89)
    at es.monlau.smartschool.Mensajeria.onCreateView(Mensajeria.java:54)
    at android.support.v4.app.Fragment.performCreateView(Fragment.java:2346)
    at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1428)
    at android.support.v4.app.FragmentManagerImpl.moveFragmentToExpectedState(FragmentManager.java:1759)
    at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1827)
    at android.support.v4.app.BackStackRecord.executeOps(BackStackRecord.java:797)
    at android.support.v4.app.FragmentManagerImpl.executeOps(FragmentManager.java:2596)
    at android.support.v4.app.FragmentManagerImpl.executeOpsTogether(FragmentManager.java:2383)
    at android.support.v4.app.FragmentManagerImpl.removeRedundantOperationsAndExecute(FragmentManager.java:2338)
    at android.support.v4.app.FragmentManagerImpl.execSingleAction(FragmentManager.java:2215)
    at android.support.v4.app.BackStackRecord.commitNowAllowingStateLoss(BackStackRecord.java:649)
    at android.support.v4.app.FragmentPagerAdapter.finishUpdate(FragmentPagerAdapter.java:145)
    at android.support.v4.view.ViewPager.populate(ViewPager.java:1238)
    at android.support.v4.view.ViewPager.populate(ViewPager.java:1086)
    at android.support.v4.view.ViewPager.onMeasure(ViewPager.java:1616)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6580)
    at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1514)
    at android.widget.LinearLayout.measureVertical(LinearLayout.java:806)
    at android.widget.LinearLayout.onMeasure(LinearLayout.java:685)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6580)
    at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
    at android.view.View.measure(View.java:22002)
    at android.support.v4.widget.DrawerLayout.onMeasure(DrawerLayout.java:1059)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6580)
    at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
    at android.support.v7.widget.ContentFrameLayout.onMeasure(ContentFrameLayout.java:141)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6580)
    at android.support.v7.widget.ActionBarOverlayLayout.onMeasure(ActionBarOverlayLayout.java:400)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6580)
    at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6580)
    at android.widget.LinearLayout.measureChildBeforeLayout(LinearLayout.java:1514)
    at android.widget.LinearLayout.measureVertical(LinearLayout.java:806)
    at android.widget.LinearLayout.onMeasure(LinearLayout.java:685)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewGroup.measureChildWithMargins(ViewGroup.java:6580)
    at android.widget.FrameLayout.onMeasure(FrameLayout.java:185)
    at com.android.internal.policy.DecorView.onMeasure(DecorView.java:721)
    at android.view.View.measure(View.java:22002)
    at android.view.ViewRootImpl.performMeasure(ViewRootImpl.java:2410)
E/AndroidRuntime:     at android.view.ViewRootImpl.measureHierarchy(ViewRootImpl.java:1498)
    at android.view.ViewRootImpl.performTraversals(ViewRootImpl.java:1751)
    at android.view.ViewRootImpl.doTraversal(ViewRootImpl.java:1386)
    at android.view.ViewRootImpl$TraversalRunnable.run(ViewRootImpl.java:6733)
    at android.view.Choreographer$CallbackRecord.run(Choreographer.java:911)
    at android.view.Choreographer.doCallbacks(Choreographer.java:723)
    at android.view.Choreographer.doFrame(Choreographer.java:658)
    at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:897)
    at android.os.Handler.handleCallback(Handler.java:789)
    at android.os.Handler.dispatchMessage(Handler.java:98)
    at android.os.Looper.loop(Looper.java:164)
    at android.app.ActivityThread.main(ActivityThread.java:6541)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)

On this line:

Cursor cursor = db.rawQuery(select_query, null);

My guess is the problem is I open the cursor on the first iteration, then close it and open it back for the second interation. Can't I do that? How can I resolve this "double query" situation?

Thanks.

Answer 1

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

Mainly happens because of cursor.close();. Your code will be

Cursor cursor = db.rawQuery(select_query, null);
    if (cursor.moveToFirst()) {
        do {
            grupo = cursor.getString(0);
            grupos.add(grupo);
        } while (cursor.moveToNext());
    }

    return getGroupLastMessage(grupos);

And

if (cursor.moveToFirst()) {
            do {

            fecha = cursor.getString(0);
            usuario = cursor.getString(1);
            mensaje = cursor.getString(2);
            Contacto_class contacto_class = new Contacto_class(gr, usuario, fecha, mensaje);
            lista.add(contacto_class);

                } while (cursor.moveToNext());
        }
        cursor.close();
        // close db connection
        db.close();

If this problem still coming, Add Break-Point and do DEBUG.