Android SQLiteDatabase - Listview and database mismatch ID after deleting value from the database

Question

Hello every one I am new to android SQLiteDatabase. I am following tutorial from this site ORIGINAL CODE . The below code is about Add, update, Delete name from the database.

Everything is working fine. I am able to add, update and delete name from the database.

Problem: For example I added 4 names.

ID name 
1   A
2   B
3   C
4   D

when I delete A it get deleted from the listview and database. It is updated in listview. Now the database looks like this

ID name 
1  null
2   B
3   C
4   D

And Listview looks like this

B
C
D

So when I click on B to delete or edit it shows nothing in my edit and delete view but when I click on C it shows me B in delete view.

I gone through all the solutions which is available in stackoverflow and I know it is repeated questions but is there any solution for this example. I want when I click on B it should show B not null and when I click on C it should show C, After deleting A.

NOTE: Can any one fix the code please. I am very sure there are many developers who are looking for to fix this issue. Specially beginners like me. It would be great help.Thank you in advance.

DBHelper

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

public ArrayList<String> getAllCotacts() {

    ArrayList<String> array_list = new ArrayList<String>();
    //hp = new HashMap();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery( "select * from contacts", null );
    res.moveToFirst();

    while(res.isAfterLast() == false){
        array_list.add(res.getString(res.getColumnIndex(CONTACTS_COLUMN_NAME)));
        res.moveToNext();
    }
    return array_list;
}

devicedetailsActivity

public class devicedetailsActivity extends AppCompatActivity {
    public final static String EXTRA_MESSAGE = "MESSAGE";
    private ListView obj;
    DBHelper mydb;
    ArrayList array_list ;
    public ArrayAdapter arrayAdapter;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.devicedetails_layout);
        mydb = new DBHelper(this);
    }
    public void onResume() {

        refreshList();
        super.onResume();
    }

    public void refreshList() {
        array_list = mydb.getAllCotacts();
        arrayAdapter=new ArrayAdapter(this,R.layout.simple_list_item_1, array_list);

        obj = (ListView)findViewById(R.id.listView1);
        obj.setAdapter(arrayAdapter);
        obj.setOnItemClickListener(new AdapterView.OnItemClickListener(){
            @Override
            public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,long arg3) {
                // TODO Auto-generated method stub
                int id_To_Search = arg2 + 1;

                Bundle dataBundle = new Bundle();
                dataBundle.putInt("id", id_To_Search);
                Intent intent = new Intent(getApplicationContext(),DetailsActivity.class);
                intent.putExtras(dataBundle);
                startActivity(intent);


            }
        });
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.menu_main, menu);
        getMenuInflater().inflate(R.menu.home, menu);

        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item){
        super.onOptionsItemSelected(item);

        switch(item.getItemId()) {
            case R.id.item1:Bundle dataBundle = new Bundle();
                dataBundle.putInt("id", 0);

                Intent intent = new Intent(getApplicationContext(),DetailsActivity.class);
                intent.putExtras(dataBundle);

                startActivity(intent);
                return true;
            case R.id.item2: dataBundle = new Bundle();
                dataBundle.putInt("id", 0);
                 intent = new Intent(getApplicationContext(),MainActivity.class);
                intent.putExtras(dataBundle);
                startActivity(intent);
                return true;
            default:
                return super.onOptionsItemSelected(item);
        }
    }
}

