Adding Multiple Items to SQLite Database Android

Question

I am creating an Android Food Delivery App where menu items need to be added to the database while adding to the cart. For that, I have used SQL database with SQLiteAssethelper library.

Now the problem:

Please understand this carefully.

In my application, there are menu items that have Half and Full options to add. Like, a Chowmin has half and full options which have separate prices. Now the problem is, suppose a user wants to order 1.5 plates of chow mien, which means 1 plate of full and a plate of the half. But, whenever a user is clicking the half or full at first and adding them onto the cart, at first it adds items according to the input from the user. But when the same user is trying to click the other option with adding extra quantities, the previously added item in the DB gets updated with the added quantity.

That is the problem. This is not the desired result for me. I want to add two separate options for the same menu item. Below is my SQL Database class. Please help. This is the only thing that stops me from making my dream project.

Below I am giving the functions that are used to add, update, and check for items in the database. If anything else is wanted please comment.

Database.java

// Function for adding items to DB

public void addToCart(Order order) {
    SQLiteDatabase db = getReadableDatabase();
    String query = String.format("INSERT OR REPLACE INTO CartDetail(UserPhone,ProductId,ProductName,Quantity,Price,HalfPrice,FullPrice,Half,Full,ResId,Image) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');",
            order.getUserPhone(),
            order.getProductId(),
            order.getProductName(),
            order.getQuantity(),
            order.getPrice(),
            order.getFullPrice(),
            order.getHalfPrice(),
            order.getFull(),
            order.getHalf(),
            order.getResId(),
            order.getImage());
    db.execSQL(query);
}

//Function for getting all the items

public List<Order> getCarts(String userPhone) {
    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    String[] sqlSelect = {"UserPhone", "ProductName", "ProductId", "Quantity", "Price", "HalfPrice", "FullPrice", "Half", "Full", "ResId", "Image"};
    String sqlTable = "CartDetail";

    qb.setTables(sqlTable);
    Cursor c = qb.query(db, sqlSelect, "UserPhone=?", new String[]{userPhone}, null, null, null);

    final List<Order> result = new ArrayList<>();
    if (c.moveToFirst()) {
        do {
            result.add(new Order(
                    c.getString(c.getColumnIndex("UserPhone")),
                    c.getString(c.getColumnIndex("ProductId")),
                    c.getString(c.getColumnIndex("ProductName")),
                    c.getString(c.getColumnIndex("Quantity")),
                    c.getString(c.getColumnIndex("Price")),
                    c.getString(c.getColumnIndex("HalfPrice")),
                    c.getString(c.getColumnIndex("FullPrice")),
                    c.getString(c.getColumnIndex("Half")),
                    c.getString(c.getColumnIndex("Full")),
                    c.getString(c.getColumnIndex("ResId")),
                    c.getString(c.getColumnIndex("Image"))
            ));
        } while (c.moveToNext());
    }
    return result;
}

//Function for updating and increasing quantity for items in the DB

public void updatecart(Order order) 
{
    SQLiteDatabase db = getReadableDatabase();
    String query = String.format("UPDATE CartDetail SET Quantity = '%s' WHERE UserPhone = '%s' 
    AND ProductId='%s'", order.getQuantity(), order.getUserPhone(), order.getProductId());
    db.execSQL(query);
}

//Increasing Cart Items

public void increatecart(int itemcount, String userPhone, String foodId) {
    SQLiteDatabase db = getReadableDatabase();
    @SuppressLint("DefaultLocale") String query = String.format("UPDATE CartDetail SET 
Quantity = Quantity+'%d' WHERE UserPhone = '%s' AND ProductId='%s'", itemcount, userPhone, 
foodId);
    db.execSQL(query);
}

Please trim your code to make it easier to find your problem. Follow these guidelines to create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). — Community, Sep 03 '21 at 11:47

MikeT · Answer 1 · 2021-09-02T11:01:15.940

It appears that your WHERE clause does not uniquely identify a single CartDetail row, if there is a combination of half and full priced orders and thus updates all rows that match the criteria that being the User's phone and the Productid, when it looks as though another column should be used to distinguish between whether or not it is a half price or full price row that is to be updated.

However, without a clear indication of how the columns are used the above is just an assumption. It could be that you are using columns full and half for the respective quantities, in which case the respective column should be updated and the quantity column would appear to be redundant.

On the matter of redundancy, it appears that you have a productid which indicates that there may be a product table that would/should include product specific data and that you may be replicating that data unnecessarily. In other terms you want to consider normalising.

Very much based upon your columns I would suggest considering a change to the schema and the use of two tables (if there are not already two). For example perhaps consider the following:-

