Android prevent duplication only if all rows are inserting again into database table

Question

I am working on a billing android app. I need to prevent duplication only if all the rows in table repeat again.

I had add a roll back method

rows = db.insertWithOnConflict("output_to_cloud4", null, cv, SQLiteDatabase.CONFLICT_REPLACE);

But still inserting duplicate details

-Here I am sharing my inserting method

void Insert_OutputDetails_withTransation() {
    long rows = 0;

   db = openOrCreateDatabase("Student.db",OPEN_READWRITE, null);

    ContentValues cv = new ContentValues();
    db.beginTransaction();
    try {
        for (int i = 0; i < myMap.size(); i++) {
            float check_not_zeroValue = Float.parseFloat(myMap.get(PosUtils.PRODUCT_CODE.get(i)));
            if (!(check_not_zeroValue == 0.0f)) {

                try {

                    int time = (int) (System.currentTimeMillis());
                    Timestamp tsTemp = new Timestamp(time);
                    String ts =  tsTemp.toString();

                    Double round_amount = Double.valueOf(Rounded_Total_price_with_tax) - Double.valueOf(Total_price_with_tax);
                    cv.put("BILLNO", (LAST_BILL_NUMBER));
                    cv.put("BILLPREFIX", SALES_MAN_BILLPREFIX);
                    cv.put("BILLDATE", Date);
                    cv.put("saleman_code", SALES_MAN_CODE);
                    cv.put("DLYVEHICLE", SALES_MAN_DLYVANNO);
                    //"CUSTOMERCODE","PRODUCTCODE","QTY","RATE","GROSS",
                    cv.put("CUSTOMERCODE", CUSTOMERS_CODE_LIST.get(CUSTOMER_POSITION));
                    cv.put("PRODUCTCODE", PRODUCT_CODE.get(i));
                    cv.put("QTY", myMap.get(PosUtils.PRODUCT_CODE.get(i)));

                    cv.put("RATE", PRODUCT_RATE.get(i));
                    cv.put("GROSS", new DecimalFormat("0.00").format(item_price));

                    //"CGST","CGSTAMT","SGSTP","SGSTAMT","CESSP",
                    cv.put("CGST", PRODUCT_CGSTP.get(i));
                    cv.put("CGSTAMT", new DecimalFormat("0.00").format(amount_cgst));

                    cv.put("SGSTP", PRODUCT_SGSTP.get(i));
                    cv.put("SGSTAMT", new DecimalFormat("0.00").format(amount_sgst));

                    cv.put("CESSP", PRODUCT_CESSP.get(i));
                    //"CESSAMT","SCESSP","SCCAMT","ROUND","BTOTAL","
                    cv.put("CESSAMT", new DecimalFormat("0.00").format(amount_cseep));

                    cv.put("SCESSP", PRODUCT_SCESSP.get(i));
                    cv.put("SCCAMT", new DecimalFormat("0.00").format(amount_scessp));


                    cv.put("BTOTAL", new DecimalFormat("0.00").format(Rounded_Total_price_with_tax));
                    cv.put("ROUND", new DecimalFormat("0.00").format(round_amount));
                    cv.put("BILL_STATUS", "1");

                rows = db.insertWithOnConflict("output_to_cloud4", null, cv, SQLiteDatabase.CONFLICT_REPLACE);
              } catch (Exception in) {
                    in.getMessage();
                }
            }
        }
        db.setTransactionSuccessful();


    } catch (Exception e) {
        e.getMessage();
    } finally {
        db.endTransaction();
    }

}

My database Formate

     db.execSQL("CREATE  TABLE IF NOT EXISTS output_to_cloud3(" +
                "BILLNO TEXT,BILLPREFIX TEXT,BILLDATE TEXT,saleman_code TEXT,DLYVEHICLE TEXT," +
                "CUSTOMERCODE TEXT,PRODUCTCODE TEXT,QTY TEXT,RATE TEXT,GROSS TEXT," +
                "CGST TEXT,CGSTAMT TEXT,SGSTP TEXT,SGSTAMT TEXT,CESSP TEXT," +
                "CESSAMT TEXT,SCESSP TEXT,SCCAMT TEXT,ROUND TEXT,BTOTAL TEXT,BILL_STATUS TEXT,TIME_IN TEXT)");

If anybody face this problem before help me.....

Answer 1

I believe that your issue is that you are expecting duplicates to be automatically rejected.

This is not the case, you have to define CONSTRAINTS unless they are implied (e.g. primary indexes imply a UNIQUE constraint), which none are.

The simplest fix, although perhaps not the most efficient, to meet your criteria of I need to prevent duplication only if all the rows in table repeat again. would be to create an index based upon all rows that has the UNIQUE constraint, so you could use:-

