Insert or update Android SQLite

Question

as you can see in the documentation, the syntax for inserting or updating is: INSERT OR REPLACE INTO <table> (<columns>) VALUES (<values>) , is there any function in my question that combines the following?

 public long insert (String table, String nullColumnHack, ContentValues values) public int update (String table, ContentValues values, String whereClause, String[] whereArgs) 

or does it need to be done using a prepared SQL statement and rawQuery?

What are the best practices for inserting or updating on Android?

Answer 1

I suggest that you ask how to INSERT new lines or UPDATE existing lines in one step. Although this is possible in one raw SQL, as discussed in this answer , I found it easier to do this in Android in two steps using SQLiteDatabase.insertWithOnConflict () , using CONFLICT_IGNORE to conflict the algorithm.,

 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 } 

This example assumes that the table key is set for the column "_id", that you know the entry _id and that there is already row # 1 (_id = 1, columnA = "valueA", columnB = "valueB"). There is a difference using insertWithOnConflict with CONFLICT_REPLACE and CONFLICT_IGNORE

• CONFLICT_REPLACE will overwrite existing values ​​in other columns with a null value (i.e. ColumnB will become NULL, and the result will be _id = 1, columnA = "valueNEW", columnB = NULL). As a result, you will lose existing data, but I do not use them in my code.
• CONFLICT_IGNORE will skip SQL INSERT for your existing row # 1, and you will UPDATE this SQL row in the next step, saving the contents of all other columns (i.e. the result will be _id = 1, columnA = "valueNEW", columnB = "VALUE billion") .

If you try to insert a new row No. 2, which does not exist yet, the code will execute SQL INSERT only in the first insertWithOnConflict statement (that is, the result will be _id = 2, columnA = "valueNEW", columnB = NULL).

Beware of this error , which causes SQLiteDatabase.CONFLICT_IGNORE crashes in API10 (and probably API11). The request returns 0 instead of -1 when testing on Android 2.2.

If you do not know the _id record key or if you have a condition that does not create a conflict, you can change the logic to UPDATE or INSERT . This will save your _id record key during UPDATE or create a new _id record during INSERT.

 int u = yourdb.update("yourtable", values, "anotherID=?", new String[]{"x"}); if (u == 0) { yourdb.insertWithOnConflict("yourtable", null, values, SQLiteDatabase.CONFLICT_REPLACE); } 

The above example assumes that you just want to update the timestamp value in a record, for example. If you call insertWithOnConflict first, INSERT will create a new _id entry due to the difference in state of the timestamp.

Answer 2

this is your SQLiteDatabase.insertWithOnConflict () method. to understand what that means, this sqlite document

Answer 3

SQLiteDatabase.replace () does this, basically calls:

 insertWithOnConflict(table, nullColumnHack, initialValues, CONFLICT_REPLACE); 

Too bad the documentation is not very clear.

Answer 4

The name of the operation for this is "upsert", and as I solve it, I identify the columns of your table that make the row UNIQUE.

Example: _id, name, job, hours_worked

The columns we will use are name and work.

 private int getID(String name, String job){ Cursor c = dbr.query(TABLE_NAME,new String[]{"_id"} "name =? AND job=?",new String[]{name,job},null,null,null,null); if (c.moveToFirst()) //if the row exist then return the id return c.getInt(c.getColumnIndex("_id")); return -1; } 

---

In your database manager class:

 public void upsert(String name, String job){ ContentValues values = new ContentValues(); values.put("NAME",name); values.put("JOB",job); int id = getID(name,job); if(id==-1) db.insert(TABLE_NAME, null, values); else db.update(TABLE_NAME, values, "_id=?", new String[]{Integer.toString(id)}); } 

Answer 5

SQLiteDatabase.replace () is probably what you are looking for. I have not tried, but the document says that it returns the row id of the newly inserted row , so it may work.

Answer 6

I had the same problem, but I realized that when my object already has an identifier, it should be updated, and when it does not have an identifier, it should be inserted so that this is a step by step that I did to solve problem: / p>

1- in your getId object use Integer or initialize Id as you see fit: here is my code

 public Integer getId() { return id; } 

2- check the identifier in your method to insert or update after you put everything in ContentValues:

 if(myObject.getId()!= null ){ int count = db.update(TABLE_NAME,myContentValues,ID + " = ? ", new String[]{String.valueOf(myObject.getId())}); if(count<=0){ //inserting content values to db db.insert(TABLE_NAME, null, myContentValues); } } else { db.insert(TABLE_NAME, null, myContentValues); } 

what happens here is that I check Id if exists. I am updating this row, but if the update method returns -1, it means that there were no rows with this identifier, so I insert a row, and if it does not have an identifier, I insert it.

hope this helps.

Answer 7

What about replaceOrThrow (String table, String nullColumnHack, ContentValues ​​initialValues)

Documents say ... A convenient method for replacing a row in a database. Inserts a new row if the row does not already exist.

This basically calls insertWithOnConflict