Android SQLite避免重复插入：重复数据时覆盖原有数据的实现方法

看起来你想要实现“存在则更新，不存在则插入”的Upsert逻辑，咱们一步步来调整你的代码：

第一步：给表添加唯一约束 #

首先得明确什么是“相同的数据条目”——比如你是认为titleFav单独相同就算重复，还是titleFav+namaPerusahaanFav组合相同才算？这里假设用titleFav作为唯一标识（你可以根据业务需求换成组合字段），修改表创建语句，给目标字段加上UNIQUE约束：

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_FAVORIT_TABLE = "CREATE TABLE " + Constant.favoritDBtable + "("
            + Constant.id_postFav + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + Constant.titleFav + " TEXT UNIQUE," // 添加唯一约束，避免重复
            + Constant.namaPerusahaanFav + " TEXT,"
            + Constant.lokasiFav + " TEXT,"
            + Constant.kriteria_1Fav + " TEXT,"
            + Constant.kriteria_2Fav + " TEXT,"
            + Constant.kriteria_3Fav + " TEXT,"
            + Constant.gajiFav + " TEXT,"
            + Constant.img_logoFav + " TEXT,"
            + Constant.tanggalFav + " TEXT);";
    db.execSQL(CREATE_FAVORIT_TABLE); // 你原来的代码漏了执行创建语句，一定要补上！
}

注意：如果是多个字段组合唯一，写成UNIQUE(titleFav, namaPerusahaanFav)即可。另外，若你已经安装过测试版本的App，修改表结构后需要卸载重装，或者在onUpgrade方法中处理表升级，否则新约束不会生效。

第二步：修改插入方法，实现Upsert逻辑 #

原来的db.insert()会直接插入重复数据，我们换成insertWithOnConflict()方法，当触发唯一约束冲突时自动替换原有数据：

public long addFavorit(Favorit favorit){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(Constant.titleFav, favorit.getTitleFav());
    values.put(Constant.namaPerusahaanFav, favorit.getNamaPerusahaanFav());
    values.put(Constant.lokasiFav, favorit.getLokasiFav());
    values.put(Constant.kriteria_1Fav, favorit.getKriteria_1Fav());
    values.put(Constant.kriteria_2Fav, favorit.getKriteria_2Fav());
    values.put(Constant.kriteria_3Fav, favorit.getKriteria_3Fav());
    values.put(Constant.gajiFav, favorit.getGajiFav());
    values.put(Constant.img_logoFav, favorit.getImg_logoFav());
    values.put(Constant.tanggalFav, favorit.getTanggalFav());

    // 冲突时替换原有数据
    long result = db.insertWithOnConflict(
            Constant.favoritDBtable,
            null,
            values,
            SQLiteDatabase.CONFLICT_REPLACE
    );

    if(result != -1){
        Log.d("Favorit saved: ", "Success 200 OK");
    } else {
        Log.e("Favorit saved: ", "Failed");
    }
    db.close(); // 别忘了关闭数据库连接，避免内存泄漏
    return result;
}

备选方案：直接使用REPLACE SQL语句 #

如果你更习惯写原生SQL，也可以用REPLACE INTO实现同样效果：

public long addFavorit(Favorit favorit){
    SQLiteDatabase db = this.getWritableDatabase();
    String sql = "REPLACE INTO " + Constant.favoritDBtable + " ("
            + Constant.titleFav + ", " + Constant.namaPerusahaanFav + ", "
            + Constant.lokasiFav + ", " + Constant.kriteria_1Fav + ", "
            + Constant.kriteria_2Fav + ", " + Constant.kriteria_3Fav + ", "
            + Constant.gajiFav + ", " + Constant.img_logoFav + ", "
            + Constant.tanggalFav + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    SQLiteStatement statement = db.compileStatement(sql);
    statement.bindString(1, favorit.getTitleFav());
    statement.bindString(2, favorit.getNamaPerusahaanFav());
    statement.bindString(3, favorit.getLokasiFav());
    statement.bindString(4, favorit.getKriteria_1Fav());
    statement.bindString(5, favorit.getKriteria_2Fav());
    statement.bindString(6, favorit.getKriteria_3Fav());
    statement.bindString(7, favorit.getGajiFav());
    statement.bindString(8, favorit.getImg_logoFav());
    statement.bindString(9, favorit.getTanggalFav());
    
    long result = statement.executeInsert();
    statement.close();
    db.close();
    
    if(result != -1){
        Log.d("Favorit saved: ", "Success 200 OK");
    } else {
        Log.e("Favorit saved: ", "Failed");
    }
    return result;
}

第三步：验证逻辑 #

你的saveToFavoritDB方法可以保留，建议每次操作后打印数据库条目数量和具体数据，确认没有重复数据且旧数据被成功覆盖。

内容的提问来源于stack exchange，提问作者Kitereative Indonesia