sqlite database delete not working

Question

This may be a dumb question to ask so with apology I want some one to help me on this.

I Have a simple custom ListView that contains a delete icon in it. This custom ListView is inside cart activity. I retrived all those data from server and saved those data using SQLite database. I wish to delete that particular list item on clicking to delete icon.

I tried some code from internet however I could not fix them out.

Please help.

This is my screen shot.

This is my database class:

 package com.example.user.ektabooks.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.view.MenuItem;

import com.example.user.ektabooks.pojos.MyCart;

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

/**
 * Created by user on 12/7/2016.
 */
public class CartDatabase extends SQLiteOpenHelper {

    public CartUpdatedListener cartUpdatedListener;
    static final String DATABASE_NAME = "CartDatabase";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME_CART = "cart";

    //    Contact table columns name
    private static final String ID = "Id";
    private static final String NAME = "Name";
    private static final String COVER = "Cover";
    private static final String PRICE = "Price";
    private static final String QUANTITY = "Quantity";

    String createCartTable = "Create table if not exists `Cart` ("
            + "`name`   TEXT," + "`price`   TEXT," + "`quantity`    TEXT," + "`cover` TEXT);";

    String CREATE_CART_TABLE = "CREATE TABLE " + TABLE_NAME_CART+ "("
            + ID + " INTEGER PRIMARY KEY, "
            + NAME + " TEXT, "
            + COVER + " TEXT, "
            + PRICE + " TEXT, "
            + QUANTITY + " TEXT " + ")";

    public CartDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void insertCartData(MyCart cartitem) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues content = new ContentValues();
        content.put("name", cartitem.cartBookTitle);
        content.put("price", cartitem.cartBooKPrice);
        content.put("quantity", cartitem.cartBookQuantity);
        content.put("cover", cartitem.cartBookCover);

        db.insert("Cart", null, content);
    }

    public ArrayList<MyCart> getCartList() {
        String sql = "select * from Cart ";
        ArrayList<MyCart> cartlist = new ArrayList<MyCart>();

        Cursor c = getWritableDatabase().rawQuery(sql, null);
        while (c.moveToNext()) {
            MyCart info = new MyCart();
            info.cartBookTitle = c.getString(c.getColumnIndex("name"));
            info.cartBooKPrice = c.getString(c.getColumnIndex("price"));
            info.cartBookQuantity = c.getString(c.getColumnIndex("quantity"));
            info.cartBookCover = c.getString(c.getColumnIndex("cover"));
            cartlist.add(info);
        }
        c.close();
        return cartlist;
    }

    public MyCart getCartData(String cartName) {
        String sql = "select * from Cart  where id='" + cartName + "'";

        Cursor c = getWritableDatabase().rawQuery(sql, null);
        while (c.moveToNext()) {
            MyCart info = new MyCart();
            info.cartBookTitle = c.getString(c.getColumnIndex("name"));
            info.cartBooKPrice = c.getString(c.getColumnIndex("price"));
            info.cartBookQuantity = c.getString(c.getColumnIndex("quantity"));
            info.cartBookCover = c.getString(c.getColumnIndex("cover"));
        }
        c.close();
        MyCart info = null;
        return info;
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // TODO Auto-generated method stub
        sqLiteDatabase.execSQL(CREATE_CART_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int arg1, int arg2) {
        // TODO Auto-generated method stub
        //Drop older version if existed
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_CART);
        //create table again
        onCreate(sqLiteDatabase);
    }

    public void addBookCart(MyCart cartitem) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(ID, cartitem.getCartBookID());
        values.put(NAME, cartitem.getCartBookTitle());
        values.put(COVER, cartitem.getCartBookCover());
        values.put(PRICE, cartitem.getCartBooKPrice());
        values.put(QUANTITY, cartitem.getCartBookQuantity());

        //inserting row
        if (db.insert(TABLE_NAME_CART, null, values) != -1) {
            cartUpdatedListener.setCartSuccess(cartitem.getCartBookTitle());
        } else {
            cartUpdatedListener.setCartError("Failed to insert");
        }

        db.close();
    }

    //    getting all selected books
    public List<MyCart> getAllCartBooks() {
        SQLiteDatabase db = this.getWritableDatabase();
        String selectQuery = "SELECT * FROM " + TABLE_NAME_CART;
        List<MyCart> cartitems = new ArrayList<>();
        Cursor cursor = db.rawQuery(selectQuery, null);

        //looping through all list and adding to list
        if (cursor.moveToFirst()) {
            do {
                MyCart cartitem = new MyCart();
                cartitem.setCartBookID(Integer.parseInt(cursor.getString(0)));
                cartitem.setCartBookTitle(cursor.getString(1));
                cartitem.setCartBookCover(cursor.getString(2));
                cartitem.setCartBooKPrice(cursor.getString(3));
                cartitem.setCartBookQuantity(cursor.getString(4));
                cartitems.add(cartitem);
            } while (cursor.moveToNext());
        }
        return cartitems;
    }

    public void delete(String id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("delete from " + TABLE_NAME_CART + " where id='" + id + "'");
    }
}

This is my activity class:

public class Cart extends AppCompatActivity implements View.OnClickListener{

    private List<MyCart> mycartlistitems = new ArrayList<MyCart>();
    private ListView listViewCart;
    private CartAdapter adapter;
    CartDatabase cartDatabase;