DetailsActivity

    public class DetailsActivity extends AppCompatActivity {
    private DBHelper mydb ;
    ArrayAdapter arrayAdapter;
    TextView name ;
    int id_To_Update = 0;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.details);
        name = (TextView) findViewById(R.id.editTextName);
        mydb = new DBHelper(this);
        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 rs = mydb.getData(Value);
                if(rs.moveToFirst()) {
                    id_To_Update = Value;
                    rs.moveToFirst();
                    String nam = rs.getString(rs.getColumnIndex(DBHelper.CONTACTS_COLUMN_NAME));
                    Button b = (Button)findViewById(R.id.button1);
                    b.setVisibility(View.INVISIBLE);
                    name.setText((CharSequence)nam);
                    name.setFocusable(false);
                    name.setClickable(false);
                }
                if (!rs.isClosed())  {
                    rs.close();
                }
            }
        }
    }
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        Bundle extras = getIntent().getExtras();
        if(extras !=null) {
            int Value = extras.getInt("id");
            if(Value>0){
                getMenuInflater().inflate(R.menu.display_contact, menu);
            } else{
                getMenuInflater().inflate(R.menu.back, menu);
            }
        }
        return true;
    }
    public boolean onOptionsItemSelected(MenuItem item) {
        super.onOptionsItemSelected(item);
        switch(item.getItemId()) {
            case R.id.Edit_Contact:
                Button b = (Button)findViewById(R.id.button1);
                b.setVisibility(View.VISIBLE);
                name.setEnabled(true);
                name.setFocusableInTouchMode(true);
                name.setClickable(true);
                return true;

            case R.id.Delete_Contact:
                AlertDialog.Builder builder = new AlertDialog.Builder(this);
                builder.setMessage(R.string.deleteContact)
                        .setPositiveButton(R.string.yes, new DialogInterface.OnClickListener() {
                            public void onClick(DialogInterface dialog, int id) {
                                mydb.deleteContact(id_To_Update);
                                Toast.makeText(getApplicationContext(), "Deleted Successfully",
                                        Toast.LENGTH_SHORT).show();
                                Intent intent = new Intent(getApplicationContext(),devicedetailsActivity.class);
                                startActivity(intent);
                            }
                        })

                        .setNegativeButton(R.string.no, new DialogInterface.OnClickListener() {
                            public void onClick(DialogInterface dialog, int id) {
                                // User cancelled the dialog
                            }
                        });
                AlertDialog d = builder.create();
                d.setTitle("Are you sure");
                d.show();
                return true;
            case R.id.back:
                Intent intent = new Intent(getApplicationContext(),devicedetailsActivity.class);
                startActivity(intent);
                return  true;
            default:
                return super.onOptionsItemSelected(item);
        }
    }
    public void run(View view) {
        Bundle extras = getIntent().getExtras();
        if(extras !=null) {
            int Value = extras.getInt("id");
            if(Value>0){
                if(mydb.updateContact(id_To_Update,name.getText().toString()
                        )){
                    Toast.makeText(getApplicationContext(), "Updated", Toast.LENGTH_SHORT).show();
                    Intent intent = new Intent(getApplicationContext(),devicedetailsActivity.class);
                    startActivity(intent);
                } else{
                    Toast.makeText(getApplicationContext(), "not Updated", Toast.LENGTH_SHORT).show();
                }
            } else{
                if(mydb.insertContact(name.getText().toString())){
                    Toast.makeText(getApplicationContext(), "done",
                            Toast.LENGTH_SHORT).show();
                } else{
                    Toast.makeText(getApplicationContext(), "not done",
                            Toast.LENGTH_SHORT).show();
                }
                Intent intent = new Intent(getApplicationContext(),devicedetailsActivity.class);
                startActivity(intent);
            }
        }
    }
}

Answer 1

The Issue

The 3rd argument (arg2) to the onItemClickListener is the position (as will be the 4th unless you use a Cursor Adapter).

