SQLITE约束异常错误

Question

我在一个应用程序中新工作，该应用程序使用sqlite db重新保存并保存数据。我已经完成了整个项目。我的应用程序可完美地用于首次登录。当我使用不同的用户ID登录时，它会引发SQLITE约束异常。我的数据库字段保持不变，与我使用用户ID作为唯一元素为用户获取值的情况相同。

如何克服这个例外？否则我需要更改整个表结构？

        @Override
            public void onCreate(SQLiteDatabase db) {
                db.execSQL("CREATE TABLE " + Table_variables + "(id INTEGER PRIMARY KEY ,var1 text, var2 text, var3 text, var4 text, var5 text, var6 text, var7 text, var8 text, var9 text, var10 text, userid text )");
                db.execSQL("CREATE TABLE " + Table_values + "(id INTEGER PRIMARY KEY , val1 text, val2 text, val3 text, val4 text, val5 text, val6 text, val7 text, val8 text, val9 text, val10 text ,userid text )");
                db.execSQL("CREATE TABLE " + Table_ListId + "(id INTEGER PRIMARY KEY , val1 text, val2 text, val3 text, val4 text, val5 text, val6 text, val7 text, val8 text, val9 text, val10 text,userid text )");
                db.execSQL("CREATE TABLE " + Table_PaymentOptions + "(id INTEGER PRIMARY KEY, var1 text, var2 text, var3 text, var4 text, var5 text, var6 text, var7 text, var8 text , userid text, randomID text)");
                db.execSQL("CREATE TABLE " + Table_PaymentRemider + "(id INTEGER PRIMARY KEY, plc text, agencyname text,amountDue text, datedue text, reminderNotes text, reminderDate text, reminderTime text, toggleOption text, userid text, uniqueID text)");
            }
    // TODO TO INSERT REGION DATA
         public boolean insertData(Context context, ArrayList list, ArrayList list1, ArrayList list2, String plcData, String agencyName, String userid) {
                SQLiteDatabase myDB = getWritableDatabase("my_paygov_secure_key");
                System.out.println("AGENCY_NAME_in DBHANDLER" + agencyName);
                if (list.size() == 0) {

                } else {
                    for (int i = 0; i < list.size(); i++) {
                        System.out.println("LIST-label and List-Edittext" + list.get(i));
                    }
                   boolean dbCheck = CheckIsDataAlreadyInDBorNot(Table_variables, "id", plcData, "userid", userid);
                    if (dbCheck) {
                        String query = "UPDATE " + Table_variables + " SET var2='" + list.get(0) + "',var3='" + list.get(1) + "',var4='" + list.get(2) + "',var5='" + list.get(3) + "',var6='" + list.get(4) + "',var7='" + list.get(5) + "',var8='" + list.get(6) + "',var9='" + list.get(7) + "',var10='" + list.get(8) + "'  WHERE id =' " + plcData + " ' and  userid='" + userid + "'";
                        String query1 = "UPDATE " + Table_values + " SET val2='" + list1.get(0) + "',val3='" + list1.get(1) + "',val4='" + list1.get(2) + "',val5='" + list1.get(3) + "',val6='" + list1.get(4) + "',val7='" + list1.get(5) + "',val8='" + list1.get(6) + "',val9='" + list1.get(7) + "',val10='" + list1.get(8) + "'  WHERE id =' " + plcData + " ' and  userid='" + userid + "'";
                        String query2 = "UPDATE " + Table_ListId + " SET val2='" + list2.get(0) + "',val3='" + list2.get(1) + "',val4='" + list2.get(2) + "',val5='" + list2.get(3) + "',val6='" + list2.get(4) + "',val7='" + list2.get(5) + "',val8='" + list2.get(6) + "',val9='" + list2.get(7) + "',val10='" + list2.get(8) + "'  WHERE id =' " + plcData + " ' and  userid='" + userid + "'";
                        myDB.execSQL(query);
                        myDB.execSQL(query1);
                        myDB.execSQL(query2);
                    } else {
                      String query = "INSERT INTO " + Table_variables + " VALUES('" + plcData + "', '" + agencyName + "','" + list.get(0) + "','" + list.get(1) + "','" + list.get(2) + "','" + list.get(3) + "','" + list.get(4) + "','" + list.get(5) + "','" + list.get(6) + "','" + list.get(7) + "','" + list.get(8) + "','" + userid + "');";
                        String query1 = "INSERT INTO " + Table_values + " VALUES('" + plcData + "','" + agencyName + "','" + list1.get(0) + "','" + list1.get(1) + "','" + list1.get(2) + "','" + list1.get(3) + "','" + list1.get(4) + "','" + list1.get(5) + "','" + list1.get(6) + "','" + list1.get(7) + "','" + list1.get(8) + "','" + userid + "');";
                        String query2 = "INSERT INTO " + Table_ListId + " VALUES('" + plcData + "','" + agencyName + "','" + list2.get(0) + "','" + list2.get(1) + "','" + list2.get(2) + "','" + list2.get(3) + "','" + list2.get(4) + "','" + list2.get(5) + "','" + list2.get(6) + "','" + list2.get(7) + "','" + list2.get(8) + "','" + userid + "');";
                        myDB.execSQL(query);
                        myDB.execSQL(query1);
                        myDB.execSQL(query2);
                    }
                   return true;
                }
                return false;
            }
    // TODO TO INSERT CARD DETAILS
         public boolean insertCardDetails(Context context, ArrayList list, String cardNumber, String userid, String randomID) {
                SQLiteDatabase myDB = getWritableDatabase("my_paygov_secure_key");
                //  Integer i = Integer.parseInt(cardNumber);
                long card = Long.valueOf(cardNumber).longValue();
                int ccnumber = (int) card;
              /* for (int i = 0; i < list.size(); i++) {
                   System.out.println("LIST VALUE" + i + list.get(i).toString());
               }*/
                boolean dbCheck = CheckIsDataAlreadyInDBorNot(Table_PaymentOptions, "randomID", randomID, "userid", userid);
                if (dbCheck) {
                    String query = "UPDATE " + Table_PaymentOptions + " SET var1='" + list.get(0) + "',var2='" + list.get(1) + "',var3='" + list.get(2) + "',var4='" + list.get(3) + "',var5='" + list.get(4) + "',var6='" + list.get(5) + "',var7='" + list.get(6) + "',var8='" + list.get(7) + "'   WHERE randomID ='" + randomID + "' and  userid='" + userid + "'";
                } else {
                    String query = "INSERT INTO " + Table_PaymentOptions + " VALUES('" + ccnumber + "', '" + list.get(0) + "','" + list.get(1) + "','" + list.get(2) + "','" + list.get(3) + "','" + list.get(4) + "','" + list.get(5) + "','" + list.get(6) + "','" + list.get(7) + "','" + userid + "', '" + randomID + "');";
                    myDB.execSQL(query);
                }
                return false;
            }
    // TO INSERT DATA FOR REMINDER
          public boolean myPaymentReminder(Context context, ArrayList list, int plcData, String userid) {
                SQLiteDatabase myDB = getWritableDatabase("my_paygov_secure_key");
                boolean dbCheck = CheckIsDataAlreadyInDBorNot(Table_PaymentRemider, "id", String.valueOf(plcData), "userid", userid);
                if (dbCheck) {
                    String query = "UPDATE " + Table_PaymentRemider + " SET agencyname='" + list.get(1) + "',amountDue='" + list.get(2) + "',dateDue='" + list.get(3) + "',reminderNotes='" + list.get(4) + "',reminderDate='" + list.get(5) + "',reminderTime='" + list.get(6) + "',toggleOption='" + list.get(7) + "', uniqueID='" + list.get(8) + "' WHERE id ='" + plcData + "' and  userid = '" + userid + "'";
                    myDB.execSQL(query);
                } else {
                    String query = "INSERT INTO " + Table_PaymentRemider + " VALUES('" + plcData + "', '" + list.get(0) + "','" + list.get(1) + "','" + list.get(2) + "','" + list.get(3) + "','" + list.get(4) + "','" + list.get(5) + "','" + list.get(6) + "','" + list.get(7) + "','" + userid + "','" + list.get(8) + "');";
                    myDB.execSQL(query);
                }
                System.out.println("PlcData" + plcData);

                return true;
            }

