Android SQLite 插入或更新 [英] Android SQLite Insert or Update
问题描述
从文档中可以看出,插入或更新的语法是: as can be seen in the documentation the syntax to make insert or update is : 还是必须使用准备好的 SQL 语句和 rawQuery 来完成? or it has to be done with a prepared SQL statement and rawQuery? 在 Android 中执行插入或更新的最佳做法是什么? What's the best practices to do an insert or update in Android? 我相信您在问如何一步插入新行或更新现有行.虽然这在 这个答案 中讨论的单个原始 SQL 中是可能的,但我发现在 Android 中使用 SQLiteDatabase.insertWithOnConflict() 使用 CONFLICT_IGNORE 作为冲突算法. I believe that you are asking how to INSERT new rows or UPDATE your existing rows in one step. While that is possible in a single raw SQL as discussed in this answer, I found that it easier to do this in two steps in Android using SQLiteDatabase.insertWithOnConflict() using CONFLICT_IGNORE for conflictAlgorithm. 此示例假设为列_id"设置了表键,您知道记录 _id,并且已经存在第 1 行(_id=1, columnA = "valueA", columnB = "valueB").这是使用 insertWithOnConflict 与 CONFLICT_REPLACE 和 CONFLICT_IGNORE 的区别 This example assumes that the table key is set for column "_id", that you know the record _id, and that there is already row #1 (_id=1, columnA = "valueA", columnB = "valueB"). Here is the difference using insertWithOnConflict with CONFLICT_REPLACE and CONFLICT_IGNORE 当您尝试插入尚不存在的新行 #2 时,代码将仅在第一条语句 insertWithOnConflict 中执行 SQL INSERT(即,结果将为 _id=2, columnA = "valueNEW", columnB =NULL). When you attempt to insert new row #2 which does not exist yet, the code will only execute the SQL INSERT in the first statement insertWithOnConflict (ie. the result will be _id=2, columnA = "valueNEW", columnB = NULL). 注意导致 SQLiteDatabase.CONFLICT_IGNORE 的这个错误在 API10(可能还有 API11)上发生故障.当我在 Android 2.2 上测试时,查询返回 0 而不是 -1. Beware of this bug which is causing SQLiteDatabase.CONFLICT_IGNORE to malfunction on API10 (and probably API11). The query is returning 0 instead of -1 when I test on Android 2.2. 如果你不知道记录键_id或者你有一个不会产生冲突的条件,你可以将逻辑反转为UPDATE或INSERT.这将在 UPDATE 期间保留您的记录键 _id 或在 INSERT 期间创建新记录 _id. If you do not know the record key _id or you have a condition that will not create a conflict, you can reverse the logic to UPDATE or INSERT. This will keep your record key _id during UPDATE or create a new record _id during INSERT. 以上示例假设您只想更新记录中的时间戳值.如果先调用insertWithOnConflict,由于时间戳条件不同,INSERT会创建新的记录_id. The above example assumes that your just want to UPDATE timestamp value in the record for example. If you call insertWithOnConflict first, INSERT will create new record _id due to the difference in the timestamp condition. 这篇关于Android SQLite 插入或更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!INSERT OR REPLACE INTO
(<columns>) VALUES (<values>),我的问题是有没有合并以下内容的函数?
INSERT OR REPLACE INTO <table> (<columns>) VALUES (<values>)
, my question is there any function that merge the following ?public long insert (String table, String nullColumnHack, ContentValues values)
public int update (String table, ContentValues values, String whereClause, String[] whereArgs)
推荐答案
ContentValues initialValues = new ContentValues();
initialValues.put("_id", 1); // the execution is different if _id is 2
initialValues.put("columnA", "valueNEW");
int id = (int) yourdb.insertWithOnConflict("your_table", null, initialValues, SQLiteDatabase.CONFLICT_IGNORE);
if (id == -1) {
yourdb.update("your_table", initialValues, "_id=?", new String[] {"1"}); // number 1 is the _id here, update to variable for your code
}
int u = yourdb.update("yourtable", values, "anotherID=?", new String[]{"x"});
if (u == 0) {
yourdb.insertWithOnConflict("yourtable", null, values, SQLiteDatabase.CONFLICT_REPLACE);
}
登录
关闭