The position will never equate to the id (unless you forced id's to have certain values) that is because the first item has a position of 0. The id of the first row will be 1. If you start deleting rows then life gets more complicated as you have found.

Soultions (in theory)

What you have to do is get the correct id.

You could use a complimentary ArrayList of Longs ArrayList<Long> or an Array of longs long[] (you need two methods to build the ArrayList<String> and the ArrayList<long> or long[]). However if you used a filter then this may then also be out of sync. As such this method is not really recommended.

You could use a Cursor Adapter and a Cursor as the source for the ListView and then the 4th parameter would be the id (requires that a column named _id which should be the row's id). I'd Recommended using Cursors as there is no need for intermediate objects

You could use an ArrayList of objects ArrayList<your_object>, in which case the object would have at least two members id and name. You could then use the Adapter's getItem(arg2) method to retrieve the object and then get the id from the object.

Working Example with 2 Solutions

The following is code for an App that displays 3 Listviews the first sourced by a String ArrayList (what you currently have), the second by an object ArrayList (an appropriate object i.e. a MyTableObject ArrayList) and the third via a Cursor. The data displayed being identical.

Clicking on an item displays the values that can be obtained (the values that could be passed to a deletion).

• the first ListView only the position can be obtained and that is displayed (as if it were the ID),
• the second ListView shows the ID obtained from the object and the ID obtained from the position (they will not match)

• the 3rd ListView shows the ID obtained via the Cursor (equivalent to the object) and the ID obtained from the 4th parameter, they always match.

• Note each time the App is run more data will be added.

MainActivity.java

public class MainActivity extends AppCompatActivity {

    DatabaseHelper mDBHlpr;

    ListView mListView01,mListVeiw02,mListView03;

    ArrayAdapter<String> mAdapterStringArrayList;
    ArrayAdapter<MyTableObject> mAdapterMyTableObjectArrayList;
    SimpleCursorAdapter mAdapterCursor;

    ArrayList<String> mMyTableListAsStrings;
    ArrayList<MyTableObject> mMyTableAsObjects;
    Cursor mMyTableListAsCursor;

    Context mContext;

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

        mListView01 = this.findViewById(R.id.listview01);
        mListVeiw02 = this.findViewById(R.id.listview02);
        mListView03 = this.findViewById(R.id.listview03);

        mDBHlpr = new DatabaseHelper(this);
        mDBHlpr.addRow("Fred");
        mDBHlpr.addRow("Bert");
        mDBHlpr.addRow("Harry");
        mDBHlpr.addRow("Fred");

        //String Array List
        mMyTableListAsStrings = mDBHlpr.getAllAsStringArrayList();
        mAdapterStringArrayList = new ArrayAdapter<>(
                this,
                android.R.layout.simple_list_item_1,
                mMyTableListAsStrings
        );
        mListView01.setAdapter(mAdapterStringArrayList);


        //Object Array List
        mMyTableAsObjects = mDBHlpr.getAllAsMyTableObjectArrayList();
        mAdapterMyTableObjectArrayList = new ArrayAdapter<>(
                this,
                android.R.layout.simple_list_item_1,
                mMyTableAsObjects
        );
        mListVeiw02.setAdapter(mAdapterMyTableObjectArrayList);

        // Cursor
        mMyTableListAsCursor = mDBHlpr.getAllAsCursor();
        mAdapterCursor = new SimpleCursorAdapter(
                this,
                android.R.layout.simple_list_item_1,
                mMyTableListAsCursor,
                new String[]{DatabaseHelper.COL_MYTABLE_NAME},
                new int[]{android.R.id.text1},
                0
        );
        mListView03.setAdapter(mAdapterCursor);


        mListView01.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
                String name = mAdapterStringArrayList.getItem(position);
                Toast.makeText(
                        mContext,
                        "Name is " + name  +
                                ". ID is " + String.valueOf(id) +
                                " (note may not match)",
                        Toast.LENGTH_SHORT
                ).show();
            }
        });

        mListVeiw02.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
                MyTableObject mytable = mAdapterMyTableObjectArrayList.getItem(position);
                String name = mytable.getName();
                long id_in_object = mytable.getId();
                Toast.makeText(
                        mContext,
                        "Name is " + name  +
                                ". ID from object is " + String.valueOf(id_in_object) +
                                ". ID from adapter is " + String.valueOf(id),
                        Toast.LENGTH_SHORT
                ).show();
            }
        });

        mListView03.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
                Cursor csr = mAdapterCursor.getCursor(); // already positioned
                String name = csr.getString(csr.getColumnIndex(DatabaseHelper.COL_MYTABLE_NAME));
                long id_in_cursor = csr.getLong(csr.getColumnIndex(DatabaseHelper.COl_MYTABLE_ID));
                Toast.makeText(
                        mContext,
                        "Name is " + name  +
                                ". ID from object is " + String.valueOf(id_in_cursor) +
                                ". ID from adapter is " + String.valueOf(id),
                        Toast.LENGTH_SHORT
                ).show();
            }
        });
    }
}

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;
    public static final String TB_MYTABLE = "mytable";
    public static final String COl_MYTABLE_ID = BaseColumns._ID; //<<<< use standard android id column name
    public static final String COL_MYTABLE_NAME = "_name";

    private static final String mytable_crtsql =
            "CREATE TABLE IF NOT EXISTS " + TB_MYTABLE +
                    "(" +
                    COl_MYTABLE_ID + " INTEGER PRIMARY KEY, " +
                    COL_MYTABLE_NAME + " TEXT " +
                    ")";

    SQLiteDatabase mDB;

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(mytable_crtsql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    public long addRow(String name) {
        ContentValues cv = new ContentValues();
        cv.put(COL_MYTABLE_NAME,name);
        return mDB.insert(TB_MYTABLE,null,cv);
    }

    public ArrayList<String> getAllAsStringArrayList() {
        ArrayList<String> rv = new ArrayList<>();
        Cursor csr = mDB.query(
                TB_MYTABLE,
                null,
                null,
                null,
                null,
                null,
                null
        );
        while (csr.moveToNext()) {
            rv.add(csr.getString(csr.getColumnIndex(COL_MYTABLE_NAME)));
        }
        csr.close();
        return rv;
    }

    public ArrayList<MyTableObject> getAllAsMyTableObjectArrayList() {
        ArrayList<MyTableObject> rv = new ArrayList<>();
        Cursor csr = mDB.query(
                TB_MYTABLE,
                null,
                null,
                null,
                null,
                null,
                null
        );
        while (csr.moveToNext()) {
            rv.add(new MyTableObject(
                    csr.getLong(csr.getColumnIndex(COl_MYTABLE_ID)),
                    csr.getString(csr.getColumnIndex(COL_MYTABLE_NAME))
                    )
            );
        }
        csr.close();
        return rv;
    }

    public Cursor getAllAsCursor() {
        return mDB.query(
                TB_MYTABLE,
                null,
                null,
                null,
                null,
                null,
                null
        );
    }
}