// TODO TO CHECK TABLE IS ALREADY PRESENT OR NOT
    public boolean CheckIsDataAlreadyInDBorNot(String TableName,
                                               String dbfield, String fieldValue, String dbfield1, String field1) {
        SQLiteDatabase myDB = getWritableDatabase("my_paygov_secure_key");
        String Query = "Select * from " + TableName + " where " + dbfield + " = " + fieldValue + " and " + dbfield1 + " = '" + field1 + "'";
        System.out.println("Check User id DB" + Query);
        Cursor cursor = myDB.rawQuery(Query, null);
        if (cursor.getCount() <= 0) {
            cursor.close();
            return false;
        }
        cursor.close();
        return true;
    }
THE EXCEPTION THAT I GET IN LOGCAT !
 net.sqlcipher.database.SQLiteConstraintException: UNIQUE constraint failed: PLC_DETAILS_TABLE_VARIABLES.id: INSERT INTO PLC_DETAILS_TABLE_VARIABLES VALUES('36400', 'Indiana-Dearborn County-Environmental Health','Name','Dearborn Enviro','','','','','','','','1e2332d3-cc60-48eb-bca0-8499097411bc');

Answer 1

public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + Table_variables + "(id INTEGER PRIMARY KEY ,var1 text, var2 text, var3 text, var4 text, var5 text, var6 text, var7 text, var8 text, var9 text, var10 text, userid text )");
            db.execSQL("CREATE TABLE " + Table_values + "(id INTEGER PRIMARY KEY , val1 text, val2 text, val3 text, val4 text, val5 text, val6 text, val7 text, val8 text, val9 text, val10 text ,userid text )");
            db.execSQL("CREATE TABLE " + Table_ListId + "(id INTEGER PRIMARY KEY , val1 text, val2 text, val3 text, val4 text, val5 text, val6 text, val7 text, val8 text, val9 text, val10 text,userid text )");
            db.execSQL("CREATE TABLE " + Table_PaymentOptions + "(id INTEGER PRIMARY KEY, var1 text, var2 text, var3 text, var4 text, var5 text, var6 text, var7 text, var8 text , userid text, randomID text)");
            db.execSQL("CREATE TABLE " + Table_PaymentRemider + "(id INTEGER PRIMARY KEY, plc text, agencyname text,amountDue text, datedue text, reminderNotes text, reminderDate text, reminderTime text, toggleOption text, userid text, uniqueID text)");
        }

