SQLite row IDs do not match the ListView row IDs

Question

I have an SQLite database and a ListView displaying in the main activity. In the ListView, I display the details of each list number and upon pressing the list item, a new activity opens up which displays the details in a TextView.

I have a delete function for each of these ListViews and they are not working properly. Essentially, a unique ID is given to each created ListView, which is how the SQLite databases should work. However, upon deleting a row within the ListView, the unique ID increases but the row ID deletes that number and moves everything up. This means that after you delete an item, the row IDs and the database IDs no longer match up and when trying to select an item within the ListView, it crashes. This is an extract from my database adapter class:

 public Integer deleteStat(Integer id) {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(STATS_TABLE, "id = ? ", new String[]{Integer.toString(id)});
}

This is my first attempt in the main activity. I got some information from this post but a failed to get the correct integer and received syntax errors:

obj.setOnItemClickListener(new OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {

 TextView pos = (TextView) findViewById(R.id.editTitle);
 displayText(Integer.parseInt(pos.getText().toString()));
        }
    });
}

public void displayText(int pos)
{
    pos++;
    String s = "";
    s += pos;

    Intent intent = new Intent(getApplicationContext(), DisplayStatsActivity.class);
    intent.putExtra("id", s);
    startActivity(intent);

}

This is my second attempt in the main activity. I used this tutorial to help me. The code I used from that website ruined the entire delete functionality:

 obj.setOnItemClickListener(new OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
           int id_To_Search = arg2 + 1;
           int id_To_Search = arg0.getCount() - arg2;

           Bundle dataBundle = new Bundle();
            dataBundle.putInt("id", id_To_Search);

            Intent intent = new Intent(getApplicationContext(), DisplayStatsActivity.class);

           intent.putExtras(dataBundle);
           startActivity(intent);

How do I get the database IDs to match up with the row IDs? Any suggestions on how to do that?

EDIT: Added in Hashmap Code. When I press on a ListView Item, nothing happens

 @Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_home);

    mydb = new StatisticsDbAdapter(this);
    ArrayList array_list = mydb.getAllStats();
    ArrayAdapter arrayAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, array_list);

    obj = (ListView) findViewById(R.id.listView1);
    obj.setAdapter(arrayAdapter);
    /*
     New code starts here
    */
   Bundle extras = getIntent().getExtras();
    if (extras != null) {
        int Value = extras.getInt("id");

        if (Value > 0) {
            //means this is the view part not the add contact part
            Cursor cursor = mydb.getData(Value);
            hashMap.put(cursor.getString(1), cursor.getInt(0));

 obj.setOnItemClickListener(new OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> adapterView, View view, int arg2, long arg3) {
     TextView textView = (TextView) view.findViewById(R.id.editTitle);
                    Bundle dataBundle = new Bundle();
                    dataBundle.putInt("id", hashMap.get(textView.getText().toString()));

                    Intent intent = new Intent(getApplicationContext(), DisplayStatsActivity.class);

                    intent.putExtras(dataBundle);
                    startActivity(intent);
 }
            });
        }
    }
}

Answer 1

You should never pass a ListView position to delete a row in a database. I have done that many times before. First of all, within the class you are managing the click events, create a new HashMap. This HashMap will be used to store our row IDs from the database and we'll refer to that rather than the ListView item position. To create it, type this code:

public HashMap<String, Integer> hashMap = new HashMap<>();

Once you have done that, in the function where you display all of the rows in the ListView, you must also load up the same items into the HashMap like this:

hashMap.put(cursor.getString(1), cursor.getInt(0));

Please note that cursor.getString(1) might not be what you used. Please replace the 1 with your appropriate column number that has the name stored within it. cursor.getInt(0) gets the row ID from the database and the column number might be different for you, too. Now, within our OnClickListener function, we can type:

obj.setOnItemClickListener(new OnItemClickListener() {
    @Override
    public void onItemClick(AdapterView<?> adapterView, View view) {
       TextView textView = (TextView) view.findViewById(R.id.editTitle);
       Bundle bundle = new Bundle();
       bundle.putInt("id", hashMap.get(textView.getText().toString()));

       Intent intent = new Intent(getApplicationContext(), DisplayStatsActivity.class);

       intent.putExtras(bundle);
       startActivity(intent);
}

Please note that the hashMap variable needs to be within the same class where you manage the click events of your ListView or you will get mistakes. Some of the parametres that you originally passed were removed since you don't need them. Also, I found this to work much better using a RecyclerView but that's now up to you. If you're having problems, please don't hesitate to comment below and I'll try to answer as soon as possible.