MyTableObject.java

public class MyTableObject {
    private long id;
    private String name;

    public MyTableObject(long id, String name) {
        this.id = id;
        this.name = name;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    /*
        NOTE toString method returns just the name
     */
    @Override
    public String toString() {
        return name;
    }
}

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:orientation="vertical"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Hello World!"
        />
    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="ArrayList-String"
            />
        <TextView
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:text="ArrayList-object"
            />
        <TextView
            android:text="Cursor"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        >
        <ListView
            android:id="@+id/listview01"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:background="#ffffaaaa">
        </ListView>
        <ListView
            android:id="@+id/listview02"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:background="#ffaaffaa">
        </ListView>
        <ListView
            android:id="@+id/listview03"
            android:layout_width="0dp"
            android:layout_weight="1"
            android:layout_height="wrap_content"
            android:background="#ffaaaaff">
        </ListView>
    </LinearLayout>

</LinearLayout>

Working Example Updated with Deletion Added

The following is the above but with the ability to delete rows on a longclick (only for the 2nd and 3rd ListView).

The MainActivity has been changed to

• include OnItemLongClick listeners for the 2nd and 3rd ListViews
  • The newly added delete method is invoked with the id passed
  • The refreshAllListViews is then called to refresh all the ListViews
• Note There is no reliable way to delete from the 1st ListView

The changed MainActivity.java :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper mDBHlpr;

    ListView mListView01,mListVeiw02,mListView03;

    ArrayAdapter<String> mAdapterStringArrayList;
    ArrayAdapter<MyTableObject> mAdapterMyTableObjectArrayList;
    SimpleCursorAdapter mAdapterCursor;

    ArrayList<String> mMyTableListAsStrings;
    ArrayList<MyTableObject> mMyTableAsObjects;
    Cursor mMyTableListAsCursor;

    Context mContext;

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

        mListView01 = this.findViewById(R.id.listview01);
        mListVeiw02 = this.findViewById(R.id.listview02);
        mListView03 = this.findViewById(R.id.listview03);

        mDBHlpr = new DatabaseHelper(this);
        mDBHlpr.addRow("Fred");
        mDBHlpr.addRow("Bert");
        mDBHlpr.addRow("Harry");
        mDBHlpr.addRow("Fred");

