Android 如何防止在sqlite数据库中插入重复值（如果重复则覆盖）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26326696/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to prevent to insert duplicate value in sqlite database (if duplicate then overwrite)
提问by Ravi
I had created two table in my database, In both table I am inserting value at the same time, now what I want to do is that, I want to insert record in second table, but the condition is that, if there is two same record then I want insert only one record not duplicate value, In second table there is two field one is idand second is category, when user insert two same category that time I want to insert only one entry, below is my code which is not working properly, It insert all record accept duplicate value..
我在我的数据库中创建了两个表，在两个表中我同时插入值，现在我想做的是，我想在第二个表中插入记录，但条件是，如果有两个相同的记录然后我只想插入一条记录而不是重复值，在第二个表中有两个字段，一个是id，第二个是类别，当用户插入两个相同的类别时，我只想插入一个条目，下面是我的代码，不是工作正常，它插入所有记录接受重复值..
public long InsertCat(String idd, String cat)
{
try
{
SQLiteDatabase db;
long rows = 0;
db = this.getWritableDatabase();
ContentValues Val = new ContentValues();
Val.put("IDD", idd);
Val.put("Category", cat);
Cursor c = db.rawQuery("SELECT * FROM " + TABLE_CATEGER + " WHERE Category='"+cat+"'",null);
while(c.moveToNext())
{
if(c.getString(0).equals(cat))
{
flag=true;
}
}
if(flag==true)
{
rows=db.update(TABLE_CATEGER, Val, "Category='"+cat+"'" , null);
System.out.print(rows);
db.close();
}
if(flag==false)
{
rows = db.insert(TABLE_CATEGER, null, Val);
System.out.print(rows);
db.close();
}
return rows; // return rows inserted.
} catch (Exception e) {
return -1;
}
}
回答by Panther
Put all your values inside ContentValuesand then call this on writableDatabase
把你所有的价值观放在里面ContentValues，然后调用它writableDatabase
db.insertWithOnConflict(tableName, null, contentValues,SQLiteDatabase.CONFLICT_REPLACE);
EDIT:
编辑：
Well all you need is only this part of code
那么你需要的只是这部分代码
SQLiteDatabase db;
long rows = 0;
db = this.getWritableDatabase();
ContentValues Val = new ContentValues();
Val.put("IDD", idd);
Val.put("Category", cat);
rows = db.insertWithOnConflict(tableName, null, contentValues,SQLiteDatabase.CONFLICT_REPLACE);
insertWithOnConflictmethods follows the last parameter as the reaction algorithm when an duplicate row is Found. And for a duplicate row to be found, the primary keys has to clash. If all this satisfys the row would surely get Replaced :-/ If not something else is going wrong..
insertWithOnConflict当发现重复行时，方法遵循最后一个参数作为反应算法。为了找到重复的行，主键必须发生冲突。如果所有这一切都满足该行肯定会被替换：-/ 如果不是其他事情出错了..
回答by Tarun Tak
While creating your table, put constraint on your column(Primary key or Unique key).This will not only the duplicate value to be inserted into your database.
在创建表时，对列（主键或唯一键）施加约束。这不仅会将重复值插入到数据库中。
回答by Vijay
Check with the primary key of which you want assign then while inserting check with on duplicate key.. if you want you update the row
检查您要分配的主键，然后在插入检查重复键的同时检查.. 如果您想更新该行
on duplicate key update
关于重复密钥更新
As mentioned i am writing the code on duplicate key
如前所述，我在重复键上编写代码
INSERT OR REPLACE INTO TABLE_CATEGER(value1,value2,value3)
回答by asim
Use the following query
使用以下查询
INSERT OR REPLACE INTO table_name (idColoumn, categoryColumn) VALUES (?, ?)
It will add new row if it does not exist or update row if it exists. hope this will help you.
如果不存在，它将添加新行，如果存在则更新行。希望这会帮助你。
回答by Ravi
This is working for me,and i also created Category as a primary key..
这对我有用，我还创建了 Category 作为主键..
ContentValues Val = new ContentValues();
Val.put("IDD", idd);
Val.put("Category", cat);
long rows=db.insertWithOnConflict(TABLE_CATEGER, null, Val,SQLiteDatabase.CONFLICT_REPLACE);
System.out.print(rows);
Log.d("kkkkkkkkkk",""+ rows);
db.close();
return rows; // return rows inserted.
回答by Sai Gopi N
use insertWithOnConflict() method instead of insert()
使用 insertWithOnConflict() 方法代替 insert()
response = sqLiteDatabase.insertWithOnConflict(TABLE_TRACKER_LOGS_LINES, null, contentValues,SQLiteDatabase.CONFLICT_REPLACE);