A Product table that has product specific data such as, the product's name (e.g. chow mein ....), a unique identifier (id), the full and half price (caters for the half-price not being half) and other information such as the image information.

and A CartDetail table which has the detail of the cart, this could either be a single row per product with the row updated accordingly or rows that culminate in an overall product order (with other rows for other products).

This latter appears to be more complicated but it does allow a more detailed look (if necessary) at the order.

The Product table could be based upon the following schema :-

CREATE TABLE IF NOT EXISTS product (productid INTEGER PRIMARY KEY, productname TEXT UNIQUE, fullprice REAL, halfprice REAL, resid INTEGER, productimage BLOB);

For testing/demonstration we could add 3 products using :-

INSERT INTO product 
    VALUES
        (1,'chow mein', 10.00, 6.00,12345678, null),
        (2,'peking duck',12.50,8.00,23456789,null),
        (3,'honey chicken',11.50,7.25,34567890,null)
;

Note that the half price, isn't actually half the price, but a higher than half price which is often the case.
The resid and image aren't considered in the demonstration.

The CartDetail table could be based upon the following schema :-

CREATE TABLE IF NOT EXISTS cartdetail (cartdetailid INTEGER PRIMARY KEY, userphone TEXT, productid, quantityfull INTEGER, quantityhalf INTEGER);

cartdetailid could be used to uniquely identify a detail, it is not used in the demonstration. However, it does not hurt having the column as it's an alias of the rowid column (a typically hidden column) so there is no overhead.
as you see the columns have been reduced, product information is all retrieved via the productid as will be demonstrated.

This first demonstration shows using the above in the more complex/complicated transactional approach, that is rows are added to adjust the order.

For example an initial order could be :-

INSERT INTO cartdetail (userphone,productid,quantityfull,quantityhalf)
    VALUES ('0000000000',1,1,1) /* initial order of 1.5 chow meins aka 1 full and 1 half*/
;

The comment explains

Then adjustments could be made (here 3 adjustments are made to the initial order 2 new products added and the original adjusted) :-

Here a single adjust is made using :-

/* one way by adding to cart i.e. adding additional details */
INSERT INTO cartdetail (userphone,productid,quantityfull,quantityhalf) 
    VALUES ('0000000000',1,0,3); /* update by adding 3 half chowmeins */

So now instead of the original 1 half meal there are 4 half meals (1 existin plus the 3 new).

then :-

INSERT INTO cartdetail (userphone,productid,quantityfull,quantityhalf)
    VALUES ('0000000000',1,0,-1) /* removes one half meal */,
            ('0000000000',3,1,0) /* add 1 full honey chicken */,
            ('0000000000',2,0,1) /* and also 1 half peking duck */
;

On Android these would very likely be individual INSERTS

To get the product details you can then JOIN the two tables e.g. using :-

SELECT * FROM cartdetail JOIN product ON product.productid = cartdetail.productid;

results in :-

However, from an overall perspective, the above isn't that user friendly, a more user friendly/information query could be :-

SELECT 
    userphone, 
    productname,
    sum(quantityfull) + (CAST(sum(quantityhalf) AS REAL) / 2) AS total,
    sum(quantityfull) AS fullMeals,
    sum(quantityhalf) AS halfmeals,
    (sum(quantityfull) * fullprice) + (sum(quantityhalf) * halfprice) AS totalprice
FROM cartdetail JOIN product ON product.productid = cartdetail.productid 
GROUP BY cartdetail.productid
;

This would produce a result of :-

Note the columns names in the resultant cursor would be according to the AS clauses (when used) so the Cursor would have columns :-
- userphone, productname, total, fullmeals, halfmeals and totalprice

The second demonstration, using the same structure/schema, uses updates to the existing product, which is closer to what you appear to be doing.

First the cartdetails from the first demonstration are all cleared using :-

/* Clear the cart */
DELETE FROM cartdetail;

An initial order is made using :-

/* add initial order */
INSERT INTO cartdetail (userphone,productid,quantityfull,quantityhalf) 
    VALUES ('0000000000',1,0,3) /* update by adding 3 half chowmeins */,
            ('0000000000',3,1,0) /* add 1 full honey chicken */,
            ('0000000000',2,0,1) /* and also 1 half peking duck */
    
;

using exactly the same query as for the first demo (2nd query) then the result is :-

Instead of adding rows (transactions) adjustment could be made by updating a product's row e.g. by using :-