您将id作为主要键。当您在表中插入重复值时，您会收到唯一的约束错误。实施一种方法以检查重复ID或用try-catch块环绕execSQL("INSERT ...")，要么可以工作。第二种方法：

String query = "INSERT INTO " + Table_variables + " VALUES('" + plcData + "', '" + agencyName + "','" + list.get(0) + "','" + list.get(1) + "','" + list.get(2) + "','" + list.get(3) + "','" + list.get(4) + "','" + list.get(5) + "','" + list.get(6) + "','" + list.get(7) + "','" + list.get(8) + "','" + userid + "');";
String query1 = "INSERT INTO " + Table_values + " VALUES('" + plcData + "','" + agencyName + "','" + list1.get(0) + "','" + list1.get(1) + "','" + list1.get(2) + "','" + list1.get(3) + "','" + list1.get(4) + "','" + list1.get(5) + "','" + list1.get(6) + "','" + list1.get(7) + "','" + list1.get(8) + "','" + userid + "');";
String query2 = "INSERT INTO " + Table_ListId + " VALUES('" + plcData + "','" + agencyName + "','" + list2.get(0) + "','" + list2.get(1) + "','" + list2.get(2) + "','" + list2.get(3) + "','" + list2.get(4) + "','" + list2.get(5) + "','" + list2.get(6) + "','" + list2.get(7) + "','" + list2.get(8) + "','" + userid + "');";
try {
     myDB.execSQL(query);
     myDB.execSQL(query1);
     myDB.execSQL(query2);
} catch(SQLException se) {
     //do stuff when you catch a duplicate ID
 }