    TextView myCartTotalPrice;
    Button myCartPlaceOrder;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_cart);
        cartDatabase = new CartDatabase(this);

        myCartTotalPrice= (TextView) findViewById(R.id.myCartTotalPrice);
        myCartPlaceOrder= (Button) findViewById(R.id.myCartPlaceOrder);

        myCartPlaceOrder.setOnClickListener(this);

        getSupportActionBar().setDisplayHomeAsUpEnabled(true);
        getSupportActionBar().setTitle("Your Cart");

        List<MyCart> cartBooks = cartDatabase.getAllCartBooks();


        listViewCart = (ListView) findViewById(R.id.cart_list);
        if (cartBooks.size() != 0) {
            adapter = new CartAdapter(this, cartBooks);
            listViewCart.setAdapter(adapter);
        } else {
            Toast.makeText(this, "You have no books on cart yet.", Toast.LENGTH_SHORT).show();
        }

    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        switch (item.getItemId()) {
            case android.R.id.home:
                onBackPressed();
                return true;
            default:
                return super.onOptionsItemSelected(item);
        }
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()){
            case R.id.myCartPlaceOrder:
                Intent intent=new Intent(Cart.this,Payment.class);
                startActivity(intent);
        }

    }
}

And this is my adapter:

   package com.example.user.ektabooks.adapter;

import android.app.Activity;
import android.content.Context;
import android.media.Image;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ImageView;
import android.widget.TextView;

import com.android.volley.toolbox.ImageLoader;
import com.android.volley.toolbox.NetworkImageView;
import com.example.user.ektabooks.R;
import com.example.user.ektabooks.database.CartDatabase;
import com.example.user.ektabooks.pojos.AppController;
import com.example.user.ektabooks.pojos.MyCart;

import java.util.List;

/**
 * Created by user on 12/2/2016.
 */
public class CartAdapter extends BaseAdapter {

    private Activity activity;
    private LayoutInflater inflater;
    private List<MyCart> myCartList;
    CartDatabase db;
    ImageLoader imageLoader = AppController.getInstance().getImageLoader();

    public CartAdapter(Activity activity, List<MyCart> myCartList) {
        this.activity = activity;
        this.myCartList = myCartList;
    }


    @Override
    public int getCount() {
        return myCartList.size();
    }

    @Override
    public Object getItem(int location) {
        return myCartList.get(location);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(final int position, View convertView, ViewGroup parent) {

        if (inflater == null)
            inflater = (LayoutInflater) activity
                    .getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        if (convertView == null)
            convertView = inflater.inflate(R.layout.mycart_list, null);

        if (imageLoader == null)
            imageLoader = AppController.getInstance().getImageLoader();
        NetworkImageView myCartBookCover = (NetworkImageView) convertView
                .findViewById(R.id.myCartBookCover);
        TextView myCartBookName = (TextView) convertView.findViewById(R.id.myCartBookTitle);
        TextView myCartBookPrice = (TextView) convertView.findViewById(R.id.myCartBookPrice);
        TextView myCartBookNumber = (TextView) convertView.findViewById(R.id.myCartBookNumber);
        ImageView delete= (ImageView) convertView.findViewById(R.id.myCartBookDelete);

        MyCart m = myCartList.get(position);

        myCartBookCover.setImageUrl(m.getCartBookCover(), imageLoader);

        myCartBookName.setText("Title: " +m.getCartBookTitle());

        myCartBookPrice.setText(m.getCartBooKPrice());
        myCartBookNumber.setText(m.getCartBookQuantity());

        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                int id=myCartList.get(position).cartBookID;
                db.delete( Integer.toString(id));
                notifyDataSetChanged();
            }
        });


        return convertView;
    }
}

Can somebody please help me?
Please don't say what is your error and all that. I am a neophyte

this is my error log

 Shutting down VM
12-14 09:41:20.051 2731-2731/com.example.user.ektabooks E/AndroidRuntime: FATAL EXCEPTION: main
                                                                          Process: com.example.user.ektabooks, PID: 2731
                                                                          java.lang.NullPointerException: Attempt to invoke virtual method 'void com.example.user.ektabooks.database.CartDatabase.delete(int)' on a null object reference
                                                                              at com.example.user.ektabooks.adapter.CartAdapter$1.onClick(CartAdapter.java:85)
                                                                              at android.view.View.performClick(View.java:5610)
                                                                              at android.view.View$PerformClick.run(View.java:22260)
                                                                              at android.os.Handler.handleCallback(Handler.java:751)
                                                                              at android.os.Handler.dispatchMessage(Handler.java:95)
                                                                              at android.os.Looper.loop(Looper.java:154)
                                                                              at android.app.ActivityThread.main(ActivityThread.java:6077)
                                                                              at java.lang.reflect.Method.invoke(Native Method)
                                                                              at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:865)
                                                                              at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:755)

Answer 1

First of all set OnCLickListener to your delete icon.

Make changes as follow in your delete query.

Use query like that to delete data.

public void delete(String id) {
     db.execSQL("delete from " + TABLE_NAME_CART + " where id='" + id + "'");
}

And like wise.

 db.delete(TABLE_NAME_CART, "id=?",new String[]{Integer.toString(id)});

Inside Base Adapter.

      delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                int id = myCartList.get(position).cartBookID;
                yourDatabaseObject.delete(id);
                notifyDataSetChanged();
            }
        });

Make sure you are taking correct Id.

Using list you need to define your id as mycartlist.getCartBookID();

And update your List View by making notifyDataSetChanged(); inside OnCLickListener.

Answer 2

first of all create a reference for your delete icon inside your adapter class,

After that write these lines before returning your "convertView"

//here delete is your icon

convertView.delete.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {

        //Implement your logic here to delete from database

        notifyDataSetChanged();
    }
});

and make changes in your Database to delete a row, as posted by "Jaydroider"