/* Now increase the full chow mein's by 1 */
UPDATE cartdetail 
    SET 
        quantityfull = quantityfull + 1 /*?? number of full meals to change by (could be negative)*/,
        quantityhalf = quantityhalf + 0 /*?? half meal adjustment amount */
    WHERE userphone = '0000000000' AND productid = 1 /*?? product to adjust */
;

Using the same query (2nd query) then you get :-

Here's the above converted to Android as a Demo App. Two classes a DatabaseHelper with all the methods within, and an activity MainActivity to demonstrate.

DatabaseHelper :-

class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "thedatabase.db";
    private static final int DBVERSION = 1;

    private static volatile DatabaseHelper instance = null;

    SQLiteDatabase db;

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

    public static DatabaseHelper getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseHelper(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CartDetail.createSQL);
        db.execSQL(Product.createSQL);
    }

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

    }


    // Product Table Methods
    public long addProduct(
            String productName,
            Float fullPrice,
            Float halfPrice,
            int resourceId,
            byte[] image
    ) {
        ContentValues cv = new ContentValues();
        cv.put(Product.NAME_COLUMN,productName);
        cv.put(Product.FULLPRICE_COLUMN,fullPrice);
        cv.put(Product.HALFPRICE_COLUMN,halfPrice);
        cv.put(Product.IMAGE_RESOURCEID_COLUMN,resourceId);
        cv.put(Product.IMAGE_COLUMN,image);
        return db.insert(Product.TABLENAME,null,cv);
    }
    public long addProduct(
            String productName,
            Float fullPrice,
            Float halfPrice
    ) {
        return this.addProduct(productName,fullPrice,halfPrice,0,new byte[0]);
    }

    public long updateProduct(
            long productId,
            String newProductName_else_null,
            Float newFullPrice_else_null,
            Float newHalfPrice_else_null,
            Integer newResourceId_else_null, byte[] newImage_else_emptybytearray
    ) {
        ContentValues cv = new ContentValues();
        if (newProductName_else_null != null) {
            cv.put(Product.NAME_COLUMN,newProductName_else_null);
        }
        if (newFullPrice_else_null != null) {
            cv.put(Product.FULLPRICE_COLUMN,newFullPrice_else_null);
        }
        if (newHalfPrice_else_null != null) {
            cv.put(Product.HALFPRICE_COLUMN,newHalfPrice_else_null);
        }
        if (newResourceId_else_null != null) {
            cv.put(Product.IMAGE_RESOURCEID_COLUMN,newResourceId_else_null);
        }
        if (newImage_else_emptybytearray.length > 0) {
            cv.put(Product.IMAGE_COLUMN, newImage_else_emptybytearray);
        }
        if (cv.size() < 1) return 0; // dont update if nothing to update
        return  db.update(Product.TABLENAME,cv,Product.ID_COLUMN + "=?",new String[]{String.valueOf(productId)});
    }

    //CartDetail methods
    public long adjustQuantity(String userphone, long productid,int fullMealAdjustment, int halfMealAdjustment) {
        String sql = "UPDATE " + CartDetail.TABLENAME + " SET " + CartDetail.FULL_MEAL_QUANTITY_COLUMN + "=" + CartDetail.FULL_MEAL_QUANTITY_COLUMN + "+?," +
                CartDetail.HALF_MEAL_QUANTITY_COLUMN + "=" + CartDetail.HALF_MEAL_QUANTITY_COLUMN + "+? " +
                " WHERE " + CartDetail.USERPHONE_COLUMN + "=? AND " + CartDetail.PRODUCT_ID_REFERENCE_COLUMN + "=?;";
        db.execSQL(
                sql,
                new String[]
                        {
                                String.valueOf(fullMealAdjustment),
                                String.valueOf(halfMealAdjustment),
                                userphone,
                                String.valueOf(productid)
                        }
        );
        return 0;
    }

    public long insertCartDetail(String userPhone, long productId, int fullMealQuantity, int halfMealQuantity) {
        ContentValues cv = new ContentValues();
        cv.put(CartDetail.USERPHONE_COLUMN,userPhone);
        cv.put(CartDetail.PRODUCT_ID_REFERENCE_COLUMN,productId);
        cv.put(CartDetail.FULL_MEAL_QUANTITY_COLUMN,fullMealQuantity);
        cv.put(CartDetail.HALF_MEAL_QUANTITY_COLUMN,halfMealQuantity);
        return db.insert(CartDetail.TABLENAME,null,cv);
    }

    public void logOrderDetails(String userphone) {

            /*
            SELECT
                userphone,
                productname,
                sum(quantityfull) + (CAST(sum(quantityhalf) AS REAL) / 2) AS total,
                sum(quantityfull) AS fullMeals,
                sum(quantityhalf) AS halfmeals,
                (sum(quantityfull) * fullprice) + (sum(quantityhalf) * halfprice) AS totalprice
            FROM cartdetail JOIN product ON product.productid = cartdetail.productid
            GROUP BY cartdetail.productid
             */

        /* From clause aka table */
        String table = CartDetail.TABLENAME +
                " JOIN " + Product.TABLENAME + " ON " + CartDetail.TABLENAME + "." + PRODUCT_ID_REFERENCE_COLUMN + "=" + Product.TABLENAME + "." + Product.ID_COLUMN;

        /* Columns */
        String[] columns = new String[]{
                CartDetail.USERPHONE_COLUMN,
                Product.NAME_COLUMN,
                "sum(" + CartDetail.FULL_MEAL_QUANTITY_COLUMN + ") + (CAST(sum(" + CartDetail.HALF_MEAL_QUANTITY_COLUMN + ") AS REAL) / 2) " +
                        " AS " + CartDetail.PRODUCT_COMBINDED_QUANTITY,
                "sum(" + CartDetail.FULL_MEAL_QUANTITY_COLUMN + ") AS " + CartDetail.FULL_MEAL_QUANTITY_COLUMN,
                "sum(" + CartDetail.HALF_MEAL_QUANTITY_COLUMN + ") AS " + CartDetail.HALF_MEAL_QUANTITY_COLUMN,
                "(sum(" + CartDetail.FULL_MEAL_QUANTITY_COLUMN + ") * " + Product.FULLPRICE_COLUMN + ") " +
                        "+ (sum(" + CartDetail.HALF_MEAL_QUANTITY_COLUMN + ") * " + Product.HALFPRICE_COLUMN + ") " +
                        " AS " + CartDetail.TOTAL_PRODUCT_PRICE_COLUMN,
                Product.HALFPRICE_COLUMN,
                Product.FULLPRICE_COLUMN,
                Product.IMAGE_RESOURCEID_COLUMN,
                Product.IMAGE_COLUMN
        };
        Cursor csr = db.query(
                table,columns,
                CartDetail.USERPHONE_COLUMN + "=?",
                new String[]{userphone},
                CartDetail.TABLENAME + "." + CartDetail.PRODUCT_ID_REFERENCE_COLUMN,
                null,
                null
        );
        while (csr.moveToNext()) {
            Log.d(
                    "ORDERDETAIL",
                    "User is " + csr.getString(csr.getColumnIndex(USERPHONE_COLUMN)) +
                            "\n\tProduct is " + csr.getString(csr.getColumnIndex(Product.NAME_COLUMN)) +
                            "\n\t\tTotal Meals (as Full) is " + csr.getString(csr.getColumnIndex(CartDetail.PRODUCT_COMBINDED_QUANTITY)) +
                            "\n\t\t# of Full Meals ordered = " + csr.getString(csr.getColumnIndex(CartDetail.FULL_MEAL_QUANTITY_COLUMN)) +
                            " @ $" + csr.getString(csr.getColumnIndex(Product.FULLPRICE_COLUMN)) + " per meal" +
                            "\n\t\t# of Half Meals ordered = " + csr.getString(csr.getColumnIndex(CartDetail.HALF_MEAL_QUANTITY_COLUMN)) +
                            " @ $" + csr.getString(csr.getColumnIndex(Product.HALFPRICE_COLUMN)) + " per meal" +
                            "\n\tCombined Cost for " + csr.getString(csr.getColumnIndex(Product.NAME_COLUMN)) + " is $" + csr.getString(csr.getColumnIndex(CartDetail.TOTAL_PRODUCT_PRICE_COLUMN)) +
                            "\n\t\tetc."
            );
        }
        csr.close();
    }

    class CartDetail {

        public static final String TABLENAME = "cartdetail";
        public static final String ID_COLUMN = BaseColumns._ID;
        public static final String TIMESTAMP_COLUMN = "timestamp";
        public static final String USERPHONE_COLUMN = "userphone";
        public static final String PRODUCT_ID_REFERENCE_COLUMN = "productid";
        public static final String FULL_MEAL_QUANTITY_COLUMN = "fullquantity";
        public static final String HALF_MEAL_QUANTITY_COLUMN = "halfquantity";

        // Derived columns
        public static final String TOTAL_PRODUCT_PRICE_COLUMN = "totalproductprice";
        public static final String PRODUCT_COMBINDED_QUANTITY = "combinedquantity";

        public static final String createSQL = "CREATE TABLE IF NOT EXISTS " + TABLENAME + "(" +
                ID_COLUMN + " INTEGER PRIMARY KEY," +
                TIMESTAMP_COLUMN + " INTEGER DEFAULT CURRENT_TIMESTAMP, " +
                USERPHONE_COLUMN + " TEXT, " +
                PRODUCT_ID_REFERENCE_COLUMN + " INTEGER, " +
                FULL_MEAL_QUANTITY_COLUMN + " INTEGER, " +
                HALF_MEAL_QUANTITY_COLUMN + " INTEGER " +
                ")";
    }

    class Product {
        public static final String TABLENAME = "product";
        public static final String ID_COLUMN = BaseColumns._ID;
        public static final String NAME_COLUMN = "productname";
        public static final String FULLPRICE_COLUMN = "fullprice";
        public static final String HALFPRICE_COLUMN = "halfprice";
        public static final String IMAGE_RESOURCEID_COLUMN = "resourceid";
        public static final String IMAGE_COLUMN = "image";

        public static final String createSQL = "CREATE TABLE IF NOT EXISTS " + TABLENAME + "(" +
                ID_COLUMN + " INTEGER PRIMARY KEY, " +
                NAME_COLUMN + " TEXT UNIQUE, " +
                FULLPRICE_COLUMN + " REAL, " +
                HALFPRICE_COLUMN + " REAL, " +
                IMAGE_RESOURCEID_COLUMN + " INTEGER, " +
                IMAGE_COLUMN + " BLOB " +
                ")";
    }
}