        //String Array List
        mMyTableListAsStrings = mDBHlpr.getAllAsStringArrayList();
        mAdapterStringArrayList = new ArrayAdapter<>(
                this,
                android.R.layout.simple_list_item_1,
                mMyTableListAsStrings
        );
        mListView01.setAdapter(mAdapterStringArrayList);


        //Object Array List
        mMyTableAsObjects = mDBHlpr.getAllAsMyTableObjectArrayList();
        mAdapterMyTableObjectArrayList = new ArrayAdapter<>(
                this,
                android.R.layout.simple_list_item_1,
                mMyTableAsObjects
        );
        mListVeiw02.setAdapter(mAdapterMyTableObjectArrayList);

        // Cursor
        mMyTableListAsCursor = mDBHlpr.getAllAsCursor();
        mAdapterCursor = new SimpleCursorAdapter(
                this,
                android.R.layout.simple_list_item_1,
                mMyTableListAsCursor,
                new String[]{DatabaseHelper.COL_MYTABLE_NAME},
                new int[]{android.R.id.text1},
                0
        );
        mListView03.setAdapter(mAdapterCursor);


        mListView01.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
                String name = mAdapterStringArrayList.getItem(position);
                Toast.makeText(
                        mContext,
                        "Name is " + name  +
                                ". ID is " + String.valueOf(id) +
                                " (note may not match)",
                        Toast.LENGTH_SHORT
                ).show();
            }
        });

        mListVeiw02.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
                MyTableObject mytable = mAdapterMyTableObjectArrayList.getItem(position);
                String name = mytable.getName();
                long id_in_object = mytable.getId();
                Toast.makeText(
                        mContext,
                        "Name is " + name  +
                                ". ID from object is " + String.valueOf(id_in_object) +
                                ". ID from adapter is " + String.valueOf(id),
                        Toast.LENGTH_SHORT
                ).show();
            }
        });
        mListVeiw02.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> adapterView, View view, int i, long l) {
                mDBHlpr.delete(mAdapterMyTableObjectArrayList.getItem(i).getId());
                refreshAllListViews();
                return true;
            }
        });

        mListView03.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> adapterView, View view, int position, long id) {
                Cursor csr = mAdapterCursor.getCursor(); // already positioned
                String name = csr.getString(csr.getColumnIndex(DatabaseHelper.COL_MYTABLE_NAME));
                long id_in_cursor = csr.getLong(csr.getColumnIndex(DatabaseHelper.COl_MYTABLE_ID));
                Toast.makeText(
                        mContext,
                        "Name is " + name  +
                                ". ID from object is " + String.valueOf(id_in_cursor) +
                                ". ID from adapter is " + String.valueOf(id),
                        Toast.LENGTH_SHORT
                ).show();
            }
        });
        mListView03.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> adapterView, View view, int i, long l) {
                mDBHlpr.delete(l);
                refreshAllListViews();
                return true;
            }
        });
    }

    public void refreshAllListViews() {
        mMyTableListAsStrings.clear();
        ArrayList<String> newStringArray = mDBHlpr.getAllAsStringArrayList();
        mMyTableListAsStrings.addAll(newStringArray);
        mAdapterStringArrayList.notifyDataSetChanged();

        mMyTableAsObjects.clear();
        ArrayList<MyTableObject> newObjectArray = mDBHlpr.getAllAsMyTableObjectArrayList();
        mMyTableAsObjects.addAll(newObjectArray);
        mAdapterMyTableObjectArrayList.notifyDataSetChanged();

        mMyTableListAsCursor = mDBHlpr.getAllAsCursor();
        mAdapterCursor.swapCursor(mMyTableListAsCursor);
    }
}

DatabaseHelper.java has been changed to include a new method to delete a row according to the id

i.e. the following method has been added :-

public int delete(long id) {
    String whereclause = COl_MYTABLE_ID + "=?";
    String[] whereargs = new String[]{String.valueOf(id)};
    return mDB.delete(TB_MYTABLE,whereclause,whereargs);
}