CREATE UNIQUE INDEX IF NOT EXISTS stop_dupplicates ON output_to_cloud4 (
    BILLNO,
    BILLPREFIX,
    BILLDATE,
    saleman_code,
    DLYVEHICLE,
    CUSTOMERCODE,
    PRODUCTCODE,
    QTY,
    RATE,
    GROSS,
    CGST,
    CGSTAMT,
    SGSTP,
    SGSTAMT,
    CESSP,
    CESSAMT,
    SCESSP,
    SCCAMT,
    ROUND,
    BTOTAL,
    BILL_STATUS,
    TIME_IN
);

Rather than using the insertWithOnConflict method along with SQLiteDatabase.CONFLICT_REPLACE you could just use the insert method which equates to INSERT OR IGNORE ......., so the if a duplicate is encountered the INSERT is just ignored (not inserted) and processing continues, rather than replacing (updating) a row with exactly the same data.

Examples/Testing

Based upon a subset of the columns, for convenience, the following demonstrates the above :-

Create output_to_cloud3 table:-

CREATE TABLE IF NOT EXISTS output_to_cloud3(
    BILLNO TEXT,
    BILLPREFIX TEXT,
    BILLDATE TEXT,
    TIME_IN TEXT
);

Create the index to stop duplicates (all rows) being inserted

CREATE UNIQUE INDEX IF NOT EXISTS stop_dupplicates ON output_to_cloud3 (
    BILLNO,
    BILLPREFIX,
    BILLDATE,
    TIME_IN
);

Insert some data :-

INSERT INTO output_to_cloud3
    VALUES
        ('A','B','C','D'),
        ('E','F','G','H'),
        ('I','J','K','L'),
        ('M','N','O','P'),
        ('Q','R','S','T')
;

The table will now be :-

Attempt 1 - add duplicates using INSERT.

• this being the equivalent to using the insertOrThrow method)

:-

INSERT INTO output_to_cloud3
    VALUES
        ('A','B','C','D'),
        ('E','F','G','H'),
        ('I','J','K','L'),
        ('M','N','O','P'),
        ('Q','R','S','T')
;

An exception would be thrown when the first insert is attempted and nothing would be inserted.

Attempt 2 - add dupliactes using INSERT OR REPLACE

• this being the equivalent of using the insertWithOnConflict along with SQLiteDatabase.CONFLICT_REPLACE.

:-

INSERT OR REPLACE INTO output_to_cloud3
    VALUES
        ('A','B','C','D'),
        ('E','F','G','H'),
        ('I','J','K','L'),
        ('M','N','O','P'),
        ('Q','R','S','T')
;

Then no exception would be raised, no rows would be added, however all rows would be updated, but the tables is effectively unchanged.

Attempt 3 - add duplicates using INSERT OR IGNORE

• this being the equivalent of using the insert method.

:-

INSERT OR IGNORE INTO output_to_cloud3
    VALUES
        ('A','B','C','D'),
        ('E','F','G','H'),
        ('I','J','K','L'),
        ('M','N','O','P'),
        ('Q','R','S','T')
;

Then no exception would be raised, no rows would be added the tables is unchanged.

Attempt 4 - add non-duplicates and duplicates using INSERT

• Again this is the equivalent of using the insertOrThrow method.

:-

INSERT INTO output_to_cloud3
    VALUES
        ('U','V','W','X'), -- New non-duplicate row
        ('A','B','C','D'),
        ('E','F','G','H'),
        ('I','J','K','L'),
        ('M','N','O','P'),
        ('Z','AA','AB','AC'), -- New non-duplicate row
        ('Q','R','S','T'),
        ('AD','AE','AF','AG') -- New non-dupliate row

;

Then an exception is thrown (with the above as it's a single statement the table would remain unchanged.) If the were individual inserts then the first row would be inserted, the second row would then result in an exception.

Attempt 5 - add non-duplicates and duplicates using INSERT OR REPLACE

• Again this is the equivalent of using the insertWithOnConflict method along with SQLiteDatabase.CONFLICT_REPLACE.

:-

INSERT OR REPLACE INTO output_to_cloud3
    VALUES
        ('U','V','W','X'), -- New non-duplicate row
        ('A','B','C','D'),
        ('E','F','G','H'),
        ('I','J','K','L'),
        ('M','N','O','P'),
        ('Z','AA','AB','AC'), -- New non-duplicate row
        ('Q','R','S','T'),
        ('AD','AE','AF','AG') -- New non-dupliate row

; 

This would insert the non-duplicates and replace the duplicates (effectively leaving them unchanged) and the resultant table would be as below.

Attempt 6 - add non-duplicates and duplicates using INSERT OR IGNORE

• Again this is equivalent of using the insert method.

:-

INSERT OR REPLACE INTO output_to_cloud3
    VALUES
        ('U','V','W','X'), -- New non-duplicate row
        ('A','B','C','D'),
        ('E','F','G','H'),
        ('I','J','K','L'),
        ('M','N','O','P'),
        ('Z','AA','AB','AC'), -- New non-duplicate row
        ('Q','R','S','T'),
        ('AD','AE','AF','AG') -- New non-dupliate row

;

The end result would be the same as attempt 5 i.e. :-