select in where clause force closing android app -sqlite-

Question

I am trying to query my database in sqlite for a android application but the app force closes. I have a list view that displays information about people. (This information was retrieved by querying my whole database which works). And now when the user clicks on an item in the list view i am trying to open another activity with that users information prefilled on the screen. To do this i am using the position variable in the on item click listener and then selecting everything from my table where my ID in my table is equal to the position.

Here is my database helper class

package com.example.assignment1;

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

import java.util.ArrayList;

public class databaseManager {
    public static final String DB_NAME = "Friends";
    public static final String DB_TABLE = "friendList";
    public static final int DB_VERSION = 1;
    private static final String CREATE_TABLE = "CREATE TABLE " + DB_TABLE +
            " (code INTEGER PRIMARY KEY AUTOINCREMENT , friendName TEXT NOT NULL, friendAge INTEGER, friendGender TEXT NOT NULL, friendAddress TEXT NOT NULL);";
    private SQLHelper helper;
    private SQLiteDatabase db;
    private Context context;
    public databaseManager(Context c) {
        this.context = c;
        helper = new SQLHelper(c);
        this.db = helper.getWritableDatabase();
    }
    public databaseManager openReadable() throws android.database.SQLException {
        helper = new SQLHelper(context);
        db = helper.getReadableDatabase();
        return this;
    }
    public void close() {
        helper.close();
    }
    public boolean addRow( String name, int age,String gender, String address) {

        ContentValues newFriend = new ContentValues();
        newFriend.put("friendName", name);
        newFriend.put("friendAge", age);
        newFriend.put("friendGender", gender);
        newFriend.put("friendAddress", address);

        db.insert(DB_TABLE,null,newFriend);
        db.close();
        return true;
    }
    
    public ArrayList<String> retrieveRow(int ID){
        ArrayList<String> result = new ArrayList<String>();
        String[] columns = new String[] {"code", "friendName", "friendAge","friendGender","friendAddress"};
        Cursor cursor = db.query(DB_TABLE, columns, "code" + "=" + ID, null, null, null, null);
        cursor.moveToFirst();
        while (cursor.isAfterLast() == false) {
            result.add(cursor.getInt(0) + ", " + cursor.getString(1) + ", " +
                    cursor.getInt(2) + ", "+ cursor.getString(3) + ", "+cursor.getString(4)+ "\n");
            cursor.moveToNext();
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return result;
    }
    public ArrayList<String>retrieveRows() {
        ArrayList<String> result = new ArrayList<String>();
        String[] columns = new String[] {"code", "friendName", "friendAge","friendGender","friendAddress"};
        Cursor cursor = db.query(DB_TABLE, columns, null, null, null, null, null);
        cursor.moveToFirst();
        while (cursor.isAfterLast() == false) {
           result.add(cursor.getInt(0) + ", " + cursor.getString(1) + ", " +
                    cursor.getInt(2) + ", "+ cursor.getString(3) + ", "+cursor.getString(4)+ "\n");
            cursor.moveToNext();
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return result;
    }

    public class SQLHelper extends SQLiteOpenHelper {
        public SQLHelper (Context c) {
            super(c, DB_NAME, null, DB_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE);
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w("Products table", "Upgrading database i.e. dropping table and re-creating it");
                    db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
            onCreate(db);
        }
    }

}

as you can see i copied the same code i used for the retrieve all function and added a where clause.

and this is how i am using this function

package com.example.assignment1;

import android.app.AppComponentFactory;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;

import org.w3c.dom.Text;

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

public class viewFriends extends AppCompatActivity {
    private ListView listView;
    private databaseManager mydb;
    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        setContentView(R.layout.viewfriendlist);
        listView = (ListView)findViewById(R.id.listView);
        mydb = new databaseManager(viewFriends.this);
        mydb.openReadable();
        displayFriendList();

        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                String pos = (String)parent.getItemAtPosition(position);
            int poss = position +1;

               Intent intent = new Intent(getApplicationContext(),editOrDelete.class);
               ArrayList<String> result1=mydb.retrieveRow(poss);


                Toast.makeText(getApplicationContext(),pos,Toast.LENGTH_SHORT).show();
               //startActivity(intent);

            }
        });
        mydb.close();



    }

    public void displayFriendList(){
        ArrayList<String> result = mydb.retrieveRows();
        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
                android.R.layout.simple_list_item_1, result);
        listView.setAdapter(adapter);
    }



}

and this is my logcat

 08-21 19:41:39.719 17562-17562/com.example.assignment1 E/AndroidRuntime: FATAL EXCEPTION: main
        Process: com.example.assignment1, PID: 17562
        java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/user/0/com.example.assignment1/databases/Friends
            at android.database.sqlite.SQLiteClosable.acquireReference(SQLiteClosable.java:55)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1158)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)
            at com.example.assignment1.databaseManager.retrieveRow(databaseManager.java:53)
            at com.example.assignment1.viewFriends$1.onItemClick(viewFriends.java:42)
            at android.widget.AdapterView.performItemClick(AdapterView.java:310)
            at android.widget.AbsListView.performItemClick(AbsListView.java:1145)
            at android.widget.AbsListView$PerformClick.run(AbsListView.java:3042)
            at android.widget.AbsListView$3.run(AbsListView.java:3879)
            at android.os.Handler.handleCallback(Handler.java:739)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:148)
            at android.app.ActivityThread.main(ActivityThread.java:5417)
            at java.lang.reflect.Method.invoke(Native Method)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

I would really appreciate any help

Answer 1

The problem is that you call close() on your db object right after you populate the list, so when you attempt to reuse that object later to get the data of the specific person, you get an error. The fact that you close it after you set the listener does not matter since the code of the listener is called later. Notice the following in your error message:

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

You have the option of either reconstructing the database object whenever you click the list item and then closing it again, or close it once and for all when your application terminates.