android - SQLiteStatement - 列不是唯一的 - Android？

Question

这段代码让我崩溃并告诉我:

SQLiteConstraintException: columns Count, Date, Time, Lat, Lng, UserCode, LatLng are not unique (code 19)

这是我的代码:

public class Query_B extends SQLiteOpenHelper {
    // database version
    private static final int DATABASE_VERSION = 7;

    // database name
    protected static final String DATABASE_NAME = "xxxxxx";

    // table details
    public String tableName = "ReportAct_tbl";
    public String fieldRepID = "RepID";
    public String fieldCount = "Count";
    public String fieldDate = "Date";
    public String fieldTime = "Time";
    public String fieldLat = "Lat";
    public String fieldLng = "Lng";
    public String fieldUserCode = "UserCode";
    public String fieldLatLng = "LatLng";

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

    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "";


        sql += "CREATE TABLE " + tableName;
        sql += " ( ";
        sql += fieldRepID + " INTEGER PRIMARY KEY AUTOINCREMENT, ";
        sql += fieldCount + " TEXT, ";
        sql += fieldDate + " TEXT, ";
        sql += fieldTime + " TEXT, ";
        sql += fieldLat + " TEXT, ";
        sql += fieldLng + " TEXT, ";
        sql += fieldUserCode + " TEXT, ";
        sql += fieldLatLng + " TEXT ";
        sql += " ) ";

        db.execSQL(sql);

        String INDEX = "CREATE UNIQUE INDEX locations_index ON "
                + tableName + " (Count, Date, Time, Lat, Lng, UserCode, LatLng)";

        db.execSQL(INDEX);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS " + tableName;
        db.execSQL(sql);

        onCreate(db);
    }


    public void InsertFast(List<Marketing_Points_B> values) {

        String sql = "INSERT INTO " + tableName + " ( Count, Date, Time, Lat, Lng, UserCode, LatLng ) VALUES ( ?, ?, ?, ?, ?, ?, ? )";

        SQLiteDatabase db = this.getWritableDatabase();

        db.beginTransactionNonExclusive();

        SQLiteStatement stmt = db.compileStatement(sql);

        for (int i = 0; i < values.size(); i++) {
            stmt.bindString(1, values.get(i).getCounts());
            stmt.bindString(2, values.get(i).getDate());
            stmt.bindString(3, values.get(i).getTime());
            stmt.bindString(4, String.valueOf(values.get(i).getLat()));
            stmt.bindString(5, String.valueOf(values.get(i).getLng()));
            stmt.bindString(6, values.get(i).getUserCode());
            stmt.bindString(7, String.valueOf(values.get(i).getmPosition()));
            stmt.execute();
            stmt.clearBindings();
        }

        db.setTransactionSuccessful();
        db.endTransaction();

        db.close();
    }
    // deletes all records
    public void deleteRecords() {

        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("delete from " + tableName);
        db.close();
    }
}

Answer 1

当您使用 CREATE UNIQUE INDEX 时，不允许重复值。
但是您的列 Count, Date, Time, Lat, Lng, UserCode, LatLng 可以包含重复值，因为它不是唯一的。因此你有这个异常(exception)

您可以使用 CREATE INDEX 而不是 CREATE UNIQUE INDEX(因为 CREATE INDEX 允许重复值)