and MainActivty making an order and then adjusting the order, writing the details of the order to the log :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper db;

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

        long chowmien = db.addProduct("Chow Mien",15.00F,8.50F);
        long pekingduck = db.addProduct("Peking Duck",25.00F,14.50F);
        long honeychicken = db.addProduct("Honey Chicken",14.00F,8.00F);

        String userphone = "0000000000";

        db.insertCartDetail(userphone,chowmien,1,3);
        db.insertCartDetail(userphone,pekingduck,0,2);
        db.insertCartDetail(userphone,honeychicken,2,2);
        db.logOrderDetails(userphone);

        db.adjustQuantity(userphone,honeychicken,-1,1);
        db.logOrderDetails(userphone);

    }
}

Result as per the Log :-

After the initial order of 1 full chow mien and 3 half chow miens, 2 half peking ducks and 2 full and 2 half Honey Chickens :-

2021-09-02 20:13:31.085 D/ORDERDETAIL: User is 0000000000
        Product is Chow Mien
            Total Meals (as Full) is 2.5
            # of Full Meals ordered = 1 @ $15 per meal
            # of Half Meals ordered = 3 @ $8.5 per meal
        Combined Cost for Chow Mien is $40.5
            etc.
2021-09-02 20:13:31.085 D/ORDERDETAIL: User is 0000000000
        Product is Peking Duck
            Total Meals (as Full) is 1
            # of Full Meals ordered = 0 @ $25 per meal
            # of Half Meals ordered = 2 @ $14.5 per meal
        Combined Cost for Peking Duck is $29
            etc.
2021-09-02 20:13:31.085 D/ORDERDETAIL: User is 0000000000
        Product is Honey Chicken
            Total Meals (as Full) is 3
            # of Full Meals ordered = 2 @ $14 per meal
            # of Half Meals ordered = 2 @ $8 per meal
        Combined Cost for Honey Chicken is $44
            etc.

Then after adjusting (reducing 2 full and 2 half honey chickens to 1 full (-1) and 3 half) :-

2021-09-02 20:13:31.086 D/ORDERDETAIL: User is 0000000000
        Product is Chow Mien
            Total Meals (as Full) is 2.5
            # of Full Meals ordered = 1 @ $15 per meal
            # of Half Meals ordered = 3 @ $8.5 per meal
        Combined Cost for Chow Mien is $40.5
            etc.
2021-09-02 20:13:31.087 D/ORDERDETAIL: User is 0000000000
        Product is Peking Duck
            Total Meals (as Full) is 1
            # of Full Meals ordered = 0 @ $25 per meal
            # of Half Meals ordered = 2 @ $14.5 per meal
        Combined Cost for Peking Duck is $29
            etc.
2021-09-02 20:13:31.087 D/ORDERDETAIL: User is 0000000000
        Product is Honey Chicken
            Total Meals (as Full) is 2.5
            # of Full Meals ordered = 1 @ $14 per meal
            # of Half Meals ordered = 3 @ $8 per meal
        Combined Cost for Honey Chicken is $38
            etc.

Adding Multiple Items to SQLite Database Android

1 Answers1